Mysql性能瓶颈深度定位分析smooth的博客-

       我们在性能测试过程中，经常会遇到Mysql出现性能瓶颈的情况，对于数据库来说，所谓的性能瓶颈无非是慢SQL、CPU高、IO高、内存高，其中前三个举实际例子来进行性能分析，最后内存高只是方法性说明（实际测试项目中没遇到过）：

       首先我们要保证没有数据库配置方面的性能问题，毕竟在性能测试前，对一些基本配置要撸一遍，避免犯低级错误。

       本文结合实际项目例子进行分析（绝对硬核），包括内容：一、慢SQL定位分析；二、高CPU定位分析；三、高IO定位分析；四、高内存定位分析。

一、慢SQL定位分析

       首先业务系统慢，肯定是体现在响应时间上，所以在性能测试中，如果发现慢我们就从响应时间上进行拆分，最后拆到mysql，那就是分析慢SQL，同样如果在高并发时发现mysql进程占CPU很高，也是优先分析是否存在慢SQL，而且判断慢SQL还是比较简单的，对于Mysq就是看慢日志查询。

1、首先是开启慢日志查询：

 #查看是否开启，以及存放路径 show variables like '%slow_query_log%';  #开启 set global slow_query_log = 1;  #记录慢日志的时间，默认情况下为10秒 show variables like '%long_query_time%'  #查看慢日志条数 show global status like '%slow_queries%'

使用set global slow_query_log=1;开启慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。想要永久生效，就必须修改配置文件，其实没这必要，我们都是临时开启，分析性能问题而已（分析完了，还得关了）。

2、测试过程获取慢SQL

要手工分析日志，查找和分析SQL，显然是个体力活，MySql提供了日志分析工具mysqldumpslow

 #得到返回记录集最多的10个SQL Mysqldumpslow –s r –t 10 /usr/local/mysql/data/localhost-slow.log #得到访问次数最多的10个SQL Mysqldumpslow –s c –t 10 /usr/local/mysql/data/localhost-slow.log #得到按照时间排序的前10条里面含有左连接的查询 Mysqldumpslow –s t –t 10 –g “left join” /usr/local/mysql/data/localhost-slow.log #另外建议在使用这些命令时结合|和more使用，否则可能出现爆破情况 Mysqldumpslow –s r –t 10 /usr/local/mysql/data/localhost-slow.log | more

 参数含义 s: 表示按照何种方式排序 c：访问次数 l：锁定时间 r：返回记录 t：查询时间 al：平均锁定时间 t：返回前面多少条的数据 g：后面搭配一个正则表达式

除此之外，你们也可以通过APM监控（全链路监控），也是能监控到慢SQL（当然压测过程中不建议依赖一些重型工具）：

3、初步Explain分析

这是最基础的功能，获取到慢SQL，当然是要实际验证一下有多慢，是否索引配置了，拿一条实际测试项目的SQL语句来分析：

 explain SELECT count(c.id)         FROM administrative_check_content c         LEFT JOIN administrative_check_report_enforcers e ON c.report_id=e.report_id         LEFT JOIN administrative_check_report r ON c.report_id = r.id         WHERE e.enforcer_id= 'ec66d95c8c6d437b9e3a460f93f1a592';

可以分析出这条语句，86%的时间是花在了Sending data（所谓的“Sending data”并不是单纯的发送数据，而是包括“收集 [检索] + 发送数据”）：

通过Explain解释也能看出索引已经加了（enforcer_id_index），而且通过索引几乎全表检索了30084条数据，如下：

 一般如果索引没加或是加的不合理，通过这么一分析也就能马上看出来，可以说索引问题是导致慢SQL的最主要原因之一，也是影响业务系统性能的关键因素，以下以本次压测项目的例子来说，没加索引时最高TPS只有200，加了索引最高TPS达到900，如下所示：

在Explain解释语句中，跟索引有关的列我们主要关注以下几个：

（1）type
这列很重要，显示了连接使用了哪种类别，有无使用到索引。一般从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL。

（2）possible_keys
possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询。
（3） key
key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
（4）key_len
key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用索引的长度，在不损失精确性的情况下，长度越短越好。
（5）ref
ref列显示使用哪个列或常数与key一起从表中选择行。这一列涉及到多表关联的字段，const表示常数关联。 ref很多时候也是和索引有关联影响的地方。

4、用show profile进行sql分析

