面试：mysql最全索引与优化详解

mysql 是我们最常用的数据存储的的程序，它是关系数据库的代表，可以直接服务于我们的常规业务，是我们不能离开的数据存储器，对于关系操作复杂的业务，具有很强的优势。

随着数据的增多，高效的索引，起着非同寻常的作用。
理解mysql 的各种索引，适用场景，可以提高程序的性能以及增加程序的稳定性。

1. mysql 数据的查询过程

首先数据库mysql是 c/s 架构，分为 client端，server端，存储引擎，文件系统这几块。

server 端包括，sql优化器，缓存主件：

一条sql的查询过程：

首先client 向server端发送一条查询sql.
server端先去缓存中进行查询，如果命中缓存，直接返回，否则进入下一个阶段。
服务器对sql进行解析优化，由sql优化器生成执行计划。
将执行计划提交给存储引擎进行查询；
将查询结果返回客户端；

当然除 select外，update,delete,insert 都会记录log,所以查询过程有点不一样

拿将 age = 10 更新 age = 15

首先client向server发送更新sql
服务器先去缓存中查询是否有age=10的数据，如果有就返回执行器，否则下一个阶段，
服务器对sql优化，优化器生成执行计划，调用存储引擎
磁盘查询，将数据返回执行器
执行器将age=15,并生成一条新数据
调用innodb的引擎写入接口，写到内存中
innodb引擎会写入相反的语句到undo日志中以便失败回滚，同时将数据写入redo日志中，将数据标记prepare
执行器将此操作写入binlog中，将binlog写入磁盘。
最后执行器提交事务，并把redo日志改为commit状态，存储引擎将数据异步写入磁盘。

2. mysql不同存储引擎不同索引的区别

我们在一个数据库里进行创建表的时候可以指定创建的存储引擎 ENGINE=InnoDB;

CREATE TABLE QRTZ_CALENDARS (
  SCHED_NAME VARCHAR(120) NOT NULL,
  CALENDAR_NAME VARCHAR(200) NOT NULL,
  CALENDAR BLOB NOT NULL,
  PRIMARY KEY (SCHED_NAME,CALENDAR_NAME))
  ENGINE=InnoDB;

我们常用的引擎有InnoDb索引和 MyISAM索引，可以将不同表根据不同需求创建不同的索引表。

InnoDB 主打的是事务， MyISAM 主打的是性能；

他们之间的区别是， InnoDB 支持事务，MyISAM 不支持事务，InnoDB 支持外键，MyISAM不支持外键；
InnoDB 支持行级锁，MyISAM 只支持表级锁，InnoDB 支持崩溃后恢复，MyISAM 不支持崩溃后恢复，其次是他们索引都是用的B+树，但是也不完全一样。
MyISAM 适合于查多更新少的情况以及存在大量整表count的情况。

当然InnoDB 和 MyISAM 默认index 都是B+Tree 但是他们都还有一定的区别

InnoDB 中索引分为，主键索引和辅助索引，主键索引B+Tree 的叶子节点存放的是整行的数据，赋值索引叶子节点存放是主键的key值，这也是为什么要求key长要小一些好的一个原因。

MyISAM 的 B+Tree 索引的叶子都是存储数据的地址值。

MyISAM 和 Innodb 虽然都不支持hash索引，但是InnoDB 有另一种实现方法：自适应哈希索引。InnoDB 存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引。

3. 页结构，页的大小和格式，innodb 行格式

在计算机中，我们的内存是有限的，是贵的，并不能把程序进行一次性的载入，所以我们提出虚拟内存；

其原理就是根据局部性原理：

局部性原理：时间局部性和空间局部性。

时间局部性：是由于程序中有大量循环，空间局部性是程序有大量的数组。

mysql 也一样，我们在查询数据时也不是直接将整个表数据加载到内存，而是每次加载磁盘上的一个页的单位进行比较。

索引也一样，每一个索引B+Tree 的每一个节点不是一条数据而是一个页节点；

innodb 页

以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16KB。16384个字节。

InnoDB的页结构分为七个部分：

页.PNG

我们数据存储在 User Records, 其次 PageDirectory 对数据建立索引目录，便于快速查找；

innodb 行

行数据就是存储在 UserRecord 中， InnoDB 中行数据格式：

主键索引的叶子节点

行.PNG

我们知道 innodb 中只有主键索引叶子节点存储数据，所以在innodb中如果没有创建主键索引，会将

我们发现行中多加几个字段，变长字段列表，NULL标志，记录头信息

变长字段长度列表：记录变长字段对应的字节的长度，顺序记录变长字段最大值，varchar(20)

