mysql索引基础

对于一个web应用程序，除了基础的程序代码外，还有一个重中之重的数据库。任何一个应用不管做什么操作，都离不开数据的存储、操作，还有数据库的优化。作为一个开发者除了CRUD基本操作外，索引也是一个开发人员必须掌握的重点知识。

什么是索引？

作为一个开发者遇到性能问题的时候，咱总会去给表给字段加一个索引，那么什么是索引呢？

索引定义：是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。就像是一本书的目录，能够通过这一个目录快速定位到具体的内容位置。

关于索引的名词其实是非常多的，如：聚集索引、二级索引、全文索引、唯一索引、普通索引、前缀索引、哈希索引、B+tree索引……单单是记住这些名词，就已经让你凌乱不堪，头皮发麻。那么应该怎么来理解这堆名词，如何整理构建自己的知识体系呢？本文会从数据结构层、物理存储层、以及索引应用层面进行一次说明。

要深入理解索引，那么还是需要从底层数据结构算法说起吧。

索引结构算法

Hash哈希索引：hash是一种key-value形式的数据结构（跟数组一样），哈希索引是以列的值转为hashCode为键，将数据行的地址指针做为值形成的一种索引。

有序数组索引：就是按顺序存储的数组序列。

B+树：是mysql目前主要的索引存储算法，B+树的数据主要存储在叶子节点，而主子点只会存储主键值做为索引（如下图myisam的B+树结构）。

B树：也称为B-树，这种数据结构在主子点和叶子节点都存储着真实数据列。

小结：

hash哈希索引更适用于等值的查询，因为在创建索引的时候，都会将列进行哈希后，再保存，那么数据并不是顺序存储的，如果用于范围、排序等查询会很消耗，或者压根就无法查询。一般来说memcached等nosql数据库会更多地使用hash作为引擎。另外如果哈希值是相同的时候，这种情况下就需要使用链表存储了，但链表也会增加查询的消耗，效率会变低。
有序数组在等值查询和范围查询场景中的性能就都非常优秀，更适用于静态存储引擎，如果表总是要插入数据，那么在维护有序数组结构时，会很消耗的。
目前大部分数据库及文件系统都采用了B树或B+树，mysql的MyISAM/Innodb引擎都是使用B+Tree作为索引结构。主要原因是b+树在随机查询、排序查询、范围查询、以及占用空间都比较好，B树虽然也支持随机查询，但因数据结构占用空间比较大，所以mysql更倾向于用B+树做为底层的数据算法。如果某列数据频繁访问非常大，使用B树算法做为引擎数据结构也是不错的，因为频繁访问的数据放到靠近主节点的位置能减少数据查询算法复杂度。

物理层的索引（数据结构）

聚集（聚簇）索引：数据库表中行数据的物理顺序与索引键值的逻辑顺序相同。在索引的存储结构上，既存储行的主键值，又存储行数据，在底层数据结构B+树里，主节点会存储键值，而叶子节点会存储到实际的数据记录。

非聚集索引：数据库表中行数据的物理顺序与索引键值的逻辑顺序不一定相同，是随机的。在索引的存储结构上，只存储行的主键值，B+树里的主节点与聚簇索引一样是存储着键值，但叶子节点只会存储着主键值（不存行数据）。

关于聚集索引的几个重点

（1）innodb引擎聚集索引一般会建立在主键上

如果没有定义主键，那么会选择一个非空的唯一索引，如果连非空的唯一索引都没有的话，innodb会使用内置6字节长的rowID来隐式定义一个主键，作为聚集索引的主键。所以innodb引擎主键索引之外的普通索引，都是称为非聚集索引，另外由于聚集索引的数据结构特性，一个表也只有一个聚集索引（整张表的数据都已经存在索引里面了，再多一个也是重复的，没意义）。

（2）聚集索引需要有自增列，保证顺序存储