开启分析也很简单，使用临时开启执行set profiling=1即可（这个功能会缓存最近查询的分析语句，默认15条，最多100条，适合在压测结束后开展sql分析，用完后再设成0关闭），如下：

 #显示是否开启Profiling，以及最多存储多少条 show variables like '%profil%';  #开启Profiling set profiling=1;  #执行你的SQL #在这里我们主要是执行前面所找到的慢SQL  #查看分析 show profiles;

通过show profiles我们可以看到我们上面执行的那条SQL（Query_ID=18）：

 执行：show profile cpu,memory,block io for query 18;

可以看出也是Sending data占用的CPU时间最多（简单的一条SQL语句占用这些时间就算很高了），另外还能看到这条SQL的IO开销（因为查询，都是ops out输出）。

另外说明一下这个show profile语句：

 show profile cpu, block io, memory,swaps,context switches,source for query [Query_ID];  # Show profile后面的一些参数： # - All：显示所有的开销信息 # - Cpu：显示cpu相关开销 # - Block io：显示块IO相关开销 # - Context switches： 上下文切换相关开销 # - Memory：显示内存相关开销 # - Source：显示和source_function,source_file,source_line相关的开销信息 

结论：通过一条慢SQL我们就能追本溯源找到它慢的原因，这样就能很好的指导性能调优了（性能测试工程师可以不会调优，但是不会性能分析还真不行，一遇到问题只会说慢，却不会告诉开发人员到底哪慢的测试工程师，应该很难让开发人员敬仰，甚至难以收获尊重！）。

二、高CPU定位分析

1、SQL引起的高CPU

在性能压测过程中，导致数据库CPU很高的原因有很多一种，一般和慢SQL也有关（因为每条SQL要么占CPU高，要么占IO高，大体是这样），那么如何分析到是某些SQL引起的呢？

（1）首先定位占用CPU高的进程

通过TOP命令找到Mysql占用CPU高，再看mysql进程下有多少线程是占用CPU高的：

 # top -p [pid] H top -p 44662 H

可以看到有6个在Running，CPU都挺高的，36个在Sleeping，其中两个Sleeping的CPU也挺高的。

（2）我们在mysql中使用 SHOW FULL PROCESSLIST; 查询（通过FULL不仅能显示所有连接的线程，而且能显示出正在执行的完整SQL语句），如下：

可以看到有不少是Sending data状态的，我们挑选其中最复杂的一条语句来分析：

 (SELECT r.id,c.check_action_name,check_date,check_end_date,c.id AS check_content_id,c.check_object_name AS checkObjectName,c.update_time,c.verify          FROM administrative_check_content c          LEFT JOIN administrative_check_report r ON c.report_id=r.id          LEFT JOIN administrative_check_report_enforcers e ON r.id=e.report_id          WHERE e.enforcer_id= 'ec66d95c8c6d437b9e3a460f93f1a592'                        )               UNION ALL              (              SELECT r.id,r.check_action_name,check_date,check_end_date,'0','无' AS checkObjectName,r.update_time,false              FROM administrative_check_report r              LEFT JOIN administrative_check_report_enforcers e ON r.id=e.report_id              WHERE e.enforcer_id= 'ec66d95c8c6d437b9e3a460f93f1a592'              AND r.check_content_id='0'                             )               ORDER BY update_time DESC,check_content_id          LIMIT 0, 15  

（3）把这条语句用前面提到show profile进行分析：

 可以看到有两Sending data占用时间都挺高的，花费时间也高，其实这语句用了联合查询，我们可以把SQL语句拆分了继续分析（复杂语句都是由简单语句组成，比较不爽的是有时候分离出来的语句也很慢），拆出一条语句继续分析：

 SELECT r.id,c.check_action_name,check_date,check_end_date,c.id AS check_content_id,c.check_object_name AS checkObjectName,c.update_time,c.verify          FROM administrative_check_content c          LEFT JOIN administrative_check_report r ON c.report_id=r.id          LEFT JOIN administrative_check_report_enforcers e ON r.id=e.report_id          WHERE e.enforcer_id= 'ec66d95c8c6d437b9e3a460f93f1a592'  

执行这条拆分出来的语句，查询时间都需要0.8秒多，如下：

执行 EXPLAIN 分析，看到通过索引查询到的内容多达30084行，如下所示：

