mysql的索引

一、索引的概念

索引：是帮助mysql高效获取数据的数据结构。

索引的本质：索引是数据结构。你可以简单理解为“排好序(order)的快速查找(where)数据结构”，

索引有两大功能：查找（where）和排序（order）

也就是说索引用于排序和快速查找。即你定义的键的索引将会影响到sql的两部分：一个就是你所写的where条件后面这部分的条件约束是否用到索引，他们负责查找的条件过滤，即索引会影响到where后面的查找。第二部分，sql中用order by 排序，索引会影响到order by后面的排序。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据。这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。下图就是一种可能的索引方式示例：

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。

假设要找4号这本书，这本书对应的编号是91，91比34大，往右边放，91比89大，往右边放，这样找了三次后就可以找到藏书的物理地址0xF3,

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应的数据，从而快速的检索出符合条件的记录。就不用全表扫描。

Java工程师往表中插入数据，我们为了查找快，DBA要给每天的数据备份、恢复日志、建索引，你在写delete方法的时候，其实不会真正进行物理删除，而是将标识位从激活状态变成非激活状态，此时仍保证数据的连续性，很多互联网公司在service层调用delete方法，最后是调用update方法，就是将激活标识位从1（激活状态）改为了0，逻辑上这条记录是被删除的，物理上存在，这是为什么呢？第一个原因是为了数据分析，因为现在进入云计算和大数据时代，你java工程师不用，别的部门最好有客户浏览记录和客服下单记录，大部分的时候是把它从使用状态变成非激活状态，尽量不删。第二个原因是为了索引，数据在频繁的修改删除新增了以后，慢慢的这颗树会失效，结果发现建了索引之后反而越来越慢了，因为有时候索引指不准。如果删除了，指过去发现是空的，这时候查找就挂了，这种情况下，很多DBA大晚上的过来锁表，然后重建索引。为什么查询快增删慢呢？因为在数据库中除了要改一条数据的记录以外，还要改索引，否则索引指不对了。所以频繁删改的字段不适合建索引。支离破碎，这棵树建起来也是残缺的。

索引的目的在于提高查找效率，可以类比字典。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的，有可能是三叉的）结构组织的索引。其中，聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引。

索引有排序加查找两大功能，一是解决where后面是否查得快，二是解决order by 排序的时候如何查找快。

索引就是在表的列上构建一个二叉树，达到大幅度提高查询效率的目的，但是索引会影响增删改的效率。

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。索引有很多种我们主要介绍常用的几种:

为什么添加了索引之后，会加快查询速度呢？
图书馆：如果杂乱地放书的话检索起来就非常困难,所以将书分类，然后再建一个箱子，箱子里面放卡片，卡片里面可以按类查询,按书名查或者类别查,这样的话速度会快很多很多，这个就有点像索引。索引的好处就是提高你找到书的速度，但是正是因为你建了索引，就应该有人专门来维护索引，维护索引是要有时间精力的开销的，也就是说索引是不能乱建的，所以建索引有个原则：如果有一个字段如果不经常查询，就不要去建索引。现在把书变成我们的表，把卡片变成我们的索引，就知道为什么索引会快，为什么会有开销。

二、索引的优势和劣势

索引的优势：

1、 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。树的高度变成了你查找次数的维度，假设树最高三层，最多查三次。

2、 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

索引的劣势：

1、实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引类也是要占用空间的，

2、虽然索引大大提高了查询速度，同时会降低更新表的速度，如对表进行增删改（写操作），因为更新表时，mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。都会调整因为更新所带来的键值变化后的索引信息。也就是说不仅要改数据，还要改索引。比如说天龙八部这本书从三号柜子挪到了7号柜子，这本书发生了物理位置上的变更，索引的指向也要改，否则索引就无效了。

3、索引只是提高效率的一个因素，如果你的mysql有大数据量的表，就需要花时间研究建立最优秀的索引，开始时你会猜测客户可能会按照这样的字段去查，故先在这个字段上建索引，后来根据点击率分析和客户所筛选的条件发现客户怎么会按那个字段查，索引字段要不停的优化和调整。索引都是根据不停的分析，删了建，建了删，逐步优化出来的。

在日常工作中，是建单值索引多还是建复合索引多？在电商系统中，在最上面有一个form表单筛选各种条件，而我们在查询的时候一般会选择多个条件查询，

三、创建索引

索引分单列索引和复合索引。建了索引之后会排序，所以查得快一些。

