mysql为什么使用B+ Tree索引,不使用B- Tree索引?
索引顺序如何生效?
什么是覆盖索引?
order by 也能用到索引?
何时索引失效?
如何设计索引,全方位理解mysql索引的特性.
1. Sql执行顺序
1. FROM 子句 组装来自不同数据源的数据
2. WHERE 子句 基于指定的条件对记录进行筛选
3. GROUP BY 子句 将数据划分为多个分组
4. 使用聚合函数进行计算
5. 使用HAVING子句筛选分组
6. 计算所有的表达式
7. 使用ORDER BY对结果集进行排序
8. select 获取相应列
9. limit截取结果集
1.2 相应Case:
需求：查询今日增长数据(根据video_id去重)
错误：select * from tb where date(created_at) = current_date() group by video_id
正确：select * from (select * from tb group by video_id ) as tb1 where tb1.date(created_at) = current_date()
错误原因：group by 操作在where后执行。所以，第一个语句 是查询今日 去重后数据，去重是在今日抓取的数据中去重。而且我们的需求是，对表中所有数据去重，然后获取今日新增长的的数据
2. 索引基础
2.1 BTree索引
mysql默认存储引擎innodb只显式支持B树索引，对于频繁访问的表，innodb会透明建立自适应hash索引， 即在B树索引基础上建立hash索引，可以显著提高查找效率，对于客户端是透明的，不可控制的，隐式的。
m阶 B树的特性:
- 树中每个节点至多包含m棵子树
- 若根节点不是叶子节点，则至少包含两颗子树
- 除根以外的所有非终点节点至少有 (m/2)向上取整棵子树
支持范围查询，前缀匹配查询，等值查询，可以避免排序，例如order by index相关的列，排序会非常快，因为该列本身就是有序存储的，查找时间复杂度 log m N(m为底,N的对数，N为总记录数)
2.2 Hash索引特性
只支持包括 “=” "in "在内的等值查询，不支持范围，前缀匹配查询
Hash索引是通过hash函数将，键值直接映射为物理存储地址，使时间复杂度降低到O(1).本身存储是无序的，所以不能通过hash索引避免排序
2.3 Mysql聚簇索引
B-树和B+树的区别在于B+树所有键值全部保存在叶子节点，而B-树则不然，B-树的键值根据树的结构分布在整个树上。而Mysql为什么要采用B+树索引呢？
1.遍历方便.B+树可以将键值保存在（线性表【数组或链表】）中，遍历线性表比索引树要快，因为保存在线性表中数据存储更加密集，B-Tree分散的 存储会导致更多的随机I/O,对于磁盘访问，随机I/O是比顺序I/O慢很多的，因为随机I/O需要额外的磁头寻道操作。顺序I/O有效减少寻道的次数
2.插入更新索引树时可以避免移动节点.
3.遍历任何节点的时间复杂度相同，即访问路径总是从根节点到叶子节点.相比B-树,访问时间略长.所以某些高频访问的搜索采用B-树，即访问频率越高 使其距离根节点越近。
4.（也许是最重要的）范围查找方便。对于[A,B]区间的范围查找，B-树索引可以直接找到A,B对应的线性表中节点，只需要返回区间的所有节点 即为目标结果。而B-树则稍显麻烦需要继续遍历索引树。
聚簇索引：
将表中一条记录存储在索引的叶子节点中（也可能保存记录的物理地址[可能是磁盘或者扇区号也可能是文件名及对应偏移量]的指针，如果在内存中即为内存地址）。一般情况下mysql中使用主键 做聚簇索引
- 一个表只能有一个聚簇索引。(一条记录物理存储只有一份)
- 非聚簇索引中叶子节点的记录中需要保存主键，如需访问记录中其他部分还需要，通过主键回表查询。即两次索引查找 ？有人疑问非聚簇索引中为什么不保存记录项的物理地址呢？当然可以记录物理地址，但是主键索引更新操作带来的索引分裂合并会改变其物理地址，这样索引的维护代价比较大，而即使回表查询，主键查找速度一般较快，影响不大。另外也可以通过覆盖索引【即索引项覆盖了select中的项】避免回表查询
3.访问聚簇索引速度应该保证足够快，主键不宜选择过大存储需求的字段，例如UUID，另外非聚簇索引需要额外保存主键，主键太长存储需求较大。 也不宜选择字符串：一.字符串比较速度较数字慢，二.字符串插入时更加无序，索引树分裂合并相对更加频繁，出现更多磁盘碎片 。当有字符串和数字都能满足代理主键【该主键与业务无关只是添加一列主键保证记录唯一性】需求时，应当优先选择数字 做主键，但是如果逻辑主键【业务中有作为主键的列，也可选为主键，即为逻辑主键】是字符串类型，那也应该选择其作为主键，因为字符串相比数字 性能差别不是很大。
3. 索引常见优化方法
3.1 不能使用索引,不建议使用索引等常见误区
1.数据类型为Text，Blob等大对象不能建立索引，也不适合建立索引,另外字段太长的字段不适合建立索引。例如超长字符串。会使索引树过大，mysql可能无法将其放入内存，访问索引会带来过多的磁盘I/O。效率低下
2.查询表达式索引项上有函数.例如date(created_at) = 'XXXX'等.字符处理等。mysql将无法使用相应索引【查询表达式相应列不能使用函数，但是右边的值可以使用函数，例如 created_at < now() and created_at > current_date()
3.一次查询（简单查询，子查询不算）只能使用一个索引， 例如 where column1 = xxx and column2 = xxx order by column3 如果column1 ,column2 列各有一个索引，那么只能使用其中之一的索引， 具体使用哪个索引，要看mysql的统计信息，mysql执行计划中包括索引的选择，具体的选择要看哪个的索引选择率更高【唯一值/总记录数=选择率，0<选择率<=1 选择率越大，说明给定一个值可以过滤更多的行，即过滤性更高】。但是mysql的统计信息不是精确实时的。所以可能存在使用“错误的索引”的情况，这时可以强制使用某个索引
select * from tb1 as t use index(index_name) ......
但是强烈不推荐使用这种方式，可以将其作为临时方案使用，应该首先考虑优化索引设计，例如，上述Case就应该建立（column1,column2,column3）或(column2,column1,column3) 联合索引
4.where后的查询表达式顺序不能决定使用哪个索引.如column1 =xxx and column2 = xxx， 但并不代表优先使用column1 在前，column2在后的联合索引。使用哪个索引由相应索引项的选择率决定，最终判定标准是：扫描最少的行.使用索引过滤尽可能多的行。然后使用where中其他条件对 索引过滤后的结果集 一行行地判断 完成where条件过滤。
5.修改过于频繁的列使用索引要慎重.1s几十次的修改就要注意了,过于频繁的更新对于索引负担太重，磁盘负载过重，另外更新操作可能会锁住相关记录，有死锁和事务超时可能。但是该使就使。这些问题可以通过分区分表或者缓存解决
6.选择率低的列不适合建立索引。如果索引项对应cardinality较小，例如小于10,那么使用索引时就需要考虑是否有必要。因为访问索引的代价可能比全表扫描还要高。索引需要访问索引文件，然后访问叶子节点，拿到主键回表查询，如果结果集比较大，这个代价极可能大于全表扫描【全表扫描是顺序I/O，索引访问会涉及更多随机I/O，随机I/O比顺序I/O慢多了】。业务中常见的状态列，在设计之初，这一列的cardinality基数【唯一值的个数】即是固定的，随着记录数增加，选择率会越来越低，索引效率反而越来越低。可以考虑不建索引，或者将其作为联合索引的第一项
7.Mysql中对于唯一性检查即声明unique的列，自动建立唯一性索引，不需要再额外建立索引
8.不应该对where中每一个查询条件都建立上索引，mysql只会使用其中一个索引，过多的索引带来冗余，导致一些索引被“浪费”，同时mysql在生成执行计划时，需要考虑更多的索引，给查询优化带来更多工作，过多的索引还会给更新操作带来更沉重的索引维护代价。应该简化索引设计。同时利用联合索引满足多项条件的查询
9.Order By ,Group By 可以利用索引避免排序。但是 存在where 语句下 只能使用where 查询中使用的索引，例如where中使用了(A,B，C,D)联合索引的A,B项，如果order by ,或者group by中存在C，或者(C，D)即可使用联合索引，如果where中没有使用索引，那么即使order by,group by列中有索引也不能使用。即优先根据where 查询使用索引，然后根据where中使用的索引再决定，order by,group by是否可以 使用到索引
10.当数据量达到千万级别以上，索引本身就很大，无法装入内存，访问索引带来的磁盘随机I/O 开销很大，索引性能下降较快，当并发量不大情况下，建立分区表可有效提高速度，因为分区表的索引结构是互相独立的，可单独装入内存，减少磁盘访问。
11.更新删除时指定索引列【事务特性，及隔离级别不熟悉同学请参考 延伸阅读4.1】，mysql在默认的事物隔离级别是序列化解决了幻读，并且通过间隙锁，多并发版本读提高了并发访问性能，幻读是指：一个事务中，当用户查询一个范围中的结果时，另一个事务执行了相应的插入删除操作，导致两次查询结果不同，少了或多了一些行，就像幻象一样。mysql 解决幻读有两种方案：一.对于查询select操作只是针对本事务开启时刻的“镜像”查询。例如本事务开启后，其他事务插入删除了相关数据并提交，本事务是无法察觉的。实现方式为 版本控制。二.更新删除【包括 select ………… for update 】等写操作涉及到范围更新时，如果查询条件where中存在索引，即锁住索引树的相关键值段例如 更新 id主键索引在 1-100的数据，那么它会锁住 1- 100 这些记录的 id 索引，其他事务更新这个范围数据时，会进入锁等待，直到拥有锁的事务，或者等待超时。如果查询条件中不能使用索引，mysql为了实现序列化的隔离级别，会对全表加锁，任何写操作不能进行。当并发写操作多，事务时间长时，会出现较多锁等待及等待超时事务。需要通过添加索引，及减小事务粒度或者降低mysql默认隔离级别方式解决此类问题。
3.2 索引设计的几个“原则”
- 索引的设计应该与业务需求息息相关，没有完美的索引设计，只有满足需求的索引设计，项目前期设计的索引不可能完美的满足后期的需求。应随时根据业务合理取舍。
- 索引设计应该优先照顾查询最为频繁，或业务优先级高，与用户相关的查询。如果我们可以忍受，那么可以不建索引
3.使用短索引,索引长度不宜过大，利用B Tree的特性使用最左匹配查找高效利用索引第一列、对选择率高的列索引、使用覆盖索引避免回表查询 4.及时删除不再使用的索引，例如发现（A,B）不满足需求，新加一项（A,B,C）即可删除旧索引（Ａ，Ｂ）
3.3 联合索引的顺序问题
1.联合索引设计时，索引顺序是很重要的。当联合索引中，每一列的查询频率都相差不多时，可以优先将选择率最高的列作为联合索引第一列，这样第一列即可过滤更多列，效率更高。由于联合索引第一列可以单独使用，例如联合索引（column1,column2,column3,column4）即可满足 where column1 =xxx 也可满足 where column1 = xxx and column2 = xxx and column3 =xxx and column4 = xxx的需求，这样不需要为第一列的独立查询额外建立单列索引
2.使用部分前缀索引键，按照联合索引声明顺序查询。例如索引(A,B,C) 只能匹配 where A = x xx ，where A = XXX and B = xxx ，where A = xxx and B = xxx and C = xxx ，不能跳过前一列，匹配后一列. 例如 where A = xxx and c = xxx 这时虽然可能也使用该索引，但是只能使用一部分，匹配A列，而B,C列不能匹配。
3.前缀匹配，与范围匹配。 BTree索引可以使用前缀匹配，例如 where A like "xxx%" ,使用前缀索引后，就不能使用前缀列的后续索引列。
4.group by,order by 本质是对where查询出的结果集进行排序操作，当待排序列匹配 where 中索引顺序时才可避免排序，直接通过索引即可返回有序结果集，例如我们需要将查询结果按照评分排名，那么就可以考虑将rank列放在联合索引的最后一列。(X, …… ,rank)。当查询结果比较大时，可以考虑这样设计
5.limit 分页查询 .limit 使用时必须排序否则可能出现不同页返回重复数据的风险。limit 返回某一位置的给定偏移量的记录，但是它的顺序依赖于存储位置顺序，索引顺序，所以分页时不同页会有出现重复数据的风险。limit 操作前需要添加order by 进行排序。由于访问非聚簇索引时，mysql有一个优化操作，当访问非聚簇索引，回表查询时，mysql 会对主键进行排序，目的是：聚簇索引是按顺序存储记录，对主键排序后，访问聚簇索引可以更加顺序的访问磁盘，减少随机I/O，提高速度，所以当分页没有特别指定的列时，指定主键排序即可，另外不需要在联合索引最后一列添加主键，因为它本身包含主键 【非聚簇索引不存储完整记录，通过访问主键索引找到完整记录 】。
3.4 索引设计优化常见小技巧
以上已经列出较多的误区及注意事项，理解即可，更重要的是根据业务对索引取舍的经验。更多的设计技巧希望同学们在实践中自己总结并分享出来。
1.数据量较大的表（千万以上）考虑是否适合建立分区。
2.对于较长字符串例如200以上，可以考虑单独增加索引列，对其整体hash或者去其中一部分hash后存入其他一列，这
样将字符串查找变成数字查找，同时索引长度大大减小，可有效提高索引速度，降低索引大小。但是需要考虑hash函数
的“碰撞”问题，选择适合的hash函数。
３．使用explain命令查看sql 的执行计划，请参考延伸阅读４.３
4.延伸阅读
4.1 数据库事务及隔离级别概要
数据库事务的四个标准特征（ACID)：
原子性：一个事务必须被一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚 ，对于一个事务来说，不可能只执行其中一部分操作，
一致性：数据库总是从一个一致性的状态转换到另外一个一致性的状态。指关联数据之间的逻辑关系是否正确和完整， 一致性处理数据库中对所有语义约束的保护。假如数据库的状态满足所有的完整性约束,就说该数据库是一致的。
隔离性：通常一个事务所做的修改在最终提交以前，对其他事务是不可见的，多个事务并发访问时，事务之间是隔离的 ，一个事务不应该影响其它事务运行效果。这指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都 有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务查看数据更新时，数据 所处的状态要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据。
持久性：意味着当系统或介质发生故障时，确保已提交事务的更新不能丢失。即一旦一个事务提交，DBMS保证它对数据 库中数据的改变应该是永久性的，耐得住任何系统故障。持久性通过数据库备份和恢复来保证。
对于事务的隔离性而言有四种隔离级别：
Read Uncommitted（读取未提交内容）：在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。
Read Committed（读取提交内容） 这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。由于正在读取的数据只获得了读取锁，读完之后就解锁，不管当前事务有没有结束，这样就容许其他事务修改本事务正在读取的数据。导致不可重复读。 解决不可重复读的问题就要求，对正在读取的若干行加上行级锁。要求在本次事务中不可修改这些行。解决ReadCommited更侧重数据行不可更新。
Repeatable Read（可重读） 这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。 解决幻读的方案应该是在表上加锁，幻读出现的场景主要是插入操作，由于插入操作使得事务不同的查询中出现不同的结果。
Serializable（可串行化） 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。
隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。（乐观锁通过版本号控制是否存在不可重复读情况，如果不存在则提交，否则事务回滚。悲观锁是通过数据库系统本身在内部加锁，锁住要更新的数据，不允许其他事务修改，但是会消耗大量的性能）
4.2 explain命令讲解
Explain 的使用
ID：MySQL Query Optimizer选定的执行计划中查询的序列号。
Select_type：所使用的查询类型，主要有以下这几种查询类型。
DEPENDENT SUBQUERY：子查询内层的第一个SELECT，依赖于外部查询的结果集。
DEPENDENT UNION：子查询中的UNION，且为UNION中从第二个SELECT开始的后面所有SELECT，同样依赖于外部查询的结果集。
PRIMARY：子查询中的最外层查询，注意并不是主键查询。
SIMPLE：除子查询或UNION之外的其他查询。
SUBQUERY：子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集。
UNCACHEABLE SUBQUERY：结果集无法缓存的子查询。
UNION：UNION语句中第二个SELECT开始后面的所有SELECT，第一个SELECT为PRIMARY。
UNION RESULT：UNION 中的合并结果。
Table：显示这一步所访问的数据库中的表的名称。
Type：告诉我们对表使用的访问方式，主要包含如下集中类型。
all：全表扫描。
const：读常量，最多只会有一条记录匹配，由于是常量，实际上只须要读一次。
eq_ref：最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问。
fulltext：进行全文索引检索。
index：全索引扫描。
index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行合并（merge），再读取表数据。
index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引。
rang：索引范围扫描。
ref：Join语句中被驱动表索引引用的查询。
ref_or_null：与ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。
system：系统表，表中只有一行数据；
unique_subquery：子查询中的返回结果字段组合是主键或唯一约束。
Possible_keys：该查询可以利用的索引。如果没有任何索引可以使用，就会显示成null，这项内容对优化索引时的调整非常重要。
Key：MySQL Query Optimizer 从 possible_keys 中所选择使用的索引。
Key_len：被选中使用索引的索引键长度。
Ref：列出是通过常量（const），还是某个表的某个字段（如果是join）来过滤（通过key）的。
Rows：MySQL Query Optimizer 通过系统收集的统计信息估算出来的结果集记录条数。
Extra：查询中每一步实现的额外细节信息，主要会是以下内容。
Distinct：查找distinct 值，当mysql找到了第一条匹配的结果时，将停止该值的查询，转为后面其他值查询。
Full scan on NULL key：子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用。
Range checked for each record (index map: N)：通过 MySQL 官方手册的描述，当 MySQL Query Optimizer 没有发现好的可以使用的索引时，如果发现前面表的列值已知，部分索引可以使用。对前面表的每个行组合，MySQL检查是否可以使用range或 index_merge访问方法来索取行。
SELECT tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段时，MySQL Query Optimizer 会通过索引直接一次定位到所需的数据行完成整个查询。当然，前提是在 Query 中不能有 GROUP BY 操作。如使用MIN()或MAX()的时候。
Using filesort：当Query 中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。
Using index：所需数据只需在 Index 即可全部获得，不须要再到表中取数据。
Using index for group-by：数据访问和 Using index 一样，所需数据只须要读取索引，当Query 中使用GROUP BY或DISTINCT 子句时，如果分组字段也在索引中，Extra中的信息就会是 Using index for group-by。
Using temporary：当 MySQL 在某些操作中必须使用临时表时，在 Extra 信息中就会出现Using temporary 。主要常见于 GROUP BY 和 ORDER BY 等操作中。
Using where：如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where 信息。
Using where with pushed condition：这是一个仅仅在 NDBCluster存储引擎中才会出现的信息，而且还须要通过打开 Condition Pushdown 优化功能才可能被使用。控制参数为 engine_condition_pushdown 。
Impossible WHERE noticed after reading const tables：MySQL Query Optimizer 通过收集到的统计信息判断出不可能存在结果。
No tables：Query 语句中使用 FROM DUAL或不包含任何 FROM子句。
Not exists：在某些左连接中，MySQL Query Optimizer通过改变原有 Query 的组成而使用的优化方法，可以部分减少数据访问次数。
