MySQL灵魂拷问系列之MySQL如何优化
1. SQL语句日常优化
- 通过skywalking发现慢sql、或者mysql中的慢查询日志(开启慢查询日志)
- 使用 explain 关键字查看sql执行计划
- type一般控制在 ref 甚至更好的话,sql性能应该都不错
- key_len:表示索引的长度,越小越好
- rows:扫描行数,很重要,最少越好
- extra:当查询包含 using filesort 文件排序和 using temporary临时表(使用order/group by很容易出现),这样的sql必须优化
2. 表结构优化
在遵循三大范式的基础上,可以为了业务增加一定的冗余字段
个人笔记:表结构设计优化
2. join优化
(1)灵活使用左连接、右连接等
- left join:A表的全部数据,即使B表不包含A表的关系数据
- right join:B表的全部数据,即使B表不包含A表的关系数据
- inner join:A表和B表的交集
- full outer join:A表和B表并集
- cross join:笛卡尔连接,把A表的数据和B表的数据任意连接,结果行数 = A表行数 *B表行数;如果cross join带有on语句把两表的外键相连,则作用等于inner join
(2)使用join时,用数据量比较小的作为驱动表,用小表驱动大表,where条件尽量走索引,参与join的表不能超过3张
个人join优化笔记:join优化
3. 索引优化
- 遵循 最左前缀原则
- 编写sql时避免 隐式转换
- 必要时建议使用 组合索引、覆盖索引
【个人索引优化笔记】
1:常见索引失效场景与解决方案
2:索引优化
4. limit优化
- 使用 覆盖索引 + join优化offset特别大的场景
- 模仿淘宝或者百度,对页面做限制(100页)
个人limit优化笔记:limit优化
5. 个人小结笔记
(1)表结构设计方面:首先需要遵循三大范式,字段不能再分割,非主键属性 https://zhuanlan.zhihu.com/p/259788257
a.字段不能再分割,比如只有一个地址字段,到时候如果按照省统计时就会非常头疼
b.每行记录要有唯一标识。比如学号、姓名、课程、课程分数,这一条记录就包含了学生、课程信息,主键无法保证唯一性,两个应该分开
c.不能存在冗余。比如学号、姓名、学院、学院电话。这里学院电话每行都有,肯定会存在冗余的情况。应该分开
d.适当加一些冗余字段,比如订单表记录商品金额,为的是保存当时商品信息快照,因为不同时期的商品价格可能发生变化
(2)表字段设计方面:字段尽量不允许为null,字段选择最适合的,比如性别、订单状态这样的字段可以使用tinyint,使用varchar可变的
字符串代替char等
(3)sql优化:通过skywalking或者mysql慢查询日志查询慢sql,通过explain关键字查询他的执行计划,保证type控制在ref,rows扫描行数
越少越好,extra列不出现临时表和文件排序;
a.join优化:遵循数据量小的表来驱动大表,where的条件尽量走索引,参与join的表最好不超过3张
b.索引优化:遵循最左前缀原则,避免隐式转换,否则可能造成索引失效。建议使用复合索引
c.limit优化:如果进行深度分页的话,比如10000,10,mysql的具体操作是查找10010条数据,然后丢弃前10000条,取后10条,性能很低。
这种情况,如果主键的自增类型,则直接可以让id>10000,limit 10即可。如果不是自增类型,就根据主键索引查询到分页信息的主键,再
根据主键查询数据。