MySQL 索引基础¶
基本概念¶
-
索引是一种特殊的文件( InnoDB 数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
-
建立索引主要是为了 提高查询效率。
索引使用¶
查看索引¶
show index from 表名;
创建索引¶
- 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
- 字段类型如果不是字符串，可以不填写长度部分
create index 索引名称 on 表名(字段名称(长度))
删除索引¶
drop index 索引名称 on 表名;
其他¶
-
建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。
-
建立索引会占用磁盘空间
基础知识储备¶
局部性原理¶
在作业信息不全部装入主存的情况下能否保证作业的正确运行？
回答是肯定的，早在1968年
P.Denning就研究了 程序执行时的局部性（principle of locality）原理，对程序局部性原理进行研究还有Knuth（分析一组学生的Fortran程序）、Tanenbaum（分析操作系统的过程）、Huck（分析通用科学计算的程序），发现 程序和数据的访问都有聚集成群的倾向，在一个时间段内，仅使用其中一小部分（称空间局部性），或者最近访问过的程序代码和数据，很快又被访问的可能性很大（称时间局部性）。
- 程序执行具有局部性原理的规则：包括空间局部性和时间局部性
- 空间局部性：在一个时间段内，仅仅使用其中一小部分
- 时间局部性：最近访问过的程序代码和数据，很快又会被访问的可能性更大。
磁盘预读¶
-
操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页大小通常为4k），主存和磁盘以页为单位交换数据。即：页是存储器的逻辑块。
-
预读的长度一般为页（page）的整数倍，即：
4nk
索引是什么¶
通过给字段添加索引可以提高数据的读取速度，提高项目的并发能力和抗压能力。
- 索引是帮助 MySQL 高效获取数据的数据结构
- 索引存储在文件系统中
- 索引的文件存储形式与存储引擎有关
- 索引文件的结构
- hash
- 二叉树
- B树
- B+树
索引的分类¶
MySQL索引的五种类型：主键索引、唯一索引、普通索引和全文索引、组合索引。
1, 主键索引
- 主键是一种唯一性索引，但它必须指定为
PRIMARY KEY，每个表只能有一个主键。
2, 唯一索引
- 索引列的所有值都只能出现一次，即必须唯一，值可以为空。
3, 普通索引
- 基本的索引类型，值可以为空，没有唯一性的限制。（覆盖索引）
4, 全文索引
-
MyISAM支持，Innodb在5.6之后支持 -
全文索引的索引类型为
FULLTEXT。全文索引可以在varchar、char、text类型的列上创建
5, 组合索引
- 多列值组成一个索引，专门用于组合搜索（最左匹配原则）
MySQL存储引擎¶
| MySQL存储引擎 | MyISAM |
InnoDB |
|---|---|---|
| 索引类型 | 非聚簇索引 | 聚簇索引 |
| 支持事务 | 否 | 是 |
| 支持表锁 | 是 | 是 |
| 支持行锁 | 否 | 是 |
| 支持外键 | 否 | 是 |
| 支持全文索引 | 是 | 是（5.6后支持） |
| 适合操作类型 | 大量 select |
大量 insert、delete、update |
MySQL索引机制¶
MySQL - MyISAM¶
MySQL - InnoDB¶
索引面试难点¶
- 回表
- 覆盖索引
- 最左前缀
- 索引下推
索引维护¶
索引在插入新的值的时候，为了维护索引的有序性，必须要维护，在维护索引的时候需要分以下几种情况：
-
如果插入一个比较大的值，直接插入即可，几乎没有成本
-
如果插入的是中间的某一个值，需要逻辑上移动后续的元素，空出位置
-
如果需要插入的数据页满了，就需要单独申请一个新的数据页，然后移动部分数据过去，叫做 页分裂，此时性能会受影响，同时空间的使用率也会降 低。
- 除了页分裂之外还包含页合并的情况也会影响到索引维护。
因此，尽量使用自增主键作为索引
索引面试题¶
- 数据库中最常见的慢查询优化方式是什么？
- 为什么加索引能优化慢查询？
- 你知道哪些数据结构可以提高查询速度？
- 那这些数据结构既然都能优化查询速度，MySQL为何选择使用B+树？