Mysql调优的顺序及面试问题总结

1.第一步&＃xff1a;本地explain&＃43;线上查询

在开发涉及SQL的业务都会去本地环境跑一遍SQL&＃xff0c;用explain去看一下执行计划&＃xff0c;看看分析的结果是否符合自己的预期(type是否为eq_ref、ref)&＃xff0c;用没用到相关的索引&＃xff08;possible_keys和key是不是想要的&＃xff09;&＃xff0c;然后再去线上环境跑一下看看执行时间&＃xff08;这里只有查询语句&＃xff0c;修改语句也无法在线上执行&＃xff09;。

• eq_ref &＃xff1a;对于前表的每一行&＃xff0c;后表只有一行被扫描
• ref&＃xff1a;对于前表的每一行&＃xff0c;后表可能有多于一行的数据被扫描

遇到的第一个坑&＃xff1a;

因为在MySQL8.0之前我们的数据库是存在缓存这样的情况的&＃xff0c;我之前就被坑过&＃xff0c;因为存在缓存&＃xff0c;我发现我sql怎么执行都是很快&＃xff0c;当然第一次其实不快但是我没注意到&＃xff0c;以至于上线后因为缓存经常失效&＃xff0c;导致rt&＃xff08;Response time&＃xff09;时高时低。

解决方法&＃xff1a;SQL NoCache去跑SQL

原因&＃xff1a;缓存失效比较频繁的原因就是&＃xff0c;只要我们一对表进行更新&＃xff0c;那这个表所有的缓存都会被清空&＃xff0c;其实我们很少存在不更新的表。

遇到的第二个坑&＃xff1a;

• 统计的行数就是完全对吗&＃xff1f;

1&＃xff09;MySQL中数据的单位都是页&＃xff0c;MySQL又采用了采样统计的方法&＃xff0c;采样统计的时候&＃xff0c;InnoDB默认会选择N个数据页&＃xff0c;统计这些页面上的不同值&＃xff0c;得到一个平均值&＃xff0c;然后乘以这个索引的页面数&＃xff0c;就得到了这个索引的基数。
2&＃xff09;我们数据是一直在变的&＃xff0c;所以索引的统计信息也是会变的&＃xff0c;会根据一个阈值&＃xff0c;重新做统计。

解决方法&＃xff1a;analyze table tablename 就可以重新统计索引信息了&＃xff0c;所以在实践中&＃xff0c;如果你发现explain的结果预估的rows值跟实际情况差距比较大&＃xff0c;可以采用这个方法来处理。

• 索引一定会走到最优索引么&＃xff1f;

1&＃xff09;如果走A索引要扫描100行&＃xff0c;B所有只要20行&＃xff0c;但是他可能选择走A索引
2&＃xff09;一般走错都是因为优化器在选择的时候发现&＃xff0c;走A索引没有额外的代价&＃xff0c;比如走B索引并不能直接拿到我们的值&＃xff0c;还需要回到主键索引才可以拿到&＃xff0c;多了一次回表的过程&＃xff0c;这个也是会被优化器考虑进去的。

解决方法&＃xff1a;还有一个方法就是force index强制走正确的索引&＃xff0c;或者优化SQL&＃xff0c;最后实在不行&＃xff0c;可以新建索引&＃xff0c;或者删掉错误的索引。

2.第二步&＃xff1a;覆盖索引

1&＃xff09;说明&＃xff1a;

可能需要回表这样的操作&＃xff0c;那我们怎么能做到不回表呢&＃xff1f;在自己的索引上就查到自己想要的&＃xff0c;不要去主键索引查了。

• 如果在我们建立的索引上就已经有我们需要的字段&＃xff0c;就不需要回表了&＃xff0c;比如在电商里面&＃xff0c;我们需要去商品表通过各种信息查询到商品id&＃xff0c;id一般都是主键&＃xff0c;可能sql类似这样&＃xff1a;
  select itemId from itemCenter where size between 1 and 6
  

• 因为商品id itemId一般都是主键&＃xff0c;在size索引上肯定会有我们这个值&＃xff0c;这个时候就不需要回主键表去查询id信息了。

• 由于覆盖索引可以减少树的搜索次数&＃xff0c;显著提升查询性能&＃xff0c;所以使用覆盖索引是一个常用的性能优化手段。
  &＃xff08;覆盖索引一般针对的是辅助索引&＃xff0c;整个査询结果只通过辅助索引就能拿到结果&＃xff0c;不需要通过辅助索引树找到主键&＃xff0c;再通过主键去主键索引树里获取其它字段值。这个辅助索引可以是组合索引&＃xff09;
  举例传送门
  假设你定义一个联合索引

