MySQL索引
tags: mysql@烩面
关于SQL索引
SQL
索引
索引是帮助SQL高效获取数据的有序数据结构
利用索引可以提高数据检索的效率,降低io成本
通过索引对数据进行排序,减少cpu消耗
索引占一部分空间
索引降低了更新表的速度，对表进行插入删除的时候成本提高
索引类型
- B+树索引
- Hash索引：底层用哈希表实现，只支持精确匹配不支持范围查询
- R-Tree空间索引
- Full-text全文索引
索引结构
InnoDB MYISAM Memory
Q：为什么用B+树不用二叉树，红黑树，B树
A：顺序查找会形成单向链表，性能大大降低，每个节点最多只能存两个子节点，大数据量情况下检索效率低，红黑树在大数据量情况下检索效率也没有提高。而B树无论是叶子节点还是非叶子节点都要保存数据和指针，这样导致一层中能够保存的建值变少，指针跟着减少，要保存大量数据只能增加树的高度，导致性能降低
B+树
非叶子节点为索引，叶子节点存放数据，叶子节点是单向链表
MYSQL的B+树对B+树进行优化，叶子节点形成了双向链表
索引分类
- 主建索引——只能有一个 PRIMARY，主键索引不能重复，且主键索引不能为空
- 唯一索引——可以有多个 UNIQUE，唯一索引可以为空，但是不能重复
- 普通索引——建立在普通字段上的索引，可以重复页，可以为空
- 前缀索引——对字符类型字段的前几个字符建立的索引，而不是建立在某个字段上的索引，减少索引占用的内存空间
- 常规索引…
- 全文索引…
InnoDB索引类型：
- 聚焦索引：数据和索引在一起，索引的叶子节点保存了行数据——必须有且只有一个
- 二级索引：数据和索引分开存储，索引结构的叶子节点关联的是对应的主键——可以存在多个
InnoDB
│
┌─────────┴─────────┐
↓ ↓
聚簇索引 二级索引
│ │
通常就是主键索引 name / age / email...
│ │
↓ ↓
整行数据 主键值
二级索引要回表查询,具体过程类似下图：
查询 name
↓
二级索引 B+Tree
↓
找到主键 id
↓
┌──────┴──────┐
↓ ↓
需要其他字段 不需要其他字段
↓ ↓
回表 直接返回
↓
聚簇索引 B+Tree
↓
完整数据
如果一张表存在主建，主键索引就是聚集索引 如果不存在主键，那么第一个索引UNIQUE为聚集索引 如果不存在主键也没有合适的索引，那么自动生成一个隐藏的rowid作为索引
联合索引
除了上文提到了建立在一个列上的单列索引，有时候会将多个字段组合建立联合索引 使用联合索引的时候，遵循最左匹配原则 也就是说，会先比较在左边的索引，在符合范围的数据内，匹配右边的索引 在这种情况下如果只依靠右边索引查询效率会变低
有一类特殊情况，并不是查询过程使用了联合查询，就代表联合索引中的所有字段都用到了联合查询。这种特殊情况发生在范围查询。范围查询的字段可以使用联合索引，但是在范围查询后面的字段无法使用联合索引，因为范围查询之后的数据不保证有序，此时索引存在的意义不大，只能通过遍历比较这一部分数据来查询匹配项。
索引失效的情况
- 左右列模糊匹配
- 对索引使用函数
- 对索引进行表达式计算
- 字符串和数字比较，如果字符串是索引列，会发生隐式类型转换，相当于对索引使用函数
- 未满足最左匹配原则
- where连接的查询语句中，有一列没有建立索引，也会导致失效
什么情况适合建立索引
- 字段有唯一性限制
- 经常用where查询的字段，建立索引可以提高查询速度，如果目标不是一个字段，可以建立联合索引
- 经常用order by和group by的字段，建立索引的话查询就不需要做排序，因为b+树里面的索引排序好了
什么情况不适合建立索引
- Where，Orderby，Groupby里面用不到的字段，如果建立索引会占用大量空间
- 表数据太少的时候也不需要索引
- 字段中出现大量重复数据，比如性别。MYSQL的优化器在进行优化的时候如果发现某个数据出现重复率很高，会默认不使用索引。
- 经常更新的字段也不需要建立索引，会导致B+树频繁进行页分裂，指针维护
怎么样对索引进行优化？
- 前缀索引优化，再进行一些大的字符串索引的时候，可以利用前缀索引，减少索引项的大小。
- 覆盖索引优化，减少回表查询次数，提高性能
- 主键索引最好自增，这样方便范围查询，而且加入新数据是插入操作不需要移动数据，性能非常高。如果主键不自增，那么添加的新数据有可能插入现有数据页的某个位置，导致页的复制或者内存碎片
- 防止索引失效，正确使用最左匹配原则，防止对索引使用函数