mysql运行的整体架构简介

Mysql是由两部分构成，一部分是服务器程序，一部分是客户端程序。
服务器程序又包括两部分：
第一部分server层包括连接器、查询缓存、分析器、优化器、执行器等。涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等
第二部分是存储引擎层负责数据的存储和提取。存储引擎有多种选择，主要有InnoDB、MyISAM、Memory等。

要操作Mysql数据库，首先客户端要连接上mysql服务器程序。
连接器： 负责跟客户端建立连接、获取权限、维持和管理连接。
连接命令：

mysql -h$ip -P$port -u$user -p

MySQL采用的TCP/IP协议进行网络通信，客户端和服务端之间通过三次握手建立连接。

连接上数据库后，就可以执行sql语句了（以查询语句为例）。

sql查询语句命中缓存
查询缓存： 当sql是查询语句，MySQL 拿到这个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。这个查询请求能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。查询缓存前要校验用户对表是否有查询权限。

查询缓存往往弊大于利：
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空，对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。
通过设置参数 query_cache_type，选择是否使用查询缓存。
mysql8.0已经将查询缓存的整块功能删掉了。

没有命中查询缓存，就要开始真正执行语句了
分析器： 分析器会做“词法分析”和“语法分析”以及“语义分析等，判断sql语句中的关键字，表，语法是否正确。

如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒，语法错误会提示第一个出现错误的位置，所以你要关注的是紧接“use near”，可以很方便检查sql语句。

优化器： 语法解析之后，服务器程序获得到了需要的信息，比如要查询的列是哪些，表是哪个，搜索条件是什么等等。但光有这些是不够的，因为我们写的MySQL语句执行起来效率可能并不是很高，MySQL的优化程序会对我们的语句做一些优化，如外连接转换为内连接、表达式简化、子查询转为连接等。可以通过expllian语句来查看某个sql语句的执行计划。

执行器： MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。先是校验权限，要先判断一下你对这个表 T 有没有执行查询的权限，然后根据表的引擎定义，去使用这个引擎提供的接口。

存储引擎： 储存数据，并提供读写接口。

存储引擎的一些操作：

查看当前服务器程序支持的存储引擎：

SHOW ENGINES;

设置表的存储引擎

-- 创建表时指定存储引擎
CREATE TABLE 表名(建表语句;
) ENGINE = 存储引擎名称;-- 修改表的存储引擎
ALTER TABLE 表名 ENGINE = 存储引擎名称;

查看表使用的存储引擎

 SHOW CREATE TABLE  表名

字符集和比较规则

字符集：表示字符的范围以及编码规则，字符编码规则是指一种映射规则，根据这个映射规则可以将某个字符映射成其他形式的数据以便在计算机中存储和传输。例如ASCII字符编码规定使用单字节中低位的7个比特去编码所有的字符，在这个编码规则下字母A的编号是65（ASCII码），用单字节表示就是0x41，因此写入存储设备的时候就是二进制的 01000001。
以下是ASCLL字符编码规则以及字符范围(128个)。

一些重要的字符集
GB2312字符集

 收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个，其他文字符号682个。同时这种字符集又兼容ASCII字符集，所以在编码方式上显得有些奇怪：-如果该字符在ASCII字符集中，则采用1字节编码。否则采用2字节编码。

GBK字符集

GBK字符集只是在收录字符范围上对GB2312字符集作了扩充，编码方式上兼容GB2312。

utf8字符集

收录地球上能想到的所有字符，而且还在不断扩充。
这种字符集兼容ASCII字符集，采用变长编码方式，编码一个字符需要使用1～4个字节

MySQL中支持的字符集
查看当前MySQL中支持的字符集

SHOW CHARSET;

MySQL中的utf8和utf8mb4区别

utf8mb3：阉割过的utf8字符集，只使用1～3个字节表示字符。
utf8mb4（mysql 5.5.3版本之后）：正宗的utf8字符集，使用1～4个字节表示字符。

某些中文生僻字或者emoji表情，是四个字符的，只能使用utf8mb4编码

