MySQL-索引

MYSQL之索引

本章包含索引概述，索引结构，索引语法，索引分类，SQL性能分析，索引使用，索引设计原则！

• 索引概述

1. 介绍
   （1）索引(index)是帮助MySQL高效获取数据的数据结构(有序)。
   （2）在数据之外，数据库系统还维护着满足特定查找算法的数据结构。这些数据结构以某种方式引用(指向)数据。
   （3）这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
2. 优缺点
   优点：
   （1）提高数据检索的效率，降低数据库的IO成本。
   （2）通过索引列对数据进行排序，降低了数据的排序成本，减低cpu的消耗。
   缺点：
   （1）索引列也是要占用空间。
   （2）索引大大提高了查询效率，同时却降低了更新表的速度，因为在进行insert,update,delete时还需要维护索引。

• 索引的数据结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构。主要分为4种。


我们平常所说的索引，一般指的是B+Tree索引。

二叉树数据结构

二叉树的缺点：
（1）顺序插入时，会形成一个链表，查询速度大大降低。
（2）由于一个节点最多只能有两个子节点，大数据量时，树的层级较深，检索速度慢。

红黑树数据结构

红黑树是自平衡二叉树：
（1）解决了形成链表的问题。
（2）本质上是二叉树，大数据量时，树的层级较深，检索速度慢。

B-Tree数据结构

B-Tree是多路平衡查找树。

动态演示链接：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
B-Tree特点，以5阶树为例
（1）5阶的树，每个节点最多存储4个key,5个指针。
（2）插入数据时，每个节点在key等于5时，发生裂变，中间元素向上抽取。以此类推

B-Tree数据结构

B+Tree是特殊的B-Tree树。

相对于B-Tree的区别
（1）所有的数据都会出现在叶子节点
（2）叶子节点形成一个单向链表。

MySQL中B+Tree索引结构

1. MySQL中B+Tree索引结构的特点：
   MySQL中的B+Tree索引数据结构对经典的B+Tree做了优化，增加了一个指向相邻叶子节点的链表指针（双向链表），就形成了带有顺序指针的B+Tree,提高了区间访问的性能。
   

MySQL中Hash索引结构

Hash索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果多个键值，映射到同一个槽位上，他们就产生了哈希冲突，可以通过链表来解决。

1. Hash索引的特点（优缺点）：
   （1）Hash索引只能用于对等比较(=,in),不支持范围查询(between,<,>)。
   （2）无法利用Hash索引完成排序操作。
   （3）查询效率高，通常只需要一次检索就可以了，效率通常要高于B+Tree索引。
2. 存储引擎的支持情况
   （1）Memory存储引擎支持Hash索引。
   （2）InnoDB存储引擎中，有自适应hash功能，MySQL会根据查询条件在指定的条件下自动将B+Tree索引转换为Hash索引。

• 索引的分类

索引从逻辑上来分分为四种

注意点：
（1）主键索引在创建表时，指定主键时自动创建，每张表只有一个主键索引。
（2）唯一索引在为某一列添加唯一约束时自动添加唯一索引，可以有多个。

索引从物理存储形式上来分分为两种

注意点：
（1）每张表必须有一个，而且只能有一个聚集索引。
（2）聚集索引是将行数据和索引放在了一起保存，而二级索引将数据与索引分开存储。
（3）聚集索引的叶子节点保存了行数据，二级索引的叶子节点保存的是对应的主键和该索引列的值。
聚集索引的选取规则：
（1）表如果存在主键，主键索引默认就是聚集索引。
（2）如果没有主键，则选择第一个唯一索引作为聚集索引。
（3）如果没有主键索引和唯一索引，InnoDB存储引擎会自动生成一个rowid作为隐藏的聚集索引。多加了一列。

回表查询

select * from user where name ="kit";的执行过程。
（1）name字段有索引，是二级索引，先在二级索引中找到kit对应的主键。
（2）在根据主键在聚集索引(主键索引)中找到该主键对应的行数据。

• 索引的语法

创建索引

点击查看代码

create [unique|fulltext] index index_name on table_name (index_col_name,...);

