mysql 索引优化和算法

**

索引注意事项

**

1. 根据区分度，更新频繁判断是否要创建索引。
   区分度：count(distinct(列))/count(*)
   如果区分度高于80%，则可以建立索引，且区分度越高，如果创建联合索引，应该放在左侧。
   如果更新过于频繁的字段不适合建立索引，因为更新会变更b+树，会大大降低索引性能。
   如果一个列的前一定长度的字节，便能做到全列索引，可以使用
   create index ix_test on aaa(col(20))创建短索引，不过短索引order by和group by，也不能用于覆盖索引。
2. 负向条件查询不能命中索引。
   如：!=，<>，not in，not exist，not like
3. like的前导模糊查询不能命中索引
4. 联合索引的最左前缀原则
5. 不能使用索引范围条件右边的列
   索引中最多只能用一个范围列
   如：select * from table where field1 < 100 and field2 = 3 and field3 between 1000 and 2000;
   则 field2和field3无法命中索引。
6. is null,is not null无法命中索引。
7. 索引中不会包含有null的列，所以创建列时，尽量使用not null.
8. 强制类型转换无法命中索引，即数据类型必须一致
9. 不要在索引列做任何操作。
10. order by,group by 可以考虑使用索引的有序性。
    无效场景：
    当索引列使用了短索引；当使用了索引范围条件查询时。
11. 利用覆盖索引来进行查询操作，避免回表，减少select *
    覆盖索引：查询的列与创建索引的列个数相同，字段相同
    被查询的列能直接从索引中取得，不需要再通过定位符，去获取
12. union，in，or都能命中索引，建议使用in

select * from table1 where status =1
union
select * from table1 where status =2;

select * from table1 where status =1 or status =2;
select * from table1 where status in (1,2);

13. 利用延迟关联或子查询优化超多分页场景

select * from table1 a,(select id from table1 limit 0,1000) b on a.id = b.id;

**

索引算法

**
MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址

innodb虽然也是以b+tree实现索引结构，但还是有所区别，它的关键字和数据在叶节点一起存储，data域存储相应记录主键的值而不是地址。

innodb主要有两大索引类型，聚集索引和普通索引。
聚集索引的b+tree的叶子节点存储的是行记录；普通索引的b+tree叶子节点存储的是记录主键。所以普通索引的查询过程一般需要两次查询索引树。

先通过普通索引定位到主键值，再通过聚集索引定位到行记录，这就是回表。
以下sql语句虽然增加了复杂度，但b表的查询就不需要进行回表操作。

select * from table1 a,(select id from table1 where name = 'ddd' limit 0,1000) b on a.id = b.id;

而覆盖索引，由于普通索引叶子节点存储的是列值和主键值，如果我们要查询的字段已经被索引包含或覆盖，则不需要再进行回表操作。

为什么选择b+tree而不选择b-tree

1. 作为关系型数据库，更多是区间查询，而b+tree的所有结点会在叶子节点中，并形成一个增序链表，这对于区间查询是非常高效的。
2. 由于从磁盘读取到内存的数据量是有限的，而b-tree的节点都有数据，不像b+tree的非叶子节点主要作为索引目录，只存储关键字，可以每次读取更多的数据量，减少IO操作。