MySQL索引-索引基础

最新推荐文章于 2025-03-10 23:17:12 发布

大厂在职_UVT

最新推荐文章于 2025-03-10 23:17:12 发布

阅读量707

点赞数 24

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/2501_90392169/article/details/145465120

版权

除此之外，我们还需要知道数据库对数据的读取并不是以行为单位进行的，无论是读取一行还是多行，都会将该行或者多行所在的页全部加载进来，然后再读取对应的数据记录；也就是说，读取所耗费的时间与行数无关，只与页数有关。

在 MySQL 中，页的大小一般为 16KB，不过也可能是 8KB、32KB 或者其他值，这跟 MySQL 的存储引擎对数据的存储方式有很大的关系。

索引或行记录是否在缓存池中极大的影响了访问索引或者数据的成本。

磁盘I/O与预读

磁盘读取数据，靠的是机械运动，每次读取数据花费的时间可以分成3个部分

寻道时间
旋转延迟
传输时间

寻道时间指的是磁臂移动到指定磁盘所需要的时间，主流的磁盘一般在 5ms 以下；

旋转延迟指的是我们经常说的磁盘转速，比如一个磁盘 7200 转，表示的就是每分钟磁盘能转 7200 次，转换成秒也就是 120 次每秒，旋转延迟就是 1/120/2 = 4.17ms；

传输时间指的是从磁盘读取出数据或将数据写入磁盘的时间，一般都在零点几毫秒，相对于前两个，可以忽略不计。

那么访问一次磁盘的时间，即一次磁盘I/O的时间约等于 5+4.17 = 9.17ms，9ms 左右，听起来还是不错的哈，但要知道一台 500-MIPS 的机器每秒可以执行 5 亿条指令，因为指令依靠的是电的性质。换句话说，执行一次I/O的时间可以执行 40 万条指令，数据库动辄百万级甚至千万级的数据，每次 9ms 的时间，显然是一个灾难。下图是计算机硬件延迟时间的对比图。

考虑到磁盘I/O是非常高昂代价的操作，计算机系统做了一些优化。当一次I/O时，不光会把当前磁盘地址的数据读取到内存中，而且会把相邻的数据也读取到内存缓冲区中，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快访问到。每一次I/O读取的数据我们称之为一页（Page）。具体一页的数据有多大，这个跟操作系统有关，一般为4K或8K，也就是我们读取一页数据的时候，实际上才发生了一次I/O，这个理论对于索引的数据结构设计很有帮助。

索引的优点

大大减少了服务器需要扫描的数据量
帮助服务器避免排序带来的性能开销
将随机IO变成顺序IO

索引的缺点

过多的使用索引将会造成滥用。索引缺点如下

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
索引只是提高效率的一个因素，如果你的 MySQL 有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

索引分类

外键索引 InnoDB 是 mysql 目前唯一支持外键索引的内置引擎。

外键成本：外键每次修改数据时都要求在另一张表多执行一次查找，当然外键在相关数据删除和更新上比在应用中维护更高效。

从存储结构上划分

BTree 索引（B+tree，B-tree)
哈希索引
full-index 全文索引
RTree

从应用层次上来划分

普通索引
唯一索引：索引列的值必须唯一，但允许有空值
组合索引（联合索引）：一个索引包含多个列，最多可包含16列
主键索引

下面从应用层次的角度对索引进行分类介绍。

普通索引

普通索引是最基本的索引，它没有任何限制。

创建索引

CREATE INDEX indexName ON mytable(username(length));

如果是 CHAR，VARCHAR 类型，length 可以小于字段实际长度；如果是 BLOB 和 TEXT 类型，必须指定 length，下同。

修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length));

创建表的时候直接指定

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

INDEX [indexName] (username(length))

);

删除索引

DROP INDEX [indexName] ON mytable;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length));

修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length));

创建表的时候直接指定

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

UNIQUE [indexName] (username(length))

);

查看索引时候，使用 \G 来格式化输出信息

mysql> SHOW INDEX FROM table_name; \G

主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

PRIMARY KEY(ID)

);

当然也可以用 ALTER 命令。记住，一个表只能有一个主键。

