mysql索引

索引

索引是对数据库表中的一列或多列的值进行排序的一种结构。Mysql索引的建立对于mysql的高效运行是很重要的，所以可以大大提高mysql的检索速度。 索引只是提高效率的一个因素，如果你的mysql有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。索引是占据物理空间的，在不同的存储引擎中，索引存在的文件也不同。

索引的优缺点

优点

• 索引大大减少了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因；
• 索引可以帮助服务器避免排序和创建临时表；
• 索引可以将随机IO变成顺序IO；
• InnoDB在辅助索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）；
• 可以加速表和表之间的连接，特别是在实现数据的完整性方面特别有意义；
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能；
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

缺点

• 空间的代价
  • 每建立一个索引，都要为它建立一个B+树，每一棵B+树的每一个节点都是一个数据页。一个数据页默认会占用16KB的存储空间，而一颗很大的B+树由许多数据页组成，这将占用很大的一片存储空间。
• 创建索引和维护索引需要耗费时间，这种时间随数据量的增加而增加；
  • 在执行查询语句前，需要生成一个执行计划，在生成执行计划时需要计算使用不同索引执行查询时所需要的成本，最后选择成本最低的索引进行查询。如果建立了太多索引，会导致成本分析过程耗时太多，影响查询语句的执行性能。
• 索引需要占用一定的物理空间，如果需要建立聚集索引，那么需要占用的空间会更大；
• 对表中的数据进行增删改的时候，索引也要动态维护；
• 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果；
• 对于非常小的表，大部分情况下简单的全表扫描更高效；

创建索引准则

索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

应该创建索引的列

• 在经常需要搜索的列上，可以加快搜索的速度；
• 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
• 在经常使用在WHERE子句中的列上面创建索引，加快条件判断；
• 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
• 在经常需要根据范围（<, <=, =, >, >=, BETWEEN, IN）上进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
• 在经常用join连接的列上，这些列主要是外键，可以加快连接的速度；

不该创建索引的列

• 对于那些在查询中很少使用或者参考的列不应该创建索引；
  • 若列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求；
• 对于那些只有很少数据值或者重复值很多的列也不应该创建索引；
• 对于那些定义为text，image和bit数据类型的列不应该创建索引；
  • 这些列的数据量要么相当大，要么取值很少
• 当该列修改性能要求远远高于检索性能时，不应该创建索引；

聚集索引

索引中键值的逻辑顺序决定了表中相应行的物理顺序（索引中的数据物理存放地址和索引的顺序是一致的），可以这么理解：只要索引是连续的，那么数据在存储介质上的存储位置也是连续的。

• 如果一个主键被定义了，那么这个主键就是作为聚集索引；
• 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引；
• 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，该列的值会随着数据的插入自增；

InnoDB的聚集索引的叶子节点存储的是行记录（其实是页结构，一个页包含多行数据）；

InnoDB必须要有至少一个聚集索引；

由此可见，使用聚集索引查询会很快，因为可以直接定位到行记录；

---

聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据叶。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都是通过一个双向链表来进行链接。

每张表只能拥有一个聚集索引。

聚集索引对于主键的排序查找和范围查找速度非常快。

---

B+树本身是一个目录，或者说本身是一个索引

• 使用记录主键值的大小进行记录和页的排序
  • 页（包括叶子节点和内节点）内的记录按照主键的大小顺序排成一个单向链表，页内的记录被划分成若干个组，每个组中最大的记录在页内的偏移量会被当做槽依次存放在页目录中，我们可以在页目录中通过二分法快速定位到主键列等于某个值的记录。
  • 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
  • 存放目录项记录的页分为不同的层级，在同一层级中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
• B+树叶子节点存储的是完整的用户记录。所谓完整的用户记录，就是指这个记录存储了所有列的值（包括隐藏列）
  聚集索引就是数据的存储方式，也就是所谓的“索引即数据，数据即索引”。

非聚集索引

非聚集索引也就是二级索引，聚集索引是按照主键来建立一颗B+树，非聚集索引按照非主键列来建立一颗B+树。