CREATE INDEX idx_name_age ON user(name,age);


查询名称为 liudehua 的年龄&＃xff1a;

mysql> select name, age from user where name &＃61; &＃39;liudehua&＃39;;


上述语句中&＃xff0c;查找的字段 name 和 age 都包含在联合索引 idx_name_age 的索引树中&＃xff0c;这样的查询就是覆盖索引查询。

3.第三步&＃xff1a;联合索引

• 还是商品表举例&＃xff0c;我们需要根据他的名称&＃xff0c;去查他的库存&＃xff0c;假设这是一个很高频的查询请求&＃xff0c;你会怎么建立索引呢&＃xff1f;
• 解决方法&＃xff1a;

建立一个&＃xff0c;名称和库存的联合索引&＃xff0c;这样名称查出来就可以看到库存了&＃xff0c;不需要查出id之后去回表再查询库存了&＃xff0c;联合索引在我们开发过程中也是常见的&＃xff0c;但是并不是可以一直建立的&＃xff0c;大家要思考索引占据的空间。

4.第四步&＃xff1a;最左匹配原则

最好能利用到现有的SQL最大化利用&＃xff0c;像上面的场景&＃xff0c;如果利用一个模糊查询 itemname like ’谢白羽%‘&＃xff0c;这样还是能利用到这个索引的&＃xff0c;而且如果有这样的联合索引&＃xff0c;大家也没必要去新建一个商品名称单独的索引了。

指的是联合索引中&＃xff0c;优先走最左边列的索引。对于多个字段的联合索引&＃xff0c;也同理。如 index(a,b,c) 联合索引&＃xff0c;则相当于创建了 a 单列索引&＃xff0c;(a,b)联合索引&＃xff0c;和(a,b,c)联合索引。

5.第五步&＃xff1a;索引下推

select * from itemcenter where name like &＃39;谢%&＃39; and size&＃61;22 and age &＃61; 20;


• 所以这个语句在搜索索引树的时候&＃xff0c;只能用 “谢”&＃xff0c;找到第一个满足条件的记录ID1&＃xff0c;当然&＃xff0c;这还不错&＃xff0c;总比全表扫描要好。

• 在MySQL 5.6之前&＃xff0c;只能从ID1开始一个个回表&＃xff0c;到主键索引上找出数据行&＃xff0c;再对比字段值。
  

• 而MySQL 5.6 引入的索引下推优化&＃xff08;index condition pushdown)&＃xff0c; 可以在索引遍历过程中&＃xff0c;对索引中包含的字段先做判断&＃xff0c;直接过滤掉不满足条件的记录&＃xff0c;减少回表次数。
  

6.唯一索引普通索引选择难题

• 当需要更新一个数据页时&＃xff0c;如果数据页在内存中就直接更新&＃xff0c;而如果这个数据页还没有在内存中的话&＃xff0c;在不影响数据一致性的前提下&＃xff0c;InooDB会将这些更新操作缓存在change buffer中&＃xff0c;这样就不需要从磁盘中读入这个数据页了。

• 什么条件下可以使用change buffer呢&＃xff1f;

要判断表中是否存在这个数据&＃xff0c;而这必须要将数据页读入内存才能判断&＃xff0c;如果都已经读入到内存了&＃xff0c;那直接更新内存会更快&＃xff0c;就没必要使用change buffer了。因此&＃xff0c;唯一索引的更新就不能使用change buffer&＃xff0c;实际上也只有普通索引可以使用。

• change buffer的大小&＃xff0c;可以通过参数innodb_change_buffer_max_size来动态设置&＃xff0c;这个参数设置为50的时候&＃xff0c;表示change buffer的大小最多只能占用buffer pool的50%。将数据从磁盘读入内存涉及随机IO的访问&＃xff0c;是数据库里面成本最高的操作之一.change buffer因为减少了随机磁盘访问&＃xff0c;所以对更新性能的提升是会很明显的。

7.第七步&＃xff1a;前缀索引

• 我们存在邮箱作为用户名的情况&＃xff0c;每个人的邮箱都是不一样的&＃xff0c;那我们是不是可以在邮箱上建立索引&＃xff0c;但是邮箱这么长&＃xff0c;我们怎么去建立索引呢&＃xff1f;
• MySQL是支持前缀索引的&＃xff0c;也就是说&＃xff0c;你可以定义字符串的一部分作为索引。默认地&＃xff0c;如果你创建索引的语句不指定前缀长度&＃xff0c;那么索引就会包含整个字符串。

