java面试之Mysql索引

java面试之Mysql索引

1.索引

索引是帮助数据库高效获取数据的排好序的数据结构。

优点：

• 索引可以加快数据检索速度，减少I/O次数，提高系统的性能。

缺点：

• 创建索引和维护索引要耗费时间。当对表中的数据进行增删改时，索引也要动态的维护。
• 索引需要占物理空间。

索引类型

存储结构

• 使用B tree数据结构来存储数据，适用于全键值，键值范围，键前缀查找(只适用于根据最左前缀的查找)。

• 全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索。

• 哈希索引：基于哈希表实现，只有精确匹配索引所有列的查询才有效。

• 空间数据索引(R-tree)：myisam支持空间索引，可以用作地理数据存储，R-tree无须前缀索引。空间索引会从所有维度来索引数据查询时，可以有效地使用任意维度来组合查询。

应用层次

• 主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
• 唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
• 普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。
• 组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并
• 覆盖索引：查询列要被所建的索引覆盖，不必读取数据行

索引的操作

创建索引

在执行CREATE TABLE时创建索引

#CREATE TABLE时创建索引
CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX  `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#ALTER TABLE命令添加索引
ALTER TABLE 表名 ADD INDEX 索引名 (列名);
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名 (列名);
ALTER TABLE 表名 ADD PRIMARY KEY (列名);
ALTER TABLE 表名 ADD INDEX 索引名 (字段名1，字段名2...);
ALTER TABLE 表名 ADD FULLTEXT 索引名 (字段名); 
#使用CREATE INDEX命令创建
CREATE INDEX 索引名 ON 表名 (列名);
CREATE UNIQUE INDEX 索引名 ON 表名 (列名); 
CREATE INDEX 索引名 ON 表名 (字段名1，字段名2...); 
CREATE FULLTEXT INDEX 索引名 ON 表名 (字段名); 

删除索引

DROP INDEX 索引名 ON 表名;
ALTER TABLE 表名 DROP INDEX 索引名;
#列的属性还带有AUTO_INCREMENT，那么要先将这个列的自动增长属性去掉，才可以删除主键。
ALTER TABLE 表名 MODIFY 主键列 INT;
ALTER TABLE 表名 DROP PRIMARY KEY;

查看索引

#查看已创建的索引：
show index from 表名;

组合索引

组合索引是两个或更多个列上的索引。Index (A, B, C),当三列在where中出现的顺序如(A,B,C) 、(A,B)、(A)才能用到索引。

在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

• A=5 AND B=6 AND C=7 查询效率最高，索引全覆盖
• A=5 AND B=6 索引覆盖A和B
• B=6 AND A=5 经过mysql的查询分析器的优化，索引覆盖A和B
• A=5 索引覆盖A
• B=6 AND C=7 没有A列，不走索引，索引失效
• C=7 没有A列，不走索引，索引失效
• B>5 没有A列，不走索引，索引失效
• A=5 AND B>6 AND C=2 索引覆盖A和B，B是范围查询，因此C列不能走索引
• A>5 AND B=2 A列走索引，范围查询，因此B列是无法使用索引
• A>5 A列走索引
• A=5 AND B=6 order by C 索引覆盖A和B，C排序
• A=5 AND B>6 order by C A、B列使用索引查找，B列是范围查询，因此C列不能使用索引排序，会出现file sort
• A>5 ORDER BY B A列使用索引查找，A列是范围查询，因此B列不能使用索引排序，会出现file sort
• A IN(1,2) ORDER BY B 理由同上
• A>5 ORDER BY A A列走索引排序
• ORDER BY B 不能索引排序
• ORDER BY A ASC, B DESC 不走排序
• ORDER BY A DESC, B DESC A、B列排序
• ORDER BY A A列排序
• A=5 ORDER BY B A列走索引，B列排序

索引查找遵循最左侧原则。遇到范围查询列之后的列索引失效。

排序也能使用索引，合理使用索引排序，避免出现file sort。

索引不适合哪些场景

• 查询中很少使用的列不应该创建索引
• 更新比较频繁的也不适合加索引
• 区分度低的字段不适合加索引（如性别）
• 定义为text、image和bit的列不适合加索引

对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。
每张表里面理论上最多可创建16个索引。

索引适合场景

• 定义主键的数据列要建立索引
• 定义有外键的数据列要建立索引
• 频繁作为查询条件的字段应该创建索引
• 经常需要使用WHERE子句的列上创建索引
• 经常被join on的字段适合建立索引
• 经常需要排列的列上创建索引
• 经常需要根据范围进行搜索的列上创建索引
• 尽量选择区分度高的列作为索引

索引注意事项

• 返回自己想到的数据列，少用select *
• 索引列的数据长度能少则少
• 尽量使用短索引
• 在数据库设计时不要让字段的默认值为NULL
• 在where字句中避免使用 != 或<>、or、in、is null、前置%(like ‘%abc%’)、对字段进行表达式、函数操作、算术等操作
• 字段类型是字符串，where时一定用引号括起来
• 将应用频度高的字段放置在组合索引的前面，
• 尽量减少子查询，使用关联查询（left join, right join, inner join）代替
• 减少使用IN或者NOT IN，使用exists,not exists或者关联查询语句代替
• 尽量用union或者union all代替or
• count(1)或count(列) 代替 count(*)
• 当数据量大时，避免使用where 1=1的条件
• 将不需要的记录在GROUP BY 之前过滤掉，避免使用HAVING子句
• 增加中间表进行优化（统计报表的场景，后台开定时任务将数据先统计好，尽量不要在查询的时候去统计）
• 合理的增加冗余的字段（减少表的关联查询）

2.执行计划

概述

使用EXPLAIN 关键字可以模拟优化器执行SQL 查询语句。

字段	字段含义
id	操作表的顺序。id 相同，执行顺序由上至下；id不同，id值越大优先级越高，越先被执行；id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中
select_type	查询类型
table	查询的数据表
type	显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL
possible_keys	显示可能应用在这张表中的索引
key	显示MySQL在查询中实际使用的索引。如果为NULL，则没有使用索引
key_len	使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref	显示索引的哪一列被使用了，如果可能的话，是一个常数
rows	估算返回请求数据的行数。越少越好
Extra	查询的额外信息

select_type字段	字段含义
SIMPLE	不包含任何子查询或union等查询
PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
SUBQUERY	在select或 where字句中包含的查询，单个值
DEPENDENT SUBQUERY	在select或 where字句中包含的查询，一组值
DERIVED	from字句中包含的查询
UNION	出现在union后的查询语句中
UNION RESULT	从UNION中获取结果集

Extra字段	字段含义
Using filesort	对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取
Using temporary	使用了临时表保存中间结果，常见于order by和分组查询group by
Using where	表明使用了where过滤
Using index	操作中使用覆盖索引避免访问表的数据行。如果出现using where，索引用来执行索引键值的查找；如果没有出现using where，索引用来读取数据而非执行查找
Using join buffer	使用了连接缓存,如在查询的时候会有多次join,则可能会产生临时表
impossible where	where子句的值总是false
select tables optimized away	仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
distinct	优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

type字段	字段含义
system	表仅有一行(系统表)，const 类型的特列
const	表最多有一个匹配行,它将在查询开始时被读取。因为只有一行，这个值实际就是常数
eq_ref	唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行
range	检索给定范围的行
index	遍历索引树
ALL	全表扫描

#在select语句前加上EXPLAIN即可
EXPLAIN SELECT * FROM projectfile WHERE fileuploadercode='1';

慢查询定位

SHOW VARIABLES LIKE 'slow_query_log%';
SET global slow_query_log = ON; 
SET global slow_query_log_file = 'slow_query_log.log'; 
#记录不使用索引的查询
SET global log_queries_not_using_indexes = ON; 
#记录超过查询时间阈值，单位秒
SET long_query_time = 1;

#使用 mysqldumpslow 工具，也可以使用第三方分析工具，比如 pt-query-digest、 mysqlsla
perl mysqldumpslow.pl -t 5 -s at C:\ProgramData\MySQL\Data\OAK-slow.log

慢查询优化

检查结果中的 key 值，是否为 NULL判断有无使用索引。在使用索引时，不要只关注是否起作用，应该关心索引是否减少了查询扫描的数据行数，如果扫描行数减少了，效率才会得到提升。对于一个大表，不止要创建索引，还要考虑索引过滤性，过 滤性好，执行速度才会快。

表：student
字段：id,name,sex,age
造数据：insert into student (name,sex,age) select name,sex,age from student;
SQL案例：select * from student where age=18 and name like ‘张%’;（全表扫描）

优化1 alter table student add index(name); //追加name索引

优化2 alter table student add index(age,name); //追加age,name索引

//为user表添加first_name虚拟列，以及联合索引(first_name,age)
alter table student add first_name varchar(2) generated always as (left(name, 1)), add index(first_name, age);
explain select * from student where first_name=‘张’ and age=18;

分页查询优化

#一般性分页
SELECT * FROM 表名 LIMIT [offset,] rows;

#偏移量固定,返回记录量低于100条，查询时间差距不大。随着查询记录量越大，所花费时间也会越来越多。
select * from user limit 10000,1; 
select * from user limit 10000,10; 
select * from user limit 10000,100;
select * from user limit 10000,1000; 
select * from user limit 10000,10000;

#记录数固定,，偏移量超过100后就开始随着偏移量增大，查询时间急剧的增加
select * from user limit 1,100; 
select * from user limit 10,100; 
select * from user limit 100,100; 
select * from user limit 1000,100; 
select * from user limit 10000,100;

#覆盖索引优化
#子查询优化

3.索引数据结构

在MySQL中，索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的。Mysql为什么选择B+树作为索引结构？

B+树

为什么不是哈希结构？

哈希结构，类似k-v结构，用于等值查询，不支持范围查找。

为什么不是一般二叉树？

如果二叉树特殊化为一个链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

为什么不是平衡二叉树呢？

树的高度随着数据量增加而增加，IO代价高。平衡二叉树每个节点只存储一个键值和数据的，如果是B树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来，查询效率加快。

那为什么不是B树而是B+树呢？

1）B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。innodb中页的默认大小是16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。

2）B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

节点存储

B+树中一个节点大小为一页或页的倍数最为合适。

Mysql的Innodb引擎中一页的默认大小是16k。

MySQL中可通过如下命令查看页的大小：

show variables like "innodb_page_size";
SHOW GLOBAL STATUS LIKE "Innodb_page_size"；

假设我们一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页里就可以存储16K/14=1170个(主键+指针)，那么一颗高度为2的B+树能存储的数据为：117016=18720条，一颗高度为3的B+树能存储的数据为：11701170*16=21902400（千万级条）。

MyISAM、InnoDB存储引擎

假设有一张学生表，id为主键

MyISAM存储引擎索引文件和数据文件是分离的。使用myisam存储引擎创建的表相关文件有三个，.frm是存放表结构数据，.myd是表数据，.myi是存放索引。叶节点的data 域存放的是数据记录的地址，主键索引和非主键索引的实现方式都是一样的。先从索引文件中查找到索引节点，从中拿到数据的文件指针，再到数据文件中通过文件指针定位了具体的数据。

InnoDB存储引擎索引和数据都在同一个文件。使用Innodb存储引擎创建的表相关文件只有两个，.frm文件是存放表结构数据，.ibd存放的数据和索引。

InnoDB是以聚集索引来组织数据的存储的，在叶子节点上，保存了数据的所有信息。如果这个时候建立了name字段的索引，会产生一个辅助索引，即name字段的索引，而此刻叶子节点上所保存的数据为聚集索引（ID索引）的关键字的值，基于辅助索引找到ID索引的值，再通过ID索引区获取最终的数据。这个做法的好处是在于产生数据迁移的时候只要ID没发生变法，那么辅助索引不需要重新生成，不这么做的话，如果存储的是磁盘地址的话，在数据迁移后所有辅助索引都需要重新生成。

为什么非主键索引结构叶子节点存储的是主键值？

保证一致性，更新数据的时候只需要更新主键索引树；节省存储空间。

为什么推荐InnoDB表必须有主键？

保证会有主键索引树的存在（因为数据存放在主键索引树上面），如果没有mysql会自己生成一个rowid作为自增的主键索引。

为什么推荐使用整型的自增主键？

方便查找比较；新增数据时只需要在最后加入，不会大规模调整树结构。如果是UUID大小不好比较，新增时也极有可能在中间插入数据，会导致树结构大规调整，造成插入数据变慢。

为什么不建议使用过长的字段作为主键?

因为所有辅助索引都引用主键索引,过长的主键索引会令辅助索引变得过大。

聚集索引与非聚集索引

• 聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据；物理存储按照索引排序
• 非聚集索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列；物理存储不按照索引排序
• 非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表

覆盖索引

查询列要被所建的索引覆盖，不必从数据表中读取，换句话说查询列可通过索引节点中的关键字直接返回。

非聚集索引一定会回表？

不一定，如果查询语句的字段全部命中了索引，那么就不必再进行回表查询。

索引下推

• 索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。
• 在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。
• 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

一张people表,包含字段name、address、first_name，索引为(name,address,first_name)

SELECT * FROM person  WHERE `name` = "1" AND `address` LIKE "%222" and first_name LIKE "%222";

不使用索引条件下推优化的话，MySQL只能根据索引查询出name=1的所有行，然后再依次比较是否符合全部条件。

当使用了索引条件下推优化技术后，可以通过索引中存储的数据判断当前索引对应的数据是否符合条件，只有符合条件的数据才将整行数据查询出来。根据explain解析结果可以看出Extra的值为Using index condition，表示已经使用了索引下推。

#关闭索引下推
SET optimizer_switch = 'index_condition_pushdown=off'; 
#开启索引下推
SET optimizer_switch = 'index_condition_pushdown=on';

参考

MySQL的索引优化