MySQL优化之三：SQL语句优化

zhouyi

　　一 SQL语句优化的一般步骤：
　　1 通过show status命令了解各种SQL语句的执行频率
　　mysql> show status;                #show status：显示服务器状态信息
　　+-----------------------------------------------+-------------+
　　| Variable_name                                 | Value       |
　　+-----------------------------------------------+-------------+
　　| Aborted_clients                               | 0           |
　　| Aborted_connects                              | 0           |
　　| Binlog_cache_disk_use                         | 0           |
　　| Binlog_cache_use                              | 8           |
　　| Binlog_stmt_cache_disk_use                    | 0           |
　　| Binlog_stmt_cache_use                         | 25          |
　　| Bytes_received                                | 2919        |
　　| Bytes_sent                                    | 51750       |
　　......
　　mysql> show status like "com%";    #显示当前session中，统计参数的值
　　+---------------------------+-------+
　　| Variable_name             | Value |
　　+---------------------------+-------+
　　| Com_admin_commands        | 0     |
　　| Com_assign_to_keycache    | 0     |
　　| Com_alter_db              | 0     |
　　| Com_alter_db_upgrade      | 0     |
　　| Com_alter_event           | 0     |
　　| Com_alter_function        | 0     |
　　| Com_alter_procedure       | 0     |
　　| Com_alter_server          | 0     |
　　| Com_alter_table           | 2     |
　　| Com_alter_tablespace      | 0     |
　　| Com_alter_user            | 0     |
　　| Com_analyze               | 0     |
　　| Com_begin                 | 0     |
　　......
　　Com_xxx：表示每个xxx语句执行的次数，以下几个统计参数非常重要：

• 　　Com_select：执行select的次数，一次查询累计加1
  
• 　　Com_insert：执行insert操作的次数，批量插入只累加1
  
• 　　Com_delete：执行delete操作的次数，
  
• 　　Com_update：执行update操作的次数，
  

　　以上参数针对所有存储引擎的表操作。
　　下面的参数是针对InnoDB存储引擎的，算法也稍有不同：
　　Innodb_rows_read：select查询返回的行数
　　Innodb_rows_inserted：执行insert操作插入的行数
　　Innodb_rows_updated：执行update操作更新的行数
　　Innodb_rows_deleted：执行delete操作删除的行数
　　通过以上参数的了解，可以判断出当前数据库是以插入更新为主还是以查询操作为主，以及各种类型SQL大致的执行比例是多少。
　　此外，以下几个参数可以帮助用户了解数据库的基本情况：
　　Uptime：数据库服务器的工作时间
　　Connections：试图连接服务器的次数
　　Slow_queries：慢查询的次数
　　2 定位执行效率低的SQL语句
　　方式1：通过慢查询日志定位
　　方式2：查看当前正在进行的线程
　　mysql> show processlist;
　　+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+

　　|>　　+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
　　|  1 | system user |           | NULL | Connect | 34400 | Waiting for master to send event                                            | NULL             |

　　|  2 | system user |           | NULL | Connect |  7738 | Slave has read all>　　|  4 | root        | localhost | NULL | Query   |     0 | init                                                                        | show processlist |
　　或
　　[root@localhost ~]# mysqladmin -uroot -h 127.0.0.1 processlist -proot
　　Warning: Using a password on the command line interface can be insecure.
　　+----+------+-----------------+----+---------+------+-------+------------------+

　　|>　　+----+------+-----------------+----+---------+------+-------+------------------+
　　| 1  | root | localhost       |    | Sleep   | 265  |       |                  |
　　| 12 | root | localhost:42210 |    | Query   | 0    | init  | show processlist |
　　+----+------+-----------------+----+---------+------+-------+------------------+
　　备注：show processlist;只列出前100条，如果想全列出请使用show full processlist;
　　3 通过explain分析低效的SQL语句的执行
　　通过之前的步骤查询到效率低的SQL语句之后，可以通过explain命令获取MySQL是如何执行select语句的信息。如：
　　mysql> explain select * from emp1;
　　+----+-------------+-------+------+---------------+------+---------+------+------+-------+

