MySQL索引原理

目录

一、索引

MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

二、索引的原理

索引用于快速查找具有特定列值的行。如果没有索引，MySQL必须从第一行开始，然后读取整个表以查找相关行。表越大，成本越高。如果表中有相关列的索引，MySQL可以快速确定要在数据文件中间寻找的位置，而无需查看所有数据。这比按顺序读取每一行要快得多。

MySQL常用的是B+ Tree索引，下面详细介绍。

2.1 b+树

b+树

如上图，是一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

上图中，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有

公式

当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。

当b+树的数据项是复合的数据结构，常见的就是组合索引，比如我们给某个表添加个组合索引，包括姓名、年龄和性别三列(name,age,sex)，b+数是按照从左到右的顺序来建立搜索树的，比如查询（where name=‘马云’ and age=18 and sex=1），b+树会优先比较name来确定下一步的检索方向，如果name相同再依次比较age和sex，最后得到检索的数据；但如果我们查询(where age=18 and sex= 1),此时索引是不生效的，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当查询(where name='张三' and sex=2)的时候，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是2的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

三、MySQL如何使用索引

MySQL使用索引进行这些操作：

  • WHERE快速 查找与子句匹配的行。

  • 如果在多个索引之间有选择，MySQL通常使用找到最小行数的索引。

  • 如果表具有多列索引，即组合索引，则优化程序可以使用索引的任何最左前缀来查找行。例如，如果你有一个三列索引上(col1, col2, col3)，你有索引的搜索功能(col1)，(col1, col2)以及(col1, col2, col3)。

  • 在执行连接时从其他表中检索行。如果声明它们的类型和大小相同，MySQL可以更有效地使用列上的索引。在这种情况下， VARCHAR与 CHAR被认为是相同的，如果它们被声明为相同的大小。例如， VARCHAR(10)和 CHAR(10)大小相同，但 VARCHAR(10)与 CHAR(15)不是。

    对于非二进制字符串列之间的比较，两列应使用相同的字符集。例如，将utf8列与 latin1列进行比较会排除使用索引。

    不相似列的比较（例如，将字符串列与时间或数字列进行比较）可能会在没有转换的情况下无法直接比较值时阻止使用索引。对于给定的值，如1 在数值列，它可能比较等于在字符串列，例如任何数量的值 '1'，' 1'， '00001'，或'01.e1'。这排除了对字符串列的任何索引的使用。

  • 查找特定索引列的值Min()或 Max()[`值key_col。这是由预处理器优化的，该预处理器检查您是否正在使用 索引之前出现的所有关键部分。在这种情况下，MySQL对每个或 表达式执行单个键查找，并用常量替换它。

  • 对指定索引列进行排序或者分组，ORDER BY或者 GROUP BY

  • 在某些情况下，可以优化查询在不查询整行数据的情况下检索值。（为查询提供所有必要结果的索引称为 [覆盖索引]）如果查询仅使用表中包含某些索引的列，则可以从索引树中检索所选值以获得更快的速度：比如

    SELECT key_part3 FROM tbl_name
WHERE key_part1 = 1

对于小型表或报表查询处理大多数或所有行的大型表的查询，索引不太重要。当查询需要访问大多数行时，顺序读取比通过索引更快。顺序读取可以最大限度地减少磁盘搜索，即使查询不需要所有行也是如此。

3.1 如何优化

  1. 主键优化

    表的主键表示您在最重要的查询中使用的列或列集。它具有关联的索引，以实现快速查询性能。查询性能受益于NOT NULL优化，因为它不能包含任何NULL值。使用InnoDB存储引擎，表数据在物理上进行组织，以根据主键或列进行超快速查找和排序。

    如果您的表很大且很重要，但没有明显的列或列集用作主键，则可以创建一个单独的列，其中包含自动增量值以用作主键。使用外键连接表时，这些唯一ID可用作指向其他表中相应行的指针。

  2. 外键优化

    如果一个表有很多列，并且您查询了许多不同的列组合，那么将频率较低的数据拆分为每个都有几列的单独表可能会很有效，并通过外键将它们与主表关联起来。这样每个小表都可以有一个主键来快速查找其数据，您可以使用连接操作查询所需的列集。根据数据的分布方式，查询可能会执行较少的I / O并占用较少的高速缓存。（为了最大限度地提高性能，查询尝试从磁盘中读取尽可能少的数据块）。

  3. 列索引

    最常见的索引类型涉及单个列，在数据结构中存储该列的值的副本，允许快速查找具有相应列值的行。B树数据结构可以让索引快速查找特定值，一组值，或值的范围，例如where条件中=， >，≤， BETWEEN，IN等。

    每个存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256个字节。

    • 索引前缀

      使用 字符串列的索引规范中的语法，可以创建仅使用列的前几个字符的索引 。以这种方式仅索引列值的前缀可以使索引文件更小。索引 或 列时， 必须为索引指定前缀长度。

      如果搜索项超过索引前缀长度，则索引用于排除不匹配的行，并检查剩余的行以查找可能的匹配项。

    • FULLTEXT索引

      FULLTEXT索引用于全文搜索。只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于CHAR，VARCHAR和TEXT类型的列。索引始终发生在整个列上，并且不支持列前缀索引。

    • 空间索引（Spatial Index）

      您可以在空间数据类型上创建索引。 MyISAM和InnoDB 支持空间类型的R树索引。其他存储引擎使用B树来索引空间类型（除了 ARCHIVE）。

  4. 多列索引

    MySQL可以创建复合索引（即多列索引）。索引最多可包含16列。对于某些数据类型，您可以索引列的前缀。

    MySQL可以对测试索引中所有列的查询使用多列索引，或者只测试第一列，前两列，前三列等的查询。如果在索引定义中以正确的顺序指定列，则单个复合索引可以加速同一表上的多种查询。

    假设一个表具有以下规范：

    CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

    在last_name和first_name列创建了一个组合索引，它既可以查询last_name和first_name`组合的值，也可以仅查询last_name，因为该列是索引的最左前缀。因此，下面这些查询是可以用到该索引的：

    //只查询last_name
SELECT * FROM test WHERE last_name='Jones';

//同时查
SELECT * FROM test
  WHERE last_name='Jones' AND first_name='John';

SELECT * FROM test
  WHERE last_name='Jones'
  AND (first_name='John' OR first_name='Jon');

SELECT * FROM test
  WHERE last_name='Jones'
  AND first_name >='M' AND first_name < 'N';

    但是，该索引 不能用于以下查询中的查找：

    SELECT * FROM test WHERE first_name='John';

SELECT * FROM test
  WHERE last_name='Jones' OR first_name='John';

    假设您写了如何SQL语句：

    SELECT * FROM tbl_name
  WHERE col1=val1 AND col2=val2;

    如果col1和col2存在组合索引，那么可以直接获取相应的行。如果col1和col2每列都存在单列索引，那么MySQL会优化合并索引，或者尝试通过确定哪个索引会排除更多的行来查找限制性最强的索引。

    如果表具有多列索引，则优化程序可以使用索引的最左前缀来查找行。例如，如果你有一个三列索引上(col1, col2, col3)，你有索引的搜索功能 (col1)，(col1, col2)以及 (col1, col2, col3)。

    如果SQL语句不适用索引的最左前缀，则MySQL无法使用索引执行查找。例如以下查询语句：

    //使用索引
SELECT * FROM tbl_name WHERE col1=val1;

//使用索引
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

//不使用索引
SELECT * FROM tbl_name WHERE col2=val2;

//不使用索引
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

    如果存在索引(col1, col2, col3)，则只有前两个查询使用索引。第三和第四个查询确实包括索引的列，但不使用索引来进行查找，因为(col2)和 (col2, col3)不是的最左边的前缀 (col1, col2, col3)。

四、MySQL索引管理

4.1 功能

  1. 索引的功能就是加速查找
  2. mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能

4.2 MySQL常用的索引

  • 普通索引INDEX：加速查找
  • 唯一索引：
    • 主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
    • 唯一索引UNIQUE:加速查找+约束（不能重复）
  • 联合索引：
    • PRIMARY KEY(id,name):联合主键索引
    • UNIQUE(id,name):联合唯一索引
    • INDEX(id,name):联合普通索引

4.3 各个索引应用场景

举个例子来说，比如你在为某商场做一个会员卡的系统。

这个系统有一个会员表
有下列字段：
会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT

那么这个 会员编号，作为主键，使用 PRIMARY
会员姓名 如果要建索引的话，那么就是普通的 INDEX
会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）

# 除此之外还有全文索引，即FULLTEXT
会员备注信息 ， 如果需要建索引的话，可以选择全文搜索。
用于搜索很长一篇文章的时候，效果最好。
用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索。

# 其他的如空间索引SPATIAL，了解即可，几乎不用

各个索引的应用场景

各个索引的应用场景

4.4 索引的两大类型hash与btree

我们可以在创建上述索引的时候，为其指定索引类型，分两类：

  1. hash类型的索引：查询单条快，范围查询慢
  2. btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

不同的存储引擎支持的索引类型也不一样：

  • InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
  • MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
  • Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
  • NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
  • Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

4.5 创建/删除索引的语法

# 方法一：创建表时
    　　CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC]) 
                );


