(一)第一步了解SQL的执行频率
命令：
show [session|global]status参数说明
session:当前连接的统计结果，默认为session
global:从数据库启动至今的统计结果，需要手动设置
mysql> show status like 'Com_%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 0 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 0 |
|Com_insert | 0 |
| Com_alter_user | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_select | 1 |
| Com_set_option | 1 |
注：优化SQL语句我们比较关心的是CRUD语句
(二)定位执行效率较低的SQL语句
2中方法
- 通过漫查询日志定位执行效率较低的SQL语句。
- 使用show processlist命令查询当前MYSQL在进行的线程。
(三)通过explain分析低效率SQL的执行
通过explain命令获取MySQL执行select语句的信息
mysql> explain select sum(amount)from customer a,payment b where 1=1 and a.customer_id=b.customer_id
-> and email='JANE BENNETT@sakilacustomer.org';
+----+-------------+-------+------------+------+--------------------+--------------------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+----------------------+------+----------+-------------+
| 1 | SIMPLE | a | NULL | ALL | PRIMARY | NULL | NULL | NULL | 599 | 10 | Using where |
| 1 | SIMPLE | b | NULL | ref | idx_fk_customer_id | idx_fk_customer_id | 2 | sakila.a.customer_id | 26 | 100 | NULL |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+----------------------+------+----------+-------------+
2 rows in set参数说明：
1.select_type:表示select类型，simple(单表，没有任何表与它连接) primary(主查询，既外层查询)、union(union中第二个或者后面的查询语句 )、subquery(子查询中的第一个select语句)。
2.table:输出结果集的表
3.type:表示访问类型。常见类型:
+-----+------+-----+-----+-------+-------------+------+
|ALL |index |rang |ref |eq_ref |const,system |null |
+-----+------+-----+-----+-------+-------------+------+
注：从左到右，性能由最差到最好。
1)type=all,全表扫描
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | film | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set
2)type=index表示索引全扫描
mysql> explain select title from film;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | film | NULL | index | NULL | idx_title | 767 | NULL | 1000 | 100 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set
3)type=range表示索引范围扫描，必须说>、<、=等等。
mysql> explain select*from payment where customer_id>300 and customer_id<=350;
+----+-------------+---------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | payment | NULL | range | idx_fk_customer_id | idx_fk_customer_id | 2 | NULL | 1319 | 100 | Using index condition |
+----+-------------+---------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
1 row in set
4)type=fer,使用的是唯一索引的前缀或非唯一索引扫描。
mysql> explain select*from payment where customer_id=350;
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | payment | NULL | ref | idx_fk_customer_id | idx_fk_customer_id | 2 | const | 23 | 100 | NULL |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set
5)type=eq_ref,类似ref，区别就是使用的索引是唯一索引。
mysql> explain select*from film a,film_text b where a.film_id=b.film_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| 1 | SIMPLE | b | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1000 | 100 | NULL |
| 1 | SIMPLE | a | NULL | eq_ref | PRIMARY | PRIMARY | 2 | sakila.b.film_id | 1 | 100 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
2 rows in set
6)type=const/system,单表中只有一个匹配行。比如根据主键或唯一索引查询。
mysql> explain select*from(select*from customer where email='AARON.SELBY@sakilacustomer.org')a;
+----+-------------+----------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | customer | NULL | const | uk_email | uk_email | 153 | const | 1 | 100 | NULL |
+----+-------------+----------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
1 row in set
7)type=null,MySQL不需要访问表或索引，直接得到结果。
mysql> explain select 1 from dual where 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set
4.possible_key:表示查询时可能使用到的索引。
5.key:表示实际用到的索引
6.key_len:使用索引的字段长度
7.rows:扫描的行数
8.Extra:执行情况的说明
(四)通过show profiling分析SQL
@使用show profiling分析SQL的好处就是:帮助我们了解优化SQL语句各个地方消耗的时间。
1.通过hava_profiling参数查看Mysql是否支持profile
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
1 row in set2.默认profile是关闭的 ，使用set语句在session级别开启profile
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set
mysql> set profiling=1;
Query OK, 0 rows affected
3.执行函数count(*)查询
1)查询
mysql> select count(*)from payment;
+----------+
| count(*) |
+----------+
| 16049 |
+----------+
1 row in set
2)分析SQL语句
mysql> show profiles;
+----------+------------+-----------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------+
| 1 | 0.06718175 | select count(*)from payment |
+----------+------------+-----------------------------+
1 row in set
3)使用show profile for query语句查看执行过程中线程的每个状态和消耗时间
mysql> show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000148 |
| checking permissions | 1.8E-5 |
| Opening tables | 5.3E-5 |
| init | 2.7E-5 |
| System lock | 1.8E-5 |
| optimizing | 1.2E-5 |
| statistics | 3.5E-5 |
| preparing | 2.5E-5 |
| executing | 6E-6 |
| Sending data | 0.066399 |
| end | 2.8E-5 |
| query end | 2.6E-5 |
| closing tables | 2.5E-5 |
| freeing items | 0.000317 |
| cleaning up | 4.7E-5 |
+----------------------+----------+
15 rows in set
4)使用show profile source for query查看SQL解析执行过程的每一步。
mysql> show profile source for query 1;
+----------------------+----------+-----------------------+----------------------+-------------+
| Status | Duration | Source_function | Source_file | Source_line |
+----------------------+----------+-----------------------+----------------------+-------------+
| starting | 0.000148 | NULL | NULL | NULL |
| checking permissions | 1.8E-5 | check_access | sql_authorization.cc | 806 |
| Opening tables | 5.3E-5 | open_tables | sql_base.cc | 5649 |
| init | 2.7E-5 | handle_query | sql_select.cc | 121 |
| System lock | 1.8E-5 | mysql_lock_tables | lock.cc | 323 |
| optimizing | 1.2E-5 | JOIN::optimize | sql_optimizer.cc | 151 |
| statistics | 3.5E-5 | JOIN::optimize | sql_optimizer.cc | 367 |
| preparing | 2.5E-5 | JOIN::optimize | sql_optimizer.cc | 475 |
| executing | 6E-6 | JOIN::exec | sql_executor.cc | 119 |
| Sending data | 0.066399 | JOIN::exec | sql_executor.cc | 195 |
| end | 2.8E-5 | handle_query | sql_select.cc | 199 |
| query end | 2.6E-5 | mysql_execute_command | sql_parse.cc | 5005 |
| closing tables | 2.5E-5 | mysql_execute_command | sql_parse.cc | 5057 |
| freeing items | 0.000317 | mysql_parse | sql_parse.cc | 5631 |
| cleaning up | 4.7E-5 | dispatch_command | sql_parse.cc | 1902 |
+----------------------+----------+-----------------------+----------------------+-------------+
15 rows in set(五)通过trace分析优化器跟踪SQL
1)打开trace设置格式为JSON
mysql> set optimizer_trace="enabled=on",END_MARKERS_IN_JSON=on;
Query OK, 0 rows affected
2)设置trace的内存，尽肯能设置大一点，避免解析过程中应默认内存过小而无法完整显示跟踪文件。
mysql> set optimizer_trace_max_mem_size=100000;
Query OK, 0 rows affected
3)演示案例
mysql> select rental_id from rental where 1=1 and rental_date>='2005-05-25 04:00:00'
-> and rental_date<='2005-05-25 05:00:00' and inventory_id=4466;
+-----------+
| rental_id |
+-----------+
| 39 |
+-----------+
1 row in set
4)使用infomation_schema.optimizer_trace语句检查Mysql是如何执行SQL语句。
(六)采取优化措施
根据以上方法可以确定SQL执行在哪些地方比较耗时间，因此需要采取一些有效的措施对SQL语句进行优化。比如说建立索引优化SQL语句、优化数据库对象、优化 MySQL服务器等等。