　　|>　　+----+-------------+-------+------+---------------+------+---------+------+------+-------+
　　|  1 | SIMPLE      | emp1  | ALL  | NULL          | NULL | NULL    | NULL |    4 | NULL  |
　　+----+-------------+-------+------+---------------+------+---------+------+------+-------+
　　1 row in set (0.00 sec)

• 　　select_type——select类型
  
• 　　table——输出结果的表
  
• 　　type——表示MySQL在表中找到所需行的方式，或者叫访问类型，常见有以下几种：性能由最差到最好。
  

　　type=all，即通过全表扫描找到匹配的行。
　　type=index，索引全扫描，mysql遍历索引才找到匹配的行。
　　type=range，索引范围扫描，
　　type=ref，使用非唯一索引扫描，或唯一索引的前缀扫描，返回匹配某个单独值的记录行
　　type=eq_ref，类似ref，区别在于使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配。
　　type=const/system，表单中最多有一个匹配行，查询起来非常迅速。如根据主键和唯一索引进行的查询。
　　type=null，不需要访问表或索引，直接就可以得到结果。

• 　　possible_keys——表示查询时可能使用的索引
  
• 　　key——表示实际使用的索引
  
• 　　key_len——使用到索引字段的长度
  
• 　　rows——扫描行的数量
  
• 　　Extra——执行情况的说明和描述
  

　　4 通过show profile了解分析SQL执行的过程
　　mysql> select @@have_profiling;        #查看是否支持
　　+------------------+
　　| @@have_profiling |
　　+------------------+
　　| YES              |
　　+------------------+
　　mysql> set profiling=1;                #开启profiling，默认是关闭
　　Query OK, 0 rows affected, 1 warning (0.00 sec)
　　mysql> select * from emp1;             #执行一个语句
　　+------+--------+-------+------------+
　　| age1 | deptno | ename | birth      |
　　+------+--------+-------+------------+
　　|  111 |      4 | ccc   | 2011-11-30 |
　　|  666 |     11 | ddd   | 2014-12-22 |
　　|  888 |     22 | eee   | 2015-11-30 |
　　|  333 |      8 | fff   | 2011-04-30 |
　　+------+--------+-------+------------+
　　4 rows in set (0.02 sec)
　　mysql> show profiles;                  #查看当前SQL语句的查询ID
　　+----------+------------+---------------------------+
　　| Query_ID | Duration   | Query                     |
　　+----------+------------+---------------------------+
　　|        1 | 0.01696625 | select count(*) from emp1 |
　　|        2 | 0.02623125 | select * from emp1        |
　　+----------+------------+---------------------------+
　　mysql> show profile for query 2;       #查看执行过程中线程的每个状态和消耗时间
　　+----------------------+----------+
　　| Status               | Duration |
　　+----------------------+----------+
　　| starting             | 0.000111 |
　　| checking permissions | 0.000019 |
　　| Opening tables       | 0.000046 |
　　| init                 | 0.000043 |
　　| System lock          | 0.000031 |
　　| optimizing           | 0.000016 |
　　| statistics           | 0.000039 |
　　| preparing            | 0.000023 |
　　| executing            | 0.000008 |
　　| Sending data         | 0.025442 |
　　| end                  | 0.000020 |
　　| query end            | 0.000014 |
　　| closing tables       | 0.000016 |
　　| freeing items        | 0.000326 |
　　| cleaning up          | 0.000079 |
　　+----------------------+----------+
　　Sending data表示MySQL线程开始访问数据行并把结果返回给客户端。通常是整个查询中耗时最长的状态
　　mysql> show profile cpu for query 2;    #查看耗费CPU的时间，Sending data主要耗费在CPU上
　　+----------------------+----------+----------+------------+
　　| Status               | Duration | CPU_user | CPU_system |
　　+----------------------+----------+----------+------------+
　　| starting             | 0.000111 | 0.000000 |   0.000000 |
　　| checking permissions | 0.000019 | 0.000000 |   0.000000 |
　　| Opening tables       | 0.000046 | 0.000000 |   0.000000 |
　　| init                 | 0.000043 | 0.000000 |   0.000000 |
　　| System lock          | 0.000031 | 0.000000 |   0.000000 |
　　| optimizing           | 0.000016 | 0.000000 |   0.000000 |
　　| statistics           | 0.000039 | 0.000000 |   0.000000 |
　　| preparing            | 0.000023 | 0.000000 |   0.000000 |
　　| executing            | 0.000008 | 0.000000 |   0.000000 |
　　| Sending data         | 0.025442 | 0.000000 |   0.001999 |
　　| end                  | 0.000020 | 0.000000 |   0.000000 |
　　| query end            | 0.000014 | 0.000000 |   0.000000 |
　　| closing tables       | 0.000016 | 0.000000 |   0.000000 |
　　| freeing items        | 0.000326 | 0.000000 |   0.000000 |
　　| cleaning up          | 0.000079 | 0.000000 |   0.000000 |
　　+----------------------+----------+----------+------------+
　　mysql> show profile all for query 1\G  #查看所有明细，了解MySQL在什么资源上耗费了过高的时间
　　5 通过trace分析优化器如何选择执行计划
　　6 确定问题之后，采取相应的措施优化
　　由前面的步骤确认对表进行全表扫描，导致查询效果不理想，那么对表的某个字段建立索引。具体如下 ：
　　mysql> create index index_ename on emp1(ename);
　　Query OK, 0 rows affected (0.25 sec)
　　Records: 0  Duplicates: 0  Warnings: 0
　　建立索引后，再看下这条语句的执行状态：
　　mysql> explain select ename from emp1;
　　建立索引后，可以发现对表扫描的行数大大减少，提高了对表的访问速度。
　　二 索引问题
　　索引是数据库优化中最重要也是最常用的手段之一，通过索引可以帮助用户解决大多数SQL性能问题。
　　1 索引的存储分类：索引是在存储引擎层中实现的

