mysql数据结构

1. mysql数据库

• 数据库是 “按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合
• MySQL 是一款安全、跨平台、高效的，并与 PHP、Java 等主流编程语言紧密结合的数据库系统。该数据库系统是由瑞典的 MySQL AB 公司开发、发布并支持，由 MySQL 的初始开发人员 David Axmark 和 Michael Monty Widenius 于 1995 年建立的。
• MySQL 的象征符号是一只名为 Sakila 的海豚，代表着 MySQL 数据库的速度、能力、精确和优秀本质。

2. mysql数据结构

在 InnoDB 存储引擎中，所有的数据都被逻辑地存放在表空间中，表空间（tablespace）是存储引擎中最高的存储逻辑单位，在表空间的下面又包括段（segment）、区（extent）、页（page），他们之间的关系如下：

2.1 表空间（tablespace）

表空间是一种用于存储数据库对象 (如：数据文件)的逻辑空间，是Mysql中信息存储的最大逻辑单元

2.1.1 系统表空间（System Tablespace）

就是我们常说的共享表空间，是我们在初始化mysql实例时生成的（在初始化mysql实例时会读取my.cnf中的innodb_data_file_path参数，然后初始出相应的文件ibdata1、ibdata2 …，至于文件多大，有多少个，看你my.cnf中的参数是怎样设置的）。mysql默认的系统表空间文件大小是12M，只有一个文件(ibdata1)，它默认是保存到你mysql实例的datadir变量的目录下

2.1.2 表文件表空间（File-Per-Table Tablespaces）

表文件表空间是一个单表表空间，该表创建于自己的数据文件中，而非创建于系统表空间中。当innodb_file_per_table选项开启时，表将被创建于表文件表空间中。否则，innodb将被创建于系统表空间中。每个表文件表空间由一个.ibd数据文件代表，该文件默认被创建于相应数据库目录中。表文件表空间支持动态（DYNAMIC)和压缩（commpressed)行格式。

2.1.3 通用表空间（General Tablespaces）

通用表空间为通过create tablespace语法创建的共享表空间。通用表空间可以创建于mysql数据目录外的其他表空间，其可以容纳多张表，且其支持所有的行格式。通过create table tab_name … tablespace [=] tablespace_name或alter table tab_name tablespace [=] tablespace_name语法将其添加与通用表空间内，对应的数据库文件是mysql.ibd

2.1.4 undo表空间（undo tablespace）

undo表空间由一个或多个包含undo日志的文件组成。innodb_undo_tablespace配置选项控制undo表空间的数目。undo表空间创建于innodb_undo_directory配置选项确定的位置，该选项典型被用于将undo日志放于不同的存储设备上。如果该选项没有确定任何路径，undo表空间则被默认创建于mysql数据目录（datadir）下。

2.1.5 临时表空间（Temporary Tablespace）

用户创建的临时表和磁盘内部临时表创建于共享临时表空间中。innodb_temp_data_file选项确定临时表空间数据文件的相对路径、名字、大小和属性等。如果该选项未确定任何值，默认情况下，系统将在innodb_data_home_dir确定的目录下创建一个叫ibtmp1的自动扩展的数据文件，该文件将稍大于12m。

接下来我们去数据库安装目录下熟悉熟悉这些文件

– 显示数据库的存储目录

show variables like 'datadir';

– 进入到该位置

docker ps
docker exec -it 7a1da4192642 /bin/bash
cd /var/lib/mysql

2.2 页（page）

2.2.1 页的介绍

• InnoDB将数据逻辑上划分为若干个页，InnoDB中页的大小默认为16KB
• 页作为磁盘和内存之间交互的基本单位，也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中16KB内容刷新到磁盘中
• 在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载，即数据库I/O操作的最小单位是页。
• 页不仅仅是IO的最小单位，它还是所有关系的最小单位，也就是说，如果一个页包含了表A的行，那么它将只包含表A的行
• 页与页之间通过双向链表连接，一个页中存储多个行记录，记录与记录之间通过单向链表连接。

2.2.2 页的数据结构

• File Header：文件头，描述页的信息
  

