MySQL高级（二）

追梦的烟火

已于 2022-10-26 13:12:59 修改

阅读量469

点赞数

文章标签： mysql 数据库 sql

于 2022-06-21 16:32:36 首次发布

本文链接：https://blog.csdn.net/m0_61470267/article/details/125334989

版权

SQl执行顺序

手写：

SELECT DISTINCT <select_list>
FROM  <left_table> <join_type>
JOIN  <right_table> ON <join_condition>
WHERE  <where_condition>
GROUP BY  <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

机读：

FROM  <left_table>
ON <join_condition>
<join_type> JOIN  <right_table> 
WHERE  <where_condition>
GROUP BY  <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

索引

为什么要有索引？

索引可以理解为书的目录，通过目录查询效率高。

例如：在数据库中主键默认是添加索引的。

索引是什么？

索引是帮助 mysql 高效获取数据的数据结构，在mysql中使用B+树的

主键：默认添加索引，主键维护在一个B+树中，保存这条记录的物理地址。

索引优劣

优点：

可以提高查询效率（使用B+树），把索引数据加载到内存中），减少IO次数；索引使用B+树结构，是有序的，排序时比较快捷方便，减少CPU消耗。

缺点：

索引也是需要空间来存储维护的
执行增，删、改操作的时候，需要对索引的结构进行更新

索引分类

主键索引：设置为主键候自动建立索引。

ALTER TABLE 表名 add PRIMARY KEY 表名(列名);

删除主键索引：

ALTER TABLE 表名 drop

单值索引/单列索引：一个索引只能包含单个列，一个表可以有多个单列索引。

创建单值索引

CREATE INDEX 索引名 ON 表名(列名);

删除索引：

DROP INDEX 索引名

唯一索引：索引列的值必须唯一，允许为null

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

删除索引：

DROP INDEX 索引名 ON 表名

组合索引：一个索引包含多个列。

覆盖索引：覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

组合索引最左前缀原则：最左侧索引原则在使用组合索引时,最左侧的列必须被使用到,否则索引失效。

创建复合索引

CREATE INDEX 索引名 ON 表名(列 1,列 2...)

删除索引

DROP INDEX 索引名 ON 表名;

例：

select * from table where a=’’and b=’’ 索引生效
select * from table where b=’’and a=’’ 索引生效
select * from table where a=’’and c=’’ 索引生效
select * from table where b=’’and c=’’ 索引不生效

全文索引：需要模糊查询时，一般索引无效。

CREATE FULLTEXT INDEX 索引名 ON 表名(字段名) WITH PARSER ngram;

SELECT 结构 FROM 表名 WHERE MATCH(列名) AGAINST(搜索词');

查看索引: SHOW INDEX FROM 表名;

索引创建原则

哪些情况下适合添加索引？

主键，默认添加唯一索引
作为查询条件的列
作为外键关联的列
排序的列
分组的列

哪些情况下不适合建立索引？

表记录很少（例如系统参数设置表）
不作为查询条件的列
增删改较为频繁的表，最好为需要的列来添加
数据重复较高的（如性别）

索引的数据结构

mysql使用B+树来存储索引

B+树是有序的
每个结点可以存储多个数据（横向扩展）
非叶子节点不存储数据，只存储索引，一个节点中可以存储多个索引
所有的数据存储在叶子节点中，每个叶子节点之间有指针指向

mysql使用B+树的原因？

有序节省排序时间，一个节点存储多个元素，树的高度降低了，叶子节点之间有指针，便于区间范围查询。

聚簇索引和非聚簇索引

聚簇索引：找到了索引就找到了数据（例如innodb引擎，索引和数据在同一文件中，找到索引就可以找到数据，使用主键作为条件查询，使用其他的列进行查询，查询的结果只有自己）

非聚簇索引：找到了索引，还需要回表进行查询（例如myisam引擎中，索引和数据在两个不同的文件中，找到索引，还需要在存储数据的文件中进行查找）

innodb非聚簇：使用其他列作为查询条件，查询结果除了本列之外还有其他的内容，这种情况需要通过该列先找到主键，在通过主键再次回表查询数据。

事务

概述

事务就是一次数据库操作中的若干单元的管理，事务管理的目标是完整性，一次中的若干操作要么都执行成功，要么都失败。

事务特性

ACID（原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability））

原子性：一个事务的一组操作（一个或多个sql语句），要么都执行成功，要么都不成功。

隔离性

为什么使用隔离性？

为了保证多个事务在并发执行的过程中，互不影响。

出现问题：脏读、不可重复读、幻读

脏读

事务A开启事务，查询表数据；

事务B开启事务，向表添加一条数据后，未提交；

事务A再次查询该表，查询到已经添加后的数据。

但是事务B可能会回滚，那么事务A再读到的就是脏数据。

不可重复读

事务A开启事务，查询到一条记录（例如age = 10），

事务B开启事务，修改这一条记录（age = 20）,并提交事务，

事务A再次查询，事务A为修改后的数据（age = 20）。这就是不可重复读

幻读

事务A开启事务，查询到一条记录，

事务B开始开启事务，增加一条记录，提交，

事务A再次查询，查询到两条记录。

注：幻读是数量上的变化

为了出现了这些问题，我们设置了隔离级别，隔离级别一共有四个。

隔离级别

读未提交（read uncommitted）

一个事务可以读到另一个事务还没提交的数据。

描述：事务A开启事务，查询表数据，

事务B开启事务，向表添加一条数据后，未提交

事务A再次查询该表，查询到已经添加后的数据。

但是事务B可能会回滚，那么事务B读到的就是脏数据。

问题：可能会出现脏读，其中还会出现不可重复读，幻读的情况。

读已提交（read committed)）

