前言
平时写sql写的比较多,一直没把优化相关的知识整理记录下来,本文章记录对SQL优化的一些技巧;
我将结合demo(一个百万级数据表),去实践验证这些优化技巧。
测试用例
接下来,我们创建一个测试表并生成100w条测试数据,有助演示或验证接下来的知识
-- 创建一个测试表CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` varchar(255) DEFAULT NULL, `b` varchar(255) DEFAULT NULL, `c` varchar(11) DEFAULT NULL, `d` int(2) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_name` (`a`,`b`,`c`) USING BTREE, KEY `d` (`d`), KEY `b` (`b`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;-- ------------------------------ Records of users-- ----------------------------INSERT INTO `users` VALUES ('1', 'a', 'b', 'c', '1');INSERT INTO `users` VALUES ('2', 'asd', '785qwe', '2', '2');INSERT INTO `users` VALUES ('3', 'wer', '123', '1', '3');INSERT INTO `users` VALUES ('4', '左先生', '123', '1', '4');INSERT INTO `users` VALUES ('5', 'qwe1', 'wq12', '2', '5');INSERT INTO `users` VALUES ('6', 'qwe', '123', '2', null);INSERT INTO `users` VALUES ('7', '1', '1', '1', null);INSERT INTO `users` VALUES ('8', 'w', '1', '1', null);INSERT INTO `users` VALUES ('9', 'aa', '1', null, null);
-- 创建生成随机数据的存储过程DROP PROCEDURE IF EXISTS `create_test_data`;DELIMITER ;;CREATE PROCEDURE `create_test_data`(n INT) COMMENT '生成若干随机数据'BEGIN DECLARE i INT DEFAULT 1; WHILE i < n DO INSERT INTO `test`.`users` (`a`, `b`, `c`) VALUES ( get_rand_str (10), get_rand_str (10), get_rand_str (10) ); SET i = i + 1; END WHILE;END;;
-- 返回随机字符串的函数DROP FUNCTION IF EXISTS `get_rand_str`;DELIMITER ;;CREATE FUNCTION `get_rand_str`(n INT) RETURNS varchar(100) COMMENT '返回随机数'BEGIN DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; DECLARE return_str varchar(255) DEFAULT ''; DECLARE i INT DEFAULT 0; WHILE i < n DO SET return_str = concat(return_str, substring(char_str, FLOOR(1 + RAND()*62), 1)); SET i = i+1; END WHILE; RETURN return_str;END;;
-- 生成100w条随机数据-- 预计花费半小时或更久,其实也可以生成1w条。主要是数据多一点,更能反映出索引的重要性call create_test_data(100*10000);
Explain
Explain是确定一个SQL是否走索引最简单的办法,我们用此方法可以对SQL进行调优,本文章只需关注以下项目,关于Exolain的具体说明可查阅具体说明
type( 从最好到最差依次是 const > eq_ref > ref > range > index > all )const 表示通过索引一次就找到了,const用于比较primary key 或者 unique索引eq_ref 多表连接中使用primary key或者 unique key作为关联条件ref 非唯一性索引扫描,返回匹配某个单独值得所有行,本质上也是一种索引访问,它返回所有匹配某个单独值得行,然而它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体range 只检索给定范围的行,一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好index 遍历全表,ALL区别为index类型只遍历索引树 ( select索引列或order by 主键 两种情况,但是where没用到索引 )all 遍历全表以找到匹配的行一般保证查询至少达到range级别,最好能达到ref。
key 本次查询最终用到哪个索引
key_len 索引使用的前缀长度或整个长度
row 扫描过的记录行数
-- 测试一下,其中b字段有索引,c字段没有索引SELECT * from users where b='随便啦,测试而已'; -- 花费0.001sSELECT * from users where c='随便啦,测试而已'; -- 花费0.306s
SQL优化建议
少用select *
老生常谈,大家都懂。
合理使用limit 1
如果知道查询结果只有一条或者只要一条记录,建议用limit 1,当然,如果已存在唯一索引就没必要用。
合理使用join
Inner join 内连接,在两张表进行连接查询时,只保留两张表中完全匹配的结果集
left join 在两张表进行连接查询时,会返回左表所有的行,即使在右表中没有匹配的记录
right join 在两张表进行连接查询时,会返回右表所有的行,即使在左表中没有匹配的记录
都满足SQL需求的前提下,推荐优先使用Inner join(内连接),如果要使用left join,左边表数据结果尽量小,如果有条件的尽量放到左边处理。
批量插入数据
数量不大的情况下,一条一条插入问题不大。如果数据量两,使用批量插入语句效率更高
for(){ INSERT INTO`test`.`users`(`a`,`b`,`c`) VALUES ('hLQK51GcL6','1DXIzvIS3t','4LsQGKva6U')}
更优:
INSERT INTO `test`.`users` (`a`, `b`, `c`)VALUES-- 此处可自行拼接语句,如使用mybatis等 ( 'hLQK51GcL6', '1DXIzvIS3t', '4LsQGKva6U' ), ( 'hLQK51GcL6', '1DXIzvIS3t', '4LsQGKva6U' )
尽量用union all替换 union
如果使用union,不管检索结果有没有重复,都会尝试进行合并,然后在输出最终结果前进行排序。如果已知检索结果没有重复记录,使用union all 代替union,这样会提高效率。
-- 执行时间0.06sSELECT *FROM usersLIMIT 0, 10000UNION ALLSELECT *FROM usersLIMIT 10000,20000-- 执行时间0.2sSELECT *FROM usersLIMIT 0, 10000UNIONSELECT *FROM usersLIMIT 10000,20000
会使索引失效的几种情况
where条件中没有匹配字段类型
where中使用NOT、!=、IN ("IN" Mysql5.6及以上支持索引)
where中使用OR连接没有索引的字段
where中使用in (mysql5.6及以上支持索引)
like '%关键字%'
where中对字段进行运算或使用函数
使用复合索引但没有使用"引导列"
我们知道测试表中b字段是有索引,c没有索引,接下来逐一测试一下
where条件中没有匹配字段类型
-- b是字符串类型,where且写了整数,虽然可以正常执行sql,但是不会走索引EXPLAIN SELECT * from users where b=1;
** NOT、!=**
-- 均会使索引失效EXPLAIN SELECT * from users where b not in('a');EXPLAIN SELECT * from users where b is not null; EXPLAIN SELECT * from users where b !='a'
OR
-- 用or连接没有索引的字段这种情况,假设它走了b的索引,但是走到c查询条件时,它还得全表扫描-- 也就是需要三步过程:全表扫描+索引扫描+合并。所以OR会导致索引失效-- 注意,测试表中c是没索引的,如果c也有索引,用or其实是OK的EXPLAIN SELECT * from users where b='a' or c='a'-- 优化方式1.改用 inSELECT * from users where b in ('b','bbb')2.UNION -- 对于or,我们可以这样优化我们的sql,虽然第二条没有走索引,但是第一条sql就走了索引啦SELECT * from users where b = 'b' UNION SELECT * from users where c = 'c'
LIKE
-- %关键字% 会让索引失效SELECT * from users where a like '%abc%'-- 正例,"关键字%"是可以使用索引提高查询效率,类似前缀索引SELECT * from users where a like 'abc%'
where中对字段进行运算或使用函数
-- 均会使索引失效EXPLAIN SELECT * from users where YEAR(ctime) = '2020';EXPLAIN SELECT * from users where d+1=2;
大于号与小于号
-- 在mysql中大于号小于号是个神奇的东西,使用它有时候会走索引有时候不走,据说是和结果的数量有关的,当数量较少(网上查到是有一个比例)时时使用索引的-- 建议能用BETWEEN就不要用>'2020-03-30 19:45:30'
使用复合索引但没有使用"引导列"
-- 可知表中有复合索引idx_abc(a,b,c),还有一个idx_b索引,我们先把idx_b删除-- 以下sql 没有用到"引导列"所以不会走idx_abc索引,"引导列"只指复合索引的第一个字段EXPLAIN SELECT * from users where c='c' and b='b' ;-- 正例 只要出现a即可EXPLAIN SELECT * from users where a='a' and b='b' ;EXPLAIN SELECT * from users where a='a' and c='c' ;
limit分页优化
我们日常做分页需求时,一般会用 limit 实现
-- 常用做法SELECT * from users LIMIT 1000000,10
当偏移量最大的时候,查询效率就会越低,因为Mysql并非是跳过偏移量直接去取后面的数据,而是先把偏移量+要取的条数,然后再把前面偏移量这一段的数据抛弃掉再返回的。
优化分页是需要跟业务结合,这里提供几种解决方案,没有最好只有最合适
where加上时间筛选
比如只获取最近一年的数据、只获取今年的数据 where createtime>'2020-01-01'
放弃选页,即只有上一页下一页
第一页直接查
获得第一页max(id),如123,一般是最后一条数据,
然后查询带上索引,这样每次只要扫描10条数据 where id>123 limit 10
限制页数
如只允许获取前100页
索引优化
建立索引
mysql中索引一共分为主键索引、唯一索引、普通索引、全文索引。常用的都是前三种,第一种跟随主键,无需手动创建,而第四种全文索引用于全文搜索。只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于 CHAR, VARCHAR和 TEXT列,一般比较少用,因为像大文本的检索都会采用一些全文检索框架如elasticsearch,而不是在数据库里检索。
-- 单列索引CREATE INDEX index_name ON users (`name`);-- 多列索引CREATE INDEX index_name ON users (`name`,age);-- 唯一索引,单列索引CREATE UNIQUE INDEX index_name ON users (`name`);-- 唯一索引,多列索引CREATE UNIQUE INDEX index_name ON users (`name`,age);
优化
索引与字段选择性
如下两个字段,邮箱、用户名这种选择性较高的字符串是比较适合做索引,而性别这种比较单一的字段,建索引效率并不会提高太多,但如果存在男极多女极少的情况下,也可以考虑建索引。另外如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是唯一的,这种情况也属于选择性较低的字段,不适合做索引
| email | age | username || asdasda@qq.com | 男 | 小明 || 123basb1@163.com | 女 | 小红 |
联合索引的顺序问题
建立联合索引的时候往往也需要考虑索引的顺序,以email与age为例,选择性高的字段应该排在age前面,如email。
-- 正确CREATE INDEX index_name ON users (email,age);-- 反例CREATE INDEX index_name ON users (age,email);
联合索引能为前缀单列,复列提供帮助
-- 联合索引idx_1(a,b,c)-- 有效where a=? where a=? and b=? where a=? and c=? (mysql5.6及以上才支持)where a=? and b=? and c=?where c=? and b=? and a=?(只要三者都出现,顺序打乱都没问题,mysql会自动给你排成上一句的顺序)-- 无效where b=? and c=? where b=?
根据上面的规律,其实可以发现如果where里面如果没有a,那么都不会走索引。这里引入一个概念叫“ 引导列 ”,在联合索引中,排在第一位的就叫引导列,只有where条件中包含引导列,该查询才会走索引。
为了理解,其实当我们创建一个联合索引的时候,如(idx1,idx2,idx3),相当于创建了(idx1)、(idx1,idx2)和(idx1,idx2,idx3)三个索引,当然实际过程中不应该建3个索引,减少不要要的冗余。
索引覆盖扫描
索引覆盖扫描是指根据字段A查询字段B,建立索引idx(a,b)会比单一索引idx(a)效率更高,如现实场景中,系统经常会根据用户名查询用户密码,进行登录操作,针对此操作我们对用户名在前密码在后建立联合索,会比只建立单一索引查询效率更好。
-- 根据用户名查询用户密码SELECT pwd from users where username='a';-- 更优做法,查询时不需要回表查询pwd字段,减少了IO开销idx_1(username,pwd);-- 一般的做法idx_1(username);
避免冗余的索引
重复的索引需要维护,并且优化器在优化查询的时候也需要逐个地进行考虑,这会影响性能的
反例:idx(a)idx(a,b)正例:组合索引(A,B)相当于创建了(A)和(A,B)索引idx(a,b)
另外索引并不是越多越好,索引虽然提高了查询的效率,但是也降低了插入和更新的效率 。 一个表的索引数最好不要超过5个,若太多需要考虑一些索引是否没有存在的必要
大家看完有什么不懂的可以在下方留言讨论
也可以关注.谢谢你的观看。
觉得文章对你有帮助的话记得关注我点个赞支持一下
————————————————
版权声明:本文为CSDN博主「曾笑雨」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/weixin_33213367/article/details/113319944