一般像这样的系统进行压测，多半是数据分布不合理，测试数据没有反应真实的业务场景，明显数据分布不均衡，一个ID号就关联三万条数据，使用索引的效率都没能体现出来。

总结：通过SHOW PROCESSLIST;我们可以知道Mysql当前的线程状态，以及主要资源消耗在哪方面；再结合show profile分析具体占用CPU高的SQL，可以进一步定位出SQL引起高CPU的原因，到这一步无疑就能指导开发人员的优化方向了。

2、其他原因引起的高CPU

基本上和上面的分析思路差不多，排除SQL原因（SQL引起的问题主要集中于CPU或IO，IO高有时候也会间接导致CPU高），其他原因引起的高CPU，可通过mysql show processlist + show status + kill Id的方式进行定位。

（1）首先，通过SHOW PROCESSLIST查询mysql线程状态，我们需要重点了解State列不同状态所代表的含义：

 Checking table 　正在检查数据表（这是自动的）。 Closing tables 　正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。 Connect Out 　复制从服务器正在连接主服务器。 Copying to tmp table on disk 　由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存（如果临时表过大会导致mysql将临时表写入硬盘的时间过长，会影响整体性能）。 Creating tmp table 　正在创建临时表以存放部分查询结果。 deleting from main table 　服务器正在执行多表删除中的第一部分，刚删除第一个表。 deleting from reference tables 　服务器正在执行多表删除中的第二部分，正在删除其他表的记录。 Flushing tables 　正在执行FLUSH TABLES，等待其他线程关闭数据表。 Killed 　发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查 kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。 Locked 　被其他查询锁住了。 Sending data 　正在处理SELECT查询的记录，同时正在把结果发送给客户端。 Sorting for group 　正在为GROUP BY做排序。 　Sorting for order 　正在为ORDER BY做排序。 Opening tables 　这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。 Removing duplicates 　正在执行一个SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。 Reopen table 　获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。 Repair by sorting 　修复指令正在排序以创建索引。 Repair with keycache 　修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。 Searching rows for update 　正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。 Sleeping 　正在等待客户端发送新请求.（Sleeping过多也是问题，比如wait_timeout设置过大，导致MySQL里大量的SLEEP进程无法及时释放，拖累系统性能，不过也不能把它设置的过小，否则你可能会遭遇到“MySQL has gone away”之类的问题）。 System lock 　正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。 Upgrading lock 　INSERT DELAYED正在尝试取得一个锁表以插入新记录。 Updating 　正在搜索匹配的记录，并且修改它们。 User Lock 　正在等待GET_LOCK()。 Waiting for tables 　该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个 表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。 waiting for handler insert 　INSERT DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。

        以上大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。
　    还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着。

（2）其次，通过show status查询当前Mysql的运行状态

了解以下状态值及含义，如果在日常运维过程有做这方面的记录，那么当系统出现性能异常时，能做个状态值的比较对，偏离过大的就是需要关注的点（其实可以把这些参数值加入到运维监控系统，作为关注指标），如下：

 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 服务器工作了多少秒。 Uptime_since_flush_status 最近一次使用FLUSH STATUS 的时间（以秒为单位）

（3）最后，可以尝试kill id（id在SHOW PROCESSLIST中显示 ），关掉疑似占CPU高的线程，以确认是否能让CPU降下来。

对于mysql来说，慢SQL及死锁以外的CPU问题确实不好定位，要求对数据库系统及性能非常了解，而对于我们做性能测试的，能做的就是逐层分析，缩小问题范围，实在不行，只能用kill id的方式来试错排查。

三、高IO定位分析

       其实高IO也可能导致CPU高，因为磁盘I/O比较慢，会导致CPU一直等待磁盘I/O请求。分析数据库IO属于基本技能（毕竟大部分数据库调优到了极致，最后的瓶颈也可能会是IO，而且IO调优的难度会高一些）。

（1）首先用万能的top命令查看进程

 [root@localhost ~]# top top - 11:53:04 up 702 days, 56 min,  1 user,  load average: 7.18, 6.70, 6.47 Tasks: 576 total,   1 running, 575 sleeping,   0 stopped,   0 zombie Cpu(s):  7.7%us,  3.4%sy,  0.0%ni, 77.6%id, 11.0%wa,  0.0%hi,  0.3%si,  0.0%st Mem:  49374024k total, 32018844k used, 17355180k free,   115416k buffers Swap: 16777208k total,   117612k used, 16659596k free,  5689020k cached     PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND 14165 mysql     20   0 8822m 3.1g 4672 S 162.3  6.6  89839:59 mysqld 40610 mysql     20   0 25.6g  14g 8336 S 121.7 31.5 282809:08 mysqld 49023 mysql     20   0 16.9g 5.1g 4772 S  4.6 10.8   34940:09 mysqld

