mysql5.6优化总结

https://www.parkjun.club/post/5

前提&＃xff1a;所有实验操作是基于mysql5.6&＃xff0c;其他版本可能有差异&＃xff0c;届时以具体的情况为准。

1、where后面的条件字段需添加索引&＃xff0c;避免全表扫描。

2、连表查询时&＃xff0c;关联字段需建立索引&＃xff0c;并且应该保证关联字段的类型一致&＃xff0c;避免类型转换。如&＃xff1a; SELECT * FROM order_goods a INNER JOIN order_info b ON a.order_id &＃61; b.id 这个sql里面应该给order_id 建立索引&＃xff0c;id字段是主键&＃xff0c;自带索引。 但这个sql是不规范并且不被推荐的&＃xff1a;order_id是varchar类型&＃xff0c;id是 int类型&＃xff0c;出现了类型转换。

3、索引过多会影响insert和update性能&＃xff08;这两个动作会重建索引&＃xff09;&＃xff0c;一般来说一张表索引数建议不要超过6个&＃xff0c; 当然得根据实际的业务场景&＃xff1a;比如你要优化的表平时做什么操作居多&＃xff0c;是查询还是插入更新&＃xff0c;查询的地方有效率要求没&＃xff0c;插入更新的地方对及时 反馈有要求没等等。

4、用Navivat建索引时&＃xff0c;索引方法不管你选啥&＃xff0c;保存后展示给我们的都是btree&＃xff0c;这个不用担心。Mysql会根据添加索引的字段类型自动适配合适的索引 方法&＃xff1a;数字型的为btree&＃xff0c;字符型的为hash。

5、不要建立联合索引&＃xff0c;这种索引的使用场景有明显的局限性。

6、Where条件后面用不等于&＃xff08;<>以及&＃xff01;&＃61;&＃xff09;会造成条件字段索引失效&＃xff0c;包含字符型和数字型。

7、对字符型字段使用<、<&＃61;、>、>&＃61;会造成索引失效&＃xff0c;数字型字段则不会。

8、Between 对字符型字段使用会造成索引失效&＃xff0c;数字型字段则会使用索引。

9、In、or 关键字是要使用索引的&＃xff0c;请放心使用。

10、用or的地方尽量替换成union all&＃xff0c;mysql查询引擎有相关优化。

11、Null值处理&＃xff1a;对一个字段使用is null 进行判断&＃xff0c;是会使用索引的&＃xff0c;但是is not null 会造成索引失效&＃xff0c;谨慎使用。

12、Like关键字&＃xff1a;对数字类型的字段来说&＃xff0c;只要使用like&＃xff0c;均会造成索引失效。对字符类型的字段来说&＃xff0c;field like ‘value%’这种写法会使用索引&＃xff0c;其他写法均为造成索引失效。

13、不要在where 子句中的“&＃61;”左边进行函数、算术运算或其他表达式运算&＃xff0c;否则系统将可能无法正确使用索引。

如&＃xff1a;select id from t where num/2&＃61;100 select id from t where ifnull&＃xff08;num&＃xff0c;0&＃xff09;&＃61;0 这两个均会造成索引失效。

14、关键字order by 后面的字段不会使用索引&＃xff0c;但是会消耗较多的cpu资源&＃xff1b;数据量达到一定量级的时候会有很明显的延迟&＃xff0c;尽量避免排序。

15、尽量避免 select *&＃xff0c;这虽然不会对数据库造成很明显的性能压力&＃xff0c;但会消耗大量的io。

16、尽量用 union all 代替 union&＃xff1b; union 和 union all 的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作&＃xff0c;这就会涉及到排序&＃xff0c;增加大量的 CPU 运算&＃xff0c;加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候&＃xff0c;尽量使用 union all 而不是 union。

17、尽量提前过滤&＃xff1b; &＃xff08;1&＃xff09;、在 SQL 编写中同样可以使用这一原则来优化一些 Join 的 SQL。比如我们在多个表进行分页数据查询的时候&＃xff0c;我们最好是能够在一个表上先过滤好数据分好页&＃xff0c;然后再用分好页的结果集与另外的表 Join&＃xff0c;这样可以尽可能多的减少不必要的 IO 操作&＃xff0c;大大节省 IO 操作所消耗的时间。 &＃xff08;2&＃xff09;、On条件的执行在where的执行之前&＃xff1a;on 条件里先给出尽可能多的匹配满足条件:如下 select * from A inner join B on B.name &＃61; A.name left join C on C.name &＃61; B.name left join D on D.id &＃61; C.id where C.status>1 and D.status&＃61;1; 效率明显不如 select * from A inner join B on B.name &＃61; A.name left join C on C.name &＃61; B.name and C.status>1 left join D on D.id &＃61; C.id and D.status&＃61;1

18、正确理解left join、right join、inner join的区别&＃xff0c;根据场景选择正确的连接方式。

外部连接有主表与从表&＃xff0c;主表在left中是左侧表&＃xff0c;right中是右侧表&＃xff0c;主表数据会全部显示&＃xff0c;从表数据则只显示关联部分匹配的数据&＃xff0c;无匹配的数据用null补全。 内连接则只显示两表关联条件匹配的数据。

19、尽量少用或不用子查询&＃xff0c;用其他方式代替。如 join、union等 &＃xff08;因为一个子查询&＃xff0c;mysql查询时候就会多建立一个零时表&＃xff09;&＃xff1b; 20、学会使用explain。