MySQL索引使用规则总结

如何加快查询，最直接有效的办法就是增加索引，在不使用索引的情况下试图采用其他方式加快查询就是在浪费时间。本文先介绍下MySQL索引的基本数据结构，再对索引的基本规则做下总结。

索引的必要性

通过索引查找的整体思路是避免遍历查找，而是通过已经建立好的索引结构找到目标数据， 或确认目标数据不存在，从而完成查询。如果说在单表不用索引的情况下遍历查询还可以忍受，那么在多表联合查询的情况下不使用索引时匹配次数将会达到天文数字。

再重温下这个常见的例子：3个没有索引的表table1,table2,table3。每个数据表都包含一个数据列c1,c2,c3，且每个数据列都从数字1到数字1000的1000个数据行。要查找这些数据表中具有相同数值的所有数据行的组合，其查询语句应该是下面这样：

select table1.c1,table2.c2,table3.c3 from table1 inner join table2 inner join table3 where table1.c1 = table2.c2 and table2.c2 = table3.c3

在不是用索引的情况下遍历查询需要从(1000*1000*1000) = 10亿的临时记录中匹配出实际需要的1000条数据，显然造成了极大的浪费。

在使用索引情况下来分析下关联查询的过程：

• 从数据表table1中选择第一个数据行，看这个数据行包含什么样的值
• 对数据表table2中使用索引，直接找到与数据表table1的值相匹的数据行。同样，对数据表table3使用索引，直接找到与数据表table1的值相匹配的数据行
• 对数据表table1的下一个数据行重复上面的过程， 直到检查完数据表table1的所有数据行。

整个查询过程中table1遍历查询，但table2,table3都是带索引搜寻，直接将那些数据挑选出来，查询速度比不用索引时快了100万倍。

MySQL索引实现

MyISAM索引实现

1. 数据与索引是分开存放(图一)；
2. 新增数据直接追加写数据文件，同时更新索引；
3. B+树的叶子节点上存储的是数据的实际地址偏移；
4. 主索引与辅助索引(图二)在数据结构上没有区别。主Key可以等同唯一索引。

(图一)

(图二)

MyISAM按主Key范围查找的时候仍然可能会导致随机读取，这对机械磁盘来说是个悲剧。

InnoDB索引实现

1. 数据文件是按B+树组织的一个索引结构文件(图三)，这个数据文件的索引就是主Key。
2. 这棵B+树的叶节点data域保存了完整的数据记录。InnoDB的辅助索引(图四)data域存储相应记录主键的值而不是数据地址。
3. 如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

(图三)

(图四)

索引常用使用方式

• 在查询操作中把与where子句所给出的条件相匹配的数据行尽快找出；
• join等关联操作中把与其他行相关的数据行尽快找出来；
• min(),max()函数操作的列如果是建立索引的，那么min,max求值时可以迅速找到而不用通过逐行遍历的方式查找；
• 如果某些列经常需要order by, group by的列加上索引也可以加快排序和分类操作；

挑选合适的列作索引

1.经常出现在where、group by, order by，join子句后的列考虑建立索引。

2.建立的索引列需要有较好区分度。如果数据列的独一无二的值越多，区分度越高，索引效果越好。例如：对班级表格的性别字段的索引只有两个值’F’,’M’，采用性别别的索引无论怎么查询，得到的是数据表的一半左右的记录。MySQL的查询优化逻辑甚至不会选择对于这样的索引，而改成遍历搜索。

3.数据列尽量的短小。例如能用整型的就不要用字符串类型。

原因：短小数值可以让比较操作更快完成，加快查找速度；短小数值可以让索引体积更小，减少磁盘I/O；短小数值可以让缓存里容纳更多的索引信息。InnoDB存储引擎短小的主Key有助于减少辅助索引的体积；

4.为字符串的前缀设置索引。例如某列char(100)，但大多数前10或20已经有足够的区分度就没必要为整个字符串列建立索引。原因还是出于磁盘I/O，缓存空间，匹配效率方面考虑。

5.在InnoDB中要用单调字段作为主键。原因：InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

使用索引的基本原则

1.数据类型相同的数据列进行比较；例如int/init比较，bigint/bigint比较效率就要好于int/bitint的比较

2.带索引的数据列在比较表达式中单独出现；例如

where mycol*2 < 4；数据列中每一个值都要被读取并计算结果在比较

where mycol < 4/2；优化程序先计算表达式4/2，然后在索引里进行检索

3.复合索引充分利用最左前缀。创建了N个列的符合索引，实际上创建了MySQL能够使用的n个索引。例如某表的复合索引 index(国家，省份，城市)。那么可用索引为

A.国家

B.国家,省份

C.国家,省份,城市

但必须满足最左缀原理。如果查询条件里只有国家,城市就无法充分利用改符合索引。

4.不在like的开始部分使用通配符。例如：

where col_name like ‘%string%’；如果是要查询出现在数据列的字符串，这子句是正确的，此时并没有使用col_name的索引，但不要出于习惯将%放到string两侧。如果改成

Where col_name like ‘string%’查询依string开头的字符串就可以使用col_name上的索引

5.自动类型转换可能会阻止索引的使用；例如：

select * from mytbl where num_col = 4;

select* from mytbl where num_col = ‘4’;

如果num_col是int且有索引，那么第二条语句有可能会阻止索引使用。

6.验证各种查询优化时需要变化查询参数多次运行。有时会发现当运行一种查询优化时查速度慢，当换另外一种查询方式时速度变快，此时会觉得第二种查询方式的效果更好。然后再用第一种查询方式查询发现速度通用变快。实际上这只不过是第一次查询时的信息仍然保存再缓存内，当后续查询时发现查询速度变快

过多索引带来的问题

1. 过多索引影响写性能。写操作时索引需要根据写入的数据进行调整，这样会降低带索引数据列的插入，删除，修改的速度。一个数据表的索引越多，需要做的调整调整越多，平均写性能降低的就越多。
2. 过多索引需要消耗更多的磁盘空间。索引在本质上通过空间换取时间。所以：谨慎创建索引，尽量采用已有索引，不需要的索引不要创建。

参考：

1、MySQL技术内幕：InnoDB存储引擎 2、MySQL技术内幕:第4版 3、MySQL高效编程 4、MySQL的官方手册 5、http://km.oa.com/articles/show/190056?kmref=search&from_page=1&no=1