---

主要先看下如下的一些信息：

- FIL_PAGE_SPACE_OR_CHKSUM

这个代表当前页面的校验和（checksum），每当一个页面在内存中修改了，在同步之前就要把它的校验和算出来。在一个页面被刷到磁盘的时候，首先被写入磁盘的就是这个 checksum。

- FIL_PAGE_OFFSET

每一个页都有一个单独的页号，InnoDB 通过页号来唯一定位一个页。如某独立表空间 a.ibd 的大小为1GB，页的大小默认为16KB，那么总共有65536个页。FIL_PAGE_OFFSET 表示该页在所有页中的位置。若此表空间的ID为10，那么搜索页（10，1）就表示查找表a中的第二个页。

- FIL_PAGE_PREV 和 FIL_PAGE_NEXT

InnoDB 是以页为单位存放数据的，InnoDB 表是索引组织的表，数据是按主键顺序存放的。数据可能会分散到多个不连续的页中存储，这时就会通过 FIL_PAGE_PREV 和 FIL_PAGE_NEXT 将上一页和下一页连起来，就形成了一个双向链表。这样就通过一个双向链表把许许多多的页就都串联起来了，而无需这些页在物理上真正连着。
- FIL_PAGE_TYPE

这个代表当前页的类型，InnoDB 为了不同的目的而设计了许多种不同类型的页。

---

• Page Header：页头，页的状态信息
  

• Infimum + Supremum：最大和最小记录，这是两个虚拟的行记录
  

• User Records：用户记录，存储行记录内容

• Free Space：空闲记录，页中还没有被使用的空间

• Page Directory：页目录，存储用户记录的相对位置
  

  首先我们要知道，InnoDB 的数据是索引组织的，B+树索引本身并不能找到具体的一条记录，只能找到该记录所在的页，页是存储数据的最小基本单位。如下图，如果我们要查找 ID=32 的这行数据，通过索引只能定位到第 17 页。
  定位到页之后我们可以通过最小记录Infimum的记录头的next_record沿着链表一直往后找，就可以找到 ID=32 这条记录了。
  但是可以想象，沿着链表顺序查找的性能是很低的。所以，页中的数据其实是分为多个组的，这看起来就形成了一个子目录，通过子目录就能缩小查询的范围，提高查询性能了。
  Page Directory 翻译过来就是页目录，这部分存放的就是一个个的槽（Slot），页中的记录分为了多个组，槽就存放了每个组中最大的那条记录的相对位置（记录在页中的相对位置，不是偏移量）。这个组有多少条记录，就通过最大记录的记录头中的 n_owned 来表示。有了目录槽之后，InnoDB就会利用二叉查找迅速确定记录所在的槽，并找到该槽所在分组中主键值最小的那条记录，再通过最小记录的 next_record 遍历记录，就能快速定位到匹配的那条记录了。
  

• File Tailer：文件尾，校验页是否完整

  前面介绍 File Header 时说过，在将页写入磁盘时，最先写入的便是 File Header 中的 FIL_PAGE_SPACE_OR_CHKSUM 值，就是页面的校验和。在写入的过程中，数据库可能发生宕机，导致页没有完整的写入磁盘。为了校验页是否完整写入磁盘，InnoDB 就设置了 File Trailer 部分。File Trailer 中只有一个FIL_PAGE_END_LSN，占用8字节。FIL_PAGE_END_LSN 又分为两个部分，前4字节代表页的校验和；后4字节代表页面被最后修改时对应的日志序列位置（LSN），与File Header中的FIL_PAGE_LSN相同。默认情况下，InnoDB存储引擎每次从磁盘读取一个页就会检测该页的完整性，这时就会将 File Trailer 中的校验和、LSN 与 File Header 中的 FIL_PAGE_SPACE_OR_CHKSUM、FIL_PAGE_LSN 进行比较，以此来保证页的完整性。
  

2.2.3 页的类型

– InnoDB 为了不同的目的而设计了许多种不同类型的

2.3 区（extent）

• 区是页的集合，一个区包含64个连续的页，默认大小为 1MB (64*16K)。

