Mysql面试过关！（详解：索引+常用引擎+常见问题+sql调优）_mysql加索引sql

一、Mysql索引

索引如同书籍的目录一般，在对Mysql数据进行查询时如若不加索引，则会进行遍历查询，而当数据量过大时其效率将会极低

·

1、添加索引sql语句

`
· 添加主键（PIAMARY KEY）索引

mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
1

· 添加唯一（UNIQUE）索引：不允许被索引的数据列中出现重复的值

mysql>ALTER TABLE `table_name` ADD UNIQUE( `column` ) 
mysql>CREATE UNIQUE INDEX index_name ON table_name( `column` ) 
1
2

· 添加普通（INDEX）索引：仅加快数据访问速度

mysql>ALTER TABLE `table_name` ADD INDEX index_name( `column` ) 
mysql>CREATE INDEX index_name ON table_name( `column` ) 
1
2

· 添加全文（FULLTEXT）索引：当字段存放多个单词组成的较大段文字时如like模糊查询，用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数据记录。

mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column` ) 
mysql>CREATE FULLTEXT INDEX index_name ON table_name( `column` ) 
1
2

· 添加联合索引：索引可覆盖多个数据列，按照最左前缀索引

mysql>ALTER TABLE `table_name` ADD INDEX index_name(  `column1`, `column2`, `column3`) 
mysql>CREATE INDEX index_name ON table_name(  `column1`, `column2`, `column3`) 
1
2

·

2、查看MySQL中建立的索引是否生效

EXPLAIN

EXPLAIN SELECT surname,first_name form a,b WHERE a.id=b.id
1

其返回的信息中，key列如果为空则表示未使用索引，如果有值则表示使用了索引

八种访问类型：system、const、eq_ref、ref、range、index、all、null
最好到最差的顺序:system>const>eq_ref>ref>range>index>all
最全的访问顺序:system>const>eq_ref>ref>fulltext>ref_or_null>index_merge> > unique_subquery>index_subquery>range>index>all
在项目使用中
至少优化到range和ref

3、索引失效场景（补充：以下在实际应用中并不会一定导致索引失效，基于mysql不同版本的优化规则）

3.1 在联合索引的场景下，查询条件不满足最左匹配原则

在创建联合索引时，where子句中使用最频繁的字段放在组合索引的最左侧。
而在查询时，要想让查询条件走索引，则需满足：最左边的字段要出现在查询条件中。

问：为什么联合索引要遵循最左匹配原则呢？

假设上述图中的数据表，我们对 b、c、d字段建立联合索引，那么根据图二的排序 我们知道顺序 是 1111a 2222b 所以 在第三层 我们可以看到 1111a 在第一层 2222b在第二层 因为 111 < 222 所以 111 进入第二层 然后得出第一层

答：索引的底层是一颗B+树，MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序，也就是说联合索引中每一个字段的搜索实际上都会依赖于前一个字段，在查询时只有先基于最左边的字段才能进入到索引树中进一步进行查询。如上图，如果查询where c=1 and d=2，那么查询的值就相当于*11，匹配到所有数据，如果查询where b=1 and c=1 那么查询的值就相当于 11*

MySQL 8.0 最左前缀会被 索引跳跃扫描 的方式打破，当最左字段索引的唯一值较少时，即使where条件没有第一列索引，查询的时候也可以用到索引

3.2 索引列参与了运算，会导致全表扫描，索引失效（除主键索引外）

create index index_test2 on test_module (CRT_USER_ID);
explain select * from test_module where CRT_USER_ID= 2-1;

where a = 2 - 1 会走索引
where a - 1 = 1 不会走索引
看出区别了不，第一条是右边是定值，第二条左边是需要动态计算的。

3.3 模糊查询时（like语句），模糊匹配的占位符位于条件的首部

like ‘%abc’；
like ‘%abc%’；
索引本身就相当于目录，从左到右逐个排序，而条件的左侧使用了占位符，导致无法按照正常的目录进行匹配
这个原理和最左前缀匹配有相似之处，而且即便百分号放在右边，如果查询的范围会过大，MySQL也可能判断不走索引。