一个事务只能查询到另一个事务已经提交的数据。

描述：

事务A开启事务，向表插入一条数据，未提交

事务B开启事务，查询该表，未查询到该条记录

事务A提交数据，事务B再次查询，查询到该条记录。

目的：解决了脏读问题

问题：会出现不可重复读和幻读问题。

可重复读（repeatable read MySQL 默认隔离级别）

一个事务开始时读到了一个数据，在事务中继续执行再次读时，读到数据与第一次是一致的。

描述：

事务A开启事务，查询到一条记录，（保存在快照中）

事务B开启事务，修改这条记录，并提交。

事务A再次查询，查询到的是原来的记录。

目的：解决不可重复读问题，

注：mysql在可重复读级别上只在Innodb引擎中不存在幻读问题。

可串行化（serializable）

对表进行操作时，只能是一个一个事务执行，如果有一个事务在执行中，即使是读操作，那么其他事务也必须进行等待。

持久性：事务执行完成后，将执行后的数据持久化的保存到硬盘上，不可回滚。

一致性：在事务执行之前和之后都是为了保持数据库的完整性。

事务设置

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --读未提交

事务实现原理

redolog 用于保证事务持久性；undolog 则是事务原子性和隔离性实现的基础。

原子性实现：使用 undo log对事物进行回滚，记录一个相反的操作。执行一个修改操作。后来又回滚了，那么数据需要知道执行前是什么样子的。

比如：在使用insert 操作时，undo log日志就会记录一个相反的操作 delete；在执行 delete 操作时，会同时记录insert操作。这样在事务回滚时，可以借助日志信息进行还原。

隔离性：

持久性实现：mysql对数据操作时，并不是立即将数据写入磁盘，这样io多，效率低。数据又不能一直保持在缓存中，万一服务器宕机，那么数据就不存在了。mysql提供了redolog日志，可以将数据先暂时保持在日志中，记录哪些数据发生了修改，定期将日志数据写到磁盘。

隔离级别实现原理（MVCC）

MVCC 多版本并发控制

目的：mvcc机制是为了提高mysql并发方法性能（读-写，写-读）；

MVCC 使得数据库读不会对数据加锁，普通的 SELECT 请求不会加锁，提高了数据库的并发处理能力 。借助MVCC，数据库可以实现READ COMMITTED，REPEATBLE READ等隔离级别。

每个事务在对表记录操作时，会将之前的数据保存在undo log 中，表中有两个隐藏列（事务id，版本id）一个保存了行的事务 ID（DB_TRX_ID），一个保存了行的回滚指针（DB_ROLL_PT）。

事务1 第一次插入数据

事务 2 第一次修改数据

事务 3 第二次修改数据

ReadView

