Mysql索引

MySql索引

1、索引介绍

1.1 什么是索引？

MYSQL官方对于索引的定义：索引是帮助Mysql 高效获取数据的数据结构

Mysql在存储数据之外，数据库系统中还维护者满足特定查找算法的数据结构，这些数据结构以某种引用（指向）表中的数据，这样我们就可以通过数据结构上实现的高级查找算法来快速找到我们想要的数据。而这种数据结构就是索引。

简单理解为：“排好序的可以快速查找数据的数据结构”

1.2 索引数据结果

下图就是已汇总可能的二叉树的索引方式：

二叉树中的节点中以key：value存储，key存值，vlaue存的是数据在数据库中的地址

二叉树弊端：当极端情况下，数据递增插入时，会一直向右插入，形成链表，查询效率大幅下降

所以Mysql中常用数据结构有BTree索引（Myisam普通索引），B+Tree索引（Innodb普通索引），Hash索引（memory 存储引擎）等等。

当前InnoDB所用的数据结构就是B+tree

为什么选用B+树？

1、 B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

2、B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

3、B+树更便于遍历：由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

4、B+树更适合基于范围的查询：B树在提高了IO性能的同时并没有解决元素遍历时效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。

1.3 索引优势

提高数据检索的效率，降低数据库的成本。

通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗

1.4 索引劣势

索引实际上也是一张表，保存了主键和索引的字段，并且指向实体表的记录，所以索引肯定也是要占用空间的。在索引大大提高查询速度的同时，却会降低表的更新速度，在进行增删改时，因为更新意味着数据更新，同时还需要调整数据对应的索引文件进行更新

1.5 索引的使用场景

哪些情况下需要创建索引：

1. 主键自动创建唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，外键关系建立索引
4. 多字段查询下倾向创建组合索引
5. 查询中排序的字段，排序字段如果通过索引去访问将大大提高排序的速度
6. 查询中统计或者分组字段

那些情况下不推荐建立索引：

1. 表的记录太少
2. 经常增删改的表
3. where条件经常用不到的字段

1.6 索引分类

1. 聚簇索引

   1）主键索引

   表中的列设定为主键后，数据库会自动创建主键索引

   单独创建和删除主键索引：

   alter table 表名 add primary key
   alter table 表名 drop primary key
   

   

   • B+数叶子结点存储的是完整的记录（所以也被称为聚簇索引）
   • 无论是业内还是页间，都按主键升序排序
     • 业内按主键大小，组成一个有序的单向链表
     • 无论是目录项所在的页还是数据页，相同层级的页之间组成一个双向链表

2. 二级索引

   1）唯一索引

   表中的数据创建了唯一约束时，数据库会自动创建唯一索引

   单独创建和删除唯一的索引语法：

   alter table 表名 add unique 索引名（字段）
   或者
   create index 索引名 on 表名
   drop index 索引名 on 表名
   

   2）普通索引

   alter table 表名 add index 索引名（字段）
   或者
   create index 索引名 on 表名
   drop index 索引名 on 表名
   

   

   叶子结点存储的是该列对应的索引值、主键id，非叶子节点只有索引值，先找主键id再通过主键id查，这也被称为回表查询，被称为二级索引的原因

   3）复合索引

   即一个索引包含多个列：

   1. 建立表时可随表一起建立复合索引

   2. 单独创建和删除复合索引

      create index 索引名 on 表名（字段1，字段2）
      drop index 索引名 on 表名（字段1，字段2）
      

可以看到，复合索引先通过最左边的列排序，最左边相同，再根据下一个字段排序，这也就是为什么我们说复合索引必须遵守最左匹配原则的根本所在

叶子结点除了索引对应数据外还存在了主键id，最终也是通过回表查询数据。

对3000000数据的查询，在不通过自创建的主键索引查询，而用没有创建索引的pname查询，索引建立前后的查询时间差距十分巨大

2、性能分析

2.1 Mysql常见瓶颈

sql中对于大量数据进行比较、关联、排序、分组时的CPU瓶颈。

实力内存满足不了缓存数据或排序等需要，导致产生大量的物理IO，查询数据是扫描多行数据导致查询效率低

2.2 Explain

使用EXPLAIN关键字可以模拟优化器执行SQL 查询语句，从而知道MYSQL 是如何处理SQL 语句的。可以用来分析查询语句或是表的结构的性能瓶颈，他的作用有：

• 表的读取顺序
• 那些索引可以使用
• 数据读取操作被实际使用
• 表之间的引用
• 每张表有多少关键字使用起来比较简单

EXPLAIN 关键字使用起来比较简单：explain + sql 语句

2.3 Explain使用

2.3.1 id

select 查询的序列号，表示查询中执行select 子句或操作表的顺序。

• id相同，执行顺序从上到下
• id不同，如果是子查询，id的序号会递增，id值越大表示优先级越高，则先被执行
• id相同和不同的都存在，可理解为id相同的为一组，不同的为一组，所有组中，id值越大，优先级越高越先执行

2.3.2 select_type

查询的类型，常见有：

• SIMPLE：简单的 select 查询，查询中不包含子查询或UNION