ALTER TABLE table_name ADD PRIMARY KEY ( column )

组合索引（联合索引）

联合索引最多只能包含16列。

为了形象地对比单列索引和组合索引，为表添加多个字段

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

city VARCHAR(50) NOT NULL,

age INT NOT NULL

);

为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。就是将 name, city, age 建到一个索引里

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

建表时，usernname 长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过 10，这样会加速索引查询速度，还会减少索引文件的大小，提高I INSERT 的更新速度。

如果分别在 usernname，city，age 上建立单列索引，让该表有 3 个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。

虽然此时有了 3 个索引，但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面3组组合索引

usernname, city, age
usernname, city
usernname

为什么没有 city，age 这样的组合索引呢？这是因为 MySQL 组合索引 “最左前缀” 的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引。

下面的几个 SQL 就会用到这个组合索引

SELECT FROM mytable WHREE username=“admin” AND city=“郑州”

SELECT FROM mytable WHREE username=“admin”

而下面几个则不会用到

SELECT FROM mytable WHREE age=20 AND city=“郑州”

SELECT FROM mytable WHREE city=“郑州”

最左匹配

当创建 (col1,col2,col3) 联合索引时，相当于创建了

(col) 单列索引
(clo1,clo2) 联合索引
(col1,col2,col3) 联合索引

从表记录的排列顺序和索引的排列顺序是否一致来划分

聚集索引：表记录的排列顺序和索引的排列顺序一致
非聚集索引：表记录的排列顺序和索引的排列顺序不一致

聚集索引

聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快。因为只要找到第一个索引值记录，其余的连续性的记录在物理表中也会连续存放，一起就可以查询到。

缺点：新增比较慢，因为为了保证表中记录的物理顺序和索引顺序一致，在记录插入的时候，会对数据页重新排序。

非聚集索引

索引的逻辑顺序与磁盘上行的物理存储顺序不同，非聚集索引在叶子节点存储的是主键和索引列。

当我们使用非聚集索引查询数据时，需要拿到叶子上的主键再去表中查到想要查找的数据。这个过程就是我们所说的回表。

索引回表

ref-mysql如何设计索引更高效——非聚集索引

| — | — | — | — |

| 1 | u001 | Lass | 15821929853 |

| 2 | u002 | Peter | 13673019487 |

对于上面的表 users，其主键为 id，下面在 user_name 列上创建一个索引。对于 user_name 的索引 idx_user_name(user_name) 而言，其实等价于 idx_user_name(user_name,id)，MySQL会自动在辅助索引的最后添加上主键 id。 下面创建索引的语句，二者等效。

– 创建user_name列上的索引

mysql> create index idx_user_name on users(user_name);

– 等效于显式添加主键id创建索引

mysql> create index idx_user_name_id on users(user_name,id);

– 对比两个索引的统计数据

mysql> select a.space as tbl_spaceid, a.table_id, a.name as table_name, row_format, space_type, b.index_id , b.name as index_name, n_fields, page_no, b.type as index_type from information_schema.INNODB_TABLES a left join information_schema.INNODB_INDEXES b on a.table_id =b.table_id where a.name = ‘test/users’;

±------------±---------±-----------±-----------±-----------±---------±-----------------±---------±-----

±------------±---------±-----------±-----------±-----------±---------±-----------------±---------±-----

| 518 | 1586 | test/users | Dynamic | Single | 1254 | PRIMARY | 9 | 4 | 3 |

| 518 | 1586 | test/users | Dynamic | Single | 4003 | idx_user_name | 2 | 5 | 0 |

| 518 | 1586 | test/users | Dynamic | Single | 4004 | idx_user_name_id | 2 | 45 | 0 |

mysql> select index_name, last_update, stat_name, stat_value, stat_description from mysql.innodb_index_stats where index_name in (‘idx_user_name’,‘idx_user_name_id’);

±-----------------±--------------------±-------------±-----------±----------------------------------+

±-----------------±--------------------±-------------±-----------±----------------------------------+

