MySQL优化-mysql优化的步骤

MySQL优化-mysql优化的步骤

 

 

通过show status命令了解各种sql执行的频率

 

Com_XXX,表示每个语句执行的次数，我们比较关心的是：

检查服务器增删改查使用的频次

show status like 'Com_select%' 执行select操作次数

show status like 'Com_insert%' 执行insert操作次数

show status like 'Com_update%' 执行update操作次数

show status like 'Com_delete%' 执行delete操作次数

 

 

只针对innodb引擎影响的行数

show status like '%InnoDB_rows%'

+----------------------+-------+

| Variable_name        | Value |

+----------------------+-------+

| Innodb_rows_deleted  | 0     |执行delete删除的行数

| Innodb_rows_inserted | 0     |执行insert插入的行数

| Innodb_rows_read     | 0     |执行select查询返回的行数

| Innodb_rows_updated  | 0     |执行update更新的行数

+----------------------+-------+

通过以上数据可以得到不同类型sql执行的比例。对于更新操作的计数，是执行次数的计数，不论是提交还是回滚都会增加

 

事务型的应用，通过com_commit和Com_rollback可以了解事物提交和回滚的情况；对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。

 

了解数据库的基本情况

#Connections :试图连接mysql服务器的次数

#Uptime :服务器工作时间

#Slow_queries :慢查询的次数

 

 

 

 

定位执行效率较低的sql语句：查看mysql的慢查询日志

 

查看慢查询设置是否生效

show variables like '%quer%'

mysql> show variables like '%quer%'

    -> ;

+-------------------------------+-------------------------------------------------+

| Variable_name                 | Value                                           |

+-------------------------------+-------------------------------------------------+

| ft_query_expansion_limit      | 20                                              |

| have_query_cache              | YES                                             |

| log_queries_not_using_indexes | OFF                                             |

| log_slow_queries              | OFF                                             |

| long_query_time               | 10.000000                                       |

| query_alloc_block_size        | 8192                                            |

| query_cache_limit             | 1048576                                         |

| query_cache_min_res_unit      | 4096                                            |

| query_cache_size              | 0                                               |

| query_cache_type              | ON                                              |

| query_cache_wlock_invalidate  | OFF                                             |

| query_prealloc_size           | 8192                                            |

| slow_query_log                | OFF                                              |

| slow_query_log_file           | D:\phpStudy\MySQL\data\Sc-201512202300-slow.log |

+-------------------------------+-------------------------------------------------+

14 rows in set (0.00 sec)

| slow_query_log                | 是否开启 on（开启） off（关闭）

| slow_query_log_file           | 日志存放路径

| long_query_time               | 10.000000 超过10秒的sql语句

 

 

查看慢查询出现的次数

show status like '%quer%';

mysql> show status like '%quer%';

+-------------------------+-----------+

| Variable_name           | Value     |

+-------------------------+-----------+

| Com_empty_query         | 0         |

| Last_query_cost         | 10.499000 |

| Qcache_queries_in_cache | 0         |

| Queries                 | 55        |

| Slow_queries            | 0         |

+-------------------------+-----------+

5 rows in set (0.00 sec)

| Slow_queries            | 0  慢查询出现的次数

 

修改慢查询的参数（my.ini）

long_query_time=6 超标时间

修改配置文件需要重启msyql

 

 

通过explain分析低效率sql的执行计划

explan 或者desc 定位一条sql语句的执行计划

 

           id: 1 #

  select_type: SIMPLE #表示select类型，常见的取值有

   simple简单表，既不使用表连接和子查询

   primary（主查询，即外层的查询）

   union（union中的第二个或者后面的查询语句）

   subquery（子查询中的第一个select）

 

        table: ecs_article #输出结果集的表

         type: const #表示msyql在表中找到所需行的方式，或者叫访问类型，性能由差到好

          all(通过扫描全表得到数据)

          index(通过遍历整个索引来得到数据)

          range(索引范围扫描,常见<,<=,>,>=,between)

          ref(使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行)

          eq_ref(使用的索引是唯一索引，对于每个索引的键值，表中只有一条匹配；简单来说，多表连接中使用了primary key或者unique index作为关联条件)

          type=const/system(单表中最多有一个匹配行，查询起来非常迅速，所这个匹配行中的其他列的值可以被优化器在当前查询中当做常量处理，例如根据主键和唯一索引进行的查询)

          type=NULL(mysql不访问表或者索引，直接就能得到结果)

 

          其他：

          ref_or_null(与ref类似，区别在于条件中包含对null的查询)

          index_merge(索引合并优化)

          unique_subquery(in的后面是一个查询主键字段的子查询)

          index_subquery(与unique_subquery类似，区别在于in的后边是查询非唯一索引字段的子查询)

         

         

