MySQL中数据库优化的常见sql语句总结

查看执行引擎以及默认引擎。

2.SHOW PROCESSLIST

SHOW PROCESSLIST查看当前数据库连接的使用情况，以及各种状态信息，非常有用。SHOW PROCESSLIST; 只列出前100条，如果想全列出请使用SHOW FULL PROCESSLIST;

属性列以及含义：

command 显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。
Closing tables 正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Copying to tmp table on disk 由于临时结果集大于tmp_table_size（默认16M），正在将临时表从内存存储转为磁盘存储以此节省内存。

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
IF
MOD ( i, 100 ) = 0 THEN

SET j = CONCAT( first_name , i );

END IF;
IF
MOD ( i, 200 ) = 0 THEN

SET k = CONCAT( last_name , i );

END IF;
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;后结果如下：

字段说明：

Key_name 索引名。如果名字相同则表明是同一个索引，并且是联合索引，每一行都表示联合索引中的某一个列。
Cardinality 一个索引上不同的值的个数，我们称之为 基数 （cardinality），也称为区分度，这个基数越大，索引的区分度越好。该值的统计不一定是准确的，可以使用ANALYZE TABLE修正。
Sub_part 前缀索引。如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列的值都被编入索引，则为NULL。
Packed 关键字如何被压缩。如果没有被压缩，则为NULL。压缩一般包括压缩传输协议、压缩列解决方案和压缩表解决方案。
6.ALTER TABLE xx ENGINE = INNODB

重建表，包括索引结构。可以消除索引页分裂以及删除数据时留下的磁盘碎片。

7.ANALYZE TABLE

不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了MDL读锁。可以用来修正show index from tablename;中统计索引的Cardinality是数据异常的情况。

到此这篇关于MySQL中数据库优化的常见sql语句总结的文章就介绍到这了,更多相关MySQL数据库优化语句内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

我想要获取技术服务或软件
服务范围：MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式：远程服务、电话支持、现场服务，沟通指定方式服务
技术标签：数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题

本站部分文章参考或来源于网络，如有侵权请联系站长。
数据库远程运维 MySQL中数据库优化的常见sql语句总结