1.索引

索引是一种数据结构，类似于书的目录，加快查找数据的速度

1.1 分类

分类（数据结构、存储位置、字段特性、字段个数）：

1. 数据结构：B+ 树索引、哈希索引、全文索引
2. 存储位置：聚簇索引和非聚簇索引
3. 字段特性：主键索引、唯一索引、普通索引、前缀索引
4. 字段个性：单列索引、联合索引

1.2 聚簇索引和非聚簇索引

• 聚簇索引：

  • 主键索引
  • 索引结构的叶子节点存储了索引数据和一整行的数据，查询性能高
  • 有且仅有一个

• 非聚簇索引：

  • 二级索引
  • 索引结构的叶子节点存储了索引数据和主键数据

2.B 树、B+树和红黑树

• B 树和 B+树都是多路平衡查找树，而红黑树是二叉平衡查找树。
• B 树中节点存储键和数据，而 B+树的数据仅存储在叶子节点，内部节点只存键。
• B+树的叶子节点通过指针相连，便于全范围扫描，而 B 树不是。
• 在内存上，红黑树的操作相对于 B 树和 B+树来说更快，因为它是二叉的；但在处理大量数据时，由于 B 树和 B+树减少了磁盘 I/O，可能会更有效率。
• B 树和 B+树通常用于数据库和文件系统中，红黑树多用于内存中数据结构的实现

3.索引底层采用 B+树

1. 高效的范围查询：

   1. B+ 树的叶子节点之间用链表连接起来，有利于范围查询
   2. B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树

2. 磁盘 IO 次数少：虽然 B 树和 B+ 树都是多路的，但是 B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，B+ 树可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O 次数会更少。

3. 高效的增删效率：

   1. B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候
   2. B 树那样会发生复杂的树的变化

4. 不选哈希索引：如果只选一个数据，那确实是 Hash 更快。但是数据库中经常会选择多条，这时候由于 B+ 树索引有序，并且又有链表相连，它的查询效率比 Hash 就快很多了

4.存储引擎

	InnoDB	MyISAM	Memory
事务安全	支持	不支持	不支持
外键	支持	不支持	不支持
锁级别	行级锁	表级锁	表级锁
存储结构	B+树	B+树
应用场景	写密集的表	读密集的表	读多写少的静态数据

5.count(*)、count(1)、count(字段)

1. 按照性能排序：count(*) = count(1) > count(主键字段) > count(字段)
2. count(主键索引)：按照二级索引、主键索引的顺序优先选择遍历，读取记录中的字段值
3. count(1)：按照二级索引、主键索引的顺序优先选择遍历，不会读取记录中的任何字段的值
4. count(*)：相当于 count(0)，
5. count(字段) ：会采用全表扫描的方式来统计
6. count(1)、 count(*)、 count(主键字段)在执行的时候，优化器优先选择二级索引进行扫描，原因是 聚簇索引的叶子节点存储了一行记录，二级索引的叶子节点存储的是主键的值，I/O 成本比遍历聚簇索引的 I/O 成本小，因此「优化器」优先选择的是二级索引

6.索引失效场景

1. 使用联合索引时，不遵循最左匹配原则
   1. 联合索引的最左匹配原则，在遇到**范围查询（如 >、<）**的时候，就会停止匹配。即在范围查询字段后面的字段无法用到联合索引
   2. 但是，对于 >=、<=、BETWEEN、like 前缀匹配这四种范围查询，并不会停止匹配
   3. 例子 1：select * from t_table where a > 1 and b = 2；使用索引 a
   4. 例子 2：select * from t_table where a >= 1 and b = 2；使用联合索引 a、b
   5. 例子 3：SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2；使用联合索引 a、b
   6. 例子 4：SELECT * FROM t_user WHERE name like 'j%' and age = 22；使用联合索引 name、age
2. 对索引进行表达式计算、函数运算
3. 使用左或者左右模糊匹配，如 like %xx 或者 like &xx&
4. where 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列
5. 如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列
6. MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。

版权所有

版权归属：haipeng-lin

许可证：署名 4.0 国际 (CC-BY-4.0)