# 方法二：CREATE在已存在的表上创建索引
        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;


# 方法三：ALTER TABLE在已存在的表上创建索引
        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                             索引名 (字段名[(长度)]  [ASC |DESC]) ;
                             
# 删除索引：DROP INDEX 索引名 ON 表名字;

4.6 示例

# 方式一
create table t1(
    id int,
    name char,
    age int,
    sex enum('male','female'),
    unique key uni_id(id),
    index ix_name(name) # index没有key
);
create table t1(
    id int,
    name char,
    age int,
    sex enum('male','female'),
    unique key uni_id(id),
    index(name) # index没有key
);


# 方式二
create index ix_age on t1(age);


# 方式三
alter table t1 add index ix_sex(sex);
alter table t1 add index(sex);

# 查看
mysql> show create table t1;
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` enum('male','female') DEFAULT NULL,
  UNIQUE KEY `uni_id` (`id`),
  KEY `ix_name` (`name`),
  KEY `ix_age` (`age`),
  KEY `ix_sex` (`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

五、测试索引

5.1 数据准备

# 1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

# 2. 创建存储过程，实现批量插入记录
delimiter $$ # 声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'eva','female',concat('eva',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ # $$结束
delimiter ; # 重新声明分号为结束符号

# 3. 查看存储过程
show create procedure auto_insert1G 

# 4. 调用存储过程
call auto_insert1();

1、在没有索引的前提下测试查询速度

无索引：mysql根本就不知道到底是否存在id等于333333333的记录，只能把数据表从头到尾扫描一遍，此时有多少个磁盘块就需要进行多少IO操作，所以查询速度很慢

mysql> select * from s1 where id=333333333;
Empty set (0.33 sec)

2、在表中已经存在大量数据的前提下，为某个字段段建立索引，建立速度会很慢

204-MySQL索引原理-02.png?x-oss-process=style/watermark

3、在索引建立完毕后，以该字段为查询条件时，查询速度提升明显

204-MySQL索引原理-03.png?x-oss-process=style/watermark

注意：

  1. mysql先去索引表里根据b+树的搜索原理很快搜索到id等于333333333的记录不存在，IO大大降低，因而速度明显提升
  2. 我们可以去mysql的data目录下找到该表，可以看到占用的硬盘空间多了
  3. 需要注意，如下图

204-MySQL索引原理-04.png?x-oss-process=style/watermark

5.2 小结

  1. 一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引
  2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快，比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。建完以后，再查询就会很快了。
  3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI

MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance True）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引。
因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型.

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/XuChengNotes/p/11747405.html