MySQL 学习笔记(八)--部分优化原则 和 关于事务、锁、等待的系统表

1.Optimizing at the Database Level

The most important factor in making a database application fast is its basic design:

• Are the tables structured properly? In particular, do the columns have the right data types, and does each table have the appropriate columns for the type of work?

• Are the right indexes in place to make queries efficient?

• Are you using the appropriate storage engine for each table, and taking advantage of the strengths and features of each storage engine you use?

• Does each table use an appropriate row format?

• Does the application use an appropriate locking strategy?

• Are all memory areas used for caching sized correctly?

2.Optimizing SELECT Statements

he main considerations for optimizing queries are:

• The first thing to check is whether you can add an index. Set up indexes on columns used in the WHERE clause, to speed up evaluation, filtering, and the final retrieval of results. To avoid wasted disk space, construct a small set of indexes that speed up many related queries used in your application.

• Isolate and tune any part of the query, such as a function call, that takes excessive time. Depending on how the query is structured, a function could be called once for every row in the result set, or even once for every row in the table, greatly magnifying any inefficiency.

• Minimize the number of full table scans in your queries, particularly for big tables.

• Keep table statistics up to date by using the ANALYZE TABLE statement periodically, so the optimizer has the information needed to construct an efficient execution plan.

• Learn the tuning techniques, indexing techniques, and configuration parameters that are specific to the storage engine for each table. Both InnoDB and MyISAM have sets of guidelines for enabling and sustaining high performance in queries.

• Avoid transforming the query in ways that make it hard to understand, especially if the optimizer does some of the same transformations automatically.

• If a performance issue is not easily solved by one of the basic guidelines, investigate the internal details of the specific query by reading the EXPLAIN plan and adjusting your indexes, WHERE clauses, join clauses, and so on. 

• Adjust the size and properties of the memory areas that MySQL uses for caching. 

• making your application more scalable. Scalability means that your application can handle more simultaneous users, larger requests, and so on without experiencing a big drop in performance.

• Deal with locking issues, where the speed of your query might be affected by other sessions accessing the tables at the same time.

3.WHERE Clause Optimization

• Leave the query in a more understandable and maintainable form.

• Constant expressions used by indexes are evaluated only once.

• COUNT(*) on a single table without a WHERE is retrieved directly from the table information for MyISAM and MEMORY tables. This is also done for any NOT NULL expression when used with only one table.

• Early detection of invalid constant expressions.

• For each table in a join, a simpler WHERE is constructed to get a fast WHERE evaluation for the table and also to skip rows as soon as possible.

• All constant tables are read first before any other tables in the query.

• The best join combination for joining the tables is found by trying all possibilities. If all columns in ORDER BY and GROUP BY clauses come from the same table, that table is preferred first when joining.

• If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

• If you use the SQL_SMALL_RESULT modifier, MySQL uses an in-memory temporary table.

• Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan.

• In some cases, MySQL can read rows from the index without even consulting the data file. If all columns used from the index are numeric, only the index tree is used to resolve the query.

• Before each row is output, those that do not match the HAVING clause are skipped.

4.正常时间格式与unix时间戳的互相转换

(1) 通过 函数 unix_timestamp  将正常时间格式转换成unix时间戳。

(2)通过函数 from_unixtime 将unix时间戳转换成正常时间格式。

5.系统表innodb_trx的解析

The INNODB_TRX table provides information about every transaction currently executing inside InnoDB, including whether the transaction is waiting for a lock, when the transaction started, and the SQL statement the transaction is executing, if any.

INNODB_TRX 表提供了信息关于在InnoDB中执行的当前的每个事务 包含是否事务是等待一个锁,当事务开始后事务正在执行的SQL语句,甚至是语句执行的进度。