按照二级索引建立的B+树，其中的叶子节点存储的并不是完整的用户记录，而是二级索引+主键这两个列的值。目录项中记录的不再是主键+页号的搭配，而变成了二级索引+页号的搭配。

索引的逻辑顺序与磁盘上的物理存储顺序不同。非聚集索引的键值在逻辑上也是连续的，但是表中的数据在存储介质上的物理顺序是不一致的，即记录的逻辑顺序和实际存储的物理顺序没有任何联系。索引的记录节点有一个数据指针指向真正的数据存储位置（指向聚集索引）。

InnoDB的普通索引叶子节点存储的是主键（聚集索引）的值。

回表：通过携带主键信息到聚集索引中重新定位完整的用户记录的过程。然后再返回到这棵B+树的叶子节点处，找到刚才定位到的符合条件的那条用户记录。

---

对于辅助索引（也称非聚集索引），叶子节点并不包含行记录的全部数据。叶子节点除了包含键值之外，每个叶子节点中的索引行还包含了一个书签（bookmark）。该书签告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的复制索引的书签就是相应行数据的聚集索引键。

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

聚集索引和非聚集索引的优缺点

优点

• 数据访问更快，因为聚集索引将索引和数据保存在同一个B+树中，因此从聚集索引种获取数据比非聚集索引更快

• 聚集索引对于主键的排序查找和范围查找速度非常快

缺点

• 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键；
• 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新；
• 辅助索引访问需要两次索引查找，第一次找到聚集索引的值，第二次根据聚集索引找到行数据；

联合索引

联合索引是指对表上的多个列进行索引。联合索引的创建方法与单个索引创建的方法一样，不同之处仅在于有多个索引列。

create table t(
	a int,
    b int,
    primary key(a),
    key idx_a_b(a,b)
)engine=innodb;

从本质上说，联合索引也是一颗B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。

数据按（a, b）的顺序进行了存放。

因此，对于查询select * from table where a=xxx and b=xxx;，显然是可以使用（a, b）这个联合索引的，对于单个a列查询select * from table where a=xxx;也可以使用这个（a, b）索引。但是对于b列的查询select * from table where b=xxx;则不可以使用这棵B+树索引。可以发现叶子节点上的b值为1，2，1，4，1，2，显然不是排序的，因此对于b列的查询使用不到（a, b）的索引。

回表查询

先通过非聚集索引的值定位到聚集索引，再通过聚集索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫描一遍索引树更低。

前缀索引

一个字符串由若干个字符组成。如果字符串很长，那么在存储这个字符串时就需要占用很大的存储空间。在需要为这个字符串所在的列建立索引时，就意味着在对应的B+树中的记录中，需要把该列的完整字符串存储起来。

字符串越长，在索引中占用的存储空间越大。

只将字符串的前几个字符存放在索引中，也就是说在二级索引的记录中只保留字符串的前几个字符。

前缀索引不支持使用索引进行排序的需求。因为前缀索引只存储了字符串的一部分。

覆盖索引

覆盖索引，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。在一棵索引树上就能获取所需的列数据，无需回表，速度更快。

在索引中已经包含所有需要读取的列的查询方式称为覆盖索引。

create table user(
	id int(10) auto_increament,
	name varchar(30),
    age tinyint(4),
    primary key(id),
    index idx_age(age);
)engine=innodb charset=utf8mb4;

如何实现覆盖索引

• 将被查询的字段，建立到联合索引中去。

如select id,age from user where age=10;

因为age是非聚集索引，使用了age索引，通过一次扫描B+树即可查询到相应的结果。

select id,age,name from user where age=10;

age是非聚集索引，但name不在索引树上，所以通过age索引在查询到id和age的值后，需要进行回表再查询name的值。

为了实现覆盖索引，需要建立联合索引

drop index idx_age on user;
create index idx_age_name on user(`age`,`name`);

此时字段age和name是组合索引idx_age_name，查询的字段id，age，name的值刚刚都在索引树上，只需要扫描一次联合索引B+树即可。

