参考文章
相关文章：
写在开头：本文为学习后的总结，可能有不到位的地方，错误的地方，欢迎各位指正。
目录
一、 索引的作用
没有索引的话就只能全局扫描，加了索引的话，可以加快查找的速度（联想下现实生活中查字典的情景，先通过目录中的索引找到对应目标的页数，然后再去对应页找到该数据即可，当然索引实际上并没有这么简单，这与索引的实现方式有关，这一点下面会解释）
PS. 索引并不是完美的，使用索引也会带来一系列问题，比如索引本身也需要磁盘块来存储，也占据一定的空间，并且随着表数据的增加而增加，再者索引为了实现迅速的访问，需要始终保持一定的顺序，因此索引的建立与维护本身也需要一定的资源消耗。
二、 索引分类
#查看索引
show index from table_name
#删除索引
DROP INDEX index_name ON table_name（1）主键索引
也叫聚簇索引（其他索引叫做非聚簇索引，或辅助索引，与物理结构有关，后面会解释）顾名思义，加在主键上的索引，创建表时若指定了主键，则会自动在这一列上创建索引。
CREATE TABLE `table1` (
`aaa` varchar(20) ,
`bbb` varchar(20),
`ccc` varchar(20) ,
`ddd` varchar(20) ,
`eee` varchar(20) ,
primary key(`aaa`)
)
可以看到自动创建了1个主键位置上的索引，其中，index_type表示索引的实现方式若创建表时没有指定主键，后续指定主键的时候也会自动创建主键索引。同时我们取消主键的时候主键索引也会自动消失。
alter table table1 drop primary key;
show index from table1注意，要删除主键索引只能用删除主键的方式。如果没有显示指定主键，则mysql会选取首个为唯一且非空的列作为主键索引，如果还没具备，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
（2）唯一索引
创建表时指定或者后续再加
CREATE TABLE `table2` (
`aaa` varchar(20) ,
`bbb` varchar(20),
`ccc` varchar(20) ,
`ddd` varchar(20) ,
`eee` varchar(20) ,
primary key(`aaa`),
UNIQUE index_name_unique(bbb)
)
CREATE UNIQUE INDEX index_name_unique2 ON table2(ccc);
（3）复合索引
create index complex_index on table2(ddd,eee);复合索引有1个需要注意的地方，我们可以从下图看出，实际上是新建了2个索引，同时复合索引还有1个特点，就是“最左侧原则”，即当你使用复合索引中的字段查询时，必须满足从左至右的顺序，比如上面这个索引是建立在ccc,ddd,eee上的，搜索时如果只会按照ccc,ddd,eee的顺序去匹配索引，比如查找条件为 ccc = '1' and eee='2' 则只有ccc使用了索引。详细的讲解可以在学习了explain后看这里的讲解《mysql联合 索引(复合索引)的探讨》
三、创建索引的准则
（一）、应该创建索引的列
- 需要经常查找的列
- 经常需要范围查找例如(in,between)的列上，因为索引是排好序的，所以可以加速查找
- 在经常需要排序的规则上创建，理由同上
（二）、不该创建索引的列
- 在查询中很少参与的列不应该建立索引
- 数据较少或者重复值很多的列不应该建立索引，这两者建立索引意义不大
- 增删改过于频繁的列不应该创建索引，会导致索引的维护成本巨大
四、 索引的物理结构
索引的物理结构需要根据不同的存储引擎去具体讨论，在InnoDB和MyIsam中 ,使用B+树作为索引的存储方式。其中MyIsam每张表都有1个.myi文件来存储索引，InnoDB则是和数据文件存储在.ibd这1个文件中。而MEMORY存储引擎则支持哈希表和B+树作为索引的实现方式。
本文主要讲下InnoDB的索引。要讲解索引的物理实现，就必须要了解B+这种数据结构。有兴趣的朋友可以按这个顺序去了解，二叉查找树->平衡二叉树->B树->B+树。简单解释下就是，（机械硬盘情况下）因为外存速度较慢，主要时间消耗在寻道时间上，所以我们尽可能把索引数据都集中在一个磁盘块中，使1个磁盘块能够存储的索引更多，这样就能避免多次寻道导致的时间浪费。可以简单的理解为我们把平衡二叉树改为了平衡m叉树，使树的深度更低，从而减少寻道时间。
（1）在InnoDB和MyIsam中，索引字段通过B+树的结构实现了顺序存储，通过中序遍历的方式就可以实现顺序访问
（2） 在Innodb的主键索引（聚簇索引）中，叶子节点存储的不仅包括索引，还包括数据行（所以聚簇索引只有1个），辅助索引（非聚簇索引，即非主键的一切索引）的叶子节点存储的不是数据行，而是本行数据对应的主键索引（结合下图左与上文中加粗字段加深理解），这也是为什么，有些书上解释聚簇索引的描述如下“聚簇索引的存储顺序就是物理数据的存储顺序”。
（3）在MyIsam中主键与非主键索引的叶子节点都只包含指向数据行的指针（因此Myisam其实没有所谓聚簇、非聚簇的概念，因为主键、非主键索引大家都一样）。
在InnoDB中使用B+树实现聚簇索引，优势在于
（1）B+树除了叶子节点都不存储数据，其余每个节点都只存储索引，使得1个磁盘块能够存储尽可能多的存储索引，减少磁盘I/O，同时B+树的叶子节点间存在顺序指针，范围查找是可以加速查找。
（2）辅助索引不直接指向数据行，当数据行出现变通时只要变更主键索引即可。
但同样也有缺点，比如：
（1）辅助索引的叶子节点大小随主键索引大小而变化(因为辅助索引的叶子节点存储的主键索引）
（2）辅助索引一次查找会更加耗时（辅助索引只能先找到主键索引，再去查询数据）
（3）插入顺序如果不是按照主键增长顺序的话，会导致B+树结构的重新调整（主要是分页），维护成本巨大
五、 索引失效的情况
看了很多分析索引失效的文章，但总结后发现很多时候并不能死记规则，有的时候1个不生效的规则稍微一改动就不一样了，因此这里暂时不去详细分析失效的情况，我们主要讲下利用explain实现索引效果的分析。
简单介绍下就是，我们的sql语句前面加上explain 就能判断这条语句是否利用了索引其中最重要的3个字段,type显示查询类型，是全表扫描还是索引合并，possible_key表示可能用到的索引和key表示实际用到的索引。explain的详解可以看这里（《一张图彻底搞懂MySQL的 explain》）
注意，在数据量不大的情况下，Mysql在指定执行计划时发现全表扫描比索引查找更快，因此会全局扫描。
（1）隐式类型转换
比如varchar字段匹配数组类型,请看下面这个例子
这里索引未生效的实际原因为mysql做了隐式的类型转换，这条sql相当于
select * from table1 where CAST(bbb AS signed int) = 1所以我们在使用字符类型做查询条件时，一定要格式规范
（2）隐式字符编码转换
这里需要先介绍下Mysql的字符集，比较早的utf8字符集实现了3字节unicode，但还是有一特殊字符未包含在内，因此又有了utf8mb4，使用4字节unicode。也就是说，utf8mb4是utf8的超集。在数据库中，字符集是按字段分的，同一张表的不同字段字符集可能不同。我们可以通过information_schema.`COLUMNS`来详细查看每个字段的字符集
然后我们修改下table2的字符集
ALTER table table2 convert to character set utf8这里我们拿子查询举例，我们可以看到，前者的前者的子查询未能有效的利用到索引，因为这里实际上产生了隐式的编码类型转换，table2的aaa字段字符集是utf8，需要先转换为utf8mb4才能匹配子查询中的结果。因为utf8mb4是utf8的超集（mysql的自动类型转换遵循向上转换的规则）。反之则不需要，因为utfbmb4是兼容了utf8的，table1的aaa字段并不需要去做专门的字符集转换。
同样的情况也会出现在表的连接与存储过程的调用中，但表的连接规则较为复杂，涉及到驱动表的选择等。这里无法全面的讲解，有兴趣的朋友可以看这篇文章《mysql连表操作是先连表还是先查询条件》
下面举个简单的例子。
PS. 关于存储过程的默认字符集，可以通过修改mysql.ini中的default-character-set 来实现
（3）常见索引失效情况
1、or两边的字段都需要字段 （只有1侧有索引无法覆盖所有的情况）
2、模糊匹配的匹配条件以%开头（导致只匹配索引的开头无法做出正确的判断）
3、组合索引使用时需遵守由左到右的规则，比如index(a,b,c)，查询条件b,或b.c都无效，a,c则只有a生效（因为组合索引的结构在建立的时候会按照索引创建时指定的顺序排序，回忆下我们java中的多字段排序，先按照a排序，然后按照b,c排序，缺少了a,自然无法对b,c查找。注意，这里的a,b,c生效是指a,b,c都出现，而不是指必须按a,b,c的顺序写sql，mysql的查询优化器会帮我们把顺序调整过来）
勘误：索引列is null 或 is not null也会使用索引，有些地方以讹传讹，认为不会使用索引，实际上我手动试了下是会引用的，并且mysql官方也给出了样例《IS NULL Optimization》。
六、 补充
（1）关于覆盖索引
explain select * from table1 where bbb='1' and ccc='2' and ddd='3'
#与
explain select a,b,c from table1 where bbb='1' and ccc='2' and ddd='3'假设我现在建立了组合索引create index complex_index on table1(bbb,ccc,ddd)的结果是不相同的，我们辅助索引complex_index在查找完非聚簇索引的B+树后，发现并不需要再查找主键索引来查询其他数据，此时便可以直接返回了，这就是覆盖索引（extra会显示using index来提示使用了覆盖索引）。
专业术语叫做不用回表（即不用再回去查主键索引）。
（2）关于前缀索引
例如1个字段varchar(2000)，我们建立索引时给整个字段建立索引那么索引文件会很大，我们可以只给前n个字符建立索引，来达到减小索引文件长度的目的，这就是前缀索引，但是这样一来又会导致出现较多的重复值，引起选择性的降低，反而降低效率，这块可以自行学习，这里不展开来讲解。
（3）关于change buffer
当一条数据被更新后，如果这条数据所在的页在内存中，则直接写入。如不在，则会将这条更新操作缓存到change buffer中，待该页被调入内存时在写入（称为megre）。同时后台线程也会定期meger，将数据写回，除此之外数据库关闭时也会执行此操作。这样做的好处是减少了随机磁盘的访问。
（4）多个普通索引同时使用的情况
我们可以发现再多个查询条件都有索引的情况下，最终使用的索引只有1个。这实际上是因为mysql优化器会评估用哪个条件的索引效率最高，在回表后再通过主键索引查找出所有的数据行后筛选出符合条件的数据。
360
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
