Mysql日常巡检参考（持续更新）_mysql巡检

2401_86358775

于 2024-09-01 22:56:58 发布

阅读量1.8k

点赞数 22

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/2401_86358775/article/details/141791779

版权

相当于show tables from mysql;
#查看某个表中的字段情况：
mysql> show columns from mysql.user;
±-------------------------±----------------------------------±-----±----±----------------------±------+
| Field | Type | Null | Key | Default | Extra |
±-------------------------±----------------------------------±-----±----±----------------------±------+
| Host | char(255) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum(‘N’,‘Y’) | NO | | N | |
| Insert_priv | enum(‘N’,‘Y’) | NO | | N | |
| Update_priv | enum(‘N’,‘Y’) | NO | | N | |
| Delete_priv | enum(‘N’,‘Y’) | NO | | N | |
| Create_priv | enum(‘N’,‘Y’) | NO | | N | |
| Drop_priv | enum(‘N’,‘Y’) | NO | | N | |
| Reload_priv | enum(‘N’,‘Y’) | NO | | N | |
| Shutdown_priv | enum(‘N’,‘Y’) | NO | | N | |
| Process_priv | enum(‘N’,‘Y’) | NO | | N | |
| File_priv | enum(‘N’,‘Y’) | NO | | N | |
| Grant_priv | enum(‘N’,‘Y’) | NO | | N | |
| References_priv | enum(‘N’,‘Y’) | NO | | N | |
| Index_priv | enum(‘N’,‘Y’) | NO | | N | |

#数据库表空间大于1T检查
SELECT table_schema as ‘Database’,table_name,
CONCAT(ROUND(data_length/(102410241024),6),’ G’) AS ‘Data Size’,
CONCAT(ROUND(index_length/(102410241024),6),’ G’) AS ‘Index Size’,
CONCAT(ROUND((data_length+index_length)/(102410241024),6),’ G’) AS’Total’
FROM information_schema.TABLES;
±-------------------±-----------------------------------------------------±-----------±-----------±-----------+
| Database | TABLE_NAME | Data Size | Index Size | Total |
±-------------------±-----------------------------------------------------±-----------±-----------±-----------+
| information_schema | ADMINISTRABLE_ROLE_AUTHORIZATIONS | 0.000000 G | 0.000000 G | 0.000000 G |
| information_schema | APPLICABLE_ROLES | 0.000000 G | 0.000000 G | 0.000000 G |
| information_schema | CHARACTER_SETS | 0.000000 G | 0.000000 G | 0.000000 G |


**information\_schema数据库表说明:**



> 
> SCHEMATA表：提供了当前mysql实例中所有数据**库的信息**。是show databases的结果取之此表。  
>    
>  TABLES表：提供了关于数据库中的**表的信息**（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。不管在哪个数据库中的表，在这里都会有一条记录对应，如果你在一个数据库中创建了一个表，相应地在这个表里，也会有一条记录对应你创建的那个表。  
>    
>  COLUMNS表：提供了表中的**列信息**。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。  
>    
>  STATISTICS表：提供了关于**表索引**的信息。是show index from schemaname.tablename的结果取之此表。  
>    
>  USER\_PRIVILEGES（用户权限）表：给出了关于**全局权限**的信息。该信息源自mysql.user授权表。是非标准表。  
>    
>  SCHEMA\_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。  
>    
>  TABLE\_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables\_priv授权表。是非标准表。  
>    
>  COLUMN\_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns\_priv授权表。是非标准表。  
>    
>  CHARACTER\_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。  
>    
>  COLLATIONS表：提供了关于各字符集的对照信息。  
>    
>  COLLATION\_CHARACTER\_SET\_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。  
>    
>  TABLE\_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。  
>    
>  KEY\_COLUMN\_USAGE表：描述了具有约束的键列。  
>    
>  ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION\_SCHEMA.ROUTINES表的mysql.proc表列。  
>    
>  VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。  
>    
>  TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表.
> 
> 
> 


##### 2）PERFORMANCE\_SCHEMA库


MySQL 5.5开始新增了该数据库：PERFORMANCE\_SCHEMA，主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE\_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE\_SCHEMA表的。MySQL5.5默认是关闭的，需要手动开启，


在配置文件里添加：  
 [mysqld]  
 performance\_schema=ON #该参数为只读参数，需要在实例启动之前设置才生效


主要功能：



> 
> ①提供了一种在数据库运行时实时检查server的内部执行情况的方法。performance\_schema 数据库中的表使用performance\_schema存储引擎。该数据库主要关注数据库运行过程中的性能相关的数据，与information\_schema不同，information\_schema主要关注server运行过程中的元数据信息。提供进程等待的详细信息，包括锁、互斥变量、文件信息；  
>  ②保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；performance\_schema通过监视server的事件来实现监视server内部运行情况， “事件”就是server内部活动中所做的任何事情以及对应的时间消耗，利用这些信息来判断server中的相关资源消耗在了哪里?一般来说，事件可以是函数调用、操作系统的等待、SQL语句执行的阶段(如sql语句执行过程中的parsing 或 sorting阶段)或者整个SQL语句与SQL语句集合。事件的采集可以方便的提供server中的相关存储引擎对磁盘文件、表I/O、表锁等资源的同步调用信息。performance\_schema中的事件与写入二进制日志中的事件(描述数据修改的events)、事件计划调度程序(这是一种存储程序)的事件不同。performance\_schema中的事件记录的是server执行某些活动对某些资源的消耗、耗时、这些活动执行的次数等情况。performance\_schema中的事件只记录在本地server的performance\_schema中，其下的这些表中数据发生变化时不会被写入binlog中，也不会通过复制机制被复制到其他server中。  
>  ③对于新增和删除监控事件点都非常容易，并可以改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）。  
>  通过该库得到数据库运行的统计信息，更好分析定位问题和完善监控信息。  
>  当前活跃事件、历史事件和事件摘要相关的表中记录的信息。能提供某个事件的执行次数、使用时长。进而可用于分析某个特定线程、特定对象(如mutex或file)相关联的活动。  
>  performance\_schema存储引擎使用server源代码中的“检测点”来实现事件数据的收集。对于performance\_schema实现机制本身的代码没有相关的单独线程来检测，这与其他功能(如复制或事件计划程序)不同。  
>  收集的事件数据存储在performance\_schema数据库的表中。这些表可以使用SELECT语句查询，也可以使用SQL语句更新performance\_schema数据库中的表记录(如动态修改performance\_schema的setup\_\*开头的几个配置表，但要注意：配置表的更改会立即生效，这会影响数据收集)。  
>  performance\_schema的表中的数据不会持久化存储在磁盘中，而是保存在内存中，一旦服务器重启，这些数据会丢失(包括配置表在内的整个performance\_schema下的所有数据)。  
>  MySQL支持的所有平台中事件监控功能都可用，但不同平台中用于统计事件时间开销的计时器类型可能会有所差异。  
>    
>  performance\_schema实现机制遵循以下设计目标：  
>    
>  启用performance\_schema不会导致server的行为发生变化。例如，它不会改变线程调度机制，不会导致查询执行计划(如EXPLAIN)发生变化。  
>    
>  启用performance\_schema之后，server会持续不间断地监测，开销很小。不会导致server不可用。  
>    
>  在该实现机制中没有增加新的关键字或语句，解析器不会变化。  
>    
>  即使performance\_schema的监测机制在内部对某事件执行监测失败，也不会影响server正常运行。  
>    
>  如果在开始收集事件数据时碰到有其他线程正在针对这些事件信息进行查询，那么查询会优先执行事件数据的收集，因为事件数据的收集是一个持续不断的过程，而检索(查询)这些事件数据仅仅只是在需要查看的时候才进行检索。也可能某些事件数据永远都不会去检索。  
>    
>  需要很容易地添加新的instruments监测点。  
>    
>  instruments(事件采集项)代码版本化：如果instruments的代码发生了变更，旧的instruments代码还可以继续工作。
> 
> 
>

#查看是否开启：
mysql>show variables like ‘performance_schema’;
±-------------------±------+
| Variable_name | Value |
±-------------------±------+
| performance_schema | ON |
±-------------------±------+
1 row in set (0.04 sec)
#值为ON表示performance_schema已初始化成功且可以使用了
mysql> select version(); #从MySQL5.6开始，performance_schema引擎默认打开
±----------+
| version() |
±----------+
| 8.0.21 |
±----------+
1 row in set (0.00 sec)
#检查performance_schema存储引擎支持情况
mysql> select engine,support from information_schema.engines where engine=‘PERFORMANCE_SCHEMA’;
±-------------------±--------+
| engine | support |
±-------------------±--------+
| PERFORMANCE_SCHEMA | YES |
±-------------------±--------+
1 row in set (0.00 sec)
#查询哪些表使用performance_schema引擎，
mysql> select table_name from information_schema.tables where engine=‘performance_schema’;

