Mysql查询优化汇总 order by优化例子，group by优化例子，limit优化例子，优化建议

时间：2022-08-06 14:21:17

Mysql查询优化汇总 order by优化例子，group by优化例子，limit优化例子，优化建议

索引

索引是一种存储引擎快速查询记录的一种数据结构。

注意

  • MYSQL一次查询只能使用一个索引，这个说法是不正确的，MYSQL会在两个索引列中，使用OR查询的时候，进行索引合并(index_merge;Using union(col1,col2);)，但这种建立索引会使得索引数据的膨胀，不建议使用。如果对多个字段使用索引，建立使用复合索引。
 

冗余和重复索引

  • Mysql需要单独维护重复的索引，并且优化查询的时候也需要逐个进行考虑，这会影响性能。
  • 重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。

例如：

  1. 在唯一限制和主键ID上建立索引，则是重复索引。Mysql的唯一限制和主键限制都是通过索引实现的。
  2. 如果创建了索引（A，B），再创建（A）索引，就是冗余索引，这只是一个索引的前缀索引。
  3. 如果创建了索引（A，B），再创建（B，A）则不是冗余索引。
  • 大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新的索引。
  • 解决冗余和重复索引的方法很简单，删除这些索引就可以了。可以使用Percona-Toolikt的pt-duplicate-key-checker检查重复索引。
 

索引的优点：

  1. 索引大大减少了服务器需要扫描的数据量。
  2. 索引可以帮助服务器避免排序和临时表。
  3. 索引可以将随机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;。

能够使用到索引的情况

  1. desc select col1, col2, col3 from tab order by col1, col2, col3。
  2. desc select col1, col2 from tab where col1 = 1 order by col2。[有where的时候，前导列为常数的情况下可以用索引]
  3. desc select * from tab where col1 < 4 order by col1 desc。
  4. desc select * from tab where col1 = 10 and col2 > 10 order by col2 desc。
  5. desc select * from tab where col1 < 10 and col2 > 20 order by col1 desc。

不能够使用索引的情况

  1. desc select col1 from tab where col1 > 18 order by col2, col3。[col1是一个范围查询条件，而不是一个常数，无法使用索引]
  2. desc select * from tab where col1 > 1 order by col2, col1。[排序键顺序与索引中列顺序不一致，无法使用索引排序]
  3. desc select col1, col2 from tab order by col1 desc, col2 asc。[升序降序不一致，无法使用索引排序]
  4. desc select col1, col2 from tab where col1 > 1 order by col2。[条件1是范围查询，无法使用索引排序，具体和组合索引数据分布有关系]
  5. desc select * from tab where col1 = 1234 or col2 = 4321 order by col2。[当你逻辑表达式为OR时，使用索引排序会丢失，无法使用索引排序]
  6. desc select * from tab where col1 = 123 and col2 in (1,2) order by col3。[IN查询也是一种范围查询，无法使用索引排序]
  7. 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]

使用松散索引扫描需要满足以下条件
  1. 查询在单一表上。
  2. GROUP BY指定的所有列是索引的一个最左前缀，并且没有其他的列。比如：表tab1（col1，col2，col3，col4）上建立了索引（col1，col2，col3）。如果查询包含“group by col1，col2”，那么可以使用松散索引扫描。但是“group by col2，col3”（不是最左前缀）和“group by col1，col2，col4”（col4字段不在索引中）无法使用。
  3. 如果在选择列表select list中存在聚集函数，只能使用MIN（）和MAX（）两个聚合函数，并且指定的事同一个列（如果min（）和max（）同时存在），这一列必须在索引中，且紧跟着GROUP BY指定的列。比如：select col1,col2,min(col3),max(col3) from tab group by col1, col2。
  4. 如果查询中存在除了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

 
 

优化建议

  1. JOIN。
    1. 在ON字段上加索引，并且字段类型必须是相同的，否则MYSQL无法使用到它们的索引。
  2. 使用 ENUM 而不是 VARCHAR。
  3. 避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN ORDER BY RAND 等这样的操作符。
  4. 把IP地址存成 UNSIGNED INT。
  5. 固定长度的表会更快。
  6. 垂直分割。
    1. “垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。
    2. 例一：在Users表中有一个字段是家庭地址，这个字段是可选字段，相比起，而且你在数据库操作的时候除了个人信息外，你并不需要经常读取或是改写这个字段。那么，为什么不把他放到另外一张表中呢？ 这样会让你的表有更好的性能，大家想想是不是，大量的时候，我对于用户表来说，只有用户ID，用户名，口令，用户角色等会被经常使用。小一点的表总是会有好的性能。
    3. 示例二： 你有一个叫 “last_login” 的字段，它会在每次用户登录时被更新。但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个字段放到另一个表中，这样就不会影响你对用户ID，用户名，用户角色的不停地读取了，因为查询缓存会帮你增加很多性能。
    4. 另外，你需要注意的是，这些被分出去的字段所形成的表，你不会经常性地去Join他们，不然的话，这样的性能会比不分割时还要差，而且，会是极数级的下降。
  7. 拆分大的 DELETE 或 INSERT 语句。
    1. 如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。
  8. 越小的列会越快。建议：布尔/枚举：tinyint，日期与时间戳：timestamp或int，char/text/blob: 尽量用符合实际长度的varchar（n），小数及货币：移位转为int 或 decimal，IP地址：int。
  9. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
  10. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
  11. NOT IN、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法。
  12. 能用UNION ALL就不要用UNION，需知道两者区别。
  13. 尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描。
    1. SELECT * FROM t WHERE id = 1 OR id = 3
    2. 优化方式：可以用union代替or。如下：
    3. SELECT * FROM t WHERE id = 1 UNION SELECT * FROM t WHERE id = 3
  14. 能够用BETWEEN的就不要用IN。
  15. 能够用DISTINCT的就不用GROUP BY。
  16. 尽量不要用SELECT INTO语句。SELECT INTO 语句会导致表锁定，阻止其他用户访问该表。
  17. UPDATE操作不要拆成DELETE操作+INSERT操作的形式，虽然功能相同，但是性能差别是很大的。
  18. 索引字段上进行运算会使索引失效。
 

数据表结构优化建议

根据数据分析建议来修改表结构，使之更符合数据存储规范：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也有做索引列的话，那么两条均能使用到索引查询。

相关文章