NULL 标志：所有字段是不是为null，如果存在null,innodb用null标注所有的字段是否为空

在创建表的时候会统计所有字段类型的大小，除开blobs类型最大是65535 字节，当然其中是包括我们隐式字段，所以我们发现一行数据甚至会超过一页的大小

记录头信息里会有指针指向下一记录 nextRecord

这时innodb采取两种策略，

1.这一页数据 + 下一页的地址，在这一页数据尾加上下一页地址

2.Dynamic: 第一页存放所有数据页的地址，指向存放这行数据的所有页

4. Innodb B+Tree 底层数据结构

innodb 中的索引树节点有3种，主键索引的叶子节点，辅助索引的叶子节点，非叶子节点；

主键索引节点

image.png

我们知道innodb 我们没有设置主键，会将定义表中的唯一索引作为主键，否则自动生成row_id 作为主键

拓展：innodb表中三个隐藏列：Row_id,transcation_id,roll_pointer

数据页中查找会首先基于 PageDirectory 进行查找，PageDirectory中查找可以基于二分查询，来快速找到对应的行数据。但是实现PageDirectory二分查询的前提就是数据的顺序性。没错，这里我们可以看出innodb会默认将数据按照主键索引进行排序，每行数据的记录头里有nextRecord字段指向下一条数据。

这里有1个点：

1. 为什么要key不要太长 ?

页的数据大小是一定的如果key太长，会导致一页数据存储行数变少，同时树的高度就变高了

非叶子节点

image.png

非叶子节点，主要存储主键的 key,相当于当数据的行数超出一页的大小，所以需要存放多个页数据，非页节点作为，叶子节点的目录索引；

辅助索引节点

image.png

辅助索引节点主要存储组合辅助索引，主键索引，以及指针；

存储主键索引的索引的目的是为，避免辅助索引不是唯一索引，可能会出现重复的数据；

根据辅助索引我们知道这就是为什么innodb支持前缀索引，就是从第一个字段进行匹配；

支持快速的范围查找是因为叶子节点是链表；

辅助索引的叶子节点存放的是主键的key值，需要进行回表查询；

5. 整个B+tree 特性

b+Tree 的好处：

数据库索引在数据量大的情况下都是放在磁盘上，而磁盘IO的读写效率是非常慢的，矮胖的B+树就是比较好的选择，B+Tree 的每一个节点可以存储一个页大小的数据，在进行二叉树查找时每次都可以将一页大小数据加载到内存，查找完再加载下一个页节点，AVL树都是基于内存的算法，必须全部加入到内存。

1.B+ 树的中间节点是不保存数据的，所以磁盘页可以容纳更多节点元素，更加的矮胖。

2.B 树的查询可以在中间节点结束， B+ 树的查询，必须查找到叶子节点，B+ 树比较稳定。

3.B+ 树的范围查询只需要遍历叶子节点的链表即可， B 树需要重复的中序遍历

4.B+ 树的叶子节点是链表结构，叶子结点本身依关键字的大小自小而大顺序链接

B+ 树允许元素重复

6.主键索引与辅助索引

innoDB 会默认创建主键索引，便于对数据的查询；

主键索引比较快，因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值，查到主键的值以后，还需要再通过主键的值再进行一次回表查询

当然在覆盖索引的时候不需要进行回表的操作

通过覆盖索引也可以只查询一次

主键索引在数据插入时就是按照主键key值进行排序，

一个表可以有多个辅助索引，辅助索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同；

可以将创建联合索引，但是联合索引遵循最左匹配原则：

image.png

例如上图将三个列最为联合索引，在每一个节点存储的直接就是三个字段顺序拼接的值 “111”，所以在进行比较是不能跳过前面的直接比较中间的数据。

其次在 a > 1 and b > 1 是不走索引的，是因为，筛选a > 1 先走一遍联合索引，查出的数据不一定是按顺序排的，所以b不走索引; 但是 a = 1 and b > 1 是走索引的，是因为，a = 1 晒出的值，是按顺序排的b就可以走索引；

7. explain 字段解析

+----+-------------+-------+------------+------+---------------+-----+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----+---------+------+------+----------+-------+

id: 询的序列号，包含一组可以重复的数字，表示查询中执行sql语句的顺序。

select_type: 主要是用于区别普通查询，联合查询，嵌套的复杂查询

type: 性能从最优到最差的排序：system > const > eq_ref > ref > range > index > all

通过查看type 可以看出是否走索引，all表示不走索引，
all代表全表扫描，index 走索引，range：只检索给定范围的行， ref：非唯一性索引扫描， eq_ref：唯一性索引扫描，const：表示通过索引一次就可以找到，system：表只有一条记录（等于系统表）。