ReadView中存储一些当前操作的活跃事务对象id，记录当前事务id、最大或最小

当为可重复读时，事务A查询后，事务B进行2次修改后，事务A再查，只能查到当前的数据，不能读到修改后的数据。

关注点是从readView 中判断应该获取版本链中的哪条记录出来

对于看到的读已提交，为什么其他事务提交后，就可以读到最新的版本？

因为每次读的时候就会产生一个readView ，读到最新的数据，称为当前读。

对于可重复读，是第一次读取数据时，就会生成readView，这样之后，再次读时，与第一次的版本是一致的，称为快照。

多版本并发控制, 为了读已提交,可重复读提供版本记录,

读已提交：因为每次读的时候读到其他事务提交的最新记录

可重复读：因为读的时候会拍照,在同一个事务中一直从此快照中读

锁机制

按锁的粒度划分

行级锁：一个事务在对某行数据操作（写），其他事务不能对此行进行操作，锁定一行。

优：并发性好

缺：加锁的频率高（开销较大）

mysql中innodb支持行锁（默认为行锁），myisam不支持行锁

表级锁：一个事务在对某表中某行操作时，将某个表锁定了，其他事务不能操作。

优：并发性低

缺：加锁的频率少（开销小）

mysql中innodb和myisam都支持表锁。

间隙锁：对表中数据的某个区间进行加锁（区间锁）

update test set name = '111' where id > 1 and id < 10

临键锁：临键锁是行锁+间隙锁，即临键锁是是一个左开右闭的区间，比如（3，5]。 InnoDB的默认事务隔离级别是RR，在这种级别下，如果你使用select ... in share mode或者select ... for update语句，那么InnoDB会使用临键锁，因而可以防止幻读；但即使你的隔离级别是RR，如果你这是使用普通的select语句，那么InnoDB将是快照读，不会使用任何锁，因而还是无法防止幻读。

排他锁（X）：写锁，实际意义上的加锁，有事务进行写操作，其他事务不能执行。执行增、删、改操作时，自动加锁；查询语句如果有需要，也可以加排他锁。

select * from test where id = 5 for update

共享锁（S）：读锁，添加查询语句，添加共享后，其他事务也可以添加共享锁，但是其他事务就不能添加排他锁了。

select * from test where id = 5 lock in share mode

数据库优化

第一个方法：选取最适用的字段属性。

MySQL可以支持大数据量的存取，但是数据库中的表越小，在上面执行的查询就越快。所以可以将表中的字段宽度设置的尽可能小。

第二个方法：使用连接来代替子查询（Sub-Queries）。

可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。

第三个方法：使用联合（UNION）来代替手动创建的临时表。

MySQL可以把需要使用临时表的两条或者更多的select查询合并到一个查询中。

第四个方法：事务。

不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。可以保持数据库中数据的完整性和一致性。

第五个方法：锁定表。

由于在事务执行的过程中，数据库将会被锁定，因此其他的用户请求只能暂时等待直到该事务结束。有些情况下我们可以通过锁定表的放大来获得更好的性能。

第六个方法：使用外键。

锁定表的方法可以保护数据的完整性，但是却不能保证数据的关联性。此时我们可以使用外键。

Sql优化

为什么要对Sql进行优化？

在数据量增大时，sql的执行效率对程序运行效率的影响逐渐增大，优化sql，可以提高查询效率。

几种优化方案

适当添加索引（添加索引的原则）
应尽量避免索引失效
状态字段/流程（尽量使用整数类型）
尽量使用varchar（定长使用char，变长使用varchar）
查询结果列出需要的列，不要用 *
尽量避免一次性查询过多的数据
尽量避免值为null，可以赋给默认值。null是会占空间的，在count统计时，不被统计

索引失效情况：

组合索引中，不满足最左前缀原则
like 模糊查询
以 null 为条件作为查询语句
例：select id from test where num is null
使用or进行条件连接
在where 中使用表达式/函数

如何避免索引失效

全值匹配我最爱

建立几个复合索引字段，最好就用上几个字段。且按照顺序来用。

最佳左前缀法则

如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始，不跳过索引中间的列。
不再索引列上做任何操作(计算、函数、（自动or手动）类型转换)，会导致索引失效而转向全表扫描。
存储引擎不能使用索引中范围条件右边的列。