• 通常，区和页一样，是所有关系的单位，如果区中的一个页从属于表A或者索引B，那么区中的所有8个页都会从属于一个相同的对象。对于很小的表或者索引来说，情况会有所不同，它们不能充满整个区，在这种情况下， 超过一个表或者索引会被定位到一个相同的区中，但对于大部分对象来说，区仍是所有关系的单位。

• 所以说数据库不认为表扫描是读取表的所有行，它认为表扫描是读取表的所有页或者区，它知道它将很可能并发的处理16K，64K的IO请求来读取整个表，这将使得表扫描变得不至于像每行读取那样吓人。

• 读取页和区不仅意味着做一个表扫描比我们期望的具有更少的工作，它还意味着，为了从非聚集索引受益，一个查询应该比我们期望的更具有选择性

  理解举例：假设有一张10W条记录的学生表,该表有主键索引id，非聚簇索引age

  假设每页能够存100条数据，则10W条数据被分成了1000页

  如果我们现在需要查询年龄大于18岁的学生 ，已知年龄大于18岁的学生总共有2000个，占总学生的2%，可以这样理解平均每50个学生中就有一个大于18岁的，我们每次查询就需要从50行中选择一行，那么平均每个页变会包含2个请求行，则平均每个页都包含了至少1个请求行，换句话说，几乎每个页都 需要被读取来满足这个查询，那么用表扫描来读取它们是最好的选择，一次读取一个区，平均每次都装在16（2*8）个请求行进入内存

  | id | name | age |
  | :—-: | :—-: | :—-: |
  | 1 | 张三 | 18 |
  | 2 | 李四 | 14 |
  | 3 | 王五 | 13 |
  | …… | …… | …… |

2.4 段（segment）

段(Segment)分为索引段，数据段，回滚段等。其中索引段就是非叶子结点部分，而数据段就是叶子结点部分，回滚段用于数据的回滚和多版本控制。一个段包含256个区(256M大小)。

3. 数据库引擎

3.1 引擎是什么？

MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

查看支持的引擎

show engines;

3.2 常用的引擎

3.2.1 InnoDB

InnoDB是MySQL默认的事务型引擎，也是最重要、最广泛的存储引擎。它的设计是用来处理大量短期事务，短期事务大部分是正常提交的，很少回滚。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中，也很流行。除了非常特别的原因需要使用其他引擎，InnoDB也是非常好值得花时间研究的对象。

InnoDB的数据存储在表空间中，表空间是由InnoDB管理的黑盒文件系统，由一系列系统文件组成。InnoDB可以将每个表的数据和索引存放在单独的文件中。InnoDB也可以使用裸设备作为表空间存储介质。

InnoDB通过间隙锁（next-key locking）防止幻读的出现。InnoDB是基于聚簇索引建立，与其他存储引擎有很大的区别，聚簇索引对主键查询有很高的性能，不过它的二级索引（secondary index，非主键索引）必须包含主键列。所以如果主键列很大的话，索引会很大。

3.2.2 MyISAM

在5.1之前，MyISAM是默认的引擎，MyISAM有大量的特心态，包括全文索引、压缩、空间函数。但是MyISAM不支持事务和行级锁，而且在崩溃后无法安全恢复。即使后续版本中MyISAM支持了事务，但是很多人的概念中依然是不支持事务的引擎。

MyISAM并不是无所事处。对于一些只读数据，或者表空间较小，可以忍受恢复操作，可以使用MyISAM。MyISAM会将表存储在两个文件中：数据文件、索引文件。分别是.MYD、.MYI扩展名。MyISAM表可以包含动态或者静态行。MySQL会根据表定义选择那种行格式。MyISAM表的行记录数，取决于磁盘空间和操作系统中的单个文件最大尺寸。

在MySQL中，默认配置只能存储256TB的数据。因为指向数据记录的指针长度是6字节。需要修改可以修改表的MAX_ROWS和AVG_ROW_LENGTH选项。两个相乘是最大的大小。会导致重建索引。

MyISAM是对整个表加锁，而不是行锁，读取的时候对表加共享锁，写入的时候加排他锁。但是在表有读取查询的同时，也可以往表内写入记录。