基本语法：

1、创建：

create 【unique】index indexname on mytable(columnname(length));

或

alter table mytable add [unique] index [indexname] on (columnname(length))

2、删除：

drop index 【indexname】 on mytable;

3、查看：

show index from table_nameG

有四种方式来添加数据表的索引：

1、 添加普通索引，索引值可出现多次

Alter table tb_name add index index_name(column_list);

2、 添加唯一索引，索引的值必须是唯一的（除了null外，null可能会出现多次）

Alter table tb_name add unique index_name(column_list);

3、 添加主键索引，索引的值非空且唯一

Alter table tb_name add primary key  (column_list);

4、 添加全文索引。

Alter table tb_name add fulltext index_name (column_list);

1． 单列索引

单列索引是基于单个列所建立的索引，即一个索引只包含单个列，一个表可以有多个单列索引。比如:

CREATE index 索引名 on 表名(列名)

-- 给emp表的ename列创建索引
create index idx_ename on emp(ename);

索引名称的定义：idx_表名_字段名，业界的潜规则：idx_开头的表示索引

如：create index idx_user_name on user(name);

此时，index目录多了一个索引：

单列索引触发规则：条件必须是索引列中的原始值（SCOTT）。单行函数，模糊查询都会影响索引的触发。

select * from emp t where t.ename='SCOTT';

一张表一般而言建复合索引优于单值索引，大部分人查询时都会按多选来查询，针对频繁使用的字段，银行系统会按照两个字段来查询，即银行卡号和个人的身份证号码，这就是复合索引。姓名是绝对不可以的，因为重名的人很多。

一张表最多建的索引不要超过5个。

2．唯一索引

索引列的值必须唯一，但允许有空值null。但是银行卡号不能重。

3． 复合索引

查询条件越来越多，但是频繁使用的字段我们建索引，这样查得更快。

select * from user where name= '' and email = '';
create index idx_user_nameEmail on user(name,email);
复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同,比如：
Create index emp_idx1 on emp(ename,job);
Create index emp_idx1 on emp(job,ename);

范例：给 person 表的 name 建立索引

create index pname_index on person(name);

范例：给 person 表创建一个 name 和 gender 的索引

create index pname_gender_index on person(name, gender);

复合索引中第一列为优先检索列，如果要触发复合索引，必须包含有优先检索列中的原始值

-- 触发复合索引
select * from emp t where t.ename='SCOTT' and job='ANALYST';

如果enamel列既是单列索引，又包含在复合索引中，执行以下SQL语句，触发的是单列索引，

select * from emp t where t.ename='SCOTT';

注意：or关键字不触发索引，如下语句，

select * from emp t where t.ename='SCOTT' or job='ANALYST';

因为有or关键字，相当于写了两个查询语句，一个触发索引，一个不触发索引，故不触发索引。

select * from emp t where t.ename='SCOTT'; --触发单列索引
select * from emp t where job='ANALYST'; --不触发索引

索引的使用原则：

1、在大表上建立索引才有意义

2、在 where 子句后面或者是连接条件上的字段建立索引

3、表中数据修改频率高时不建议建立索引，因为建立索引会影响增删改的效率。

四、索引的类型

1、主键索引 PRIMARY KEY

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。

PRIMARY KEY (`id`)

2、 唯一索引 UNIQUE

唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。可以在创建表的时候指定，也可以修改表结构。

UNIQUE KEY `num` (`number`) USING BTREE

3、 普通索引 INDEX

这是最基本的索引，它没有任何限制。可以在创建表的时候指定，也可以修改表结构

KEY `num` (`number`) USING BTREE

4、组合索引 INDEX

索引分单列索引和组合索引(联合索引)。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

KEY `num` (`number`,`name`) USING BTREE

注意，组合索引前面索引必须要先使用，后面的索引才能使用。

5、 全文索引 FULLTEXT

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

五、有索引和没有索引的区别

创建表

 创建存储过程

drop procedure if exists tb_insert;
CREATE PROCEDURE tb_insert()
BEGIN
DECLARE i INT;
SET i = 0;
START TRANSACTION;
WHILE i < 10 DO -- 10即插入10条数据
INSERT INTO tb_table (`name`,`number`) VALUES (concat("张三",i),i);
SET i = i+1;
END WHILE;
COMMIT;
END;
call tb_insert();

然后再添加数据库的数据，插入 100万条，测试有索引和没有索引的查询语句。