可以使用 INSTR(table.name, #{name}) 来代替like，效率会更快！

3.4 索引列参使用了函数

因为索引保存的是索引字段的原始值，而不是经过函数运算后的值，所以没有办法走索引。MySQL 8.0提供了 函数索引，针对函数计算后的值建立一个索引。

3.5 参数类型与字段类型不匹配，导致类型发生了隐式转换，索引失效

3.6 查询条件使用or关键字，其中一个字段没有创建索引，则会导致整个查询语句索引失效； or两边为“>”和“<”范围查询时，索引失效

两边字段都加上索引则一定会走索引

3.7 两列数据做比较，即便两列都创建了索引，索引也会失效

id和age都存在索引；
explain select * from t_user where id > age;索引失效。

3.8 查询条件使用不等进行比较时，需要慎重，普通索引会查询结果集占比较大时索引会失效。

3.9 查询条件使用is null时正常走索引，使用is not null时，不走索引

3.10 查询条件使用not in、not exists时，如果是主键则走索引，如果是普通索引，则索引失效

3.11 当查询条件涉及到order by、limit等条件时，是否走索引情况比较复杂，而且与Mysql版本有关，通常普通索引，如果未使用limit，则不会走索引。order by多个索引字段时，可能不会走索引，主键会走索引。其他情况，建议在使用时进行expain验证。

order by如果走索引，索引是排好序的，但是所要返回的值可能需要回表，因此MySQL会认为全表扫描是更好的选择。

3.12 当查询条件为大于等于、in等范围查询时，根据查询结果占全表数据比例的不同，优化器有可能会放弃索引，进行全表扫描。

当Mysql发现通过索引扫描的行记录数超过全表的10%-30%时，优化器可能会放弃走索引，自动变成全表扫描。某些场景下即便强制SQL语句走索引，也同样会失效。
类似的问题，在进行范围查询（比如>、< 、>=、<=、in等条件）时往往会出现上述情况，而上面提到的临界值根据场景不同也会有所不同。

·

4、Mysql索引数据结构

● 二叉树索引
● 红黑树索引
● Hash表索引
● B-Tree索引
● B+Tree索引

注：树结构存储时，其根节点一般已经放在内存中

·

4.1 二叉树索引

缺点：
当二叉树所展现的数据逐行递增或递减时，其本质上就是一个链表的数据结构，则其查询效率和未建二叉树索引时效率一样。

·

·

4.2 红黑树索引（二叉平衡树）

在二叉树的基础上实现了自旋，即当一边数据过多时会自动进行分裂平衡，改进了二叉树递增后变成链表的缺点。

缺点：
当树的深度（log2N）较大时，其查询效率依然很低！

·

·

4.3 B-Tree索引（多叉平衡树）

为了解决红黑树深度上的缺点，B树为了控制树的深度，将每一个节点进行横向扩展，其每个节点中的数据索引从左向右递增排列。

注：在MySQL中每个节点所能存储的最大容量为16KB

缺点：
在进行范围查询时，需要每次将根节点进行查询的数据放在一个数据表中，待所有查询结束后将数据表返回。（查询次数及IO消耗过多）

·

·

4.4 B+Tree索引（B-Tree优化）

与B-Tree相比，B+Tree非叶子节点中不存储data，只存储索引+冗余，目的是在每个节点所限制的16KB大小中能存储更多的索引。我们常说的二叉树指的是一个结点可以发散出两个新的结点。m叉树一个节点能指向m个新的结点。这个指向新节点的操作就叫扇出（fanout）。而B+树能指向上千个节点，扇出非常高。

注：每个索引冗余所占大小为6B

为改进B-Tree范围查询的缺点，其叶子节点之间有指针相连，那么通过B+Tree进行范围查询时只需要通过一次从根节点进行查询，在找到相应数据后根据数据末尾的指针指向的数据即可直接得到范围内所有匹配的数据，无需从头再进行查询。

·

·

4.5 Hash索引

Hash索引通过Hash算法将所有数据直接以其指纹保存，它的查找速度是所有索引中最快的，但仅限于等值查找，当进行范围查询或模糊查询时就无法提供良好的查询支持。

·

·

二、MySQL常用引擎

● MyISAM引擎
● InnoDB引擎

1、MyISAM引擎（非聚集索引）

其叶子节点中的data保存数据存放的地址，保存的文件如下图所示

每张表存储在磁盘中分为：

· frm文件：存储表结构和字段
· MYD文件：存储数据
· MYI文件：存储索引

特性：

· 支持全文索引
· 内置计数器，执行count("*")查看表行数时只需要读出计数器数值，速度极快，但不能有WHERE条件
· 不支持事务
· 不支持行级锁，只支持表级锁，因其默认写请求比读请求优先级高，不适用于大量的查询和修改并存的情况
· 不支持外键
1
2
3
4
5

·

·

2、InnoDB引擎（聚集索引）

其叶子节点中的data保存实体数据内容，保存的文件如下图所示

每张表存储在磁盘中分为：

· frm文件：存储表结构和字段
· ibd文件：存储索引+数据（即Myisam中MYI文件+MYD文件）

特性：

· 支持事务
· 支持行级锁和表级锁，其行级锁实现在索引上，如果查询中未使用索引则自动转为表级锁
· 支持外键
· 5.7版本后支持全文索引
· 因其事务特性，用计数器保存表行数将会进行过多的消耗，所以InnoDB中查询表行数需要全表扫描
· 必须有主键
1
2
3
4
5
6

·

·

三、常见问题

1、聚集索引和非聚集索引区别

在MyISAM存储引擎中，索引与数据分开存储即为非聚集索引
在InnoDB存储引擎中，索引与数据存储在一起即为聚集索引

·

·

2、为何MySQL建议表主键为整型自增

· 整型：
在进行查询时，对整型数值快速计算大小消耗低，其磁盘占用空间也相对较小。

· 自增：
叶子节点其索引元素从左到右是一个递增的趋势，非自增时，中间插入一个索引，当插入在相应的节点后其存储大小超过最大限制16KB时，则会进行分裂平衡，将会造成一定的消耗。而自增时，则只会有很小的概率造成分裂平衡。

·

·

3、禁止使用select * 语句

【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。 说明:
1)增加查询分析器解析成本。
2)增减字段容易与 resultMap 配置不一致。
3)无用字段增加网络 消耗，尤其是 text 类型的字段。
4）为了获取不需要的数据可能会首先从辅助索引过滤数据再通过聚集索引获取所有列，就多了一次B+树的查询，也就是回表的性能消耗
1
2
3
4
5

