mysql性能优化学习与实战-2

mysql性能分析

不论进行什么优化，观察系统运行情况为第一步

定时分析process

没有压测的情况下

[root@centos1 ~]# mysql -uroot -p123456 -e 'show processlist \G' 
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist

--只关心State这一数据信息
[root@centos1 ~]# mysql -uroot -p123456 -e 'show processlist \G' \
> | grep State \
> | sort -rn \
> | uniq
  State: NULL

编写脚本定时分析mysql的process状态

#!/bin/bash

while true
do
mysql -uroot -p123456 -e 'show processlist \G' | grep State | sort -rn | uniq >> proce.txt --结果保存
usleep 100000 --0.1S观察一次
done

下面运行此脚本，然后运行压测程序，见实战1，分析结果

[root@centos1 shell]# cat proce.txt | sort -r | uniq -c
    104   State: Writing to net 
    116   State: Updating --线程正在寻找要更新的行和更新他们。
     50   State: update --修改数据
    353   State: statistics --服务器计算统计数据,以制定一个查询执行计划。 如果一个线程处于这种状态很长一段时间,服务器可能是磁盘绑定执行其他工作。
     18   State: Sorting result --对结果进行排序，未建立索引的列
      3   State: Sending data --线程处理一个SELECT语句行,将数据发送到客户端。 因为在这个状态发生的操作往往执行大量的磁盘访问(读取),它往往是在一个给定的查询的生命周期最长的运行状态。
      1   State: Opening tables
   1022   State: NULL --这种状态是用于SHOW PROCESSLIST状态。
      1   State: login --连接线程的初始状态,直到客户端已成功通过身份验证。
      3   State: Copying to tmp table --将数据放入临时表；如果group by 的列没有索引,必产生内部临时表,如果order by 与group by为不同列时,或多表联查时order by ,group by 包含的列不是第一张表的列,将会产生临时表
      1   State: removing tmp table --线程是消除处理后,内部的SELECT表SELECT语句。 如果没有创建临时表,这种状态是不使用。
      2   State: optimizing --系统进行查询语句优化
      2   State: Creating tmp table --创建临时表
      15   State: Creating sort index--线程正在处理一个SELECT就是使用内部临时表解决。
      3   State: cleaning up --线程处理一个命令,并正准备以释放内存和重置某些状态变量。
      6   State: 

耗时的地方发送数据给客户端、数据统计、创建临时表、排序
我们应该避免大批量数据传输，数据统计、排序优先考虑组合索引
如下，500W的数据

mysql> set profiling=on; 
mysql> select max(k) from sbtest;
+--------+
| max(k) |
+--------+
|     14 |
+--------+
1 row in set (6.06 sec)

mysql> show profile;             
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.004926 |
| checking permissions | 0.000094 |
| Opening tables       | 0.000030 |
| System lock          | 0.000017 |
| init                 | 0.001631 |
| optimizing           | 0.000024 |
| statistics           | 0.000020 |
| preparing            | 0.000013 |
| executing            | 0.000020 |
| Sending data         | 6.047464 |
| end                  | 0.000010 |
| query end            | 0.000004 |
| closing tables       | 0.000006 |
| freeing items        | 0.000116 |
| logging slow query   | 0.000003 |
| cleaning up          | 0.000002 |
+----------------------+----------+
16 rows in set (0.00 sec)

mysql> explain select * from sbtest group by id%20 order by pad \G; 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE --简单查询
        table: sbtest
         type: ALL --全表扫描，如果用了索引就是index
possible_keys: NULL --可用