字段 意义 Remark
TRX_ID A unique transaction ID number, internal to InnoDB. These IDs are not created for transactions that are read only and nonlocking. 事务ID。只读事务和非锁事务是不会创建id的。
TRX_STATE The transaction execution state. Permitted values are RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING. 事务状态,有以下几种状态:RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。
TRX_STARTED The transaction start time. 事务开始时间。
TRX_REQUESTED_LOCK_ID The ID of the lock the transaction is currently waiting for, if TRX_STATE is LOCK WAIT; otherwise NULL. To obtain details about the lock, join this column with the LOCK_ID column of the INNODB_LOCKS table. 事务当前正在等待锁的标识,可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息。
TRX_WAIT_STARTED The time when the transaction started waiting on the lock, if TRX_STATE is LOCK WAIT; otherwise NULL. 事务开始等待的时间。
TRX_MYSQL_THREAD_ID The MySQL thread ID. To obtain details about the thread, join this column with the ID column of the INFORMATION_SCHEMA PROCESSLIST table. 事务线程 ID,可以和 PROCESSLIST 表 JOIN。
TRX_WEIGHT The weight of a transaction, reflecting (but not necessarily the exact count of) the number of rows altered and the number of rows locked by the transaction. To resolve a deadlock, InnoDB selects the transaction with the smallest weight as the “victim” to roll back. Transactions that have changed nontransactional tables are considered heavier than others, regardless of the number of altered and locked rows. 事务的权重。代表修改的行数和被事务锁住的行数。为了解决死锁,innodb会选择一个高度最小的事务来当做牺牲品进行回滚。已经被更改的非交易型表的事务权重比其他事务高,即使改变的行和锁住的行比其他事务低。
TRX_QUERY The SQL statement that is being executed by the transaction. 事务正在执行的 SQL 语句。
TRX_OPERATION_STATE The transaction's current operation, if any; otherwise NULL. 事务当前操作状态。
TRX_TABLES_IN_USE The number of InnoDB tables used while processing the current SQL statement of this transaction. 当前事务执行的 SQL 中使用的表的个数。
TRX_TABLES_LOCKED The number of InnoDB tables that the current SQL statement has row locks on. (Because these are row locks, not table locks, the tables can usually still be read from and written to by multiple transactions, despite some rows being locked.) 当前执行 SQL 的行锁数量。因为只是行锁,不是表锁,表仍然可以被多个事务读和写
TRX_LOCK_STRUCTS The number of locks reserved by the transaction. 事务保留的锁数量。
TRX_LOCK_MEMORY_BYTES The total size taken up by the lock structures of this transaction in memory. 事务锁住的内存大小,单位为 BYTES。
TRX_ROWS_LOCKED The approximate number or rows locked by this transaction. The value might include delete-marked rows that are physically present but not visible to the transaction. 事务锁住的记录数。包含标记为 DELETED,并且已经保存到磁盘但对事务不可见的行。
TRX_ROWS_MODIFIED The number of modified and inserted rows in this transaction. 事务更改的行数。
TRX_CONCURRENCY_TICKETS A value indicating how much work the current transaction can do before being swapped out, as specified by the innodb_concurrency_tickets system variable. 该值代表当前事务在被清掉之前可以多少工作,由 innodb_concurrency_tickets系统变量值指定。
TRX_ISOLATION_LEVEL The isolation level of the current transaction. 当前事务的隔离级别。
TRX_UNIQUE_CHECKS Whether unique checks are turned on or off for the current transaction. For example, they might be turned off during a bulk data load. 是否打开唯一性检查的标识。
TRX_FOREIGN_KEY_CHECKS Whether foreign key checks are turned on or off for the current transaction. For example, they might be turned off during a bulk data load. 是否打开外键检查的标识。
TRX_LAST_FOREIGN_KEY_ERROR The detailed error message for the last foreign key error, if any; otherwise NULL. 最后一次的外键错误信息。
TRX_ADAPTIVE_HASH_LATCHED Whether the adaptive hash index is locked by the current transaction. When the adaptive hash index search system is partitioned, a single transaction does not lock the entire adaptive hash index. Adaptive hash index partitioning is controlled by innodb_adaptive_hash_index_parts, which is set to 8 by default. 自适应哈希索引是否被当前事务阻塞。当自适应哈希索引查找系统分区,一个单独的事务不会阻塞全部的自适应hash索引。自适应hash索引分区通过 innodb_adaptive_hash_index_parts参数控制,默认值为8。
TRX_ADAPTIVE_HASH_TIMEOUT Whether to relinquish the search latch immediately for the adaptive hash index, or reserve it across calls from MySQL. When there is no adaptive hash index contention, this value remains zero and statements reserve the latch until they finish. During times of contention, it counts down to zero, and statements release the latch immediately after each row lookup. When the adaptive hash index search system is partitioned (controlled by innodb_adaptive_hash_index_parts), the value remains 0. 是否为了自适应hash索引立即放弃查询锁,或者通过调用mysql函数保留它。当没有自适应hash索引冲突,该值为0并且语句保持锁直到结束。在冲突过程中,该值被计数为0,每句查询完之后立即释放门闩。当自适应hash索引查询系统被分区(由 innodb_adaptive_hash_index_parts参数控制),值保持为0。
TRX_IS_READ_ONLY A value of 1 indicates the transaction is read only.  
TRX_AUTOCOMMIT_NON_LOCKING A value of 1 indicates the transaction is a SELECT statement that does not use the FOR UPDATE or LOCK IN SHARED MODE clauses, and is executing with autocommit enabled so that the transaction will contain only this one statement. When this column and TRX_IS_READ_ONLY are both 1, InnoDB optimizes the transaction to reduce the overhead associated with transactions that change table data.  

