zhouyi 发表于 2018-10-9 10:55:15

MySQL优化之三:SQL语句优化

  一 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 |
  或
  # 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: 0Duplicates: 0Warnings: 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: 0Duplicates: 0Warnings: 0
  创建索引方式 2:

  mysql>>  Query OK, 0 rows affected (0.05 sec)
  Records: 0Duplicates: 0Warnings: 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: 0Duplicates: 0Warnings: 0
  或

  mysql>>  Query OK, 0 rows affected (0.04 sec)
  Records: 0Duplicates: 0Warnings: 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:值高意味着查询运行低效,并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果进行了大量的扫描,它的值会很高,说明索引不正确或查询没有利用到索引。

页: [1]
查看完整版本: MySQL优化之三:SQL语句优化