MySQL索引专题

1. MySQL数据库优化

假如面试官提出这样的一个问题，你是如何对MySQL数据库性能进行调优的？

有的同学在回答时，通常会聚焦在一个点，例如他会说将select * 替换为select 列明的方式，然后添加索引等

此时我们有经验的求职者往往不会直接给出答案，他会思考或者说出影响MYSQL性能的因素有哪些？

• 数据量 （数据量太大会影响查询的效率）

• 扫描范围

• SQL语句的设计

• 数据库缓存

• 。。。。

基于这些因素，我们通常采用的优化策略，有如下几个点：....

1. 整体设计

从设计上，可根据需要分库分表、读写分离、冷热分离、使用缓存、定期进行数据清理。

• 分库：垂直分库和水平分库

• 分表：垂直分表（表中字段太多）和水平分表（表中数据量太大，商品表、订单表）

• 读写分离：读多写少（对热点读数据进行读写分离）

• 缓存：在数据库中可以设置查询缓存（可以调整缓存大小），通过缓存减少磁盘IO次数

1. 配置文件

从优化MySQL配置文件上，调整MySQL配置文件中的参数，如缓冲区大小、最大连接数等，以适应应用程序的需要。

1. 表结构设计

从优化表结构上，使用合适的存储引擎；避免使用大类型或不必要的列，并尽可能使用小型数据类型（能用tinyint不用int）；尽量把字段设置为NOT NULL；对于某些文本字段来说，例如“省份”或者“性别”，我们可以将他们定义为ENUM(枚举)类型。因为在MySQL中，ENUM类型被当做数值型数据来处理，而数值型数据被处理起来的速度要比文本类型要快得多。

1. 查询优化

从优化查询上，善用EXPLAIN查看SQL执行计划；使用连接(JOIN)来代替子查询，减少在内存中创建临时表；尽量用union all代替union减少排序；利用小表去驱动大表，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数；善用索引(index)。

1. 客户端应用

从客户端使用上，使用连接池(HikariCP,DRUID,参数配置)、避免大事务、返回数据多使用物理分页。

1. MySQL中的索引设计

1. 什么是索引？

面试官：说说你对索引的理解？(3W1H=What+Where+Why+How)

官方的定义索引是一种数据结构.

从生活维度讲,假如将一本书看成是一张表,这本书的目录就是表中的索引(Index).

1. 为什么使用索引？

数据量比较大时,为了快速找到们需要的数据可以使用索引,这样可以提高查询的效率。例如索引一般自动应用在查询条件(where)、排序条件(order by )、分组条件(group by ,having)、表连接中。

1. 索引应用时有什么弊端？

面试官可能在了解你对索引的理解以后，可能随口再问一下，索引应用时存在哪些弊端？

1. 索引会占用额外的存储空间(InnoDB存储引擎中索引和数据存储一起)

2. 对更新操作会带来一定的复杂度.(更新记录时需要更新索引.思考一本书,增加了几页内容,目录要更新吗?)

1. MySQL中的索引是如何分类的？

1. 从逻辑应用维度可分为主键,普通,联合,唯一,空间索引，全文索引等（不同存储引擎支持的索引是不一样的）。

2. 从物理存储维度可分为聚簇索引(数据和索引存储在一起)和非聚簇索引(数据和索引分开存储)。

3. 从数据结构维度可分为hash索引,B+Tree索引(InnoDB存储引擎默认)等。

1. 如何查看MySQL表中的索引？

show index from 表名;

1. 如何为表中字段添加索引？

1. 创建表的同时创建索引.(例如 create table tablename(....,index 索引名 (字段名)))

2. 创建表后通过create语句创建索引(例如 create index 索引名 on 表名(字段名))

3. 创建表后通过alter语句创建索引(例如 alter table add index 索引名(字段名))

准备工作

 create table if not exists student
 (
    id int auto_increment,
    first_name varchar(50) not null comment '名字',
    last_name varchar(50) not null comment '姓',
    email varchar(100) default '' comment '邮箱',
    phone varchar(20) not null comment '手机号',
    birthday date  not null comment '出生日期'，
    index index_birthday (birthday),
    unique key index_phone_uk (phone),-- 这里的key等效于index,没有指定索引名(index_phone_uk),默认系统自动起名.
    primary key (id) using BTREE
 );

创建普通索引(创建表之后创建索引)

create index index_first_name on student(first_name);
alter table student add index index_last_name (last_name);

创建唯一索引

create unique index index_first_name on student(first_name);
alter table student add unique index index_email (email)

创建组合(联合)索引案例分享（查询字段是多个时）

