MySQL高阶之索引篇

select语句流程

索引

定义

一句话理解 高效获取数据的一种数据结构。

优缺点：

• 优点：效率高
• 缺点：索引会占据磁盘空间。索引太多会导致降低更新表的效率 索引也会随着数据的改变而改变

分类

• 主键索引(聚集索引)：主键索引和数据存放在一起 因此非常快。Innodb如果表没有主键，也会自动生成rowid创建主键索引
• 非聚集索引：通过非聚集索引找到id，通过非聚集索引可以找到主键id 通过id找到数据。除了主键之外的普通字段上创建的索引。也叫作辅助索引。非主键索引
• 单列索引：索引上只有一个列
• 组合索引：使用两个或以上的字段创建的索引

索引的数据结构

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html 可以演示不同的数据结构插入数据的不同展示

hash:

select * from user where id= 1等值查询 时间复杂度O(1) 缺点:仅仅满足等值查询

二叉排序树(二叉查找树)

select * from user where id >100 and id<200 范围查询 数据类型： 时间复杂度：O(log2n) 但是依赖根节点的数值(第一个插入进去的元素作为根节点)

问题:

• 1.第一个插入进去的元素作为根节点 不能平衡数据
• 2.如图 如果运气不好需要找38的元素 需要IO6次性能底下

平衡二叉查找树

特点：时刻保持二叉树的左右子树的高度差小于等于1，如果大于1会进行左旋或者右旋从而达到保持平衡。可以保持时间复杂度为O(log2n)

问题：数据量大的情况下，从根节点便利到叶子结点也需要很多次的磁盘IO，因为树的高度比较高。
mysql每个数据页是16k,最好每个节点可以存放多个数据。

1和3元素

分裂:

存在问题:如果我的表里有10w条数据,即使分裂过后,树的高速依然很高 性能低下

b-tree(b树 多叉平衡树)

特点：

• 1.每个节点保存多个元素，每个节点保存的元素的个数上限叫做度(一个节点只存放x个元素，达到了就会进行分裂)。
• 2.每个元素的左右两侧各有一个指针指向下一个节点，左边节点小于根节点，右边节点大于根节点

缺点：

• 1.中间节点中每个元素都包含数据，节点的度取决于数据行的大小，数据页大小固定是16k，如果数据行太大，度就变小了，此时树的高度同样也会变高，此时就会产生多次IO磁盘
• 2.范围查询时效率很低下 需要来回折腾 比如需要查找大于10小于100的数据,那么找到大于10的数据之后,还需要返回根节点重新遍历查找,范围查询的效率有待提高
  

B+tree

特点：

• 1.中间节点不保存数据，只包含主键信息。所有的数据存放到叶子节点
• 2.每个叶子节点 通过双向指针连接

好处：

• 1.等值查询效率很高
• 2.范围查询效率很高 不需要经过非叶子节点
  

B树和B+树最大的区别就是非叶子节点不再存储数据,意味着什么?我这一个磁盘块16KB,可以全部用来存放索引,如果运气好建立了主键索引,假设16KB可以存储1000个索引,那么插入一百万条数据,树的高度也只有2,也就是说只需要2次IO就能查询到所有的任何一条数据.

MySql索引的存储形式

MyIsam引擎

• 索引放到.MYI文件中
• 数据放到.MYD文件中

1.主键索引

• b+tree数据结构
• 叶子节点中存储的是 主键信息和偏移量 通过偏移量找到对应的数据

2.主键索引和辅助索引没有什么区别
3.查询一条数据->找到数据行的偏移量->通过偏移量找到数据行 需要IO次数:n+1次

InnoDB引擎

.ibd文件保存了索引和数据信息，保存在一起

1.主键索引

• b+tree数据结构
• 叶子节点就是记录行。根据主键索引查询效率高于Myisam引擎，少一次磁盘IO。主键索引和数据行都是存在一起的，因此叫做聚集索引。

2.辅助索引

• 叶子节点保存的是主键，如果要查询数据，需要根据辅助索引找到对应的主键，然后通过主键查询找到该索引对应的数据行，这个查询主键索引的过程叫做回表。
• 如果使用辅助索引来查询的话效率要比MyIsam引擎要低

