MySQL 索引

定义

• 帮助MySQL高效获取数据的数据结构
• 默认都是使用B+树结构组织的索引

分类

1. 数据结构纬度
   • B+树索引：所有数据存储在叶子节点，复杂度为O(logn)，适合范围查询。
   • 哈希索引：适合等值查询，检索效率高，一次到位。
   • 全文索引：MyISAM和InnoDB中都支持使用全文索引，一般在文本类型char,text,varchar类型上创建。
2. 物理存储纬度
   • 聚簇索引：聚簇索引就是以主键创建的索引，在叶子节点存储的是表中的数据。（Innodb存储引擎）
   • 非聚簇索引：非聚簇索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列。（Innodb存储引擎）
3. 逻辑纬度
   • 主键索引：一种特殊的唯一索引，不允许有空值。
   • 普通索引：基本索引类型，允许空值和重复值。
   • 联合索引：多个字段创建的索引，使用时遵循最左前缀原则。
   • 唯一索引：索引列中的值必须是唯一的，但是允许为空值。
   • 空间索引：MySQL5.7之后支持空间索引，在空间索引这方面遵循OpenGIS几何数据模型规则。

MsSQL 读取数据格式

数据库的 I/O 操作的最小单位是 **数据页**，InnoDB 默认的大小是 16KB
数据页包括 文件头、用户记录、页目录等

• 文件头：有两个指针，分别指向上一个数据页和下一个数据页，连接起来相当于一个双向的链表
• 用户记录：按照主键顺序组成单向链表
• 页目录：由多个槽按照先后顺序组成
  • 将用户记录划分成几个组，记录包括最小记录(第一条记录)和最大记录(最后一条记录)
  • 组中最后一条记录会存储该组一共有多少条记录
  • 组中最后一条记录的地址偏移量称为槽，相当于分组记录的索引

数据结构

Hash表

• 优点：
• 适合单值快速检索数据
• 缺点：
• 不支持顺序和范围查询

二叉树

• 优点：
• 平衡时查询的时间复杂度为 O(log2(N))
• 缺点：
• 不平衡时查询的时间复杂度最大为O(N)

平衡二叉树

• 优点：
• 查询的时间复杂度都是 O(log2(N))
• 缺点：
• 数据量大时磁盘IO次数多
• 不支持范围查询

B树

• 特点：
• B树的节点中存储这多个元素，每个内节点有多个分叉
• 节点中的元素包含键值和数据，节点中的键值从小到大排列。
• 父节点当中的元素不会出现在子节点中。
• 所有的叶子节点都位于同一层，叶子节点具有相同的深度，叶子节点之间没有指针连接。
• 优点：
• 可以很好的提升查询的效率
• 缺点：
• 不支持范围快速查询
• 行记录增加，所占空间变大，树高度变高，磁盘IO次数变大

B+树

• 特点：
• 只有叶子节点才会存储数据，非叶子节点只存储键值key
• 叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表
• 优点：
• 可以保证等值和范围查询的快速查找
• 缺点：
• 会占用更多的空间

回表与索引覆盖

• 如果某个查询语句使用了二级索引，但是查询的数据不是主键值，这时在二级索引找到主键值后，需要去聚簇索引中获得数据行，这个过程就叫作「回表」，也就是说要查两个 B+ 树才能查到数据。
• 当查询的数据是主键值时，因为只在二级索引就能查询到，不用再去聚簇索引查，这个过程就叫作「索引覆盖」，也就是只需要查一个 B+ 树就能找到数据。

合理的库表、索引

合理设计表

1. 第一范式：确保每列保持原子性

2. 第二范式：确保表中的每列都和主键相关

3. 第三范式：确保每列都和主键列直接相关,而不是间接相关，不存在传递依赖

4. 设计合理的数据表

   • 将数据分解为合适的表，避免将过多的数据存储在单个表中
   • 每个表都应该有清晰定义的目的

5. 表、字段规范

   • 表名、字段名应该使用小写字母，采用下划线分割
   • 表达是与否概念的字段，必须使用 is _ xxx 的方式命名，数据类型是 unsigned tinyint（ 1 表示是，0 表示否 ）
   • 表必备三字段： id 主键, gmt _ create创建时间 , gmt _ modified更新时间
   • 不同表之间存储相同数据的列名和列类型必须一致(关联列)
   • 不得使用外键与级联
   • 增加冗余字段

6. 选择合适的数据类型

   • 优先选择符合存储需要的最小、最简单的数据类型
   • 小数类型为 decimal ，禁止使用 float 和 double
   • 日期使用date、时间使用时间戳

7. 避免使用过多的NULL值

合理设计索引

1. 创建合适的索引

   • 为常作为查询条件的字段建立索引
   • 为经常需要排序、分组和联合操作的字段建立索引
   • 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引
   • 主键索引名为 pk_ 字段名；唯一索引名为 uk _字段名 ； 普通索引名则为 idx _字段名
   • 在 varchar 字段上建立索引时，必须指定索引长度

2. 选择合适的索引类型

   • 等值查询可以选择hash索引、范围选择B+树
   • 唯一字段选择唯一索引、多个字段建立联合索引

3. 正确设置索引列的顺序

4. 使用覆盖索引

索引优化

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句

1. 限制索引的数目

2. 数据量小的表最好不要使用索引

3. 尽量使用数据量少的索引

4. 避免使用长字符串作为索引、尽量使用前缀来索引

5. 索引最好设置为 NOT NULL

6. 关联查询优化

   • 内连接时，mysql 会自动把小结果集选为驱动表，所以大表字段需加上索引
   • 左外连接时，左表（驱动表）会全表扫描，所以右边大表字段需加上索引
   • 右外连接同理，被驱动表上的字段需建立索引

7. 排序、分组优化

   • 尽量避免使用Using FileSort方式排序
   • 语句满足索引最左匹配
   • 不要出现索引范围查询

8. 慢查询日志

9. 定期维护索引

10. 监控数据库性能

11. 删除不再使用或者很少使用的索引

索引失效

• 最左前缀匹配原则
  • 联合索引中只有最左边的字段是有序的
• 不在索引列上做任何计算、函数操作
• 不使用：不等于和is not null
• like不以通配符开头
• 字符串字段不加单（双）引号
• 不使用 or 连接

参考文章：MySQL的B+树