MySQL优化

SQL 优化的一般步骤

    SQL 优化，首先查看数据库的哪种 DML 执行次数多，然后查看 mysql 的慢查询日志，分析 SQL 语句是否可以优化，通过调整数据库结构、存储引擎和 SQL 语句达到数据库优化和 SQL 优化。
常用命令：show status、show variables

• show status 查看 mysql 状态列表，不可手动更改

# 查看增删改查的次数
# 格式：show [session|global] status;
# 解释：session 表示本次登录以来（默认），global 表示自服务器启动以来
# 常用命令：
show status like 'com_insert%';         show global status like 'com_insert%';
show status like 'com_delete%';         show global status like 'com_delete%';
show status like 'com_update%';         show global status like 'com_update%';
show status like 'com_select%';         show global status like 'com_select%';
# 只针对 innodb 存储引擎，表示每次执行影响的行数的累加和
show status like 'innodb_rows%';

• show variables 查看mysql 参数列表

# 查看慢查询是否开启
show variables like 'slow_query%';
# 查看慢查询临界时间
show variables like 'long%';

• 根据慢查询日志定位执行效率低的 SQL 语句

explain select * from table_name\G;
desc select * from table_name\G;

慢查询日志

• 开启慢查询

mysql> show variables like'slow_query%';
+---------------------+-------------------------------------------------+
| Variable_name       | Value                                           |
+---------------------+-------------------------------------------------+
| slow_query_log      | OFF                                             |
| slow_query_log_file | /var/lib/mysql/iz2ze9u14nhegdgjmi3eayz-slow.log |
+---------------------+-------------------------------------------------+
mysql> show variables like 'long%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

开启方式一：全局变量

set global slow_query_log='ON'; 
set global slow_query_log_file='/data/mysql/slow.log';
set global long_query_time=1;

开始方式二：配置文件

# 修改 mysql 的 my.cnf 文件，在 [mysqld] 下添加
slow_query_log = ON
slow_query_log_file = /data/mysql/slow.log
long_query_time = 1
# 重启 MySQL 服务
service mysqld restart

• 查看慢查询的次数

show status like 'slow_queries';

索引改善优化

索引存储方式：
    MyISAM 存储引擎的表的数据和索引是分开存储的，即每个 MyISAM 在磁盘上存储 .frm（存储表定义）、.MYD（存储数据）、.MYI（存储索引），数据和索引存放在不同的文件里，获取速度更快。
Innodb 存储引擎的表数据和索引是存储在同一个表空间里面。

索引的分类：

• 普通索引
• 主键索引
• 唯一索引
• 组合索引

    主键索引和唯一索引：在整个表结构里，它们都是唯一的，即一个确定的索引在查询时有且仅有一条对应的数据
普通索引：索引不具有唯一性，一个确定的索引可能包含多条数据

# 创建索引的语法
# alter 可以添加普通、唯一和主键索引，column_name_list 是列名，列与列之间用 “,” 隔开
alter table table_name add index index_name(column_name_list);
alter table table_name add [unique|primary key] (column_name_list);
# alter 删除索引
alter table table_name drop index index_name;
alter table table_name drop primary key;

# create 可以添加普通和唯一索引
create index index_name on table_name (column_name_list);
create unique index index_name on table_name (column_name_list);

# drop 删除索引
drop index index_name on table_name;

# 查看表存在的索引
show index from user;

# 查看表索引使用的情况
show [session|global] status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 2     |
| Handler_read_key      | 4     |   一个行被索引值读的次数
| Handler_read_last     | 0     |
| Handler_read_next     | 19    |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 17    |   值越高，表示表里面需要做索引，结合慢查询分析是哪条 SQL 语句
+-----------------------+-------+

# 表结构
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'user1');
INSERT INTO `user` VALUES ('2', 'user2');
INSERT INTO `user` VALUES ('3', 'user3');
INSERT INTO `user` VALUES ('4', 'user4');
INSERT INTO `user` VALUES ('5', 'user5');
INSERT INTO `user` VALUES ('6', null);
INSERT INTO `user` VALUES ('7', '123');

# 普通查询
desc select * from user where name='user1';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

# 给 name 添加普通索引
create index in_name on user(name);
desc select * from user where name='user1';
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | user  | ref  | in_name       | in_name | 93      | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+

# 比较两次查询，发现 type、possible_keys、key、ref、rows 均有不同，其他列不太重要
# 首先简单介绍一下这些列的含义
type（重点）：表的连接类型，性能由好到差：
    system：表仅有一行
    const：只有一行匹配，即查询使用了索引，并且索引唯一
    eq_ref：使用主键或唯一性索引时
    ref：使用索引，但不是主键和唯一索引
    range：范围扫描，即带有 BETWEEN 或在 WHERE 子句里面带有 > 的查询
    index：全表扫描，按索引次序进行而不是行，避免了排序。如果在 Extra 列中看到了 Using index，说明使用的是覆盖索引，只扫描索引的数据
    All：全表扫描
