1.索引介绍1.1 什么是索引你面前有一本书当你想查找这本书的内容时我想大部分人都会选择先去查看这本书的目录然后根据目录到指定页去查找所需的内容。在这个过程中书本的目录就充当了索引的角色。在MySQL中索引就可以说是一种帮助存储引擎提高获取数据效率的数据结构与前面的例子类比一下就是索引就是数据的目录。1.2 索引的数据结构前面介绍索引本身是一种数据结构但数据结构也分很多种下面一一介绍1.2.1 哈希表哈希表的底层结构是Key-value模式以键值对的方式存储数据使用哈希表存储数据的情况下Key可以存放索引列value可以存放行记录或者磁盘地址在等值查询的情况下效率很高时间复杂度为O(1)。但在范围查询下则会走全表扫描效率极低。1.2.2 二叉查找树二叉查找树的特点是一个节点的左子节点全部小于这个节点右子节点全部大于这个节点这样独特的结构让我们在查询数据时和插入数据时就只要将数据和对应节点做比较就行。而且底层是通过二分查找去实现的所以时间复杂度只需要O(logn效率也不低。但二叉查找树有个致命弱点如果每次插入的都是当前最大值它会退化成一条链表查询退化为 O(n)。1.2.3 平衡二叉树上面介绍二叉查找树时引出一个问题在极端情况下二分查找树会退化为链表时间复杂度也相应退化成O(n)所以这就引入了平衡二叉树。平衡二叉树主要是在二叉查找树上增加了约束保证左子树和右子树的高度差不会超过1保证时间复杂度一直是O(logn也不会出现极端情况退化成链表的情况因为他会维持自平衡。但是它也存在缺陷随着插入元素变多而导致树的高度变高导致数据库磁盘I/O操作次数变多会影响整体数据查询效率。1.2.4 B树虽然上面的平衡二叉树能保证时间复杂度不变但它本质上仍然是个二叉树就导致每个节点最多只有2个子节点从而导致树的高度不断堆加增加磁盘I/O次数影响数据查询效率。基于此B树就解决了二叉树的节点问题它不限制一个节点只能有2个子节点而是允许有多个子节点从而降低了树本身的高度进而降低了数据库磁盘I/O的操作次数相比二叉树提高了数据查询的效率。虽然B树解决了二叉树带来的高度问题但是B树的每个节点都会存储数据这就会导致在进行数据库查询的时候会读取到无用的索引数据进而导致需要更多的磁盘I/O操作读取到有用的索引数据。这样不仅降低了数据查询的性能而且还对内存不友好因为那些无效数据也会被加载进内存。1.2.5 B树基于上述情况B树出现了B树就是对B树的一个升级。相比B树B树的非叶子节点只会存放索引数据只有叶子结点才会存放实际数据也就是索引记录并且所有的索引记录都会出现在叶子结点构成一个有序链表。这就解决了原本B树存在的无效数据记录会过多占用内存资源的问题。而MySQL中索引的数据结构就是采用了B树。在B树中非叶子节点只会存放索引数据这就意味着在数据扫描过程中即便扫描到无效数据也只是把索引数据加载进内存大大降低了对内存资源的占用。并且B树的叶子结点是通过链表来连接的这也就意味着在数据查询中不需要每一次都从根节点重新查找大大提高了查询的效率。总而言之B树通过减少磁盘I/O中无效数据的载入更少的磁盘I/O操作的优势获得了MySQL的青睐并将其作为索引的数据结构。2. 索引的使用2.1 索引的使用场景索引的存在提高了数据库的查询速度基于这个要点接下来分析一下索引的使用场景。什么时候需要创建索引1、字段具有唯一性限制的比如商品编码。2、经常需要作为where查询条件的字段这样可以提高表的查询效率如果查询字段不止一个可以考虑建立联合索引。3、经常用于group by 和 order by的字段这样就省去了排序的操作因为建立索引之后在底层是已经排序好了。什么时候不需要创建索引1、表中数据太少的情况下不需要创建索引。比如10条、50条这种情况这时候走全表扫描效率还比走索引匹配要高。2、经常需要做更新的字段不需要创建索引。因为一个字段经常更改数据库去维护索引结构也需要成本所以这种情况不考虑建立索引。3、存放大量重复数据的字段不需要创建索引。比如性别字段基本就是男和女数据量非常平均那这时候如果走索引查询这个过程除了在性别字段这个索引树上查一遍还要回表去主键索引在查一遍。这个性能开销甚至还不如直接走全表扫描。4、where条件里用不到的字段也不加。因为索引本身也是占磁盘空间的既然都用不到那这个索引也没必要创建出来。2.2 索引优化上面介绍了索引的常见使用场景接下来介绍常见的索引优化方案。前缀索引优化这种方案通过截取某个字段中字符串的前几位建立索引从而降低索引字段大小增加索引查询效率。比如把电话号码这种大字符串字段作为索引如果按照原串长度作为索引那索引字段就太大了所以通常截取前几位作为索引字段存进去。不过这样也存在缺陷因为索引字段不全所以就必须走一次回表去获取完整数据。由于这个原因所以前缀索引也不适用于order by 和 group by因为排序的依旧并不完整。覆盖索引优化这种方案是指查询后返回的字段全是索引字段不需要通过回表操作去获取所有完整信息降低了回表带来的I/O操作。比如把age字段作为索引然后查询也只查询age这种时候不需要回表直接能在索引树上查询到记录 如果要查询多个字段通常可以把这多个字段建立为联合索引然后直接查询。主键索引采用自增模式这种方案也好理解因为主键索引在Btree的叶子结点直接存放的就是索引数据行数据把主键索引设为自增的数据的存放也就是按顺序添加对内存空间的使用非常友好查询效率也快维护成本也低。索引列设置非空约束索引的目的是为了提高数据的查询效率基于此在创建索引的时候需要去对索引字段设置非空约束。因为null这个字段本身无意义如果把null作为索引数据存放有点浪费内存空间了所以为了更好的使用索引最好给索引列加非空约束。2.3 索引失效上面介绍了索引的使用场景但是索引创建成功了就一定会被成功使用吗 索引它也存在失效的情况的在索引失效的情况下数据库会放弃使用索引走全表扫描这样就大幅降低了查询效率。所以我们要尽可能避免索引失效的情况。常见的索引失效的情况1、查询条件中索引列参与运算、进行函数操作、类型转换都会导致索引失效。2、索引字段使用like进行模糊匹配时在like %… 和 like %…%这种左模糊匹配和左右模糊匹配的方式下索引会失效。但是进行右模糊匹配的情况下索引会正常使用。3、where语句中如果or条件的左右中有一个不是索引字段索引也会失效、4、如果索引字段是字符串类型在查询时未加单引号也会导致索引失效。举个例子select*fromuserwherephone13333556phone字段为字符串类型但此时索引失效。因为MySQL 会进行隐式的数据类型转换在遇到字符串和数字比较的时候会自动把字符串转为数字然后再进行比较。对于上述的案例phone字段因为未加单引号数据库会将这个字符串字段隐式转化为整数类型也就是说在这个过程中phone这个索引字段参与了函数操作而前面也介绍过如果索引字段参与函数操作会导致索引失效。5、在使用联合索引时未遵循最左匹配原则时索引也会失效。比如创建一个a , b, c)的联合索引这时候索引在B树中的结构就是按a、b、c顺序排列也就是说在一个节点里按顺序存放了a、b、c三组字段数据后续进行查询时也是按照字段顺序分别进行匹配where顺序不重要主要和创建索引时的顺序有关。也就是说在联合索引的情况下数据是按照索引第一列排序在这个例子里就是先匹配a第一列相同才会走第二列如果跳过第一列数据会导致数据库查找不到指定索引数据从而放弃使用索引于是索引失效。2.4 索引的优缺点索引也不是万能的它有长处那它也存在弊端下面介绍一下索引的优缺点。优点1、提高了数据查询的效率大幅降低了磁盘操作I/O的成本。2、索引本身是有序的所以也降低了数据排序的成本。缺点1、索引本质上是拿空间换时间所以它也会占据磁盘空间如果索引数量多起来占据的磁盘空间也越大。2、索引的创建和维护会耗费时间索引越大时间越长。3、索引提高了查询效率但相对的对数据的更新就没那么友好了因为每次更新数据都要重新维护B树的结构很耗费性能。