MySql详解优化

MySql基础知识请参考MySql详解基础

MySql优化知识请参考MySql详解优化

一、慢查询分析

1、SQL性能差原因

• 索引失效：索引建了，但是没有用上。
• 关联查询太多 join （设计缺陷或者不得已的需求）。
• 服务器调优以及各个参数的设置（缓冲、线程数等）。

2、分析慢SQL的步骤

• 观察，至少跑1天，看看生产的慢SQL情况。
• 开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，并将它抓取出来。
• explain + 慢SQL分析。
• show Profile查询SQL在MySQL数据库中的执行细节和生命周期情况。
• 运维经理 OR DBA，进行MySQL数据库服务器的参数调优。

3、慢查询日志

（1）慢查询日志是什么？

• MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中。
• long_query_time 的默认值为10，意思是运行10秒以上的语句。
• 由慢查询日志来查看哪些SQL超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒钟的SQL，结合之前 explain 进行全面分析。

（2）特别说明

• **默认情况下，MySQL数据库没有开启慢查询日志，**需要我们手动来设置这个参数。
• 当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

（3）查看慢查询日志是否开启以及如何开启

• 查看慢查询日志是否开启： SHOW VARIABLES LIKE '%slow_query_log%'; 。
• 开启慢查询日志： SET GLOBAL slow_query_log = 1; 。使用该方法开启MySQL的慢查询日志只对当前数据库生效，如果MySQL重启后会失效。

# 1、查看慢查询日志是否开启
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/6d6724c8f6ef-slow.log |
+---------------------+--------------------------------------+
# 2、开启慢查询日志
SET GLOBAL slow_query_log = 1;
# 3、关闭慢查询日志
SET GLOBAL slow_query_log = 0;

• 如果要永久开启慢查询日志，需要修改 my.cnf 文件，在 [mysqld] 下增加修改参数。

# my.cnf
[mysqld]
# 1.这个是开启慢查询。注意ON需要大写
slow_query_log=ON  
# 2.这个是存储慢查询的日志文件。这个文件不存在的话，需要自己创建
slow_query_log_file=/var/lib/mysql/slow.log

（4）开启了慢查询日志后，什么样的SQL才会被记录到慢查询日志里面呢？

• 这个是由参数 long_query_time 控制的，默认情况下 long_query_time 的值为10秒。
• MySQL中查看 long_query_time 的时间： SHOW VARIABLES LIKE 'long_query_time%'; 。

# 查看long_query_time 默认是10秒
# 只有SQL的执行时间>10才会被记录
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

• 修改 long_query_time 的时间，需要在 my.cnf 修改配置文件

[mysqld]
# 这个是设置慢查询的时间，我设置的为1秒
long_query_time=1

• 查询慢查询日志的总记录条数： SHOW GLOBAL STATUS LIKE '%Slow_queries%'; 。

mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 3     |
+---------------+-------+

（5）日志分析工具mysqldumpslow

日志分析工具 mysqldumpslow ：在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow 。

# 1、mysqldumpslow --help 来查看mysqldumpslow的帮助信息
root@1dcb5644392c:/usr/bin# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
 --verbose   verbose
 --debug     debug
 --help       write this text to standard output
 -v           verbose
 -d           debug
 -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default # 按照何种方式排序
               al: average lock time # 平均锁定时间
               ar: average rows sent # 平均返回记录数
               at: average query time # 平均查询时间
                 c: count # 访问次数
                 l: lock time # 锁定时间
                 r: rows sent # 返回记录
                 t: query time # 查询时间
 -r           reverse the sort order (largest last instead of first)
 -t NUM       just show the top n queries # 返回前面多少条记录
 -a           don't abstract all numbers to N and strings to 'S'
 -n NUM       abstract numbers with at least n digits within names
 -g PATTERN   grep: only consider stmts that include this string  
 -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
 -i NAME     name of server instance (if using mysql.server startup script)
 -l           don't subtract lock time from total time
  
# 2、 案例
# 2.1、得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log
# 2.2、得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log
# 2.3、得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log
# 2.4、另外建议使用这些命令时结合|和more使用，否则出现爆屏的情况
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log | more

4、Show Profile

（1）数据准备

建表

#班级表
CREATE TABLE `class` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`className` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`monitor` INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

#学员表
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

由于开启过慢查询日志，开启了 bin-log ，我们就必须为 function 指定一个参数，否则使用函数会报错。不加global只是当前窗口有效。

# 在mysql中设置
# log_bin_trust_function_creators 默认是关闭的 需要手动开启
mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)
mysql> SET GLOBAL log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)

上述修改方式MySQL重启后会失败，在 my.cnf 配置文件下修改永久有效。

[mysqld]
log_bin_trust_function_creators=ON

创建函数

#随机产生字符串
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN  
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO 
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); 
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;

#假如要删除
#drop function rand_string;

#用于随机产生多少到多少的编号
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN 
DECLARE i INT DEFAULT 0; 
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1))  ;
RETURN i; 
END //
DELIMITER ;

#假如要删除
#drop function rand_num;

创建存储过程

#创建往stu表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE insert_stu(  START INT , max_num INT )
BEGIN 
	DECLARE i INT DEFAULT 0; 
	SET autocommit = 0;   #设置手动提交事务
	REPEAT  #循环
	SET i = i + 1;  #赋值
	INSERT INTO student (stuno, name ,age ,classId ) VALUES
	((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000)); 
	UNTIL i = max_num 
	END REPEAT; 
	COMMIT;  #提交事务
END //
DELIMITER ;

#假如要删除
#drop PROCEDURE insert_stu;

#执行存储过程，往class表添加随机数据
DELIMITER //
CREATE PROCEDURE `insert_class`( max_num INT )
BEGIN 
	DECLARE i INT DEFAULT 0; 
	SET autocommit = 0;  
	REPEAT 
	SET i = i + 1; 
	INSERT INTO class ( classname,address,monitor ) VALUES
	(rand_string(8),rand_string(10),rand_num(1,100000)); 
	UNTIL i = max_num 
	END REPEAT; 
	COMMIT;
END //
DELIMITER ;

#假如要删除
#drop PROCEDURE insert_class;

调用存储过程

#执行存储过程，往class表添加1万条数据 
CALL insert_class(10000);
#执行存储过程，往stu表添加50万条数据 
CALL insert_stu(100000,500000);

（2）Show Profile是什么？

Show Profile ：MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

（3）分析步骤

是否支持，看看当前的MySQL版本是否支持。

# 查看Show Profile功能是否开启
mysql> SHOW VARIABLES LIKE 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+

开启 Show Profile 功能，默认是关闭的，使用前需要开启。

# 开启Show Profile功能
mysql> SET profiling=ON;

运行SQL

SELECT * FROM `student` GROUP BY `id`%10 LIMIT 150000;
SELECT * FROM `student` GROUP BY `id`%20 ORDER BY name;

查看结果，执行 SHOW PROFILES;``Duration ：持续时间。

SHOW PROFILES;

诊断SQL， SHOW PROFILE cpu,block io FOR QUERY Query_ID;

# 这里的3是第四步中的Query_ID。
# 可以在SHOW PROFILE中看到一条SQL中完整的生命周期。
mysql> SHOW PROFILE cpu,block io FOR QUERY 3;

（4）Show Profile 查询参数备注

• ALL ：显示所有的开销信息。
• BLOCK IO ：显示块IO相关开销（通用）。
• CONTEXT SWITCHES ：上下文切换相关开销。
• CPU ：显示CPU相关开销信息（通用）。
• IPC ：显示发送和接收相关开销信息。
• MEMORY ：显示内存相关开销信息。
• PAGE FAULTS ：显示页面错误相关开销信息。
• SOURCE ：显示和Source_function。
• SWAPS ：显示交换次数相关开销的信息。

