面试官常问：MySQL索引是如何提高查询效率的呢？

MySQL是一个开放源码的关系数据库管理系统，目前为Oracle旗下产品。
被甲骨文公司收购后，MySQL的创始人麦克尔·维德纽斯以MySQL为基础，成立分支计划MariaDB。原先一些使用MySQL的开源软件，部分转向了MariaDB或其它的数据库。
不可否认的是，MySQL由于其性能高、成本低、可靠性好，已经成为最流行的开源数据库之一，随着MySQL的不断成熟，它也逐渐用于更多大规模网站和应用，非常流行的开源软件组合LAMP中的“M”指的就是MySQL。

为什么使用MySQL

在众多开源免费的关系型数据库系统中，MySQL有以下比较出众的优势：

• 运行速度快（据介绍，MySQL8比MySQL5.7运行速度有将近两倍的提升）
• 易使用
• SQL语言支持
• 移植性好
• 功能丰富
• 成本低廉

MySQL索引

数据库是数据表的集合，数据表是数据行和数据列的集合。当你执行一个SELECT语句从数据表中查询部分数据行的时候，得到的就是另外一个数据表和数据行的集合。

当然，我们都希望获得这个新的集合的时间尽可能地短，效率尽可能地高，这就是优化查询。

提升查询速度的技术有很多，其中最重要的就是索引。当你发现自己的查询速度慢的时候，最快解决问题的方法就是使用索引。索引的使用是影响查询速度的重要因素。在使用索引之前其他的优化查询的动作纯粹是浪费时间，只有合理地使用索引之后，才有必要考虑其他优化方式。

索引是如何工作的

首先，在你的MySQL上创建t_user_action_log 表，方便下面进行演示。

CREATE DATABASE `ijiangtao_local_db_mysql` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE ijiangtao_local_db_mysql;

DROP TABLE IF EXISTS t_user_action_log;