possible_keys: PRIMARY #表查询时可能使用的索引

          key: PRIMARY #实际使用的索引

      key_len: 3 #使用索引字段的长度

          ref: const

         rows: 1 扫描行的数量

        Extra:  using where(用到了where);using index(用到了索引) #执行情况的说明和描述

1 row in set (0.00 sec)

 

mysql> explain select *  from ecs_article where article_id=1\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: ecs_article

         type: const

possible_keys: PRIMARY

          key: PRIMARY

      key_len: 3

          ref: const

         rows: 1

        Extra:

1 row in set (0.00 sec)

 

mysql> explain select *  from ecs_article where title='用户协议'\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: ecs_article

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 6

        Extra: Using where

1 row in set (0.00 sec)

 

 

 

 

 

使用explain extended 查看sql真正被执行前优化器做了哪些改写

可以利用explain extended 的结果迅速获取一个更清晰易读的sql语句

 

 

 

通过show profile 分析sql了解时间消耗在哪里

 

查看是否支持profile

select @@have_profiling;

mysql> select @@have_profiling;

+------------------+

| @@have_profiling |

+------------------+

| YES              |

+------------------+

1 row in set (0.00 sec)

 

如果是关闭的可以使用set语句在session级别开启profiling

mysql> select @@profiling;

+-------------+

| @@profiling |

+-------------+

|           0 |

+-------------+

1 row in set (0.00 sec)

 

mysql> set profiling=1;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select @@profiling;

+-------------+

| @@profiling |

+-------------+

|           1 |

+-------------+

1 row in set (0.00 sec)

 

在innodb引擎表上，执行count(*)查询

mysql> select count(*) from us;

+----------+

| count(*) |

+----------+

|   131072 |

+----------+

1 row in set (0.05 sec)

执行完毕后，通过show profiles语句找到当前sql的query id

mysql> show profiles;

+----------+------------+-------------------------+

| Query_ID | Duration   | Query                   |

+----------+------------+-------------------------+

|        1 | 0.00028025 | select @@profiling      |

|        2 | 0.04886325 | select count(*) from us |

+----------+------------+-------------------------+

2 rows in set (0.00 sec)

 

通过show profile for query query_id 语句能够看得到执行过程中线程的每个状态和消耗时间

mysql> show profile for query 2;

+----------------------+----------+

| Status               | Duration |

+----------------------+----------+

| starting             | 0.000066 |

| checking permissions | 0.000008 |

| Opening tables       | 0.000018 |

| System lock          | 0.000009 |

| init                 | 0.000018 |

| optimizing           | 0.000009 |

| statistics           | 0.000013 |

| preparing            | 0.000007 |

| executing            | 0.000004 |

| Sending data         | 0.048509 |

| end                  | 0.000013 |

| query end            | 0.000003 |

| closing tables       | 0.000006 |

| freeing items        | 0.000178 |

| logging slow query   | 0.000003 |

| cleaning up          | 0.000001 |

+----------------------+----------+

16 rows in set (0.00 sec)

注：seding data 状态表示mysql线程开始访问数据行并把结果返回给客户端，而不仅仅是返回结果给客户端。由于在sendingdata状态下，mysql线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的。

 

在获取最消耗时间的线程状态后，msyql支持进一步选择all、cpu、block、io、context switch、page faults等明细类型来查看mysql在使用什么资源上耗费了过高的时间，例如，选择查看cpu的耗费时间：

mysql> show profile cpu for query 2;

+----------------------+----------+----------+------------+

| Status               | Duration | CPU_user | CPU_system |

+----------------------+----------+----------+------------+

| starting             | 0.000066 | 0.000000 |   0.000000 |

| checking permissions | 0.000008 | 0.000000 |   0.000000 |

| Opening tables       | 0.000018 | 0.000000 |   0.000000 |

| System lock          | 0.000009 | 0.000000 |   0.000000 |

| init                 | 0.000018 | 0.000000 |   0.000000 |

| optimizing           | 0.000009 | 0.000000 |   0.000000 |

| statistics           | 0.000013 | 0.000000 |   0.000000 |

| preparing            | 0.000007 | 0.000000 |   0.000000 |

| executing            | 0.000004 | 0.000000 |   0.000000 |

| Sending data         | 0.048509 | 0.046800 |   0.000000 |

| end                  | 0.000013 | 0.000000 |   0.000000 |

| query end            | 0.000003 | 0.000000 |   0.000000 |

| closing tables       | 0.000006 | 0.000000 |   0.000000 |

| freeing items        | 0.000178 | 0.000000 |   0.000000 |

| logging slow query   | 0.000003 | 0.000000 |   0.000000 |

| cleaning up          | 0.000001 | 0.000000 |   0.000000 |

+----------------------+----------+----------+------------+

16 rows in set (0.00 sec)

 

可以发现时间都消耗在了cpu。

show profile 能够在做sql优化时帮助我们了解时间都消耗到哪了。而mysql5.6则通过trace文件进一步向我们展示了优化器是如何执行计划的。