#查看数据库系统中打开了文件的对象：包括ibdata文件，redo文件，binlog文件，用户的表文件等，open_count显示当前文件打开的数目，如果重来没有打开过，不会出现在表中。
mysql> select * from file_instances limit 2,5;
±-------------------------------±--------------------------------------±-----------+
| FILE_NAME | EVENT_NAME | OPEN_COUNT |
±-------------------------------±--------------------------------------±-----------+
| /video/mysql/ibdata1 | wait/io/file/innodb/innodb_data_file | 3 |
| /video/mysql/#ib_16384_0.dblwr | wait/io/file/innodb/innodb_dblwr_file | 2 |
| /video/mysql/#ib_16384_1.dblwr | wait/io/file/innodb/innodb_dblwr_file | 2 |
| /video/mysql/ib_logfile0 | wait/io/file/innodb/innodb_log_file | 2 |
| /video/mysql/ib_logfile1 | wait/io/file/innodb/innodb_log_file | 2 |
±-------------------------------±--------------------------------------±-----------+
5 rows in set (0.00 sec)
#查看哪个SQL执行最多
mysql> select schema_name,digest_text,count_star,sum_rows_sent,sum_rows_examined,first_seen,last_seen from events_statements_summary_by_digest order by count_star desc limit 1\G
*************************** 1. row ***************************
schema_name: zabbix
digest_text: BEGIN
count_star: 228311169 #执行次数
sum_rows_sent: 0
sum_rows_examined: 0
first_seen: 2021-03-16 10:43:00.830886
last_seen: 2022-01-27 22:56:10.547003
1 row in set (0.04 sec)
#哪个SQL平均响应时间最多
mysql> select schema_name,digest_text,count_star,avg_timer_wait,sum_rows_sent,sum_rows_examined,first_seen,last_seen from events_statements_summary_by_digest order by avg_timer_wait desc limit 1\G
*************************** 1. row ***************************
schema_name: zabbix
digest_text: SELECT clock , ns , VALUE FROM HISTORY WHERE itemid = ? AND clock > ? AND clock <= ?
count_star: 576
avg_timer_wait: 250369909000 #该SQL平均响应时间，皮秒（1000000000000皮秒=1秒）
sum_rows_sent: 1564
sum_rows_examined: 1564
first_seen: 2021-03-18 02:10:54.662146
last_seen: 2022-01-24 17:04:28.496527
1 row in set (0.00 sec)
#哪个SQL扫描的行数最多
mysql> select schema_name,digest_text,count_star,avg_timer_wait,sum_rows_sent,sum_rows_examined,first_seen,last_seen from events_statements_summary_by_digest order by sum_rows_examined desc limit 1\G
*************************** 1. row ***************************
schema_name: zabbix
digest_text: SELECT DISTINCTROW d . triggerid\_down , d . triggerid\_up FROM trigger\_depends d , TRIGGERS t , HOSTS h , items i , functions f WHERE t . triggerid = d . triggerid\_down AND t . flags != ? AND h . hostid = i . hostid AND i . itemid = f . itemid AND f . triggerid = d . triggerid\_down AND h . status IN (…)
count_star: 456338
avg_timer_wait: 14573128000
sum_rows_sent: 209333096
sum_rows_examined: 4484007395 ##主要关注
first_seen: 2021-03-16 10:43:14.363971
last_seen: 2022-01-27 23:01:58.261088
1 row in set (0.01 sec)

#哪个SQL使用的临时表最多
mysql> select schema_name,digest_text,count_star,avg_timer_wait,sum_rows_sent,sum_rows_examined,sum_created_tmp_tables,sum_created_tmp_disk_tables,first_seen,last_seen from events_statements_summary_by_digest order by sum_created_tmp_tables desc limit 1\G
*************************** 1. row ***************************
schema_name: zabbix
digest_text: SELECT DISTINCTROW g . graphid , g . name , g . width , g . height , g . yaxismin , g . yaxismax , g . show\_work\_period , g . show\_triggers , g . graphtype , g . show\_legend , g . show\_3d , g . percent\_left , g . percent\_right , g . ymin\_type , g . ymin\_itemid , g . ymax\_type , g . ymax\_itemid , g . discover FROM graphs g , graphs\_items gi , items i , item\_discovery id WHERE g . graphid = gi . graphid AND gi . itemid = i . itemid AND i . itemid = id . itemid AND id . parent\_itemid = ?
count_star: 7359187
avg_timer_wait: 443879000
sum_rows_sent: 14083781
sum_rows_examined: 306592148
sum_created_tmp_tables: 7359204 ##主要关注
sum_created_tmp_disk_tables: 0
first_seen: 2021-03-16 10:43:02.510002
last_seen: 2022-01-27 23:05:19.469465
1 row in set (0.00 sec)
#哪个SQL返回的结果集最多
mysql> select schema_name,digest_text,count_star,avg_timer_wait,sum_rows_sent,sum_rows_examined,sum_created_tmp_tables,first_seen,last_seen from events_statements_summary_by_digest order by sum_rows_sent desc limit 1\G
*************************** 1. row ***************************
schema_name: zabbix
digest_text: SELECT i . itemid , i . hostid , i . templateid FROM items i INNER JOIN HOSTS h ON i . hostid = h . hostid WHERE h . status = ?
count_star: 456344
avg_timer_wait: 5604268000
sum_rows_sent: 1706726560 #主要关注
sum_rows_examined: 1770168229
sum_created_tmp_tables: 0
first_seen: 2021-03-16 10:43:14.292694
last_seen: 2022-01-27 23:07:58.857928
1 row in set (0.00 sec)
#哪个SQL排序数最多，其中IO(SUM_ROWS_EXAMINED)，CPU消耗(SUM_SORT_ROWS)，网络带宽(SUM_ROWS_SENT)
mysql> select schema_name,digest_text,count_star,avg_timer_wait,sum_rows_sent,sum_rows_examined,sum_sort_rows,first_seen,last_seen fromevents_statements_summary_by_digest order by sum_sort_rows desc limit 1\G
*************************** 1. row ***************************
schema_name: zabbix
digest_text: SELECT pp . item\_preprocid , pp . itemid , pp . type , pp . params , pp . step , i . hostid , pp . error\_handler , pp . error\_handler\_params , i . type , i . key\_ , h . proxy\_hostid FROM item\_preproc pp , items i , HOSTS h WHERE pp . itemid = i . itemid AND i . hostid = h . hostid AND ( h . proxy\_hostid IS NULL OR i . type IN (…) ) AND h . status IN (…) AND i . flags != ? ORDER BY pp . itemid
count_star: 456346
avg_timer_wait: 14210098000
sum_rows_sent: 1069260615
sum_rows_examined: 3582380844
sum_sort_rows: 1069260615 #主要关注
first_seen: 2021-03-16 10:43:14.313793
last_seen: 2022-01-27 23:09:59.091879
1 row in set (0.00 sec)
#哪个表、文件逻辑IO最多（热数据）：file_summary_by_instance表，意味着这个表经常需要访问磁盘IO
mysql> select file_name,event_name,count_read,sum_number_of_bytes_read,count_write,sum_number_of_bytes_write from file_summary_by_instance order by sum_number_of_bytes_read+sum_number_of_bytes_write desc limit 2\G

*************************** 1. row ***************************
file_name: /video/mysql/#ib_16384_0.dblwr
event_name: wait/io/file/innodb/innodb_dblwr_file
count_read: 1
sum_number_of_bytes_read: 1179648
count_write: 5546947
sum_number_of_bytes_write: 523535138816
*************************** 2. row ***************************
file_name: /video/mysql/ib_logfile1
event_name: wait/io/file/innodb/innodb_log_file
count_read: 0
sum_number_of_bytes_read: 0
count_write: 180237316
sum_number_of_bytes_write: 266296773120
2 rows in set (0.00 sec)
#查看哪个表逻辑IO最多，亦即最“热”的表
mysql> SELECT
-> object_name,
-> COUNT_READ,
-> COUNT_WRITE,
-> COUNT_FETCH,
-> SUM_TIMER_WAIT
-> FROM table_io_waits_summary_by_table
-> ORDER BY sum_timer_wait DESC limit 1\G
*************************** 1. row ***************************
object_name: history #关注
COUNT_READ: 384830011
COUNT_WRITE: 750076101
COUNT_FETCH: 384830011
SUM_TIMER_WAIT: 29159481894425960
1 row in set (0.01 sec)