禁止使用select * 语句可能会带来的附带好处就是：某些情况下可以走覆盖索引。确定查询的列都存在索引（包含联合索引），where条件也都存在索引，则会走覆盖索引.

select * 是否走索引取决于后面的where条件查询的范围，当MySQL判断返回结果集较大时则会认为使用全表扫描更合适。

·

·

4、关于MySQL的单表行数最大值问题

我们知道一般数据库单表建议最大2千万条数据，超过后性能会下降，建议分库分表。
那么这个2千万数据是如何而来的呢？

4.1 理论上单表行数最大值依赖于主键的大小

int 32位 能支持2^32-1，也就是21个亿左右。
bigint 那就是2^64-1，但这个数字太大，一般还没到这个限制之前，磁盘先受不了。
tinyint 一个字节8位，最大2^8-1，支持255个数据量

4.2 除了主键外，索引也会影响行数

假设我们有这么一张user数据表。

上面user表数据，放在了user.ibd文件下。含义是user表的innodb data文件，又叫表空间。
虽然在数据表里，它们看起来是挨在一起的。但实际上在user.ibd里他们被分成很多小份的数据页，每份大小16k。

页的存储结构：
每个页只能存放16K的数据，数据表里的数据被分割成多个页进行存放，通过页号（表空间的地址偏移量）标识具体是哪一页，在页头里引入前后指针，指向前后的页，将这些数据页关联起来。页尾中加入了校验码，用来保证数据页的正确性（应对数据进行写操作时的意外场景）。剩余的空间都用来存放表数据。

在查询数据的时候也不可能对每个页遍历去查询，效率太低了，那么B+树的出现就是为了解决这个问题，取出每个数据页里的最小主键id以及对应页号组成一个新的数据页，作为索引数据，也就是上面说的非叶子节点。

如下图，当我们想要查询id为5的主键，则从上向下顺着页地址查询了三个页，也就是发生了三次磁盘IO查询。

假设
● 非叶子结点内指向其他内存页的指针数量为x（整个页16k除去页头页尾等，大约有15k的空间存放指针数量，每个指针由主键和页号组成大约12Byte，那么相除后大约有1280页）
● 叶子节点内能容纳的数据为y（叶子节点和非叶子节点的数据结构是一样的，也有15k的空间用于存放每条数据，假设一条数据1kb，则可以存放15条）
● B+树的层数为z

这棵B+树放的行数据总量等于 (x ^ (z-1)) * y = (1280 ^ (3-1)) * 15 ≈ 2.5kw
而三层数据页对应三次磁盘IO是数据读取较为合理的场景。但如果叶子节点存放的每条数据要少于1kb的话，则这个数据总量也可以大于2千万，所以存放数据的多少并不一定能够影响查询效率。

再说说 B树（B-树），主要区别在于非叶子节点中除了指针外还存放了主键对应的行数据，那么x的值就变成了约15条，那么套用公式后就变成了15 ^z，要达到两千万的数据量最少要6层数，最坏情况需要进行六次磁盘IO，在性能上与B+数差距较大。

·

四、关于SQL调优

1、标准sql执行顺序

正常情况下是先join再进行where过滤。

2、查看mysql对sql的自动优化语句

在命令窗口执行

show warnings;
1

3、优化总结

1、mysql嵌套子查询效率比较低，可以优化成连接查询

2、连接表时，可以先用where条件对表进行过滤，然后做表连接

3、建立合适的索引，必要时建立多列联合索引

当单个列过滤之后还有很多数据，那么索引的效率将会比较低，即列的区分度较低，那么如果在多个列上建立索引，那么多个列的区分度就大多了，将会有显著的效率提高。
1

4、学会分析sql执行计划，mysql会对sql进行优化，所以分析执行计划很重要

5、根据业务场景建立覆盖索引

只查询业务需要的字段，如果这些字段被索引覆盖，将极大的提高查询效率
1

6、多表连接的字段上需要建立索引 这样可以极大的提高表连接的效率

7、where条件字段上需要建立索引

8、排序字段上需要建立索引

9、分组字段上需要建立索引

10、Where条件上不要使用运算函数，以免索引失效

11、列类型尽量定义成数值类型，且长度尽可能短