mysql查询优化准则_优化MySQL数据库查询的技巧

最新推荐文章于 2022-05-19 20:57:10 发布

小狐狸与小道士

最新推荐文章于 2022-05-19 20:57:10 发布

阅读量136

点赞数

文章标签： mysql查询优化准则

本文链接：https://blog.csdn.net/weixin_30768881/article/details/113953603

版权

众所周知，几乎所有大型项目的最终效率瓶颈都来自于数据查询，不论是使用何种数据源。而在 MySQL 中，大部分效率低下的查询，都是因为没有正确的使用索引。可以说，合理有效的使用索引将会对 MySQL 的效率优化起到决定性的作用。

MySQL查询优化中必须遵循的几点原则

(1)索引是为了优化查询效率而存在的，正确的设置索引，将会使查询效率有质的飞跃。

(2)索引并不是设置的越多越好，设置过多的索引，将会大大的影响非 SELECT 查询的执行效率。

(3)将作为查询和排序条件次数最多的字段作为索引，是我们最常使用的设置原则。

MySQL查询优化技巧分享

索引也会被用来做其他的事情，比如我们经常使用唯一索引，来做一些特殊的规则限定，比如地图中的坐标唯一。不过我始终认为，这个应该是程序应该做的事情，而数据结构的限制，只应该是最后的保障。索引的使用，也存在一些“潜规则”，如果不弄清楚，很可能提高效率的愿望没有达到，查询反而变慢了。

以下的几种情况，对应字段的索引是无法生效的：

(1)查询条件里有不等号

SELECT * FROM `user` WHERE `id` != 2 这条SQL语句中，id 的索引将不会被使用。

(2)查询条件里使用了函数

SELECT * FROM `user` WHERE DAY(`regTime`) = 6 这条SQL语句中，id 的索引将不会被使用。

(3)在JOIN操作中，如果主键和外键的数据类型不同,则两个表的索引将都不会使用。

SELECT `C` . * , `U`.`name` AS `userName` FROM `t_user_city` AS `C` LEFT JOIN `user` AS `U` ON `U`.`id` = `C`.`userId` 中，如果 t_user 表的 id 字段和 t_user_city 表的 userId 字段数据类型不同，那么这两个字段的索引将不会被使用。

(4)如果查询条件里使用了比较操作符 LIKE 和 REGEXP，则只有在搜索模板的第一个字符不是通配符的情况下才能使用索引，如下面两条SQL语句：

SELECT * FROM `user` WHERE `name` LIKE '123%'

SELECT * FROM `user` WHERE `name` LIKE '%123'

结论：第一条查询语句能使用 name 的索引，而第二条查询却不行。

学会正确设置表索引

使用索引查询出的记录数量超过全表记录的30%，MySQL 会遍历全表，例如：

SELECT * FROM `user` WHERE `isNovice` = 1 中，t_user 表所有的记录都会被遍历，因为 isNovice 只有 0 和 1 这两个值，记录数各占 50%，设置 isNovice 为索引没有任何意义。

索引的优化是双向的，不仅仅是数据库结构如何设计、索引如何定义，在程序中如何构造查询语句也是至关重要的。

除了注意以上的几条规则以外，还有一些构造的技巧，也可以帮助你提升查询的效率，如果查询逻辑比较复杂，并且存在or的情况，请尝试使用 IN 代替 OR，例如下面两条查询语句：

SELECT * FROM `user` WHERE `id` = 2 OR `id` = 4 OR `id` = 6 OR `id` = 8 OR `id` = 10

SELECT * FROM `user` WHERE `id` IN (2, 4, 6, 8, 10)

结论：第二条的查询效率明显比第一条要高的多。