对于MyISAM，即使是Blob，Text等等长字段，也可以基于前500字符创建索引，MyISAM支持全文索引，这是一个基于分词创建的索引，也可以支持复杂的查询。

MyISAM可以选择延迟更新索引键，在创建表的时候指定delay_key_write选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是写到缓存区，只有在清理缓存区或者关闭表的时候才会将索引写入磁盘。这可以极大的提升写入性能，但是在主机崩溃时会造成索引损坏，需要执行修复操作。

MyISAM另一个特性是支持压缩表。如果数据在写入后不会修改，那么这个表适合MyISAM压缩表。可以使用myisampack对MyISAM表进行打包，压缩表是不可以修改数据的。压缩表可以极大的减少磁盘占用，因此可以减少磁盘IO，提升性能，压缩表也支持索引，但是索引也是只读的。

整体来说MyISAM并没有那么不堪，但是由于没有行锁机制，所以在海量写入的时候，会导致所有查询处于Locked状态。

3.2.3 Archive

Archive引擎支持是Insert，Select操作，现在支持索引，Archive引擎会缓存所有的写，并利用zlib对写入行进行压缩，所以比MyISAM表的磁盘IO更少。但是在每次Select查询都需要执行全表扫描。所以在Archive适合日志和数据采集应用。这类应用在分析时往往需要全表扫描忙活着更快的Insert操作场景中也可以使用。

Archive引擎支持行级锁和专用的缓存区，所以可以实现高并发写入，在查询开始到返回表存在的所有行数之前，Archive会阻止其他Select执行，用来实现一致性读。另外也实现了批量写入结束前批量写入数据对读操作不可见，这种机制模仿了事务和MVCC的特性，但是Archive不是一个事务型引擎，而是针对高写入压缩做了优化的简单引擎。

3.2.4 Blackhole

Blackhole没有实现任何存储机制，它会舍弃所有写入数据，不做任何保存，但是服务器会记录Blackhole表的日志，用于复制数据到备库，或者只是简单的记录到日志，这种特殊的存储引擎可以在一些特俗的复制架构和日志审核时发挥作用。但是不推荐。

3.2.5 CSV

CSV引擎可以将普通的CSV文件作为MySQL表来处理，但是这种表不支持索引，CSV可以在数据库运行时拷贝或者拷出文件，可以将Excel等电子表格中的数据存储未CSV文件，然后复制到MySQL中，就能在MySQL中打开使用。同样，如果将数据写入到一个CSV引擎表，其他外部程序也可以从表的数据文件中读取CSV的数据。因此CSV可以作为数据交换机制。非常好用。

3.2.6 Federated

Federated引擎是访问其他MySQL服务器的一个代理，它会创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或者发送需要的数据。最初设计该存储引擎是为了和企业级数据库如MicrosoftSQLServer和Oracle的类似特性竞争的，可以说更多的是一种市场行为。尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

3.2.7 Memroy

如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表（以前也叫做HEAP表）是非常有用的。Memory表至少比MyISAM表要快一个数量级，因为所有的数据都保存在内存中，不需要进行磁盘I/O。Memory表的结构在重启以后还会保留，但数据会丢失。

Memroy表在很多场景可以发挥好的作用：

用于查找（lookup）或者映射（mapping）表，例如将邮编和州名映射的表。
用于缓存周期性聚合数据（periodicallyaggregateddata）的结果。
用于保存数据分析中产生的中间数据。
Memory表支持Hash索引，因此查找操作非常快。虽然Memory表的速度非常快，但还是无法取代传统的基于磁盘的表。Memroy表是表级锁，因此并发写入的性能较低。它不支持BLOB或TEXT类型的列，并且每行的长度是固定的，所以即使指定了VARCHAR列，实际存储时也会转换成CHAR，这可能导致部分内存的浪费。如果MySQL在执行查询的过程中需要使用临时表来保存中间结果，内部使用的临时表就是Memory表。如果中间结果太大超出了Memory表的限制，或者含有BLOB或TEXT字段，则临时表会转换成MyISAM表。