字符集的比较规则：既比较两个字符大小的规则，每种字符集对应若干种比较规则，每种字符集都有一种默认的比较规则。例如：不区分大小写，按照中文拼音顺序等。

查看MySQL中支持的比较规则的命令

SHOW COLLATION 

MySQL有4个级别的字符集和比较规则

• 服务器级别
• 数据库级别
• 表级别
• 列级别

服务器级别

-- 查看Mysql服务器的字符集
SHOW VARIABLES LIKE 'character_set_server';-- 查看Mysql服务器的比较规则
SHOW VARIABLES LIKE 'collation_server';-- 修复服务器的字符集和比较规则
-- 可以在启动服务器程序时通过启动选项
-- 或者在服务器程序运行过程中使用SET语句修改这两个变量的值。
[server]
character_set_server=gbk
collation_server=gbk_chinese_ci

数据库级别

-- 查看数据库的字符集SHOW VARIABLES LIKE 'character_set_database';-- 查看数据库的比较规则
SHOW VARIABLES LIKE 'collation_database';-- 创建数据库时指定字符集和比较规则
CREATE DATABASE 数据库名[[DEFAULT] CHARACTER SET 字符集名称][[DEFAULT] COLLATE 比较规则名称];-- 修改数据库指定字符集和比较规则
ALTER DATABASE 数据库名[[DEFAULT] CHARACTER SET 字符集名称][[DEFAULT] COLLATE 比较规则名称];

表级别

-- 查看表的字符集
show create table <表名>;-- 查看表的比较规则
show table status from 数据库名 like '%表名%‘ ;-- 创建表时指定字符集和比较规则
CREATE TABLE 表名 (列的信息)[[DEFAULT] CHARACTER SET 字符集名称][[DEFAULT] COLLATE 比较规则名称]]-- 修改表指定字符集和比较规则
ALTER TABLE 表名[[DEFAULT] CHARACTER SET 字符集名称][[DEFAULT] COLLATE 比较规则名称]

列级别

-- 查看列的字符集和比较规则
select *  
FROM information_schema.`COLUMNS`
where TABLE_SCHEMA = '表名'-- 创建表时指定列的字符集和比较规则
CREATE TABLE 表名(列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],其他列...
);-- 修改列指定字符集和比较规则
ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

在转换列的字符集时需要注意，如果转换前列中存储的数据不能用转换后的字符集进行表示，就会发生错误。比方说原先列使用的字符集是utf8，列中存储了一些汉字，现在把列的字符集转换为ascii的话就会出错，因为ascii字符集并不能表示汉字字符。

创建时规则：

• 如果创建或修改列时，没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则
• 如果创建或修改表时，没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则
• 如果创建或修改数据库时，没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则

修改时规则：

• 只修改字符集，则比较规则将变为修改后的字符集默认的比较规则。
• 修改比较规则，则字符集将变为修改后的比较规则对应的字符集。

客户端和服务器通信中的字符集

• 客户端使用操作系统的字符集编码请求字符串，向服务器发送的是经过编码的一个字节串。

• 服务器将客户端发送来的字节串采用character_set_client代表的字符集进行解码，将解码后的字符串再按照character_set_connection代表的字符集进行编码。

• 如果character_set_connection代表的字符集和具体操作的列使用的字符集一致，则直接进行相应操作，否则的话需要将请求中的字符串从character_set_connection代表的字符集转换为具体操作的列使用的字符集之后再进行操作。

• 将从某个列获取到的字节串从该列使用的字符集转换为character_set_results代表的字符集后发送到客户端。

• 客户端使用操作系统的字符集解析收到的结果集字节串。

我们通常都把 character_set_client 、character_set_connection*、character_set_results*** 这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换

InnoDB记录结构

Mysql默认使用InnoDB作为存储引擎的数据存储结构,我们最常用到的存储引擎也是Innodb，故要了解的是使用InnoDB作为存储引擎的数据存储结构。

