#夏日挑战赛#MySQL 优化真的难吗？学习第2天 原创

「本文正在参加星光计划3.0–夏日挑战赛」

优化可用的一些命令

上篇博客说到了数据库支持的引擎，如果你想查阅你当前电脑上支持的引擎，可以使用如下命令。

show engines;

其中列名含义如下：

• Engine：引擎名称；
• Support：是否支持；
• Comment：备注；
• Transactions：是否支持事务；
• XA：XA事务；
• Savepoints：事务回滚。

查看当前数据库使用的引擎

show variables like '%storage_engine%'

其中各个变量值的含义如下：

• default_storage_engine：默认存储引擎；
• default_tmp_storage_engine：临时表默认存储引擎；
• internal_tmp_disk_storage_engine：磁盘临时表。

使用 show variables; 可以查看全部变量。
如果想查看最大连接数，使用如下命令即可。

show variables like 'max_connections' 

上文提及了临时表，那必然会涉及临时表大小的问题。

show variables like '%tmp_table_size%';
show variables like '%max_heap_table_size%';

上述2个变量值中，临时表的大小会以较小者为准。

如果涉及的表有 TEXT 或 BLOB 类型的列，则临时表的大小<配置的阈值，也会在磁盘上创建临时表。

临时表空间处理办法

临时表具有自己的表空间文件。
新文件与通用表空间一起位于数据目录中，名称为 ibtmp1 。
使用如下命令可以清理表空间

optimize table '表名'

不运行手动运行 optimize table （该命令用来重新利用未使用的空间，并整理数据文件的碎片。如果你不能使用 optimize table ，那么让 ibtmp1 大小缩小为零的方法，只能重新启动服务器。

另外需要注意 optimize table 只对 MyISAM ， BDB 和 InnoDB 表起作用，而且该命令在使用的时候，MySQL会锁定表。
运行成功，结果如下所示。

在处理前，也可以使用下面的命令查看数据库碎片空间大小。

show table status like '表名'

然后对该表使用 optimize table 命令，但是会出现如下内容，使用新的命令进行修改。

当是InnoDB引擎时我们就用 alter table table.name engine='innodb' 代替 optimize 做优化

show status

通过 show status 命令了解 SQL 的执行频率，从而进行优化操作。

• show session status：查看当前连接的统计结果；
• show global status：查看 global 级的统计结果，即从数据库上次启动开始计算。

例如如下命令：

show status like 'Bin%'

上述参数的含义为：

• binlog_cache_use：二进制日志已缓存的事务类条数（内存中）；
• binlog_cache_disk_use：二进志日志缓存的已经存在硬盘的事务类条数 ；
• Binlog_stmt_cache_disk_use：二进志日志缓存的已经存在硬盘的（非事务）条数；
• Binlog_stmt_cache_use ：二进制日志已缓存的条数（内存中） 非事务型的语句条数；

事务个数，每次事务提交，都会有1次增加。

show status like 'Com_%' 命令
该命令表示 SQL 语句执行测次数，例如下述内容：

• Com_insert：插入次数，批量插入，只累加1次；
• Com_select：查询次数；
• Com_update：更新次数；
• Com_delete：删除次数。

show status like 'Innodb_%' 命令
该命令只针对 InnoDB 存储引擎。

• Innodb_rows_inserted：插入的行数；
• Innodb_rows_updated：更新的行数；
• Innodb_rows_read：查询的行数；
• Innodb_rows_deleted：删除的行数。

这些命令可以比较清洗的看到数据库应用是偏查询，还是偏操作。

除此之外，还有几个参数可以查看，例如 Uptime 查看服务器工作时间，单位是秒， Slow_queries 慢查询次数， Connections 尝试链接 MySQL次数。

show processlist 命令

查看当前 MySQL 正在执行的线程，定位锁，仅 root 用户，可以看到全部线程运行情况。

该命令表示从 information_schema.processlist 表中查询数据。

使用如下命令，可以查询连接者的 IP

select substring_index(host,':' ,1) as client_ip from information_schema.processlist 

除此之外，还可以查看正在执行的命令。

select * from information_schema.processlist where Command != 'Sleep' order by Time desc;

如果发现执行的时间过长，可以找出进程序号，然后 kill 掉。

查询出来的 ID 就是进程ID，其中最复杂的列是 Command，表示是指此刻该线程正在执行的命令，后面遇到具体的内容再行进行说明。

记录时间

今天是持续写作的第 <font color=red>284</font> / 365 天。
可以<font color=#04a9f4>关注</font>我，<font color=#04a9f4>点赞</font>我、<font color=#04a9f4>评论</font>我、<font color=#04a9f4>收藏</font>我啦。