MySQL灵魂拷问系列之MySQL如何优化


MySQL灵魂拷问系列之MySQL如何优化

1. SQL语句日常优化

  1. 通过skywalking发现慢sql、或者mysql中的慢查询日志(开启慢查询日志)
  2. 使用 explain 关键字查看sql执行计划
  3. type一般控制在 ref 甚至更好的话,sql性能应该都不错
  4. key_len:表示索引的长度,越小越好
  5. rows:扫描行数,很重要,最少越好
  6. 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. 索引优化

  1. 遵循 最左前缀原则
  2. 编写sql时避免 隐式转换
  3. 必要时建议使用 组合索引、覆盖索引

【个人索引优化笔记】
1:常见索引失效场景与解决方案
2:索引优化

4. limit优化

  1. 使用 覆盖索引 + join优化offset特别大的场景
  2. 模仿淘宝或者百度,对页面做限制(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即可。如果不是自增类型,就根据主键索引查询到分页信息的主键,再
根据主键查询数据。


评论
  目录