但是&＃xff1a;

但是前缀索引&＃xff0c;即使你的联合索引已经包涵了相关信息&＃xff0c;他还是会回表&＃xff0c;因为他不确定你到底是不是一个完整的信息&＃xff0c;就算你是www.aobing&＃64;mogu.com一个完整的邮箱去查询&＃xff0c;他还是不知道你是否是完整的&＃xff0c;所以他需要回表去判断一下。

解决方法&＃xff1a;

你可以substring()函数截取掉前面的&＃xff0c;然后建立索引。hash&＃xff0c;把字段hash为另外一个字段存起来&＃xff0c;每次校验hash就好了&＃xff0c;hash的索引也不大。

8.第八步&＃xff1a;条件字段函数操作

• 对索引字段做函数操作&＃xff0c;可能会破坏索引值的有序性&＃xff0c;因此优化器就决定放弃走树搜索功能。
• 这个时候大家可以用一些取巧的方法&＃xff0c;比如 select * from tradelog where id &＃43; 1 &＃61; 10000 就走不上索引&＃xff0c;select * from tradelog where id &＃61; 9999就可以。

9.第九步&＃xff1a;防止类型隐式转换

select * from t where id &＃61; 1


• 如果id是字符类型的&＃xff0c;1是数字类型的&＃xff0c;你用explain会发现走了全表扫描&＃xff0c;根本用不上索引&＃xff0c;为啥呢&＃xff1f;

• 因为MySQL底层会对你的比较进行转换&＃xff0c;相当于加了 CAST( id AS signed int) 这样的一个函数&＃xff0c;上面说过函数会导致走不上索引。

10.第十步&＃xff1a;隐式字符编码转换

• 还是一样的问题&＃xff0c;如果两个表的字符集不一样&＃xff0c;一个是utf8mb4&＃xff0c;一个是utf8&＃xff0c;因为utf8mb4是utf8的超集&＃xff0c;所以一旦两个字符比较&＃xff0c;就会转换为utf8mb4再比较。
• 转换的过程相当于加了CONVERT(id USING utf8mb4)函数&＃xff0c;那又回到上面的问题了&＃xff0c;用到函数就用不上索引了。
• 还有大家一会可能会遇到mysql突然卡顿的情况&＃xff0c;那可能是MySQLflush了。

11.第十一步&＃xff1a;flush

• redo log大家都知道&＃xff0c;也就是我们对数据库操作的日志&＃xff0c;他是在内存中的&＃xff0c;每次操作一旦写了redo log就会立马返回结果&＃xff0c;但是这个redo log总会找个时间去更新到磁盘&＃xff0c;这个操作就是flush。

• Innodb刷脏页控制策略&＃xff0c;我们每个电脑主机的io能力是不一样的&＃xff0c;你要正确地告诉InnoDB所在主机的IO能力&＃xff0c;这样InnoDB才能知道需要全力刷脏页的时候&＃xff0c;可以刷多快。

• 这就要用到innodb_io_capacity这个参数了&＃xff0c;它会告诉InnoDB你的磁盘能力&＃xff0c;这个值建议设置成磁盘的IOPS&＃xff0c;磁盘的IOPS可以通过fio这个工具来测试。正确地设置innodb_io_capacity参数&＃xff0c;可以有效的解决这个问题。

• 补充&＃xff1a;这中间有个有意思的点&＃xff0c;刷脏页的时候&＃xff0c;旁边如果也是脏页&＃xff0c;会一起刷掉的&＃xff0c;并且如果周围还有脏页&＃xff0c;这个连带责任制会一直蔓延&＃xff0c;这种情况其实在机械硬盘时代比较好&＃xff0c;一次IO就解决了所有问题&＃xff0c;

1&＃xff09;B树和B&＃43;树的区别&＃xff0c;为什么mysql使用B&＃43;树&＃xff1f;