数据页简介
InnoDB是一个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启我们的数据还是存在的。而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。而我们知道读写磁盘的速度非常慢，和内存读写差了几个数量级，所以当我们想从表中获取某些记录时，InnoDB存储引擎需要一条一条的把记录从磁盘上读出来么？不，那样会慢死，InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。
行记录、页结构、区概念、段概念、独立表空间和系统表空间

行记录

在mysql中，行记录是数据存储的基本单位，我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。InnoDB存储引擎有四种行格式，分别是Compact（紧凑的）、Redundant（冗余的）、Dynamic（动态的）和Compressed（压缩的）。虽有不同，但原理相同。

创建或修改表的语句中指定行格式

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称ALTER TABLE 表名 ROW_FORMAT=行格式名称

查看当前表指定的行格式

show table status from lottery like '%表名%' ;

Compact（紧凑的）行格式

一条完整的记录其实可以被分为记录的额外信息和记录的真实数据两大部分

记录的额外信息
变长字段长度列表
存放所有变长字段的真实数据占用的字节长度，每个可变长字段的对应的长度按照列的顺序逆序存放；
变长字段中存储多少字节的数据是不固定的，故需要记录变长字段的真实数据占用的字节长度。
变长字段类型包括VARCHAR(M)、VARBINARY(M)、各种TEXT类型，各种BLOB类型等。
NULL值列表
用于标识表中允许存储NULL的列，是否为空。也是按照列的顺序逆序排列
记录头信息
由固定的5个字节组成


记录的真实数据
除了用户自己定义的列的数据以外，MySQL会为每个记录默认的添加一些列，
row_id （唯一标识一条记录，占6字节，当表中有主键或唯一约束，无改字段）、transaction_id（事务ID，占6字节）、roll_pointer（回滚指针，占7字节）

数据演示：
准备表和表数据：

-- 创建表
CREATE TABLE record_format_demo (
c1 VARCHAR(10),
c2 VARCHAR(10) NOT NULL,
c3 CHAR(10),
c4 VARCHAR(10)
) CHARSET=ascii ROW_FORMAT=COMPACT;
-- 插入表数据
INSERT INTO record_format_demo(c1, c2, c3, c4) 
values
('aaaa', 'bbb', 'cc', 'd'), 
('eeee', 'fff', NULL, NULL);

当前表结构：

表record_format_demo使用的字符集是ascii,行格式是compact,可以得到表中两条记录的存储格式详情如下：

Compact（紧凑的）行格式对CHAR(M) 类型的处理
对于 CHAR(M) 类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表。

Redundant行格式

与Compact（紧凑的）行格式相比，记录字段的位置，通过字段长度偏移列表。字段长度偏移指的是从第一列的真实数据的开始的到当前列的真实数据的结尾。

Redundant的记录头信息

与Compact（紧凑的）行格式的记录头信息相比
Redundant （冗余的）行格式多了 n_field 和 1byte_offs_flag 这两个属性。
Redundant 冗余的）行格式没有 record_type 这个属性。
当表record_format_demo使用的字符集是ascii,行格式是Redundant 冗余的）,可以得到表中两条记录的存储格式详情如下：

Redundant行格式对CHAR(M) 类型的处理
Redundant行格式不管该列使用的字符集是什么，只要是使用CHAR(M)类型，占用的真实数据空间就是该字符集表示一个字符最多需要的字节数和M的乘积。比方说使用utf8字符集的CHAR(10)类型的列占用的真实数据空间始终为30个字节，使用gbk字符集的CHAR(10)类型的列占用的真实数据空间始终为20个字节。

行溢出数据
对于VARCHAR(M)类型的列最多可以占用65535个字节，其中的M代表该类型最多存储的字符数量。这个65535个字节除了列本身的数据之外，还包括一些其他的数据（storage overhead），比如说以Compact（紧凑的）行格式为例，我们为了存储一个VARCHAR(M)类型的列，其实需要占用3部分存储空间：

• 真实数据
• 真实数据占用字节的长度
• NULL值标识（该列没有NOT NULL属性）

