乐趣区

SQL的索引优化你知道有多少

jiezi

前言

平时写 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.001s

SELECT * 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.06s
SELECT
    *
FROM
    users
LIMIT 0,
 10000
UNION ALL
SELECT
    *
FROM
    users
LIMIT 10000,
20000

-- 执行时间 0.2s
SELECT
    *
FROM
    users
LIMIT 0,
 10000
UNION
SELECT
    *
FROM
    users
LIMIT 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. 改用 in
SELECT * 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 就不要用 ><

-- 可以自行按时间筛选出不同的数量测试
SELECT id from users where ctime>'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′

放弃选页，即只有上一页下一页

  1. 第一页直接查
  2. 获得第一页 max(id)，如 123，一般是最后一条数据，
  3. 然后查询带上索引，这样每次只要扫描 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 为例，选择性高的字段应该排在 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 个，若太多需要考虑一些索引是否没有存在的必要.

退出移动版