对比一下两个索引的结果，n_fields 表示索引中的列数，n_leaf_pages 表示索引中的叶子页数，size 表示索引中的总页数。通过数据比对就可以看到，辅助索引中确实包含了主键 id，也说明了这两个索引时完全一致。

| — | — | — | — |

| idx_user_name | 2 | 1358 | 1572 |

| idx_user_name_id | 2 | 1358 | 1572 |

上面证明了辅助索引包含主键id，如果通过辅助索引列去过滤数据有可能需要回表。

举个例子，根据用户名 user_name 去查找信息。

对于索引 idx_user_name 而言，其实就是一个小表 idx_user_name(user_name, id)，如果只查询索引中的列，只需要扫描索引就能获取到所需数据，是不需要回表的。如下 SQL 语句

– SQL 1

select id, user_name from users where user_name = ‘Laaa’;

– SQL 2

select id from users where user_name = ‘Laaa’;

mysql> explain select id, name from users where name = ‘Laaa’;

±—±------------±------±-----------±-----±--------------±--------------±--------±------±-----±------

±—±------------±------±-----------±-----±--------------±--------------±--------±------±-----±------

| 1 | SIMPLE | users | NULL | ref | idx_user_name | idx_user_name | 82 | const | 1 | 100.00 | Using index |

mysql> explain select id from users where name = ‘Laaa’;

±—±------------±------±-----------±-----±--------------±--------------±--------±------±-----±------

±—±------------±------±-----------±-----±--------------±--------------±--------±------±-----±------

| 1 | SIMPLE | users | NULL | ref | idx_user_name | idx_user_name | 82 | const | 1 | 100.00 | Using index |

上面的 SQL 1 和 SQL 2 的执行计划中的 Extra=Using index 表示使用覆盖索引扫描，不需要回表。

但是如果需要查询的列，不再索引中的列呢？这个时候就会产生回表。如下SQL语句

select user_id, user_name, phone from users where user_name = ‘Laaa’;

可以看到 select 后面的 user_id，phone 列不在索引 idx_user_name 中，就需要通过主键 id 进行回表查找，MySQL内部分如下两个阶段处理

Section 1： select id from users where user_name = ‘Laaa’ //id = 100101

Section 2: select user_id, user_name, phone from users where id = 100101;

将 Section 2 的操作称为回表，即通过辅助索引中的主键 id 去原表中查找数据。

聚集索引和非聚集索引的区别

聚集索引在叶子节点存储的是表中的数据
非聚集索引在叶子节点存储的是主键和索引列

索引创建规范

什么情况下需要建立索引

一般来说，在 WHERE 和 JOIN 中出现的列需要建立索引，但也不完全如此，因为 MySQL 只对 <，<=，=，>，>=，BETWEEN，IN，以及某些时候的 LIKE 才会使用索引。

例如

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE

m.age=20 AND m.city=‘郑州’;

此时就需要对 city 和 age 建立索引，由于 mytable 表的 userame 也出现在了 JOIN 子句中，也有对它建立索引的必要。

刚才提到只有某些时候的 LIKE 才需建立索引。因为在以通配符 % 和 _ 开头作查询时，MySQL 不会使用索引。 例如下句会使用索引

SELECT FROM mytable WHERE username like ‘admin%’

而下句就不会使用

SELECT FROM mytable WHEREt Name like ‘%admin’

因此，在使用 LIKE 时应注意以上的区别。

索引设计原则

适合索引的列是出现在 where 子句中的列，或者连接子句中指定的列
基数较小的类，索引效果较差，没有必要在此列建立索引
使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可
何时使用聚簇索引或非聚簇索引

| 使用动作描述 | 使用聚簇索引 | 使用非聚簇索引 |

| — | — | — |

| 列经常被分组排序 | ✅ | ✅ |

| 返回某范围内的数据 | ✅ | ❎ |

| 一个或极少不同的值 | ❎ | ❎ |

| 小数目不同的值 | ✅ | ❎ |

| 大数目不同的值 | ❎ | ✅ |

| 频繁更新的列 | ❎ | ✅ |

| 外键列 | ✅ | ✅ |

| 主键列 | ✅ | ✅ |

| 频繁修改索引列 | ❎ | ✅ |

索引失效的场景