Mysql简介，B+树索引结构，数据页结构，索引生效和失效

服务器处理客户端请求

1. 处理连接
2. 查询缓存
3. 语法解析
4. 查询优化
5. 存储引擎
6. 文件系统
   

连接管理

客户端请求->服务器创建一个进程处理交互
客户端断开，服务器缓存线程给新的客户端。
使用SSL网络连接进行通信。

查询缓存

将刚处理后的请求和结果缓存起来，如果有同样的请求就返回相同数据
可以在不同的客户端之间共享。

缓存命中

请求完全匹配字符
如果请求包括系统函数、用户自定义变量、函数、系统表就不会命中缓存。

缓存失效

缓存系统监控每一张表
如果结构或者数据被修改就会判断为缓存失效。

语法解析

判断SQL语法是否正确
然后提取文本信息。

存储引擎

真实存放数据的结构，提供上层api供mysql执行并获取数据

存储引擎探秘

查看mysql服务器支持的存储引擎

SHOW ENGINES;

是否支持事务，分布式事务，部分事务回滚

创建表指定存储引擎

CREATE TABLE 表名(
    建表语句;
) ENGINE = 存储引擎名称;
例如
CREATE TABLE engine_demo_table(
    i int
)ENGINE = MyIsAm;

运行时修改结构

ALTER  TABLE engine_demo_table ENGINE = InnoDB; 

mysql支持的字符集

utf-8 和uft8mb4
utf-8使用1-3个字节表示字符
mb4没有被阉割。

InnoDB

简介

将数据划分成若干个页
16kb一页

行格式

1. Compact
2. Redundant
3. Dynamic
4. Compressed

Compact行格式

记录的额外信息+记录的真实数据

变长字段长度列表

真正的数据内容+占用字节数

各变长字段数据占用的字节数按照列的顺序逆序存放。
比如列一 长度04 03 01
则开头为01 03 04

NULL值列表 统一管理

每个允许存储null值的列对应一个二进制位，二进制位逆序排序
值为1时 列值为NUll
值为0时，值不为null
二进制位不够整数时高位补0

记录头信息

5个字节，40个二进制位

记录的真实数据

主键生成策略

1. 先使用用户自定义主键作为主键
2. 选取一个Unique键作为主键
3. 添加一个row_id 隐藏列作为主键

数据页结构

插入一条记录的时候，从Free Space中申请记录大小空间划分到User Records中， Free Space 划分完后，代表页使用完毕，新的纪录插入需要去请求新的页。

B+树索引

没有索引的查找

SELECT 列名 FROM 表名 WHERE 列名 =xxx;

1. 主键搜索条件： 二分法定位到槽，然后遍历槽中的记录
2. 其他列：从最小记录依次搜索。
3. 如果数据在很多页中，则需要从最小记录查找。

引入索引

如果数据量很大，则需要建立索引来操作

CREATE TABLE index_demo
(
    c1 INT,
    c2 INT,
    c3 CHAR(1),
    PRIMARY KEY (c1)
)
    ROW_FORMAT = Compact;

InnoDB的索引方案

记录头的record_type =1 是 为目录项纪录
目录项纪录只有主键值和页的编号两个列

例： 查找主键为20的纪录

1. 先到存储目录项纪录的页，定位对应目录项， 找到对应的页码
2. 然后在对应页码继续二分，在槽中找对应的纪录
3. 存储目录项页多了 也可以用高级目录管理。

键为最小值，值为页码

主键索引用B+树管理
用户纪录存在叶子结点，目录项存在非叶子结点

聚簇索引

通过纪录主键值的大小来排序页，二分进行查找。
叶子结点存储完整的用户纪录

二级索引

通过别的列建一颗b+树
然后用别的列+主键值存放在叶子结点
目录项用c2列+页号来存放
找到主键值再走聚簇索引（回表）获取所有数据

联合索引

给多个列上索引
先按一列排序，相同再按另外的列排序
叶子结点由两列值+主键组成。

注意事项

1. 创建B+树索引时，为索引创建根节点页面
2. 插入用户纪录，插入到根节点
3. 根节点用完后，复制所有数据到新的页，页分裂后，根节点升级为存储目录项纪录的页。
4. 二级索引除了索引列+页号，还包括了主键值（最小那一个）。
   

索引的操作

1. 创建

CREATE TALBE 表名 (
    各种列的信息 ··· , 
    [KEY|INDEX] 索引名 (需要被索引的单个列或多个列)
)

2. 修改

ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);

3. 删除

ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);

CREATE TABLE index_demo(
    c1 INT,
    c2 INT,
    c3 CHAR(1),
    PRIMARY KEY(c1),
    INDEX idx_c2_c3 (c2, c3)
);

索引的代价

1. 空间代价
2. 时间代价（修改数据）要修改索引

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)
);

1. 全值匹配

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

先找name列，再找birthday列，再找phone number列
2. 左侧匹配
只有name，或者name，birthday列也会走索引。
3. 前缀匹配
字符串前缀也会走索引

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

#索引失效
SELECT * FROM person_info WHERE name LIKE '%As%';

存储的时候可以逆序存储来达成前缀匹配。
4. 匹配范围值

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';
#只有左边列会走索引 然后不断过滤

先找name大于asa的纪录，然后找第二条二级纪录判断纪录符合。

5. 精确匹配某一列并范围匹配另外一列
   先精确查找，走索引，然后范围查找，继续走索引
   但后面的列不能再走索引

6. 排序
   联合索引的排序顺序必须按索引列顺序给出
   左边匹配索引
   失效情况：asc，desc乱用
   排序列包含不是同一个索引
   复杂的表达式

7. 分组
   分组列顺序按索引列顺序排列

回表的代价

随机io的代价

覆盖索引

最好在查询列表只包含索引列。
不是用*

建立索引的原则

1. 用于搜索，排序，分组的列创立索引
2. 列的基数越大越要建立索引
3. 索引列的类型尽量小，占用空间较小的
4. 只对字符串值的前缀建立索引（这样就不能用索引排序）
5. 让索引列在比较表达式中单独出现
6. 最好让插入纪录的主键值依次递增。