官网SQL优化手段

sql优化没有固定的标准，本质上就是做减法，减少io、cpu等消耗，让sql执行的更快，最终达到我们的性能要求。以下是一些常规性的建议，希望能让大家少踩些坑。

案例：创建10万数据

DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
  `user_id` bigint(100) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) DEFAULT NULL COMMENT '用户名',
  `password` varchar(100) DEFAULT NULL COMMENT '密码',
  `salt` varchar(100) DEFAULT NULL COMMENT '盐',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `mobile` varchar(100) DEFAULT NULL COMMENT '手机号',
  `status` varchar(100) DEFAULT NULL COMMENT '状态  0：禁用   1：正常',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=100000000000 DEFAULT CHARSET=utf8 COMMENT='系统用户';
SET FOREIGN_KEY_CHECKS=1;


show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;


DELIMITER $$
CREATE FUNCTION random_string(n INT) RETURNS VARCHAR(255)
BEGIN
 DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0;
 WHILE i < n DO
 SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
 SET i = i + 1;
 END WHILE;
 RETURN return_str;
END $$

DELIMITER $$
CREATE FUNCTION random_num( ) 
RETURNS INT(5)  
BEGIN   
 DECLARE i INT DEFAULT 0;  
 SET i = FLOOR(100+RAND()*10);  
RETURN i;  
 END $$
 
 
DELIMITER $$
CREATE PROCEDURE insert_sys_user(IN START INT(10),IN max_num INT(10))  
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO sys_user (user_id,username,password,salt,email,mobile,status) VALUES (START+i,random_string(10),random_string(6),random_string(10),random_string(20),random_string(16),random_string(12));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  
 END $$
 
 
DELIMITER ;
CALL  insert_sys_user(1,100000); 

1. 优化业务逻辑，尽量降低需要查找的数据范围

比如 in 太多， 建议控制在100个以内

select * from table1 where id in （1，5，7 ......）；❌ 

2. 只返回必要的字段，避免出现select *

select * from table1 where name="路人甲"； ❌

3. 选择区分度高的列建索引，优先考虑在 where 及 order by 涉及的列上建立索引

如果能避免排序最好。没有合适的索引且符合条件的记录非常多时, 排序会消耗大量CPU、IO等硬件资源，很容易引发sql性能问题。

#name 字段可以建立索引,区分度比较高
select id from table1 where name="路人甲"  and city="北京"；

#grade可以建立索引，由于索引的顺序存储可以避免排序带来的消耗
select id from table1 order by  grade limit 10；

#(class,grade)可以建立复合索引
select name from table1 where class=? order by  grade limit 10；

4. 避免在索引列上做运算或函数操作，这样会导致索引失效

select id from table1 where num/2 = 100；❌ 
select id from table1 where abs(num)= 100；❌ 

5. 避免like '%aaa'

select id from table1 where name like '%jim'; ❌ 
select id from table1 where name like 'jim%'; ✅

6. 避免隐式转换

数量类型优先级由高到底: Datetime >Float>Int>Text>Varchar>Binary

CREATE TABLE `insert_para` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `thread_num` int(11) DEFAULT NULL,
  `addtime` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `idx_uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=255042 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 


mysql> explain select * from insert_para where uid='1';✅

#优先级Int高于varchar ,低优先级转换为高优先级，造成索引失效 
mysql> explain select * from insert_para where uid=1;❌ 

7. 优先用join 替代 in子查询

select clo1 from a  where a.id in (select pid from b) ❌ 
select a.name,b.name from a join b on a.id=b.pid  where b.pid=1 

8. update/delete尽量根据主键或唯一键进行操作

update table1  set b=1 where pk=?  or uk=? ;         ✅

9. dml要尽量短小，避免长时间持有大量的锁阻塞其他sql，影响系统整体的吞吐量

update table1  set b=1 where id between 1 and 10000;  ❌ 

10. 尽量避免join

如果要, 关联字段要有索引，并优先选择小表作为驱动表。

select a.name,b.name from a join b on a.id=b.pid where a.col1=?

11. 除非明确指定的执行计划永远是最优解，否则不要加hint

12. 尽量避免使用视图、存储过程、游标等。对数据库的使用越简单越好，业务逻辑要在后端代码层实现，不要放到数据库层，做到各司其职