key：
显示查询语句实际使用的索引。若为null，则表示没有使用索引。

extra
Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。

Using index：表示相应的select 操作中使用了覆盖索引（Covering index），避免访问了表的数据行，效果不错！如果同时出现Using where，表明索引被用来执行索引键值的查找。如果没有同时出现Using where，表示索引用来读取数据而非执行查找动作。

Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。出现这个更要立刻优化sql。

EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

8. 查询性能分析

image.png

我们在查询的过程中，sql优化器可以对我们的sql语句进行优化，不一定走索引，当然我们也可以强制设置为走索引；

例如：test

id	a	b	c
1	1	1	2
2	1	1	1
3	3	1	3
4	6	5	7
5	1	1	1

id 是主键索引，abc是联合索引

sql优化器

select * from test where a > 1;

上面这个语句一定不走索引，虽然我们语法上分析要走索引，这是因为，我们先走abc的前缀索引，查到主键集，再进行回表，但是这个主键集是整个表的集合，还不如直接all查找，一般走辅助索引查表返回主键key超过表85% 不会走索引；

但是改为：

select a from test where a > 1;

走索引，这是因为覆盖索引

联合索引

select a from test where b =1 and c = 1 and a = 1;

上面这是走索引的，sql优化器会对 abc进行自动排序

select a from test where a = 1 and b >1;

这也是走索引的如果第一个字段是a = 1,则查出的数据是由顺序的索引，后面的数据还可以继续走索引，

select a from test where a >1 and b > 1;

这是不走索引的，这是因为 a > 1 查出数据无序的；

select a from test where a = 1 and b = 1 and c = 1 and id = 1;

走索引，主键默认添加在辅助索引中

使用不等于查询,
列参与了数学运算或者函数
在字符串like时左边是通配符.类似于'%aaa'.
当mysql分析全表扫描比使用索引快的时候不使用索引.
当使用联合索引,前面一个条件为范围查询,后面的即使符合最左前缀原则,也无法使用索引.

group by order by

select a from test order by a,b,c;

order by 就是排序如果走索引的话就直接返回不要创建临时表或者加载到内存进行排序

select a from test group by a,b,c;

group by 是先使用order by进行排序，然后将相同的分为一组；

SQL 优化过程

开启慢查询并分析
explain + sql 慢查询
show profile 查询sql 在mysql 中的执行细节
数据库服务器参数调优

小表驱动大表

单路排序，从磁盘读取查询所需要的查询列，按照order by 列在 buffer 对其进行排序，然后扫描排序后的列表进行输出，它的效率更高，避免二次io,把随机io变顺序io

sort_buffer 值，不能大于 sort_buffer 的值

order by 调大 sort_buffer 值，让其可以一次加入

order by 调优：

不要用select * ，避免查出数据超过 sort_buffer 大小
尝试提高 sort_buffer_size
提高 max_length_for_sort_data ,但是不能设置太高容易超过sort_buffer_size

show profiles;

注意避免情况以下情况；

1. converting Heap to MySIAM
2. creating tmp table 
3. coping to tmp table on disk
4. locked

面试：mysql最全索引与优化详解

1. mysql 数据的查询过程

一条sql的查询过程：

2. mysql不同存储引擎不同索引的区别

InnoDB 主打的是事务， MyISAM 主打的是性能；

当然InnoDB 和 MyISAM 默认index 都是B+Tree 但是他们都还有一定的区别

3. 页结构，页的大小和格式，innodb 行格式

其原理就是根据局部性原理：

innodb 页

innodb 行

在创建表的时候会统计所有字段类型的大小，除开blobs类型 最大是65535 字节，当然其中是包括我们隐式字段，所以我们发现一行数据甚至会超过一页的大小

4. Innodb B+Tree 底层数据结构

innodb 中的索引树节点有3种，主键索引的 叶子节点，辅助索引的叶子节点，非叶子节点；

1. 为什么要key不要太长 ?

存储主键索引的索引的目的是为，避免辅助索引不是唯一索引，可能会出现重复的数据；

5. 整个B+tree 特性

6.主键索引 与 辅助索引

主键索引在数据插入时就是按照主键key值进行排序，

7. explain 字段解析

8. 查询性能分析

SQL 优化过程

在创建表的时候会统计所有字段类型的大小，除开blobs类型最大是65535 字节，当然其中是包括我们隐式字段，所以我们发现一行数据甚至会超过一页的大小

innodb 中的索引树节点有3种，主键索引的叶子节点，辅助索引的叶子节点，非叶子节点；

6.主键索引与辅助索引