细节：
（1）不指定是唯一索引还是全文索引，创建的是常规索引。
（2）一个索引可以绑定多个字段，这样的索引叫做聚合索引/组合索引，绑定单个字段的索引叫做单列索引。

查看索引

点击查看代码

show index from table_name;

删除索引

点击查看代码

drop index index_name on table_name;

练习题

• SQL性能分析工具介绍

我们经常优化的是查询语句。

SQL执行频率

点击查看代码

show global status like 'Com_______';//7个下划线

查看当前MySQL服务的insert,update,select,delete语句的执行情况。

慢查询日志工具

1. 介绍
   慢查询日志记录了所有执行时间超过指定参数（lone_query_time,单位：秒，默认10秒）的所有SQL语句的日志。默认是关闭的。
2. 开启慢查询日志
   （1） 查看慢查询日志的开关

点击查看代码

show variables like 'slow_query_log';
show variables like 'slow_query_log_file';
show variables like 'long_query_time';

（2）配置开启慢查询日志

3. 慢查询日志的细节
只有超出了指定时间的SQL才会被记录在慢查询日志中。

profile详情工具

1. 介绍
   show profiles能够在做SQL优化时帮助我们了解时间都消耗到哪里去了。
   通过have_profiling参数，能够看到当前MySQL是否支持profile操作。

点击查看代码

select @@have_profiling;//查看profile的开关

2. 开启profile开关

点击查看代码

set profiling = 1;//可以设置global/session级别

3. 查看SQL的耗时基本情况
   

explain执行计划工具

1. 介绍
   explain或者desc命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。
2. 语法

点击查看代码

explain select 字段列表 from 表名 where 条件;//desc效果一样

执行代码: explain select * from tb_order where id =101;
结果：

3. 执行结果各字段详解
（1）id:select查询的序列号，表示查询过程中操作表的顺序。（id相同，执行顺序从上到下；id不同，值越大，越先执行）
（2）type:表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all。
const:使用唯一索引和主键索引时，ref:使用普通索引时。
（3）possible_keys:表示可能用到这张表的索引，一个或多个
（4）key:实际使用的索引，为null,表示没有使用索引
（5）key_len:表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不
损失精度的前提下，长度越短越好。
（6）rows:MySQL认为必须要执行查询的行数，在innoDB引擎的表中，是一个估计值，可能不准确。
（7）filtered:表示返回结果的行数占需读取行数的百分比，值越大越好。

• 索引的使用原则

验证索引查询效率

大数据量下，有索引和没索引的查询效率时非常明显的。

最左前缀法则

1. 介绍
   如果索引了多列（联合索引），要遵守最左前缀法则。指的是查询从索引的最左列开始，并且不跳过索引中的列。
2. 索引失效情况
   （1）如果不使用最左列查询，不走索引。与编写顺序无关，只要使用即可。
   （2）如果跳跃某一列，索引将部分失效（后面的字段索引失效）

点击查看代码

//建立联合索引，绑定字段的顺序就是索引的顺序，可以查看索引情况，根据索引字段长度可以得知哪一列的索引失效了。
create index idx_user_pro_age_sta on tb_user(profession,age,status);
//查询语句的失效情况
explain select * from tb_user where profession='软件工程' and age =31 and status='0';//索引不会失效
explain select * from tb_user where profession='软件工程' and age =31 ;//索引不会失效
explain select * from tb_user where profession='软件工程';//索引不会失效
explain select * from tb_user where age =31 and status='0';//索引失效
explain select * from tb_user where status='0';//索引失效
//前三条索引不会失效，后两条不符合最左前缀原则（没有使用最左列pro）导致失效

范围查询原则

1. 介绍
   联合索引中，出现范围查询(<,>)时，范围查询右侧的列索引失效
2. 索引失效情况

点击查看代码

explain select * from tb_user where profession='软件工程' and age >30 and status=0;//右侧的索引列会失效，status字段的索引失效。
explain select * from tb_user where profession='软件工程' and age >=30 and status=0;//业务逻辑允许的情况下，>= 可以保证部分索引不失效。

