索引的优点
- 通过索引快速定义到数据，大大减少了需要扫描的数据量。
- 避免排序和临时表（索引的有序性）
- 将随机I/O变为顺序I/O(磁盘预读取、节点大小==磁盘页、索引的有序性、以及聚簇索引)
什么时候使用索引
- 数据量大，需要索引，并且重复率低。否则，全表扫描效率更高。
优化器对索引的部分影响
目标：给定一个SQL，查找SQL最优（局部最优）的执行路径，使得用户能够更快的得到SQL的执行结果。
对索引的影响：
- 如果优化器判断使用索引的路径更长，会跳过索引，使用全表扫描；
可以使用force关键字强制使用索引
索引的使用规则
一、索引本身的常识规则和B+树特性得出的基础规则
1.删除不使用的索引
1.选择性：记录的重复比例越低，查询效率越高，所以将选择性高的列放在最前面；
2.所以的数据类型、长度越短越好
- 整型类型快于字符类型
- 使用内置的日期和时间类型（本质是整型）快于字符串
3.字段类型尽量设置为非空：
原则：即使要在表中储存「没有值」的字段，还是有可能不使用 NULL 的。考虑使用 0、特殊值或空字符串来代替它。
原因：Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MYisam 中固定大小的索引变成可变大小的索引。
经验教训：
-
1、空值匹配要使用 not null
-
2、NOT IN子查询在有NULL值的情况下返回永远为空结果，查询容易出错
select user_name from table_2 where user_name not in (select user_name from table_3 where id!=1) -- 子查询中返回 ["congcong",null],则查询为空；
-
3、如果有 Null column存在的情况下，count(Nullcolumn)需要格外注意，null 值不会参与统计。
select CONCAT("1",null) from dual; -- 执行结果为null。
-
4、如果在两个字段进行拼接：比如题号+分数，首先要各字段进行非null判断，否则只要任意一个字段为空都会造成拼接的结果为null。
4.覆盖索引：索引列就是要要查询的数据，直接返回，减少回表操作。
5.多列索引的效率 > 多个单列索引
6.索引列不支持左侧是函数
7.从左匹配：
- 对于特别长的字段，可以只索引字段的左边一部分;
- 如果是单列索引，体现在左模糊匹配，即name like '%xxxx';
- 如果是多列索引，（code,name,age,）,从左边的索引开始匹配，不会跳过中间的索引列；
7.区间查询
8.多列索引中，中间某列是范围查询，则右边的列都无法使用索引
9.利用B+索引的有序性来排序,来提高ORDER BY和GROUP BY的速度
二、利用聚簇索引的特性
1.主键索引的生成要有序，插入的时候按主键索引的顺序的插入。
2.充分利用主键索引直接获取数据来提高性能。
3.InnoDB通过主键聚集数据，如果没有定义主键，InnoDB将选择一个没有空值的列创建聚簇索引，所以一定要创建主键索引。
三、利用索引排序
前言：自己的对知识的复习和归纳，参考网上资料和书籍（Mysql高性能），部分概念便于理解会做简单处理。
规则：
- 满足索引的最左前缀要求。
- order by 字段要有索引。
- 如果是多列索引，索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。
- 如果有where，并且是多列索引，需要满足最左原则，where条件必须和索引的顺序一致，如果只用到单列则必须是最左列。
- 如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表（驱动表）时，才能使用索引做排序。
example:
可以:
1、SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort]
-- > 在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。
2、SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort]
-- > 建立一个联合索引(columnX,sort)来实现order by 优化。
不可以：
1、KEY (realname,sex,age) : SELECT * FROM test WHERE realname = 'wen' ORDER BY sex DESC ,age ASC
-- 不可以（排序顺序不对）
2、KEY (realname,sex,age) : SELECT * FROM test WHERE realname = 'wen' ORDER BY age
-- 不能（不满足最左匹配）
3、in ：SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort]
-- in多列索引不行；
4、SELECT * FROM test WHERE realname = 'wen' ORDER BY age
-- 不满足最左匹配
5、（key1）,(key2) : SELECT * FROM t1 ORDER BY key1, key2
-- 对不同的索引键做 ORDER BY ：(key1,key2分别建立索引) 不行。
6、KEY (realname,sex,age) : SELECT * FROM test WHERE realname > 'wen' ORDER BY sex,age
-- 多列索引，查询在索引第一列上是范围条件不行。
filesort排序
介绍： 当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序）。
对于filesort，MySQL有两种排序算法。
- (1)、两遍扫描算法(Two passes)：
实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存（通过参数sort_buffer_size设定）中进行排序，完成排序之后再次通过行指针信息取出所需的Columns。 -- 注：该算法是4.1之前采用的算法。
- 缺点：它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。
- 优点：内存开销较小。
- (2)、一次扫描算法(single pass)
该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出。-- 注：从 MySQL 4.1 版本开始使用该算法。
- 优点：它减少了I/O的次数，效率较高
- 缺点：内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存。
总结：在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。
当取出的所有大字段大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，
反之，则会选择第二种。
为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。
当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出“Using filesort”；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出“Using temporary;Using filesort”。