除去真实数据占用字节的长度占的两字节，NULL值标识标识占的一字节，真实数据还可使用65532字节。
utf8mb4字符集表示一个字符最多需要4个字节，那在该字符集下，M的最大取值就是16,383，就是说最多能存储16,383（也就是：65532/4）个字符。

行溢出处理
MySQL中磁盘和内存交互的基本单位是页，以页为基本单位来管理存储空间的，我们的记录都会被分配到某个页中存储。一个页的大小是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65532个字节，会出现一个页存放不了一条记录情况。
对于Compact和Reduntant行格式来说，如果某一列中的数据非常多的话，在本记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中，这个过程也叫做行溢出，存储超出768字节的那些页面也被称为溢出页。

Dynamic和Compressed行格式

Dynamic和Compressed行格式，这俩行格式和Compact行格式类似，在处理行溢出数据时有不同，它们不会在记录的真实数据处存储字段真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。
Compressed行格式和Dynamic不同的一点是，Compressed行格式会采用压缩算法对页面进行压缩，以节省空间。

InnoDB数据页结构

页是Innodb管理存储空间的基本单位，大小一般是16KB,InnoDB为了不同目的，有许多不同类型的页，比如存放表空间头部信息的页，存放Insert Buffer信息的页，存放INODE信息的页，存放undo日志信息的页等。我们聚焦的是那些存放我们表中记录的那种类型的页，称为数据页（索引（INDEX）页）。

数据页整体结构

数据页中的UserRecords（用户记录以及最大、最小记录）

行记录的格式已经了解了，现在重点看行记录中的记录头信息。
还是以Compact(紧凑的)行格式为例：


准备演示数据：

CREATE TABLE page_demo(c1 INT,c2 INT,
c3 VARCHAR(10000),PRIMARY KEY (c1)
)CHARSET=ascii ROW_FORMAT=Compact;INSERT INTO page_demo VALUES(1, 100, 'aaaa'), (2, 200, 'bbbb'), (3, 300, 'cccc'), (4, 400, 'dddd');

• delete_mask
  标记着当前记录是否被删除，占用1个二进制位，值为0的时候代表记录并没有被删除，为1的时候代表记录被删除掉了。
  这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗，所以只是打一个删除标记而已，所有被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为所谓的可重用空间（介绍事务的时候会详细介绍删除操作的详细过程）
• min_rec_mask
  B+树的每层非叶子节点中的最小记录都会添加该标记，我们插入的四条记录的min_rec_mask值都是0，意味着它们都不是B+树的非叶子节点中的最小记录。
• n_owned
  当前记录拥有的记录数。
• heap_no
  示当前记录在本页中的位置
• record_type
  表示当前记录的类型，一共有4种类型的记录，0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录。
• next_record
  表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。

页中记录按照主键从小到大的顺序形成了一个单链表,通过next_record作为引用找到下一个节点记录，页中维护了两个初始节点记录，Infimum记录（也就是最小记录） 的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是 Supremum记录（也就是最大记录）,record_type区分用户记录（0或1）、最小记录（2）和最大记录（3）。min_rec_mask只有B+树的每层非叶子节点中的最小记录是1，其他记录都是0。heap_no标识记录位置，最小记录为0，依次是用户记录递增，最后是最大记录。

数据页中的Free Space（空闲空间）

就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了。

数据页中的Page Directory（页目录）

Page Directory（页目录）为了快速在页中查找某条记录。
页目录构建规则：

• 将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。对每个分组中的记录条数是有规定的：对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。

• 每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录。

• 将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近页的尾部的地方，这个地方就是所谓的Page Directory，也就是页目录（此时应该返回头看看页面各个部分的图）。页面目录中的这些地址偏移量被称为槽（英文名：Slot），所以这个页面目录就是由槽组成的。
  
  页中查找页的过程

• 通过二分法确定该记录所在的槽，并找到该槽中主键值最小的那条记录。

• 通过记录的next_record属性遍历该槽所在的组中的各个记录。

