MySQL性能优化（二）

通过上篇文章学习，可以定位到慢查询的SQL语句，本篇文章将介绍SQL语句的具体优化。

SQL语句优化有很多，而本章主要讲述利用索引优化。索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构，而MySQL数据库索引采用的就是B+tree，本篇讲解利用索引来优化SQL，对其底层结构就不做详细解释。

相信很多读者对索引优化没什么概念，话不多说，直接看下图有无索引的查询比较。其中test_user表中大概有15万条数据。

下图是在没建索引的情况下，查询时间大概在0.6s左右。

 

 

在userName上创建索引：alter table test_user add INDEX test_user_userName(userName);

查询时间在0.001s，提高了600倍左右，当然随着test_user表中数据量持续增长，可能都不止这个数。

 

所以，当我们定位到慢查询时，首先应当想到，通过创建索引来提升SQL运行效率。

同时，我们可以借助MySQL的EXPLAIN关键字来模拟优化器执行SQL语句，了解MySQL如何执行SQL，分析SQL语句的瓶颈,重点关注type列，possible_keys列，key列。

执行语句如下：explain select * from test_user where userName='张三8014';

 

执行结果如上图，所展示的列具体含义如下：

1、id列：select的序列号，SQL语句中有几个select就会有几个id，id越大，执行优先级越高，其中为NULL的最后执行。

2、select_type列：当前行的查询类型，具体可分为simple（简单查询），primary（复杂查询的最外层select），       subquery（select中的子查询），derived（存放子查询结果的临时表）等。

3、table列：当前行在执行的表。

4、partitions列：可以不用关注，几乎没用。

5、type列：执行SQL的访问类型，决定如何查找表中数据，即使用索引级别。根据查询效率从大到小可分为

    system > const > eq_ref > ref > range > index > ALL

一般来说，至少要保证达到range级别，最好达到ref级别。

     （1）、system和const

             explain select * from test_user where id='22142';

             如上述SQL，其中id为test_user主键。如果SQL语句的where条件是根据主键索引或唯一索引去查询，可以明确知道返回结果只有一条，则type为const类型。system是const的特列，若表中只有一条数据，则为system类型。

       （2）、eq_ref

           explain select * from test_user left join test_dept on test_user.id=test_dept.userId where test_user.id='22142';

           如上SQL，id为test_user的主键。当主键索引或唯一索引被连接使用，最多只返回一条符合条件记录。则type为eq_ref类型。

        （3）、ref

           explain select * from test_user where userName='张三8014';

           如上SQL，在userName列上创建普通索引。当where条件使用普通索引或者唯一索引的前缀，返回多条符合条件的记录，则type为ref类型。

        （4）、range

           explain select * from test_user where userName  > '张三8014';

          如上SQL，当使用索引范围扫描（in，between，>，<，>=）获得符合条件的行，则type为range类型。

        （5）、index

          explain select  userName  from test_user;

          如上SQL，扫描全表索引或者覆盖索引（查询的列创建了普通索引），所以，通常我们写SQL，尽量不要写select *，而写明返回具体字段，就是为更大可能使用覆盖索引，提升查询效率。

        （6）、ALL

         全表扫描，当出现这个类型时，就意味着该SQL需要索引来优化了。

6、possible_keys列

      显示可能使用那些索引来执行当前行。

7、keys列

      实际采用哪个索引来执行当前行。如果该列为NULL，则没有使用索引，则SQL就需优化。除非possible_keys列存在索引，而当前列为NULL，可能是表中数据量较小，MySQL会认为索引查询帮助不大，进行全表扫描。

8、key_len列

     在所索引里所使用的的字节数。

9、ref列

     查询表时所用到的值。

10、rows列

     大概会检索的行数。

11、filtered列

     可不用关注。

12、Extra列

     主要有如下值：Using index，一般使用了覆盖索引，性能较好。

                              Using where，未被索引覆盖，结合type列和keys列，可能需要优化。

一般通过上述执行计划，即可分析优化SQL语句。当然索引并不是创建越多越好，索引本身也会占用存储空间，过多无用的索引影响查询效率。                     

 另索引一般创建在：1，where条件中经常出现的字段，不作为查询条件的字段，尽量不要创建索引。

                              2，字段值较为分散，例：性别字段分男，女。就不适合创建索引。

                              3，修改较为频繁的字段，也不适合创建索引。频繁修改字段，索引也会重新整理，影响数据库性能。

SQL优化肯定不是几篇文章就能说得清，只需要我们长时间实战经验积累，这里只是提供优化的一个大致方向。