1&＃xff09;B树&＃xff1a;
①一个节点有多个元素
②整个树都是排好序的
2&＃xff09;B&＃43;树&＃xff1a;
①叶子节点是有指针的
②一个节点有多个元素
&＃xff08;用这种存储结构来存储大量数据的情况下呢&＃xff0c;它的整体高度相比二叉树来说比较低&＃xff0c;而对于数据库来说&＃xff0c;所有的数据存储必然是存储在磁盘上的而磁盘io的效率事件上是很低的&＃xff0c;特别是随机磁盘的一个情况下效率更低&＃xff0c;所以树的高度决定磁盘io一个次数&＃xff0c;磁盘io次数越少&＃xff0c;那么对性能的提升就会越大&＃xff0c;采用b树作为索引存储结构的原因&＃xff0c;&＃xff09;
③整个树都是排好序的
③非叶子节点在叶子节点的元素都冗余了一份

2&＃xff09;mysql有哪些存储引擎&＃xff1f;

①InnoDB是mysql默认事物型引擎&＃xff0c;也是最广泛的存储引擎&＃xff0c;被设计来处理大量短期事务
②MyISAM是5.1及之前版本的默认存储引擎&＃xff0c;但是不支持事务和行级锁&＃xff0c;且崩溃后无法安全恢复。同时MyISAM对表加锁&＃xff0c;很容易因为表锁的问题导致典型的性能问题。
③Memory引擎&＃xff1a;至少比MyISAM表要快一个数量级&＃xff0c;数据文件是存储在内存中&＃xff0c;查找和映射比较快。表结构在重启后还会保留&＃xff0c;但是数据会丢失。
④Archive引擎&＃xff1a;只支持INSERT和SELECT操作&＃xff0c;会缓存所有的写并利用zlib对插入的行进行压缩&＃xff0c;所以比MyISAM表的磁盘IO更少。但是每次SELECT查询都需要执行全表扫描,所以一般是用于日志或数据采集类存储
⑤CSV引擎&＃xff1a;将普通的CSV文件作为MYSQL的表来处理&＃xff0c;但这个表不支持索引&＃xff0c;但是可以作为数据交换的机制

3&＃xff09;MyISAM和InnoDB的区别是什么&＃xff1f;

①InnoDB支持事务&＃xff0c;MyISAM不支持事务
②InnoDB可以包含外键&＃xff0c;但是MyISAM不支持
③InnoDB是聚簇索引&＃xff0c;MyISAM是非聚簇索引
InnoDB使用辅助索引的时候&＃xff0c;如果主键很大&＃xff0c;那么其他索引也会很大&＃xff0c;因为辅助索引需要两次查询&＃xff0c;他存的是主键的信息&＃xff0c;然后再根据主键去查询数据
④InnoDB不保存行数&＃xff0c;数表行时是全表扫描
⑤InnoDB最小锁粒度是行锁&＃xff0c;MyISAM最小锁粒度是表锁

4&＃xff09;什么叫回表&＃xff1f;

一次性select不能拿到所有列的数据&＃xff0c;还需要到表中再去查找列的数据&＃xff0c;就叫回表

5&＃xff09;什么叫聚簇索引&＃xff1f;

比如说以主键建立的B&＃43;树&＃xff0c;叶子节点存储的是对应行的数据&＃xff0c;而不是指向另外一块内存&＃xff08;该内存存储对应行数据&＃xff09;的指针&＃xff0c;存指针叫做非聚簇索引&＃xff08;或叫辅助索引&＃xff09;

6&＃xff09;什么是索引覆盖&＃xff1f;怎么实现&＃xff1f;

定义&＃xff1a;执行某个查询语句&＃xff0c;在一棵索引树上就能获取SQL所需的所有列数据&＃xff0c;无需回表
怎么实现&＃xff1a;将查询的字段建立到联合索引里面去

7&＃xff09;谈谈联合索引生效的条件和失效的条件&＃xff1f;

1、创建联合索引时应仔细考虑列的顺序&＃xff08;知道姓和名更为有用&＃xff09;
2、避免索引失效条件&＃xff1a;
①不在索引列上做任何操作&＃xff0c;包括不限于计算、函数、自动或手动类型转换
②存储引擎不能使用索引范围条件右侧的列&＃xff08;左侧优先&＃xff09;
③尽量使用索引覆盖&＃xff08;索引和查询列一致&＃xff09;
④mysql在使用(!&＃61;,>,<)的时候无法使用索引
⑤is null &＃xff0c;is not null也无法使用索引
⑥like以通配符开头&＃xff0c;即’%ABC’

8&＃xff09;什么是索引下推&＃xff1f;

根据条件查询的过程中&＃xff0c;再返回server之前就根据比如说联合索引查询的条件过滤了一部分数据&＃xff0c;这样在返回数据库server层的时候就减少了回表的次数&＃xff08;5.6及5.6以上版本&＃xff09;
传送门