若中间索引列用到了范围，则后面的索引全失效。
尽量使用覆盖索引（只访问索引的查询(索引列和查询列一致)），减少select* 。

覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。
Mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描。
IS NULL和IS NOT NULL也无法使用索引
like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
字符串不加单引号索引失效
用or来连接时索引会失效应少用,要想使用 or，又想让索引生效，只能将 or 条件中的每个列都加上索引。

执行计划

EXPLAIN

sql发送给服务器，在服务器内如何执行，执行流程是怎们样的，先执行谁，有没有用到索引等.....

EXPLAIN 作用

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

EXPLAIN 使用

使用explain关键字，添加到查询语句的前面，输出sql执行过程的参数。

EXPLAIN SELECT * FROM USER WHERE id = 1

1. id

sql执行顺序

例如有嵌套的子查询

主查询id 1

子查询id 2 表名子查询是先执行

2. select_type

表示查询结构

simple 简单查询

PRIMARY 主查询

SUBQUERY 子查询

3. type

查询性能指标

system>const>eq_ref>ref>range>index>ALL

system 表中只有一条记录

const 通过索引一次性可以找到

ref 使用了索引例如姓名,查询出来可能会有多条数据

range 使用了索引范围查询

index 类型只遍历索引树。

All 全表扫描索引失效查询所有数据了

主键自增 B+ 123456789 203423

4. possible_keys

可能用到的索引，key 实际用到的索引。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

5. key

实际使用的索引。如果为NULL，则没有使用索引,或者索引失效.

6. rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数.

分区、分表、分库

Mysql分区

一般情况下我们创建表对应的一组存储文件，使用MYISAM存储引擎时是一个.MYI和.MYD文件，是用Innodb存储引擎时是一个.ibd和.frm（表结构）文件。

当数据量较大时（一般来说是千万记录级别以上），MySQL的性能就可使下降，这时我们就需要将数据分散存储到多组文件中，保证单个文件的执行效率。

为什么要使用分区？分区的优点？

逻辑数据分割
提高单一的读写应用速度
提高分区范围读查询的速度
分割数据能够有多个不同的物理文件路径
高效的保存历史数据

分区类型及操作：

RANGE分区：基本属于一个给定连续区间的劣质，把多行分配给分区。mysql将会根据指定的拆分策略，把数据放在不同的表文件上。相当于在文件上，被拆成了小块。但是，对外给客户的感觉还是一张表，透明的。按照range来分，就是每个库一段连续的数据，这个一般是按比如时间范围来的，比如交易表啊，销售表等，可以根据年月来存放数据。可能会产生热点问题，大量的流量都打在最新的数据上了。range 来分，好处在于说，扩容的时候很简单。

LIST分区：类似于按range分区，每个分区必须明确定义。它们的主要区别在于，LIST中每个分区的定义和选择都是基于某列的值从属于一个值列表集中的一个值，而range分区是从属于一个连续区间值的集合。

HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中这些行的列值进行计算。这个函数可以包含Mysql中有效的，产生非负整数值的任何表达式。

hash分发：可以平均分配每个库的数据量和请求压力；坏处在于扩容起来比较麻烦，会有一个数据迁移的过程，之前的数据需要重新计算hash值重新分配到不同的库或表。

KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MYSQL服务器供其自身的哈希函数。必须有一列或多列包含整数值。

看上去分区表很帅气，为什么大部分互联网还是更多的选择自己分库分表来水平扩展咧？

分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁
一旦数据并发量上来，如果在分区表实施关联，就是一个灾难
自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么玩的，不太可控

MYSQL 分表

分表有两种分割方式：一种是垂直拆分，一种是水平拆分

垂直拆分：

通常是按照业务功能的使用频次，把主要的、热门的字段放在一起做为主要表，然后把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中；主要表和次要表的关系都是一对一的关系。

水平拆分（数据分片）

单表的容量不超过500W，否则建议水平拆分。是把一个表赋值成同样表结构的不同表，然后把数据按照一定的规则划分，分别存储到这些表中，从而保证单表的容量不会太大，提升性能。当然这些结构一样的表，可以防在一个或多个数据库中。

水平分割的几种方法：

