MySQL数据库优化（1）
短信预约 -IT技能 免费直播动态提醒
前言
不管对于哪种服务，对于其优化，无非是从两个方面着手，第一个是对于硬件方面的优化，第二个是对系统以及服务本身的优化。
1、查询连接MySQL服务器的次数
mysql> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections | 3 |
+---------------+-------+
1 row in set (0.01 sec)2、查询MySQL服务器的运行时间
mysql> show status like 'uptime'; //单位为“秒”
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 127 |
+---------------+-------+
1 row in set (0.00 sec)
3、查询操作的次数
mysql> show status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 12 |
+---------------+-------+
1 row in set (0.00 sec)
4、插入操作的次数
mysql> show status like 'com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert | 1 |
+---------------+-------+
1 row in set (0.00 sec)
5、更新操作的次数
mysql> show status like 'com_update';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_update | 1 |
+---------------+-------+
1 row in set (0.00 sec)
6、删除操作的次数
mysql> show status like 'com_delete';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete | 0 |
+---------------+-------+
1 row in set (0.00 sec)
7、查询MySQL服务器的慢查询次数
mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 21 |
+---------------+-------+
1 row in set (0.00 sec)
二、对SQL语句进行分析
1、使用explain关键字进行分析
mysql> explain select * from stu_info\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: stu_info #表名
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL #使用哪个列或常数与索引一起使用来查询记录
rows: 3
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)上面的select_type解释如下：
- Select_type:表示select语句的类型 其中simple 是简单查询(不包括连接查询和子查询) Primary 主查询 Union 连接查询；
2、利用索引来提高查询效率
mysql> explain select * from stu_info where s_id=3\G #没有索引时的查询结果分析如下
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: stu_info
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3 #需要查询三行才能查到（这个表数据总共也就三行）
filtered: 33.33
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> create index index_01 on stu_info(s_id); #创建索引
mysql> explain select * from stu_info where s_id=3\G #再次进行查询
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: stu_info
partitions: NULL
type: ref
possible_keys: index_01 #使用的是哪个索引名称
key: index_01
key_len: 5
ref: const
rows: 1 #创建索引后，查询1行就查到可。
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
使用索引注意事项如下：
- 做索引了之后，用 like ‘xx%’ %不在第一位查询效率最高；
- 若使用多字段索引，除了第一字段查询最快，其余不会按索引来，索引不生效；
- 若创建索引所设置的字段，查询索引组合 or 左右边的值都是属于索引设置字段下的值。
关于使用索引的其他注意事项，可以参考博文：MySQL索引类型详解；
三、profiling分析查询
通过慢日志查询可以知道哪些SQL语句执行效率低下，通过explain我们可以得知SQL语句的具体执行情况，索引使用等，还可以结合show命令查看执行状态。如果觉得explain的信息不够详细，可以同通过profiling命令得到更准确的SQL执行消耗系统资源的信息。 profiling默认是关闭的。可以通过以下语句查看：
1、查看profiling是否开启
mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF | #OFF表示未开启
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 | # 0表示未开启
+-------------+
1 row in set, 1 warning (0.00 sec)
2、开启profiling
mysql> set profiling=1; #开启
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@profiling; #qu
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set, 1 warning (0.00 sec)
3、执行要测试的SQL语句
mysql> select * from bank;
+-------+-------+
| name | money |
+-------+-------+
| lu | 1000 |
| qi | 1000 |
| zhang | 2000 |
+-------+-------+
3 rows in set (0.00 sec)
4、查看SQL语句对应的ID，对其进行分析
mysql> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------+
| 1 | 0.00012925 | select @@profiling |
| 2 | 0.00401325 | SELECT DATABASE() |
| 3 | 0.01405400 | show databases |
| 4 | 0.00034675 | show tables |
| 5 | 0.00011475 | show tabels |
| 6 | 0.00029225 | show tables |
| 7 | 0.00041200 | select * from bank |
| 8 | 0.00020225 | select * from bank |
+----------+------------+--------------------+
8 rows in set, 1 warning (0.00 sec)
mysql> show profile for query 7; #查询sql语句的详细分析
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000161 |
| checking permissions | 0.000010 |
| Opening tables | 0.000016 |
| init | 0.000047 |
| System lock | 0.000013 |
| optimizing | 0.000004 |
| statistics | 0.000013 |
| preparing | 0.000009 |
| executing | 0.000004 |
| Sending data | 0.000050 |
| end | 0.000004 |
| query end | 0.000008 |
| closing tables | 0.000007 |
| freeing items | 0.000012 |
| logging slow query | 0.000041 |
| cleaning up | 0.000013 |
+----------------------+----------+
16 rows in set, 1 warning (0.00 sec)
在上面命令的返回结果中，status是profile里的状态，duration是status状态下的耗时，因此我们关注的就是哪个状态最耗时，这些状态中哪些可以优化，当然也可以查看更多的信息，比如：CPU等。语法如下：
mysql> show profile block io for query 7\G
mysql> show profile all for query 7\G除了上面的block io和all以外，还可以换成cpu（显示用户cpu时间、系统cpu时间）、ipc（显示发送和接收相关开销信息）、page faults（显示页面错误相关开销信息）、swaps（显示交换次数相关开销的信息）。
注意：测试完成之后，记得要关闭调试功能，以免影响数据库的正常使用。
四、对数据库表结构进行优化
对数据库表结构的优化大概可以从以下几个方面着手：
- 将字段很多的表分解成多个表，尽量避免表字段过多；
- 增加中间表，合理增加冗余字段；
- 优化插入记录的速度；
- 在插入数据之前禁用索引，会让创建索引不生效，命令： ALTER TABLE table_name DISABLE KEYS;
- 根据实际情况来定，在插入记录之前禁用唯一性检查，命令：set unique_checks=0;
- 多条插入数据的命令最好整合为一条；
- 使用load data infle批量插入数据。
- 对于innodb引擎的表来说，以下几点可以进行优化：
- 禁用唯一性检查：set unique_checks=0;
- 禁用外键检查：set foreign_key_checks=0;
- 禁用自动提交：set autocommit=0;
分析表，检查表和优化表
所谓分析表，就是分析关键字的分布，检查表就是检查是否存在错误，优化表就是删除或更新造成的空间浪费。
1、分析表
分析表可以一次分析一个或多个表，在分析期间只能读，不能进行插入和更新操作。分析表的语法如下：
mysql> analyze table bank;
+-------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| test01.bank | analyze | status | OK |
+-------------+---------+----------+----------+
1 row in set (0.00 sec)
对于上述返回的结果解释：Table是表名 ，op执行的操作是什么， msg_type 信息级别（status是正常状态，info是信息，note注意，warning警告，error错误）， msg_text 是显示信息。
2、检查表
检查是否存在错误,关键字统计，检查视图是否有错误 Check table 表名 option ={quick |fast | medium|extended |changed} Quick 不扫描行，不检查错误连接 Fast 只检查没有被正确关闭的表 Medium 扫描行验证被删除的连接是有效的，也可以计算各行的关键字校验和。 Extended 对每行所有关键字进行全面的关键字查找，Changed 只检查上次检查后被更改的表和没有被正确关闭的表，Option只对myisam 有效 对innodb表无效，在执行时会给表加上只读锁。
mysql> check table bank;
+-------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| test01.bank | check | status | OK |
+-------------+-------+----------+----------+
1 row in set (0.00 sec)
3、优化表
消除删除或更新造成的空间浪费，命令语法格式为：Optimize [local |no_write_to_binlog] table tb1_name ….， 优化myisam的表和innodb的表都有效， 但是只能优化表中的varchar\text\blob数字类型， 执行过程中上只读锁。
mysql> optimize table bank\G
*************************** 1. row ***************************
Table: test01.bank
Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 2. row ***************************
Table: test01.bank
Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (0.04 sec)
———————— 本文至此结束，感谢阅读 ————————
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/40.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