索引列参与运算

细节：不要在索引列上进行运算操作，索引会失效

点击查看代码

//phone字段有索引，截取最后两位函数运算。索引失效
explain select * from tb_user where substring(phone,10,2)='15';

字符串类型不加单引号

细节：字符串类型不加单引号，该索引字段会失效

点击查看代码

//phone,status为varchar类型
explain select * from tb_user where phone ='18626297115';//索引生效
explain select * from tb_user where phone =18626297115;//索引失效
explain select * from tb_user where profession='软件工程' and age =31 and status='0';//三个索引列不会失效
explain select * from tb_user where profession='软件工程' and age =31 and status=0;//status索引列失效,前两个索引列不失效。

模糊查询情况

细节：如果仅仅是尾部模糊匹配，索引不失效。如果是头部模糊匹配，索引会失效。

or连接的条件情况

细节：
（1）用or分割开的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及的索引都不会被用到。

点击查看代码

explain select * from tb_user where id =10 or age =23;//索引失效

（2）由于age没有索引，即使id有索引，索引也会失效。所以需要针对age字段建立索引。

数据分布情况

细节：
（1）如果MySQL评估使用索引比全表更慢，则不使用索引.
(2)查询某个字段为空和不为空，走不走索引看的是数据的分布情况。不是固定的。

SQL提示

1. 介绍
   SQL提示，是优化数据库的一个重要手段，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
2. 语法

点击查看代码

//use index：建议使用
explain select * from tb_user use index(idx_user_pro) where profession='软件工程';
//ignore index：不使用
explain select * from tb_user ignore index(idx_user_pro) where profession='软件工程';
//force index:强制使用
explain select * from tb_user force index(idx_user_pro) where profession='软件工程';

覆盖索引和回表查询

1. 介绍
   尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能找到），减少select *。
2. 查看SQL执行计划最后一个字段Extra的两个值：
   （1）using index condition:查询使用了索引，但是返回字段需要回表查询数据
   （2）using where,using index:查询使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据。
3. 示例

点击查看代码

//前三条是覆盖索引，最后一条需要回表查询
//因为使用联合索引（二级索引）来查询，就可以拿到id,profession,age,status四个字段的值，而name字段需要查询聚集索引才能查到
explain select id,profession from tb_user where profession='软件工程' and age =31 and status='0';
explain select id,profession,age from tb_user where profession='软件工程' and age =31 and status='0';
explain select id,profession,age,status from tb_user where profession='软件工程' and age =31 and status='0';
explain select id,profession,age,status,name from tb_user where profession='软件工程' and age =31 and status='0';

4. 练习题
   select * from tb_user where id =2;//直接走聚集索引。
   select * from tb_user where name = 'jack';//需要回表查询。
   两个SQL的效率更高？

前缀索引

1. 介绍
   当字段类型为字符串(varchar,text,longtext等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量磁盘IO,影响查询效率。此时可以只将字符串的一部分前缀，建立索引。这样可以大大节约索引空间，从而提高索引效率。
2. 语法

点击查看代码

create index idx_xxx on table_name(column(n))；//n为截取的个数

3. 前缀长度的选择
   可以根据索引的选择性来决定，选择性就是指不重复的索引值和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

点击查看代码

//计算选择性
select count(distinct email) from tb_user;//计算email字段不重复有多少行
select count(distinct email) /count(*) from tb_user;//计算email字段不重复的行数的选择性
select count(distinct substring(email,1,5) ) /count(*) from tb_user;//计算email字段不重复的行的前五位的选择性

单例索引与联合索引

1. 介绍
   单例索引：一个索引只包含单个列。
   联合索引：一个索引包含了多个列。建立索引时要考虑字段的顺序。最左前缀原则。
   在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
2. 场景说明

点击查看代码

//phone和name都是单列索引，此时查询之后走phone的索引，而要查询name字段就需要回表查询
explain select id,phone,name from tb_user where phone ='18636396178' and name ='jack';
//如果给phone和name建立联合索引，且指定使用联合索引，则不需要回表查询。

3. 联合索引的结构图
   

• 索引的设计原则