SQL索引与不走索引的优化

前言

在传统的系统应用程序中我们通常都会和[数据库]建立连接进行数据的读写操作，为了减少连接数据库造成的资源消耗于是有了数据库连接缓冲池。在此基础上，SQL 语句的优化对于研发人员也是非常重要的，高效的 SQL 语句经常会给使一个业务逻辑的接口响应速度变得非常快。所以本篇小编将主要从 SQL 语句的优化给出一些建议以及如何使用 SQL 语句里面的关键字等才能使 SQL 的执行效率相对提升，并且分享一份[MySQL优化]学习笔记，希望给研发人员在编写 SQL 语句时能有一些帮助。

一、基础数据准备

创建表并初始化一些基础数据，便于后面SQL优化时使用， tbl_user 用户表，tbl_userinfo用户详情表。

DROP TABLE IF EXISTS `tbl_user`;
CREATE TABLE `tbl_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
INSERT INTO `tbl_user` VALUES 
('1', 'admin', 'admin@126.com', '18', '1', '2018-07-09 11:08:57'), 
('2', 'mengday', 'mengday@163.com', '31', '2', '2018-07-09 11:09:00'), 
('3', 'mengdee', 'mengdee@163.com', '20', '2', '2018-07-09 11:09:04'), 
('4', 'root', 'root@163.com', '31', '1', '2018-07-09 14:36:19'), 
('5', 'zhangsan', 'zhangsan@126.com', '20', '1', '2018-07-09 14:37:28'), 
('6', 'lisi', 'lisi@gmail.com', '20', '1', '2018-07-09 14:37:31'), 
('7', 'wangwu', 'wangwu@163.com', '18', '1', '2018-07-09 14:37:34'), 
('8', 'zhaoliu', 'zhaoliu@163.com', '22', '1', '2018-07-11 18:29:24'), 
('9', 'fengqi', 'fengqi@163.com', '19', '1', '2018-07-11 18:29:32');
DROP TABLE IF EXISTS `tbl_userinfo`;
CREATE TABLE `tbl_userinfo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`address` varchar(255) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_userId` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `tbl_userinfo` VALUES 
('1', '上海市', '1'), 
('2', '北京市', '2'), 
('3', '杭州', '3'), 
('4', '深圳', '4'), 
('5', '广州', '5'), 
('6', '海南', '6');

二、五百万数据插入

上面插入几条测试数据，在使用索引时还需要插入更多的数据作为测试数据，下面就通过存储过程插入500W条数据作为测试数据

-- 修改mysql默认的结束符号，默认是分号；但是在函数和存储过程中会使用到分号导致解析不正确
delimiter $$
-- 随机生成一个指定长度的字符串
create function rand_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 $$
-- 创建插入的存储过程
create procedure insert_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 tbl_user values ((start+i) ,rand_string(8), concat(rand_string(6), '@random.com'), 1+FLOOR(RAND()*100), 3, now());
until i = max_num
end repeat;
commit;
end $$
-- 将命令结束符修改回来
delimiter ;
-- 调用存储过程，插入500万数据，需要等待一会时间，等待执行完成
call insert_user(100001,5000000);
-- Query OK, 0 rows affected (7 min 49.89 sec) 我的Macbook Pro i5 8G内存用了8分钟才执行完
select count(*) from tbl_user;

三、使用索引和不使用索引的比较

没有添加索引前一个简单的查询用了1.79秒

image.png

创建索引，然后再查询可以看到耗时0.00秒，这就是索引的威力

image.png image.png image.png

四、explain命令

image.png image.png image.png

如果是子查询，可以用exists代替。详情见《MySql中如何用exists代替in》如下：

- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);

image.png

explain命令用于查看sql执行时是否使用了索引，是优化SQL语句的一个非常常用而且非常重要的一个命令, 上面中的key字段表示查询使用到的索引即使用了idx_username索引

• id: SELECT识别符。这是SELECT的查询序列号
• select_type: 查询类型simple: 简单表即不适用表连接或者子查询
• primary: 主查询，即外层的查询
• subquery: 子查询内层第一个SELECT，结果不依赖于外部查询
• dependent subquery: 子查询内层第一个
• select: 依赖于外部查询
• union: UNION语句中第二个SELECT开始后面所有SELECTunion result union 中合并结果DERIVED
• table：查询的表
• partitionstype：扫描的方式，
• all表示全表扫描all : 全表扫描
• index: 扫描所有索引range: 索引范围扫描,常见于< <=、>、>=、between、
• const: 表最多有一个匹配行, 常见于根据主键或唯一索引进行查询
• system: 表仅有一行(=系统表)。
• 这是const联接类型的一个特例refpossible_keys: 该查询可以利用的索引，可能同一个查询有多个索引可以使用，如果没有任何索引显示
• nullkey: 实际使用到的索引，从Possible_key中所选择使用索引，当有多个索引时，mysql会挑出一个最优的索引来使用key_len: 被选中使用索引的索引长度ref:多表连接时的外键字段
• constrows: 估算出结果集行数，该sql语句扫描了多少行，可能得到的结果,MySQL认为它执行查询时必须检查的行数filtered
• Extra： 额外重要的信息no tables: Query语句中使用FROM DUAL 或不含任何FROM子句using
• filesort : 使用文件排序，最好能避免这种情况Using temporary: 某些操作必须使用临时表，常见 GROUP BY ;
• ORDER BYUsing where: 不用读取表中所有信息，仅通过索引就可以获取所需数据;Using join buffer

查看索引的使用情况： show status like 'Handler_read%'; Handlerreadkey: 越高越好 Handlerreadrnd_next:越低越好

image.png
查询优化器：
重新定义表的关联顺序(优化器会根据统计信息来决定表的关联顺序)将外连接转化成内连接(当外连接等于内连接)使用等价变换规则（如去掉1=1）优化count()、min()、max()子查询优化提前终止查询in条件优化mysql可以通过 EXPLAIN EXTENDED 和 SHOW WARNINGS 来查看mysql优化器改写后的sql语句 image.png

当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：

SELECT username, age, sex FROM T WHERE 1=1
优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and。

五、走索引的情况和不走索引的情况

1. in走索引
   in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内。

   
   image.png

2. 范围查询走索引

   
   image.png

3. 模糊查询只有左前缀使用索引

   
   image.png

4. 反向条件不走索引 != 、 <> 、 NOT IN、IS NOT NULL

   
   image.png
   image.png

# 常见的对not in的优化，使用左连接加上is null的条件过滤
SELECT id, username, age FROM tbl_user WHERE id NOT IN (SELECT user_id FROM tbl_order);
SELECT
u.id, u.username, u.age
FROM tbl_user u
LEFT JOIN tbl_order o ON u.id = o.user_id
WHERE o.user_id IS NULL;

5. 对条件计算(使用函数或者算数表达式)不走索引
   使用函数计算不走索引，无论是对字段使用了函数还是值使用了函数都不走索引，解决办法通过应用程序计算好，将计算的结果传递给sql，而不是让数据库去计算

   
   image.png
   image.png
   

   id是主键，id/10使用了算数表达式不走索引

   
   image.png

6. 查询时必须使用正确的数据类型
   如果索引字段是字符串类型，那么查询条件的值必须使用引号，否则不走索引

   
   image.png

7. or 使用索引和不使用索引的情况

or 只有两边都有索引才走索引，如果都没有或者只有一个是不走索引的，[MySQL优化]面试实战真题分享。

image.png

六、建表优化

1. 在表中建立索引，优先考虑where、order by使用到的字段。

2. 尽量使用数字型字段（如性别，男：1 女：2），若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

3. 查询数据量大的表 会造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段分页进行查询，循环遍历，将结果合并处理进行展示。要查询100000到100050的数据，如下：

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,* 
   FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 100050

4. 用varchar/nvarchar 代替 char/nchar

尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。