Use this table to help diagnose performance problems that occur during times of heavy concurrent load.

Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.

6.INNODB_LOCKS

The INNODB_LOCKS table provides information about each lock that an InnoDB transaction has requested but not yet acquired, and each lock that a transaction holds that is blocking another transaction.

字段 意义 Remark
LOCK_ID

A unique lock ID number, internal to InnoDB. Treat it as an opaque string.

Although LOCK_ID currently contains TRX_ID, the format of the data in LOCK_ID is subject to change at any time. Do not write applications that parse the LOCK_ID value.

锁ID。
LOCK_TRX_ID The ID of the transaction holding the lock. To obtain details about the transaction, join this column with the TRX_ID column of the INNODB_TRX table. 拥有锁的事务ID。可以和INNODB_TRX 表Join得到事务的详细信息。
LOCK_MODE How the lock is requested. Permitted lock mode descriptors are S, X, IS, IX, GAP, AUTO_INC, and UNKNOWN. 锁的模式。有如下锁类型:S、X、IS、IX,分别代表:共享锁、排他锁、意向共享锁、意向排他锁。
LOCK_TYPE The type of lock. Permitted values are RECORD for a row-level lock, TABLE for a table-level lock. 锁的类型。RECORD代表行级别锁,Table代表表级别锁。
LOCK_TABLE The name of the table that has been locked or contains locked records. 被锁定的或者包含锁定记录的表的名称。
LOCK_INDEX The name of the index, if LOCK_TYPE is RECORD; otherwise NULL. 当LOCK_TYPE is RECORD时,表示索引的名称,否则为NULL
LOCK_SPACE The tablespace ID of the locked record, if LOCK_TYPE is RECORD; otherwise NULL. 当LOCK_TYPE is RECORD时,表示锁定行的表空间ID,否则为NULL。
LOCK_PAGE The page number of the locked record, if LOCK_TYPE is RECORD; otherwise NULL. 当LOCK_TYPE is RECORD时,表示锁定行的页号,否则为NULL。
LOCK_REC The heap number of the locked record within the page, if LOCK_TYPE is RECORD; otherwise NULL. 当LOCK_TYPE is RECORD,表示一堆页面中锁定页面中锁定行的数量,亦即被锁定的记录号;否则为NULL。
LOCK_DATA

The data associated with the lock, if any. A value is shown if the LOCK_TYPE is RECORD, otherwise the value is NULL.

Primary key values of the locked record are shown for a lock placed on the primary key index.

Secondary index values of the locked record are shown for a lock placed on a unique secondary index. Secondary index values are shown with primary key values appended if the secondary index is not unique.

If there is no primary key, LOCK_DATA shows either the key values of a selected unique index

or the unique InnoDB internal row ID number, according to the rules governing InnoDB clustered index use.

LOCK_DATA reports “supremum pseudo-record” for a lock taken on a supremum pseudo-record. If the page containing the locked record is not in the buffer pool because it was written to disk while the lock was held,

InnoDB does not fetch the page from disk. Instead, LOCK_DATA reports NULL.

当LOCK_TYPE is RECORD时,表示锁定行的主键,否则为NULL。

Use this table to help diagnose performance problems that occur during times of heavy concurrent load.

Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.

