Java最全史上最全MySQL索引详解，你想要的都在这里，吊打面试官，啃完999页Java面试高频宝典

本文链接：https://blog.csdn.net/2401_84688180/article/details/138678815

首先分享一份学习大纲，内容较多，涵盖了互联网行业所有的流行以及核心技术，以截图形式分享：

（亿级流量性能调优实战+一线大厂分布式实战+架构师筑基必备技能+设计思想开源框架解读+性能直线提升架构技术+高效存储让项目性能起飞+分布式扩展到微服务架构…实在是太多了）

其次分享一些技术知识，以截图形式分享一部分：

Tomcat架构解析：

算法训练+高分宝典：

Spring Cloud+Docker微服务实战：

最后分享一波面试资料：

切莫死记硬背，小心面试官直接让你出门右拐

1000道互联网Java面试题：

Java高级架构面试知识整理：

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 2690100 | 11.11 | Using where |

±—±------------±------±-----------±-----±--------------±-----±--------±-----±--------±---------±------------+

1 row in set, 1 warning (0.00 sec)

mysql> alter table s1 add primary key(id);

Query OK, 0 rows affected (16.25 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select * from s1 where id > 1 and id < 1000000; #有聚集索引，预估需要检索的rows数如下

±—±------------±------±-----------±------±--------------±--------±--------±-----±--------±---------±------------+

±—±------------±------±-----------±------±--------------±--------±--------±-----±--------±---------±------------+

| 1 | SIMPLE | s1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1343355 | 100.00 | Using where |

±—±------------±------±-----------±------±--------------±--------±--------±-----±--------±---------±------------+

1 row in set, 1 warning (0.09 sec)

4.2辅助索引

就是我们在查询的时候，where后面需要写id之外的其他字段名称来进行查询，比如说是where name=xx，没法用到主键索引的效率，怎么办，就需要我们添加辅助索引了，给name添加一个辅助索引。

表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引）（unique key啊、index key啊），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。

叶子节点存放的是对应的那条数据的主键字段的值，除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark），其实这个书签你可以理解为是一个{‘name字段’，name的值，主键id值}的这么一个数据。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。如果我们select 后面要的是name，我们直接就可以在辅助索引的叶子节点找到对应的name值，比如：select name from tb1 where name=‘xx’；这个xx值你直接就在辅助索引的叶子节点就能找到，这种我们也可以称为覆盖索引。如果你select后面的字段不是name，例如：select age from tb1 where name=‘xx’；也就是说，我通过辅助索引的叶子节点不能直接拿到age的值，需要通过辅助索引的叶子节点中保存的主键id的值再去通过聚集索引来找到完整的一条记录，然后从这个记录里面拿出age的值，这种操作有时候也成为回表操作，就是从头再回去查一遍，这种的查询效率也很高，但是比覆盖索引低一些，再说一下昂，再辅助索引的叶子节点就能找到你想找的数据可称为覆盖索引。

5.MySQL索引管理

5.1功能

#1. 索引的功能就是加速查找

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

5.2MySQL常用的索引

普通索引INDEX：加速查找

唯一索引：

-主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）

-唯一索引UNIQUE:加速查找+约束（不能重复）

联合索引：

-PRIMARY KEY(id,name):联合主键索引

-UNIQUE(id,name):联合唯一索引

-INDEX(id,name):联合普通索引

索引操作：

添加主键索引:

创建的时候添加: 添加索引的时候要注意,给字段里面数据大小比较小的字段添加,给字段里面的数据区分度高的字段添加.

聚集索引的添加方式

创建的是添加

Create table t1(

Id int primary key,

)

Create table t1(

Id int,

Primary key(id)

)

表创建完了之后添加

Alter table 表名 add primary key(id)

删除主键索引:

Alter table 表名 drop primary key;

唯一索引:

Create table t1(

Id int unique,

)

Create table t1(

Id int,

Unique key uni_name (id)

)

表创建好之后添加唯一索引:

alter table s1 add unique key u_name(id);

删除:

Alter table s1 drop index u_name;

普通索引:

创建:

Create table t1(

Id int,

Index index_name(id)

)

Alter table s1 add index index_name(id);

Create index index_name on s1(id);

删除:

Alter table s1 drop index u_name;

DROP INDEX 索引名 ON 表名字;

各种索引的应用场景：

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

这个系统有一个会员表

有下列字段：

会员编号 INT

会员姓名 VARCHAR(10)

会员身份证号码 VARCHAR(18)

会员电话 VARCHAR(10)

会员住址 VARCHAR(50)

会员备注信息 TEXT

那么这个会员编号，作为主键，使用 PRIMARY

会员姓名如果要建索引的话，那么就是普通的 INDEX

会员身份证号码如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）

#除此之外还有全文索引，即FULLTEXT

会员备注信息，如果需要建索引的话，可以选择全文搜索。

用于搜索很长一篇文章的时候，效果最好。

用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索。

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

5.3索引的两大类型hash与btree

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

hash类型的索引：查询单条快，范围查询慢

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 等索引；

5.4创建/删除索引的语法

#方法一：创建表时

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 表名字;

看下面的示范：

#方式一

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 index ix_age on t1(age);

#方式三

alter table t1 add index ix_sex(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

6.测试索引

6.1准备

#1. 准备表

create table s1(

id int,

name varchar(20),

gender char(6),

email varchar(50)

);

#2. 创建存储过程，实现批量插入记录

delimiter $KaTeX parse error: Expected 'EOF', got '#' at position 2: #̲声明存储过程的结束符号为$

create procedure auto_insert1()

BEGIN

declare i int default 1;

while(i<3000000)do

insert into s1 values(i,‘egon’,‘male’,concat(‘egon’,i,‘@oldboy’));

set i=i+1;

end while;

END $KaTeX parse error: Expected 'EOF', got '#' at position 2: #̲$ 结束

delimiter ; #重新声明分号为结束符号

#3. 查看存储过程

show create procedure auto_insert1\G

#4. 调用存储过程

call auto_insert1();

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

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

mysql> select * from s1 where id=333333333;

Empty set (0.33 sec)

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

或者用alter table s1 add primary key(id);加主键，建索引很慢的。

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

1. mysql先去索引表里根据b+树的搜索原理很快搜索到id等于333333333的记录不存在，IO大大降低，因而速度明显提升

2. 我们可以去mysql的data目录下找到该表，可以看到占用的硬盘空间多了

3. 需要注意，如下图

6.5总结

#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个字节，类型为长整型.

你想一下，是不是全部加索引就一定好的呢，首先，我们加上索引，查询时快了，但是写入的时候就慢了，还记得吗，每次插入新的记录，你的整个索引结构都会跟着改，所以如果你乱加索引，你会发现，即便是你的网站没有几个人在注册，或者说没有几个写入数据的操作，你的磁盘IO会居高不下，磁盘在疯狂的转，因为你每插入一条数据，我们的索引都需要重新建，重建的索引要写入硬盘里面的，还记得我们给那三百万条数据建索引的时候的速度吗？每次都要把所有的数据取出来，做好一个数据结构，然后再写回硬盘，也就是要经历很多的IO才能实现这个事儿，所以乱加索引的一个弊端就是，你很少的写入都会导致你的磁盘IO非常的高，导致效率很差，所以我们要学一下怎么正确的加索引。

7.正确使用索引

7.1索引未命中

并不是说我们创建了索引就一定会加快查询速度，****若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题

1 范围问题，或者说条件不明确，条件中出现这些符号或关键字：****>、>=、<、<=、!= 、between…and…、like、

大于号、小于号

如果你写where id >1 and id <1000000;你会发现，随着你范围的增大，速度会越来越慢，会成倍的体现出来。

不等于！=

between …and…

like #测like的时候，你可以先把id的主键索引去掉，然后测一下like，然后加上index key，再测一下。就知道你忘了，看语句：create index email_index on s1(email) ，email_index是索引名、on、 s1是表名（字段名），你会发现建索引的时间也是很慢的，desc s1;查看一下索引是不是创建成功了，看那个key字段是不是有个mul，有这个说明创建成功了。

like=后面如果没有那些特殊字符，通配符之类的，就跟等于是一个效果，精确匹配。

#使用like的时候，通配符写在最前面，也是需要全匹配一遍，然后在比较字符串的第二个字符，最左匹配的规则，还记得吗。

2 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

#先把表中的索引都删除，让我们专心研究区分度的问题

mysql> desc s1;

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

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

| id | int(11) | YES | MUL | NULL | |

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

4 rows in set (0.00 sec)

mysql> drop index a on s1;

Query OK, 0 rows affected (0.20 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> drop index d on s1;

Query OK, 0 rows affected (0.18 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc s1;

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

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

| id | int(11) | YES | | NULL | |

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

4 rows in set (0.00 sec)

我们编写存储过程为表s1批量添加记录，name字段的值均为egon，也就是说name这个字段的区分度很低（gender字段也是一样的，我们稍后再搭理它）

回忆b+树的结构，查询的速度与树的高度成反比，要想将树的高低控制的很低，需要保证：在某一层内数据项均是按照从左到右，从小到大的顺序依次排开，即左1<左2<左3<…

而对于区分度低的字段，无法找到大小关系，因为值都是相等的，毫无疑问，还想要用b+树存放这些等值的数据，只能增加树的高度，字段的区分度越低，则树的高度越高。极端的情况，索引字段的值都一样，那么b+树几乎成了一根棍。本例中就是这种极端的情况，name字段所有的值均为’egon’

#现在我们得出一个结论：为区分度低的字段建立索引，索引树的高度会很高，然而这具体会带来什么影响呢？？？

#1：如果条件是name=‘xxxx’,那么肯定是可以第一时间判断出’xxxx’是不在索引树中的（因为树中所有的值均为’egon’,看第一条的时候就知道你不在索引树里面了），所以查询速度很快

#2：如果条件正好是name=‘egon’,查询时，我们永远无法从树的某个位置得到一个明确的范围，只能往下找，往下找，往下找。。。这与全表扫描的IO次数没有多大区别，所以速度很慢

3 =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

4 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)

把上面的条件写成 where id = 3000/3;你会发现速度变得很快，因为等于号后面的数字，是在比较之前就计算出来了，不需要每次都计算一次每次都计算一次了，跟直接等于一个常数是一样的，所以很快。结论是不要让你的索引字段参与到计算中。

5 and/or

#1、and与or的逻辑

条件1 and 条件2:所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立

条件1 or 条件2:只要有一个条件成立则最终结果就成立

#2、and的工作原理

条件：

a = 10 and b = ‘xxx’ and c > 3 and d =4

索引：

制作联合索引(d,a,b,c)

工作原理: #如果是你找的话，你会怎么找，是不是从左到右一个一个的比较啊，首先你不能确定a这个字段是不是有索引，即便是有索引，也不一定能确保命中索引了（所谓命中索引，就是应用上了索引），mysql不会这么笨的，看下面mysql是怎么找的：

索引的本质原理就是先不断的把查找范围缩小下来，然后再进行处理，对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照d—>a->b->c的顺序

#3、or的工作原理

条件：

a = 10 or b = ‘xxx’ or c > 3 or d =4

索引：

制作联合索引(d,a,b,c)

工作原理:

只要一个匹配成功就行，所以对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d

索引要加在数据区分度高的字段上

在左边条件成立但是索引字段的区分度低的情况下（name与gender均属于这种情况），会依次往右找到一个区分度高的索引字段，加速查询

经过分析，在条件为name=‘egon’ and gender=‘male’ and id>333 and email='xxx’的情况下，我们完全没必要为前三个条件的字段加索引，因为只能用上email字段的索引，前三个字段的索引反而会降低我们的查询效率

6 最左前缀匹配原则（详见第八小节），非常重要的原则，对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了，有索引速度也慢)，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

7 其他情况

使用函数

select * from tb1 where reverse(email) = ‘egon’;

类型不一致

如果列是字符串类型，传入条件是必须用引号引起来，不然…

select * from tb1 where email = 999;

#排序条件为索引，则select字段必须也是索引字段，否则无法命中

order by

select name from s1 order by email desc;

当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢

select email from s1 order by email desc;

特别的：如果对主键排序，则还是速度很快：

select * from tb1 order by nid desc;

组合索引最左前缀

如果组合索引为：(name,email)

name and email – 命中索引

name – 命中索引

email – 未命中索引

count(1)或count(列)代替count(*)在mysql中没有差别了
create index xxxx on tb(title(19)) #text类型，必须制定长度

其他注意事项

避免使用select *
count(1)或count(列) 代替 count(*)
创建表时尽量时 char 代替 varchar
表的字段顺序固定长度的字段优先

Java核心架构进阶知识点

面试成功其实都是必然发生的事情，因为在此之前我做足了充分的准备工作，不单单是纯粹的刷题，更多的还会去刷一些Java核心架构进阶知识点，比如：JVM、高并发、多线程、缓存、Spring相关、分布式、微服务、RPC、网络、设计模式、MQ、Redis、MySQL、设计模式、负载均衡、算法、数据结构、kafka、ZK、集群等。而这些也全被整理浓缩到了一份pdf——《Java核心架构进阶知识点整理》，全部都是精华中的精华，本着共赢的心态，好东西自然也是要分享的