常用命令
explain select * from tb order by id limit 10\G;
alter table add c int not null;
alter table add key idx_c (c);
show index from t\G;
一 InnoDB存储引擎索引概述
- InnoDB 支持 B+树索引、hash索引、全文索引，其中hash索引是自适应的，不能人工干预。
- B+树索引并不是根据键值找到一个具体的行，而是找到相应数据行所在的页，然后把页读到内存中，再在内存中查找相应数据行。
二、数据结构与算法
- 二分查找
- 二叉查找树
- 平衡二叉树
- B+树（叶子节点存放具体数据，非页节点存放指针）
- B+树的插入操作可能导致页的拆分（磁盘操作）操作（所以主键应该尽量为递增值）
- B+树中的删除操作可能造成页的合并
三、B+树索引
- B+树索引在数据库中的实现具有高扇出的特点，其高度一般为2-4层，索引查找一行记录的时间最多为2到4次IO的时间（0.02~0.04秒）。
- 分为聚集索引和辅助索引（区别为 叶子节点是否存放一整行信息）
3.1 聚集索引
- InnoDB为索引组织表，表中数据按照主键顺序存放
- 聚集索引按照每张表的主键构造一颗B+树，同时叶子节点中存放的是整张表的行记录数据。叶子节点数据页，其余索引页。
- 数据页之间通过双向链表链接，可以更快的全表查询。
- 聚集索引对于主键的排序查找（order by && limit,这里避免了filesort）和范围查找(where)速度非常快。
- 数据页存放的是完整的行记录，而索引页存放的是键值和指向数据页的偏移量。
3.2辅助索引
- 叶子节点不包含整行记录的全部列（只包含索引列和对应主键值）。
- InnoDB会遍历辅助索引并通过页级别的指针获得指向主键索引的主键，然后通过主键索引找到完整的行记录。
3.3 B+树索引的分裂
- B+树索引页的分裂并不总是从页的中间记录开始（这种是索引完全随机插入的情况），会根据Page Header判断
- 如果索引是递增插入的，那么分裂点一般为插入记录本身。
3.4索引管理
- alter table 管理索引
alter table tb_name
| add {index|key} [index_name]
[index_type] (index_col_name,...)
alter table tb_name
drop primary key |
drop {index|key} index_name
- create/drop 管理索引
create [unique] index index_name
[index_type] on
tb_name(col_name,...)
drop index index_name on tb_name
- 可以只索引一个列的开头部分数据
alter table t add key idx_b (b(100)) - 查看表中所有索引信息
show index from t\G - Cardinality值：索引中唯一值的数目的
估计值,可以通过 analyze table命令更新（以便优化器更好的工作）。 - Fast Index Creation
老版本对一张大表进行索引的添加和删除，会需要很长时间（临时表，原表数据到临时表，临时表替换原表）。
FIC对创建索引的表加S锁（写事务阻塞），不需要重建表，速度提高很多。 - online ddl
对FIC的改进（FIC能够避免创建临时表，但是会阻塞DML操作），其在创建索引的同时，还运行insert、update、delete这类dml操作。
对online ddl的支持，是将执行insert、update、delete这类的操作的日志写到一个缓存中，待完成索引的创建后再应用到表上，以完成事务的一致性。缓存的大小（innodb_online_alter_log_max_size）
语法如下
alter table tb_name
add {index|key} index_name
[index_type] (col_name,...) [index_option]...
algorithm [=] {default|inplace|copy}
lock [=] {default|none|shared|exclusive}
algorithm算法
copy为创建临时表的方式，inplace不创建临时表，默认值取决于old_alter_table变量（为off时，是inplace方式）。
lock选项
none: 不添加任何锁
shared:添加S锁，阻塞写操作（同 FIC）
exclusive: 对目标表加X锁（读写事务都不允许）
四、Cardinality值
- 代表索引列中唯一值的个数（估计值），会被用于优化器。
- cardinality的值可以表示一个索引的选择性.
- cardinality/n_rows_in_table 应该尽可能接近1（高选择性），当这个值远小于1时，索引失去意义（性能可能会更糟糕，取决于优化器）。
- analyze table、 show table status、show index等操作会更新 cardinality值，所以以上操作会比较慢，不应在高峰期运行。
五、B+树索引的使用
5.1 联合索引
- 最左前缀匹配原则
- 对第二个键值已经进行了排序，如key(a,b,c)和 条件where a=1 and b=2 order by c(这可以避免filesort),而where a = 1 order by c 不行。
5.2 覆盖索引
- 注意：辅助索引中包含主键列的值，所以对于一些统计查询可能直接使用辅助索引，而不使用聚集索引（以获得更少的IO次数）
- 覆盖索引为包含查询所需的所有列的辅助索引，如key(a,b,c) 和 条件 (select [主键列,] a,b,c from t)就直接使用覆盖索引，而不需要去查询表（聚集索引）;
- 使用覆盖索引的标志：explain 的extra 列有 using index.（不去聚集索引走一趟就可以获得所需数据）
- 统计查询可能会使用一些出乎意料的索引（优化器选择）。
5.3优化器不使用索引的情况
- 核心是顺序读 优于 离散读，查找的数据量应该能可能少。
假设 key (b) 和条件{select * from t where b<10000 and b > 10},这里虽然在b列建立了索引，但是优化器可能会全表查询。这里使用索引根据b列找到 主键值，再到聚集索引查找是一个离散读的过程，可能还不如全表查（顺序读）。
5.4索引提示
- use index 向优化器建议,但可能不被采用。
select * from tb use index(index_name) where ... - force index
select * from tb force index(index_name) where ...
5.5 muti_range read 优化
- MRR适用于range、ref、eq_ref类型的查询
- MRR使数据较为顺序。在使用辅助索引时，根据查询结果按照主键排序，然后按照主键的顺序来访问聚集索引（离散读 变为 顺序读，减少了io的次数，减少了缓冲池中页被替换的次数）
- MRR 体现为 extra列的 using MRR。
- MRR还可以把某些范围查询，拆分为键值对（多个条件），这样可以直接过滤一些不符合查询的数据（也减少了io次数）。
- 是否启用此优化（查看optimizer_switch变量）。
set @@optimizer_switch='mrr=on,mrr_cost_based=off'
5.6 index condition 优化
- ICP优化是在取出索引时，判断是否可以进行where条件的过滤。
- using index condition.
六、自适应哈希
- 缓冲池中的页 可以理解为都存放在一个哈希表中。（链表解决冲突）
- 对于键值方式的查询，可以很快的定位到相应的缓冲池中的页，然后读取数据。（范围查找不支持）
-
show engine innodb status\G查看自适应hash的使用情况
七、全文检索
需要时再看吧。
