Mysql性能优化
Sql
1、profiling
可以使用profiling命令查看sql语句执行的时间。
使用select @@profiling;查看开启状态。
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set (0.36 sec)
默认为0，表示不开启。
使用set命令开启。
mysql> set profiling=1;
Query OK, 0 rows affected (0.08 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set (0.16 sec)
使用几条select语句查询后，查看profiles的状态。
mysql> show profiles;
+----------+------------+----------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------+
| 1 | 0.00020175 | select @@profiling |
| 2 | 0.00231275 | select * from ADMIN |
| 3 | 0.00065675 | select * from ADMIN where ID%2=0 |
| 4 | 0.00070650 | select * from ADMIN where ID%2=1 |
+----------+------------+----------------------------------+
4 rows in set (0.16 sec)
使用show profile for query X;查看sql具体执行时间分析。（X表示Query_ID）
mysql> show profile for query 3;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000125 |#开始
| checking permissions | 0.000009 |#检查缓存
| Opening tables | 0.000020 |#打开表
| init | 0.000051 |#初始化
| System lock | 0.000014 |#锁
| optimizing | 0.000023 |#优化查询
| statistics | 0.000027 |
| preparing | 0.000019 |#准备
| executing | 0.000005 |#执行
| Sending data | 0.000212 |#发送数据
| end | 0.000007 |#结束
| query end | 0.000012 |#查询结束
| closing tables | 0.000012 |#关闭表
| freeing items | 0.000042 |#释放
| logging slow query | 0.000063 |#写入慢查询日志
| cleaning up | 0.000018 |
+----------------------+----------+
16 rows in set (0.16 sec)
2、慢查询日志
可以查看我的另一篇文章：Mysql日志
3、开启查询缓存
使用select @@query_cache_type;查看开启状态。
mysql> select @@query_cache_type;
+--------------------+
| @@query_cache_type |
+--------------------+
| OFF |
+--------------------+
1 row in set (0.38 sec)
在my.cnf文件中加入下面语句，重启生效。
query_cache_type = 1
query_cache_size = 600000
再次查看开启状态。
mysql> select @@query_cache_type;
+--------------------+
| @@query_cache_type |
+--------------------+
| ON |
+--------------------+
1 row in set (0.33 sec)
4、优化select语句
1、只需要一条记录时，使用limit 1.
2、在join时，尽量将join的字段都使用索引。
3、避免select *。
4、拆分大的delete和insert语句，因为在使用这两个命令时会锁表。
表
1、每一张表都设置一个ID字段为主键，而且该主键的类型应为int或unsigned类型，还要为该字段添加自增（auto_increment）。
2、给经常要用到的搜索的字段添加索引。
3、尽量使用not null。
4、为不同的业务需求设置不同的存储引擎。
5、垂直分割。顾名思义，就是将单张表拆分成多个表，将不经常用到的数据拆分到其他表，经常用的数据保留在这个表，这样可以降低表的复杂度。