CREATE TABLE `t_user_action_log` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` VARCHAR(32) DEFAULT NULL COMMENT '用户名',
  `ip_address` VARCHAR(50) DEFAULT NULL COMMENT 'IP地址',
  `action` INT4 DEFAULT NULL COMMENT '操作：1-登录，2-登出，3-购物，4-退货，5-浏览',
  `create_time` TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 1, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.1', 2, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 1, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.3', 1, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.4', 1, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.1', 1, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 1, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 5, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 2, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 5, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 3, CURRENT_TIMESTAMP);
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values ('LiSi', '8.8.8.2', 4, CURRENT_TIMESTAMP);

假如我们要筛选 action为2的所有记录，SQL如下：

SELECT id, name, ip_address FROM t_user_action_log WHERE `action`=2;

通过查询分析器explain分析这条查询语句：

EXPLAIN SELECT id, name, ip_address FROM t_user_action_log WHERE `action`=2;

分析结果如下：

其中type为ALL表示要进行全表扫描。这样效率无疑是极慢的。

下面为action列添加索引：

ALTER TABLE t_user_action_log ADD INDEX (`action`);

然后再次执行查询分析，结果如下：

那么为什么索引会提高查询速度呢？原因是索引会根据索引值进行分类，这样就不用再进行全表扫描了。我们看到这次查询就使用索引了。加索引前Extra的值是Using Where，加索引后Extra的值为空（使用索引避免全表扫描）。

比如上图，action值为2的索引值分类存储在了索引空间，可以快速地查询到索引值所对应的列。

如何使用

创建索引

① 使用CREATE INDEX创建，语法如下：

CREATE INDEX indexName ON tableName (columnName(length));

例如我们对ip_address这一列创建一个长度为16的索引：

CREATE INDEX index_ip_addr ON t_user_action_log (ip_address(16));

② 使用ALTER语句创建，语法如下：

ALTER TABLE tableName ADD INDEX indexName(columnName);

ALTER语句创建索引前面已经有例子了。下面提供一个设置索引长度的例子：

ALTER TABLE t_user_action_log ADD INDEX ip_address_idx (ip_address(16));
SHOW INDEX FROM t_user_action_log;

③ 建表的时候创建索引：

CREATE TABLE tableName(
  id INT NOT NULL,   
  columnName  columnType,
  INDEX [indexName] (columnName(length))  
);

查看索引

可以通过show语句查看索引：

SHOW INDEX FROM t_user_action_log;

删除索引

使用ALTER命令可以删除索引，例如：

ALTER TABLE t_user_action_log DROP INDEX index_ip_addr;

索引的使用规则

索引由于其提供的优越的查询性能，似乎不使用索引就是一个愚蠢的行为了。但是使用索引，是要付出时间和空间的代价的。因此，索引虽好不可贪多。

下面介绍几个索引的使用技巧和原则，在使用索引之前，你应该对它们有充分的认识。

写操作比较频繁的列慎重加索引

索引在提高查询速度的同时，也由于需要更新索引而带来了降低插入、删除和更新带索引列的速度的问题。一张数据表的索引越多，在写操作的时候性能下降的越厉害。

索引越多占用磁盘空间越大

与没有加索引比较，加索引会更快地使你的磁盘接近使用空间极限。

不要为输出列加索引

为查询条件、分组、连接条件的列加索引，而不是为查询输出结果的列加索引。

例如下面的查询语句：

select ip_address from t_user_action_log
where name='LiSi'
group by action
order by create_time;

所以可以考虑增加在 name action create_time 列上，而不是 ip_address。

考虑维度优势

例如action列的值包含：1、2、3、4、5，那么该列的维度就是5。

维度越高（理论上维度的最大值就是数据行的总数），数据列包含的独一无二的值就越多，索引的使用效果越好。

对于维度很低的数据列，索引几乎不会起作用，因此没有必要加索引。

例如性别列的值只有男和女，每种查询结果占比大约50%。一般当查询优化处理器发现查询结果超过全表的30%的时候，就会跳过索引，直接进行全表扫描。

对短小的值加索引

对短小的值加索引，意味着索引所占的空间更小，可以减少I/O活动，同时比较索引的速度也更快。

尤其是主键，要尽可能短小。

另外，InnoDB使用的是聚集索引（clustered index），也就是把主键和数据行保存在一起。主键之外的其他索引都是二级索引，这些二级索引也保留着一份主键，这样在查询到索引以后，就可以根据主键找到对应的数据行。如果主键太长的话，会造成二级索引占用的空间变大。

比如下面的action索引保存了对应行的id。

为字符串前缀加索引

前边已经讲过短小索引的种种好处了，有时候一个字符串的前几个字符就能唯一标识这条记录，这个时候设置索引的长度就是非常划算的做法。

复合索引的左侧索引

创建复合索引的语法如下：

CREATE INDEX indexName ON tableName (column1 DESC, column2 DESC, column3 ASC);

我们可以看到，最左侧的column1索引总是有效的。

索引加锁

对于InnoDB来说，索引可以让查询锁住更少的行，从而可以在并发情况下拥有更佳表现。

下面演示一下查询锁与索引之间的关系。

前面使用的t_user_action_log表目前有一个id为主键，还有一个二级索引action。

下面这条语句的修改范围是id值为1 2 3 4所在的行，查询锁会锁住id值为1 2 3 4 5所在的行。

update ijiangtao_local_db_mysql.t_user_action_log set name='c1' where id<5;

1、首先创建数据库连接1，开启事务，并执行update语句

set autocommit=0;
begin;
update ijiangtao_local_db_mysql.t_user_action_log set name='c1' where id<5;

2、然后开启另外一个连接2，分别执行下面几个update语句

-- 没有被锁
update ijiangtao_local_db_mysql.t_user_action_log set name='c2' where id=6;
-- 被锁
update ijiangtao_local_db_mysql.t_user_action_log set name='c2' where id=5;

你会发现id=5的数据行已经被锁定，id=6的数据行可以正常提交。

3、连接1提交事务，连接2的id=1和id=5的数据行可以update成功了。

4、如果不使用索引

ip_address没有索引的话，会锁定全表。

连接1开启事务以后commit;之前，连接2对该表的update全部需要等待连接1释放锁。

set autocommit=0;
begin;
update ijiangtao_local_db_mysql.t_user_action_log set name='c1' where ip_address='8.8.8.1';

覆盖索引

如果索引包含满足查询的所有数据，就被称为覆盖索引(Covering Indexes)，覆盖索引非常强大，可以大大提高查询性能。

覆盖索引高性能的原因是：

• 索引通常比记录要小，覆盖索引查询只需要读索引，而不需要读记录。
• 索引都按照值的大小进行顺序存储，相比与随机访问记录，需要更少的I/0。
• 大多数数据引擎能更好的缓存索引，例如MyISAM只缓存索引。

ijiangtao_local_db_mysql表的action列包含索引。使用explain分析下面的查询语句，对于索引覆盖查询(index-covered query)，分析结果Extra的值是Using index，表示使用了覆盖索引 :

explain select `action` from ijiangtao_local_db_mysql.t_user_action_log;

聚簇索引

聚簇索引(Clustered Indexes)保证关键字的值相近的元组存储的物理位置也相同，且一个表只能有一个聚簇索引。

字符串类型不建议使用聚簇索引，特别是随机字符串，因为它们会使系统进行大量的移动操作。

并不是所有的存储引擎都支持聚簇索引，目前InnoDB支持。

如果使用聚簇索引，最好使用AUTO_INCREMENT列作为主键，应该尽量避免使用随机的聚簇主键。

从物理位置上看，聚簇索引表比非聚簇的索引表，有更好的访问性能。

选择合适的索引类型

从数据结构角度来看，MySQL支持的索引类型有B树索引、Hash索引等。

(1) B树索引

B树索引对于<、<=、 =、 >=、 >、 <>、!=、 between查询，进行精确比较操作和范围比较操作都有比较高的效率。

B树索引也是InnoDB存储引擎默认的索引结构。

(2) Hash索引

Hash索引仅能满足=、<=>、in查询。

Hash索引检索效率非常高，索引的检索可以一次定位，不像B树索引需要从根节点到枝节点，最后才能访问到页节点这样多次的I/O访问，所以Hash索引的查询效率要远高于B树索引。但Hash索引不能使用范围查询。

查询优化建议

使用explain分析查询语句

前面已经演示过如何使用explain命令分析查询语句了，这里再解释一下其中几个有参考价值的字段的含义：

select_type

select_type表示查询中每个select子句的类型，一般有下面几个值：

• SIMPLE
  简单SELECT,不使用UNION或子查询等。

• PRIMARY
  查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY。

• UNION
  UNION中的第二个或后面的SELECT语句。

• DEPENDENT UNION
  UNION中的第二个或后面的SELECT语句，取决于外面的查询。

• UNION RESULT
  UNION的结果。

• SUBQUERY
  子查询中的第一个SELECT。

• DEPENDENT SUBQUERY
  子查询中的第一个SELECT，取决于外面的查询。

• DERIVED
  派生表的SELECT, FROM子句的子查询。

• UNCACHEABLE SUBQUERY
  一个子查询的结果不能被缓存，必须重新评估外链接的第一行。

type

type表示MySQL在表中找到所需行的方式，又称“访问类型”，常用的类型有：

all, index, range, ref, eq_ref, const, system, null。

从左到右，性能从差到好。

• all
  Full Table Scan，MySQL将遍历全表以找到匹配的行。

• index
  Full Index Scan，index与ALL区别为index类型只遍历索引树。

• range
  只检索给定范围的行，使用一个索引来选择行。

• ref
  表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

• eq_ref
  类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件。

• const
  当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。
  如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

• null
  MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

key

key列显示MySQL实际决定使用的键（索引），如果没有选择索引，键是NULL。

possible_keys

possible_keys指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上如果存在索引则该索引将被列出，但不一定被查询使用。

ref

ref表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

rows

rows表示MySQL根据表统计信息，以及索引选用的情况，找到所需记录需要读取的行数。这个行数是估算的值，实际行数可能不同。

用好explain命令是查询优化的第一步 !

声明NOT NULL

当数据列被声明为NOT NULL以后，在查询的时候就不需要判断是否为NULL，由于减少了判断，可以降低复杂性，提高查询速度。

如果要表示数据列为空，可以使用0等代替。

考虑使用数值类型代替字符串

MySQL对数值类型的处理速度要远远快于字符串，而且数值类型往往更加节省空间。

例如对于“Male”和“Female”可以用“0”和“1”进行代替。

考虑使用ENUM类型

如果你的数据列的取值是确定有限的，可以使用ENUM类型代替字符串。因为MySQL会把这些值表示为一系列对应的数字，这样处理的速度会提高很多。

CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);

INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
  ('polo shirt','small');

SELECT name, size FROM shirts WHERE size = 'medium';

总结

索引是一个单独的，存储在磁盘上的数据结构，索引对数据表中一列或者多列值进行排序，索引包含着对数据表中所有数据的引用指针。

面试需要掌握那些技能？

1. Java基础知识：包括面向对象编程、集合框架、多线程编程、JVM、测试和调试技术等。

2. 熟悉Spring框架：包括Spring MVC、Spring Boot、Spring Cloud等。

3. 掌握常见的数据库操作技术：如SQL语句、关系型数据库和非关系型数据库等。

4. 熟练使用版本控制工具：如Git等。

5. 对Web开发有一定的了解，熟悉前端相关技术：如HTML、CSS、JavaScript等。

6. 能够写高效的算法，并对数据结构有一定的了解。

7. 有良好的代码习惯，能够编写易于维护和扩展的代码，并理解单元测试和集成测试等概念。

8. 在面试过程中，还需要表达清晰、思路清晰明了、能够准确地回答面试官提出的问题，此外，自信、积极和礼貌也是很重要的。

---

大厂面试官常问的技术核心知识点

1. Java基础知识：Java语言的基本知识，包括数据类型、继承、多态、接口等。

2. 面向对象编程：对面向对象编程原则和设计模式的理解，如单例、工厂、观察者、策略等。

3. 数据库知识：对关系型数据库和非关系型数据库操作的熟悉程度，掌握SQL语言，了解事务管理机制，并清楚地描述ORM框架的使用场景及实际操作。

4. Web开发：Web开发相关技术，例如Servlet、JSP、Spring MVC、JSON、RESTful API等。熟悉HTTP/HTTPS协议以及网络通信机制。

5. 常用框架：Spring、Hibernate、MyBatis等框架，尤其是Spring框架，深入理解Spring IOC，AOP等核心原理，知道如何配置基础设施组件，如事务管理、缓存等基础组件。

6. 分布式系统架构：分布式系统相关技术，如Dubbo、Zookeeper等，对微服务架构模式有一定的了解，熟悉分布式锁、分布式缓存、分布式数据存储等高可用性方案。

7. 性能排查：了解性能优化的方法，包括代码和SQL调优等，并且熟悉性能监测和分析工具，例如掌握JVM内存结构及堆栈排查技术。

8. 算法和数据结构：有基本的算法和数据结构知识，例如排序、查找、哈希表等。

---

我最近整理了一些小伙伴们发给我的面试题以及我的一些最新的面试等学习资料，有需要的小伙伴可以找我领取下。或者点击 → 《最新Java后端全套VIP面试学习资源》直接获取以下Java后端架构VIP进阶学习面试资料。

资料里面包含了：Java基础、MySQL、jvm、分布式、性能优化、spring 、spring boot、spring cloud、 MyBatis、Netty源码分析、算法、乙级高并发、Redis、dubbo、Tomcat、集合框架、锁、MQ、百万简历模板等等学习视频资料。

资料如图展示：（知识其中一部分）

同时也欢迎大家关注公众号【Java烂猪皮】，回复【666】，获取最新Java后端架构VIP学习资料以及视频学习教程，然后一起学习，一文在手，面试我有。

 看完三件事❤️

• 如果你觉得这篇内容对你还蛮有帮助，我想邀请你帮我三个小忙：
• 点赞，转发，有你们的 『点赞和评论』，才是我创造的动力。
• 关注作者公众号 『 java烂猪皮 』，不定期分享原创知识。
• 关注后回复【666】扫码即可获取学习资料包。
• 同时可以期待后续文章ing🚀。