【MySQL】索引

记录MySQL学习笔记，大部分图片来自黑马程序员MySQL教程。

概述

索引是MySQL高效获取数据的数据结构，这些数据结构利用特定查找算法引用（指向）数据。

优缺点：

优点	缺点
提高数据检索效率，降低IO成本	需要占用空间
索引列对数据进行排序，降低了数据排序的成本，减少CPU消耗	因为更新表的时候也要更新索引，所以降低了更新表的速度

但是现在磁盘很便宜，且正常情况下增删改数据库的频率很小，所以以上劣势可以忽略。

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要的几种结构如下所示。

索引结构	描述
B+Tree索引（重点）	最常见
Hash索引	底层数据结构用哈希表实现，不支持范围查询
R-Tree（空间索引）	是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型
Full-text（全文索引）	一种通过建立倒排索引，快速匹配文档的索引

B+Tree

Q：为什么使用B+树？
A：针对数据库数据量大且其他树形结构的闭端，权衡下采用B+树。

图片来自黑马程序员MySQL教程。

中间元素向上分裂

所有元素都会出现在叶子节点，叶子节点形成一个单向链表。

为什么InnoDB使用B+Tree索引结构？

• 相较于二叉树 (红黑树) ，层级更少，搜索效率高。
• 对于B树，无论叶子节点还是非叶子节点都会保存数据，而每一个节点都存放在一个页中（页的大小是固定的），这样导致一页中存储的键值减少，指针也跟着减少，所以保存大量数据时，树的高度就会增加，查找性能降低。
• B+树叶子节点的双向链表便于范围搜索和排序。
• 相较于哈希索引，B+树支持范围匹配和排序操作。

索引分类

聚集索引叶子节点存放数一行数据，二级索引叶子节点存放主键值。
回表查询：先在二级索引当中找到主键值，再根据主键值到聚集索引中找到这一行数据。

即使存储两千多万行数据，B+树的高度也只有3，所以B+树的效率很高，如果B+树的高度要大于3了，就会用分表解决。

索引语法

SQL性能分析

1、查看执行频次

七个下划线，恰好可以代表_delete、_insert、_select、_update。

2、慢查询日志

通过指令查看

show variables like 'slow_%';

3、profile详情

4、explain执行计划

尽量优化type。

但仅当查询不需要访问表时才会出现NULL，只访问系统表才能达到system，所以尽量往const上优化（即使用主键或者唯一索引），当使用非唯一索引的时候 type 是ref，all代表全表扫描，性能最差，index代表用了索引，但遍历了整个索引，性能也不高。

索引使用

最左前缀法则

• 若跳过了某一列，则从这一列开始，之后的索引失效。
• 每一列的使用顺序不影响结果。

索引失效情况

1、范围查询

• 解决方法：将 < , > 改为 <= , >=

2、索引列运算

3、字符串不加单引号

4、模糊查询

like '计算机科学%' -- 不失效

like '%与技术' -- 失效 

like '%与%' -- 失效

5、or连接的条件

用or分隔开的条件，如果or之前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会用到。

age没有单独建立索引，并且也不是联合索引的第一个，所以查询的时候没有索引。

6、数据分布

如果MySQL评估使用索引比不使用还要慢，则不使用索引。

• is null 走不走索引取决于查询的字段中null的个数占总行数的比例。

SQL提示

• 用use指定，MySQL会自己评估可能不用，用force指定，系统则只能用这个索引。

覆盖索引

• 找gender的时候需要回表。
• 使用select * 很容易就会回表查询。

• 对username和password建立联合索引，username是第一个，那么查询的时候走联合查询，叶子节点上就是主键id，满足覆盖索引。

前缀索引

• 不可能返回一个前缀，所以查询前缀肯定要回表。

单列索引和联合索引

索引设计原则

• 一张表数据量超过100万就算大，如果不怎么查询，建立索引也没用；

总结