• PRIMARY：查询中若包含任何复杂的子查询，最外层查询则被标记为Primary

  primary

• DERIVED：在FROM列表中包含的子查询被标记为DERIVED，Mysql会递归执行这些子查询，把结果放在零时表里

  derived

• SUBQUERY：在SELECT或WHERE列表中包含了子查询

  subquery

2.3.3 table

表示该条数据来自哪个表

2.3.4 type

访问类型

• System：表只有一条数据，这是const的特例，一般不会出现。

• Const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，Mysql 就能将该查询转换为一个常量。

  理解：就是条件为字段==XXX，满足这个字段的行只有一个，且这个字段设置了索引，就是const

• eq_ref：唯一性索引扫描，对于每一个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

  select * from t1 join t2 on t1.id = t2.id
  注：t1,t2表中id都为其主键
  执行过程，先把t1中id都取出，type为All，t2的id对t1的id为唯一对比，type为eq_ref
  

​ 理解：连表，连表条件为索引字段且是唯一字段

• ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而，他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

  理解：就是条件为字段==XXX，满足这个字段的行比较多，且这个字段设置了索引，就是ref

• Range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了那个索引，一般就是在你的where语句中出现了between、<、>、in 等查询这种范围扫描索引扫描比全盘扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

  理解：范围查询，条件为索引字段

• Index：Full index Scan，index与ALL区别于index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小，也就是说虽然all 和 Index都是全读表，但 index 是从索引中读取的，而all是从硬盘中去读的。

  理解：查询所有，所以字段都是索引

• ALL：Full Table Scan，将遍历全表 以找到匹配的行。

从最好到最差依次是：system>const>eq_ref>ref>range>index>All。一般来说，最好能保证查询到range级别，最好达到ref

2.3.5 possible_key

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上如果存在索引，则该索引会被列出来但不一定会被使用上

2.3.6 key

查询过程中使用到的索引

2.3.7 ref

显示索引的那一列被引用了。那些列或常量被用于查找索引列上的值

2.3.8 rows

rows列显示Mysql认为它执行查询时必须检查的行数，通常情况下越少越好

2.3.9 extra

一些常见的重要的额外信息：

• Using filesort：Mysql无法利用索引完成的排序操作称作为”文件排序“。

• Using temporary：Mysql在对查询结果排序时使用临时表 ，常见于排序order by 和分组查询 group by

• Using index：表示索引被用来执行索引键值的查找，避免访问了表的数据行，效率不错。

• Using where：表示使用了where过滤

3、查询优化

3.1 索引失效

1. 最佳左前缀法则：如果索引了很多列，要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列

   理解：针对组合索引，你查询条件必须满足建立索引时字段从左到右的顺序，顺序错或混乱，索引失效，注意使用and时若组合索引都用上了，那么顺序无关，它自己会调整

   alter table user add index userRole_userName_gender(userRole,userName,gender)
   EXPLAIN SELECT * from user where userRole="user" and userName="111" and gender="11" //用上了索引
   EXPLAIN SELECT * from user where gender="11"
   //没用上了索引
   

2. 如果对索引列上做任何计算、函数操作，会导致索引失效而转向全表扫描

3. 存储引擎不能使用索引范围中带范围条件字段的右边的列

   alter table user add index userRole_age_gender(userRole,age,gender)
   EXPLAIN SELECT * from user where userRole="user" and age=111
   EXPLAIN SELECT * from user where userRole="user" and age>11 and gender="11" //和上面一句sql的key_len一致，说明>条件的右边gender的索引没用上
   

4. Mysql在使用不等于条件时，不能使用索引

5. is null可以用索引，is not null不可以用索引

   alter table user add index userAvatar(userAvatar)
   EXPLAIN SELECT * from user where userAvatar is  null//可以用索引
   

6. like以通配符开头会使得索引失效导致全表扫描

7. 字符串不加单引号索引会失效

8. 使用or链接时会导致索引失效

3.2 排序优化

1. 尽量避免使用Using FileSort方式排序
2. order by 语句使用索引最前列或使用where子句与order by 子句条件组合满足索引最左前列
3. where子句中如果出现范围查询会导致order by 索引失效

分组排序类似

3.3 关联查询优化

内连接时，mysql会自动把小结果集的选为驱动表，所以大表的字段最好加上索引。左外连接时，左表会全表扫描，所以右边大表字段最好加上索引，右外连接同理，我们最好保证被驱动表（一般为大表）上的字段建立了索引。

4、慢查询日志

4.1 慢查询日志简介

Mysql的慢查询日志是Mysql提供的一种日志记录，他用来记录在Mysql中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中，可以由它来查看那些SQL超出我们最大忍耐时间值。

4.2 慢查询日志使用

默认情况下，Mysql并没有开启慢查询日志，需要手动设置参数。

查看是否开启：show variables like ‘%slow_query_log%’;

开启日志：set global slow_query_log = 1

设置时间：set global long_query_time = 1

查看设置时间：how variables like ‘%lobal long_query_time%’;

注意：非调优情况下，不建议开启慢查询日志，因为慢查询写入文件会带来一定的性能影响。