背景
本人刚毕业的大学生,在大学期间专业为软件工程主要从事Java web开发,作为后端的开发工程师,我们经常与数据库打交道,在校期间也学习过数据库但是对于数据库的学习没那么重视,认为只要学会了简单的SQL语句就好了,现在框架这么好用,mybatis-plus
的出现甚至于都很少去写SQL语句了,简单的SQL查询直接使用querywrapper
去完成了。
转折
但是最近的工作中由于需要使用SQL语句,发现自己写的SQL语句在查询速度方面很慢,严重影响了客户的体验,于是决定学习SQL数据库的优化。
SQL语句优化原理
想要写好SQL语句首先要理解SQL语句的执行过程,只有理解了执行过程,用数据库的方式去写SQL语句(降维打击)这样才能写出更好的SQL语句。
数据库执行SQL语句大致流程
大致过程如此对于不同DBMS(data base management system
)有着细微的差别。
- 应用程序与数据库服务器建立连接
- SQL发送到数据库,数据库验证是否有执行权限
- 进入语法解析器,进行词法和语法分析
- 进入优化器生成执行计划,部分DBMS会检查是否有可重用的执行计划
- 根据执行计划依次扫描相关表中的行,不在数据缓冲区的走io
- 同时对于被扫描的行可能加锁,同时也可能会被其他sql阻塞
- 扫描的行足够放入查询缓存则开始运算或直接返回,不够则生成临时表,可能消耗io
- 对sql结果进行计算(可能)
- 将计算完成的结果全部写入网络io(可能)
- 如果事务完成则同步事务日志并释放锁,具体方式取决于dbms和当前配置
- 关闭连接(可选)
MySQL性能分析及EXPLAIN的使用
SQL优化的第一步先找出问题,通过EXPLAIN可以查看SQL语句的分析结果,帮助我们分析select语句,让我们知道查询效率低下的原因,从而改进我们的查询,让查询优化器能够更好的工作。
EXPLAIN关键字一般写在select查询语句的前面,用户描述mysql如何执行查询操作、以及MySQL成功返回结果集所需要的执行行数。
用法如下:
EXPLAIN tbl_name 或 EXPLAIN [EXTENDED] SELECT select_options
前者可以得出一个表的字段结构等等,后者主要是给出相关的一些索引信息。
执行计划解释
通过EXPLAIN+SQL语句我们可以得到一个执行计划
执行计划包括以下字段
id | select_typee | table | type | possidle_keys | key | key_len | ref | rows | Extra |
---|
各字段解释
id
select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序。
- 当id相同,执行顺序由上至下
- id不同,子查询序号递增,id之越大优先级越高,约先被执行
- 部分相同部分不同,id相同的可以认为是一组由上而下执行,在所有组中ID值越大越先执行。
select_type
-
SIMPLE
简单的select查询
,查询中不包含子查询或者UNION
-
PRIMARY 查询中若包含任何复杂的子部分,最外层查询则被标记为PRIMARY
-
SUBQUERY 在SELECT或WHERE列表中包含了子查询
-
DERIVED 在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表中
-
UNION 若第二个SELECT出现在UNION之后,则被标记为UNION:若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED
-
UNION RESULT 从UNION表获取结果的SELECT
table
指当前执行的表
type
type所显示的是查询使用的那种类型,type包括如下几种
ALL | index | range | ref | eq_ref | const,system | NULL |
---|
从好到坏依次是
system > const > eq_ref > ref > range > index > all
一般来说,得保证查询至少达到range级别,最好能达到ref。
system
表只有一行记录(等于系统表),这是const类型的特列,平时不会出现,这个也可以忽略不计const
表示通过索引一次就找到了,const用于比较primary key 或者unique索引。因为只匹配一行数据,所以很快。如将主键置于where列表中,MySQL就能将该查询转换为一个常量。eq_ref
唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描ref
非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体。range
只检索给定范围的行,使用一个索引来选择行,key列显示使用了哪个索引,一般就是在你的where语句中出现between、< 、>、in等的查询,这种范围扫描索引比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引。index
Full Index Scan,Index与All区别为index类型只遍历索引树。这通常比ALL快,因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表,但index是从索引中读取的,而all是从硬盘读取的)all
Full Table Scan 将遍历全表以找到匹配的行
possible_keys
possible_keys
显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上,则索引将被列出,但不一定被查询实际使用。
key
实际使用的索引,如果为NULL,则没有使用索引。(可能是没有建立索引或者索引失败)
注意:查询中若使用了覆盖索引
(select 后要查询的字段刚好和创建的索引字段完全相同),则该索引仅出现在key列表中
key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精度的情况下,长度越短越好。key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。
ref
显示索引的哪一列被使用了,如果可能的话,最好是一个常数。哪些列或常量被用于查找索引列上的值。
rows
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,也就是说,用的越少越好
Extra
包含不适合在其他列中显式但十分重要的额外信息
Using filesort
说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
Using temporary
使用了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
Using index
表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错。
如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。
换句话说就是select的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,查询列要被所建的索引覆盖。
Using where
表示使用了where循环
Using join buffer
表示使用了连接缓存,比如说在查询的时候,多表join的次数非常多,那么将配置文件中的缓冲区的join buffer调大一些。
impossible where
where子句的值总是false
,不能用来获取任何元组
select tables optimized away
在没有GROUPBY子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
distinct
优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作