Mysql查询优化汇总 order by优化例子,group by优化例子,limit优化例子,优化建议
Mysql查询优化汇总 order by优化例子,group by优化例子,limit优化例子,优化建议
索引
索引是一种存储引擎快速查询记录的一种数据结构。
注意
- MYSQL一次查询只能使用一个索引,这个说法是不正确的,MYSQL会在两个索引列中,使用OR查询的时候,进行索引合并(index_merge;Using union(col1,col2);),但这种建立索引会使得索引数据的膨胀,不建议使用。如果对多个字段使用索引,建立使用复合索引。
冗余和重复索引
- Mysql需要单独维护重复的索引,并且优化查询的时候也需要逐个进行考虑,这会影响性能。
- 重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引,发现以后也应该立即移除。
例如:
- 在唯一限制和主键ID上建立索引,则是重复索引。Mysql的唯一限制和主键限制都是通过索引实现的。
- 如果创建了索引(A,B),再创建(A)索引,就是冗余索引,这只是一个索引的前缀索引。
- 如果创建了索引(A,B),再创建(B,A)则不是冗余索引。
- 大多数情况下都不需要冗余索引,应该尽量扩展已有的索引而不是创建新的索引。
- 解决冗余和重复索引的方法很简单,删除这些索引就可以了。可以使用Percona-Toolikt的pt-duplicate-key-checker检查重复索引。
索引的优点:
- 索引大大减少了服务器需要扫描的数据量。
- 索引可以帮助服务器避免排序和临时表。
- 索引可以将随机I/O变为顺序I/O。
CREATE TABLE `tab` (
`col1` int(11) DEFAULT NULL,
`col2` int(11) DEFAULT NULL,
`col3` int(11) DEFAULT NULL,
`col4` int(11) DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
KEY `col1_2` (`col1`,`col2`,`col3`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
insert into tab (col1, col2, col3) values (1,2,3,1),(4,5,6,1),(4,5,6,2),(4,5,6,3),(4,5,6,4),(4,5,6,3),(4,5,6,3);
CREATE TABLE `tab_1` (
`col1` int(11) DEFAULT NULL,
`col2` int(11) DEFAULT NULL,
`col3` int(11) DEFAULT NULL,
`col4` int(11) DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ORDER BY优化
- 在ORDER BY操作中,MYSQL只有在排序条件不是一个查询表达式的情况下才使用索引。
- 说明:表:tab,字段:col1,col2,col3,索引:INDEX(col1,col2,col3)。
- 在关联表查询(JOIN)中,order by第一张表会使用索引排序,如果是使用JOIN的话,由于优化器在优化时可能将第二个表当成第一张表即(RIGHT JOIN),那么实际上无法使用索引,如:desc select t.col1 from tab t left join tab_1 t1 on t.id = t1.id order by t.col1;。
能够使用到索引的情况
- desc select col1, col2, col3 from tab order by col1, col2, col3。
- desc select col1, col2 from tab where col1 = 1 order by col2。[有where的时候,前导列为常数的情况下可以用索引]
- desc select * from tab where col1 < 4 order by col1 desc。
- desc select * from tab where col1 = 10 and col2 > 10 order by col2 desc。
- desc select * from tab where col1 < 10 and col2 > 20 order by col1 desc。
不能够使用索引的情况
- desc select col1 from tab where col1 > 18 order by col2, col3。[col1是一个范围查询条件,而不是一个常数,无法使用索引]
- desc select * from tab where col1 > 1 order by col2, col1。[排序键顺序与索引中列顺序不一致,无法使用索引排序]
- desc select col1, col2 from tab order by col1 desc, col2 asc。[升序降序不一致,无法使用索引排序]
- desc select col1, col2 from tab where col1 > 1 order by col2。[条件1是范围查询,无法使用索引排序,具体和组合索引数据分布有关系]
- desc select * from tab where col1 = 1234 or col2 = 4321 order by col2。[当你逻辑表达式为OR时,使用索引排序会丢失,无法使用索引排序]
- desc select * from tab where col1 = 123 and col2 in (1,2) order by col3。[IN查询也是一种范围查询,无法使用索引排序]
- desc select * from tab where col1 = 123 order by col2, col4。[ORDER BY子句中引用了一个不在索引中的列,col4,无法使用索引排序]
LIMIT优化
- 对limit分页问题的性能优化方法-延迟关联
推荐使用“延迟关联”的方法来优化排序操作,何为“延迟关联”:通过使用覆盖索引查询返回需要的主键,然后再根据主键关联原表获得需要的数据来加速分页查询。
我们都知道,利用了索引查询的语句中如果只包含了那个索引列(覆盖索引),那么这种情况会查询很快。因为利用索引查找有优化算法,且数据就在查询索引上面,不用再去找相关的数据地址了,这样节省了很多时间。
例如
select * from ummor_user_detail ud order by user_id limit 300000,20 ;
优化写法1
select * from ummor_user_detail ud where user_id >= (select user_id from ummor_user_detail order by user_id limit 300000,1) limit 20 ;
优化写法2
select * from ummor_user_detail as a join (select user_id from ummor_user_detail order by user_id limit 300000,20 ) as b on a.user_id = b.user_id;
GROUP BY
MYSQL有三种索引扫描方式完成GROUP BY操作,分别是松散索引扫描和紧凑索引扫描以及临时表实现GROUP BY。
在松散索引扫描下,分组操作和范围预测(如果有的话)一起执行完成的。
在紧凑索引扫描下,先对索引执行范围扫描(range scan),再对结果元祖进行分组。
GROUP BY优化
- desc select * from tab where col1 > 1 group by col1, col2, col3, col4。[松散索引扫描]
- desc select * from tab where col2 > 1 group by col1, col3。[紧凑索引扫描,必须加where,在查询中存在常量相等的where条件字段(索引中的字段,且该字段在GROUP BY指定的字段的前面或者中间)]
- desc select * from tab where col1 > 1 group by col2, col3。
- desc select * from tab where col1 > 1 group by col3, col4。[临时表实现GROUP BY]
1. 松散索引扫描[Loose Index San]
使用松散索引扫描需要满足以下条件
- 查询在单一表上。
- GROUP BY指定的所有列是索引的一个最左前缀,并且没有其他的列。比如:表tab1(col1,col2,col3,col4)上建立了索引(col1,col2,col3)。如果查询包含“group by col1,col2”,那么可以使用松散索引扫描。但是“group by col2,col3”(不是最左前缀)和“group by col1,col2,col4”(col4字段不在索引中)无法使用。
- 如果在选择列表select list中存在聚集函数,只能使用MIN()和MAX()两个聚合函数,并且指定的事同一个列(如果min()和max()同时存在),这一列必须在索引中,且紧跟着GROUP BY指定的列。比如:select col1,col2,min(col3),max(col3) from tab group by col1, col2。
- 如果查询中存在除了group by指定的列之外的索引其他部分,那么必须以常量的形式出现(除了min()和max()两个聚集函数)。比如:select col1, col3 from tab group by col1, col2不能使用松散索引扫描。而select col1, col3 from tab where col3 = 3 group by col1, col2可以使用松散索引扫描。
2. 紧凑索引扫描(Tight Index Scan)
紧凑索引扫描可能是全索引扫描或者范围索引扫描,取决于查询条件。
紧凑索引扫描实现GROUP BY和松散索引扫描的区别主要在于他需要在扫描索引的时候,读取所有满足条件的索引键,然后再根据读取的数据来完成GROUP BY 操作得到相应结果。
3. 使用临时表实现GROUP BY
优化建议
- JOIN。
- 在ON字段上加索引,并且字段类型必须是相同的,否则MYSQL无法使用到它们的索引。
- 使用 ENUM 而不是 VARCHAR。
- 避免使用!=或<>、IS NULL或IS NOT NULL、IN ,NOT IN ORDER BY RAND 等这样的操作符。
- 把IP地址存成 UNSIGNED INT。
- 固定长度的表会更快。
- 垂直分割。
- “垂直分割”是一种把数据库中的表按列变成几张表的方法,这样可以降低表的复杂度和字段的数目,从而达到优化的目的。
- 例一:在Users表中有一个字段是家庭地址,这个字段是可选字段,相比起,而且你在数据库操作的时候除了个人信息外,你并不需要经常读取或是改写这个字段。那么,为什么不把他放到另外一张表中呢? 这样会让你的表有更好的性能,大家想想是不是,大量的时候,我对于用户表来说,只有用户ID,用户名,口令,用户角色等会被经常使用。小一点的表总是会有好的性能。
- 示例二: 你有一个叫 “last_login” 的字段,它会在每次用户登录时被更新。但是,每次更新时会导致该表的查询缓存被清空。所以,你可以把这个字段放到另一个表中,这样就不会影响你对用户ID,用户名,用户角色的不停地读取了,因为查询缓存会帮你增加很多性能。
- 另外,你需要注意的是,这些被分出去的字段所形成的表,你不会经常性地去Join他们,不然的话,这样的性能会比不分割时还要差,而且,会是极数级的下降。
- 拆分大的 DELETE 或 INSERT 语句。
- 如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询,你需要非常小心,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。
- 越小的列会越快。建议:布尔/枚举:tinyint,日期与时间戳:timestamp或int,char/text/blob: 尽量用符合实际长度的varchar(n),小数及货币:移位转为int 或 decimal,IP地址:int。
- 在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。
- 尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。
- NOT IN、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法。
- 能用UNION ALL就不要用UNION,需知道两者区别。
- 尽量避免使用or,会导致数据库引擎放弃索引进行全表扫描。
- SELECT * FROM t WHERE id = 1 OR id = 3
- 优化方式:可以用union代替or。如下:
- SELECT * FROM t WHERE id = 1 UNION SELECT * FROM t WHERE id = 3
- 能够用BETWEEN的就不要用IN。
- 能够用DISTINCT的就不用GROUP BY。
- 尽量不要用SELECT INTO语句。SELECT INTO 语句会导致表锁定,阻止其他用户访问该表。
- UPDATE操作不要拆成DELETE操作+INSERT操作的形式,虽然功能相同,但是性能差别是很大的。
- 索引字段上进行运算会使索引失效。
数据表结构优化建议
根据数据分析建议来修改表结构,使之更符合数据存储规范:PROCEDURE ANALYSE(1)。
- 如:select * from tab PROCEDURE ANALYSE(1);分析出每个字段。
编写MYSQL习惯
1. SELECT * from TABLE1 和 SELECT * FROM TABLE1
- 上面的两条SQL语句对于查询缓冲是完全不同的SELECT。而且查询缓冲并不自动处理空格,因此,在写SQL语句时,应尽量减少空格的使用,尤其是在SQL首和尾的空格(因为,查询缓冲并不自动截取首尾空格)。
2. 关键字大写。如:SELECT,LIKE,OR。
例子:
desc select col1, col2 from tab where col1 = 1 or col2 = 1; # index col1, index col2 索引合并,不建议
desc select * from tab where col1 = 1 or col2 = 2; # index col1, index col2,使用col1索引,索引合并,不建议使用
desc select col1, col2 from tab where col1 = 1 or col2 = 1; # index col1 col3, index col2 没有使用到索引,不是独立索引列
desc select * from tab where col1 = 1 or col2 = 2; # index col1 col3, index col2 没有使用到索引,不是独立索引列
desc select col1,col2 from tab where col1 = 1 or col2 = 1; # index col1, col2 使用到了索引
desc select * from tab where col1 = 1 or col2 = 1; # index col1, col2 没有使用到索引
对比下面
desc
select * from tab where col1 = 1
union all
select * from tab where col2 = 2; # 使用这个策略是第一条语句能够使用到索引,而第二条不行,这样就有一半的数据还是能够使用到索引,而不是整条语句使用不到索引。如果col2也有做索引列的话,那么两条均能使用到索引查询。