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

来源:脚本之家  责任编辑:小易  
目录
1.SHOW ENGINES2.SHOW PROCESSLIST3.SHOW STATUS LIKE 'InnoDB_row_lock%'4.SHOW ENGINE INNODB STATUS5.SHOW INDEXS6.ALTER TABLE xx ENGINE = INNODB7.ANALYZE TABLE

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
		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;后结果如下:

字段说明:

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,则为YES
Index_type索引结构类型,常见有FULLTEXT,HASH,BTREE,RTREE
Comment、Index_comment注释

6.ALTER TABLE xx ENGINE = INNODB

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

7.ANALYZE TABLE

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

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

您可能感兴趣的文章:mysql数据库优化总结(心得)MySQL数据库优化详解Mysql数据库性能优化之子查询9种 MySQL数据库优化的技巧MySQL数据库性能优化介绍MySQL数据库优化的六种方式总结MySQL数据库查询性能优化的4个技巧干货

  • 本文相关:
  • mysql存储过程原理与使用方法详解
  • 在centos上mysql数据库服务器配置方法
  • mysql之group by和having用法详解
  • sql多表多字段比对方法实例代码
  • can''t connect to local mysql through socket ''/tmp/mysql.so
  • mysql求和函数使用示例
  • mysql 8.0.18各版本安装及安装中出现的问题(精华总结)
  • spring jdbc中数据库操作对象化模型的实例详解
  • 关于mysql?b+树索引与哈希索引详解
  • mysql中对表连接查询的简单优化教程
  • MySQL 数据库优化的具体方法说明
  • 优化MySQL数据库中的查询语句详解
  • MySQL数据库优化技术之索引使用技巧总结
  • 如何保证数据安全性 MySQL数据库设计优化技巧
  • MySQL数据库十大优化技巧
  • 从哪些方面优化mysql数据库
  • mysql数据库优化总结
  • 网站访问量大 怎样优化mysql数据库
  • 网站访问量大 怎样优化mysql数据库
  • 求高手优化MySQL数据库,数据库反应太慢。
  • 如何优化MySQL数据库
  • mysql数据库优化需要遵守的原则
  • 优化mysql数据库的经验总结
  • Mysql数据库优化可以从哪几个方面优化
  • mysql数据库如何优化,优化了哪些功能
  • 怎么进行mysql数据库优化?
  • mysql数据库sql优化原则(经验总结)
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mysqlmysql数据库优化总结(心得)mysql数据库优化详解mysql数据库性能优化之子查询9种 mysql数据库优化的技巧mysql数据库性能优化介绍mysql数据库优化的六种方式总结mysql数据库查询性能优化的4个技巧干货mysql存储过程原理与使用方法详解在centos上mysql数据库服务器配置方法mysql之group by和having用法详解sql多表多字段比对方法实例代码can""t connect to local mysql through socket ""/tmp/mysql.somysql求和函数使用示例mysql 8.0.18各版本安装及安装中出现的问题(精华总结)spring jdbc中数据库操作对象化模型的实例详解关于mysql?b+树索引与哈希索引详解mysql中对表连接查询的简单优化教程mysql安装图解 mysql图文安装教程(详细说明)can""""t connect to mysql servwindows下mysql5.6版本安装及配置过程附有截图和mysql字符串截取函数substring的用法说明mysql提示:the server quit withoumysql之timestamp(时间戳)用法详解mysql创建用户与授权方法mysql——修改root密码的4种方法(以windows为mysql查看版本号的几种方式mysql日期数据类型、时间类型使用总结mysql错误:can’t open file: ‘××centos7下安装mysql8.0.23的步骤(小白入门级别)mysql timestamp自动更新时间分享navicat查询结果不能修改的原因及解决方法如何给mysql添加自定义语法的方法示例centos下重置mysql的root密码的教程mysql 存储过程中变量的定义与赋值操作解决can""t locate extutils/makemaker.pm mysql数据迁移使用mysqldump命令windows下安装mysql8.0.18的教程(社区版)
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved