索引

高效获取数据的有序数据结构
提高检索效率，降低IO成本。
索引列对数据进行排序，降低排序成本，减少CPU的消耗
但是需要占用一些空间，提高查询效率有时候会降低更新表的速度，比如增删改。但是实际上增删改比例较少。

索引结构	描述	`InnoDB`	`MyISAM`	`Memory`
B+树索引	最常见的索引类型，大部分引擎都支持	支持	支持	支持
Hash索引	底层使用哈希表实现，只能精确匹配索引列的查询才有效，不支持范围查询	-	-	支持
R-tree(空间索引)	是`MyISAM`引擎的一个特殊索引类型，用于地理空间数据类型，使用较少	-	支持	-
Full-text(全文索引)	通过建立倒排索引，快速匹配文档的方式，类似于`Lucene, Solr, ES`	5.6版本后支持	支持	-

常见索引模型

哈希表
- 比如拉链法解决哈希冲突，追加新数据的速度很快，但是如果需要区间查找，就很慢。所以哈希表只适合等值查找
有序数组
- 可以解决范围查找问题，等值和范围查找都可以达到 $log(n)$ ，但是插入删除元素需要移动数据，只适合静态搜索
搜索树
- 二叉搜索树就是左子树<父节点<右子树，只要能维持平衡状态，就可以达到 $log(n)$ 的复杂度，但是如果不是平衡的，可能会出现单链表的情况。
- 二叉的效率最高，但是大多数都不会用二叉，因为索引也需要存储在磁盘，二叉树高度太高了，所以一般会使用B+树。索引越多读磁盘次数就越多
- MySQL一个节点都是一页，默认大小是16K，一个整数bigint大小为8B，每个整数都有6B的指向子树的指针，所以一个整数占用14B，所以一个节点可以存储 $16KB / 14B=1170$ ，所以m叉树就是1170叉，高为4的时候可以存储 $1170 * 1170 * 1170 - 1 = 16$ 亿左右的数据，由于部分数据还在内存中，所以顶多只需要访问三次磁盘即可

B+树

B+树的所有元素都在叶子节点处，非叶子节点只用于索引，存储都在叶子节点处，所有叶子节点形成了一个单向链表。
MySQL对B+树进行优化，在原来的B+树基础上，增加了一个指向相邻叶子节点的链表指针，形成了带顺序指针的B+树，提高区间访问性能。

`InnoDB`为何使用B+树索引，没有选择哈希索引、二叉树或者红黑树索引？

如果是二叉树，顺序插入最后会形成链表，搜索性能很低，红黑树可以解决
但是红黑树本质上还是二叉树，自平衡二叉树，某些场景下层级太多了
- 层级越多, 随机访问磁盘次数越多, 速度越慢
B+树相对于二叉树层级更少，搜索效率更高，这也是B树的优点
但是B树无论叶子还是非叶子节点，都会保存数据，最终导致一页中存储的键值减少，指针也减少。如果要保存大量的数据，只能增加树的高度，导致性能降低。
对于B+树，找什么都去叶子节点中，所以搜索效率稳定
MySQL中B+树的叶子节点形成了双向链表，便于范围搜索
哈希索引不支持排序和范围匹配，只能等值匹配。
InnoDB也有自适应哈希功能，有的条件下会自动将B+树索引转换为哈希索引。(这点在后面会介绍)

`MyISAM` VS `InnoDB`

二者都使用B+树实现索引
- MyISAM叶子节点的data域存放的是数据记录的地址，所以先用B+树找到索引，如果key存在，就通过对应的地址找到数据记录
- InnoDB数据文件本身就是索引文件，叶子节点data域保存了完整的数据记录，辅助索引data域存储主键的值，而不是地址，所以辅助索引都使用主键作为data域
InnoDB中，一张表就是多个B+树(一个主键索引树和多个非主键索引树)，主键索引是一级索引，也是聚簇索引；非主键索引是二级索引，非主键索引只存储了主键值，所以需要在主键索引中再查找一次才能找到数据，所以使用非主键索引就需要回表

索引分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	`PRIMARY`
唯一索引	避免同表某数据列中的值重复	可以多个	`UNIQUE`
常规索引	快速定位特定数据	可以多个
全文索引	查找文本中关键词，而不是比较索引值	可以多个	`FULLTEXT`

按存储形式分类

分类	含义	特点
聚集索引	数据和索引放在一块，索引结构的叶子节点保存了行数据	必须有且只有一个
二级索引	将数据和索引分开存储，索引结构的叶子节点关联的是对应的主键	可以多个

如果存在主键，则主键索引就是聚集索引
如果没有主键，则第一个唯一索引就是聚集索引
没有主键索引和唯一索引的话，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

聚集索引下面挂的是一整行的数据，二级索引下面挂的是主键
回表查询: 先走二级索引找到对应的主键，然后再从聚集索引找到需要的数据

select * from user where id=10;
select * from user where name="A";
-- 其中 id 为主键，name也有对应的索引
-- 前者性能更高，因为不需要回表查询了，可以直接通过聚集索引找到数据

`InnoDB`主键索引中B+树高度为多少？

假设一行数据为1K，一页中存储16行(因为一页大小固定为16K)，InnoDB指针占用6B，主键即使为BIGINT，占用8B
如果树高为2，假设有 $n$ 个键，就有 $n + 1$ 个指针， $n * 8 + (n + 1) * 6 = 16 * 1024$ 计算得 $n \approx 1170$
所以一个节点下面最多1171个指针，一个指针指向下面的子节点，一个子节点表示一个页，最多可以存16行数据。所以如果B+树高2，能够存储数据量为 $1171 * 16 = 18736$
如果树高为3，则 $1171 * 1171 * 16 = 21939856$ ，所以即使存储两千多万条数据，树结构也只有三层

索引增删查

创建索引

create [unique | fulltext] index 索引名 on 表名 (索引关联字段1, 索引关联字段2, ...);

如果一个索引只关联了一个字段，则称为单列索引
如果关联了多个字段，则称为联合索引

删除索引

drop index 索引名 on 表名;

查看索引

show index from 表名;

性能分析

执行频率

show [session | global] status;查看服务器状态信息
show global status like 'Com_______';查看当前数据库的insert, update, delete, select的访问频次 (Com后面是7个下划线)

慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time单位: 秒，默认为10秒)的所有语句的日志
show variables like 'slow_query_log';查看慢查询日志是否开启，默认是关闭状态
- 需要在/etc/my.cnf中配置慢查询日志开关，表示超过200ms就是慢查询了
  1
  2
  slow_query_log=1
  long_query_time=0.2
慢查询日志存放在var/log/mysql/mysql_slow.log

语句耗时

show profiles;可以查看SQL语句耗时，select @@have_profiling;可以查看当前MySQL是否支持
确认支持后，还需要查看是否开启了select @@profiling;使用set @@profiling=1开启
开启后，执行完命令可以使用show profiles;查看一条指令的耗时情况
show profile for query query_id可以查看一条query_id的查询指令的耗时细节
show profile cpu for query query_id可以查看指定指令的CPU耗时细节

执行计划

在任意select之前加上explain或者desc就可以查看执行计划
- id: select查询的序列号，表示执行顺序，id相同，从上到下执行；id不同，越大越早执行
- select_type: 表示select类型，常见有:
  - simple: 简单单表查询
  - primary: 最外层的主查询
  - union: 联合查询中的第二个查询
  - subquery: select/where后的子查询
- type: 表示连接类型，性能由好到差分别为null, system, const, eq_ref, ref, range, index, all
  - null: 查询时不访问任何表，比如select 'A';
  - system: 访问系统表
  - const: 根据主键、唯一索引访问
  - ref: 根据非唯一索引访问
  - range: 非唯一索引，但是范围匹配，比如age > 18
  - index: 虽然使用了索引，但是遍历整个索引树扫描，性能也低
  - all: 全表扫描，性能最低
- extra:
  - using index condition: 查找使用了索引，但是需要回表查询
  - using where; using index: 查找使用了索引，但是需要的数据在索引列中都能找到，不需要回表
- possible_key: 表示这表中可能用到的索引，一个或多个
- key: 表示实际用到的索引，null表示没有用到索引
- key_len: 表示索引中使用的字节数，是索引字段的最大长度，不是实际使用的长度，不损失精度的前提下越短越好
- rows: 表示查询的行数，只是预估值，不是精确值
- filtered: 表示查询返回行数占总读取行数的百分比，越大越好

最左前缀法则

如果有联合索引，查询从索引的最左列开始，不跳过中间的列。如果跳过某一列，则所有后面的字段都失效
如果没有索引最左边的列，则索引失效
只需要where条件有最左边的列即可，只要存在就行，跟在where中的位置无关
联合索引中出现了范围查询>, <，则右边的索引失效
- select * from user where profession='A' and age > 30 and status='0';则stauts失效
- 如果是>=, <= 则不会产生影响，所以尽量少用>, <
- MySQL会一直向右匹配，直到范围查询，所以比如where第一个条件就是范围查找，那么就只会用到最左边的索引，5.6版本提出索引下推(ICP)，在只能够使用最左边的索引，而无法使用全部联合索引的情况下，在遍历索引的时候，就对where条件将进行过滤，这样就可以减少遍历查询得到的主键条数，从而减少回表次数。
  - 因为在5.6版本之前，如果只能使用最左边的索引，那么就会一个个回表查询所有信息再对where条件过滤，回表次数增加。

索引失效

如果在索引列上进行运算，则索引失效
- select * from user where phone='12312312312'; 走单列索引，type=const
- select * from user where substring(phone, 10, 2)='15'; 走全表扫描，type=all
- 因为B+树的快速定位能力，实际上是通过同一层兄弟节点的有序性得到的，函数计算，索引选择都是server层的，跟InnoDB引擎无关，依然会使用索引树，但是函数操作会破坏索引的有序性，所以优化器就放弃走索引搜素的功能，不得不在这个索引树上遍历全树扫描
- 即便不改变有序性，也不会走索引快速搜索
- 所以MySQL如果是字符串和数字比较，就会将字符串转为数字
  - 如果id类型是varchar，where id = 11111就不会使用索引，会导致全表扫描，因为涉及字符串的强制类型转换，也就是函数操作
  - 如果id类型是int，where id = "11111"，这个会把"11111"转换为数字，不会对表上的字段进行类型转换，因此会使用索引
  - 如果两个表关联查询，两个表字符集不同，比如一个是utf8，另一个是utf8mb4，第二个表就会没办法使用索引，因为utf8mb4是utf8的超集，所以会将utf8转换为utf8mb4，同样会使用函数操作，导致全表扫描
  只需要保证参数值小于等于索引，就不会走全表，因为会将参数进行类型转换，而不会将索引类型转换，就没问题
字符串不加引号索引就会失效
- select * from user where phone=12312312312; 走的是全表扫描，因为没有添加引号
仅仅使用尾部模糊匹配，索引不会失效；只要有头部模糊匹配，索引会失效
- select * from user where profession like '软件%'; 走索引，type=range
- select * from user where profession like '%软件'; 不走索引，type=all
- 大数据量一定要规避模糊匹配前面加上%的情况
使用or分割的条件，只有两侧都有索引，索引才会生效，不然不会生效(实际上经过测试发现必须要有两边的联合索引，or语句的索引才会生效)
如果MySQL评估使用索引比全表扫描还要慢，就不会用索引
- 比如范围匹配或者判断空或非空，满足要求的条件是表中的绝大部分数据，则不会使用索引，直接走全表扫描了

`SQL`提示

一个查询能满足多个索引的时候，MySQL会自动选择一个索引
可以给MySQL提示，要求选择哪个索引进行查找，从而达到优化的目的
use / ignore / force index; 告诉MySQL使用哪个索引，不使用哪个索引
use index是一个建议，MySQL未必接受；force index是强制MySQL使用这个索引

如果`MySQL`取错索引，应该如何操作？

使用use / ignore / force index; 让MySQL使用某个索引
修改SQL语句，诱导MySQL选择某个索引
建立合适的索引，删除无效的索引

优化器选择索引的依据

扫描行数

估算，跟索引的基数有关，基数是一个索引下，不同值的个数，基数越大，索引区分度越高，选择正确索引的概率越大。
- 基数通过采样估计，选择N个页，计算平均基数，然后乘页数
- 索引值变更超过1/M的时候，就会触发重新计算索引统计基数
- 配置参数innodb_stats_persistent = ON，统计信息会持久化存储，N=20，M=10
- innodb_stats_persistent = OFF，统计信息只在内存中，N=8，M=16
- 如果预估的扫描行数不符和实际，可以使用analyze table tablename来重新评估行数

覆盖索引

尽量使用覆盖索引，而不用select *
如果二级索引查询的结果仅仅是主键，那就不需要回表查主键索引，就称为覆盖索引
二级索引会默认和主键做联合索引

如果有根据身份证号查找人名的应用，就可以对身份证号和人名做联合索引，这样的话，人名信息也会放在身份证号的索引上，就不需要回表了。
根据最左前缀法则，有身份证号-人名索引了，就不需要再去单独建立身份证号的索引了。如果这样的联合索引可以少维护一个索引的话，那这样的联合索引顺序就是优先考虑的。
第二考虑的就是两个字段的占用字节数，比如用户名和年龄，那年龄占用字节数较少，所以可以构建索引用户名-年龄，然后年龄单独再做一个索引，也就长索引在前，短索引构建两遍。

前缀索引

如果需要索引很长的字符串，可以使用前缀索引，或者使用ES实现
create index idx_xxx on 表名(字段名(n)); 建立前缀索引，n表示要提取这个字段中的前n个字符构建索引
n是前缀长度，可以根据索引选择性决定，选择性是不重复的索引值(基数)与数据表的记录总数的比值。
- 选择性越高，查询效率越高
- 唯一索引的选择性就是1，是最高的索引选择性，性能也是最好的
  1
  2
  select count(distinct email) / count(*) from user;
  select count(distinct substring(email, 1, 5)) / count(*) from user;
前缀索引不支持覆盖索引，所以一定会回表

存储身份证号，可以倒序存储，取前缀索引。

或者使用crc32()或者别的hash函数在插入身份证号的时候存储校验码字段，然后对校验码添加索引
倒序和hash都无法支持范围查找
倒序存储不需要额外字段空间，hash存储需要额外校验码
倒序存储需要reverse()，hash需要crc32()，相比之下，hash消耗的cpu稍大
hash稳定一些，crc32()冲突概率很低

单列索引 VS 联合索引

如果where有两个部分，分别都只有单列索引，这时候第一个条件会走单列索引，第二个条件不会走索引
select id, phone, name from user where phone='12312312312' and name='123'; 此时只有phone走单列索引，name没有走索引，导致最终回表
如果这时候有两个部分的单列索引，以及两个部分的联合索引，简单的使用where，依然是只有第一部分使用单列索引。因此需要指定使用联合索引才行
如果涉及多个查询条件，推荐使用联合索引

索引设计原则

数据量较大(> 100万)，查询频繁的表需要涉及索引。如果只插入，不查询，就不用建立索引
针对常常作为查询条件where，排序order by，分组group by这几个字段建立索引
建立索引使用区分度高的列作为索引，尽量建立唯一索引。如果是性别，状态这种字段没有必要建立索引
字符串较长考虑使用前缀索引
尽量使用联合索引，减少单列索引，以达到使用覆盖索引，避免回表
控制索引的数量，索引越多，增删改的效率越低
索引列不能存储NULL，建表的时候使用NOT NULL约束，可以更有效的使用索引

例子:

索引命名规范: idx_表名_字段名
创建联合索引对字段顺序有要求

name是姓名字段，可能重复: create index idx_user_name on user(name); 创建常规索引
phone是手机号，非空，唯一，创建索引: create unique index idx_user_phone on user(phone); 创建唯一索引
为profession, age, status创建索引: create index idx_user_pro_age_status on user(profession, age, status); 创建联合索引
为email创建合适的索引: create index idx_user_email on user(email); 创建常规索引
一张表四个字段(id, username, password, status)如何优化select id, username, password from user where username='a';:
- 建立username, password联合索引，并且username放在第一个，从而满足最左前缀法则，可以避免回表

唯一索引 VS 普通索引

二者查询性能差距微乎其微，因为MySQL都是一页一页在内存中读取的，唯一索引无非就是只要找到就能返回，而普通索引找到一个以后还需要找下一个是否满足,直到找到下一个不满足的才能返回
普通索引比唯一索引写性能更好，因为普通索引可以使用change buffer，但是唯一索引用不了
- 唯一索引插入数据之前要先判断是否唯一，此时需要将数据读入内存才能判断，因此唯一索引插入的时候不需要再使用change buffer了
- change buffer用的时buffer pool中的内存，不能无限增大，可以配置innodb_change_buffer_max_size=50表明最多占用50%的buffer pool

如果目标页在内存中(插入4)
- 唯一索引：找到3和5中间，判断没有冲突，插入4
- 普通索引：找到3和5中间，插入4
- 性能差异微乎其微，只有一个判断
如果目标页不在内存中(插入4)
- 唯一索引：将目标页读入内存，判断没有冲突，插入4
- 普通索引：更新记录在change buffer中
- 性能差异明显，因为减少随机访问磁盘

总结

对于change buffer，只有在merge的时候，才是真正写入数据的时候，所以如果写多读少，页面写完以后马上被访问的概率较小，change buffer的效果最好，比如账单，日志
如果写完立马进行查询，就会出现change buffer反复merge的情况，效果更差，所以如果是写了以后立马查询的话，可以使用普通索引，同时关闭change buffer，也就是innodb_change_buffer_max_size设置为0，同理对于账单日志，可以将change buffer尽量开大一些

数据更新过程

如果更新一个数据页，并且数据页在内存中，就直接更新。如果不在内存中，就将更新写入change buffer中，下次查询需要访问对应的数据页的时候，将数据页读入内存，然后执行change buffer中与这个数据页有关操作。
change buffer中的数据更新到数据页上称为merge，查询会执行merge，正常关闭数据库会执行merge，后台线程也会定期merge

redo log减少的是随机写入磁盘的消耗，就是变成了顺序写；
change buffer是减少了随机读磁盘的消耗，等到后面读取了磁盘以后，再讲内存写入

MySQL笔记_2

索引

常见索引模型

B+树

InnoDB为何使用B+树索引，没有选择哈希索引、二叉树或者红黑树索引？

MyISAM VS InnoDB

索引分类

按存储形式分类

InnoDB主键索引中B+树高度为多少？

索引增删查

创建索引

删除索引

查看索引

性能分析

执行频率

慢查询日志

语句耗时

执行计划

最左前缀法则

索引失效

SQL提示

如果MySQL取错索引，应该如何操作？

优化器选择索引的依据

覆盖索引

前缀索引

单列索引 VS 联合索引

索引设计原则

例子:

唯一索引 VS 普通索引

总结

数据更新过程

`InnoDB`为何使用B+树索引，没有选择哈希索引、二叉树或者红黑树索引？

`MyISAM` VS `InnoDB`

`InnoDB`主键索引中B+树高度为多少？

`SQL`提示

如果`MySQL`取错索引，应该如何操作？