数据页中的Page Header（页面头部）

是专门针对数据页记录的各种状态信息，记录的信息包括本页中已经存储了多少条记录，第一条记录的地址是什么，页目录中存储了多少个槽等等，，这个部分占用固定的56个字节。

• PAGE_DIRECTION
  用来表示最后一条记录插入方向的状态，假如新插入的一条记录的主键值比上一条记录的主键值大，我们说这条记录的插入方向是右边，反之则是左边。

• PAGE_N_DIRECTION
  表示最后插入记录的方向的连续数量，最后插入方向与上一次插入方向不同，清零重新计算。

数据页中的File Header（文件头部）

File Header针对各种类型的页都通用，它描述了一些针对各种页都通用的一些信息，比方说这个页的编号是多少，它的上一个页、下一个页是谁等， 这个部分占用固定的38个字节。

• FIL_PAGE_SPACE_OR_CHKSUM
  这个代表当前页面的校验和（checksum），快速比较页是否相同。
• FIL_PAGE_OFFSET
  每一个页都有一个单独的页号，InnoDB通过页号来可以唯一定位一个页。
  -FIL_PAGE_TYPE
  上面介绍的其实都是存储记录的数据页，其实还有很多别的类型的页
  
• FIL_PAGE_PREV和FIL_PAGE_NEXT
  FIL_PAGE_PREV和FIL_PAGE_NEXT就分别代表本页的上一个和下一个页的页号。所有的数据页其实是一个双链表。
  

数据页中的File Trailer（文件尾部）

可以分成2个小部分：

• 前4个字节代表页的校验和
  这个部分是和File Header中的校验和相对应的。每当一个页面在内存中修改了，在同步之前就要把它的校验和算出来，File Header在页面的前面，校验和会被首先同步到磁盘，当完全写完时，校验和也会被写到页的尾部，如果完全同步成功，则页的首部和尾部的校验和应该是一致的。
• 后4个字节代表页面被最后修改时对应的日志序列位置（LSN）
  这个部分也是为了校验页的完整性的。

B+树索引

InnoDB中的索引方案

为了更方便查找记录，我们把数据页存放到B+树这个数据结构中的最底层的节点上，这些节点也被称为叶子节点或叶节点。B+树的非叶子节点是都是目录页。
目录项记录和普通的用户记录的不同点：

• 目录项记录的record_type值是1，而普通用户记录的record_type值是0。

• 目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列，另外还有InnoDB自己添加的隐藏列。

• 记录头信息的min_rec_mask的属性，只有在存储目录项记录的页中的主键值最小的目录项记录的min_rec_mask值为1，其他别的记录的min_rec_mask值都是0。

select * from 表 where 主键 = 1；
查找过程：
1.如果B+树只有一层，也就是只有根节点，该节点也就是数据页，查找过程：

• 通过二分法确定该记录所在的槽，并找到该槽中主键值最小的那条记录。
• 通过记录的next_record属性遍历该槽所在的组中的各个记录。
  2.如果B+树有两层及以上，只有最底层的节点类型是数据页，其他层的节点类型查找过程：
• 从根节点出发，通过二分法确定该记录所在的槽，并找到该槽中主键值最小的那条记录。
  递归查找下一层节点（所在的槽分组中的节点数最多8条）。
• 当到达最底层，找到数据页节点，还是通过二分查找定该记录所在的槽，再通过记录的next_record属性遍历该槽所在的组中的各个记录。
  设B+树为节点上的记录数为m,一共有n个节点。
  访问的节点数量$\approx$ B+树深度 = $log{m}^n$
  每个节点访问时间复杂度=在槽中二分查找下一层节点时间复杂度
  $\approx$ O( $log{2}^m$
  )
  可知
  B+树中查找一个记录时间复杂度=访问的节点数量×每个节点访问时间复杂度=$log{m}^n$×O( $log{2}^m$
  )
  B+树的搜索过程中的IO次数 = 搜索过程中访问节点的数量 $\approx$ B+树的深度 = $log{m}^n$