哪些场景适合适用覆盖索引来优化SQL

全表count()查询优化

select count(age) from user;

列查询回表优化

select id,age,name from user where age=10;

建立联合索引idx_age_name(age,name)即可

分页查询

select id,age,name from user order by age limit 100, 2;

自适应哈希索引

InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引（Adaptive Hash Index, AHI）。AHI是通过缓冲池的B+树构造而来的，因此建立的速度很快，而且不需要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。

AHI有一个要求，即对这个页的连续访问的模式必须是一样的。例如对于（a，b）这样的联合索引页，其访问模式可以是以下情况

• where a=xxx
• where a=xxx and b=xxx

访问模式一样指的是查询的条件一样，若交替进行上述两种查询，那么InnoDB存储引擎不会对该页构造AHI。

需要注意的是。哈希索引只能用来搜索等值的查询，如：select * from table where index_col='xxx';，对于其他查找类型，如范围查找，是不能使用哈希索引的。

操作索引

//创建普通索引
create index index_name on table_name(col_name);

//创建唯一索引
create unique index index_name on table_name(col_name);

//创建普通组合索引
create index index_name on table_name(col_name_1,col_name_2);

//创建唯一组合索引
create unique index index_name on table_name(col_name_1,col_name_2);

修改表结构创建索引

alter table table_name add index index_name(col_name);

创建表时直接指定索引

create table table_name(
	ID int not null,
	col_name varchar (16) not null,
	index index_name(col_name)
);

删除索引

//直接删除索引
drop index index_name on table_name;

//修改表结构删除索引
alter table table_name drop index index_name;

全文索引

倒排索引

全文检索通常使用倒排索引（inverted index）来实现。倒排索引同B+树索引一样，也是一种索引结构。它在辅助表中存储了单词与单词自身在一个或多个文档所在位置之间的映射。通常用关联数组实现。

拥有两种表现形式：

• inverted file index，其表现形式为{单词，单词所在文档的ID}
• full inverted index，其表现形式为{单词，（单词所在文档的ID，在具体文档中的位置）}

全文索引

全文索引是将存储于数据库中的整本书或整篇文章中的任何内容信息查找出来的技术。它可以根据需要获得全文中有关章，节，段，句，词等信息，也可以进行各种统计和分析。

FULLTEXT 与 LIKE+%的对比

使用like+%可以实现模糊匹配，适用于文本比较少的时候。对于大量的文本检索，like+%的速度比不上全文索引。

例如：有title字段，需要查询所有包含“政府”的记录，使用like+%政府%方式查询，查询速度慢（全表查询）。且当查询包含“政府”or“中国”两个字段时，使用like就难以满足，而全文索引可以实现这个功能。

全文索引的限制

• 每张表只能有一个全文检索的索引；
• 由多列组合而成的全文检索的索引列必须使用相同的字符集和排序规则；
• 不支持没有单词界定符的语言，如中文，日语，韩文等；

创建全文索引

//建表的时候
fulltext key keyname(colume1,colume2)

//在已存在的表上的创建
create fulltext index keyname on xxtable(colume1,colume2)

alter table xxtable add fulltext index keyname (colume1, colume2)

使用全文索引

全文索引有独特的语法格式，需要配合match和against关键字使用

• match()函数中指定的列必须是设置为全文索引的列
• against()函数标识需要模糊查找的关键字

create table fulltext_text(
	id int auto_increment primary key,
    words varchar(2000) not null,
    article text not null,
    fulltext index words_artical(words, artical)
)engine=innodb default charset=utf8;

insert into fulltext_test values(null,'a','a');
insert into fulltext_test values(null,'aa','aa');
insert into fulltext_test values(null,'aaa','aaa');
insert into fulltext_test values(null,'aaaa','aaaa');

select * from fulltext_test where match(words,artical) against('a');
select * from fulltext_test where match(words,artical) against('aa');
select * from fulltext_test where match(words,artical) against('aaa');
select * from fulltext_test where match(words,artical) against('aaaa');

