http://geeksblog.cc/2016/06/11/mysql-optimize/
优化sql的一般步骤
- 通过show status了解各种sql的执行频率
- 定位执行效率低的sql语句
- 通过explain分析效率低的sql
- 通过show profile分析sql ?
- 通过trace分析优化器如何选择执行计划
- 确定问题，采取措施优化
索引优化措施
-
mysql中使用索引的典型场景
- 匹配全值，条件所有列都在索引中而且是等值匹配
- 匹配值的范围查找，字段必须在索引中
- 匹配最左前缀，复合索引只会根据最左列进行查找
- 仅仅对索引进行查询，即查询的所有字段都在索引上
- 匹配列前缀，比如like ‘ABC%’,如果是like ‘%aaa’就不可以
- 如果列名是索引，使用column is null会使用索引
-
存在索引但不会使用索引的典型场景
- 以%开头的like查询不能使用b树索引
- 数据类型出现隐式转换不能使用索引
- 复合索引，查询条件不符合最左列原则
- 用or分割的条件，如果前面的条件有索引，而后面的条件没有索引
-
查看索引使用的情况
1
show status like 'Handler_read%';
如果Handler_read_rnd_next的值比较高，说明索引不正确或者查询没有使用到索引
有索引: mysql> select * from dd;
+----+
| a |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
+----+
13 rows in set (0.00 sec) mysql> show create table dd;
+-------+----------------------------------------
| Table | Create Table
+-------+----------------------------------------
| dd | CREATE TABLE `dd` (
`a` int(11) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------
1 row in set (0.02 sec)
mysql> select * from dd where a=10;
+----+
| a |
+----+
| 10 |
+----+
1 row in set (0.00 sec) mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 | //增加的是这个值
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 2 |
+-----------------------+-------+
7 rows in set (0.00 sec)
无索引: mysql> show create table q;
+-------+------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------+
| q | CREATE TABLE `q` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec) mysql> select * from q where a=10;
+------+
| a |
+------+
| 10 |
+------+
1 row in set (0.00 sec) mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 56 |
+-----------------------+-------+
7 rows in set (0.00 sec) mysql> select * from q where a=11;
+------+
| a |
+------+
| 11 |
+------+
1 row in set (0.00 sec) mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 2 |
| Handler_read_key | 2 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 70 |
+-----------------------+-------+
7 rows in set (0.01 sec)
简单实用的优化方法
- 定期检查表和分析表
分析表语法：1
analyze table 表名；
检查表语法：
1 |
check table 表名； |
- 定期优化表
- 对于字节大小不固定的字段，数据更新和删除会造成磁盘空间不释放，这时候就行优化表，可以整理磁盘碎片，提高性能
语法如下：1
optimize table user(表名)；
- 对于字节大小不固定的字段，数据更新和删除会造成磁盘空间不释放，这时候就行优化表，可以整理磁盘碎片，提高性能