SQL的优化不得不提到索引,索引可以极大的提高SQL语句的查询性能。但是当我们维护一个SQL的时候,如何去入手优化这条语句呢。我理解可以分为以下这几个步骤。
拿到慢SQL
在生产中,我们要如何定位到查询比较慢的SQL语句呢。我们可以查看MYSQL的慢查询日志。现在以我本地的表为例。
如何查看
show variables like '%slow_query_log%';
结果:
slow_query_log ON
slow_query_log_file C:\ProgramData\MySQL\MySQL Server 5.5\Data\PC-201809241048-slow.log
可以看到我本地的MYSQL默认是没有开启慢查询日志的。我们现在打开一下。使用命令
set global slow_query_log = on;
设置多少秒以上的日志为慢查询日志,并记录到查询日志当中,
set long_query_time = 1;
我们可以复现一下自己的慢查询SQL了。执行SQL
select * from portal_sys_user_info;
这个SQL的执行时间是1.2s。我们到慢查询日志中去看一下是什么内容。
可以看到这个图片,其中包含查询查询条数、时间、用户、以及查询SQL。但是在我们实际生产环境当中,可能会有好多的日志,直接查看的方式显然不适用于生产环境。在这里我们使用MYSQL自带的mysqldumpslow工具来分析一下慢查询日志。
mysqldumpslow -t 10 /home/mysql/mysqldata/3306/log/dataslow.log
显示结果,其中Count:1 代表 执行的次数
在这里我们便可以拿到生产上的慢日志了。
分析SQL
我们先拿到日志之后,要怎么进行分析呢。这里我们先提一下常规优化索引吧。索引分类:聚簇索引(主键索引)、二级索引。按照另一种类型也可以分为唯一索引、联合索引、普通索引。
索引创建
唯一索引
我们都知道,要在Where频繁的字段增加索引,那么我们应该建立什么索引呢。
如果我们只给一个字段加索引,需要首先明确这个字段是是唯一的,如果存在空值,可以创建唯一索引。
CREATE unique index account_index
ON sptl.portal_sys_user_info(user_id);
唯一的,不存在空值。可以创建主键索引,这个索引。主键会默认建立。
普通索引
如果是其他情况,可以创建普通索引。
CREATE index account_index
ON sptl.portal_sys_user_info(user_id);
联合索引
我们也可以创建联合索引,联合索引的优先级,在某些情况下要大于普通索引。
两个或更多个列上的索引被称作联合索引(复合索引)。
联合索引可减少索引开销,以联合索引 (a,b,c) 为例,建立这样的索引相当于建立了索引 a、ab、abc 三个索引。一个查询可以只使用索引中的一部份,但只能是最左侧部分。这个原理叫做最左前缀原则。
例如:
SELECT * FROM USERTABLE WHERE A = 'XX' AND B='XX' ADN C='XX'
可以走索引。但是,
SELECT * FROM USERTABLE WHERE B = 'XX' AND A='XX' ADN C='XX'
则不走索引。
explain 分析SQL
我们也拿到SQL了,也知道怎么创建索引了。但是目前生产上的SQL有没有走索引,具体怎么执行的呢。我们可以使用explain命令来分析一下。使用方法
explain SELECT * FROM USERTABLE WHERE A = 'XX' AND B='XX' ADN C='XX'
返回的结构如下
我们来分析一下字段含义。
- SELECT_TYPE : 表示SQL的类型(简单 到复杂)。
包含的类型有:
SIMPLE:查询中不包含子查询或者UNION;
PRIMARY:查询中包含复杂的子部分。数据表的某个字段 = (子查询)
SUBQUERY:在SELECT或WHERE列表中包含了子查询,该子查询被标记为SUBQUERY;
DERIVED:衍生,在FROM列表中包含的子查询被标记为DERIVED;
UNION:若第二个SELECT出现在UNION之后,则被标记为UNION;
UNION RESULT:从UNION表获取结果的SELECT被标记为UNION RESULT; - TABLE :表名称
- TYPE: 表示 MySQL 在表中找到所需行的方式
包含的类型有:
ALL:Full Table Scan, MySQL 将遍历全表以找到匹配的行;
index:在SQL不加where语句时候,并且查询字段只包含索引列的时候出现。
range:常见于 between < > 等查询,也命中了非唯一索引,行数大于1。
ref:非唯一性索引扫描;
eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描;
const 和 system:命中主键索引
NULL:MySQL 在优化过程中分解语句,执行时甚至不用访问表或索引。 - Extra
包含的类型有:
Using index:查询的是索引列。
Using where:MySQL 将用 where 子句来过滤结果集;
Using temporary:表示 MySQL 需要使用临时表来存储结果集,常见于排序和分组查询;
Using filesort:MySQL 中无法利用索引完成的排序操作称为“文件排序”。
好,到这里。explain的主要参数就讲解完了。其实没啥,但要好好记一下。