3.2.8 Merge

Merge引擎是MyISAM引擎的一个变种。Merge表是由多个MyISAM表合并而来的虚拟表。如果将MySQL用于日志或者数据仓库类应用，该引擎可以发挥作用。但是引入分区功能后，该引擎已经被放弃

3.2.9 NDB

NDB集群存储引擎，作为SQL和NDB原生协议之间的接口。MySQL服务器、NDB集群存储引擎，以及分布式的、share-nothing的、容灾的、高可用的NDB数据库的组合，被称为MySQL集群（MySQLCluster）。

4. 数据库索引

数据库索引指的是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中的数据。简单理解就是类似书籍的目录，独立于书籍内容之外，能够快速根据目录查到到需要的文章的页码。如果没有目录，我们就要一页一页地翻页来查找文章，同理，如果没有索引的话，数据库系统只能一行一行地查找数据

优劣势

• 所有的MySql列类型(字段类型)都可以被索引，也就是可以给任意字段设置索引。
• 大大加快数据的查询速度。
• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
• 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或者优化查询。

4.1 索引分类

按数据结构分类：B+tree索引、Hash索引、Full-text索引。
按物理存储分类：聚集索引、非聚集索引（也叫二级索引、辅助索引）。
按字段特性分类：主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索引(INDEX)、全文索引(FULLTEXT)。
按字段个数分类：单列索引、联合索引（也叫复合索引、组合索引）。

4.1.1 按数据结构分类

4.1.1.1 B+tree索引

B+tree 是在B树基础上的一种优化，其更适合做存储索引结构。在 B+tree 中，非叶子节点上仅存储键值，不存储数据；而所有数据记录均存储在叶子节点上，并且数据是按照顺序排列的。此外在 B+tree 中各个数据页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。B+tree 的结构图如下：

---

B+tree 结构实现数据索引具有如下优点：

* 非叶子节点上可以存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树也就会变得更矮更胖。这样一来我们查找数据进行磁盘I/O的次数就会大大减少，数据查询的效率也会更快，在生产环境，一般树的层级为3-4层
* 所有数据记录都有序存储在叶子节点上，就会使得范围查找，排序查找，分组查找以及去重查找变得异常简单。
* 数据页之间、数据记录之间都是通过链表链接的，有了这个结构的支持就可以方便的在数据查询后进行升序或者降序操作。

怎样查询InnoDB树的层级
InnoDB系统表介绍
层级计算

SELECT b.name 表名称,
         a.name 索引名称,
         a.index_id 索引ID,
         a.type 索引类型,
         a.space,
         a.PAGE_NO
FROM information_schema.INNODB_INDEXES a, information_schema.INNODB_TABLES b
WHERE a.table_id = b.table_id
        AND a.space <> 0
		
show global variables like 'innodb_page_size'		

4.1.1.2 Hash索引

Hash索引是基于Hash算法实现的，如上图所示，我们将一系列的最终的键值通过哈希函数转化为存储实际数据桶的地址数值。值本身存储的地址就是基于哈希函数的计算结果，而搜索的过程就是利用哈希函数从元数据中推导出桶的地址。（对Hash算法不太理解就往这里看：HashMap核心知识-深度学习） 所以基于Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B+Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的I/O访问，所以Hash索引在精确查询时的效率要远高于B+Tree索引。虽然Hash索引效率高，但是Hash索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些：

Hash索引仅仅能满足等值查询，不能进行范围查询

由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。

Hash索引无法通过操作索引来排序

由于 Hash索引中存放的是经过 Hash 计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。

组合Hash索引不能利用部分索引键进行查询

对于组合Hash索引，索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

Hash索引依然需要回表扫描

Hash索引是将索引键通过 Hash 运算之后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键可能存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B+Tree索引高

区分度低的索引键（如，性别），如果创建Hash索引，那么将会存在大量记录指针信息与同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

4.1.1.3 Full-text索引

Full-text索引一般使用倒排索引实现。倒排索引同B+tree索引一样，也是一种索引结构。