主键索引:

辅助索引(需要回表):

组合索引

select * from t_multiple_index where a=13 and b=16 and c=4; 

• 1. 先在索引树中从根节点开始检索，将根节点加载到内存，先比较a列，a=14，14>13,走左路。（1次磁盘
  IO）
• 2. 将左子树节点加载到内存中，先比较a列，a=13，比较b列b=14，14<16，走右路，向下检索。（1次磁盘
  IO）
• 3. 达到叶节点，将节点加载到内存中从前往后遍历比较。（1次磁盘IO）
  第一项（13,14,3,id=4）：先比较a列，a=13，比较b列b=14，b??16不符合要求，丢弃。
  第二项（13,14,4,id=1）：一样的比较方式，a=13，b=16，c=4 满足筛选条件。取出索引data值即主键id=1，再去主键索引树中检索id=1的数据放入结果集中。（回表：3次磁盘IO）
  第三项（13,14,5,id=3）：a=13，b=16，c??4 不符合要求，丢弃。查询结束。
• 4.最后得到1条符合筛选条件，将查询结果集返给客户端。

索引使用口诀:

• 全值匹配我最爱 最左前缀要遵守：(所以当我们使用 where a=13 and b=16 and c=4去查询数据的时候，B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列，B+树就不知道第一步应该从哪个节点查起。)需要注意最左前缀原则 编写sql语句时，查询条件的顺序应该和索引创建的顺序一致。
  使用返回查询或不等于查询条件会大只后面的条件不能应用索引

• 带头大哥不能死 中间兄弟不能断
  查询条件中的第一个字段必须存在，如果不存在不能应用组合索引。
  中间字段缺失，会导致后面的字段无法应用索引

• **索引列上不计算 范围之后全失效
  查询条件中字段使用函数会导致索引失效
  隐式数据类型转换也会导致索引失效
  范围查询后面的字段会导致索引失效**

• like百分号写最右 覆盖索引不写
  like模糊查询是注意：‘张%’**

• 不等空值还有OR 索引失效要少用
  is null 可以应用索引
  is not null导致索引失效

组合索引的创建原则

• 1.频繁出现在where条件的列
• 2.order by 和group by的列 可以按照顺序创建组合索引
  select * from user where id = 2 and b > 3 order by c
  这个语句可以创建 id,b组合索引 和id,c组合索引
• 3.出现在select语句中的字段可以考虑加到组合索引中

覆盖索引

在select后吧查询的字段列出，如果查询的字段在组合索引中包含，此时不需要回表，直接返回数据。

#表t_multiple_index，组合索引idx_abc(a,b,c)的叶子节点中包含(a,b,c,id)四列的值，对于以下查询语句
select a from t_multiple_index where a=13 and b=16;
select a,b from t_multiple_index where a=13 and b=16;
select a,b,c from t_multiple_index where a=13 and b=16;
select a,b,c,id from t_multiple_index where a=13 and b=16;

前面提到首选需要通过赋值索引找到主键值,然后进行回表找到数据行.
如果我们select中的列,可以在索引辅助书上全部获取,或者说我们建立的idx_abc组合索引已经覆盖了我们的查询列,此时mysql就不需要进行回表了,这个现象称之为索引覆盖.看到using index代表使用到了索引覆盖

索引条件下推ICP.

 mysql5.6之后才支持
show baribales like 'optimizer_switch'
在这种情况下可以使用 原来无法应用索引的条件，可以在引擎层进一步做筛选，向server层返回更少的结果集，提高查询效率
select * from user where a=1 and b> 13 and c=2
关闭：using where
开启：using index condition
看到 using index condition代表这是索引下推生效了 使用了

索引优化建议：

• 1.表记录过少不需要创建索引
• 2.一个表的索引个数不能太多
• 3.频繁更新的字段不建议创建索引会带来性能开销
• 4.区分度低的字段不建议索引 男女字段
• 5.在InnoDB引擎中推荐使用自增的长整型，避免使用很长的字段 否则会导致度变小 分叉变少 树变高
• 6.不建议使用无序的值作为主键。例如身份证 UUID
• 7.尽可能选择组合索引