前边的系列文章中是基于Linux中的MySQL进行案例演示的，为了后续测试百万条数据的sql性能分析，接下来的案例将会在Windows的MySQL中进行演示，MySQL版本要求需在8.0以上，我的MySQL版本信息如下：
链接：https://pan.baidu.com/s/1TGLDb9awudyjhZDcu0HNfQ?pwd=yyds
提取码：yyds 百万数据初始化：
前置说明 ：
-- 查询是否开启了加载本地文件（默认没有开启，需要开启才能执行load指令）
show variables like 'local_infile';
-- 开启加载本地文件
set global local_infile=on;
-- 执行load指令（依次执行tb_sku1、tb_sku2、...tb_sku5，执行一个脚本差不多耗费100s，耐心等待即可！）
load data local infile 'D:/temp/tb_sku1.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';
创建索引：
（1）name字段为姓名字段，该字段的值可能会重复，为该字段创建普通索引；
create index idx_user_name on user(name);
（2）phone字段为手机号码，非空且唯一，为该字段创建唯一索引
create unique index idx_user_phone on user(phone);
（3）为profession、age、status字段创建联合索引；
create index idx_user_profession_age_status on user(profession,age,status);
（4）为email字段建立普通索引
create index idx_user_email on user(email);
查看索引： 查看user表中所有的索引数据；
show index from user;
概述：
MySQL客户端连接成功后，通过show [session|global] status 命令可以查询服务器的状态信息，通过如下指令可以查看当前数据库的insert、update、delete、select的访问频次。
查看当前数据库以哪种业务为主：
show session status like 'Com_______';
通过上述指令的执行结果，可以分析出当前业务是以查询为主还是以增删改为主，从而为数据库的优化提供参考依据，如果是以增删改为主，就可以考虑不对其进行索引优化了，如果是以查询为主，那么就要考虑对索引进行优化了。
说明：
Com_insert：插入次数
Com_update：更新次数
Com_delete：删除次数
Com_select：查询次数
概述：
慢查询日志记录了执行时间超过指定参数（long_query_time，单位：秒）的所有SQL语句的日志。MySQL默认的慢查询日志没有开启，我们可以看一下系统变量中该参数对应的值是什么：
（1）查看慢SQL配置：show variables like '%slow_query_log%';
（2）开启慢SQL配置： Windows ===> my.ini、Linux ===> /etc/my.cnf
添加如下内容：
# 开启MySQL慢查询日志开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
（3）重启MySQL
（4）执行如下sql，观察耗时情况
（5）观察慢sql日志中的信息
通过日志可以清晰的看出，哪些sql超时了，从而定位出效率低下的sql，进行针对性的优化；
概述：
profiles详情用于帮助开发者定位sql执行的消耗时间都花在哪里了。
（1）查看当前mysql是否支持profile操作
select @@having_profiling;
说明：YES表示当前数据库支持profile操作。
（2）查看是否开启了profile支持
说明：1表示已经开启了profile支持，如果没有开启，可以通过如下指令进行开启：
set [session|global] profiling = 1;
（3）开关已经开启了，接下来我们执行的查询操作都将被记录下来；
select * from user;
select * from user where id = 1;
select * from user where name = '吕布';
select * from user where name = '吕不韦';
select count(*) from tb_sku;（4）profile指令
-- 查看每一条sql的耗时情况
show profiles;
-- 查看指定sql的耗时情况
show profile for query 68;
-- 查看指定query_id的sql语句的CPU使用情况
show profile cpu for query 103;
概述：
explain或者desc命令，用于获取MySQL是如何执行select语句的，包括select语句执行过程中表是如何连接以及连接顺序的。
语法：直接在select语句前加上explain或者desc指令即可。
例如：explain select * from user;
explain各字段的含义解释：
本系列文章前边使用了大面积篇幅介绍了索引的基本知识，那么索引它真的能够提升查询效率吗？以及它是如何提升查询效率的？带着这个疑问，我们先测试下相同的sql语句，在不使用索引和使用索引的情况下，查询效率有什么变化，通过对比即可知道索引对查询效率的影响。
先看一下表结构：
按照id查询，获得sn： select * from tb_sku where id = 1;
未建立索引的情况（根据sn查询）： select * from tb_sku where sn = '100000003145001';
sn字段建立索引后，再次查询：
create index idx_tb_sku_sn on tb_sku(sn);
select * from tb_sku where sn = '100000003145001';
结果分析 ：
（1）通过索引可以极大地提高查询效率（提高了 16.261 / 0.035 = 464.6倍！）；
（2）大数据量情况下，建立索引是一个非常耗时的操作；
概述：
如果一个表中存在联合索引，那么查询的字段包含联合索引对应的字段时，要遵循最左前缀法则。所谓最左前缀法则值得是查询从索引的最左列开始，并且不跳过索引中的列，如果跳跃某一列，索引将会部分失效（后面的字段索引失效）。先来回顾一下user表中之前建立的索引情况：
在user表中存在一个联合索引idx_user_profession_age_status，这个索引涉及到3个字段，顺序分别为：profession、age、status。对于最左前缀法则，查询时最左边的列，也就是profession必须存在，否则索引全部失效，而且中间不能跳过某一列，否则该列后面的字段索引将失效，接下来用一组案例验证一下我们的结论：
索引有效案例：
（1）explain select * from user where profession = '软件工程' and age = 31 and status = '0';
（2）explain select * from user where profession = '软件工程' and age = 31;
（3）explain select * from user where profession = '软件工程';
结果分析：
通过上述三组的演示，可以发现只要联合索引最左边的字段profession字段存在，索引就会生效，只不过索引的长度不同罢了，并且经过以上三组测试，也可以得出如下结论：
profession索引的长度为：47
age索引的长度为：2
status索引的长度为：5
索引失效案例：
（1）explain select * from user where age = 31 and status = '0';
（2）explain select * from user where status = '0';
结果分析：
通过上述两组的测试结果，可以看到索引并未生效，原因是不满足最左前缀法则，联合索引最左边的列profession不存在。
再来看几组测试：
（1）explain select * from user where profession = '软件工程' and status = '0';
（2） explain select * from user where status = '0' and profession = '软件工程';
结果分析：
通过上述两组的测试结果，可以知道由于profession字段存在，满足最左前缀法则，所以索引是生效的，但是查询时忽略了age这个字段，导致age和status字段对应的索引失效，也就是索引部分失效，另外对比（1）和（2），也可以得出结论：where后边的查询条件不一定非要按照联合索引的字段顺序写，只要满足最左前缀法则即可。
思考题：当执行 explain select * from user where age = 31 and status = '0' and profession = '软件工程'；时，是否满足最左前缀法则，走不走上述的联合索引，索引长度？
答：走，索引长度为54。
注意事项：最左前缀法则指的是联合索引中最左边的列，在查询时必须存在，与我们编写sql时，条件的编写顺序无关；
先说一组结论，然后再使用案例进行验证。 结论：
（一）联合索引中，出现范围查询（>,<）时，范围右侧的列索引将失效；
（二）联合索引中，出现范围查询（>=,<=）时，索引不会失效；
验证一：
explain select * from user where profession = '软件工程' and age > 30 and status = '0';
验证二：
explain select * from user where profession = '软件工程' and age >= 30 and status = '0';
小总结：
当表中存在联合索引时，如果想使用范围查询，而又不想让联合索引失效，尽量使用 >= 或者 <=，而避免使用 > 或者 < ！！！
除了上述演示的索引失效外，还有其他场景的索引失效，下面为大家一 一演示：
先看一下当前user表中的索引情况：
（一）索引列运算
结论：不要在索引列上进行运算操作， 否则索引将失效！！！
（二）字符串不加引号
结论： 字符串类型字段使用时，不加引号，虽然查询结果一致，但是由于数据库存在隐式类型转换，将会导致索引将失效！！！
（三）模糊查询
结论：如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引将失效！！！
（四）or连接条件
age字段建立索引后，再次执行上述查询语句；
create index idx_user_age on user(age);
结论： 当or连接的条件，左右两侧字段都有索引时，索引才会生效！！！
（五）数据分布影响
如果 MySQL 评估使用索引比全表更慢，则不使用索引。
结论：
通过上述测试我们发现，相同的SQL语句，只是传入的字段值不同，最终的执行计划也完全不一样，这是为什么呢？ 就是因为MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引走全表扫描。 因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效！！！
（六） is null 与 is not null
查询时MySQL 会评估，走索引快，还是全表扫描快，如果全表扫描更快，则放弃索引走全表扫描。 因此，is null 、 is not null 是否走索引，得具体情况具体分析，并不是固定的。
当前user表的数据情况和索引情况如下：
删除idx_user_age和idx_user_email索引：
（一） explain select * from user where profession = ' 软件工程 ';
结果： 查询走了联合索引
（二）创建profession的单例索引后再次查询 ：create index idx_user_profession on user(profession);
结果：
我们可以看到，possible_keys中idx_user_profession_age_status,idx_user_profession这两个索引都可能用到，但是最终MySQL选择了idx_user_profession_age_status索引。这是MySQL自动选择的结果。 那么，我们能不能在查询的时候，自己来指定使用哪个索引呢？ 答案是肯定的，此时就可以借助于 MySQL 的 SQL 提示来完成。 接下来介绍一下 SQL 提示：
概述：
SQL提示，是优化数据库的一个重要手段，简单来说，就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。常见命令如下：
（一）use index
指令解释： 建议MySQL使用哪一个索引来完成此次查询（注意：仅仅是建议，MySQL内部还是会再次评估）；
案例： explain select * from user use index(idx_user_profession) where profession = '软件工程';
（二）ignore index
指令解释： 忽略指定的索引
案例： explain select * from user ignore index(idx_user_profession) where profession = '软件工程';
（三） force index
指令解释： 强制使用索引
案例： explain select * from user force index(idx_user_profession) where profession = '软件工程';
尽量使用覆盖索引，减少select *的使用 。 那么什么是覆盖索引呢？ 所谓覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。
执行如下sql：
explain select id, profession from user where profession = '软件工程' and age =31 and status = '0' ;
explain select id,profession,age, status from user where profession = '软件工程'and age = 31 and status = '0' ;
explain select id,profession,age, status, name from user where profession = '软件工程' and age = 31 and status = '0' ;
explain select * from user where profession = '软件工程' and age = 31 and status= '0';
结果分析：
从上述的执行计划我们可以看到，这四条SQL 语句的执行计划前面所有的指标都是一样的，看不出来差 异。但是此时，我们主要关注的是后面的Extra ，前面两条 SQL 的结果为 Using where; Using Index ; 而后面两条 SQL 的结果为 : Using index condition，那么它们的区别是什么？请看下图：
因为，在user表中有一个联合索引idx_user_profession_age_status，该索引关联了三个字段profession、 age 、 status ，而这个索引也是一个二级索引，所以叶子节点下面挂的是这一行的主键id 。 所以当我们查询返回的数据在 id 、 profession 、 age 、 status 之中，则直接走二级索引直接返回数据了。 如果超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据了，这个过程就是回表。 而我们如果一直使用 select * 查询返回所有字段值，很容易就会造成回表查询（除非是根据主键查询，此时只会扫描聚集索引）。为了大家更清楚的理解，什么是覆盖索引，什么是回表查询，我们一起再来看下面的这组SQL 的执行过程。表结构示意图：
id 是主键，是一个聚集索引。 name 字段建立了普通索引，是一个二级索引（辅助索引）。（一）执行 SQL : select * from user where id = 2;根据 id 查询所有，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。（二）执行 SQL ： selet id,name from tb_user where name = 'Arm';虽然是根据name 字段进行查询二级索引，但是由于查询返回的字段为 id和 name ，在 name 的二级索引中，这两个值都是可以直接获取到的，因为覆盖索引，所以不需要回表查询，性能高。（三）执行 SQL ： selet id,name,gender from tb_user where name = 'Arm';由于在 name 的二级索引中，不包含 gender ，所以，需要两次索引扫描，也就是需要回表查询，性能相对较差一点。
当字段类型为字符串（varchar ， text ， longtext 等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO ， 影响查询效率。此时可以只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率。
语法： create index idx_xxxx on table_name(column(n)) ;
需求： 为user表的email字段建立长度为5的索引
create index idx_user_email on user(email(5));
可以根据索引的选择性来决定，所谓选择性是指不重复的索引值（基数）和数据表的记录总数的比值， 索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。select count ( distinct email) / count (*) from user ;select count ( distinct substring(email, 1 , 5 )) / count (*) from user ;
单列索引：即一个索引只包含单个列。联合索引：即一个索引包含了多个列。当前user表中的索引情况：从查询结果可以看出，红色框的为联合索引，紫色框的为单列索引
通过上述的执行计划我们可以看出，在and 连接的两个字段 phone 、 name 上都有单列索引，但是最终mysql只会选择一个索引，也就是说，只能走一个字段的索引，此时是会走回表查询的。小总结： 在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
如果查询使用的是联合索引，具体的结构示意图如下：
（1） 针对于数据量较大，且查询比较频繁的表建立索引。（2）针对于常作为查询条件（ where ）、排序（ order by ）、分组（ group by ）操作的字段建立索 引。（3） 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。（4）如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。（5）尽量使用联合索引，减少单列索引查询时，联合索引很多时候可以覆盖索引，节省存储空间， 避免回表，提高查询效率。（6） 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。（7） 如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。