MySQL中InnoDB存储引擎在之前版本中是不支持全文检索的，要使用全文检索的话只能使用MySIAM存储引擎。在 MySQL 5.6.4 版本中InnoDB存储引擎才开始支持Full-text索引。

对于文本类型的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。

Full-text索引的查询有自己特殊的语法，而不能使用 LIKE 模糊查询的语法，语法如下：

SELECT * FROM table_name MATCH(ft_index) AGAINST('查询字符串');

4.1.2 按物理存储分类

4.1.2.1 聚簇索引

• 聚簇索引就是按照每张表的主键构造一颗 B+tree，同时叶子节点中存放的就是整张表的行记录数据，聚集索引的叶子节点被称为数据页。
• InnoDB表要求必须有聚簇索引，默认在主键字段上建立聚簇索引，在没有主键字段的情况下，表的第一个非空的唯一索引将被建立为聚簇索引，在前两者都没有的情况下，InnoDB将自动生成一个隐式的自增id列，并在此列上建立聚簇索引。
• InnoDB 有且只能有一个聚簇索引，为什么？

4.1.2.2 非聚集索引

非聚集索引的结构和聚集索引基本相同（非叶子结点存储的都是索引指针），区别在于叶子节点存放的不是行数据而是数据主键。因此在使用非聚集索引进行查找时，需要先查找到主键值，然后再到聚集索引中进行查找。

回表是什么？通俗的讲就是，如果索引的列在 select 所需获得的列中（因为在 mysql 中索引是根据索引列的值进行排序的，所以索引节点中存在该列中的部分值）或者根据一次索引查询就能获得记录就不需要回表，如果 select 所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就叫回表

4.1.2.3 二级索引(覆盖索引)

二级索引属于非聚簇索引的另一种分类，区别在于叶子节点除了存放数据主键之外还可以储存表中其他的字段，来进一步减小查询的开销

4.1.3 按物理存储分类

4.1.3.1 主键索引(PRIMARY KEY)

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定了主键，就会创建主键索引， CREATE INDEX不能用来创建主键索引，使用 ALTER TABLE来代替

4.1.3.2 唯一索引(UNIQUE)

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有一个空值。如果是组合索引，则列值的组合必须一

4.1.3.3 普通索引(INDEX)

这是最基本的索引，它没有任何限制

4.1.3.4 全文索引(FULLTEXT)

FULLTEXT索引用于全文搜索。只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于 CHAR， VARCHAR和 TEXT列。

4.1.4 按字段个数分类

4.1.4.1 单列索引

只包含一个字段的索引叫做单列索引

4.1.4.2 联合索引

包含两个或以上字段的索引叫做复合索引（或组合索引）。建立复合索引时，字段的顺序极其重要

一个例子，以index(a,b,c)复合索引为例：

语句	索引是否生效
where a = 1	是，字段 a 索引生效
where a = 1 and b = 2	是，字段 a 和 b 索引生效
where a = 1 and b = 2 and c = 3	是，全部生效
where b = 2 and a = 1 and c = 3	是，全部生效
where b = 2 或 where c = 3	否
where a = 1 and c = 3	字段 a 生效，字段 c 失效
where a = 1 and b > 2 and c = 3	字段 a，b 生效，字段 c 失效
where a = 1 and b like ‘xxx%’ and c = 3	字段 a，b 生效，字段 c 失效
……	……

• 最左前缀匹配原则，非常重要的原则

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a 1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

• =和in可以乱序

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

4.2 哪些情况需要创建索引

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其它表关联的字段，外键关系建立索引
• 单键/组合索引的选择问题，who？（在高并发下倾向创建组合索引）
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段

4.3 哪些情况不需要创建索引

• 表记录太少
• where条件里用不到的字段不创建索引
• 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录，还会更新索引，加重IO负担
• 经常增删改的表：虽然提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
• 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

4.3 索引失效的场景

CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `id_no` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '身份编号',
  `username` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `union_idx` (`id_no`,`username`,`age`),
  KEY `create_time_idx` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

4.3.1 联合索引不满足最左匹配原则