很明显是前面两个mysqld进程导致整体负载较高。而且，从 Cpu(s) 这行的统计结果也能看的出来，%us 和 %wa 的值较高，表示当前比较大的瓶颈可能是在用户进程消耗的CPU以及磁盘I/O等待上。
（2）我们先分析下磁盘I/O的情况

执行 sar -d 1或（iostat -d -x -k 1）命令（每秒刷新） 确认磁盘I/O是否真的较大：

 [root@localhost ~]# sar -d 1 Linux 2.6.32-431.el6.x86_64 (localhost.localdomain)     06/05/2020  _x86_64_        (8 CPU) 11:54:31 AM     DEV      tps    rd_sec/s    wr_sec/s    avgrq-sz   avgqu-sz  await     svctm   %util 11:54:32 AM    dev8-0   5338.00 162784.00   1394.00     30.76      5.24      0.98      0.19    100.00 11:54:33 AM    dev8-0   5134.00 148032.00  32365.00     35.14      6.93      1.34      0.19    100.10 11:54:34 AM    dev8-0   5233.00 161376.00    996.00     31.03      9.77      1.88      0.19    100.00 11:54:35 AM    dev8-0   4566.00 139232.00   1166.00     30.75      5.37      1.18      0.22    100.00 11:54:36 AM    dev8-0   4665.00 145920.00    630.00     31.41      5.94      1.27      0.21    100.00 11:54:37 AM    dev8-0   4994.00 156544.00    546.00     31.46      7.07      1.42      0.20    100.00

%util 达到或接近100%，说明产生的I/O请求太多，I/O系统已经满负荷。 

（3）再利用 iotop 确认到底哪些进程消耗的磁盘I/O资源最多：

 [root@localhost ~]# iotop Total DISK READ: 59.52 M/s | Total DISK WRITE: 598.63 K/s TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND 16397 be/4 mysql       7.98 M/s    0.00 B/s  0.00 % 95.67 % mysqld --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data --port=3306  7295 be/4 mysql      9.95 M/s    0.00 B/s  0.00 % 93.72 % mysqld --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data --port=3306 14295 be/4 mysql      9.86 M/s    0.00 B/s  0.00 % 94.53 % mysqld --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data --port=3306 14288 be/4 mysql      13.38 M/s    0.00 B/s  0.00 % 92.21 % mysqld --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data --port=3306 14292 be/4 mysql      13.54 M/s    0.00 B/s  0.00 % 91.96 % mysqld --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data --port=3306

可以看到，端口号是3306的实例消耗的磁盘I/O资源比较多，那就看看这个实例里都有什么查询在跑。

（4）可以用上面提到的SHOW PROCESSLIST方法，也可以用mysqladmin命令工具

我们需要看到当前都有哪些SQL在运行：

（以下用mysqladmin的方式，该命令mysql自带，可创建软链接方便调用，ln -s /usr/local/mysql/bin/mysqladmin /usr/bin）：

 [root@localhost ~]# mysqladmin -uroot -p123456 pr|grep -v Sleep +----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+ | Id |User| Host     | db |Command|Time | State        | Info                                                                                          | +----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+ | 25 |root| 172.16.1.133:45921 | db | Query | 68  | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>404612 order by Fvideoid) t1 | | 26 |root| 172.16.1.133:45923 | db | Query | 65  | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>484915 order by Fvideoid) t1 | | 28 |root| 172.16.1.133:45928 | db | Query | 130 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>404641 order by Fvideoid) t1 | | 27 |root| 172.16.1.133:45930 | db | Query | 167 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>324157 order by Fvideoid) t1 | | 36 |root| 172.16.1.133:45937 | db | Query | 174 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>324346 order by Fvideoid) t1 | +----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+

可以看到有不少慢查询还未完成，从slow query log中也能发现，这类SQL发生的频率很高。
这是一个非常低效的SQL写法，导致需要对整个主键进行扫描，但实际上只需要取得一个最大值而已，从slow query log中可看到：

 Rows_sent: 1  Rows_examined: 5413058

