本文主要是介绍如何定位并优化慢查询SQL以及联合索引和索引多的弊端,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
以下思路均以Mysql为例,不过SQL调优通用的
根据慢日志定位慢查询SQL;
- 使用show variables like ‘%quer%’;主要查看slow_query_log,slow_query_log_file,long_query_time;这三个是主要关注和修改的变量。
- show status like ‘%slow_queries%’; ——查询慢查询的数量。
- set global slow_query_log = on; ——设置打开慢查询日志;set global long_query_time = 1; ——设置大于1s的查询都属于慢查询;( 这个需要重连数据库才能更新的);并且以上修改在重启数据库之后会重置成原来的设置,如果需要永久修改的话就需要到my.cmf(linux)或my.ini(windows)的配置文件到修改好对应的值。
使用explain等工具分析SQL;
在SQL语句前使用explain执行分析语句,优先观察三个字段:id,type,extra;(其他字段的作用请网上查询);其中id越大,代表越先执行,type为all则代表全表扫描,extra为using filesort或using temporary 都是可选优化的。
修改SQL或者尽量让SQL走索引。
这个主要是网上的SQL优化所说的具体内容了,可参考——https://www.cnblogs.com/lddbupt/p/5781831.html
创建联合索引
建表时:
CREATE TABLE test
(
‘a’ INT(11) NOT NULL DEFAULT ‘’,
‘b’ INT(11) NOT NULL DEFAULT ‘’,
‘c’ INT(11) NOT NULL DEFAULT ‘’,
‘d’ INT (11) UNSIGNED NOT NULL DEFAULT 0,
KEY sindex
(a
, b
, c
,‘d’)
) ENGINE = MyISAM COMMENT = ‘’;
建表后:
alert table test add INDEX sindex
(a
,b
,c
,‘d’) ;
最左匹配原则:
- mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就会停止匹配,比如a=3 and b=4 and c>5 and d=6 ,如果像上面一样建立(a,b,c,d)联合索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整;
- =和in可以乱序,比如a = 1 and b = 2 and c = 3建立(a,b,c)索引可以任意顺序,mysql的查询优化优化器会帮你优化成索引可以识别的形式;
成因:
mysql创建复合索引的规则是首先会对复合索引的最左边,也就是索引中的第一个字段进行排序,在第一个字段排序的基础上,在对索引上第二个字段进行排序,其实就像是实现类似order by 字段1,字段2这样的排序规则,那么第一个字段是绝对有序的,而第二个字段就是无序的了,因此一般情况下直接只用第二个字段判断是用不到索引的,这就是为什么mysql要强调联合索引最左匹配原则的原因。
索引是越多越好么
答案是否定的
数据量小的表不需要建立索引,建立会增加额外的索引开销
数据变更需要维护索引,意味着更多的索引意味着更多的维护成本
更多的索引也需要跟多的存储空间
这篇关于如何定位并优化慢查询SQL以及联合索引和索引多的弊端的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!