在日常开发中，我们往往会给表加各种索引，来提高 MySQL 的检索效率。但我们有时会遇到明明给字段加了索引，并没有走索引的Case。 进而导致 MySQL 产生慢查询。
严重场景下，甚至出现主从延迟、数据库拖垮的极端事故
1，建立user表并初始化
use usermanager;
create table userinfo(
uid int primary key auto_increment,
username varchar(250) not null,
loginname varchar(250) unique not null,
password varchar(65) not null,
sex varchar(2) default '男',
age int default 0,
address varchar(250) default '',
qq varchar(250) default '',
email varchar(250) default '',
isadmin bit default 0,
state int default 1,
createtime datetime default now(),
updatetime datetime default now()
) default charset='utf8mb4';
insert into userinfo(username,loginname,password,isadmin)
values('超级管理员','admin','admin',1);
insert into userinfo(username,loginname,password,isadmin)
values('张三','zhangsan','123456',0);
2，explain命令的使用
只要我们在 SQL 前加上 explain，就可以分析出，当前环境下 MySQL 的“查询方式”以及“索引选择”。
首先大致看下每个字段的含义：
type 列表示了 MySQL 关联的类型，它代表了mysql是如何在表里找数据的。
下面按性能从高到低的顺序介绍type类型：以下四种类型，说明 “性能很好，一般无需优化” ：
explain select * from userinfo where username=“张三”;
优化：根据业务场景，合理的建立相应的索引。
具体：索引使用和索引失效
select id,name,balance from account where create_time> ‘2020-09-19’ limit 100000,10;
这个SQL的执行流程：
原因
limit深分页，导致SQL变慢原因有两个：
就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描
select id,name,balance FROM account where id > 100000 limit 10;
这样的话，后面无论翻多少页，性能都会不错的，因为命中了id索引。但是这种方式有局限性：需要一种类似连续自增的字段。
select acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.create_time > '2020-09-19' limit 100000, 10) AS acct2 on acct1.id= acct2.id;
优化思路就是，先通过idx_create_time二级索引树查询到满足条件的主键ID，再与原表通过主键ID内连接，这样后面直接走了主键索引了，同时也减少了回表。
如果使用了in，即使后面的条件加了索引，还是要注意in后面的元素不要过多哈。in元素一般建议不要超过500个，如果超过了，建议分组，每次500一组进行哈。
反例：
select user_id,name from user where user_id in (1,2,3...1000000);
如果我们对in的条件不做任何限制的话，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。尤其有时候，我们是用的子查询。如下这种子查询：
select * from user where user_id in (select author_id from artilce where type = 1);
一般来说，不建议使用子查询，可以把子查询改成join来优化。而数据库有个规范约定就是：尽量不要有超过3个以上的表连接。
一方面，过多的表连接，会大大增加SQL复杂度。另外一方面，如果可以使用被驱动表的索引那还好，并且使用小表来做驱动表，查询效率更佳。如果被驱动表没有可用的索引，join是在join_buffer内存做的，如果匹配的数据量比较小或者join_buffer设置的比较大，速度也不会太慢。但是，如果join的数据量比较大时，mysql会采用在硬盘上创建临时表的方式进行多张表的关联匹配，这种显然效率就极低，本来磁盘的 IO 就不快，还要关联。
一般情况下，如果业务需要的话，关联2~3个表是可以接受的，但是关联的字段需要加索引哈。如果需要关联更多的表，建议从代码层面进行拆分，在业务层先查询一张表的数据，然后以关联字段作为条件查询关联表形成map，然后在业务层进行数据的拼装。
order by的文件排序，分为全字段排序和rowid排序。它是拿max_length_for_sort_data和结果行数据长度对比，如果结果行数据长度超过max_length_for_sort_data这个值，就会走rowid排序，相反，则走全字段排序。
order by使用文件排序，效率会低一点。我们怎么优化呢？
因为数据是无序的，所以就需要排序。如果数据本身是有序的，那就不会再用到文件排序啦。而索引数据本身是有序的，我们通过建立索引来优化order by语句。
我们还可以通过调整max_length_for_sort_data、sort_buffer_size等参数优化；
有时候，我们查询一条很简单的SQL，但是却等待很长的时间，不见结果返回。一般这种时候就是表被锁住了，或者要查询的某一行或者几行被锁住了。我们只能慢慢等待锁被释放。
这时候，我们可以用show processlist命令，看看当前语句处于什么状态
当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。一般有更新SQL才可能会导致脏页，我们回忆一下：一条更新语句是如何执行的
以下的这个更新SQL，如何执行的呢？
update t set c=c+1 where id=666
对于这条更新SQL，执行器会先找引擎取id=666这一行。如果这行所在的数据页本来就在内存中的话，就直接返回给执行器。如果不在内存，就去磁盘读入内存，再返回。
执行器拿到引擎给的行数据后，给这一行C的值加一，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，但是此时redo log 是处于prepare状态的哈。
执行器生成这个操作的binlog，并把binlog写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。
InnoDB 在处理更新语句的时候，只做了写日志这一个磁盘操作。这个日志叫作redo log（重做日志）。平时更新SQL执行得很快，其实是因为它只是在写内存和redo log日志，等到空闲的时候，才把redo log日志里的数据同步到磁盘中。
更新SQL只是在写内存和redo log日志，等到空闲的时候，才把redo log日志里的数据同步到磁盘中。这时内存数据页跟磁盘数据页内容不一致,我们称之为脏页。
InnoDB存储引擎的redo log大小是固定，且是环型写入的，如下图（图片来源于MySQL 实战 45 讲）：
那什么时候会刷脏页？有几种场景：
redo log写满了，要刷脏页。这种情况要尽量避免的。因为出现这种情况时，整个系统就不能再接受更新啦，即所有的更新都必须堵住。
内存不够了，需要新的内存页，就要淘汰一些数据页，这时候会刷脏页
InnoDB 用缓冲池（buffer pool）管理内存,而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。
MySQL 认为系统空闲的时候，也会刷一些脏页，MySQL 正常关闭时，会把内存的脏页都 flush 到磁盘上
redo log写满了，要刷脏页，这时候会导致系统所有的更新堵住，写性能都跌为0了，肯定慢呀。一般要杜绝出现这个情况。
一个查询要淘汰的脏页个数太多，一样会导致查询的响应时间明显变长