#获得系统运行到现在，哪个表的具体哪个索引(包括主键索引，二级索引)使用最多
mysql> select object_name,index_name,count_fetch,count_insert,count_update,count_delete from table_io_waits_summary_by_index_usage order by sum_timer_wait desc limit 1\G
*************************** 1. row ***************************
object_name: history
index_name: NULL
count_fetch: 0
count_insert: 374268229
count_update: 0
count_delete: 0
1 row in set (0.00 sec)


更多详情参考：https://www.cnblogs.com/cchust/p/5061131.html


##### 3）sys 库


sys schem在5.7.x版本默认安装，sys schema是一组对象(包括相关的视图、存储过程和函数)，可以方便地访问performance\_schema收集的数据。同时检索的数据可读性也更高(例如：performance\_schema中的时间单位是皮秒，经过sys schema查询时会转换为可读的us,ms,s,min,hour,day等单位)。sys中的数据实际上主要是从performance\_schema、information\_schema中获取。通过sys库不仅可以完成MySQL信息的收集，还可以用来监控和排查问题。


sys库主要内容：



> 
> 1)这个库是通过视图的形式把information\_schema 和performance\_schema结合起来，查询出更加令人容易理解的数据  
>  2)存储过程可以可以执行一些性能方面的配置，也可以得到一些性能诊断报告内容  
>  3)存储函数可以查询一些性能信息
> 
> 
>

#查看用户、连接情况
查看每个客户端IP过来的连接消耗资源情况。
mysql> select host,current_connections,total_connections,unique_users,current_memory,total_memory_allocated from sys.host_summary;
±----------±--------------------±------------------±-------------±---------------±-----------------------+
| host | current_connections | total_connections | unique_users | current_memory | total_memory_allocated |
±----------±--------------------±------------------±-------------±---------------±-----------------------+
| localhost | 20 | 458106 | 2 | 20.99 GiB | 46.42 TiB |
±----------±--------------------±------------------±-------------±---------------±-----------------------+
1 row in set (0.01 sec)
#查看每个用户消耗资源情况

mysql> select user,table_scans,file_ios,file_io_latency,current_connections,current_memory from sys.user_summary;
±-----------±------------±----------±----------------±--------------------±---------------+
| user | table_scans | file_ios | file_io_latency | current_connections | current_memory |
±-----------±------------±----------±----------------±--------------------±---------------+
| zabbix | 12575536 | 195754143 | 17.84 d | 19 | 10.49 GiB |
| root | 32 | 84 | 335.81 ms | 1 | 2.48 MiB |
| background | 0 | 442868581 | 14.88 d | 53 | 233.65 MiB |
±-----------±------------±----------±----------------±--------------------±---------------+
3 rows in set (0.01 sec)
#查看当前正在执行的SQL
mysql> select conn_id,pid,user,db,command,current_statement,last_statement,time,lock_latency from sys.session;
±--------±------±--------------------±-------±--------±------------------------------------------------------------------±------------------------------------------------------------------±-----±-------------+
| conn_id | pid | user | db | command | current_statement | last_statement | time | lock_latency |
±--------±------±--------------------±-------±--------±------------------------------------------------------------------±------------------------------------------------------------------±-----±-------------+
| 28 | 17167 | zabbix@localhost | zabbix | Sleep | select itemid from items where … and flags<>2 and hostid=10084 | select itemid from items where … and flags<>2 and hostid=10084 | 203 | 46.00 us |
| 15 | 17168 | zabbix@localhost | zabbix | Sleep | select itemid from items where … and flags<>2 and hostid=10084 | select itemid from items where … and flags<>2 and hostid=10084 | 164 | 52.00 us |
| 24 | 17147 | zabbix@localhost | zabbix | Sleep | select co.corr_operationid,co. … o.correlationid and c.status=0 | select co.corr_operationid,co. … o.correlationid and c.status=0 | 58 | 44.00 us |
| 30 | 17169 | zabbix@localhost | zabbix | Sleep | select itemid from items where … and flags<>2 and hostid=10084 | select itemid from items where … and flags<>2 and hostid=10084 | 44 | 42.00 us |
| 26 | 17166 | zabbix@localhost | zabbix | Sleep | commit | commit | 40 | 0 ps |
| 10 | 17149 | zabbix@localhost | zabbix | Sleep | commit | commit | 13 | 0 ps |
| 16 | 17151 | zabbix@localhost | zabbix | Sleep | select dcheckid,type,key_,snmp … re druleid=3 order by dcheckid | select dcheckid,type,key_,snmp … re druleid=3 order by dcheckid | 6 | 48.00 us |
| 22 | 17170 | zabbix@localhost | zabbix | Sleep | select itemid from items where … and flags<>2 and hostid=10084 | select itemid from items where … and flags<>2 and hostid=10084 | 5 | 60.00 us |
| 21 | 17154 | zabbix@localhost | zabbix | Sleep | commit | commit | 5 | 0 ps |
| 14 | 17165 | zabbix@localhost | zabbix | Sleep | commit | commit | 4 | 0 ps |
| 23 | 17153 | zabbix@localhost | zabbix | Sleep | commit | commit | 3 | 0 ps |
| 8 | 17159 | zabbix@localhost | zabbix | Sleep | select taskid,type,clock,ttl f … tatus in (1,2) order by taskid | select taskid,type,clock,ttl f … tatus in (1,2) order by taskid | 3 | 100.00 us |
| 20 | 17156 | zabbix@localhost | zabbix | Sleep | select escalationid,actionid,t … ,triggerid,itemid,escalationid | select escalationid,actionid,t … ,triggerid,itemid,escalationid | 3 | 47.00 us |
| 9 | 17150 | zabbix@localhost | zabbix | Sleep | select min(t.nextcheck) from h … tus=0 or h.maintenance_type=0) | select min(t.nextcheck) from h … tus=0 or h.maintenance_type=0) | 2 | 62.00 us |
| 11 | 17155 | zabbix@localhost | zabbix | Sleep | commit | commit | 2 | 0 ps |
| 458094 | 8986 | root@localhost | sys | Query | SET @sys.statement_truncate_le … (‘statement_truncate_len’, 64) | NULL | 1 | 996.00 us |
| 13 | 17182 | zabbix@localhost | zabbix | Sleep | select h.hostid,h.host,h.name, … tid and hd.parent_itemid=31891 | select h.hostid,h.host,h.name, … tid and hd.parent_itemid=31891 | 1 | 59.00 us |
| 17 | 17181 | zabbix@localhost | zabbix | Sleep | select h.hostid,h.host,h.name, … tid and hd.parent_itemid=31832 | select h.hostid,h.host,h.name, … tid and hd.parent_itemid=31832 | 1 | 81.00 us |
| 18 | 17152 | zabbix@localhost | zabbix | Sleep | commit | commit | 1 | 0 ps |
| 19 | 17183 | zabbix@localhost | zabbix | Sleep | commit | commit | 1 | 0 ps |
| 5 | NULL | sql/event_scheduler | NULL | Sleep | NULL | NULL | NULL | NULL |
±--------±------±--------------------±-------±--------±------------------------------------------------------------------±------------------------------------------------------------------±-----±-------------+
21 rows in set (0.08 sec)
#查看发生IO请求前5名的文件
mysql> select * from sys.io_global_by_file_by_bytes order by total limit 5;
#查看总共分配了多少内存
mysql> select * from sys.memory_global_total;
mysql> select * from sys.memory_global_by_current_bytes;
#每个库（database）占用多少buffer pool;pages是指在buffer pool中的page数量；pages_old指在LUR 列表中处于后37%位置的page。当出现buffer page不够用时，就会征用这些page所占的空间。37%是默认位置，具体可以自定义。
mysql> select * from sys.innodb_buffer_stats_by_schema order by allocated desc;
±--------------±----------±-----------±-------±-------------±----------±------------+
| object_schema | allocated | data | pages | pages_hashed | pages_old | rows_cached |
±--------------±----------±-----------±-------±-------------±----------±------------+
| mysql | 5.95 MiB | 3.33 MiB | 381 | 185 | 275 | 1380 |
| zabbix | 2.25 GiB | 1.74 GiB | 147654 | 104738 | 55439 | 401231 |
| sys | 16.00 KiB | 338 bytes | 1 | 0 | 1 | 6 |
±--------------±----------±-----------±-------±-------------±----------±------------+
3 rows in set (2.58 sec)
#统计每张表具体在InnoDB中具体的情况，比如占多少页
mysql> select * from sys.innodb_buffer_stats_by_table;
±--------------±-----------------------------±-----------±------------±------±-------------±----------±------------+
| object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached |
±--------------±-----------------------------±-----------±------------±------±-------------±----------±------------+
| zabbix | history | 873.78 MiB | 629.83 MiB | 55922 | 36446 | 25499 | 9101216 |
| zabbix | history_uint | 598.88 MiB | 449.53 MiB | 38328 | 27248 | 16863 | 6572995 |
| zabbix | trends | 457.16 MiB | 388.25 MiB | 29258 | 22582 | 7277 | 7040277 |
| zabbix | trends_uint | 337.12 MiB | 291.23 MiB | 21576 | 17373 | 4991 | 5291601 |
| zabbix | events | 16.50 MiB | 11.24 MiB | 1056 | 582 | 263 | 66166 |
| zabbix | history_str | 5.12 MiB | 2.52 MiB | 328 | 40 | 86 | 29315 |
#查询每个连接分配了多少内存
mysql> SELECT b.USER, current_count_used, current_allocated, current_avg_alloc, current_max_alloc, total_allocated, current_statement FROM sys.memory_by_thread_by_current_bytes a, sys.session b WHERE a.thread_id = b.thd_id;
#查看表自增字段最大值和当前值，有时候做数据增长的监控，可以作为参考
mysql> select * from sys.schema_auto_increment_columns;
#MySQL索引使用情况统计
mysql> select * from sys.schema_auto_increment_columns;
#MySQL中有哪些冗余索引和无用索引;若库中展示没有冗余索引，则没有数据；当有联合索引idx_abc(a,b,c)和idx_a(a)，那么idx_a就算冗余索引了。
mysql> select * from sys.schema_redundant_indexes;
#查看INNODB 锁信息
mysql> select * from sys.innodb_lock_waits;
#查看库级别的锁信息，这个需要先打开MDL锁的监控：
#打开MDL锁监控
update performance_schema.setup_instruments set enabled=‘YES’,TIMED=‘YES’ where name=‘wait/lock/metadata/sql/mdl’;
mysql> select * from performance_schema.setup_instruments where name=‘wait/lock/metadata/sql/mdl’;
±---------------------------±--------±------±-----------±-----------±--------------+
| NAME | ENABLED | TIMED | PROPERTIES | VOLATILITY | DOCUMENTATION |
±---------------------------±--------±------±-----------±-----------±--------------+
| wait/lock/metadata/sql/mdl | YES | YES | | 0 | NULL |
±---------------------------±--------±------±-----------±-----------±--------------+
1 row in set (0.00 sec)
#MySQL内部有多个线程在运行，线程类型及数量
mysql> select user,count(*) from sys.processlist group by user;
#查看MySQL自增id的使用情况
mysql> SELECT
-> table_schema,
-> table_name,
-> ENGINE,
-> Auto_increment
-> FROM
-> information_schema.TABLES
-> WHERE
-> TABLE_SCHEMA NOT IN ( “INFORMATION_SCHEMA”, “PERFORMANCE_SCHEMA”, “MYSQL”, “SYS” );
±-------------------±-----------------------------------------------------±-------------------±---------------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE | AUTO_INCREMENT |
±-------------------±-----------------------------------------------------±-------------------±---------------+
| information_schema | ADMINISTRABLE_ROLE_AUTHORIZATIONS | NULL | NULL |


