MySQL学习笔记Ⅱ-索引
索引是什么
索引图解
数据库索引，是数据库管理系统（DBMS）中的一个排序的数据结构，以协助快速查询、更新数据库表中数据。
数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。而索引就是存放这些地址的数据结构。
索引类型
- Normal：非唯一索引，是最普通的索引，没有任何限制
- Unique：唯一索引要求键值不能重复。主键索引是一种特殊的唯一索引，它要求键值不能为空。主键索引使用primary key创建。
- Fulltext：全文索引。针对比较大的数据，几KB大小的数据情况，如果要解决like查询效率低的问题，可以创建全文索引。只有文本类型的字段才能创建全文索引，比如char、varchar、text。
- MyISAM和InnoDB都支持全文索引。
索引存储模型推演
二叉查找树（BST，Binary Search Tree）：左小右大。当插入的数据刚好是有序的时候，就会变成链表，斜树。不够平衡。
平衡二叉树（AVL Tree）：左右子树的深度差不超过1。需要通过左旋右旋，RL，LR进行调整确保平衡。每个节点都必须包含索引字段键值、数据磁盘地址（一行）、左右子树的引用。一个树的节点就是16K的大小，但实际使用不需要这么多，所以访问一个树节点进行一次IO的时候就浪费了大量的空间。
多路平衡查找树（B Tree）：分裂合并。节点关键字大于等于1，每个节点的分叉数永远比关键字树多1。每个节点的存储内容和AVL Tree差不多。节点的分裂和合并，其实就是InnoDB页（page）的分裂和合并。
B+树：
特点：
它的关键字的数量和路数是相等的。
B+Tree的根节点和枝节点中都不会存储数据，只有叶子结点才存储数据。找到关键字并不会返回，而是将继续到叶子结点。
B+树的每个叶子结点增加了一个指向相邻节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成一个有序的链表结构。
优势：
- 是B树的变种，B树能解决的它都能解决。（每个节点存储更多的关键字，路数更多）
- 扫库、扫表的能力更强（对表进行全表扫描，只需要遍历叶子结点就可以了，不需要遍历整棵树）
- B+树对磁盘读写能力相对于B树来说更强（根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多）
- 排序能力更强（叶子节点上有下一数据区的指针，数据形成了链表）
- 效率更稳定（B+树永远是在叶子结点上拿到数据，所以IO次数是稳定的）
索引的实现方法：
- B/B+树：适合用来查找某一范围内的数据，支持数据排序。MyISAM表数据文件和索引文件是分离的，索引文件仅保存数据记录的磁盘地址；InnoDB表数据文件本身就是主索引，叶节点data域保存了完整的数据记录。
- Hash：仅支持”=”,”IN”和”<=>”精确查询，不范围查询，不支持排序，不支持排序，只有Memory引擎支持显式的Hash索引，但是它的Hash是nonunique的，冲突太多时也会影响查找性能。
- R-Tree：仅支持geometry数据类型。
MyISAM
MyISAM有两个文件：
.MYD：D代表Data，是数据文件，存放数据记录，所有表数据。
.MYI：I表示Index，是索引文件，存放索引。
在MyISAM中数据和索引是两个独立的文件。MyISAM的B+树中叶子结点存储的是数据文件对应的磁盘地址。所以从.MYI中找到键值之后，会到数据文件.MYD中获取相应的数据记录。
InnoDB
在InnoDB中，是以主键为索引来组织数据的存储的，所以索引文件和数据文件是同一个文件，都在.ibd文件里面。在InnoDB的主键索引的叶子节点上，直接存储了数据。
聚集索引（聚簇索引）
索引键值的逻辑顺序和表数据行的物理存储顺序是一致的。在InnoDB中，它组织数据的方式叫做（聚集）索引组织表(clustered index organize table)，所以主键索引是聚集索引，非主键索引都是非聚集索引。
辅助索引
非主键索引，非聚集索引。辅助索引存储的是辅助索引和主键值，如果使用辅助索引查询，会根据主键值在主键索引中查询，最终取得数据。
无主键情形
未指定主键时，InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引，如果没有这样的唯一索引，啧InnoDB会选择内置6字节长的ROWID作为隐藏的聚集索引，会随着行记录的写入而逐渐递增。
索引使用原则
列的离散度
count(distinct(column_name)):count(*)，列的全部不同值和所有数据行的比例。数据行数相同的情况下，分子越大列的离散度就越高。如果列的重复值越多，离散度就越低。离散度越高需要扫描的行数就越少，查询速度就越快。
联合索引最左匹配
多条件查询时会建立联合索引，单列索引可看做是特殊的联合索引。
alter table user_innodb add index comidx_name_phone(name,phone),这就是给user表建立了一个name和phone的联合索引。联合索引在B+Tree中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的(name在左边，phone在右边)。name是有序的，phone是无序的。当name相等的时候phone才是有序的。在使用wher name='name' and phone='131***'去查询数据的时候，会优先比较name来确定下一步应该搜索的方向，在name相同的时候再比较phone。但是如果查询条件中没有name，就不知道第一步应查询哪个节点，因为建立搜索树的时候name是第一个比较因子，所以用不到索引。对于联合索引（a,b）只有在使用右边的b字段时，无法使用索引，会全表扫描，其他情况都会使用联合索引。
创建联合索引：
create index idx_name_phone on user_innodb(name,phone)create index idx_a_b_c on user_innodb(a,b,c)，想当于创建三个索引：index(a),index(a,b),index(a,b,c)即最左边的是必要条件。
覆盖索引
回表：非主键索引，先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫做回表。
例如：select * from user_innodb where name='qqs'
在辅助索引里面，不管是单列索引还是联合索引，如果select的数据列只用从索引中就能取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。
1 | -- 创建联合索引 |
假若一个主键索引，其他3个字段建立辅助索引，select *也属于覆盖索引。
索引的创建与使用
索引的创建
- 在用于where判断order排序和join的（on）字段上创建索引。
- 索引的个数不要过多–浪费空间，更新变慢。
- 过长的字段，建立前缀索引。
- 区分度低的字段，例如性别，不要建索引。离散度太低，导致扫描行数太多。
- 频繁更新的值，不要作为主键或者索引。 页分裂。
- 随机无序的值，不建议作为主键索引，例如身份证，UUID。无序，分裂。
- 组合索引把散列性高（区分度高）的值放在前面。
- 创建复合索引，而不是修改单列索引。
什么时候用不到索引
索引列上使用函数（replace\substr\concat\sum count avg）、表达式（+-*/）。
字符串不加引号，出现隐式转换。
like条件中前面带%，where条件中like abc%，like %23432%，like %777都用不到索引，因为过滤的开销太大，这时候可以用全文索引。
负向查询 not like 不能；!=, <>,not in在某些情况下可以：
1
2
3explain select * from employees where emp_no not in (1);
explain select * from employees where emp_no <> 1
-- 跟数据库版本、数据量、数据选择度都有关系其实，用不用索引，最终都是优化器说了算。
Optimizer，是基于cost开销（cost base optimizer），不是基于规则（rule-based optimizer），也不是基于语义。怎么开销小就怎么来。
使用索引有基本原则，但没有具体规则