（5）日常开发需要注意的结论

• converting HEAP to MyISAM ：查询结果太大，内存都不够用了，往磁盘上搬了。
• Creating tmp table ：创建临时表（拷贝数据到临时表，用完再删除），非常耗费数据库性能。
• Copying to tmp table on disk ：把内存中的临时表复制到磁盘，危险！
• locked ：死锁。

二、执行计划

1、概述

项目开发中，性能往往都是是我们重点关注的问题，其实很多时候一个SQL往往是整个请求中瓶颈最大的地方，因此我们必须了解SQL语句的执行过程来帮助我们做SQL语句的优化。

MySQL提供了 explain / desc 语句，来显示这条SQL语句的执行计划，执行计划可以帮助我们查看SQL语句的执行情况。explain对select，delete，update，insert，replace语句有效。

explain extended：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表 进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。

explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

2、数据准备

-- 创建数据库
create database test;

-- 创建数据表
CREATE TABLE `test`.`role` (
	`id` INT ( 11 ) NOT NULL,
	`name` VARCHAR ( 255 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
	PRIMARY KEY ( `id` ) USING BTREE 
) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `test`.`user` (
	`id` INT ( 11 ) NOT NULL,
	`name` VARCHAR ( 255 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
	`role_id` INT ( 11 ) NULL DEFAULT NULL,
	PRIMARY KEY ( `id` ) USING BTREE 
) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

3、desc和explain

（1）EXPLAIN是什么？

EXPLAIN：SQL的执行计划，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。

（2）EXPLAIN怎么使用？

语法： explain + SQL 。

explain 和 desc 效果一样，两个语句的效果是一模一样的，我们后面就统一使用 explain。

explain select * from user;

desc select * from user;

（3）EXPLAIN用途

• 表的读取顺序如何
• 数据读取操作有哪些操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间是如何引用
• 每张表有多少行被优化器查询

（4）EXPLAIN包含的字段

• id //select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
• select_type //查询类型
• table //正在访问哪个表
• partitions //匹配的分区
• type //访问的类型
• possible_keys //显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
• key //实际使用到的索引，如果为NULL，则没有使用索引
• key_len //表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
• ref //显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
• rows //根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
• filtered //查询的表行占表的百分比
• Extra //包含不适合在其它列中显示但十分重要的额外信息

4、 id字段

（1）id相同，越靠前的表越先执行

explain select * from user u left join role r on u.role_id=r.id;

（2）id不同，id越大的表越先执行

explain select * from user u where u.role_id=(select id from role r where
r.id=1);

（3）id有相同，也有不同，id越大的表越先执行，在id相同的表中，id越靠前的表越先执行

5、select_type 字段

（1）SIMPLE：简单的 select 查询，查询中不包含子查询或者 union

explain select * from user;

（2）PRIMARY：查询条件中包含有子查询时最外层的表（u1）

（3）SUBQUERY：条件子查询中的表（u2）

explain select * from user u1 where u1.id =(select id from user u2 where u2.id=1);

（3）SUBQUERY：条件中的子查询中的表（包括多重层级）（u2，u3）

explain select * from user u1 where u1.name =(select name from user u2 where u2.name=(select name from user u3 where u3.name='zs'));

（4）UNION：使用到union关联时，union关联的表（u2）

（5）UNION RESULT：使用union时，最终的结果集表（<union1,2>）

explain select * from user u1 union select * from user u2;

（6）DEPENDENT UNION：在子查询中使用到union的第二个以上的表（u3,u4）

（7）DEPENDENT SUBQUERY：在子查询中，使用到union的第一表（u2）

explain select * from user u1 where u1.id in (select id from user u2 where u2.id=1 union select id from user u3 where u3.id=2 union select id from user u4 where u4.id=3);

（7）DEPENDENT SUBQUERY: 子查询中的条件依赖于外部的查询（r1）

explain select * from user u1 where u1.role_id=(select id from role r1 where u1.id=1);

（8）DERIVED：衍生表的from子表（该子表必须使用union关联其他表）（u1）

explain select * from(select * from user u1 where u1.role_id=1 union select * from user u2 where u2.name='zs') temp;

6、 table 字段

表示该SQL语句是作用于那张表的，取值为：表名、表别名、衍生表名等。

explain select * from user;
explain select * from user u1;

7、partitions 字段

涉及到分区的表

准备数据：

create table goods_partitions (
 id int auto_increment primary key, 
 name varchar(12)
)
partition by range(id)
(
    partition p0 values less than (10000),
		partition p1 values less than MAXVALUE
);

（1）查看查询语句所使用到的分区

整个goods_partitions使用到了两个分区

explain select * from goods_partitions;

（2）查询id<10000的记录（属于p0分区）

explain select * from goods_partitions where id<10000;

8、type字段

反应一段SQL语句性能指标的重要参数，可以通过此参数来判断是否使用到了索引、是否全表扫描、是否范围查询等。

NULL>system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL //最好到最差
备注：掌握以下10种常见的即可，一般来说，得保证查询至少达到 range 级别，最好达到 ref 。
NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL

插入测试数据：

insert into role values(1,'保洁');
insert into role values(2,'保安');
insert into role values(3,'厨师');
insert into user values(1,'zs',1);

（1）null：代表不访问任何表

MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引

explain select 1;

（2）system：表中只有一条记录,并且此表为系统表(一般很少出现)

（3）const：通过唯一索引或者主键查询到的数据,只查询一次就查询到了

因为只匹配一行数据，所以很快，如主键置于where列表中，MySQL就能将该查询转换为一个常量

-- id是主键
explain select * from user where id=1;

根据name查询，类型不是const，给name加上唯一索引后，再查就是const了，测试完成再删除索引

-- 查看执行计划
explain select * from user where name='zs';
-- 创建唯一索引
create unique index user_name_unique on user(name);
-- 再查看执行计划
explain select * from user where name='zs';
-- 测试完成删除索引
drop index user_name_unique on user;

（4）eq_ref：使用主键或者唯一键索引的关联查询

代表有其他表引用了r表的主键或者唯一键，这里需要提一下，eq_ref有时候会不准！

explain select * from user u left join role r on u.role_id=r.id;

（5）ref：通过非唯一索引查询到的数据

非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，应该属于查找和扫描的混合体

-- 创建普通索引
create index user_name_index on user(name);
-- 查询执行计划
explain select * from user where name='zs';
-- 测试完毕删除索引
drop index user_name_index on user;

（6）ref_or_null：类似ref，但是可以搜索值为NULL的行

-- 创建普通索引
create index user_name_index on user(name);
-- 查询执行计划
explain select * from user where name='zs' or name is null;
-- 测试完毕删除索引
drop index user_name_index on user;

（7）index_merge：表示使用了索引合并的优化方法

两个字段都有索引，优化器会合并索引

-- 创建普通索引
create index user_name_index on user(name);
-- 查询执行计划
explain select * from user where id=1 or name='zs';
-- 测试完毕删除索引
drop index user_name_index on user;

（8）range：使用索引的范围查询

只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、<>、in、<、>等的查询。

explain select * from user u where u.id>20; -- 使用索引列进行范围查询
explain select * from user u where u.role_id>20; -- 使用普通列进行范围查询不会是range

-- 给role_id列添加索引，再次执行sql，查看执行计划
create index user_role_id_index on user(role_id);
explain select * from user u where u.role_id>20;
-- 测试完毕删除索引
drop index user_role_id_index on user;

（9）index：查询的是索引列，遍历了索引树

全索引树扫描，Index与All区别：index只遍历索引树，通常比All快，因为索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引文件中读取，而all是从数据文件读的。

explain select id from user;

（8）ALL：效率最低，遍历全表

explain select * from user;

9、 possible_keys 字段

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

-- 给name列加索引
create index idx_name on user(name); 
-- 可能用到了 idx_name 索引，但实际没有使用到。
explain select * from user where name='1' or name='2';
-- 测试完毕删除索引
drop index idx_name on user;

10、key字段

实际使用的索引。如果为 NULL ，则没有使用索引。查询中如果使用了覆盖索引，则该索引仅仅出现在 key 列表中。

explain select * from user where id=1;
-- 根据普通列查询
explain select * from user where name='zs';
-- 给name列加上索引
create index idx_name on user(name);
-- 根据索引查询
explain select * from user where name='zs';
-- 测试完毕删除索引
drop index idx_name on user;

11、key_len字段

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的。在不损失精度的情况下，长度越短越好。

key_len 计算规则

A、char和varchar

• 列长度

• 列是否为空：NULL（+1），NULL(+0)

• 字符集：如utf8mb4=4,utf8=3,gbk=2,lathin1=1

• 列类型为字符：如varchar（+2），char（+0）

• 计算公式：key_len=（表字符集长度）*列长度+1（null）+2（变长列）

B、数值类型

• tinyint 非空为1，可空为2
• smallint 非空为2，可空为3
• int 非空为4，可空为5
• bigint　　非空为8，可空为9

C. 时间类型

• date：非空3字节，可空4字节
• timestamp：非空4字节，可空5字节
• datetime：非空8字节，可空9字节

-- 我的id类型为int类型，因此占用4个字节
explain select * from user where id=1;

-- 我们把id类型改为bigint（Long），再次查看索引使用字节数
alter table user modify column id bigint;
explain select * from user where id=1;
-- 测试完毕更改回来
alter table user modify column id int;

12、ref 字段

表示某表的某个字段引用到了本表的索引字段

-- 表示u表的role_id引用了本表（r表）的索引字段（PRIMARY），ref字段显示正常，type字段也显
示eq_ref（正常）.
explain select * from user u left join role r on u.role_id=r.id;

13、rows 字段

根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数。

-- user表中有1条记录，role表中有3条记录
explain select * from user;
explain select * from role;

14、filtered 字段

查询的表行占表的百分比。

-- 去掉user表和role表的主键，现在也没有索引
-- 现在表中数据如下
select * from user;
select * from role;
-- r表实际记录3条，上述sql语句关联查询出来的结果只能得出一条结果集，因此命中率为
33.33%。
select * from user u inner join role r on u.role_id=r.id;
explain select * from user u inner join role r on u.role_id=r.id;
-- 测试完成 恢复表的主键

15、extra 字段

包含不适合在其它列中显示但十分重要的额外信息。

Using index > NULL > Using where >= Using temporary > Using filesort

（1）Using filesort：排序时无法使用到索引，效率低。

常见于order by和group by语句中。

explain select name from user order by name;

（2）Using temporary：表示SQL语句的操作使用到了临时表。

MySQL在对结果排序时使用临时表，常见于排序order by 和分组查询group by。

explain select name from user group by name;

（3）Using index：代表使用到了索引，效率高。

表示相应的 SELECT 操作中使用了覆盖索引，避免访问了表的数据行，效率不错！如果同时出现 Using where ，表示索引出现在where查找条件中。

覆盖索引：就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。注意：如果要使用覆盖索引，一定不能写SELECT *，要写出具体的字段。

-- 创建索引
create index user_name_index on user(name); 
-- 查看执行计划
explain select name from user order by name;
-- 测试完毕删除索引
drop index user_name_index on user;

（4）Using where：扫描全表。通常是查询条件中不是索引字段。

explain select * from user where name='zs';

（5）Using join buffer：使用了连接缓存

explain select * from user,role;

（5）NULL：没有用到额外的附加条件

explain select * from user where id=1;

（6）Impossible where ： WHERE 子句的值总是false，不能用来获取任何元组。

explain select *  from user where name='zs' and name='ls';

（7）Distinct：去重

一旦mysql找到了与行相联合匹配的行，就不再搜索了

explain select distinct user.name from user left join role on user.id=role.id;

（8）Select tables optimized away：MySQL根本没有遍历表或索引就返回数据

三、索引和查询优化

1、索引概述

（1）索引是什么

• MySQL官方对索引的定义为：索引（INDEX）是帮助MySQL高效获取数据的数据结构。所以说索引的本质是：数据结构。
• 数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

（2）索引优势和劣势

优势：

• 索引大大减少了服务器需要扫描的数据量（提高数据检索效率）
• 索引可以帮助服务器避免排序和临时表（降低数据排序成本，降低 CPU 的消耗）
• 索引可以将随机 I/O 变为顺序 I/O（降低数据库 IO 成本）

劣势：

• 实际上索引也是一张表，该表保存了索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
• 虽然索引大大提高了查询速度，但是同时会降低表的更新速度，因为更新表的时候，MySQL不仅要保存数据，还要维护索引表，增加了开销。

（3）索引分类

• 从主键角度

  • 主键索引：不允许有空值，为主键建立的索引。
  • 辅助索引：非主键索引，允许在定义索引的列中插入重复值和空值。

• 从列数量角度

  • 单列索引：在单个字段上创建的索引。
  • 多列索引（复合索引、联合索引）：在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。

• 从字段值唯一性角度

  • 唯一索引：索引列中的值必须是唯一的，但是允许为空值。
  • 非唯一索引：索引列中的值可以不唯一。

• 字段类型特殊角度

  • 全文索引：它查找的是文本中的关键词，而不是直接比较索引中的值
  • 空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。
  • 前缀索引：在文本类型如CHAR，VARCHAR，TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

• 数据结构角度

  • Hash 索引：主要就是通过 Hash 算法，将数据库字段数据转换成定长的 Hash 值，与这条数据的行指针一并存入 Hash 表的对应位置；如果发生 Hash 碰撞，则在对应 Hash 键下以链表形式存储。

    查询时，就再次对待查关键字再次执行相同的 Hash 算法，得到 Hash 值，到对应 Hash 表对应位置取出数据即可。

    使用 Hash 索引的数据库并不多， 目前有 Memory 引擎和InnoDB引擎支持 Hash 索引。

    缺点是，只支持等值比较查询，像 = 、 in() 这种，不支持范围查找，比如 where id > 10 这种，也不能排序。

  • B+ 树索引（下文会详细讲）

• 从物理存储角度

  • 聚集索引（clustered index）：指索引项的排序方式和表中数据记录排序方式一致的索引，并不是一种单独的索引类型，而是一种数据存储方式。

    也就是说聚集索引的顺序就是数据的物理存储顺序。它会根据聚集索引键的顺序来存储表中的数据，即对表的数据按索引键的顺序进行排序，然后重新存储到磁盘上。因为数据在物理存放时只能有一种排列方式，所以一个表只能有一个聚集索引。

    聚集索引插入数据速度慢（时间花费在“物理存储的排序”上），查询数据比非聚集索引速度快。

    

    InnoDB 也使用B+Tree作为索引结构，索引页大小16，和表数据页共同存放在表空间中。从InnoDB表数据存放方式可看出InnoDB表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

    InnoDB默认对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。一般来说，InnoDB 会以聚簇索引的形式来存储实际的数据，它是其它二级索引的基础。

  • 非聚集索引（non-clustered index）：索引顺序与物理存储顺序不同的索引。

    

    MyIsam 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。主索引和辅助索引没有区别都是非聚集索引。索引页正常大小为1024字节，索引页存放在.MYI 文件中。MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

（4）索引sql

-- 创建索引
CREATE [UNIQUE] INDEX indexName ON TableName(columnName(length));
-- 如：在article表上创建category_id的单个索引，名为：idx_article_c
CREATE INDEX idx_article_c ON article(category_id);
-- 如：在article表上创建category_id,views的联合索引，名为：idx_article_cv
CREATE INDEX idx_article_cv ON article(category_id,views);

-- 修改索引
/* 1、该语句添加一个主键，这意味着索引值必须是唯一的，并且不能为NULL */
ALTER TABLE TableName ADD PRIMARY KEY(column_list);
/* 2、该语句创建索引的键值必须是唯一的(除了NULL之外，NULL可能会出现多次) */
ALTER TABLE TableName ADD UNIQUE indexName(column_list);
/* 3、该语句创建普通索引，索引值可以出现多次 */
ALTER TABLE TableName ADD INDEX indexName(column_list);
--如：在article表中对views列添加索引，名为idx_article_v
alter table article add index idx_article_v(views);
/* 4、该语句指定了索引为FULLTEXT，用于全文检索 */
ALTER TABLE TableName ADD FULLTEXT indexName(column_list);

-- 删除索引
DROP INDEX [indexName] ON TableName;
-- 如：删除acticle表上的 idx_article_cv 索引
drop index idx_article_cv on article;

-- 查询索引
show index from TableName;
-- 如：查看article表的索引
show index from article;

（5）索引策略

哪些情况需要建索引

• 主键自动建立主键索引（唯一 + 非空）。
• 频繁作为查询条件的字段应该创建索引。
• 查询中与其他表关联的字段，外键关系建立索引。
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
• 查询中统计或者分组字段（group by也和索引有关）。

哪些情况不要建索引

• 记录太少的表不适合创建索引。

• 频繁更新的字段不适合创建索引。

• Where条件里用不到的字段不适合创建索引。

• 选择性很低的表不适合创建索引。

  索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。

2、高效索引

（1）覆盖索引

什么是覆盖索引

• 索引是高效找到行的一个方法，当能通过读取索引就可以得到想要的数据，就不需要读取行了。一个索引包含了满足查询结果的数据列就叫做覆盖索引。

覆盖索引的好处

• 在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询，减少了I0操作，提升了查询效率。
• 可以把随机IO变成顺序IO加快查询效率。

执行计划分析

/* 假设当前索引是index(`name`, `age`, `pos`) */
/* 在写SQL的不要使用 SELECT * ，用什么字段就查询什么字段 */
/* 没有用到覆盖索引 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 
'manager';
/* 用到了覆盖索引 */
EXPLAIN SELECT `name`, `age`, `pos` FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';

（2）前缀索引

前缀索引

前缀索引其实就是对文本的前几个字符（具体是几个字符在建立索引时指定）建立索引，这样建立起来的索引占用空间更小，所以查询更快。

对于内容很长的列，比如 blob, text 或者很长的 varchar 列，必须使用前缀索引，MySQL 不允许索引这些列的完整长度。

关键在于要选择合适长度的前缀，即 prefix_length。前缀太短，选择性太低，前缀太长，索引占用空间太大。为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列进行比较。

数据准备

-- 创建表
CREATE TABLE teacher ( ID BIGINT UNSIGNED PRIMARY KEY, email VARCHAR ( 64 ) ) ENGINE = INNODB;

前缀索引语法

-- 语法
ALTER TABLE table_name ADD KEY(column_name(prefix_length));
ALTER TABLE table_name ADD index index_name(column_name(prefix_length));

-- 默认包含整个字符串
alter table teacher add index index1(email);
-- 前缀索引
alter table teacher add index index2(email(6));

前缀索引数据结构

这两种不同的定义在数据结构和存储上有什么区别呢？下图就是这两个索引的示意图。

现在执行一个查询select id,email from teacher where email='xxx';

如果使用的是index1（即email整个字符串的索引结构），执行顺序是这样的：

• 从index1索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得ID2的值；
• 到主键上查到主键值是ID2的行，判断email的值是正确的，将这行记录加入结果集；
• 取index1索引树上刚刚查到的位置的下一条记录，发现已经不满足email=’zhangssxyz@xxx.com’的条件了，循环结束。

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

如果使用的是index2（即email(6)索引结构），执行顺序是这样的：

• 从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是ID1；
• 到主键上查到主键值是ID1的行，判断出email的值不是’zhangssxyz@xxx.com’，这行记录丢弃；
• 取index2上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出ID2，再到ID索引上取整行然后判断，这次值对了，将这行记录加入结果集；
• 重复上一步，直到在idxe2上取到的值不是’zhangs’时，循环结束。

也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又无需增加很多查询成本。

前缀索引缺点

• 无法使用前缀索引做 ORDER BY 和 GROUP BY
• 无法使用前缀索引做『覆盖索引』。

倒叙前缀

身份证号（前缀相同多，后缀相同少）这样的数据如何索引？

• 使用倒序存储：如果你存储身份证号的时候把它倒过来存，每次查询的时候，你可以这么写。

select field_list from t where id_card = reverse('input_id_card_string');	

• **使用 hash 字段。**你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

-- 创建索引
alter table t add id_card_crc int unsigned, add index(id_card_crc);
-- 查询
select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string' 

压缩前缀索引

MyISAM 使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，在某些情况下能极大地提高性能。

MyISAM 压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。

例如，索引块中的第一个值是“perform“，第二个值是”performance“，那么第二个值的前缀压缩后存储的是类似”7,ance“这样的形式。MyISAM 对行指针也采用类似的前缀压缩方式。

压缩块使用更少的空间，代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值，所以 MyISAM 查找时无法在索引块使用二分查找而只能从头开始扫描。正序的扫描速度还不错，但是如果是倒序扫描，例如 ORDER BY DESC，就不是很好了。所有在块中查找某一行的操作平均都需要扫描半个索引块。

测试表明，对于 CPU 密集型应用，因为扫描需要随机查找，压缩索引使得 MyISAM 在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在 CPU 内存资源与磁盘之间做权衡。压缩索引可能只需要十分之一大小的磁盘空间，如果是 I/O 密集型应用，对某些查询带来的好处会比成本多很多。

可以在 CREATE TABLE 语句中指定 PACK_KEYS 参数来控制索引压缩的方式。

如果您希望索引更小，则把此选项设置为1。这样做通常使更新速度变慢，同时阅读速度加快。把选项设置为0可以取消所有的关键字压缩。把此选项设置为DEFAULT时，存储引擎只压缩长的CHAR或VARCHAR列(仅限于MyISAM)。

如果您不使用PACK_KEYS，则默认操作是只压缩字符串，但不压缩数字。如果您使用PACK_KEYS=1，则对数字也进行压缩。

CREATE TABLE (
`id` INT NOT NULL ,
`name` VARCHAR(250) NULL ,
PRIMARY KEY (`id`) )
PACK_KEYS = 1;
ALTER TABLE table_name PACK_KEYS = 1;

（3）联合索引

索引合并

对于下面的查询 where 条件，这两个单列索引都是不好的选择：

SELECT user_id,user_name FROM mydb.sys_user where user_id = 1 or user_name = 'zhang3';

MySQL 5.0 版本之前，MySQL 会对这个查询使用全表扫描，除非改写成两个查询 UNION 的方式。

MySQL 5.0 和后续版本引入了一种叫做“索引合并”的策略，查询能够同时使用这两个单列索引进行扫描，并将结果合并。

这种算法有三个变种：OR 条件的联合（union），AND 条件的相交（intersection），组合前两种情况的联合及相交。索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建得很糟糕：

• 当出现多个AND条件，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
• 当出现多个OR条件，通常需要耗费大量的 CPU 和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
• 如果在 explain 中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。

最左前缀原则

索引项是按照索引定义里面出现的字段顺序排序的，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。如果跳过最左前缀就无法使用索引来加速检索。

建立联合索引的时候，我们的评估标准是，索引的复用能力。所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

/* 假设当前索引是index(user_id,user_name) */
/*  使用索引*/
SELECT * FROM mydb.sys_user where user_name = 'zhang3' and user_id=1;
/*  使用索引*/
SELECT * FROM mydb.sys_user where user_name = 'zhang3';
/*  使用索引*/
SELECT * FROM mydb.sys_user where user_name like 'zha%';
/*  没有最左匹配不能使用索引*/
SELECT * FROM mydb.sys_user where user_id=1;

索引下推

如果现在有一个需求：检索出表中“名字第一个字是 B，而且年龄是 19 岁的所有男孩”。那么，SQL 语句是这么写的：

/* 假设当前索引是index(name,age,sex) */
select * from tuser where name like 'B%' and age=19 and sex=F;

根据最左前缀原则，这个语句在搜索索引树的时候，只能用 “B”，找到第一个满足条件的记录 ID = 2。

在 MySQL 5.6 之前，只能从 ID = 2 开始一个个回表。到主键索引上找出数据行，再对比字段值。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，大大提升了查询的效率。

（4）重复索引和冗余索引

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。

如果创建了索引(A,B)，再创建索引(A)就是冗余索引，索引(A,B)也可以当做索引(A)来使用（这种冗余只是对 B-Tree 索引来说的）。但是如果再创建索引(B,A)，则不是冗余索引，索引(B)也不是，因为B不是索引(A,B)的最左前缀。另外，其他不同类型的索引（例如哈希索引或者全文索引）也不会是 B-Tree 索引的冗余索引，而无论覆盖的索引列是什么。

（5）未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远不使用的索引，这样的索引完全是累赘，建议考虑删除，有两个工具可以帮助定位未使用的索引：

• 在 percona server 或者 mariadb 中先打开 userstat=ON 服务器变量，默认是关闭的，然后让服务器运行一段时间，再通过查询information_schema.index_statistics 就能查到每个索引的使用频率。
• 使用 percona toolkit 中的 pt-index-usage 工具，该工具可以读取查询日志，并对日志中的每个查询进行explain 操作，然后打印出关于索引和查询的报告，这个工具不仅可以找出哪些索引是未使用的，还可以了解查询的执行计划。

3、索引是如何设计的

（1）定义问题

这里我们假设要解决的问题，只包含这样两个常用的需求：

• 根据某个值查找数据，比如 select * from user where id=1234；
• 根据区间值来查找某些数据，比如 select * from user where id > 1234 and id < 2345。

在执行效率方面，我们希望通过索引，查询数据的效率尽可能地高；在存储空间方面，我们希望索引不要消耗太多的内存空间。

推荐一个数据结构可视化网站：www.cs.usfca.edu/~galles/vis…

（2）尝试解决问题--散列表

原理

• 事先将索引通过 hash算法后得到的hash值(即磁盘文件指针）存到hash表中。
• 在进行查询时，将索引通过hash算法，得到hash值，与hash表中的hash值比对。通过磁盘文件指针，只要一次磁盘IO就能找到要的值，查询性能很好，时间复杂度是 O(1)。

例如：

　在第一个表中，要查找col=6的值。hash(6) 得到值，比对hash表，就能得到值。性能非常高。

存在的问题

• 散列表不能支持按照区间快速查找数据。
• 散列表不能支持模糊查询。

（3）尝试解决问题--二叉树

特点

• 左子节点值 < 节点值；
• 右子节点值 > 节点值；
• 当数据量非常大时，要查找的数据又非常靠后，和没有索引相比，那么二叉树结构的查询优势将非常明显。

存在的问题

• 如下图，可以看出，二叉树出现单边增长时，二叉树变成了“链”，查找效率依旧低下。

（4）尝试解决问题--红黑树（一种平衡二叉树）

特点

• 根节点是黑色的；
• 每个叶子节点都是黑色的空节点（NIL），也就是说，叶子节点不存储数据；
• 任何相邻的节点都不能同时为红色，也就是说，红色节点是被黑色节点隔开的；
• 每个节点，从该节点到达其可达叶子节点的所有路径，都包含相同数目的黑色节点。

存在的问题

• 红黑树虽然和二叉树相比，一定程度上缓解了单边过长的问题，但是它依旧有存储高度问题。
• 不能支持按照区间快速查找数据问题。

（5）尝试解决问题--B-Tree（M叉树）

特点

B-Tree == B Tree，是同一个东西，没有 B 减树，就叫B树。

B-Tree索引能很好解决红黑树中遗留的高度问题，B-Tree 是一种平衡的多路查找（又称排序）树，在文件系统中和数据库系统有所应用，主要用作文件的索引，其中的B就表示平衡（Balance）。

为了描述B-Tree，首先定义一条数据记录为一个二元组 [key, data]，key为记录的键值key，对于不同数据记录，key是互不相同的；data为数据记录除以key外的数据 （这里指的是聚集索引）。那么B-Tree是满足下列条件的数据结构：

• d 为大于1的一个正整数，称为BTree的度；

• h为一个正整数，称为BTree的高度；

• key和指针互相间隔，节点两端是指针；

• 叶子节点具有相同的深度，叶子节点的指针为空，节点中数据索引(下图中的key)从左往右递增排列。

存在问题

• 范围查找性能差。

• 每个节点中不仅包含数据的key值，还有data值。而每一个节点的存储空间是有限的(mysql默认设置一个节点的大小为16K)，如果data中存放的数据较大时，将会导致每个节点（即一个页）能存储的key的数量（索引的数量）很小，所以当数据量很多，且每行数据量很大的时候，同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。

（6）尝试解决问题--B+Tree（B-Tree优化）

特点

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低树的高度。

• 非叶子节点不存储data，只存储索引，可以存放更多索引。
• 叶子节点不存储指针。
• 顺序访问指针，提高区间访问性能。
• 非叶子节点中的索引最终还是会在叶子节点上存储一份，也就是叶子节点会包含非叶子节点上的所有索引。
• 一个父节点，它的左侧子节点都小于父节点的值，右侧的子节点都大于等于父节点的值。
• 每一层节点从左往右都是递增排列，无论是数值型还是字符型。

（7）B-Tree 和 B+Tree比较

B 树 和 B+ 树的结构差异

将 [11,13,15,16,20,23,25,30,23,27] 用 B 树 和 B+ 树存储，看下结构

关键词	B-树	B+树	备注
最大分支，最小分支	每个结点最多有m个分支（子树），最少⌈m/2⌉（中间结点）个分支或者2个分支（是根节点非叶子结点）。	同左	m阶对应的就是就是最大分支
n个关键字与分支的关系	分支等于n+1	分支等于n	无
关键字个数（B+树关键字个数要多）	大于等于⌈m/2⌉-1小于等于m-1	大于等于⌈m/2⌉小于等于m	B+树关键字个数要多，+体现在的地方。
叶子结点相同点	每个节点中的元素互不相等且按照从小到大排列；所有的叶子结点都位于同一层。	同左	无
叶子结点不相同	不包含信息	叶子结点包含信息，指针指向记录。	无
叶子结点之间的关系	无	B+树上有一个指针指向关键字最小的叶子结点，所有叶子节点之间链接成一个线性链表	无
非叶子结点	一个关键字对应一个记录的存储地址	只起到索引的作用	无
存储结构	相同	同左	无

看一个范围查找的例子比较 B 树和 B+ 树

B Tree 结构查询 [10-25] 的数据（从根节点开始，随机查找是一样的）

1. 加载根节点，第一个节点元素15，大于10【磁盘 I/O 操作第 1 次】
2. 通过根节点的左子节点地址加载，找到 11，13【磁盘 I/O 操作第 2 次】
3. 重新加载根节点，找到中间节点数据 16，20【磁盘 I/O 操作第 3 次】
4. 再次加载根节点，23 小于 25，再加载右子节点，找到 25，结束【磁盘 I/O 操作第 4 次】

而 B+ 树对范围查找就简单了，数据都在最下边的叶子节点下，而且链起来了，我只需找到第一个然后遍历就行（暂且不考虑页分裂等其他问题）。

为什么 MySQL 索引要用 B+ 树不是B树

B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构。

用 B+ 树不用考虑的是 IO 对性能的影响，B 树的每个节点都存储数据，而 B+ 树只有叶子节点才存储数据，所以查找相同数据量的情况下，B 树的高度更高，IO 更频繁。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。其中在 MySQL 底层对 B+ 树进行进一步优化：在叶子节点中是双向链表，且在链表的头结点和尾节点也是循环指向的。

B-Tree 结构图每个节点中不仅要包含数据的 key 值，还有 data 值。而每一个页的存储空间是有限的，如果 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小，当存储的数据量很大时同样会导致 B-Tree 的深度较大，增大查询时的磁盘 I/O 次数，进而影响查询效率。在 B+Tree 中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的 key 值数量，降低 B+Tree 的高度。

IO 次数取决于 B+ 数的高度 h，假设当前数据表的数据为 N，每个磁盘块的数据项的数量是 m，则有 h=㏒(m+1)N，当数据量 N 一定的情况下，m 越大，h 越小；而 m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如 int 占 4 字节，要比 bigint 8 字节少一半。这也是为什么 B+ 树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于 1 时将会退化成线性表。

（8）B+Tree详细设计过程

解决区间查找问题

为了让二叉查找树支持按照区间来查找数据，我们可以对它进行这样的改造：树中的节点并不存储数据本身，而是只是作为索引。除此之外，我们把每个叶子节点串在一条链表上，链表中的数据是从小到大有序的。

改造之后，如果我们要查找某个区间的数据。我们只需要拿区间的起始值，在树中进行查找，当查找到某个叶子节点之后，我们再顺着链表往后遍历，直到链表中的结点数据值大于区间的终止值为止。所有遍历到的数据，就是符合区间值的所有数据。

减少内存的占用，同时也减少磁盘IO

但是，我们要为几千万、上亿的数据构建索引，如果将索引存储在内存中，尽管内存访问的速度非常快，查询的效率非常高，但是，占用的内存会非常多。

比如，我们给一亿个数据构建二叉查找树索引，那索引中会包含大约 1 亿个节点，每个节点假设占用 16 个字节，那就需要大约 1GB 的内存空间。如果我们要给 10 张表建立索引，那对内存的需求是无法满足的。

我们可以借助时间换空间的思路，把索引存储在硬盘中，但硬盘是一个非常慢速的存储设备，尽管减少了内存消耗，但是在数据查找的过程中，数据查询效率就相应降低很多。

磁盘IO是一个很慢的过程

磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分
- 寻道时间：是磁臂移动到指定磁道所需要的时间，主流磁盘一般在 5ms 以下；
- 旋转延迟：是我们经常听说的磁盘转速，比如一个磁盘 7200 转，表示每分钟能转 7200 次，也就是说 1 秒钟能转 120 次，旋转延迟就是 1/120/2 = 4.17ms；
- 传输时间：是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。

那么访问一次磁盘的时间，即一次磁盘 IO 的时间约等于 5+4.17 = 9ms 左右，听起来还挺不错的，但要知道一台 500 -MIPS 的机器每秒可以执行 5 亿条指令，因为指令依靠的是电的性质，换句话说执行一次 IO 的时间可以执行 40 万条指令，数据库动辄十万百万乃至千万级数据，每次 9 毫秒的时间，显然是个灾难。

每个节点的读取，都对应一次磁盘 IO 操作。树的高度就等于每次查询数据时磁盘 IO次数，我们优化的重点就是尽量减少磁盘 IO 操作，也就是，尽量降低树的高度。

那如何降低树的高度呢？如果我们把索引构建成 m 叉树，高度是不是比二叉树要小呢？如果给 16 个数据构建二叉树索引，树的高度是 4，查找一个数据，就需要 4 个磁盘 IO 操作（如果根节点存储在内存中，其他节点存储在磁盘中），如果对 16 个数据构建五叉树索引，那高度只有 2，查找一个数据，对应只需要 2 次磁盘IO操作。如果 m 叉树中的 m 是 100，那对一亿个数据构建索引，树的高度也只是 3，最多只要 3 次磁盘 IO 就能获取到数据。磁盘 IO 变少了，查找数据的效率也就提高了。

如何确定M叉树的M值

对于相同个数的数据构建 m 叉树索引，m 叉树中的 m 越大，那树的高度就越小，那 m 叉树中的 m 是不是越大越好呢？到底多大才最合适呢？

不管是内存中的数据，还是磁盘中的数据，操作系统都是按页（一页大小通常是 4KB，这个值可以通过 getconfig PAGE_SIZE 命令查看）来读取的，一次会读一页的数据。如果要读取的数据量超过一页的大小，就会触发多次 IO 操作。所以，我们在选择 m 大小的时候，要尽量让每个节点的大小等于一个页的大小。读取一个节点，只需要一次磁盘 IO 操作。

局部性原理与磁盘预读

磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。

这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此预读可以提高I/O效率。

预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后返回，程序继续运行。

InnoDB 存储引擎中默认每个页的大小为16KB，可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K，可通过如下命令查看页的大小：show variables like 'innodb_page_size';

而系统一个磁盘块的存储空间往往没有这么大，因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘 I/O 次数，提高查询效率。

增删改数据如何保持节点M值

对于一个 B+ 树来说，m 值是根据页的大小事先计算好的，也就是说，每个节点最多只能有 m 个子节点。在往数据库中写入数据的过程中，这样就有可能使索引中某些节点的子节点个数超过 m，这个节点的大小超过了一个页的大小，读取这样一个节点，就会导致多次磁盘 IO 操作。我们该如何解决这个问题呢？

我们只需要将这个节点分裂成两个节点。节点分裂之后，其上层父节点的子节点个数就有可能超过 m 个，可以用同样的方法，将父节点也分裂成两个节点。这种级联反应会从下往上，一直影响到根节点。（下图中的 B+ 树是一个三叉树。我们限定叶子节点中，数据的个数超过 2 个就分裂节点；非叶子节点中，子节点的个数超过 3 个就分裂节点）。

正是因为要时刻保证 B+ 树索引是一个 m 叉树，所以，索引的存在会导致数据库写入的速度降低。实际上，不光写入数据会变慢，删除数据也会变慢。这是为什么呢？

频繁的数据删除，就会导致某些节点中，子节点的个数变得非常少，长此以往，如果每个节点的子节点都比较少，势必会影响索引的效率。

我们可以设置一个阈值。在 B+ 树中，这个阈值等于 m/2。如果某个节点的子节点个数小于 m/2，我们就将它跟相邻的兄弟节点合并。不过，合并之后节点的子节点个数有可能会超过 m。针对这种情况，我们可以借助插入数据时候的处理方法，再分裂节点。

下面举了一个删除操作的例子（下图中的 B+ 树是一个五叉树。我们限定叶子节点中，数据的个数少于 2 个就合并节点；非叶子节点中，子节点的个数少于 3 个就合并节点。）。

4、MyISAM和InnoDB的索引结构对比

（1）MyISAM 索引结构

MyISAM 引擎的索引文件和数据文件是分离的。MyISAM 引擎索引结构的叶子节点的数据域，存放的并不是实际的数据记录，而是数据记录的地址。这样的索引称为“非聚簇索引”。MyISAM 的主索引与辅助索引区别并不大，主键索引就是一个名为 PRIMARY 的唯一非空索引。

在 MyISAM 中，索引（含叶子节点）存放在单独的 .myi 文件中，叶子节点存放的是数据的物理地址偏移量（通过偏移量访问就是随机访问，速度很快）。

通过索引查找数据的流程：先从索引文件中查找到索引节点，从中拿到数据的文件指针，再到数据文件中通过文件指针定位了具体的数据。

（2）InnoDB 索引结构

InnoDB 引擎索引结构的叶子节点的数据域，存放的就是实际的数据记录（对于主索引，此处会存放表中所有的数据记录；对于辅助索引此处会引用主键，检索的时候通过主键到主键索引中找到对应数据行），或者说，InnoDB 的数据文件本身就是主键索引文件，这样的索引被称为"“聚簇索引”，一个表只能有一个聚簇索引。

主键索引：

我们知道 InnoDB 索引是聚集索引，它的索引和数据是存入同一个 .idb 文件中的，因此它的索引结构是在同一个树节点中同时存放索引和数据，如下图中最底层的叶子节点有三行数据，对应于数据表中的 id、name、score 数据项。

在 Innodb 中，索引分叶子节点和非叶子节点，非叶子节点就像新华字典的目录，单独存放在索引段中，叶子节点则是顺序排列的，在数据段中。

InnoDB 的数据文件可以按照表来切分（只需要开启innodb_file_per_table)，切分后存放在xxx.ibd中，不切分存放在 xxx.ibdata中。

从 MySQL 5.6.6 版本开始，它的默认值就是 ON 了。建议将这个值设置为 ON。因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

辅助（非主键）索引：

这次我们以示例中学生表中的 name 列建立辅助索引，它的索引结构跟主键索引的结构有很大差别，在最底层的叶子结点有两行数据，第一行的字符串是辅助索引，按照 ASCII 码进行排序，第二行的整数是主键的值。

这就意味着，对 name 列进行条件搜索，需要两个步骤：

1. 在辅助索引上检索 name，到达其叶子节点获取对应的主键；
2. 使用主键在主索引上再进行对应的检索操作

这也就是所谓的“回表查询”

InnoDB 索引结构需要注意的点

1. 数据文件本身就是索引文件
2. 表数据文件本身就是按 B+Tree 组织的一个索引结构文件
3. 聚集索引中叶节点包含了完整的数据记录
4. InnoDB 表必须要有主键，并且推荐使用整型自增主键

正如我们上面介绍 InnoDB 存储结构，索引与数据是共同存储的，不管是主键索引还是辅助索引，在查找时都是通过先查找到索引节点才能拿到相对应的数据，如果我们在设计表结构时没有显式指定索引列的话，MySQL 会从表中选择数据不重复的列建立索引，如果没有符合的列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，并且这个字段长度为 6 个字节，类型为整型。

5、索引执行过程（InnoDB）

（1）数据准备

以下面的student表为例，它的 id 是主键，age 列为普通索引。

CREATE TABLE `stu`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index_age`(`age`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 66 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

表数据如下:

（2）主键索引

为什么要有主键？

底层就是用B+Tree维护的，而B+Tree的结构就决定了必须有主键才能构建B+Tree树这个结构。

为什么推荐用整型主键？

假如使用类似 UUID 的字符串作为主键，那么在查找时，需要比较两个主键是否相同，这是一个相比整型比较 非常耗时的过程。需要一个字符，一个字符的比较，自然比较慢。

为什么用自增主键

• 后面的主键索引总是大于前面的主键索引，在做范围查询时，非常方便找到需要的数据。
• 在添加的过程中，因为是自增的，每次添加都是在后面插入，树分裂的机会小；而UUID大小不确定，分裂机会大，需要重新平衡树结构，性能损耗大。

主键索引，它使用 B+ 树构建，叶子节点存储的是数据表的某一行数据。当表没有创建主键索引时，InnoDB 会自动创建一个 ROWID 字段用于构建聚簇索引。规则如下：

• 在表上定义主键 PRIMARY KEY，InnoDB 将主键索引用作聚簇索引。
• 如果表没有定义主键，InnoDB 会选择第一个不为 NULL 的唯一索引列用作聚簇索引。
• 如果以上两个都没有，InnoDB 会使用一个 6 字节长整型的隐式字段 ROWID 字段构建聚簇索引。该 ROWID 字段会在插入新行时自动递增。

主键索引数据结构

主键索引等值查询执行过程

select * from stu where id = 38;

过程如下：

• 第一次磁盘 IO：从根节点检索，将数据块 1 加载到内存，比较 38 < 44，走左边。
• 第二次磁盘 IO：将左边数据块 2 加载到内存，比较 8<37<38，走右边。
• 第三次磁盘 IO：将右边数据块 6 加载到内存，比较 37<38，38=38。查询完毕，将数据返回客户端。

主键索引范围查询执行过程

select * from stu where id between 38 and 44;

前面也介绍说了，B+ 树因为叶子节点有双向指针，范围查询可以直接利用双向有序链表。

过程如下：

• 第一次磁盘 IO：从根节点检索，将数据块 1 加载到内存，比较 38 < 44，走左边。
• 第二次磁盘 IO：将左边数据块 2 加载到内存，比较 8<37<38，走右边。
• 第三次磁盘 IO：将右边数据块 6 加载到内存，比较 37<38，38=38。走右边。
• 第四次磁盘 IO：将右边数据块 7 加载到内存，比较 38<44=44。查询完毕，将数据返回客户端。

（3）普通单列索引

为什么非主键索引结构叶子节点存储的是主键值，而不是全部数据？

• 节省空间：指向主键的节点，不用再存储一份相同的数据；（否则的话，如果建立多个非主键索引，每个上面都存储的完整数据，非常占用空间）
• 数据一致性：如果修改索引15 的数据，那只要修改主键的 data，而如果非主键的data也存一份的话，那得修改两份，这样就涉及到事务一致性的问题，耗时，性能低。

普通索引数据结构

在 InnDB 中，B+ 树普通索引不存储数据，只存储数据的主键值。比如本表中的 age，它的索引结构就是这样的：

普通索引等值查询执行过程

select * from stu where age = 48;

使用普通索引需要检索两次索引。第一次检索普通索引找出 age = 48 得到主键值，再使用主键到主键索引中检索获得数据。这个过程称为回表。也就是说，基于非主键索引的查询需要多扫描一遍索引树。因此，我们应该尽量使用主键查询。

过程如下：

• 第一次磁盘 IO：从根节点检索，将数据块1 加载到内存，比较 48 < 54，走左边。
• 第二次磁盘 IO：将左边数据块 2 加载到内存，比较 28<47<48，走右边。
• 第三次磁盘 IO：将右边数据块 6 加载到内存，比较 47<48，48=48。得到主键 38。
• 第四次磁盘 IO：从根节点检索，将根节点加载到内存，比较 38 < 44，走左边。
• 第五次磁盘 IO：将左边数据块 2 加载到内存，比较 8<37<38，走右边。
• 第六次磁盘 IO：将右边数据块 6 加载到内存，比较 37<38，38=38。查询完毕，将数据返回客户端。

（4）联合索引

为什么希望使用覆盖索引？

如果非聚集索引中能索引覆盖，那么我们只需要遍历非聚集索引这个B+Tree从其中的Key里拿到索引值就结束了，只需要遍历一棵树。 如果不能索引覆盖，需要先遍历非聚集索引，然后拿到data中存储的主键值，再去聚集索引中遍历查找数据，相比索引覆盖的话，IO次数更多，性能相对低。

如果为每一种查询都设计一个索引，索引是不是太多了？如果我现在要根据学生的姓名去查它的年龄。假设这个需求出现的概览很低，但我们也不能让它走全表扫描吧？

但是为一个不频繁的需求创建一个（姓名）索引是不是有点浪费了？那该咋做呢？我们可以建个（name，age）的组合索引来解决呀。

组合索引的结构

组合条件查询执行过程

select name,age from stu where name='二狗5' and age = 48;

过程如下：

• 第一次磁盘 IO：从根节点检索，将数据块1 加载到内存，比较 二狗5 < 二狗6，走左边。
• 第二次磁盘 IO：将左边数据块 2 加载到内存，比较 二狗2<二狗4<二狗5，走右边。
• 第三次磁盘 IO：将右边数据块 6 加载到内存，比较 二狗4<二狗5，二狗5=二狗5。得到主键 38。
• 第四次磁盘 IO：从根节点检索，将根节点加载到内存，比较 38 < 44，走左边。
• 第五次磁盘 IO：将左边数据块 2 加载到内存，比较 8<37<38，走右边。
• 第六次磁盘 IO：将右边数据块 6 加载到内存，比较 37<38，38=38。查询完毕，将数据返回客户端。

6、索引失效情况

（1）数据准备

-- 创建表
CREATE TABLE `staffs`(
`id` INT(10) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` INT(10) NOT NULL DEFAULT 0 COMMENT '年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
)COMMENT '员工记录表';

-- 插入数据
INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES('王五', 18, 'manager');
INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES('张三', 20, 'dev');
INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES('李四', 21, 'dev');

-- 创建索引
CREATE INDEX idx_staffs_name_age_pos ON `staffs`(`name`,`age`,`pos`);

（2）最佳左前缀法则

在MySQL建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须 按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段的第1个字段时，多列（或联合）索引不会被使用。

/* 用到了idx_staffs_name_age_pos索引中的name字段 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = '王五';

/* 用到了idx_staffs_name_age_pos索引中的name, age字段 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = '王五' AND `age` = 18;

/* 用到了idx_staffs_name_age_pos索引中的name，age，pos字段 这是属于全值匹配的情况*/
EXPLAIN SELECT * FROM `staffs` WHERE `name` = '王五' AND `age` = 18 AND `pos` = 
'manager';

/* 索引没用上，跳过索引的第一个字段，ALL全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `age` = 18 AND `pos` = 'manager';

/* 索引没用上，跳过索引的第一个字段第二个字段，ALL全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `pos` = 'manager';

/* 用到了idx_staffs_name_age_pos索引中的name字段，pos字段索引失效 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = '王五' AND `pos` = 'manager';

（3）主键插入不用自增列

对于一个 使用InnoDB存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序， 所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如 果我们插入的主键值忽大忽小的话，就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在1-100 之间：

如果此时再插入一条主键值为 9的记录，那它插入的位置就如下图：

可这个数据页已经满了，我们需要把当前 页面分裂 成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着： 性能损耗 ！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增 ，这样就不会发生这样的性能损耗了。 所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入 ，比如： person_info 表：

CREATE TABLE person_info(
	 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	 name VARCHAR(100) NOT NULL,
	 birthday DATE NOT NULL,
	 phone_number CHAR(11) NOT NULL,
	 country varchar(100) NOT NULL,
	  PRIMARY KEY (id),
	  KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

我们自定义的主键列 id 拥有AUTO_INCREMENT 属性，在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。

（4）计算、函数导致索引失效

在索引列上进行计算，会使索引失效。

explain SELECT * FROM `staffs` WHERE `name` = '王五';

explain SELECT * FROM `staffs` WHERE LEFT(`name`, 2) = '王五';

（5）类型转换导致索引失效

这里name = 2000在MySQL中会发生强制类型转换，将数字转成字符串，索引就会失效

设计实体类属性时，一定要与数据库字段类型相对应。否则，就会出现类型转换的情况

EXPLAIN SELECT * FROM `staffs` WHERE `name` = '2000';
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 2000;

（6）数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行转换会造成索引失效。

（7）范围条件右边的列索引失效

-- 全部索引生效
EXPLAIN SELECT * FROM `staffs` WHERE `name` = '王五' AND `age` = 18 AND `pos` = 
'manager';
-- `age` > 18 范围后面的索引失效
EXPLAIN SELECT * FROM `staffs` WHERE `name` = '张三' AND `age` > 18 AND `pos` = 
'dev';

（8）不等于(!= 或者<>)索引失效

!=或者<>会使索引失效，使用覆盖索引可以提高性能。

EXPLAIN SELECT * FROM `staffs` WHERE `name` != '王五';
EXPLAIN SELECT * FROM `staffs` WHERE `name` <> '王五';

（9）is null可以使用索引，is not null无法使用索引

EXPLAIN SELECT name FROM staffs WHERE name is not null;
EXPLAIN SELECT name FROM staffs WHERE name is null;

（10）like以通配符%开头索引失效

like百分号加载左边会使索引失效

/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%王%';
/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%王';
/* 使用索引范围查询 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '王%';

一定要在左边加%用的话，使用覆盖索引

/* 使用覆盖索引 */
EXPLAIN SELECT name,age,pos FROM `staffs` WHERE `name` LIKE '%王';

（11）OR 前后存在非索引的列，索引失效

在 where 子句中，如果在 OR 前的条件列进行了索引，而在OR 后的条件列没有进行索引，那么索引失效，也就是，让OR的前后条件都具备索引，如果缺少一个就会出现索引失效

因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引时没有意义的。只要有条件列没有索引，就会进行全表扫描，因此 所有的条件列也会失效。

-- 索引生效
EXPLAIN SELECT name,age FROM `staffs` WHERE `name`='zhangsan' or age=18;
-- 索引失效 add_time不在索引范围
EXPLAIN SELECT name,age FROM `staffs` WHERE `name`='zhangsan' or add_time='2022-07-20 02:28:58';

（12）一般性总结建议

假设index(a,b,c)，案例总结

Where语句	索引是否被使用
where a = 3	Y，使用到a
where a = 3 and b = 5	Y，使用到a，b
where a = 3 and b = 5 and c=6	Y，使用到a，b，c
where b = 3 或者 where b = 3 and c = 4 或者 where c= 4	N，没有用到a字段
where a = 3 and c = 5	使用到a，但是没有用到c，因为b断了
where a = 3 and b > 4 and c = 5	使用到a，b，但是没有用到c，因为c在范围之后
where a = 3 and b like 'kk%' and c = 4	Y，a，b，c都用到
where a = 3 and b like '%kk' and c = 4	只用到a
where a = 3 and b like '%kk%' and c = 4	只用到a
where a = 3 and b like 'k%kk%' and c = 4	Y，a，b，c都用到

一般性建议

• 对于单值索引，尽量选择针对当前 query 过滤性更好的索引。
• 在选择复合索引的时候，当前 query 中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
• 在选择复合索引的时候，尽量选择可以能够包含当前 query 中的 where 子句中更多字段的索引。
• 尽可能通过分析统计信息和调整 query 的写法来达到选择合适索引的目的