#### 2.6、数据引擎

mysql> show engines;
±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
±-------------------±--------±---------------------------------------------------------------±-------------±-----±-----------+
9 rows in set (0.00 sec)

mysql> select @@global.transaction_isolation;
±-------------------------------+
| @@global.transaction_isolation |
±-------------------------------+
| READ-COMMITTED |
±-------------------------------+
1 row in set (0.00 sec)

mysql> show engine innodb status \G
* 1. row *
Type: InnoDB
Name:
Status:

2022-10-12 19:07:39 0x7fea8ffff700 INNODB MONITOR OUTPUT

Per second averages calculated from the last 35 seconds

BACKGROUND THREAD

srv_master_thread loops: 1908188 srv_active, 0 srv_shutdown, 6489032 srv_idle
srv_master_thread log flush and writes: 8397210

SEMAPHORES

OS WAIT ARRAY INFO: reservation count 3531015
OS WAIT ARRAY INFO: signal count 3558501
RW-shared spins 0, rounds 7415601, OS waits 3415283
RW-excl spins 0, rounds 748278, OS waits 20602
RW-sx spins 552, rounds 11861, OS waits 244
Spin rounds per wait: 7415601.00 RW-shared, 748278.00 RW-excl, 21.49 RW-sx

TRANSACTIONS

Trx id counter 19243668
Purge done for trx’s n:o < 19243668 undo n:o < 0 state: running but idle
History list length 32
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 422123507920512, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 422123507919600, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 422123507915952, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 422123507914128, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 422123507918688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 422123507917776, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 422123507916864, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 422123507912304, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 422123507913216, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 422123507915040, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 422123507910480, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 422123507911392, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

FILE I/O

I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o’s:, sync i/o’s:
Pending flushes (fsync) log: 0; buffer pool: 0
6223404 OS file reads, 24323047 OS file writes, 16317333 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 2.74 writes/s, 2.23 fsyncs/s

INSERT BUFFER AND ADAPTIVE HASH INDEX

Ibuf: size 1, free list len 0, seg size 2, 659 merges
merged operations:
insert 913, delete mark 103, delete 35
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 3 buffer(s)
Hash table size 34673, node heap has 9 buffer(s)
Hash table size 34673, node heap has 13 buffer(s)
Hash table size 34673, node heap has 2 buffer(s)
Hash table size 34673, node heap has 2 buffer(s)
Hash table size 34673, node heap has 6 buffer(s)
Hash table size 34673, node heap has 13 buffer(s)
Hash table size 34673, node heap has 2 buffer(s)
4.40 hash searches/s, 1.77 non-hash searches/s

LOG

Log sequence number 8268087675
Log flushed up to 8268087675
Pages flushed up to 8268087153
Last checkpoint at 8268087144
0 pending log flushes, 0 pending chkp writes
10746170 log i/o’s done, 1.46 log i/o’s/second

BUFFER POOL AND MEMORY

Total large memory allocated 137428992
Dictionary memory allocated 1082657
Buffer pool size 8191
Free buffers 1024
Database pages 7117
Old database pages 2607
Modified db pages 4
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 772098, not young 39906636
0.00 youngs/s, 0.00 non-youngs/s
Pages read 6223019, created 102083, written 11737689
0.00 reads/s, 0.00 creates/s, 1.03 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 7117, unzip_LRU len: 0
I/O sum[48]:cur[0], unzip sum[0]:cur[0]

ROW OPERATIONS

0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=19101, Main thread ID=140648158062336, state: sleeping
Number of rows inserted 12977848, updated 1936730, deleted 7422, read 388089484821
0.00 inserts/s, 0.26 updates/s, 0.00 deletes/s, 6.37 reads/s

END OF INNODB MONITOR OUTPUT

1 row in set (0.04 sec)

#结果说明
Log:Innodb 事务日志相关信息，包括当前的日志序列号（Log sequence number），已经刷新同步到那个序列号，最近的check point到那个序列号了。除此之外，还显示了系统从启动到现在已经做了多少次check point，多少次日志刷新。不同时刻的lsn的值的差值/时间差==日志的产生速度

Log sequence number 2560255（当前的日志序列号,log buffer中已经写入的LSN值,//字节，日志生成的最新位置，最新位置出现在log buffer中），表刷出去了多少日志；Log sequence number - Log flushed up to== 当前logbuffer的值<1M；不同时刻的差值/时间间隔==日志的写入速度

Log flushed up to 2560255（刷新到日志重做日志文件的lsn,已经刷新到redo logfile的LSN值//字节，日志已经写入到log file的位置，1-2=log buffer日志量，最好是<=1M）

Pages flushed up to 2560255（写入磁盘的脏页的lsn。记录在checkpoint中//字节，脏页的数量(日志字节数来衡量)，2-3=脏页的数量(日志字节为单位)）；Log sequence number - Pages flushed up to 值很小，说明脏页写入的很快

