上一篇我们从索引的应用层次分析，说到了普通索引、唯一索引、主键索引、复合索引。这一期，我们在从其他层次学习洗索引。
一、前期回顾：
从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引以及R Tree索引
从应用层次划分：普通索引、唯一索引、主键索引、复合索引
从键值划分：主键索引、辅助索引（二级索引）
从数据存储以及索引逻辑关系划分：聚集索引、非聚集索引
二、索引的分类
再说 辅助索引 之前，我们先了解什么是聚集索引、非聚集索引；
聚簇索引和非聚簇索引：B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行 记录分开存放就属于非聚簇索引
在MyIsam中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key是可以重复的。
MyIsam中的主键索引如上图，辅助索引如下图：
在InnoDB中要求表必须有主键(MyIsam可以没有)，如果没有显示指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键类型为长整形
上图是 InnoDB 主索引(同时也是数据文件)的示意图，可以看到叶节点包含了完整的数据记录，这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集。
nnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说，InnoDB 的所有辅助索引都引用主键作为 data 域。
聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录，即回表查询。
三、索引优化时应注意的问题
前面我们说了很多索引的定义以及结构，接下来我们聊一聊索引优化时应该注意的问题。
索引分析我们就不得不借助explain命令了。explain是我们优化sql语句中常用的命令，它可以输出select执行的详细信息。
select_type:查询类型
SIMPLE ：表示查询语句不包含子查询或union
PRIMARY：表示此查询是最外层的查询
UNION：表示此查询是UNION的第二个或后续的查询
DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果
UNION RESULT：UNION的结果
SUBQUERY：SELECT子查询语句
DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。
type:表示存储引擎查询数据时采用的方式，通过它可以判断查询是全表扫描还是基于索引的部分扫描
ALL：表示全表扫描，性能最差
index：表示基于索引的全表扫描，先扫描索引再扫描全表数据
range：表示使用索引范围查询。使用>、>=、<、<=、in等等
ref：表示使用非唯一索引进行单值查询
eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一 行结果
const：表示使用主键或唯一索引做等值查询，常量查询。
NULL：表示不用访问表，速度最快
possible_keys：表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称
key:表示查询时真正使用到的索引，显示的是索引名称
rows:MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录
key_len:表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引
Extra:表示很多额外的信息，各种操作会在Extra提示相关信息
Using where:表示查询需要通过索引回表查询数据。
Using index:表示查询需要通过索引，索引就可以满足所需数据。
Using filesort:表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort 建议优化
Using temprorary:查询使用到了临时表，一般出现于去重、分组等操作
对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们发忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查询任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况下非要加索引不可，比如性别可能就只有两个值，建索引不仅没有优势，还会影响到更新速度，这被称为过度索引。
复合索引使用时遵循最左前缀原则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列， 那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效
3.2、LIKE查询
MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引
对MySQL来说，NULL是一个特殊的值，从概念上讲，NULL意味着“一个未知值”，它的处理方式与其他 值有些不同。比如：不能使用=，<，>这样的运算符，对NULL做算术运算的结果都是NULL，count时 不会包括NULL行等，NULL比空字符串需要更多的存储空间等
虽然MySQL可以在含有NULL的列上使用索引，但NULL和其他数据还是有区别的，不建议列上允许为 NULL。最好设置NOT NULL，并给一个默认值，比如0和 ‘’ 空字符串等，如果是datetime类型，也可以 设置系统当前时间或某个固定的特殊值，例如'1970-01-01 00:00:00'。
MySQL查询支持filesort和index两种方式的排序，filesort是先把结果查出，然后在缓存或磁盘进行排序 操作，效率较低。使用index是指利用索引自动实现排序，不需另做排序操作，效率会比较高
3.5、不要在列上进行运算
select * from user where YEAR（adddate）
3.6、不适用NOT IN
NOT IN 和操作都不会使用索引将进行全表扫描，NOT IN可以NOT EXISTS代替，
3.7、主键的设计
当我们设计表时，如果使用的是InnodDB,主键尽可能选择有序自增的值，这样在出入数据时可以减少BTree的分裂调整，如果不是有序自增的，可能会造成BTree频繁的分裂调整。
3.8、分页查询优化
问题：偏移量固定时，返回记录增多
在查询记录时，返回记录量低于100条，查询时间基本没有变化，差距不大。随着查询记录 量越大，所花费的时间也会越来越多
问题：返回记录增多，偏移量变大
在查询记录时，如果查询记录量相同，偏移量超过100后就开始随着偏移量增大，查询时间 急剧的增加。（这种分页查询机制，每次都会从数据库第一条记录开始扫描，越往后查询越慢，而 且查询的数据越多，也会拖慢总查询速度。）
优化 ： 1、先利用覆盖索引
select id from `user` limit 10000,100;
2、再利用子查询优化
select * from `user` where id>= (select id from user limit 10000,1) limit 100;
原因：使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化
好了关于索引暂时就说这些，
…………………………………分割线……………………………
不积跬步，无以至千里；不积小流，无以成江海。
关注我，每天分享一些小知识点。分享自己的小心得，包含但不限于初、中、高级面试题呦！！！
我都墨迹这么半天了 ，你不点关注，不点赞，不收藏，还不转发，你想干啥！！！
