mysql 索引

** mysql 的索引的构成以及优化**

B+树.png

（B+树 网上来，但并不是索引的实现，索引是用双向链表）

InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。聚集索引就是按照每张表的主键构造一棵B+树，叶子节点存放的是整张表的行记录数据。这种叶子节点也可以称为数据页。聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。每个数据页都是通过一个双向链表来链接的。

数据页只能按照一棵b+树来排序。

在多数情况下，查询优化器倾向于采用聚集索引，因为该叶子节点中包含了数据。定义了数据的逻辑顺序，聚集索引能很快的对某一范围值进行查询。查询优化器能够快速发现某一段范围的数据页需要扫描。

如果聚集索引必须按照特定顺序存放物理记录，则维护成本显得非常高。这也决定了聚集索引的存储并不是物理上连续的，而是逻辑连续的。页通过双向链表链接，页按照主键的顺序排序的。

因为是双向链表的情况，用户可以快速的找到最好的一个数据页。查找某一范围的数据，只要通过节点的上层中间节点就可以得到页的范围。

辅助索引也叫非聚集索引，叶子节点并不包括含行数据，而是存了一个书签，告诉我们到哪里去找相应行数据的聚集索引键。

如果在一棵高度为3的辅助索引中查找数据，需要遍历3次辅助索引，还要根据书签查询聚集索引的地址3次，一共6次io操作，从而得到最终的一个数据页。

mysql 可以通过SHOW Index 来查看表中的索引的信息。查询结果中有一个非常关键的值Cardinality ，这个是索引中唯一值的估计值如果非常小，就要考虑是否删除该索引。

覆盖索引，就是可以从辅助索引中自己得到查询的记录，而不去查询聚集索引。覆盖索引本身并不会存储行数据，自然比聚集索引要消耗小。

---

MySQL的子查询一直是以性能差著称的，针对select子查询，
select * from country where city='hangzhou' and country.code in(select * ......)
其实这首先会把符合的数据过了，对每个数据都与内表city进行一次select查询，性能低下。
其实我们可以改成
select .....as a join(select .....) as b on a.code =b.country

---

** 索引优化**
like'%xxx%' 是不会用到索引的，like 'xxx%'可以
但如果select id 这个id是聚集索引主键，就用的了覆盖索引

limit 分页
select * from text order by id limit 99999,10;
优化：
select * from text where id >=100000 order by id limit 10;

count（辅助索引）<count（*）
count 是很耗时的，对于innDB内部是没有计数器的，其实可以加一个列计数。

---

 -索引适用场景：
    1. 经常order by 、group by distinct 后面的字段
    2. 在union等集合操作的结果集字段
    3. 经常查询的字段
    4. 经常用在表连接上的字段
    5. 很少被更新的字段
    6.均匀分布的不用索引
- 索引失效场景：
    1. 如果条件中有or,即是其中有条件带索引也不生效
    2. like 查询是以%开头
    3. 如果类型是字符串，则一定要在条件中使用引号引起来，否则不会使用索引
    4. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引（当取出的数据量超过数据的20%）
    5.字段用了函数是不能用索引的
    此外，查看索引的使用情况
    show status like ‘Handler_read%';
    大家可以注意：
    handler_read_key:这个值越高越好，越高表示使用索引查询到的次数
    handler_read_rnd_next:这个值越高，说明查询低效