本文详细介绍了MySQL中索引的类型（如唯一索引、主键索引、组合索引），创建方法，以及聚集索引与非聚集索引的区别。重点讲解了最左匹配规则和索引选择的策略，强调了何时以及如何创建索引来提高查询效率，包括覆盖索引和避免回表的概念。
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%’
小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。
深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！
因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频
如果你觉得这些内容对你有帮助，可以添加下面V无偿领取！（备注Java）
索引
SELECT FROM mytable WHERE username like ‘admin%’
小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。
深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！
因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
[外链图片转存中…(img-tgErLPFE-1710836043165)]
[外链图片转存中…(img-EbbFSh3T-1710836043165)]
[外链图片转存中…(img-D6f6wy8V-1710836043166)]
由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频
如果你觉得这些内容对你有帮助，可以添加下面V无偿领取！（备注Java）
[外链图片转存中…(img-z0erSiFh-1710836043166)]
943
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