对于innoDB存储引擎的全文索引，还需要考虑以下因素：

• 查询的word在stopword列中，忽略该字符串的查询。
• 查询的word字符的长度是否在区间[innodb_ft_min_token_size, innodb_ft_max_token_size]中。

索引失效的情况

• > <范围查询

  • mysql会一致向右匹配直到遇到> <就停止匹配。索引最多使用一个> <的范围列，因此如果查询条件中有两个> <范围列则无法全用到索引

• like %xx

  • 若搜索键值以通配符%开头，如like '%abc'，则索引失效，直接全表扫描；若只是以%结尾，则不影响索引构建

• 对索引列进行计算

  • 如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描。select * from user where YEAR(birthday) < 1990;

• or条件索引问题

  • or的条件列除了同时是主键的时候，索引才会生效；其他情况下，无论条件列是什么，索引都失效。select * from tb1 where nid=1 or nid=2; 这条语句会生效；select * from tb1 where name='Joe' or name='Tom';这条语句会失效；

• 数据类型不一致（隐式类型转换导致的索引失效）

  • 如果列是字符串类型，传入条件必须用引号括起来，不然报错或索引失效；select * from tb1 where name=12;

• 联合索引违背最左匹配原则

• order by问题

  • order by对主键索引排序会用到索引，其他的索引失效；select * from tb1 order by name;索引失效；select * from tb1 order by nid;索引生效

• !=问题

  • 普通索引使用!=索引失效，主键索引没有影响。where语句中索引列使用了负向查询，可能会导致索引失效。负向查询包括NOT,!=,<>,NOT IN,NOT LIKE等；

explain

使用explain关键字可以模拟优化器执行SQL查询语句，从而直到mysql是如何处理SQL语句。

mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+

• id：选择标识符
• select_type：表示查询的类型
• table：输出结果集的表
• partitions：匹配的分区
• type：表示表的连接类型
• possible_keys：表示查询时，可能使用的索引
• key：表示实际使用的索引
• key_len：索引字段的长度
• ref：列与索引的比较
• rows：扫描出的行数
• filtered：按表条件过滤的行百分比
• extra：执行情况的描述和说明

普通索引和唯一索引的区别

有普通索引的字段可以写入重复的值，有唯一索引的字段不可以写入重复的值。

Insert Buffer

对于非聚集索引的插入时，先判断插入的非聚集索引页是否在缓冲池中；如果在，则直接插入；如果插入，则先放入Insert Buffer中，然后再以一定的频率和情况进行Insert Buffer和辅助索引叶子的merge操作，这时通常能将多个插入合并到一个操作中，就大大提高了非聚集索引的插入性能；

为什么增加insert Buffer?

增加Insert Buffer有两个好处？

• 减少磁盘的离散读取；
• 将多次插入合并为一次操作；

使用Insert Buffer得满足两个条件；

• 索引是辅助索引；
• 索引不是唯一的；

Change Buffer

Change Buffer也得满足两个条件；

• 索引是辅助索引；
• 索引不是唯一的；

为什么唯一索引的更新不使用Change Buffer？

唯一索引必须要将数据页读入内存中才能判断是否违反唯一性约束，如果都已经读入内存了，那直接更新内存会更快，就没必要使用Change Buffer了。

区别

• 数据修改时，普通索引可以使用Change Buffer，而唯一索引不行；
• 数据修改时，唯一索引在重复读隔离级别下，更容易出现死锁；
• 查询数据时，普通索引查到满足条件的第一条记录还需要继续查找下一个记录，而唯一索引查找到第一个记录就可以直接返回结果了，但是普通索引多出的查找次数所消耗的资源多数情况下可以忽略不计；
• 有普通索引的字段可以写入重复的值，而有唯一索引的字段不可以写入重复的值；

哪些情况需要添加索引

• 数据检索时在条件字段添加索引；
• 聚合函数对聚合字段添加索引；
• 对排序字段添加索引；
• 为了防止回表添加索引；
• 关联查询在关联字段添加索引；