explain select * from user where id_no = '1000001';
explain select * from user where id_no = '1000001' and username = '张1'
explain select * from user where id_no = '1000001' and age = 63;


-- 失效查询
explain select * from user where username = '张1' and age = 63;

4.3.2 使用了select * ，从而没有走覆盖索引

-- 注意Extra
explain select * from user where id_no = '1000001' and username = '张1';
explain select id_no, username, age from user where id_no = '1000001' and username = '张1';

4.3.3 索引列参与计算

explain select * from user where id + 1 = 2 ;

4.3.4 索引列使用了函数

explain select * from user where SUBSTR(id_no,1) = '10000111';

4.3.5 错误的Like使用

explain select * from user where id_no like '%10000111%';

explain select * from user where id_no like '10000111%';

4.3.6 类型隐式转换

explain select * from user where id_no = 1000001;

4.3.7 使用OR操作

explain select * from user where id_no = '1000001' or username = '张1';

4.3.8 两列做比较

explain select * from user where id < age;

4.3.9 不等于比较

explain select * from user where id_no != '1000001';

-- 主键索引使用不等于也是走索引的
explain select * from user where id != 1

4.3.10 is not null作

explain select * from user where id_no is not null;

4.3.11 not in和not exists

explain select * from user where id not in (2,3);
explain select * from user where id_no not in ('1000001','1000009');

-- 正常情况
explain select * from user where id in (2,3);
explain select * from user where id_no in ('1000001','1000009');

4.4 explain字段详细说明

参考

4.5 索引碎片

4.5.1 前置说明

• 每一个页包含了一个指向上一个和下一个页的指针。页的逻辑顺序和物理顺序或许是不一致的。指向下一个页的指针后续会指向位于同一个区的页，或者指向几百个页之前的一个页（也可能是之后）。
• 页的物理顺序和逻辑顺序越一致，扫描整个索引所需要的IO数就越少。当两个顺序一致的时候，每次IO可读取一整个区或者更多。同样的，页的物理顺序和逻辑顺序越相关，那么使用的预读数便越多（预读：在使用之前，先将页存入内存）。不管是扫描整个索引，还是只扫描索引的一部分，这都是确定无疑的。
• 索引碎片的分类：
  • 一个页内空闲容量的百分比称为页的内部碎片；
  • 逻辑顺序和物理顺序的偏差称为外部碎片；

4.5.2 内部碎片简述

覆盖率 = 数据字节数 / 一页字节数

内部碎片率 = 1 - 覆盖率

• 内部碎片通常是以覆盖率的形式表现出来，并且其体现的是字节数上的覆盖率而不是条目上的覆盖率；
• 一个100%充满的是没有内部碎片的，但通常来说一个页是否被充满还与条目的大小相关，当一个覆盖率是96%的索引页而剩余的4%的页空间不够添加一个新的条目时，此时这个页是被完全充满的；

4.5.3 外部碎片简述

外部碎片率 = 索引叶子节点中的无序页数 / 叶子节点总页数

• 无序页：当前页物理上分配的紧邻的页不是偏移指针所指向的页，那么当前页便称之为无序页；

4.5.4 碎片产生原因

对于非聚簇索引来说无序的insert是外部碎片产生的主要因素，insert和delete是内部碎片产生的主要因素

4.5.5 碎片对性能的影响

在官方文档的说明中，索引碎片率超过30%就会极大的影响性能，必须考虑重建索引

• 内部碎片的影响：
  • 内部碎片会增加IO操作。当执行的查询扫描部分或全部的表/索引时，如果那个表/索引上面有内部碎片，它会增加额外的页读取；
  • 内部碎片降低缓存效率。当索引有碎片时，在缓存里就会占用更多的空间。这会降低缓存命中率，依次增加物理IO，同样增加逻辑读；
  • 增加数据库文件。需要更多的空间来存储额外页，并降低备份和还原性能；
• 外部碎片的影响：
  • 读取单条记录时，外部碎片不会影响性能，因为是直接到页里拿记录；
  • 在有序的索引扫描里，外部碎片会成为性能下降因素。性能下降的原因是磁盘的磁头需要物理磁盘上来回跳；