MySQL中数据库优化的常见sql语句是什么

数据库小白一枚，正在不断学习积累知识，现将学习到的知识记录一下，也是将我的所得分享给大家！而今天这篇文章《MySQL中数据库优化的常见sql语句是什么》带大家来了解一下##content_title##，希望对大家的知识积累有所帮助，从而弥补自己的不足，助力实战开发！

1.SHOW ENGINES

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

2.SHOW PROCESSLIST

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

属性列以及含义：

state列以及含义，mysql列出的状态：

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 <p>使用show index from contacts;后结果如下：</p><p><img  src="/uploads/20230427/1682594856644a5c28a0b57.png" alt="MySQL中数据库优化的常见sql语句是什么" ></p><p>字段说明：</p>

6.ALTER TABLE xx ENGINE = INNODB

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

7.ANALYZE TABLE

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

以上就是本文的全部内容了，是否有顺利帮助你解决问题？若是能给你带来学习上的帮助，请大家多多支持golang学习网！更多关于数据库的相关知识，也可关注golang学习网公众号。