学习资料汇总
- MySQL官网
- MySQL学习之索引 | CSDN !!!
- 深入理解 MySQL 索引 !!!
- MySQL 索引 | 菜鸟教程
- 数据库两大神器【索引和锁】 | 掘金
- mysql索引使用 | 简书
- mysql 索引面试题 | gitbook
- MySQL 索引设计概要
- 深入理解 MySQL 索引 | InfoQ
- 详解Mysql索引原理及其优化 | 掘金
- MySQL索引原理及慢查询优化 | 美团技术
Overview
索引是一种能够改善表操作速度的数据结构。 索引可以通过一个或多个列来创建，它可以提高随机查询的速度，并在检索记录时实现高效排序。
索引一经创建不能修改，如果要修改索引，只能删除重建。
索引分单列索引和组合索引
- 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
- 组合索引，即一个索引包含多个列。
MyISAM 引擎和 Innodb 引擎都使用 B+Tree 作为索引结构，但是底层实现还是有些不同的
- Innodb 索引和数据存储在同一个文件
- MyISAM 索引文件和数据文件是分离的
磁盘IO
Overview
一个数据库必须保证其中存储的所有数据都是可以随时读写的，同时因为 MySQL 中所有的数据其实都是以文件的形式存储在磁盘上的，而从磁盘上随机访问对应的数据非常耗时，所以数据库程序和操作系统提供了缓冲池和内存以提高数据的访问速度。
除此之外，我们还需要知道数据库对数据的读取并不是以行为单位进行的，无论是读取一行还是多行，都会将该行或者多行所在的页全部加载进来，然后再读取对应的数据记录；也就是说，读取所耗费的时间与行数无关，只与页数有关。
在 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-最左匹配图解
- ref-最左匹配 | 官方文档翻译
- ref-联合索引和最左匹配 | CSDN!!!
从表记录的排列顺序和索引的排列顺序是否一致来划分
- 聚集索引：表记录的排列顺序和索引的排列顺序一致
- 非聚集索引：表记录的排列顺序和索引的排列顺序不一致
聚集索引
聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快。因为只要找到第一个索引值记录，其余的连续性的记录在物理表中也会连续存放，一起就可以查询到。
缺点：新增比较慢，因为为了保证表中记录的物理顺序和索引顺序一致，在记录插入的时候，会对数据页重新排序。
非聚集索引
索引的逻辑顺序与磁盘上行的物理存储顺序不同，非聚集索引在叶子节点存储的是主键和索引列。
当我们使用非聚集索引查询数据时，需要拿到叶子上的主键再去表中查到想要查找的数据。这个过程就是我们所说的回表。
索引回表
| id | user_id | user_name | phone |
|---|---|---|---|
| 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';
+-------------+----------+------------+------------+------------+----------+------------------+----------+------
| tbl_spaceid | table_id | table_name | row_format | space_type | index_id | index_name | n_fields | page_no | index_type |
+-------------+----------+------------+------------+------------+----------+------------------+----------+------
| 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');
+------------------+---------------------+--------------+------------+-----------------------------------+
| index_name | last_update | stat_name | stat_value | stat_description |
+------------------+---------------------+--------------+------------+-----------------------------------+
| idx_user_name | 2021-01-02 17:14:48 | n_leaf_pages | 1358 | Number of leaf pages in the index |
| idx_user_name | 2021-01-02 17:14:48 | size | 1572 | Number of pages in the index |
| idx_user_name_id | 2021-01-02 17:14:48 | n_leaf_pages | 1358 | Number of leaf pages in the index |
复制代码对比一下两个索引的结果，n_fields 表示索引中的列数，n_leaf_pages 表示索引中的叶子页数，size 表示索引中的总页数。通过数据比对就可以看到，辅助索引中确实包含了主键 id，也说明了这两个索引时完全一致。
| Index_name | n_fields | n_leaf_pages | size |
|---|---|---|---|
| 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';
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+-------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+-------
| 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';
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+-------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+-------
| 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子句中的列，或者连接子句中指定的列 - 基数较小的类，索引效果较差，没有必要在此列建立索引
- 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
- 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可
- 何时使用聚簇索引或非聚簇索引
| 使用动作描述 | 使用聚簇索引 | 使用非聚簇索引 |
|---|---|---|
| 列经常被分组排序 | ✅ | ✅ |
| 返回某范围内的数据 | ✅ | ❎ |
| 一个或极少不同的值 | ❎ | ❎ |
| 小数目不同的值 | ✅ | ❎ |
| 大数目不同的值 | ❎ | ✅ |
| 频繁更新的列 | ❎ | ✅ |
| 外键列 | ✅ | ✅ |
| 主键列 | ✅ | ✅ |
| 频繁修改索引列 | ❎ | ✅ |
索引失效的场景
违反最左匹配原则
最左匹配原则：最左优先，以最左边的为起点任何连续的索引都能匹配上。如不连续，则匹配不上。
比如，建立索引为 (a,b) 的联合索引，那么只查 where b = 2 则不生效。换句话说，如果建立的索引是 (a,b,c)，也只有 (a)，(a,b)，(a,b,c) 三种查询可以生效。
遇到范围查询（>、<、between、like）会停止匹配
比如，a= 1 and b = 2 and c > 3 and d =4，如果建立 (a,b,c,d) 顺序的索引，d 是用不到索引的。因为 c 字段是一个范围查询，它之后的字段会停止匹配。
在索引列上做任何操作
如计算、函数、（手动或自动）类型转换等操作，会导致索引失效而进行全表扫描。
explain select * from user where left(name,3) = 'zhangsan' and age = 20
复制代码这里对 name 字段进行了 left 函数操作，导致索引失效。
使用不等于（!= 、<>、NOT IN）
explain select * from user where age != 20;
explain select * from user where age <> 20;
复制代码上述操作会导致索引失效。
like 中以通配符开头 %
like 中以通配符开头 %，会导致索引失效。
//索引失效
explain select * from user where name like "%zhangsan";
复制代码但是以 like 中以通配符结尾 %，索引依然有效。
//索引生效
explain select * from user where name like "zhangsan%";
复制代码字符串不加单引号索引失效
explain select * from user where name = 2000;
复制代码上述语句中会发生类型转换，导致索引失效。
or 连接索引失效
explain select * from user where name = ‘2000’ or age = 20 or pos =‘cxy’;
复制代码索引注意事项
索引不会包含有 NULL 值的列
只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为 NULL。
使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个 CHAR(255) 的列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和 I/O 操作。
通过有序索引排序避免冗余执行order by
- 尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引
- MySQL 查询只使用一个索引，因此如果
where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作
只有当 order by 与where 语句同时出现，order by的排序功能无效。换句话说，order by 中的字段在执行计划中利用了索引时，不用排序操作。如下SQL时，不会按 sex 一列排序，因为 sex 本身已经是有序的了。
SELECT sex, price, name FROM LOL where sex = 1 ORDER BY sex ;
复制代码对于上面的语句，数据库的处理顺序是
- 第1步，根据
where条件和统计信息生成执行计划，得到数据。 - 第2步，将得到的数据排序。当执行 order by 时，数据库会先查看第一步的执行计划，查看 order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则排序操作。
- 返回排序后的数据
尽量选择区分度高的列作为索引
区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例。
比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、性别字段可能在大数据面前区分度就是 0。一般需要 join 的字段都要求区分度 0.1 以上，即平均 1 条扫描 10 条记录。
like 语句操作
一般情况下不鼓励使用 like 操作，如果非使用不可，如何使用也是一个问题。like "%aaa%" 不会使用索引，而 like "aaa%" 可以使用索引。
不要在列上进行运算
select from users where YEAR (adddate)<2007;
复制代码将在每个行上进行运算，这将导致索引失效而进行全表扫描。
因此我们可以改成
select from users where adddate<‘2007-01-01’;
复制代码
近期评论