使用MD5哈希，做法是对UID进行md5加密，然后取前几位（我们这里取前两位），然后就可以将不同的UID哈希到不同的用户表（user_xx）中了。
还可根据时间放入不同的表，比如：article_201601，article_201602。
按热度拆分，高点击率的词条生成各自的一张表，低热度的词条都放在一张大表里，待低热度的词条达到一定的贴数后，再把低热度的表单独拆分成一张表。
根据ID的值放入对应的表，第一个表user_0000，第二个100万的用户数据放在第二个表user_0001中，随用户增加，直接添加用户表就行了。

MySQL分库

为什么要分库？
数据库集群环境后都是多台slave，基本满足了读取操作，但是写入或者说大数据、频繁的写入数据操作对master性能影响就比较大，这个时候，单库并不能解决大规模并发写入的问题，所以就会考虑分库。

分库是什么？

一个库里表太多了，导致了海量数据，系统系统下降，把原本存储于一个库的表拆分存储到多个库上，通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上。

优点：

减少增量数据写入时的锁对查询的影响
由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单次查询所需的检索行数变少，减少了磁盘IO，时延变短

MySQL索引结构

首先要明白索引（index）是在存储引擎（storage engine）层面实现的，而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型，它们的实现和行为也可能有所差别。

B+Tree索引

MyISAM 和 InnoDB 存储引擎，都使用 B+Tree的数据结构，它相对与 B-Tree结构，所有的数据都存放在叶子节点上，且把叶子节点通过指针连接到一起，形成了一条数据链表，以加快相邻数据的检索效率。

先了解下 B-Tree 和 B+Tree 的区别

B-Tree

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。而系统一个磁盘块的存储空间往往没有这么大，因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述 B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data 为一行记录中除主键外的数据。对于不同的记录，key值互不相同。当数据太大的话就会导致节点所占的空间就会增大，导致增大查询时的磁盘I/O次数，进而影响查询效率

B+Tree

B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构，InnoDB 存储引擎就是用 B+Tree 实现其索引结构。B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

非叶子节点只存储键值信息；
所有叶子节点之间都有一个链指针；
数据记录都存放在叶子节点中

将B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

B+Tree性质

通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。

MyISAM主键索引与辅助索引的结构

MyISAM引擎的索引文件和数据文件是分离的。MyISAM引擎索引结构的叶子节点的数据域，存放的并不是实际的数据记录，而是数据记录的地址。索引文件与数据文件分离，这样的索引称为"非聚簇索引"。MyISAM的主索引与辅助索引区别并不大，只是主键索引不能有重复的关键字。

在MyISAM中，索引（含叶子节点）存放在单独的.myi文件中，叶子节点存放的是数据的物理地址偏移量（通过偏移量访问就是随机访问，速度很快）。

主索引是指主键索引，键值不可能重复；辅助索引则是普通索引，键值可能重复。

通过索引查找数据的流程：先从索引文件中查找到索引节点，从中拿到数据的文件指针，再到数据文件中通过文件指针定位了具体的数据。辅助索引类似。

InnoDB主键索引与辅助索引的结构

InnoDB引擎索引结构的叶子节点的数据域，存放的就是实际的数据记录（对于主索引，此处会存放表中所有的数据记录；对于辅助索引此处会引用主键，检索的时候通过主键到主键索引中找到对应数据行），或者说，InnoDB的数据文件本身就是主键索引文件，这样的索引被称为"“聚簇索引”，一个表只能有一个聚簇索引。

主键索引：

我们知道InnoDB索引是聚集索引，它的索引和数据是存入同一个.idb文件中的，因此它的索引结构是在同一个树节点中同时存放索引和数据，如下图中最底层的叶子节点有三行数据，对应于数据表中的id、stu_id、name数据项。

在Innodb中，索引分叶子节点和非叶子节点，非叶子节点就像新华字典的目录，单独存放在索引段中，叶子节点则是顺序排列的，在数据段中。Innodb的数据文件可以按照表来切分（只需要开启innodb_file_per_table)，切分后存放在xxx.ibd中，默认不切分，存放在xxx.ibdata中。

辅助（非主键）索引：

这次我们以示例中学生表中的name列建立辅助索引，它的索引结构跟主键索引的结构有很大差别，在最底层的叶子结点有两行数据，第一行的字符串是辅助索引，按照ASCII码进行排序，第二行的整数是主键的值。

