欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

MySQL优化篇：索引

程序员文章站 2022-03-08 17:42:10
...

文章目录

1、概念

1.1 是什么

MySQL对索引的定义：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。

简单理解为：排好序的快速查找数据结构。

在数据本身之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

下图是一种可能的索引方式的示例：

MySQL优化篇：索引

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应的数据，从而快速的检索出符合条件的记录。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

平时所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）。其中聚集索引，次要索引，覆盖索引，复合索引、前缀索引、唯一索引默认都是使用B+树索引，统称索引，除了B+树这种类型的索引之外，还有哈希索引（Hash Index）等。

1.2 优缺点

优点

  • 提高数据检索的效率，降低数据库的IO成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

缺点

  • 索然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的的键值变化后的索引信息。
  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
  • 索引只是提高效率的一个因素，如果MySQL有大量的表，就需要花时间研究建立最优秀的索引，或者优化查询语句

2、MySQL的索引

2.1 Btree索引

MySQL使用的是Btree索引。

B-Tree是为磁盘等外存储设备设计的一种平衡查找树

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K。

而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

一棵m阶的B-Tree有如下特性：

  1. 每个节点最多有m个孩子。
  2. 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
  3. 若根节点不是叶子节点，则至少有2个孩子
  4. 所有叶子节点都在同一层，且不包含其它关键字信息
  5. 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
  6. 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
  7. ki(i=1,…n)为关键字，且关键字升序排序。
  8. Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：

MySQL优化篇：索引

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

  1. 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
  2. 比较关键字29在区间（17,35），找到磁盘块1的指针P2。
  3. 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
  4. 比较关键字29在区间（26,30），找到磁盘块3的指针P2。
  5. 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
  6. 在磁盘块8中的关键字列表中找到关键字29。

真实的情况是，3 层的 b+树可以表示上百万的数据，如果上百万的数据查找只需要三次 IO，性能提高将是巨大的

如果没有索引，每个数据项都要发生一次 IO，那么总共需要百万次的 IO，显然成本非常非常高

2.2 B+Tree索引

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

以下内容，摘自BTree和B+Tree详解

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

  1. 非叶子节点只存储键值信息。
  2. 所有叶子节点之间都有一个链指针。
  3. 数据记录都存放在叶子节点中。

将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

MySQL优化篇：索引

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算：

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10^3）。
也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。
mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。

辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

3、MySQL索引分类

3.1 单值索引(普通索引)

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

跟表一起创建，语法如下：

KEY(name)

CREATE TABLE emp(
	id INT AUTO_INCREMENT,
	name VARCHAR(100),	
	KEY(name)
);

单独建单值索引，语法如下

CREATE INDEX idx_name ON emp(name);

3.2 唯一索引

唯一索引：索引列的值必须唯一，但允许有空值

唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。

如果是组合索引，则列值的组合必须唯一。

简单来说：唯一索引是加速查询 + 列值唯一（可以有null）

跟表一起创建，语法如下

UNIQUE (customer_no)

CREATE TABLE customer (
	id INT(10) UNSIGNED AUTO_INCREMENT ,
	customer_no VARCHAR(200),
	customer_name VARCHAR(200),	
	UNIQUE (customer_no)
);

单独建唯一索引，语法如下

CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

3.3 主键索引

主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。

简单来说：主键索引是加速查询 + 列值唯一（不可以有null）+ 表中只有一个。

跟表一起创建，语法如下

PRIMARY KEY(id)

CREATE TABLE customer (
	id INT(10) UNSIGNED AUTO_INCREMENT ,
	customer_no VARCHAR(200),
	customer_name VARCHAR(200), 
	PRIMARY KEY(id)
);

单独建主键索引，语法如下

ALTER TABLE customer add PRIMARY KEY customer(customer_no);

删除主键索引，语法如下

ALTER TABLE customer drop PRIMARY KEY;

修改主键索引，语法如下

必须先删除掉（drop）原索引，再创建（add）索引

3.4 复合(组合)索引

复合（组合）索引：即一个索引包含多个列

组合索引指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用

使用组合索引时遵循最左前缀集合

组合索引是多列值组成的一个索引，专门用于组合搜索，其效率大于索引合并

跟表一起创建索引，语法如下

CREATE TABLE customer (
	id INT(10) UNSIGNED AUTO_INCREMENT ,
	customer_no VARCHAR(200),
	customer_name VARCHAR(200), 
	KEY(customer_no,customer_name)
);

单独创建索引，语法如下

CREATE INDEX idx_no_name ON customer(customer_no,customer_name);

3.5 基本语法

#创建
CREATE [UNIQUE] INDEX [indexName] ON table_name(column);
#删除
DROP INDEX [indexName] ON mytable;
#查看
SHOW INDEX FROM table_name;
#使用Alter命令
#该语句添加一个主键，意味着索引值必须是惟一的，且不能为NULL
ALTER TABLE tbl_name ADD PRIMARY KEY(column_list);
#添加普通索引，索引值可出现多次
ALTER TABLE tbl_name ADD INDEX index_name (column_list);
#该语句指定了索引为 FULLTEXT ，用于全文索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);

4、索引创建的时机

4.1 适合创建索引的情况

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 查询中与其他表关联的字段，外键关系建立索引
  4. 单键/组合索引的选择问题，组合索引性价比更高
  5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  6. 查询中统计或者分组字段

4.2 不适合创建索引的情况

  1. 表记录太少
  2. 经常增删改的表或者字段
  3. where条件里用不到的字段不创建索引
  4. 过滤性不好的不适合创建索引
相关标签： MySQL 数据库 mysql 索引

上一篇： php中链表和数组的区别是什么

下一篇： PHP学习之浅谈if与switch的使用与区别

推荐阅读