possible_keys：可能用到的索引
key：实际使用的索引
rows：查询影响行数，不是精确值

索引被使用的前提：

1 . like 查询，只有 % 不再第一个字符的位置时
2 . 如果索引值存在 null 值时desc select * from user where name is null\G;
3 . or 的连接条件，当前后连接条件都用到索引时，索引才能被使用
4 . and 的连接条件，当且仅当前后都使用索引并且条件都为真时，索引才能被使用

desc select * from user where id =1 and name ='user1';

+----+-------------+-------+-------+-----------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys   | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-----------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | user  | const | PRIMARY,in_name | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+-----------------+---------+---------+-------+------+-------+

desc select * from user where id =1 and name ='user7';

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

5 . 如果列是字符类型，查询条件一定也是字符类型

desc select * from user where name='123';

+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | user  | ref  | in_name       | in_name | 93      | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+

desc select * from user where name=123;

+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | in_name       | in_name | 93      | NULL |    7 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

6 .where 条件中避免在 “=” 左边有算术运算、函数或其他运算

select id from t where num/2=100
# 应该改为
select id from t where num=100*2

表优化

• 检查表

# 创建表 user2
create table user2 like user;
# 复制内容
insert into user2 select * from user;
# 创建视图
create view v_user as select * from user where id>2 and id<4;
# 删除 user1
drop table user1;
# 检查一个或多个表是否有错误
check table v_user;
+---------------+-------+----------+---------------------------------------------------------------------------------------------------------------------------------+
| Table         | Op    | Msg_type | Msg_text                                                                                                                        |
+---------------+-------+----------+---------------------------------------------------------------------------------------------------------------------------------+
| mytest.v_user | check | Error    | Table 'mytest.user' doesn't exist                                                                                               |
| mytest.v_user | check | Error    | View 'mytest.v_user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
| mytest.v_user | check | error    | Corrupt                                                                                                                         |
+---------------+-------+----------+---------------------------------------------------------------------------------------------------------------------------------+
# 将 user2 重命名
alter table user2 rename user;
# 再次检查
check table v_user;
+---------------+-------+----------+----------+
| Table         | Op    | Msg_type | Msg_text |
+---------------+-------+----------+----------+
| mytest.v_user | check | status   | OK       |
+---------------+-------+----------+----------+

• 优化表空间
  如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表进行了很多次改动，则需要做定期优化。这个命令可以将表中的空间碎片进行合并，但是此命令只对 MyISAM、BDB 和 InnoDB 表起作用。
  注意：千万不要在有大量访问人群的时间点操作

# 格式：optimize table table_name [,table_name...]
optimize table user;
+-------------+----------+----------+-------------------------------------------------------------------+
| Table       | Op       | Msg_type | Msg_text                                                          |
+-------------+----------+----------+-------------------------------------------------------------------+
| mytest.user | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| mytest.user | optimize | status   | OK                                                                |
+-------------+----------+----------+-------------------------------------------------------------------+

常用 SQL 的优化

• 大批量插入数据

# 使用 infile 和 outfile 代替 mysqldump，因为 mysqldump 会导出大量的表结构语句
# outfile：仅导出数据
select * from user into outfile '/tmp/test.txt';
# 创建与 user 相同结构的表，这里使用 truncate 清空表结构
truncate user;
# infile 配合 load data 使用
load data infile '/tmp/test.txt' into table user(id,name);

• 大批量插入数据（MyISAM 引擎）

# 上面介绍的 infile 和 outfile 还可以进一步优化
# 即针对 MyISAM 引擎，在 infile 数据时，关闭非唯一索引的检测
alter table table_name disable keys;
# 执行过 infile 后，在打开检测
alter table table_name enable keys;

• 大批量插入数据（InnoDB）
  因为 InnoDB 表是按照主键顺序保存的，所以使用 outfile 时，先按照主键顺序排列，可以有效提高导入效率。

select * from user order by id into outfile '/tmp/test.txt';

• 大批量插入数据，可选操作

# 关闭唯一性校验，一般情况下，不建议关闭，怕导入数据后，再开启唯一性校验时，有数据冲突的问题
# 关闭
set unique_checks=0;
# 开启
set unique_checks=1;

# 关闭事务自动提交
# 关闭
set autocommit=0;
# 开启
set autocommit=1;

• 优化 group by

# group by 会对结果排序，因此可以使用 order by null 禁止排序
select * from shop_order group by buyer_id order by null;

• 优化嵌套查询
  嵌套查询中，主查询不会使用索引。多使用连接查询和多表查询。