（十四）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=?
