索引问题 - 简书

1.索引存储分类
索引是在MySQL的存储引擎层实现的，每个存储引擎的索引不一定相同。MySQL提供以下4种索引：

B-Tree索引：最常见的索引类型，大部分引擎都支持B数索引。
HASH索引：只有Memory引擎支持。
R-Tree索引（空间索引）：MyISAM的一个特殊索引类型，主要用于地理空间数据类型。
Full-text（全文索引）：MyISAM的一个特殊类型，用于全文索引，Innodb5.6版本开始也支持。

MySQL支持前缀索引，缺点是在排序order by和分组group by时无法使用。

2.MySQL如何索引
可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询。

（1） MySQL能够使用索引的典型场景：

匹配全值，对索引中所有列都指定具体值，即对索引所有列都有等值匹配条件。
匹配值的范围查询，对索引值能够进行范围查找。
匹配最左前缀，仅仅使用索引中的最左边列进行查找。最左匹配原则可以算B-Tree索引使用的首要原则。
仅仅对索引进行查询，当查询列都在索引的字段中时，查询效率更高。
匹配列前缀，仅仅使用索引中的第一列，并且只包含索引第一列开头一部分进行查找。
能够实现索引匹配部分精确而其他部分进行范围匹配。
如果列名是索引，那么使用column_name is null 就会使用索引。（区别于Oracle）
5.6引入Index Condition Pushdown（ICP）特性，优化查询。

（2）存在索引但不能使用索引典型场景：

以%开头的like查询不能够利用B-Tree索引。
数据类型出现隐式转换的时候不会使用索引，特别是当列类型是字符串，那么一定记得在where条件中八字符串常量用引号引起来，否则即便这个列上有索引，MySQL也不会用到，因为MySQL默认把输入的常量值进行转换后才进行检索。
复合索引时，假如查询条件不包含索引列最左边部分，不会使用复合索引。
若使用索引比全表扫描更慢，则不使用索引。筛选性越高越容易使用到索引，筛选性越低越不容易使用索引。
用or分隔的条件，若or前的条件中的列有索引，而后面没有，那么涉及的索引不会被用到。因为后面的列没有索引，一定会全表扫描，不必多作一次索引扫描。

（3）查看索引使用情况
若索引正在工作，则Handler_read_key的值将很高，这个值表示一个行被索引值读的次数，值低表明增加索引性能改善不高，即该索引并不经常使用。

Handler_read_rnd_next值高意味着查询运行低效，并且应该建立索引补救。这个值含义是在数据文件中读下一行的请求数。

3.优化方法

定期分析表和检查表：
分析表语法如下：

analyze [LOCAL|NO_WRITE_BINLOG|TABLE] tbl_name [,tbl_name]...

分析和存储表的关键字分布，分析结果将可以使得系统得到准确的统计信息，使得SQL能够生成正确的执行计划。分析期间，使用一个读取锁定对表进行锁定。对MyISAM、DBD和InnoDB表有作用。
检查表语法如下：

check table tbl_name [,tbl_name] ... [option]...option={QUICK|FAST|MEDIUM|EXDIUM|EXTENDED|CHANGED}

作用是检查一个或多个表是否有误，对MyISAM、InnoDB表有作用，也可以用于检查视图。

定期优化：
优化表语法如下：

optimize [LOCAL|NO_WRITE_TO_BINLOG] table tbl_name[,tbl_name]...

如果已经删除表的一大部分，或者已经对含有可变长度行的表进行了很大更改，则应使用optimize table进行表优化。该命令可以将表空间碎片进行合并，支队MyISAM、BDB、InnoDB表有作用。在删除大量数据后，InnoDB表可以用alter table但不修改引擎方式回收不用空间。

4.常用SQL优化

大批量插入数据：当使用load命令导入数据时，设当设置可以提高导入速度。对于MyISAM表，可以通过以下方式快速导入大量数据。

alter table name disable keys;
loading the data
alter table name enable keys;

disable/enable keys用来打开/关闭MyISAM表非唯一索引的更新。

对于InnoDB类型，可以用以下几种方式提高导入效率：
（1）因为InnoDB类型表是按照主键顺序保存的，所以将导入的数据按照主键顺序排列，可以有效提高导入数据的效率。
（2）在导入数据前执行set unique_checks=0，关闭唯一性校验，导入完成后恢复唯一性校验。
（3）如果应用使用自动提交方式，导入前执行set autocommit=0，关闭自动提交，导入结束后打开自动提交。

优化insert语句
（1）如果同时从同一客户插入很多行，应该尽量使用多个值表的insert语句，将缩减客户端于数据库间的连接、关闭等消耗。例如：

insert into name values(1,1),(2,2)...

(2)如果从不同客户端插入很多行，可以通过使用insert delayed语句得到更高速度。delayed含义是让insert语句马上执行，其实数据都被放在内存队列中，并没有真正写入磁盘；low_priority刚好相反，在所有其他用户线程对表的读写完成后进行。
（3）将索引和数据文件分布在不同磁盘是存储。
（4）如果进行批量插入，可以增加bulk_insert_buffer_size提高速度，只能对MyISAM使用。
（5）当从一个文本文件装入一个表时，使用load data infile。通常比使用insert快。

优化order by 语句
（1）MySQL中两者排序方式：第一种通过有序索引直接返回有序数据，这种方法在使用explain分析程序是显示为using index，不需要额外排序。
（2）第二种通过对返回数据进行排序，所有不是通过索引直接返回排序结果的排序都叫Filesort排序

尽量减少额外排序，通过索引直接返回有序数据。

Filesort优化
通过创建合适的索引能够减少Filesort出现。对于Filesort，MySQL有两种排序算法。
（1）两次扫描：先根据条件排序和行指针信息第二次读取存在可能导致大量随机I/O，有点是内存开销少。
（2）一次扫描算法：一次性取出索引满足条件行的索引字段，内存消耗较。

MySQL通过比较，max_length_for _data大小和query语句取出字段中大小来判断使用哪种排序。如果max_length_for _data值更大，使用第二种优化后的算法，否则就是要第一章算法。

优化group by语句
默认MySQL对所有group by的字段进行排序，想要避免排序消耗可以指定order by null禁止排序。
优化嵌套查询
连接（join）更有效一些，是因为MySQL不需要在内存中创建临时表来完成逻辑上需要两个步骤的查询工作。
MySQL如何优化or条件
对于含有or的查询子句，如果要利用索引，则or之间的每个条件都必须用到索引。在建有复合索引的列上面做or操作时，却不能用到索引。
优化分页查询
一般分页查询时，通过创建覆盖索引能够比较好提高性能。
（1）第一种优化思路：在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他内容。让MySQL扫描尽可能少的页面来提高分页效率。

（2）第二种优化思路：把limit 查询转换成某个位置的查询，和开发协商，在翻页过程中增加一个参数last_page_record,用来记录上一页最后一行租赁编号。把limit m，n转换成limit n查询，只适合在排序字段不会出现重复值的特定环境，能够减轻分页翻页压力；如果排序字段出现大量重复值，不适合这种方式优化。

使用SQL提示
sql提示是在SQL语句中加入一些人为的提示来达到优化操作的目的。
（1）use index：在查询语句中表名后面，添加use index来提供希望MySQL去参考索引列表，就可以让MySQL不在考虑其他索引。
（2）ignore index：单纯的想让MySQL忽略一个或多个索引，则可以使用ignore index作为hint。
（3）force index：为强制MySQL使用一个特定的索引，可以在查询中使用force index作为hint。