对于非自增主键的聚集索引里（如：唯一索引）这种索引在物理存储的时候，基本上是随机的（没顺序），那么排序和范围查找也会很消耗，另外在插入新数据的时候，都因为重构B+结构树，重新分页（B+树超叶节点数据16k时就需要分页存储）等问题导致插入速度很慢，所以聚集索引最好使用自增列（Innodb引擎已经默认自增列为聚集索引），有了自增列聚集索引存储时也是顺序的，也基本上与表数据一致，对于排序和范围查找基本上都不需要查表进行磁盘io读取，速度上是非常快。

（3）回表：

由于innodb引擎的非聚集索引数据结构里，叶子节点只存储主键值，并没有存储数据，那么在对主键以外的列进行查询时，在查找到列的数据时，只有主键值，这个时候，还需要通过这个主键值再找一次，才能找到真正的数据。这种情况称为回表。

（4）myisam引擎是非聚集索引，因数据存储物理地址的原因也没有回表这情况。

如上两图，myisam引擎在存储结构上，在叶子节点的数据里面存储的只是行数据的物理地址指针，并不像innodb引擎那样存储着整个数据列，所以myisam引擎在做查询的时候都会进行io读取，也没有回表这一说法。

（5）引申-索引的存储物理特征：

MyISAM引擎存储表分为三个文件.frm（表结构）、.MYD（表数据）、.MYI（表索引）。而Innodb引擎只有两个文件.frm（表结构）、.ibd（表数据），innodb的索引是存储在表数据里。

应用层面的索引名词

这也是此文刚开头所提到的名词，也是实际工作中使用及接触得比较多的名称，在我们比较熟悉的索引相关sql语句会有如下一些

#对表创建索引
create index index_name on `table_name`(column);

#PRIMARY KEY（主键索引）
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

#UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE ( `column` )

#INDEX|KEY (普通索引，在这里面KEY与INDEX是同义)
ALTER TABLE `table_name` ADD INDEX index_name ( `column1` )
ALTER TABLE `table_name` ADD KEY index_name ( `column1` )

#INDEX (联合/组合/多列索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

#FULLTEXT (全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

#SPATIAL INDEX (空间索引)
ALTER TABLE `table_name` ADD spatial INDEX index_name ( `column1` )

建立索引的sql语句

从sql来看，无非就是PRIMARY、UNIQUE、INDEX、FULLTEXT、SPATIAL等不同的关键字，也就是我们在用索引做性能优化时出现的sql语句。

主键索引：是使用主键创建的一个索引，因为主键的唯一性约束，也就成为了特殊性的唯一索引，主键索引是不允许空值的；对于innodb引擎来说，主键索引就是聚集索引

唯一索引：是不允许存在两个相同的索引值，当存在这样的字段索引时，增加新的数据时，如字段已经存在相同的记录，将会被拒绝；对于mysql来说唯一索引可以是空值null，而sql-server是不允许为空的。

普通索引：并没有太多限制，允许在定义索引的列中插入重复值和空值，主要还是实现索引的唯一目标，加快对数据的访问；

联合/组合/多列索引：是将多列字段创建形成一个普通索引，建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引，联合索引有一个最左前缀原则，也就是在查询时会按左到右的字段优先顺序使用索引；只有最左边的col1命中了，才会走索引，如上面的索引建立后，如果查询的where条件只有col2 and col3的判断（没有col1），那么在sql执行查询的时候，是不会走索引的。

复合主键索引：当一个主键与其它字段联合起来组成索引，就称为复合主键，比如id+name的索引，就保证了唯一性，不过这样的索引字段数目越少越好；

联合主键索引：当多个主键组成联合索引后，也就是联合主键索引，联合就在于主键A跟主键B形成的联合主键来确认唯一的记录，2个字段可以内容可以重复；

全文索引：在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建，目前是myisam引擎支持；

空间索引：是对空间数据类型的字段建立的索引，空间索引的列必须声明为NOT NULL，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON，对于普通程序员来说这是很少使用到的数据类型。

聚集索引：innodb引擎上特有的索引，只在主键索引建立，而且一张表只有一个聚集索引，主键索引之外的非聚集索引一般称为二级或辅助索引；