This table is deprecated as of MySQL 5.7.14 and is removed in MySQL 8.0.

7.INNODB_LOCK_WAITS

The INNODB_LOCK_WAITS table contains one or more rows for each blocked InnoDB transaction, indicating the lock it has requested and any locks that are blocking that request.

 

字段 意义 Remark
REQUESTING_TRX_ID The ID of the requesting (blocked) transaction. 请求事务的ID。
REQUESTED_LOCK_ID The ID of the lock for which a transaction is waiting. To obtain details about the lock, join this column with the LOCK_ID column of the INNODB_LOCKS table. 事务所等待的锁定的ID。可以和INNODB_LOCKS表Join
BLOCKING_TRX_ID The ID of the blocking transaction. 阻塞事务的ID。
BLOCKING_LOCK_ID The ID of a lock held by a transaction blocking another transaction from proceeding. To obtain details about the lock, join this column with the LOCK_ID column of the INNODB_LOCKS table. 某一个事务的锁的ID,该事务阻塞了另一事务的运行。可以和INNODB_LOCKS表Join。

Use this table to help diagnose performance problems that occur during times of heavy concurrent load.

Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.

This table is deprecated as of MySQL 5.7.14 and is removed in MySQL 8.0.

8.以上三表概况

• INNODB_TRX: Provides information about every transaction currently executing inside InnoDB, including the transaction state (for example, whether it is running or waiting for a lock), when the transaction started, and the particular SQL statement the transaction is executing.

• INNODB_LOCKS: Each transaction in InnoDB that is waiting for another transaction to release a lock (INNODB_TRX.TRX_STATE is LOCK WAIT) is blocked by exactly one blocking lock request. That blocking lock request is for a row or table lock held by another transaction in an incompatible mode. A lock that blocks a transaction is always held in a mode incompatible with the mode of requested lock (read vs. write, shared vs. exclusive). The blocked transaction cannot proceed until the other transaction commits or rolls back, thereby releasing the requested lock. For every blocked transaction, INNODB_LOCKS contains one row that describes each lock the transaction has requested, and for which it is waiting. INNODB_LOCKS also contains one row for each lock that is blocking another transaction, whatever the state of the transaction that holds the lock (INNODB_TRX.TRX_STATE is RUNNING, LOCK WAIT, ROLLING BACK or COMMITTING).

• INNODB_LOCK_WAITS: This table indicates which transactions are waiting for a given lock, or for which lock a given transaction is waiting. This table contains one or more rows for each blocked transaction, indicating the lock it has requested and any locks that are blocking that request. The REQUESTED_LOCK_ID value refers to the lock requested by a transaction, and the BLOCKING_LOCK_ID value refers to the lock (held by another transaction) that prevents the first transaction from proceeding. For any given blocked transaction, all rows in INNODB_LOCK_WAITS have the same value for REQUESTED_LOCK_ID and different values for BLOCKING_LOCK_ID.

In this scenario, use the following query to see which transactions are waiting and which transactions are blocking them:

复制代码
SELECT
 r.trx_id waiting_trx_id,
 r.trx_mysql_thread_id waiting_thread,
 r.trx_query waiting_query,
 b.trx_id blocking_trx_id,
 b.trx_mysql_thread_id blocking_thread,
 b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
 ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
 ON r.trx_id = w.requesting_trx_id;
复制代码

Or, more simply, use the sys schema innodb_lock_waits view:

SELECT
 waiting_trx_id,
 waiting_pid,
 waiting_query,
 blocking_trx_id,
 blocking_pid,
 blocking_query
FROM sys.innodb_lock_waits;

 

学习文档

(1)Mysql 官方文档

《MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6》

(2) mysql之unix时间戳和正常时间格式之间的转换

https://zhuanlan.zhihu.com/p/140091293

(3) Mysql:Mysql数据库系统表之详细了解INNODB_TRX、INNODB_LOCKs、INNODB_LOCK_waits、PROCESSLIST表

https://blog.csdn.net/yu121380/article/details/110132741

 

posted @   东山絮柳仔  阅读(131)  评论(0编辑  收藏  举报
编辑推荐:
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
历史上的今天:
2019-04-24 SQL Server 数据库部分常用语句小结(三)
点击右上角即可分享
微信分享提示