Last checkpoint at 2560246（刷新到磁盘的lsn,最近一次checkpoint时的LSN值//字节，共享表空间上的日志记录点，最后一次检查点，及崩溃恢复时指定的起点，3-4就是崩溃恢复多跑的日志，值越大说明需要提升checkpoint的跟进速度）；系统启动的时候，日志恢复的起点，肯定比Pfut的值低。防止系统崩；Log flushed up to - Last checkpoint at == 系统要恢复的日志数；Pages flushed up to - Last checkpoint at == checkpoint的跟进速度，如果大的话，说明checkpoint需要增大。


![在这里插入图片描述](https://img-blog.csdnimg.cn/2dbe12a840a34e8ea2d98e4af11aaa1e.png)


#### 2.7、基础巡检

#数据文件存放路径
mysql> show variables like ‘datadir’;
±--------------±--------------+
| Variable_name | Value |
±--------------±--------------+
| datadir | /video/mysql/ |
±--------------±--------------+
1 row in set (0.00 sec)
#告警 mysql错误日志存放路径
mysql> show variables where variable_name = ‘log_error’;
±--------------±----------------------------+
| Variable_name | Value |
±--------------±----------------------------+
| log_error | /video/mysql/log/mysqld.log |
±--------------±----------------------------+
1 row in set (0.00 sec)
#慢查询日志
mysql> show variables WHERE variable_name = ‘slow_query_log_file’;
±--------------------±--------------------------------------------+
| Variable_name | Value |
±--------------------±--------------------------------------------+
| slow_query_log_file | /video/mysql/yangguangcaigoutest02-slow.log |
±--------------------±--------------------------------------------+
1 row in set (0.00 sec)
#查询写入慢查询日志的时间阈值
mysql> show variables WHERE variable_name = ‘long_query_time’;
±----------------±---------+
| Variable_name | Value |
±----------------±---------+
| long_query_time | 2.000000 |
±----------------±---------+
1 row in set (0.00 sec)
#查询哪个sql消耗资源情况
mysql> select Id,User,Host,db,Time,Info from information_schema.PROCESSLIST where info is not null;
±-------±-----±----------±-----±-----±----------------------------------------------------------------------------------------------+
| Id | User | Host | db | Time | Info |
±-------±-----±----------±-----±-----±----------------------------------------------------------------------------------------------+
| 458094 | root | localhost | sys | 0 | select Id,User,Host,db,Time,Info from information_schema.PROCESSLIST where info is not null |
±-------±-----±----------±-----±-----±----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#查询是否锁表
show OPEN TABLES where In_use > 0;
#查询所有数据的大小
mysql> select concat(round(sum(data_length/1024/1024),2),‘MB’) as data from information_schema.tables;
±----------+
| data |
±----------+
| 1825.38MB |
±----------+
1 row in set (0.00 sec)

检查mysql数据库各个表空间的大小(数据空间和索引空间以及总和)

select TABLE_NAME,
concat(truncate(data_length/1024/1024/1024, 2), ‘GB’) as data_size,
concat(truncate(index_length /1024/1024/1024, 2), ‘GB’) as index_size,
truncate(data_length/1024/1024/1024, 2)+ truncate(index_length /1024/1024/1024, 2)
from information_schema.tables where TABLE_SCHEMA = ‘smapuum’ order by data_length desc;
#查看锁情况
mysql> show status like ‘innodb_row_lock_%’;
±------------------------------±------+
| Variable_name | Value |
±------------------------------±------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 124 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 15 |
| Innodb_row_lock_waits | 386 |
±------------------------------±------+
5 rows in set (0.04 sec)
#nnodb_row_lock_current_waits : 当前等待锁的数量
#Innodb_row_lock_time : 系统启动到现在，锁定的总时间长度
#Innodb_row_lock_time_avg : 每次平均锁定的时间
#Innodb_row_lock_time_max : 最长一次锁定时间
#Innodb_row_lock_waits : 系统启动到现在总共锁定的次数
#查询是否锁表
mysql> show OPEN TABLES where In_use > 0;
#锁等待的对应关系
mysql> mysql> select * from sys.innodb_lock_waits\G
#当前运行的所有事务
select * from information_schema.innodb_trx\G
#show status like ‘%跟下面变量%’;
Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。
Connections 试图连接MySQL服务器的次数。
Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。
Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。
Delayed_writes 用INSERT DELAYED写入的行数。
Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
Flush_commands 执行FLUSH命令的次数。
Handler_delete 请求从一张表中删除行的次数。
Handler_read_first 请求读入表中第一行的次数。
Handler_read_key 请求数字基于键读行。
Handler_read_next 请求读入基于一个键的一行的次数。
Handler_read_rnd 请求读入基于一个固定位置的一行的次数。
Handler_update 请求更新表中一行的次数。
Handler_write 请求向表中插入一行的次数。
Key_blocks_used 用于关键字缓存的块的数量。
Key_read_requests 请求从缓存读入一个键值的次数。
Key_reads 从磁盘物理读入一个键值的次数。
Key_write_requests 请求将一个关键字块写入缓存次数。
Key_writes 将一个键值块物理写入磁盘的次数。
Max_used_connections 同时使用的连接的最大数目。
Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。
Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。
Open_tables 打开表的数量。
Open_files 打开文件的数量。
Open_streams 打开流的数量(主要用于日志记载）
Opened_tables 已经打开的表的数量。
Questions 发往服务器的查询的数量。
Slow_queries 要花超过long_query_time时间的查询数量。
Threads_connected 当前打开的连接的数量。
Threads_running 不在睡眠的线程数量。
Uptime 服务器工作了多少秒。


#### 2.8、数据量统计


Mysql多使用InnoDB存储引擎，它的最小储存单元是页，一页大小就是**16k**。B+树叶子存的是数据，内部节点存的是键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据；


假设B+树的高度为2的话，即有一个根结点和若干个叶子结点。`这棵B+树的存放总记录数为=根结点指针数*单个叶子节点记录行数`。如果一行记录的数据大小为1k，那么单个叶子节点可以存的记录数 =16k/1k =16。非叶子节点内存放多少指针呢？我们假设主键ID为bigint类型，长度为8字节(int是32位，4字节)，而指针大小在InnoDB源码中设置为6字节，所以就是8+6=14字节，16k/14B =16\*1024B/14B = 1170因此，一棵高度为2的B+树，能存放`1170 * 16=18720`条这样的数据记录。同理一棵高度为3的B+树，能存放`1170 *1170 *16 =21902400`，即可以存放两千万左右的记录。B+树高度一般为1-3层，就可满足千万级别的数据存储。如果B+树想存储更多的数据，那树结构层级就会更高，查询一条数据时，消耗的磁盘IO也就大幅增加，查询性能就会变慢。当然我们可以分库分表，比如分表后单表数据量降低，树的高度变低，查询经历的磁盘io变少，从而提高效率；或者分区将数据分布存到多个数据文件上，提高查询的并行和减少数据查询的遍历数量；

#统计各数据库的数据量大小：bytes
mysql -uroot -p -e “SELECT table_schema AS ‘Database’, SUM(data_length + index_length)/1024/1024 AS ‘Total Size (MB)’ FROM information_schema.tables GROUP BY table_schema ORDER BY ‘Total Size (MB)’ DESC;”
#或登录后执行：其中，information_schema.tables的data_length、index_length默认单位为bytes,更多参看：https://dev.mysql.com/doc/refman/5.7/en/information-schema-tables-table.html

SELECT table_schema AS ‘Database’, SUM(data_length + index_length)/1024/1024 AS ‘Total Size (MB)’ FROM information_schema.tables GROUP BY table_schema ORDER BY ‘Total Size (MB)’ DESC;
#多表联合查看系统表统计索引大小，其中IF函数判断是否表压缩，round函数用于计算结果四舍五入到整数
mysql> SELECT a.NAME, a.FILE_FORMAT, a.ROW_FORMAT,
@page_size :=
IF(a.ROW_FORMAT=‘Compressed’,
b.ZIP_PAGE_SIZE, b.PAGE_SIZE)
AS page_size,
ROUND((@page_size * c.CLUST_INDEX_SIZE)
/(1024*1024)) AS pk_mb,
ROUND((@page_size * c.OTHER_INDEX_SIZE)
/(1024*1024)) AS secidx_mb
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES a
INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES b on a.NAME = b.NAME
INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS c on b.NAME = c.NAME
WHERE a.NAME LIKE ‘你的表名%’
ORDER BY a.NAME DESC;
#查看所有数据库容量大小
mysql> select table_schema as ‘数据库’,
sum(table_rows) as ‘记录数’,
sum(truncate(data_length/1024/1024, 2)) as ‘数据容量(MB)’,
sum(truncate(index_length/1024/1024, 2)) as ‘索引容量(MB)’
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;
#查看所有表容量大小，其中TRUNCATE() 函数，用于截断（或截短）数字的小数部分，保留指定位数的小数位，下面为保留两位小数
mysql> select table_schema as ‘数据库’,
table_name as ‘表名’,
table_rows as ‘记录数’,
truncate(data_length/1024/1024, 2) as ‘数据容量(MB)’,
truncate(index_length/1024/1024, 2) as ‘索引容量(MB)’
from information_schema.tables
order by data_length desc, index_length desc;

#查看单个数据库的大小
mysql -uroot -ppassword -e “SELECT table_name AS ‘Table’, sum(data_length + index_length)/1024 AS ‘Size (MB)’ FROM information_schema.tables WHERE table_schema = ‘database_name’ ORDER BY ‘Size (MB)’ DESC;”
#查看单个表的详细大小信息
mysql -uroot -ppassword -e “SELECT table_schema, table_name, round((sum(data_length + index_length) /1024/1024), 2) AS ‘Size (MB)’ FROM information_schema.tables WHERE table_name = ‘table_name’;”

#查询当天的数据记录
mysql> SELECT * FROM 表名 WHERE TO_DAYS(日期列) = TO_DAYS(NOW());
mysql> SELECT * FROM 表名 WHERE DATE(日期列) = CURDATE();

#统计一定范围内，每个单位时间内的数据数量【单位可以天，月、周、年、等】

#按天统计：
SELECT DATE_FORMAT( 日期列, ‘%Y-%m-%d’ ) days, count(*) FROM 表名 GROUP BY days;
#按周统计：
SELECT DATE_FORMAT( 日期列, ‘%Y-%u’ ) weeks, count(*) FROM 表名GROUP BY weeks;
#按月统计：
SELECT DATE_FORMAT( 日期列, ‘%Y-%m’ ) months, count(*) FROM 表名 GROUP BY months;

#统计最近七天内的数据并按天分组：
mysql> SELECT DATE_FORMAT( 日期列, ‘%Y-%m-%d’ ) dates,count(*) FROM ( SELECT * FROM 表名 WHERE DATE_SUB( CURDATE(), INTERVAL 7 DAY )<= date( 日期列 ) ) AS v GROUP BY dates;

#查询1分钟内的数据
mysql> SELECT * FROM 表名 WHERE 日期列 >= CURRENT_TIMESTAMP - INTERVAL 1 MINUTE;

#查询一个数据库中每个表的总行数，数据大小，索引大小和总大小，CONCAT_WS字符串函数，用于连接多个字符串，并在它们之间插入一个指定的分隔符，语法：CONCAT_WS(separator, str1, str2, …);

SELECT concat_ws(‘.’,a.table_schema ,a.table_name),CONCAT(a.table_schema,‘.’,a.table_name),
CONCAT(ROUND(table_rows/1000,4),‘KB’) AS ‘Number of Rows’,
CONCAT(ROUND(data_length/(10241024),4),‘,’) AS ‘data_size’,
CONCAT(ROUND(index_length/(10241024),4),‘M’) AS ‘index_size’, CONCAT(ROUND((data_length+index_length)/(1024*1024),4),‘M’) AS’Total’
FROM information_schema. TABLES a WHERE a.table_schema = ‘要查询的库名’ ORDER BY ‘Total’ DESC;

#查看特定数据库特定表的数据部分大小，索引部分大小和总占用磁盘大小，加LIMIT 1,30查看指定行的数据

SELECT
a.table_schema ,
a.table_name ,
concat(round(sum(DATA_LENGTH / 1024 / 1024) + sum(INDEX_LENGTH / 1024 / 1024) ,2) ,‘MB’) total_size ,
concat(round(sum(DATA_LENGTH / 1024 / 1024) , 2) ,‘MB’) AS data_size ,
concat(round(sum(INDEX_LENGTH / 1024 / 1024) , 2) ,‘MB’) AS index_size
FROM
information_schema. TABLES a
WHERE
a.table_schema = ‘db_name’
AND a.table_name = ‘table_name’;

查询每日数据增量，用CURDATE() 获取当前日期，通过 DATE(date_column) = CURDATE() - INTERVAL 1 DAY 条件筛选前一天的数据；使用COUNT() - (SELECT COUNT()) 计算每日数据增量，sql如下（执行报错，待进一步优化）：

SELECT
table_name,
CURDATE() AS today,
COUNT() AS today_count,
(
SELECT COUNT()
FROM table_name
WHERE DATE(date_column) = CURDATE() - INTERVAL 1 DAY
) AS yesterday_count,
COUNT() - (
SELECT COUNT()
FROM table_name
WHERE DATE(date_column) = CURDATE() - INTERVAL 1 DAY
) AS daily_increment
FROM information_schema.tables T
WHERE table_schema = ‘your_database_name’
AND table_type = ‘BASE TABLE’;
#存储过程
DELIMITER //

CREATE PROCEDURE CalculateDailyIncrement()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE tableName VARCHAR(255);
DECLARE curTables CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = ‘BASE TABLE’;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN curTables;

readTables: LOOP
    FETCH curTables INTO tableName;

    IF done THEN
        LEAVE readTables;
    END IF;

    SET @query = CONCAT('

SELECT
“', tableName, '” AS table_name,
CURDATE() AS today,
COUNT(*) AS today_count,
(
SELECT COUNT(*)
FROM ', tableName, ’
WHERE DATE(date_column) = CURDATE() - INTERVAL 1 DAY
) AS yesterday_count,
COUNT(*) - (
SELECT COUNT(*)
FROM ', tableName, ’
WHERE DATE(date_column) = CURDATE() - INTERVAL 1 DAY
) AS daily_increment
FROM ', tableName, ’
WHERE DATE(date_column) = CURDATE();
');

    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END LOOP;

CLOSE curTables;

END //

DELIMITER ;
#调用
CALL CalculateDailyIncrement();


### 三、数据库优化


MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈，CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么平瓶颈就会出现在网络上，我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态；使用索引，使用EXPLAIN分析查询以及调整MySQL的内部配置；常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，以更好的优化数据库系统的性能。大体我们可从以下几方面进行优化考虑：1.MySQL集群架构；2. 性能问题排查；3. 优化方法；4. 其他；


#### 1）慢查询优化


![在这里插入图片描述](https://img-blog.csdnimg.cn/ea3a53ca3e1147c9a346f97e5edeff3e.png#pic_center)

[mysqld]

slow_query_log = on
slow-query-log-file=/var/log/mysql/slowquery.log
long_query_time=2 #查询超过两秒才记录
log-queries-not-using-indexes #表示记录下没有使用索引的查询

mysql> show variables like “slow%”;
#查询long_query_time 的值
mysql> show variables like “long%”;
#修改慢查询时间为5秒
mysql> set long_query_time = 5;


因mysql-slow.log慢查询日志里记录了所有慢查询的sql语句，more mysql-slow.log来管哈哪些语句执行有问题，找出影响最大的，跟研发侧评估论证确定优化方案。也可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总。它会对慢查询日志文件进行了分类汇总，显示汇总后摘要结果，可以非常明确的得到各种我们需要的查询语句。执行：

mysqldumpslow mysql-slow.log
#输出记录次数最多的10条SQL语句
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log
#返回记录集最多的10个查询
mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log
#按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g “left join” /var/log/mysql/mysql-slow.log


参数说明：



> 
> -s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；  
>    
>  -t, 是top n的意思，即为返回前面多少条的数据；  
>    
>  -g, 后边可以写一个正则匹配模式，大小写不敏感的；
> 
> 
> 


**第2种就是explain分析查询**：通过它优化器可模拟执行SQL查询语句，从而知道MySQL是如何处理哪些SQL语句的，从而分析那些查询语句或是表结构的性能瓶颈。通过explain命令可以得到:



> 
> * 表的读取顺序
> * 数据读取操作的操作类型
> * 哪些索引可以使用
> * 哪些索引被实际使用
> * 表之间的引用
> * 每张表有多少行被优化器查询
> 
> 
> 


**第3种就是profiling分析查询**：



> 
> 通过慢日志查询可以知道哪些SQL语句执行效率低下，通过explain我们可以得知SQL语句的具体执行情况，索引使用等，还可以结合show命令查看执行状态。而通过profiling命令得到更准确的SQL执行消耗系统资源的信息，通过profiling资源耗费信息，我们可以采取针对性的优化措施；profiling默认是关闭的。
> 
> 
>

mysql> select @@profiling;
#开启
mysql> set profiling=1;
mysql> show profiles\G; #可以得到被执行的SQL语句的时间和ID
mysql> show profile for query 1; #得到对应SQL语句执行的详细信息
mysql> set profiling=0


#### 2）索引


**1、索引的类型**



> 
> Ø **普通索引**：这是最基本的索引类型，没唯一性之类的限制。  
>    
>  Ø **唯一性索引**：和普通索引基本相同，但所有的索引列值保持唯一性。  
>    
>  Ø **主键**：主键是一种唯一索引，但必须指定为”PRIMARY KEY”。  
>    
>  Ø **全文索引**：MYSQL从3.23.23开始支持全文索引和全文检索。在MYSQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。  
>    
>  Ø **其他索引**：大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)使用**B树**中存储。空间列类型的索引使用**R-**树，MEMORY表支持**hash索引**。  
>    
>  Ø **单列索引和多列索引（复合索引）**：  
>    
>  索引可以是单列索引，也可以是多列索引。对相关的列使用索引是提高SELECT操作性能的最佳途径之一。  
>    
>  **多列索引：**  
>    
>  MySQL可以为多个列创建索引。一个索引可以包括15个列。对于某些列类型，可以索引列的左前缀，列的顺序非常重要。  
>    
>  多列索引可以视为包含通过**连接索引列**的值而创建的值的排序的数组。一般来说，即使是限制最严格的单列索引，它的限制能力也远远低于多列索引。  
>    
>  多列索引有一个特点，即**最左前缀**（Leftmost Prefixing）。假如有一个多列索引key(firstname lastname age)，当搜索条件是以下各种列的组合和顺序时，MySQL将使用该多列索引，也就是说，相当于还建立了key(firstname lastname)和key(firstname)。：  
>    
>  firstname，lastname，age  
>    
>  firstname，lastname  
>    
>  firstname
> 
> 
> 


**2、索引的使用**



> 
> Ø 快速找出匹配一个WHERE子句的行。  
>    
>  Ø 删除行。当执行联接时，从其它表检索行。  
>    
>  Ø 对具体有索引的列key\_col找出MAX()或MIN()值。由预处理器进行优化，检查是否对索引中在key\_col之前发生所有关键字元素使用了WHERE key\_part\_# = constant。在这种情况下，MySQL为每个MIN()或MAX()表达式执行一次**关键字查找**，并用常数替换它。如果所有表达式替换为常量，查询立即返回。例如：SELECT MIN(key2), MAX (key2) FROM tb WHERE key1=10;  
>    
>  Ø 如果对一个可用关键字的**最左面**的前缀进行了排序或分组(例如，ORDER BY key\_part\_1,key\_part\_2)，排序或分组一个表。如果所有关键字元素后面有DESC，关键字以倒序被读取。  
>    
>  Ø 在一些情况中，可以对一个查询进行优化以便不用查询数据行即可以检索值。如果查询只使用来自某个表的数字型并且构成某些关键字的最左侧前缀的列，为了更快，可以从索引树检索出值。  
>  SELECT key\_part3 FROM tb WHERE key\_part1=1
> 
> 
> 


**合理的建立索引的建议：**



> 
> (1) 越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。  
>    
>  (2) 简单的数据类型更好：**整型数据**比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。  
>    
>  (3) **尽量避免NULL**：应该指定**列为NOT NULL**，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值;
> 
> 
> 


SQL语句注意事项：



> 
> 1、当结果集只有一行数据时使用 Limit 1  
>    
>  2、避免SELECT \*，始终指定你需要的列  
>    
>  从表中读取越多的数据，查询会变得更慢。这增加了磁盘需要操作的时间，还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟，仅仅是因为数据不必要的在服务器之间传输。  
>    
>  3、**使用连接（JOIN）来代替子查询(Sub-Queries)**  
>    
>  连接（JOIN）之所以更有效率一些，是因为MySQL**不需要在内存中创建临时表**来完成这个逻辑上的需要两个步骤的查询工作。  
>    
>  4、使用ENUM、CHAR 而不是VARCHAR，使用合理的字段属性长度  
>    
>  5、尽可能的使用NOT NULL  
>    
>  6、 固定长度的表会更快  
>    
>  7、 拆分大的DELETE 或INSERT 语句  
>    
>  8、查询的列越小越快  
>    
>  9、尽量少且合理使用where条件，在多个条件的时候，把会提取尽量少数据量的条件放在前面，减少后一个where条件的查询时间。有些where条件会导致索引无效：
> 
> 
> 
> > 
> > Ø where子句的查询条件里有！=，MySQL将无法使用索引。  
> >  Ø where子句使用了Mysql函数的时候，索引将无效，比如：select \* from tb where left(name, 4) = ‘xxx’  
> >  Ø 使用LIKE进行搜索匹配的时候，这样索引是有效的：select \* from tbl1 where name like ‘xxx%’，而like ‘%xxx%’ 时索引无效
> > 
> > 
> > 
> 
> 
> 


3）Mysql配置优化


这里主要关注对性能优化影响较大的变量，简单分为连接请求的变量和缓冲区变量。


● 最大连接数：max\_connections


MySQL的最大连接数，增加该值就是增加mysqld 能打开的**文件描述符**的数量。如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，但是连接数越多同时也会加大主机负载，数值过小又会经常出现**ERROR 1040: Too many connections**错误，可以过\*\*’conn%’**通配符查看**当前状态的连接数量\*\*，以核定该值的大小。MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，尽量取一个平衡。

mysql> show variables like ‘max_connections’ #最大连接数
mysql> show status like ‘max_used_connections’#响应的连接数


**参考标准：** max\_used\_connections / max\_connections \* 100% （理想值≈ 85%） ；如果max\_used\_connections跟max\_connections相同 那么就是max\_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。


● MySQL能暂存的连接数量： back\_log



> 
> 如果MySQL的连接数据达到max\_connections时，新来的请求将会被存在**堆栈**中，以等待某一连接释放资源，该堆栈的数量即**back\_log**，如果等待连接的数量超过back\_log，将不被授予连接资源，被拒绝连接。  
>    
>  back\_log的值指出在MySQL暂时停止回答新请求之前的**短时间内有多少个请求**可以被**存在堆栈**中。只有当现场实际确实存在一个短时间内有很多连接的情况，简单理解，这值可表到来的TCP/IP连接的侦听队列的大小。当观察mysql进程列表（mysql> show full processlist），发现大量264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大back\_log 的值了。
> 
> 
> 


**参考标准：** 默认数值是50，可调优为128，对于Linux系统设置范围为小于512的整数。


● 交互连接超时时间：interactive\_timeout



> 
> 该值表一个交互连接在被服务器**在关闭前等待行动的秒数**。一个交互的客户被定义为对mysql\_real\_connect()使用CLIENT\_INTERACTIVE 选项的客户。
> 
> 
> 


**参考标准：** 默认数值是28800，可调优为7200。


● 索引缓冲区 ：key\_buffer\_size —>只对**MyISAM表**起作用



> 
> key\_buffer\_size可指定索引缓冲区的大小，它**决定索引处理的速度**，尤其是**索引读**的速度，增加它可得到更好处理的索引(对所有读和多重写)，对MyISAM表性能影响最大的一个参数。如果太大，系统将开始换页并且真的变慢了。严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。通过检查状态值Key\_read\_requests和Key\_reads，可以知道key\_buffer\_size设置是否合理。比例key\_reads / key\_read\_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key\_read%’获得）。对于内存在4GB左右的服务器该参数可设置为256M或384M。
> 
> 
> 


其中，key\_buffer\_size只对**MyISAM表**起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created\_tmp\_disk\_tables得知详情。通过检查状态值Key\_read\_requests和Key\_reads，利用公式计算得到该值表现情况，key\_reads / key\_read\_requests应该尽可能的低，比如1:100，1:1000 ，1:10000。

mysql> show variables like ‘key_buffer_size‘;
mysql> show global status like ‘key_read%‘; #其中，Key_reads表多少个请求在内存中没有找到，直接从硬盘读取索引


计算**索引未命中缓存**的概率：



> 
> key\_cache\_miss\_rate ＝Key\_reads / Key\_read\_requests \* 100%
> 
> 
> 


**参考标准：** 默认配置数值是8388600(8M)，建议设置在1/1000左右较好，主机有4GB内存，可以调优值为268435456(256MB)。


● 查询缓冲：query\_cache\_size



> 
> 主要用来缓存MySQL中的ResultSet，也就是**一条SQL语句执行的结果集**。因MySQL会将**查询结果**存放在缓冲区中，之后对于同样的SELECT语句（区分大小写），就可直接从缓冲区中读取结果。Query Cache也有一个致命的缺陷，那就是当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache可能会得不偿失；Query Cache的使用需要多个参数配合，其中最为关键的是query\_cache\_size和query\_cache\_type，前者设置用于缓存 ResultSet的内存大小，后者设置在何场景下使用Query Cache  
>    
>  通过检查**状态值**Qcache\_\*，可以知道query\_cache\_size设置是否合理（上述状态值可以使用**SHOW STATUS LIKE ‘Qcache%’** 获得）。如果Qcache\_lowmem\_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache\_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache\_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL\_NO\_CACHE可以明确表示不使用查询缓冲。另外与查询缓冲有关的参数还有query\_cache\_type、query\_cache\_limit、query\_cache\_min\_res\_unit。  
>    
>  query\_cache\_type指定是否使用查询缓冲，可以设置为0、1、2，该变量是SESSION级的变量。  
>    
>  query\_cache\_limit指定单个查询能够使用的缓冲区大小，缺省为1M。  
>    
>  query\_cache\_min\_res\_unit是在4.1版本以后引入的，它指定分配缓冲区空间的最小单位，缺省为4K。检查状态值Qcache\_free\_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小query\_cache\_min\_res\_unit。
> 
> 
>

mysql> show global status like ‘qcache%‘;
mysql> show status like ‘Qcache_%’; //查看目前系统Query catch使用大小
mysql> show variables like ‘query_cache%‘;



> 
> * **查询缓存碎片率**= Qcache\_free\_blocks / Qcache\_total\_blocks \* 100%;
> * **查询缓存利用率**= (query\_cache\_size – Qcache\_free\_memory) / query\_cache\_size \* 100%
> * **查询缓存命中**率= (Qcache\_hits – Qcache\_inserts) / Qcache\_hits \* 100%
> 
> 
> 


**参考标准：** 如果查询缓存碎片率**超过20%**，可以用FLUSH QUERY CACHE**整理缓存碎片**，或者试试减小query\_cache\_min\_res\_unit，如果你的查询都是小数据量的话。



> 
>   
>  查询缓存利用率在**25%以下**的话说明query\_cache\_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache\_lowmem\_prunes > 50的话说明query\_cache\_size可能有点小，要不就是碎片太多。
> 
> 
> 


● 单进程缓冲区：record\_buffer\_size



> 
> 每个进行一顺序扫描的线程都会为其扫描的每张表分配该值指定大小的一个缓冲区。如果存在很多顺序扫描，可能需要增加该值。
> 
> 
> 


**参考标准：** 默认数值是131072(128K)，可改为16773120 (16M)


● 随机读缓冲区：read\_rnd\_buffer\_size



> 
> MySql的随机读缓冲区大小。当按**任意顺序读**取行时(例如，按照排序顺序)，将分配一个**随机读缓存区**。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要**排序大量数据**，可适当调高该值。但MySQL会为**每个客户连接**发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
> 
> 
> 


**参考标准：** 一般可设置为16M


● 排序线程缓冲区：sort\_buffer\_size



> 
> 是MySql执行排序使用的缓冲大小；每个需要进行排序的**线程**分配该大小的一个缓冲区。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort\_buffer\_size变量的大小增加这值**加速**ORDER BY或GROUP BY操作。
> 
> 
> 


**参考标准：** 默认数值是2097144(2M)，可改为16777208 (16M)。


● 联合查询缓冲区：join\_buffer\_size



> 
> 联合查询操作所能使用的缓冲区大小
> 
> 
> 


record\_buffer\_size，read\_rnd\_buffer\_size，sort\_buffer\_size，join\_buffer\_size为每个线程独占，也就是说，如果有100个线程连接，则占用为16M\*100


● 表高速缓存： table\_cache



> 
> 每当MySQL访问一个表时，如果在**表缓冲区**中还有空间，该表就被打开并放入其中，这样可以**更快地访问表**内容。通过检查**峰值时间**的状态值Open\_tables和Opened\_tables，可以决定是否需要增加table\_cache的值。如果你发现open\_tables等于table\_cache，并且opened\_tables在不断增长，那么你就需要增加table\_cache的值了（上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得）。注意，不能盲目地把table\_cache设置成很大的值。如果设置得太高，可能会**造成文件描述符不足**，从而造成性能不稳定或者连接失败。
> 
> 
> 


**参考标准：** 1G内存机器，推荐值是128－256。内存在4GB左右的服务器该参数可设置为256M或384M。


● 用户最大内存表： max\_heap\_table\_size



> 
> 该值表用户可以创建的内存表(memory table)的大小。这个值用来**计算内存表的最大行数值**。这个变量**支持动态改变**，即set @max\_heap\_table\_size=#；这个变量和tmp\_table\_size一起限制了内部内存表的大小。如果某个内部heap（堆积）表大小超过tmp\_table\_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。
> 
> 
> 


● 临时表大小：tmp\_table\_size



> 
> 它是MySql的heap (堆积)表缓冲大小。所有联合在一个DML指令内完成，并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内存的(HEAP)表。如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的MyISAM表。如果某个内部heap(堆积)表大小超过tmp\_table\_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp\_table\_size选项来增加临时表的大小。也就是说，如果调高该值，MySql同时将增加heap表的大小，可达到提高联接查询速度的效果。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。通过设置tmp\_table\_size选项来**增加一张临时表的大小**，例如：做高级GROUP BY操作生成的临时表。
> 
> 
>

mysql> show global status like ‘created_tmp%‘;


**参考标准：** 每次创建临时表，Created\_tmp\_tables增加，如果临时表大小超过tmp\_table\_size，则是在磁盘上创建临时表，Created\_tmp\_disk\_tables也增加,Created\_tmp\_files表示MySQL服务创建的临时文件文件数，比较理想的配置是：



> 
> * Created\_tmp\_disk\_tables / Created\_tmp\_tables \* 100% <= 25%
> * Created\_tmp\_disk\_tables / Created\_tmp\_tables \* 100% ＝1.20%
> 
> 
> 


默认为16M，可调到64-256最佳，线程独占，太大可能内存不够I/O堵塞


● 线程复用缓冲区： thread\_cache\_size



> 
> 可以复用的保存在缓存中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。通过比较 Connections和Threads\_created状态的变量，可以看到这个变量的作用。  
>    
>  mysql> show status like ‘thread%’;
> 
> 
> 


连接线程池的命中率：



> 
> (Connections - Threads\_created) / Connections \* 100 % #命中率超过90%以上,设定合理
> 
> 
> 


**参考标准：** 默认值为110，可调优为80。 根据物理内存设置规则如下：1G —> 8，2G —> 16，3G —> 32 ，3G —> 64


● thread\_concurrency


**参考标准：** 推荐设置为服务器 CPU核数的2倍，例如双核的CPU, 那么thread\_concurrency的应该为4；2个双核的cpu, thread\_concurrency的值应为8。默认为8


● wait\_timeout



> 
> 指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。
> 
> 
> 


● innodb\_buffer\_pool\_size



> 
> 对于InnoDB表来说，innodb\_buffer\_pool\_size的作用就相当于key\_buffer\_size对于MyISAM表的作用一样，对InnoDB表性能影响最大的一个参数。InnoDB使用该参数指定大小的**内存来缓冲数据和索引**。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%。不同的是，MyISAM 的 key\_buffer\_size 只能缓存索引键，而 innodb\_buffer\_pool\_size 却可以缓存数据块和索引键。适当的增加这个参数的大小，可以有效的减少 InnoDB 类型的表的磁盘 I/O 。可以通过 ：  
>    
>  show status like ‘Innodb\_buffer\_pool\_read%’;  
>    
>  (Innodb\_buffer\_pool\_read\_requests – Innodb\_buffer\_pool\_reads) / Innodb\_buffer\_pool\_read\_requests \* 100% 计算缓存命中率，并根据命中率来调整 innodb\_buffer\_pool\_size 参数大小进行优化，命中率越高越好。
> 
> 
> 


**参考标准：** 根据MySQL手册，对于2G内存的机器，推荐值是1G（50%）。


● innodb\_flush\_log\_at\_trx\_commit



> 
> 主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；1，则在每秒钟或是每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；设置为2，每次事务提交引起写入日志文件的动作，但每秒钟完成一次flush磁盘操作。
> 
> 
>