Mysql-索引优化

1. 索引的概念

MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。可以简单理解为排好序的快速查找数据结构。

1.1 索引优缺点

优点：
① 提高数据检索的效率，降低数据库的IO成本。
② 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

缺点：
①虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
② 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

1.2 索引分类

单值索引
概念：即一个索引只包含单个列，一个表可以有多个单列索引
语法：
①随表一起创建：

CREATE TABLE customer (
	id INT(10) UNSIGNED AUTO_INCREMENT ,
	customer_no VARCHAR(200),
	customer_name VARCHAR(200), 
	PRIMARY KEY(id),
	KEY (customer_name)
);

②单独建单值索引：

CREATE INDEX idx_customer_name ON customer(customer_name);

唯一索引
概念：索引列的值必须唯一，但允许有空值
语法：
①随表一起创建：

CREATE TABLE customer (
	id INT(10) UNSIGNED AUTO_INCREMENT ,
	customer_no VARCHAR(200),
	customer_name VARCHAR(200),
	PRIMARY KEY(id),
	KEY (customer_name),
	UNIQUE (customer_no)
);

②单独建唯一索引：

CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

主键索引
概念：设定为主键后数据库会自动建立索引，innodb为聚簇索引
语法：
①随表一起建索引：

CREATE TABLE customer (
	id INT(10) UNSIGNED AUTO_INCREMENT ,
	customer_no VARCHAR(200),
	customer_name VARCHAR(200), 
	PRIMARY KEY(id)
);

②单独建主键索引：

ALTER TABLE customer add PRIMARY KEY customer(customer_no);

③删除建主键索引：

ALTER TABLE customer drop PRIMARY KEY ;

④修改建主键索引：
必须先删除掉(drop)原索引，再新建(add)索引

复合索引
概念：即一个索引包含多个列
语法：
①随表一起建索引：

CREATE TABLE customer (
	id INT(10) UNSIGNED AUTO_INCREMENT ,
	customer_no VARCHAR(200),
	customer_name VARCHAR(200), 
	PRIMARY KEY(id), 
	KEY (customer_name), 
	UNIQUE (customer_name),
	KEY (customer_no,customer_name)
);

②单独建索引：

CREATE INDEX idx_no_name ON customer(customer_no,customer_name);

2. 单表使用索引常见的索引失效

①最佳左前缀法则
查询字段与索引字段顺序的不同会导致，索引无法充分使用，甚至索引失效！
原因：使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。
②不要在索引列上做任何计算
不在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换），会导致索引失效而转向全表扫描。
③索引列上不能有范围查询
建议：将可能做范围查询的字段的索引顺序放在最后
④尽量使用覆盖索引
即查询列和索引列，不要写 select *。
⑤注意不等于(!= 或者<>)
mysql 在使用不等于(!= 或者<>)时，有时会无法使用索引会导致全表扫描。
⑥字段的 is not null 和 is null
当字段允许为 Null 的条件下：is not null 用不到索引，is null 可以用到索引。
⑦like 的前后模糊匹配
前缀不能出现模糊匹配！(eg: %a)
⑧减少使用 or
使用 union all 或者 union 来替代

3. 关联查询优化

①在优化关联查询时，只有在被驱动表上建立索引才有效。
②left join 时，左侧的为驱动表，右侧为被驱动表。
③inner join 时，mysql 会自己帮你把小结果集的表选为驱动表。
④straight_join: 效果和 inner join 一样，但是会强制将左侧作为驱动表。
⑤子查询尽量不要放在被驱动表，有可能使用不到索引；left join时，尽量让实体表作为被驱动表。
⑥能够直接多表关联的尽量直接关联，不用子查询。
⑦在范围判断时，尽量不要使用 not in 和 not exists，使用 left join on xxx is null 代替。

4. 排序分组优化

①无过滤，不索引。where，limt 都相当于一种过滤条件，有过滤条件才能使用上索引。
②顺序错，必排序。where 两侧列的顺序可以变换，效果相同，但是 order by 列的顺序不能随意变换。
③方向反，必排序。如果可以用上索引的字段都使用正序或者逆序，实际上是没有任何影响的，无非将结果集调换顺序。如果排序的字段，顺序有差异，就需要将差异的部分，进行一次倒置顺序，因此还是需要手动排序的。
④group by 使用索引的原则几乎跟 order by 一致 ，唯一区别是 group by 即使没有过滤条件用到索引，也可以直接使用索引。

5. using filesort

5.1 mysql 的排序算法

①双路排序
MySQL 4.1 之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 order by 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。从磁盘取排序字段，在 buffer 进行排序，再从磁盘取其他字段。简单来说，取一批数据，要对磁盘进行了两次扫描，众所周知，I\O 是很耗时的，所以在 mysql4.1 之后，出现了第二种改进的算法，就是单路排序。
②单路排序
从磁盘读取查询需要的所有列，按照 order by 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机 IO 变成了顺序 IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。
③单路排序的问题
由于单路是后出的，总体而言好过双路。但是存在以下问题：
在 sort_buffer 中，方法 B 比方法 A 要多占用很多空间，因为方法 B 是把所有字段都取出, 所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序（创建 tmp 文件，多路合并），排完再取 sort_buffer 容量大小，再排……从而多次 I/O。
结论：本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失

5.2 如何优化

①增大 sort_butter_size 参数的设置
不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 1M-8M 之间调整。
②增大 max_length_for_sort_data 参数的设置
mysql 使用单路排序的前提是排序的字段大小要小于 max_length_for_sort_data。提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，明显症状是高的磁盘 I/O 活动和低的处理器使用率。（1024-8192 之间调整）。
③减少 select 后面的查询的字段。
当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序。两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 I/O，但是用单路排序算法的风险会更大一些,所以要提高 sort_buffer_size。

6. 截取查询分析

6.1 慢查询日志

（1）MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
（2）具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。
（3）默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

开启设置
（1）开启语句

SQL 语句	描述	备注
SHOW VARIABLES LIKE ‘%slow_query_log%’;	查看慢查询日志是否开启	默认情况下 slow_query_log 的值为 OFF，表示慢查询日志是禁用的
set global slow_query_log=1;	开启慢查询日志
SHOW VARIABLES LIKE ‘long_query_time%’;	查看慢查询设定阈值	单位秒
set long_query_time=1	设定慢查询阈值	单位秒

（2） 如永久生效需要修改配置文件 my.cnf 中[mysqld]下配置

[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/mysql-slow.log
long_query_time=3
log_output=FILE

（3） 运行查询时间长的 sql，打开慢查询日志查

6.2 日志分析工具 mysqldumpslow

（1） 命令：[root@cocoon ~]# mysqldumpslow --help

参数	描述
-s	是表示按照何种方式排序
c	访问次数
l	锁定时间
r	返回记录
t	查询时间
al	平均锁定时间
ar	平均返回记录数
at	平均查询时间
-t	即为返回前面多少条的数据
-g	后边搭配一个正则匹配模式，大小写不敏感的

（2） 查看mysqldumpslow的帮助信息

#得到返回记录集最多的 10 个 SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log
#得到访问次数最多的 10 个 SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/mysql-slow.log
#得到按照时间排序的前 10 条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/mysql-slow.log
#另外建议在使用这些命令时结合 | 和 more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log | more