每次都要扫描500多万行数据，却只为读取一个最大值，效率非常低。

经过分析，这个SQL稍做简单改造即可在个位数毫秒级内完成，提升了N次方。
改造的方法是：对查询结果做一次倒序排序，取得第一条记录即可。而原先的做法是对结果正序排序，取最后一条记录。

总结：mysql的IO分析思路挺简单，首先通过top关注%wa（指CPU等待磁盘写入完成的时间，平时为0，越高表示磁盘越忙）的波动是否较大；其次分析下磁盘I/O情况，并找到哪些进程占用IO资源最多；最后还是用SHOW PROCESSLIST或mysqladmin查看哪些语句的频繁调用在占用IO。

四、高内存定位分析

       要在linux下分析内存占用高低，对于新手来说不太容易，因为涉及MemFree、虚拟内存（Swap）和Buffers、Cached的概念要搞明白，直观性不如windows，另外mysql本身默认没有开启内存 / 缓存监控（只对performance_schema进行了内存开销的统计），一般的mysql监控软件在这方面也很难直观的暴露问题。

（1）首先分析内存也可以用万能的TOP命令，看看是否mysql进程占用内存高 ：

       以上这张图，比较容易看出是内存占用高，因为free、buffers、cached都不高，那么大部分被used掉的内存就属于被进程占用，而且没有Swap（被禁用了，一般正常情况下，swap交换分区的used值如果在不断的变化，就说明内核在不断进行内存和swap的数据交换，很可能是内存不够用了），那么现在mysql占用63.3%就可以判断确实内存高了。

（2）排查是否大量SQL运行占用内存高

查看mysql里的线程，观察是否有长期运行或阻塞的sql，也是用到万能的show full processlist;，如果没有发现相关线程（具体参考上面提到的State列不同状态含义）异常现象，就可以排除该原因。

（3）查看mysql内存/缓存的相关配置，以便排查mysql连接使用完后是否没有真正释放内存

Mysql的内存消耗一般分为两种：global级共享内存、session级私有内存。

执行如下命令，即可查询global级共享内存分配情况：

 show variables where variable_name in ( 'innodb_buffer_pool_size','innodb_log_buffer_size','innodb_additional_mem_pool_size','query_cache_size' );

session级私有内存，主要是数据库连接私有内存使用，查询命令如下： 

 show variables where variable_name in ( 'tmp_table_size','sort_buffer_size','read_buffer_size','read_rnd_buffer_size','join_buffer_size','thread_stack', 'binlog_cache_size' );

按理用mysql查询命令，就能查到当前各项内存或缓存的使用情况，但是mysql默认是没有开启内存监控的，通过以下语句就能查出大部分监控项都是未开启的：

 SELECT * FROM performance_schema.setup_instruments        WHERE  NAME LIKE '%memory%' and NAME not LIKE '%memory/performance_schema%';

我们可以用update语句批量开启（属于临时性开启，重启mysql后又还原为关闭）：

 mysql> update performance_schema.setup_instruments set enabled = 'yes'        WHERE  NAME LIKE '%memory%' and NAME not LIKE '%memory/performance_schema%'; > Affected rows: 310 > 时间: 0.002s

然后通过以下语句就可以查出mysql所有内存的使用：

 SELECT SUBSTRING_INDEX(event_name,'/',2) AS        code_area, sys.format_bytes(SUM(current_alloc))        AS current_alloc        FROM sys.x$memory_global_by_current_bytes        GROUP BY SUBSTRING_INDEX(event_name,'/',2)        ORDER BY SUM(current_alloc) DESC;

查到占用内存最高的是memory/innodb，如下：

 可以进一步细化查询memory/innodb：

 SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name LIKE 'memory/innodb%';

对于内存使用预估，在网上有人推荐了一款内存计算器，统计网址：https://www.mysqlcalculator.com/ 

（说明：上图左列为mysql默认配置，右列为当前数据库的配置  [通过show variables可以查到]，可以预估出内存使用最大值，如上图，轻微调大一些配置，就能达到7119MB的内存量；如果预计到的结果不符合要求，就说明当前配置不合理，需要进行调整）。 