SELECT * FROM tb_table WHERE number = 500000

然后再添加数据库的数据，插入 100万条，再次测试有索引和没有索引的查询语句。

通过上面的对比测试可以看出，索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。对于少量的数据，没有合适的索引影响不是很大，但是，当随着数据量的增加，性能会急剧下降。索引的目的在于提高查询效率，大家可以回忆之前学习的全文检索技术。类似使用字典，如果没有目录（索引），那么我们要从字典的第一个字开始查询到最后一个字才能有结果，可能要把字典中所有的字看一遍才能找到要结果，而目录（索引）则能够让我们快速的定位到这个字的位置，从而找到我们要的结果。

 六、索引的存储结构

BTree索引、Full-text全文索引、哈希索引、R-tree索引。

1、BTree索引：

USING BTREE：就是MySQL所使用的索引方案，MySQL中普遍使用B+Tree做索引，也就是BTREE。

特点：

BTREE 索引以B+tree的结构存储数据

BTREE 索引能够加快数据的查询速度

BTREE 索引更适合进行行范围查找

使用的场景：

1. 全值匹配的查询，例如根据订单号查询 order_sn='98764322119900'

2. 联合索引时会遵循最左前缀匹配的原则,即最左优先

3. 匹配列前缀查询，例如：order_sn like '9876%'

4. 匹配范围值的查找，例如：order_sn > '98764322119900'

5. 只访问索引的查询

【初始化介绍】

一颗B+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的，P2表示17和35之间的，P3表示大于35的。

真实的数据存在于叶子节点，即3、4、9、10、13、15、28、29、36、68、75、79、90、99

非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

【查找过程】

如果要查找29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短，（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3层的B+树可以表示上百万的数据，如果上百万的数据查找只需要3次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

2、全文索引：

Full-text索引也就是我们常说的全文索引，MySQL中仅有MyISAM和InnoDB存储引擎支持。对于文本的大对象，或者较大的 CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成Full-text索引时，会为文本生成一份单词的清单，在索引时根据这个单词的清单来索引。

注意：

1、对于较大的数据集，把数据添加到一个没有 Full-text索引的表，然后添加Full-text索引的速度比把数据添加到一个已经有Full-text索引的表快。

2、针对较大的数据，生成全文索引非常的消耗时间和空间。

3、5.6 版本前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那么全文索引不会生效。5.6版本和之后InnoDB存储引擎开始支持全文索引。

4、在 MySQL中，全文索引只对英文有用，目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。

5、在 MySQL中，如果检索的字符串太短则无法检索得到预期的结果，检索的字符串长度至少为4字节。

七、索引的使用

虽然索引能够为查找带来速度上的提升，但是也会对性能有一些损失。

1、索引会增加写操作的成本

2、太多的索引会增加查询优化器的选择时间

当创建索引带来的好处多过于消耗的时候，才是最优的选择~

使用索引的场景：

1、主键自动建立唯一索引；

2、经常作为查询条件在 WHERE语句中出现的列要建立索引；如银行系统的银行账号，电信系统的手机号，微信系统的微信号，

3、作为排序的列要建立索引；

4、查询中与其他表关联的字段，外键关系建立索引。如员工表的deptId可以建立索引。

5、高并发条件下倾向建立组合索引；

6、用于聚合函数的列可以建立索引，例如使用 count(number)时，number列就要建立索引

单表查询：Id、where、order by、聚合函数、

多表查询：外键

高并发：组合索引

哪些情况要建立索引？

单值索引还是组合索引？选组合索引。

Group by是分组，分组的前提是必排序，也就是说group by跟索引息息相关。也要满足复合的要求。

不使用索引的场景：

1、有大量重复的列不单独建立索引

2、表记录太少不要建立索引，因为没有太大作用。

3、不会作为查询的列不要建立索引，即where条件里用不到的字段不创建索引。

4、频繁更新的字段不适合创建索引。

哪些情况不要建立索引？

1、 表记录太少。Mysql中300万条记录左右性能开始逐渐下降，

2、 经常增删改的表。因为建索引提高了查询速度，同时会降低更新表的速度，因为更新表时，mysql不仅要保存数据，还要保存一下索引文件。

3、 数据重复且分布平均（各占50%）的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意如果某个数据列包含了许多重复的内容，为它建立索引就没有太大的实际效果。

 


 
 
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/zwh0910/p/14354366.html