B+树都不会超过4层，数据页中用户记录最多存放100条记录，目录页中目录记录最多存放1000条，如果B+树是4层，也就是100×1000×1000×1000=100000000000，既一千亿条数据。

B+树索引的分类

聚簇索引

所有完整的用户记录都存放在这个聚簇索引的叶子节点处，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。
它有两个特点：
1.使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

• 页内的记录是按照主键的大小顺序排成一个单向链表。

• 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。

• 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

2.B+树的叶子节点存储的是完整的用户记录。

所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

非聚簇索引（二级索引）

在非聚簇索引的叶子节点上存储的并不是真正的行数据，而是主键 +当前索引列。
按字段特性分类可分为：唯一索引、普通索引、前缀索引。

• 唯一索引
  建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突。

• 普通索引
  建立在普通字段上的索引被称为普通索引。

• 前缀索引
  前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引，而不是在整个字段上建索引。前缀索引可以建立在类型为char、varchar、binary、varbinary的列上，可以大大减少索引占用的存储空间，也能提升索引的查询效率。

按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）。

• 单列索引
  建立在单个列上的索引被称为单列索引。

• 联合索引（复合索引、组合索引）
  建立在多个列上的索引被称为联合索引，又叫复合索引、组合索引。
  回表
  在非聚簇索引中查找到的最终结果是——主键 +当前索引列，当前索引列可能无法包含select的数据列（select的数据列能直接从二级索引中取得，称为覆盖索引）还需拿着主键去聚簇索引中再进行查询。聚簇索引中才包含

B+树索引的使用

索引的代价

索引是个好东西，可不能乱建。一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。

• 空间上的代价
  每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，是很大的一片存储空间
• 时间上的代价
  每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。

B+树索引适用的情况

准备数据：

CREATE TABLE person_info(id INT NOT NULL auto_increment,name VARCHAR(100) NOT NULL,birthday DATE NOT NULL,phone_number CHAR(11) NOT NULL,country varchar(100) NOT NULL,PRIMARY KEY (id),KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);

该表拥有主键索引 key（id）和组合索引idx_name_birthday_phone_number (name, birthday, phone_number)

全值匹配

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239';

组合索引idx_name_birthday_phone_number (name, birthday, phone_number)，按照name，birthday, phone_number顺序排序，查询条件中三个字段都是等值比较。索条件中的列和索引列一致的话，这种情况为全值匹配。

匹配左边的列

SELECT * FROM person_info WHERE name = 'Ashburn';SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27';

搜索语句中也可以不用包含全部联合索引中的列，只包含左边的就行。

匹配列前缀

SELECT * FROM person_info WHERE name LIKE 'As%';

B+树中的数据页和记录通过该列的字符集和比较规则进行排序的，这些字符串的前n个字符，也就是前缀都是排好序的，所以对于字符串类型的索引列来说，我们只匹配它的前缀也是可以快速定位记录的。
匹配范围值

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';
-- 如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';

由于B+树中的数据页和记录是先按name列排序的,name列相同再按birthday列排序,birthday列相同再按照phone_number列排序。

精确匹配某一列并范围匹配另外一列

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday < '2000-12-31' AND phone_number > '15100000000';``
对于同一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找**用于排序**

SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;

因为这个B+树索引本身就是按照上述规则排好序的，所以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了。**用于分组**

SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number
``
和使用B+树索引进行排序是一个道理，分组列的顺序也需要和索引列的顺序一致，也可以只使用索引列中左边的列进行分组

在使用索引时需要注意下面这些事项：

• 只为用于搜索、排序或分组的列创建索引
• 为列的基数大(某列不重复的个数)的列创建索引
• 索引列的类型尽量小
• 可以只对字符串值的前缀建立索引
• 只有索引列在比较表达式中单独出现才可以适用索引
• 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性。
• 定位并删除表中的重复和冗余索引
• 尽量使用覆盖索引进行查询，避免回表带来的性能损耗。