定义
- 帮助MySQL高效获取数据的数据结构
- 默认都是使用B+树结构组织的索引
分类
- 数据结构纬度
- B+树索引:所有数据存储在叶子节点,复杂度为O(logn),适合范围查询。
- 哈希索引:适合等值查询,检索效率高,一次到位。
- 全文索引:MyISAM和InnoDB中都支持使用全文索引,一般在文本类型char,text,varchar类型上创建。
- 物理存储纬度
- 聚簇索引:聚簇索引就是以主键创建的索引,在叶子节点存储的是表中的数据。(Innodb存储引擎)
- 非聚簇索引:非聚簇索引就是以非主键创建的索引,在叶子节点存储的是主键和索引列。(Innodb存储引擎)
- 逻辑纬度
- 主键索引:一种特殊的唯一索引,不允许有空值。
- 普通索引:基本索引类型,允许空值和重复值。
- 联合索引:多个字段创建的索引,使用时遵循最左前缀原则。
- 唯一索引:索引列中的值必须是唯一的,但是允许为空值。
- 空间索引: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+ 树就能找到数据。
合理的库表、索引
合理设计表
-
第一范式:确保每列保持原子性
-
第二范式:确保表中的每列都和主键相关
-
第三范式:确保每列都和主键列直接相关,而不是间接相关,不存在传递依赖
-
设计合理的数据表
- 将数据分解为合适的表,避免将过多的数据存储在单个表中
- 每个表都应该有清晰定义的目的
-
表、字段规范
- 表名、字段名应该使用小写字母,采用下划线分割
- 表达是与否概念的字段,必须使用 is _ xxx 的方式命名,数据类型是 unsigned tinyint( 1 表示是,0 表示否 )
- 表必备三字段: id 主键, gmt _ create创建时间 , gmt _ modified更新时间
- 不同表之间存储相同数据的列名和列类型必须一致(关联列)
- 不得使用外键与级联
- 增加冗余字段
-
选择合适的数据类型
- 优先选择符合存储需要的最小、最简单的数据类型
- 小数类型为 decimal ,禁止使用 float 和 double
- 日期使用date、时间使用时间戳
-
避免使用过多的NULL值
合理设计索引
-
创建合适的索引
- 为常作为查询条件的字段建立索引
- 为经常需要排序、分组和联合操作的字段建立索引
- 业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引
- 主键索引名为 pk_ 字段名;唯一索引名为 uk _字段名 ; 普通索引名则为 idx _字段名
- 在 varchar 字段上建立索引时,必须指定索引长度
-
选择合适的索引类型
- 等值查询可以选择hash索引、范围选择B+树
- 唯一字段选择唯一索引、多个字段建立联合索引
-
正确设置索引列的顺序
-
使用覆盖索引
索引优化
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句
-
限制索引的数目
-
数据量小的表最好不要使用索引
-
尽量使用数据量少的索引
-
避免使用长字符串作为索引、尽量使用前缀来索引
-
索引最好设置为 NOT NULL
-
关联查询优化
- 内连接时,mysql 会自动把小结果集选为驱动表,所以大表字段需加上索引
- 左外连接时,左表(驱动表)会全表扫描,所以右边大表字段需加上索引
- 右外连接同理,被驱动表上的字段需建立索引
-
排序、分组优化
- 尽量避免使用Using FileSort方式排序
- 语句满足索引最左匹配
- 不要出现索引范围查询
-
慢查询日志
-
定期维护索引
-
监控数据库性能
-
删除不再使用或者很少使用的索引
索引失效
- 最左前缀匹配原则
- 联合索引中只有最左边的字段是有序的
- 不在索引列上做任何计算、函数操作
- 不使用:不等于和is not null
- like不以通配符开头
- 字符串字段不加单(双)引号
- 不使用 or 连接
参考文章:MySQL的B+树