索引覆盖：属于索引优化的一种方式，在sql查询的select字段里面，如果存在字段并不在索引里面（没建立字段的索引），那么这个时候查询是不会命中索引，这个时候要么修改sql语句，只查包含索引的字段，或者给相应的字段加上索引，让查询能命中索引，这就是索引覆盖。

#name并没有建立索引
select id,name from user where name='sna';

#只查id,实现索引覆盖
select id from user where name='sna';
#给name字段加上索引,也能实现索引覆盖
create index username on user(name);

索引下推：属于innodb引擎层的索引算法优化，mysql5.6之后出来的索引特性，可以理解为优先在索引中做筛选，先选择到符合查询结果，减少回表查询次数，我们先看看以下的sql语句。

#name,age字段建立了联合索引
select * from user name like '张%' and age=20；

按照联合索引的最左原则，会先找到符合"name=张"开头的索引，比如有10条记录，那么这个时候就需要通过这10条记录的主键索引key进行回表，拿到数据列后依次找到每一条记录，再判断age是否等于20，10条记录就要回表10次。

mysql5.6使用了索引下推后，在找到符合"name=张"开头的索引后，会先从当前的记录的数据列里面拿数据判断age是否等于20，过滤掉不符合的条件的记录，而符合条件的再通过主键索引进行回表，拿到所需要的数据。

从上面能看出，通过索引下推能减少没必要的回表查询，性能更好。需要注意的是如果age字段没有建立索引，那么是不会进行下推的，索引下推的一个重点是优先对有索引字段进行筛选。

索引优化及原则

在此先说说索引优缺点吧

优点：在sql查询方面能减少很多io的读取操作。

缺点：索引也会占用一定的物理空间，另外在更新/插入数据时也需要同步更新索引，这个时候维护索引结构开销就会比较大了，不合理的索引会导致查询变得更慢。

索引的原则

选择合适的引擎：不同的引擎在数据查询是实现的算法是不同的，要考虑到curd的频繁度、查询数据是否经常做排序，从上面的数据结构中已经说明过innodb与myisam引擎的存储差异，在hash算法结构下做排序及范围查询怎么优化都没用，切记合适引擎更为重要，建表之前要做一下业务逻辑分析。
聚集索引最好是int类型：不建议使用字符串或过长的数值（如身份证），原因是过长的字符串占用空间也大，读取到内存的索引记录数量就会少，io读取会变得更大，如果字段过长最好截取前几个字符做为索引。
尽量让查询sql命中索引：如select *的时候，还有where条件like查询使用了%开头的话，是无法命中索引的。
优先使用联合索引：如果a字段已经建立了索引，业务运行一段时间后，发现b字段也需要建立索引，那么修改原来的a字段为(a,b)方式的联合索引会更好一些。
联合索引注意左匹配原则：使用联合索引时，第一个字段要使用高频查询字段，如（a,b,c）的索引，where条件里只查b、c字段是无法命中索引的。
不要创建没必要的索引：某些字段基本上不用于查询，创建了索引反而占用了空间，另外如果某些列经常更新，创建了索引虽然加快了读取速度，但更新的越频繁，更新数据时维护索引也很消耗，最明显的是更新插入数据时变慢了。
使用explain查看sql执行情况：explain这个命令可以查看SQL语句的执行计划，能看到SQL语句有没有使用上了索引，有没有做全表扫描。

索引具体案例

like 以%通配符开头：会导致索引失效会变成全表扫描操作；
select 带*号的查询少用：因为表最多只能建立16个索引，少于16列的表以及全字段创建索引的情况并不多，用了*来查询所有字段，基本上不会走索引；
使用不等于：!=或者<>的时候无法使用索引会导致全表扫描；
is null，is not null：在某些引擎上也无法使用索引，not in not exist也是如此，in的话，可用union代替。
where条件的列不要进行运算：from_unixtime(create_time) = ’2014-05-29’;是不会走索引的，改成create_time = unix_timestamp(’2014-05-29’)；
字符串与数值的隐式转换：也会导致不索引，如where id='1';//id 为int类型；
join 语法：尽量将小的表放在前面，在需要on的字段上，数据类型保持一致；
or条件前后：只要有一个列没有索引，就都不会用索引；