mysql的数据库内存/缓存优化真没什么经验，以下是网上提供的一个优化过程配置项（实际要设置多大，得看自己机器的内存有多大，结合内存计算器算一算，看看有没有超标）：

 key_buffer_size = 32M //key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。由于我的数据库引擎为innodb，大部分表均为innodb，此处取默认值一半32M。 query_cache_size = 64M //查询缓存大小，当打开时候，执行查询语句会进行缓存，读写都会带来额外的内存消耗，下次再次查询若命中该缓存会立刻返回结果。默认改选项为关闭，打开则需要调整参数项query_cache_type=ON。此处采用默认值64M。 tmp_table_size = 64M //范围设置为64-256M最佳，当需要做类似group by操作生成的临时表大小，提高联接查询速度的效果，调整该值直到created_tmp_disk_tables / created_tmp_tables * 100% <= 25%，处于这样一个状态之下，效果较好，如果网站大部分为静态内容，可设置为64M，如果为动态页面，则设置为100M以上，不宜过大，导致内存不足I/O堵塞。此处我们设置为64M。 innodb_buffer_pool_size = 8196M //这个参数主要作用是缓存innodb表的索引，数据，插入数据时的缓冲。专用mysql服务器设置的大小： 操作系统内存的70%-80%最佳。由于我们的服务器还部署有其他应用，估此处设置为8G。此外，这个参数是非动态的，要修改这个值，需要重启mysqld服务。设置的过大，会导致system的swap空间被占用，导致操作系统变慢，从而减低sql查询的效率。 innodb_additional_mem_pool_size = 16M //用来存放Innodb的内部目录，这个值不用分配太大，系统可以自动调。不用设置太高。通常比较大数据设置16Ｍ够用了，如果表比较多，可以适当的增大。如果这个值自动增加，会在error log有中显示的。此处我们设置为16M。 innodb_log_buffer_size = 8M //InnoDB的写操作，将数据写入到内存中的日志缓存中，由于InnoDB在事务提交前，并不将改变的日志写入到磁盘中，因此在大事务中，可以减轻磁盘I/O的压力。通常情况下，如果不是写入大量的超大二进制数据（a lot of huge blobs），4MB-8MB已经足够了。此处我们设置为8M。 max_connections = 800 //最大连接数，根据同时在线人数设置一个比较综合的数字，最大不超过16384。此处我们根据系统使用量综合评估，设置为800。 sort_buffer_size = 2M //是一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存。并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。官方文档推荐范围为256KB~2MB，这里我们设置为2M。 read_buffer_size = 2M //（数据文件存储顺序）是MySQL读入缓冲区的大小，将对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区，read_buffer_size变量控制这一缓冲区的大小，如果对表的顺序扫描非常频繁，并你认为频繁扫描进行的太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能，read_buffer_size变量控制这一提高表的顺序扫描的效率 数据文件顺序。此处我们设置得比默认值大一点，为2M。 read_rnd_buffer_size = 250K //是MySQL的随机读缓冲区大小，当按任意顺序读取行时（列如按照排序顺序）将分配一个随机读取缓冲区，进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要大量数据可适当的调整该值，但MySQL会为每个客户连接分配该缓冲区所以尽量适当设置该值，以免内存开销过大。表的随机的顺序缓冲 提高读取的效率。此处设置为跟默认值相似，250KB。 join_buffer_size = 250K //多表参与join操作时的分配缓存，适当分配，降低内存消耗，此处我们设置为250KB。 thread_stack = 256K //每个连接线程被创建时，MySQL给它分配的内存大小。当MySQL创建一个新的连接线程时，需要给它分配一定大小的内存堆栈空间，以便存放客户端的请求的Query及自身的各种状态和处理信息。Thread Cache 命中率：Thread_Cache_Hit = (Connections - Threads_created) / Connections * 100%;命中率处于90%才算正常配置，当出现“mysql-debug: Thread stack overrun”的错误提示的时候需要增加该值。此处我们配置为256K。 binlog_cache_size = 250K // 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存。作用是提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是1048576 –1M；后者建议是： 2097152 – 4194304 即 2–4M。此处我们根据系统实际，配置为250KB。 table_definition_cache = 400 // 开发模式：从1400设置为400，内存从150M降到90M；服务模式：从1400设置为400，内存从324M降到227M

总结：默认情况下按默认配置，很少能出现内存不足问题（毕竟现在的数据库产品，管理内存还是挺成熟的），因为按照默认的配置，占内存总计576.2MB，只是在使用过程中，很多人配置了不合理的参数（为了追求高性能，没有平衡好配置和硬件的关系）或是运行实例异常，导致内存爆了。