MySQL查询性能优化：导致查询操作阻塞或变慢的可能原因及分析⽅法
⼀般情况下，对于“查询”的性能优化是针对于⼀些复杂的语句，并且需要返回⼤量的数据的场景。但有些情况下，“查⼀⾏”也会执⾏的特别慢，本⽂介绍可能出引发这种现象的⼏类，并总结出解决⽅法。
1. 第⼀类：查询长时间不返回：
1.1 第1种情况：等MDL锁：
mysql> select * from t where id = 1;
如果查询结果长时间不返回，⼀般碰到这种情况，⼤概率是表t被锁住了。
接下来分析原因的时候，⼀般都是⾸先执⾏以下 show processlist 命令，看看当前语句处于什么状态。然后再针对每种状态，去分析它们产⽣的原因、如何复现，以及如何处理。
⼿动构造MDL锁的⽅法：
mysql>lock table runoob_test write;
//对⼀个表直接上锁。
mysql>show processlist;全屏轮播海报
+----+-----------------+-----------------+----------+---------+---------+---------------------------------+-----------------------------------------------+
| Id |User| Host | db | Command |Time| State | Info |
+----+-----------------+-----------------+----------+---------+---------+---------------------------------+-----------------------------------------------+
|5| event_scheduler | localhost |NULL| Daemon |4256410| Waiting on empty queue |NULL|
|55| root | localhost | runoob | Query |6| Waiting for table metadata lock|select*from runoob_test where runoob_id =1|
|56| root | localhost | runoob | Query |0| init |show processlist |
+----+-----------------+-----------------+----------+---------+---------+---------------------------------+-----------------------------------------------+
5rows in set(0.01 sec)
可以查看到这条语句的状态是 “Waiting for table metadata lock”
mysql>select blocking_pid from sys.schema_table_lock_waits;
+--------------+
| blocking_pid |
+--------------+
|59|
+--------------+
1row in set(0.05 sec)
再通过查询 sys.schema_table_lock_waits 表到导致占⽤锁的线程ID，将其处理掉即可解决阻塞问题。
注：
表级锁是Server层级别的锁，⾏级锁是InnoDB存储引擎级别的锁。
1.2 第2种情况：等flush：
⼿动触发MySQL执⾏flush操作的命令：
mysql> flush tables runoob_test with read lock;
正常情况下flush操作会执⾏的很快，除⾮它们也被别的线程堵住了。
所以出现“Waiting for table flush”状态的可能情况是：有⼀个flush tables命令被别的语句阻塞了，然后它⼜阻塞了当前的select语句。
遇到这种情况时，同样可以使⽤ show processlist 进⾏解决：
1.3 第3种情况：等⾏级锁：
这类情况⽐较常见，例如两个事务A、B同时运⾏，当事务B中占有了某⼀⾏的写锁，此时如果事务A试图去访问这⼀⾏，则事务A会被阻塞，事务A中的查询语句不返回。
如果使⽤的是MySQL 5.7版本，可以通过 sys.innodb_lock_waits 表查到（MySQL 8.0中似乎⽆法查到）
。
2. 第⼆类：查询慢：
MySQL提供了“慢查询”⽇志的功能，可以将超过⽤户指定的查询时间阈值的查询命令存⼊到 slow_query ⽇志中，供⽤户分析优化查询语句使⽤。
具体的操作⽅法是：
（1）⽅法⼀： 在配置⽂件中修改：
[mysqld]
slow_query_log =1 //=1表⽰开启慢查询⽇志
slow_query_log_file = /var/log/mysql/slow-query.log //设置⽇志的保存路径
long_query_time =5 //设置判定为慢查询的时间阈值，单位为秒
（Ubuntu中mysql配置⽂件所在路径：/etc/f.d/mysqldf）
（2）⽅法⼆： 通过client客户端命令操作的⽅式：
mysql>set global slow_query_log =1;springfestive怎么读英语发音
mysql>set global slow_qeury_log_file ="/var/log/mysql/slow-query.log";
mysql>set global long_query_time =5;
使⽤ set long_query_time = 0，可以将“慢查询”
查看 mysql-slow.log 慢查询⽇志中的内容：
select * from runoob_test where runoob_id =1;
# Time: 2021-08-08T06:12:43.052494Z
# User@Host: root[root] @ localhost [] Id: 60
# Query_time: 0.005899 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
其中，Query_time 表⽰查询语句执⾏的时间（单位秒）。
PS：在MySQL客户端查看服务器中某个配置参数的值：
mysql>show variables like'slow_query_log';
+----------------+-------+
| Variable_name |Value|
+----------------+-------+
| slow_query_log |ON|
+----------------+-------+
1row in set(0.01 sec)
mysql>show variables like'slow_query_log_file';
mysql面试题导图+---------------------+-------------------------------+
| Variable_name |Value|
+---------------------+-------------------------------+
| slow_query_log_file |/var/log/mysql/mysql-slow.log |
+---------------------+-------------------------------+
1row in set(0.01 sec)
mysql>show variables like'long_query_time';
+-----------------+----------+
| Variable_name |Value|
+-----------------+----------+
| long_query_time |0.000000|
+-----------------+----------+
1row in set(0.00 sec)
3. ⼩结：
本⽂介绍的内容是执⾏“查⼀⾏”这种操作时可能出现的被锁住和执⾏慢的例⼦。
其中，“被锁住”⼀般是由于被其他线程占⽤了锁（表锁、⾏锁）或系统的flush操作被阻塞导致的间接阻塞。定位⽅法是在问题复现时（查询操作处于阻塞状态时），使⽤ show processlist 命令，通过 state 字段查看被锁住具体原因（例如Waiting for metadata lock / table flush / statistics），通过 sys.schema_table_lock_waits 表 和 sys.innodb_lock_wait 表可分别查看到持有表锁 或 ⾏锁 的线程id，从⽽进⼀步决定对此线程的处理⽅式（例如直接kill掉）；
“执⾏慢”的原因有很多，例如在事务中所要查询的⾏数据的undo log过⼤（⾏数据被update更新了100万次，则undo log中将⽣成100万条⾏数据的版本），定位“执⾏慢”的⽅法是使⽤ “慢查询⽇志”，将阈值设置为合理的值，复现问题后通过 slow-query-log 锁定执⾏慢的语句并对其进⾏分析。
4. 番外：关于sys.schema_table_lock_waits和sys.innodb_lock_wait表：
MySQL中⾃带四个默认的数据库：
mysql>show databases;
+--------------------+
|Database|
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
（1）information_schema 库：
information_schema 数据库提供了访问数据库元数据的⽅式（所谓“元数据”是指关于数据的数据，如数据库名或表名、列的数据类型、访问权限等）。
换句话说，information_schema 是⼀个信息数据库，它保存着关于MySQL服务器所维护的所有其他数据库的信息。
mysql>show tables;
+---------------------------------------+
| Tables_in_information_schema |
| Tables_in_information_schema |
+---------------------------------------+
| ADMINISTRABLE_ROLE_AUTHORIZATIONS |
| APPLICABLE_ROLES |
| CHARACTER_SETS |
| CHECK_CONSTRAINTS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
|COLUMNS|//提供了表中的列信息，详细表述了某张表的所有列以及每个列的信息，`show columns from schemaname.tablename` 的结果取⾃于此表
| COLUMNS_EXTENSIONS |
| COLUMN_PRIVILEGES |
| COLUMN_STATISTICS |
| ENABLED_ROLES |
| ENGINES |
| EVENTS |
| FILES |
| INNODB_BUFFER_PAGE |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_BUFFER_POOL_STATS |
| INNODB_CACHED_INDEXES |
| INNODB_CMP |
| INNODB_CMPMEM |
| INNODB_CMPMEM_RESET |
| INNODB_CMP_PER_INDEX |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_CMP_RESET |
| INNODB_COLUMNS |
| INNODB_DATAFILES |
| INNODB_FIELDS |
| INNODB_FOREIGN |
| INNODB_FOREIGN_COLS |
| INNODB_FT_BEING_DELETED |
| INNODB_FT_CONFIG |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_DELETED |
| INNODB_FT_INDEX_CACHE |
| INNODB_FT_INDEX_TABLE |
| INNODB_INDEXES |
| INNODB_METRICS |
| INNODB_SESSION_TEMP_TABLESPACES |
| INNODB_TABLES |
| INNODB_TABLESPACES |
| INNODB_TABLESPACES_BRIEF |
| INNODB_TABLESTATS |
| INNODB_TEMP_TABLE_INFO |
| INNODB_TRX |
| INNODB_VIRTUAL |
| KEYWORDS |
| KEY_COLUMN_USAGE |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
html文件合并器| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| RESOURCE_GROUPS |
| ROLE_COLUMN_GRANTS |
| ROLE_ROUTINE_GRANTS |
| ROLE_TABLE_GRANTS |
| ROUTINES |
| SCHEMATA |
| SCHEMATA_EXTENSIONS |
| SCHEMA_PRIVILEGES |
|STATISTICS|//提供了关于表索引的信息，`show index from schemaname.tablename`的结果取⾃于此表
| ST_GEOMETRY_COLUMNS |
| ST_SPATIAL_REFERENCE_SYSTEMS |
| ST_UNITS_OF_MEASURE |
|TABLES|//提供了关于数据库中的表的信息（包括视图），详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息，`sh ow tables from schemaname`的结果取⾃与此表
| TABLESPACES |
| TABLESPACES_EXTENSIONS |
| TABLES_EXTENSIONS |
| TABLE_CONSTRAINTS |
| TABLE_CONSTRAINTS_EXTENSIONS |
二级c语言大题怎么给分
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_ATTRIBUTES |
| USER_PRIVILEGES |
| VIEWS |
| VIEW_ROUTINE_USAGE |
| VIEW_TABLE_USAGE |
+---------------------------------------+
79rows in set(0.00 sec)
使⽤举例：
（1）通过 information_schema.TABLES 表，查看runoob.runoob_test 表信息：
mysql>select*from TABLES where TABLE_NAME ='runoob_test'\G
***************************1.row***************************
TABLE_CATALOG: def
TABLE_SCHEMA: runoob
TABLE_NAME: runoob_test
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 2
AVG_ROW_LENGTH: 8192
DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 0
AUTO_INCREMENT: 3
CREATE_TIME: 2021-05-3017:00:02
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
1row in set(0.00 sec)
（2）通过 information_schema.COLUMNS 表查看runoob_test表中的列信息：
mysql>show columns from runoob.runoob_test;
+-----------------+--------------+------+-----+---------+----------------+
怎么打开oracle11g| Field |Type|Null|Key|Default| Extra |
+-----------------+--------------+------+-----+---------+----------------+
| runoob_id |int|NO| PRI |NULL|auto_increment|
| runoob_title |varchar(100)|NO||NULL||
| submission_date |date| YES ||NULL||
+-----------------+--------------+------+-----+---------+----------------+
3rows in set(0.01 sec)
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论