这就意味着，对name列进行条件搜索，需要两个步骤：

① 在辅助索引上检索name，到达其叶子节点获取对应的主键；

② 使用主键在主索引上再进行对应的检索操作

这也就是所谓的“回表查询”

InnoDB 索引结构需要注意的点

数据文件本身就是索引文件
表数据文件本身就是按 B+Tree 组织的一个索引结构文件
聚集索引中叶节点包含了完整的数据记录
InnoDB 表必须要有主键，并且推荐使用整型自增主键

正如我们上面介绍 InnoDB 存储结构，索引与数据是共同存储的，不管是主键索引还是辅助索引，在查找时都是通过先查找到索引节点才能拿到相对应的数据，如果我们在设计表结构时没有显式指定索引列的话，MySQL 会从表中选择数据不重复的列建立索引，如果没有符合的列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，并且这个字段长度为6个字节，类型为整型。

那为什么推荐使用整型自增主键而不是选择UUID？

UUID是字符串，比整型消耗更多的存储空间；
在B+树中进行查找时需要跟经过的节点值比较大小，整型数据的比较运算比字符串更快速；
自增的整型索引在磁盘中会连续存储，在读取一页数据时也是连续；UUID是随机产生的，读取的上下两行数据存储是分散的，不适合执行where id > 5 && id < 20的条件查询语句。
在插入或删除数据时，整型自增主键会在叶子结点的末尾建立新的叶子节点，不会破坏左侧子树的结构；UUID主键很容易出现这样的情况，B+树为了维持自身的特性，有可能会进行结构的重构，消耗更多的时间。

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

保证数据一致性和节省存储空间，可以这么理解：商城系统订单表会存储一个用户ID作为关联外键，而不推荐存储完整的用户信息，因为当我们用户表中的信息（真实名称、手机号、收货地址···）修改后，不需要再次维护订单表的用户数据，同时也节省了存储空间。

Hash索引

主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。

检索算法：在检索查询时，就再次对待查关键字再次执行相同的Hash算法，得到Hash值，到对应Hash表对应位置取出数据即可，如果发生Hash碰撞，则需要在取值时进行筛选。目前使用Hash索引的数据库并不多，主要有Memory等。

MySQL目前有Memory引擎和NDB引擎支持Hash索引。

full-text全文索引

全文索引也是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从MYSQL5.6版本提供对全文索引的支持。
它用于替代效率较低的LIKE模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。
同样使用B-Tree存放索引数据，但使用的是特定的算法，将字段数据分割后再进行索引（一般每4个字节一次分割），索引文件存储的是分割前的索引字符串集合，与分割后的索引信息，对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。

R-Tree空间索引

空间索引是MyISAM的一种特殊索引类型，主要用于地理空间数据类型

为什么Mysql索引要用B+树不是B树？

用B+树不用B树考虑的是IO对性能的影响，B树的每个节点都存储数据，而B+树只有叶子节点才存储数据，所以查找相同数据量的情况下，B树的高度更高，IO更频繁。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。其中在MySQL底层对B+树进行进一步优化：在叶子节点中是双向链表，且在链表的头结点和尾节点也是循环指向的。

面试官：为何不采用Hash方式？

因为Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ Tree是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描。

哈希索引不支持多列联合索引的最左匹配规则，如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

哪些情况需要创建索引

主键自动建立唯一索引
频繁作为查询条件的字段
查询中与其他表关联的字段，外键关系建立索引
单键/组合索引的选择问题，高并发下倾向创建组合索引
查询中排序的字段，排序字段通过索引访问大幅提高排序速度
查询中统计或分组字段

哪些情况不要创建索引

表记录太少
经常增删改的表
数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）
频繁更新的字段不适合创建索引（会加重IO负担）
where条件里用不到的字段不创建索引

MySQL高效索引

覆盖索引（Covering Index）,或者叫索引覆盖，也就是平时所说的不需要回表操作

就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据，当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含（覆盖）满足查询结果的数据就叫做覆盖索引。
判断标准

使用explain，可以通过输出的extra列来判断，对于一个索引覆盖查询，显示为using index，MySQL查询优化器在执行查询前会决定是否有索引覆盖查