SQL优化之片面见解
一、一条SQL查询语句是如何执行的?
一条简单的SQL。
select * from user where user_id =1;
看到的只是输入一条语句,返回一个结果。接下来这条语句在 MySQL 内部的执行过程。
MySQL 的基本架构图
大体来说,MySQL 可以分为 Server 层和存储引擎层两部分。
Server 层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖 MySQL 的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。
而存储引擎层负责数据的存储和提取。现在最常用的存储引擎是 InnoDB,它从 MySQL 5.5.5 版本开始成为了默认存储引擎。也就是说,执行 create table 建表的时候,如果不指定引擎类型,默认使用的就是 InnoDB。
连接器
第一步,你会先连接到这个数据库上,这时候接待你的就是连接器。
连接器负责跟客户端建立连接、获取权限、维持和管理连接。
mysql -h$ip -P$port -u$user -p
连接命令中的 mysql 是客户端工具,用来跟服务端建立连接。在完成经典的 TCP 握手后,连接器就要开始认证你的身份,这个时候用的就是你输入的用户名和密码。
-
如果用户名或密码不对,你就会收到一个"Access denied for user"的错误,然后客户端程序结束执行。
-
如果用户名密码认证通过,连接器会到权限表里面查出你拥有的权限。之后,这个连接里面的权限判断逻辑,都将依赖于此时读到的权限。
查找缓存
执行逻辑第二步:查询缓存
MySQL 拿到一个查询请求后,会先到查询缓存看看,之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式,被直接缓存在内存中。key 是查询的语句,value 是查询的结果。
查询缓存往往弊大于利:
查询缓存的失效非常频繁,只要有对一个表的更新,这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来,还没使用呢,就被一个更新全清空了。对于更新压力大的数据库来说,查询缓存的命中率会非常低。除非你的业务就是有一张静态表,很长时间才会更新一次。
MySQL 8.0 版本直接将查询缓存的整块功能删掉了。
分析器
MySQL 需要知道你要做什么,因此需要对 SQL 语句做解析。
词法分析
分析器先会做“词法分析”,输入的是由多个字符串和空格组成的一条 SQL 语句,MySQL 需要识别出里面的字符串分别是什么,代表什么。
select * from user where user_id =1;
MySQL 从你输入的"select"这个关键字识别出来,这是一个查询语句。
它也要把字符串“user”识别成“表名 user”,把字符串“user_id”识别成“列 user_id”。
语法分析
做完了“词法分析”以后,就要做“语法分析”。
根据词法分析的结果,语法分析器会根据语法规则,判断你输入的这个 SQL 语句是否满足 MySQL 语法。
优化器
经过了分析器,MySQL 就知道你要做什么了。
优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序。
select * from t1 join t2 on t1.id=t2.id and t1.c=10 and t2.d=20;
-
既可以先从表 t1 里面取出 c=10 的记录的 ID 值,再根据 ID 值关联到表 t2,再判断 t2 里面 d 的值是否等于 20。
-
也可以先从表 t2 里面取出 d=20 的记录的 ID 值,再根据 ID 值关联到 t1,再判断 t1 里面 c 的值是否等于 10。
这两种执行方法的逻辑结果是一样的,但是执行的效率可能会有不同,而优化器的作用就是决定选择使用哪一个方案。
优化器阶段完成后,这个语句的执行方案就确定下来了,然后进入执行器阶段。
执行器
执行器MySQL 通过分析器知道了你要做什么,通过优化器知道了该怎么做,于是就进入了执行器阶段,开始执行语句。
开始执行的时候,要先判断一下你对这个表 T 有没有执行查询的权限,如果没有,就会返回没有权限的错误。
如果有权限,就打开表继续执行。打开表的时候,执行器就会根据表的引擎定义,去使用这个引擎提供的接口。
select * from T where ID=10;
假设表 T 中,ID 字段没有索引,那么执行器的执行流程是这样的:
-
调用 InnoDB 引擎接口取这个表的第一行,判断 ID 值是不是 10,如果不是则跳过,如果是则将这行存在结果集中;
-
调用引擎接口取“下一行”,重复相同的判断逻辑,直到取到这个表的最后一行。
-
执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
假设表 T 中,ID 字段有索引,那么执行器的执行流程是这样的:
第一次调用的是“取满足条件的第一行”这个接口,
之后循环取“满足条件的下一行”这个接口,这些接口都是引擎中已经定义好的。
二、索引
一句话简单来说,索引的出现其实就是为了提高数据查询的效率,就像书的目录一样。
一本 500 页的书,如果你想快速找到其中的某一个知识点,在不借助目录的情况下,那我估计你可得找一会儿。
同样,对于数据库的表而言,索引其实就是它的“目录”。
索引类型
MySQL索引类型如下:
-
从索引存储结构划分:B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
-
从应用层次划分:普通索引、唯一索引、主键索引、复合索引
-
从索引键值类型划分:主键索引、辅助索引(二级索引)
-
从数据存储和索引键值逻辑关系划分:聚集索引(聚簇索引)、非聚集索引(非聚簇索引)
B+Tree结构
MySQL数据库索引采用的是B+Tree结构,在B-Tree结构上做了优化改造。
B-Tree结构
-
索引值和data数据分布在整棵树结构中
-
每个节点可以存放多个索引值及对应的data数据
-
树节点中的多个索引值从左到右升序排列
B树的搜索:
从根节点开始,对节点内的索引值序列采用二分法查找,如果命中就结束查找。没有 命中会进入子节点重复查找过程,直到所对应的的节点指针为空,或已经是叶子节点了才结束。
B+Tree结构
- 非叶子节点不存储data数据,只存储索引值,这样便于存储更多的索引值
- 叶子节点包含了所有的索引值和data数据
- 叶子节点用指针连接,提高区间的访问性能
相比B树,B+树进行范围查找时,只需要查找定位两个节点的索引值,然后利用叶子节点的指针进 行遍历即可。而B树需要遍历范围内所有的节点和数据,显然B+Tree效率高。
聚簇索引和辅助索引
B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引;
主键索引和辅助索引:
-
B+Tree的叶子节点索引位置存放的是主键字段值就属于主键索引;
-
如果存放的是非主键值 就属于辅助索引(二级索引)。
聚簇索引(聚集索引)
B+Tree 的叶子节点就是行记录,行记录和主键值紧凑地存储在一起。
这也意味着 InnoDB 的主键索引就是数据表本身,它按主键顺序存放了整张表的数据,占用的空间就是整个表数据量的大小。通常说 的主键索引就是聚集索引。
InnoDB的表要求必须要有聚簇索引:
-
如果表定义了主键,则主键索引就是聚簇索引
-
如果表没有定义主键,则第一个非空unique列作为聚簇索引
-
否则InnoDB会从建一个隐藏的row-id作为聚簇索引
辅助索引
辅助索引,InnoDB辅助索引,也叫作二级索引,是根据索引列构建 B+Tree结构。
但在 B+Tree 的叶子节点中 只存了索引列和主键的信息。
二级索引占用的空间会比聚簇索引小很多,通常创建辅助索引就是 为了提升查询效率。
一个表InnoDB只能创建一个聚簇索引,但可以创建多个辅助索引。
非聚簇索引
如果索引值和行记录分开存放就属于非聚簇索引。
三、优化
应用角度
1、尽可能减少无意义的网络IO连接
2、查询SQL尽量不要使用select *,而是根据所需要,select具体字段。
3、如果知道查询结果只有一条或者只要最大/最小一条记录,建议用limit 1,避免全表扫描
4、尽可能不返回大量数据,而是相对思考,我需要这么多数据的意义是什么,能不能根据一下函数,例如count(),max()…以及group by 分组和having过滤,或者其他的操作,来达到目的
SQL角度
一个完整的select语句格式如下:
select 字段
from 表名
where …….
group by ………
having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)
order by ………
limit…以上语句的执行顺序:
首先执行where语句过滤原始数据
执行group by进行分组
执行having对分组数据进行操作
执行select选出数据
执行order by排序
执行limit
原则:能在where中过滤的数据,尽量在where中过滤,效率较高。
having的过滤是专门对分组之后的数据进行过滤的。
1、应尽量避免在where子句中使用or来连接条件,取而代之的是union和union all
CREATE TABLE `user` (
`user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`username` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户名称',
`age` int(11) DEFAULT NULL COMMENT '年纪',
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
假设现在需要查询userid为1或者年龄为18岁的用户,很容易有以下sql
# 反例
select * from user where user_id=1 or age =18
# 使用union all 或者union
select * from user where user_id=1
union all
select * from user where age = 18
理由:
- 使用or可能会使索引失效,从而全表扫描。
对于or+没有索引的age这种情况,假设它走了userId的索引,但是走到age查询条件时,它还得全表扫描,也就是需要三步过程:全表扫描+索引扫描+合并 如果它一开始就走全表扫描,直接一遍扫描就完事。
mysql是有优化器的,处于效率与成本考虑,遇到or条件,索引可能失效,看起来也合情合理。
2、尽量避免在索引列上使用mysql的内置函数
理由:索引列上使用mysql的内置函数,索引失效。
如果索引列不加内置函数,该走索引,索引还是会走的。
3、Inner join 、left join、right join,优先使用Inner join,如果是left join,左边表结果尽量小,以小表作为驱动表。
Inner join 内连接,在两张表进行连接查询时,只保留两张表中完全匹配的结果集
left join 在两张表进行连接查询时,会返回左表所有的行,即使在右表中没有匹配的记录。
right join 在两张表进行连接查询时,会返回右表所有的行,即使在左表中没有匹配的记录。
4、应尽量避免在 where 子句中对字段进行表达式操作,这将导致系统放弃使用索引而进行全表扫描
# 反例
select * from user where age-1 =10;
# 正例
select * from user where age =11;
理由:
- 虽然age加了索引,但是因为对它进行运算,索引直接迷路了。。。
5、应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。
# 反例
select age,name from user where age <>18;
# 正例
select age,name from user where age <18
union all
select age,name from user where age >18;
6、使用联合索引时,注意索引列的顺序,一般遵循最左匹配原则。
B+ 树这种索引结构,可以利用索引的“最左前缀”,来定位记录。
接下来用(name,age)这个联合索引来分析
可以看到,索引项是按照索引定义里面出现的字段顺序排序的。
当你的逻辑需求是查到所有名字是“张三”的人时,可以快速定位到 ID4,然后向后遍历得到所有需要的结果。
如果你要查的是所有名字第一个字是“张”的人,你的 SQL 语句的条件是"where name like ‘张 %’"。
这时,你也能够用上这个索引,查找到第一个符合条件的记录是 ID3,然后向后遍历,直到不满足条件为止。
可以看到,不只是索引的全部定义,只要满足最左前缀,就可以利用索引来加速检索。
这个最左前缀可以是联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符。
理由:
- 当我们创建一个联合索引的时候,如(k1,k2,k3),相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则。
- 联合索引不满足最左原则,索引一般会失效,但是这个还跟Mysql优化器有关的。
7、优化你的like语句
MySQL在使用Like模糊查询时,索引是可以被使用的,只有把%字符写在后面才会使用到索引。
# 不起作用
select * from user where name like '%o%';
# 起作用
select * from user where name like 'o%';
# 不起作用
select * from user where name like '%o';
8、使用覆盖索引,减少回表查询
如果执行的语句是 select user_id from user where age between 23 and 35;(假设age列上设定了一个普通索引age )
这时只需要查 user_id 的值,而 user_id 的值已经在 age 索引树上了,因此可以直接提供查询结果,不需要回表。
也就是说,在这个查询里面,索引 age 已经“覆盖了”我们的查询需求,我们称为覆盖索引。
由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。
9、关于null,尽可能设定一个默认值
对MySQL来说,NULL是一个特殊的值,从概念上讲,NULL意味着“一个未知值”,它的处理方式与其他 值有些不同。
比如:不能使用=,<,>这样的运算符,对NULL做算术运算的结果都是NULL,count()时 不会包括NULL行等,NULL比空字符串需要更多的存储空间等。
虽然MySQL可以在含有NULL的列上使用索引,但NULL和其他数据还是有区别的,不建议列上允许为 NULL。
最好设置NOT NULL,并给一个默认值,比如0和 ‘’ 空字符串等,如果是datetime类型,也可以 设置系统当前时间或某个固定的特殊值,例如’1970-01-01 00:00:00’。varchar字段可以设定默认值-1,之类的。
# age的默认值是0
select * from user where age > 0;
理由:
- 并不是说使用了is null 或者 is not null 就会不走索引了,这个跟mysql版本以及查询成本都有关。
如果mysql优化器发现,走索引比不走索引成本还要高,肯定会放弃索引。
这些条件
!=,>is null,is not null
经常被认为让索引失效,其实是因为一般情况下,查询的成本高,优化器自动放弃的。
- 如果把null值,换成默认值,很多时候让走索引成为可能,同时,表达意思会相对清晰一点。
10、如果数据量较大,优化你的增加、修改、删除语句。
避免同时修改或删除过多数据,因为会造成cpu利用率过高,从而影响别人对数据库的访问。
比如要往mysql中增加或者修改10万条数据,可以进行分批增加或者修改。
11、索引不适合建在有大量重复数据的字段上,如性别这类型数据库字段。
因为SQL优化器是根据表中数据量来进行查询优化的,如果索引列有大量重复数据,Mysql查询优化器推算发现不走索引的成本更低,很可能就放弃索引了。
12、慎用distinct关键字
13、不要有超过5个以上的表连接,很多公司名言规定,表连接最多三张表
- 连表越多,编译的时间和开销也就越大。
- 把连接表拆开成较小的几个执行,可读性更高。
- 如果一定需要连接很多表才能得到数据,那么意味着糟糕的设计了。
14、索引不宜太多,一般5个以内。
- 索引并不是越多越好,索引虽然提高了查询的效率,但是也降低了插入和更新的效率。
- insert或update时有可能会重建索引,所以建索引需要慎重考虑,视具体情况来定。
- 一个表的索引数最好不要超过5个,若太多需要考虑一些索引是否没有存在的必要。
15、常见分页优化
第一步:利用覆盖索引优化
select * from user limit 10000,100;
select user_id from user limit 10000,100;
第二步:利用子查询优化
select * from user limit 10000,100;
select * from user where user_id >= (select id from user limit 10000,1) limit 100;
使用了user_id做主键比较(user_id>=),并且子查询使用了覆盖索引进行优化。
15、对查询进行优化,应考虑在 where 及 order by 涉及的列上建立索引,尽量避免全表扫描。
MySQL查询支持filesort和index两种方式的排序.
-
filesort是先把结果查出,然后在缓存或磁盘进行排序 操作,效率较低。
-
使用index是指利用索引自动实现排序,不需另做排序操作,效率会比较高。
filesort有两种排序算法:双路排序和单路排序。
-
双路排序:需要两次磁盘扫描读取,最终得到用户数据。第一次将排序字段读取出来,然后排序;第二 次去读取其他字段数据。
-
单路排序:从磁盘查询所需的所有列数据,然后在内存排序将结果返回。
如果查询数据超出缓存 sort_buffer,会导致多次磁盘读取操作,并创建临时表,最后产生了多次IO,反而会增加负担。
解决方 案:少使用select *;增加sort_buffer_size容量和max_length_for_sort_data容量。
如果我们Explain分析SQL,结果中Extra属性显示Using filesort,表示使用了filesort排序方式,需要优化。
如果Extra属性显示Using index时,表示覆盖索引,也表示所有操作在索引上完成,也可以使用 index排序方式,建议大家尽可能采用覆盖索引。
…
https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&mid=2247486461&idx=1&sn=60a22279196d084cc398936fe3b37772&chksm=cea24436f9d5cd20a4fa0e907590f3e700d7378b3f608d7b33bb52cfb96f503b7ccb65a1deed&token=1987003517&lang=zh_CN%23rd
https://mp.weixin.qq.com/s/8qN81pjkn6A2BBiohw2r4g
explain分析
select_type
表示查询的类型。常用的值如下:
-
SIMPLE : 表示查询语句不包含子查询或union
-
PRIMARY:表示此查询是最外层的查询
-
UNION:表示此查询是UNION的第二个或后续的查询 EXPLAIN SELECT * from user WHERE id < 3;
-
DEPENDENT UNION:UNION中的第二个或后续的查询语句,使用了外面查询结果
-
UNION RESULT:UNION的结果
-
SUBQUERY:SELECT子查询语句
-
DEPENDENT SUBQUERY:SELECT子查询语句依赖外层查询的结果。
最常见的查询类型是SIMPLE,表示我们的查询没有子查询也没用到UNION查询。
type
表示存储引擎查询数据时采用的方式。比较重要的一个属性,通过它可以判断出查询是全表扫描还 是基于索引的部分扫描。常用属性值如下,从上至下效率依次增强。
- ALL:表示全表扫描,性能最差。
- index:表示基于索引的全表扫描,先扫描索引再扫描全表数据。
- range:表示使用索引范围查询。使用>、>=、<、<=、in等等。
- ref:表示使用非唯一索引进行单值查询。
- eq_ref:一般情况下出现在多表join查询,表示前面表的每一个记录,都只能匹配后面表的一 行结果。
- const:表示使用主键或唯一索引做等值查询,常量查询。
- NULL:表示不用访问表,速度最快。
possible_keys
表示查询时能够使用到的索引。注意并不一定会真正使用,显示的是索引名称。
key
表示查询时真正使用到的索引,显示的是索引名称。
rows
MySQL查询优化器会根据统计信息,估算SQL要查询到结果需要扫描多少行记录。原则上rows是 越少效率越高,可以直观的了解到SQL效率高低。
key_len
表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。
key_len的计算规则如下:
-
字符串类型
-
字符串长度跟字符集有关:latin1=1、gbk=2、utf8=3、utf8mb4=4
-
char(n):n*字符集长度
-
varchar(n):n * 字符集长度 + 2字节
-
-
数值类型
- TINYINT:1个字节
- SMALLINT:2个字节
- MEDIUMINT:3个字节
- INT、FLOAT:4个字节
- BIGINT、DOUBLE:8个字节
-
时间类型
- DATE:3个字节
- TIMESTAMP:4个字节
- DATETIME:8个字节
-
字段属性
- NULL属性占用1个字节,如果一个字段设置了NOT NULL,则没有此项。
Extra
Extra表示很多额外的信息,各种操作会在Extra提示相关信息,常见几种如下:
-
Using where 表示查询需要通过索引回表查询数据。
-
Using index 表示查询需要通过索引,索引就可以满足所需数据。
-
Using filesort 表示查询出来的结果需要额外排序,数据量小在内存,大的话在磁盘,因此有Using filesort 建议优化。
-
Using temprorary 查询使用到了临时表,一般出现于去重、分组等操作。