codetc - 网站开发技术 首页 后端 数据库 查看内容

优化MySQL数据库查询的技巧

2014-12-20 18:06| 发布者: CODETC| 查看: 1261| 评论: 0

众所周知,几乎所有大型项目的最终效率瓶颈都来自于数据查询,不论是使用何种数据源。而在 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)

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

避免在查询条件中使用函数
 
SELECT * FROM `user` WHERE DATE(`regTime`) = '2010-02-01'

SELECT * FROM `user` WHERE `regTime` > '2010-02-01' AND `regTime` < '2010-02-02'

结论:第二条查询的效率比第一条要高。

使用程序处理来代替数据库处理
 
SELECT `userId`, SQRT(POW(`unitX`, 2) + POW(`unitY`, 2)) AS `distance` FROM `t_map_unit` WHERE `userId` = 2

这条SQL语句中,计算距离的操作完全可以由程序来做,或者在需要的时候再用程序计算。这里让数据库来运算,是不理想的做法。

尽量避免使用联合查询

SELECT `C` . * , `U`.`name` AS `userName` FROM `t_user_city` AS `C` LEFT JOIN `user` AS `U` ON `U`.`id` = `C`.`userId`

SELECT * FROM `t_user_city`

SELECT `name` FROM `user` WHERE `id` IN (……)

我更建议使用后面两条查询取代第一条查询,实际上通过增加简单的程序处理就可以实现,但是效率上区别确是很大的,特别是当两个表的记录都很多的时候。两个简单的查询有时候比一个复杂的查询更快,这完全取决于查询语句的复杂度。

项目风险的控制
 
优化的过程是持续的,能及时的发现问题、解决问题,是控制项目风险的精要所在。
 
我们可以通过很多方法来发现数据库查询的种种问题。
 
比如频繁的 SHOW FULL PROCESSLIST,然后记录那些频繁出现或者出现 copy tmp table 的语句,并解决它们。
 
但是这毫无疑问是个笨方法,我们可以使用更便捷的方式记录这些有问题的语句。

修改 MySQL 配置,记录查询速度较慢的查询
 
在 my.cnf 中增加如下配置选项:
 
long_query_time = 1 
log-slow-queries = /data/mysql/slow.log
 
这样我们就能通过查看 /data/mysql/slow.log 这个文件,找到所有查询时间超过 1 秒的查询语句。

修改 MySQL 配置,记录没有使用索引的查询
 
在 my.cnf 中增加如下配置选项: 
 
log-slow-queries = /data/mysql/slow.log 
log-queries-not-using-indexes
 
这样 /data/mysql/slow.log 将会记录所有没有使用索引的查询语句。
 
两个选项可以一起使用,这样我们基本上就可以抓住大多数的“问题查询”了。
 
再针对这些查询进行分析和研究,修改查询或修改索引设置,最终让它们不再在这个日志文件中出现。
 
如果最终这个日志文件中除了必要的遍历查询以外再无其它内容,你的项目至少在数据库上已经没有效率问题了。
 
另外,对于频繁进行数据修改操作的表,索引可能会损坏。
 
索引损坏后,将不会起任何作用,周期性的检查和优化是非常必要的。
文章来源 CODETC,欢迎分享,转载请注明地址: http://www.codetc.com/article-99-1.html

最新评论

 作为游客发表评论,请输入您的昵称

返回顶部