• 　　B-Tree索引：最常见的索引，大部分引擎支持B树索引。
  
• 　　HASH索引：只有Memory引擎支持，使用场景简单
  
• 　　Full-text（全文索引）：一种特殊索引类型
  

　　创建索引方式 1：
　　mysql> create index index_age1 on emp1(age1);
　　Query OK, 0 rows affected (0.15 sec)
　　Records: 0  Duplicates: 0  Warnings: 0
　　创建索引方式 2：

　　mysql>>　　Query OK, 0 rows affected (0.05 sec)
　　Records: 0  Duplicates: 0  Warnings: 0
　　查看索引：
　　mysql> show index from zwj.emp1;
　　+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
　　| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
　　+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
　　| emp1  |          1 | index_ename |            1 | ename       | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
　　| emp1  |          1 | index_age1  |            1 | age1        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
　　+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
　　删除索引：
　　mysql> drop index index_age1 on zwj.emp1;
　　Query OK, 0 rows affected (0.06 sec)
　　Records: 0  Duplicates: 0  Warnings: 0
　　或

　　mysql>>　　Query OK, 0 rows affected (0.04 sec)
　　Records: 0  Duplicates: 0  Warnings: 0
　　另有复合索引：需要咨询开发人员
　　创建复合索引（将最常用作限制条件的列放在最左边，依次递减）：
　　mysql> create index name_passwd on abc.student(name,passwd);（需要咨询研发部门）
　　2 查看索引的使用情况：
　　mysql> show status like 'handler_read%';
　　+-----------------------+-------+
　　| Variable_name         | Value |
　　+-----------------------+-------+
　　| Handler_read_first    | 4     |
　　| Handler_read_key      | 5     |
　　| Handler_read_last     | 0     |
　　| Handler_read_next     | 0     |
　　| Handler_read_prev     | 0     |
　　| Handler_read_rnd      | 0     |
　　| Handler_read_rnd_next | 56    |
　　+-----------------------+-------+
　　7 rows in set (0.00 sec)
　　Handler_read_key：如果索引正在工作，此值应该很高，这个值代表了一个行被索引值读的次数。如果值过低，表明增加索引得到的性能改善不高，因为索引并不常被使用。
　　Handler_read_rnd_next：值高意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果进行了大量的扫描，它的值会很高，说明索引不正确或查询没有利用到索引。