create index index_first_last on student(first_name,last_name);
alter table student add index index_first_last (first_name,last_name);

1. 如何删除表中字段上的索引？

drop index 索引名 on 表名;

例如：

drop index index_first_last on student;

1. 什么是"B-树"以及特点？

• 什么是二叉排序树?

软件系统中为提高数据的查找效率,一般会先对数据进行排序,然后以二叉排序树(又称为二叉查找树)的方式对数据进行存储.

二叉查找树最坏的一种情况会变为一个链表（数据库的自增id）,而这种情况查询的效率就会降低.

• 什么是平衡二叉排序树?

平衡二叉树,也是采用二分法的思路,首先它最多是两个分支,其次是左右两颗树的层数最多相差1,例如

平衡二叉树在执行频繁插入和删除操作,为了维护整个树的平衡,需要不断进行左旋或右旋,从而来保持树的平衡.这种平衡二叉树解决了二叉查找树可能退化为链表的问题，但是因为只能有两个分叉，这样节点数越多，树的高度就会越高，树的高度越高，查询的效率就会越低。

什么是"B-树"?

B-树(多叉平衡树)中每个非叶子节点允许有多个分叉并且每个非叶子节点都存储指针、索引、数据，每个叶子节点只存储索引和数据。

B-树的存在的缺陷如下：

1、非叶子节点都会存储数据，这样一个磁盘块存储的索引就会少一些，分叉少，树的高度高。（操作系统默认磁盘块大小为4K,MySQL读写数据是以Page为单位。这里的Page大小默认为16KB，也就是说一个Page中可能会有多个块。但是一个块中存储的数据多了，索引肯定就会少，索引少分叉就少，分叉少了树的高度可能就会比较高。高度越高查询效率越低）。

2、叶子节点上不支持范围查询，每次查询数据都要从根节点开始，这样可能影响查询效率。

1. 什么是B+树以及它的特点？

B+树一种多叉平衡树，是B-树的Plus版（B-树的一种的优化），主要用于索引的存储结构，然后基于这种结构提高查询效率。其特点如下：

1. 树中非叶子节点只存储索引和指针，叶子节点存储索引和数据。

2. 树中叶子节点在相同层并且有序，他们之间使用双向链表连接,用以更好的支持范围查询.

B+树中非叶子节点只存储索引和指针,不存储数据，这样相对于B-树可存储的索引就会更多，分叉也会更多，树的高度就会降低，查询效率会更好,同时在叶子节点上添加了双向链表，可以更好的提高范围查询的效率。

1. 说说B+树相对B-树的优势有哪些?

• 相同数据量节点的情况下B+树的高度相对于B-树的高度会变低. （高度低了，查询效率会比较高）

• B+树叶子节点之间有双向链表的连接,范围查询效率会更快.

1. 说说什么是聚簇和非聚簇索引？

从数据和索引的存储维度上进行分类的话,索引可以分为聚簇索引和非聚簇索引.聚簇索引也叫聚集索引，索引和数据存储在一起，也就是索引与数据是不分离。InnoDB存储引擎就是聚簇索引。

非聚簇索引也称之为非聚集索引，索引和数据分开存储，例如：

非聚簇索引在创建时，存储的是索引值以及索引对应的记录的地址。 基于非聚簇索引查询数据时，可以先基于索引找到数据的一个地址，然后基于地址再去查找数据。 单从索引角度来说，非聚集索引查找速度不如聚集索引，非聚集索引找到索引位置后还需要根据索引找到数据对应的位置.

1. 查询在什么时候会出现索引失效？

• 不满足最左匹配原则 （... where first_name='A' and last_name='B'）

• 查询条件使用了函数 (... where year(birthday)='1999')

• or操作有一个字段没有索引 (... Where salary>10000 or birthday='1999-10-12')

• 使用like条件以%开头 (... Where first_name='%A')

• 显式或隐式类型转换导致索引失效 (... where id='1')

• 存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效

• 走索引效率低于全表扫描

• 查询条件对null做判断，而null的值很多

• 一个字段区分度很小，比如性别、状态

1. nnoDB 中的索引模型是怎样的？

在 MySQL 中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样。而即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。

每一个索引在 InnoDB 里面对应一棵 B+ 树。

假设，我们有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。

这个表的建表语句是：

create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k)
)engine=InnoDB;

表中 R1~R5这5条记录中的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下

InnoDB中根据叶子节点的内容，索引类型分为主键索引和非主键索引。

• 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

• 非主键索引的叶子节点对应的数据是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

基于主键索引和普通索引的查询有什么区别？

• 如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；

• 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。（面试过程中经常会被问到什么是回表查询）

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询.