共计 3701 个字符，预计需要花费 10 分钟才能阅读完成。
这篇文章主要介绍“MySQL 中数据库优化的常见 sql 语句有哪些”，在日常操作中，相信很多人在 MySQL 中数据库优化的常见 sql 语句有哪些问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL 中数据库优化的常见 sql 语句有哪些”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！
1.SHOW ENGINES
查看执行引擎以及默认引擎。
2.SHOW PROCESSLIST
SHOW PROCESSLIST 查看当前数据库连接的使用情况，以及各种状态信息，非常有用。SHOW PROCESSLIST; 只列出前 100 条，如果想全列出请使用 SHOW FULL PROCESSLIST;
属性列以及含义：
id 一个标识，要 kill 一个语句的时候很有用。user 显示当前用户，如果不是 root，这个命令就只显示你权限范围内的 sql 语句。host 显示这个语句是从哪个 ip 的哪个端口上发出的。可用来追踪出问题语句的用户。db 显示这个进程目前连接的是哪个数据库。command 显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。
state 列以及含义，mysql 列出的状态：
Checking table 正在检查数据表（这是自动的）。Closing tables 正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。Connect Out 复制从服务器正在连接主服务器。Copying to tmp table on disk 由于临时结果集大于 tmp_table_size（默认 16M），正在将临时表从内存存储转为磁盘存储以此节省内存。Creating tmp table 正在创建临时表以存放部分查询结果。deleting from main table 服务器正在执行多表删除中的第一部分，刚删除第一个表。3.SHOW STATUS LIKE InnoDB_row_lock%
InnoDB 的行级锁定状态变量。
InnoDB 的行级锁定状态变量不仅记录了锁定等待次数，还记录了锁定总时长，每次平均时长，以及最大时长，此外还有一个非累积状态量显示了当前正在等待锁定的等待数量。对各个状态量的说明如下：
InnoDB_row_lock_current_waits：当前正在等待锁定的数量；
InnoDB_row_lock_time：从系统启动到现在锁定总时间长度；
InnoDB_row_lock_time_avg：每次等待所花平均时间；
InnoDB_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
InnoDB_row_lock_waits：系统启动后到现在总共等待的次数；
对于这 5 个状态变量，比较重要的主要是 InnoDB_row_lock_time_avg（等待平均时长），InnoDB_row_lock_waits（等待总次数）以及 InnoDB_row_lock_time（等待总时长）这三项。尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。
如果发现锁争用比较严重，如 InnoDB_row_lock_waits 和 InnoDB_row_lock_time_avg 的值比较高，还可以通过设置 InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。
4.SHOW ENGINE INNODB STATUS
SHOW ENGINE INNODB STATUS 命令会输出当前 InnoDB 监视器监视到的很多信息，它输出就是一个单独的字符串，没有行和列，内容分为很多小段，每一段对应 innodb 存储引擎不同部分的信息，其中有一些信息对于 innodb 开发者来说非常有用。
有一节 LATEST DETECTED DEADLOCK，就是记录的最后一次死锁信息，如下案例：
“(1) TRANSACTION”显示第一个事务的信息；
“(1) WAITING FOR THIS LOCK TO BE GRANTED”显示第一个事务等待的锁信息
“(2) TRANSACTION”显示第二个事务的信息；
“(2) HOLDS THE LOCK(S)”显示的是第二个事务持有的锁信息；
“(2) WAITING FOR THIS LOCK TO BE GRANTED”显示第二个事务等待的锁信息
最后一行表示处理结果，比如“WE ROLL BACK TRANSACTION (2)，表示回滚了第二个事务。
5.SHOW INDEXS
SHOW INDEXS 查询一个表中的索引信息：SHOW INDEXES FROM table_name;
建表的 sql 如下：
CREATE TABLE contacts(
contact_id INT AUTO_INCREMENT,
first_name VARCHAR(100) NOT NULL comment first name ,
last_name VARCHAR(100) NOT NULL,
email VARCHAR(100),
phone VARCHAR(20),
PRIMARY KEY(contact_id),
UNIQUE(email),
INDEX phone(phone) ,
INDEX names(first_name, last_name) comment By first name and/or last name
);存储过程插入五万条数据：
CREATE PROCEDURE zqtest ( ) BEGIN
DECLARE
i INT DEFAULT 0;
DECLARE
j VARCHAR ( 100 ) DEFAULT first_name
DECLARE
k VARCHAR ( 100 ) DEFAULT last_name
DECLARE
l VARCHAR ( 100 ) DEFAULT email
DECLARE
m VARCHAR ( 20 ) DEFAULT 11111111111
SET i = 0;
START TRANSACTION;
WHILE
i 50000 DO
MOD ( i, 100 ) = 0 THEN
SET j = CONCAT( first_name , i );
END IF;
MOD ( i, 200 ) = 0 THEN
SET k = CONCAT( last_name , i );
END IF;
MOD ( i, 50 ) = 0 THEN
SET m = CONCAT( , CAST( m as UNSIGNED) + i );
END IF;
INSERT INTO contacts ( first_name, last_name, email, phone )
VALUES
( j, k, CONCAT(l,i), m );
SET i = i + 1;
END WHILE;
COMMIT;
END;使用 show index from contacts; 后结果如下：
字段说明：
Table 表名 Non_unique 唯一索引为 0，其他索引为 1。主键索引也是唯一索引。Key_name 索引名。如果名字相同则表明是同一个索引，并且是联合索引，每一行都表示联合索引中的某一个列。Seq_in_index 索引中的列序列号，从 1 开始。也可以表明该列在联合索引中的顺序。Column_name 索引列名，如果是联合索引则是某一个列的名字 Collation 列以什么方式存储在索引中，大概意思就是字符序。Cardinality 一个索引上不同的值的个数，我们称之为“基数”（cardinality），也称为区分度，这个基数越大，索引的区分度越好。该值的统计不一定是准确的，可以使用 ANALYZE TABLE 修正。Sub_part 前缀索引。如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列的值都被编入索引，则为 NULL。Packed 关键字如何被压缩。如果没有被压缩，则为 NULL。压缩一般包括压缩传输协议、压缩列解决方案和压缩表解决方案。Null 如果列值可以包含 null，则为 YESIndex_type 索引结构类型，常见有 FULLTEXT，HASH，BTREE，RTREEComment、Index_comment 注释 6.ALTER TABLE xx ENGINE = INNODB
重建表，包括索引结构。可以消除索引页分裂以及删除数据时留下的磁盘碎片。
7.ANALYZE TABLE
不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁。可以用来修正 show index from tablename; 中统计索引的 Cardinality 是数据异常的情况。
到此，关于“MySQL 中数据库优化的常见 sql 语句有哪些”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！
