MySQL索引

MYISAM存储引擎
非聚集索引（主键/唯一键/RowID[6字节]）

image.png

Innodb存储引擎
聚集索引（主键/唯一键/RowID[6字节]）

image.png

MYISAM 与Innodb 存储引擎的差异

image.png

通过辅助索引进行检索，需要检索两次索引。
MyISAM存储引擎，一旦数据发生迁移，则需要去重新组织维护所有的索引。

回表：参考 https://www.jianshu.com/p/8991cbca3854
索引覆盖
索引下推：参考 https://www.cnblogs.com/Chenjiabing/p/12600926.html
MRR:muti_range read : 参考 https://blog.csdn.net/zhang123456456/article/details/82917018

FIC: fast index creation ：只对普通索引有效，聚集索引无效
无FIC场景或聚集索引处理流程:
- 创建一个新的、空的临时表，表结构为使用alter table定义的新结构
- 逐一拷贝数据到新表，插入数据行同时更新索引
- 删除原表
- 将新表的名字改为原表的名字
FIC：
- 创建辅助索引时加S锁，只能读不能写。
- 删除辅助索引时，只需更新内部视图→把辅助索引的空间标记为可用→删除内部视图上对该表的索引定义

参考：https://zhuanlan.zhihu.com/p/105215928

索引

创建test_user表，索引idx_test_user_name_age_account(name, age,account)

CREATE TABLE `test_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account` char(30) NOT NULL,
  `name` varchar(100) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `position` varchar(45) NULL,
  PRIMARY KEY (`id`),
  KEY `idx_test_user_name_age_account` (`name`,`age`,`account`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

着重讲一下key_len 怎么计算出来的

三个字段的长度分别为 name:100 , age: 4, account: 30
varchar 为可变长度每个字段需要加 2位
可以为Null 需要加 1位
字符集使用的是 UTF-8, 所以所有的字符类型要乘以 3
key_len = (100 + 30 ) * 3 + 4 + 2(name字段位varchar) + 1 （age 可Null） = 397
Type 类型
null 不需要访问表或者索引
const/system 单条记录，系统会把匹配行中的其他列作为常数处理
eq_ref 使用唯一索引扫描
ref 使用非唯一索引扫描或者唯一索引前缀扫描
range 索引范围扫描
index 索引扫描
ALL 全表扫描

全值匹配

explain select * from test_user where name='xxx' and age = 15 and account= 'xxx'

image.png

匹配最左前缀

explain select * from test_user where name='xxx' and age = 15

image.png

匹配列前缀

explain select * from test_user where name like 'x%'

image.png

匹配范围

explain select * from test_user where name = 'wang' and age > 15 and account= 'xxx'
条件 age > 15 后的 account 索引失效

image.png

访问索引的查询

explain select name,age,account from test_user where name='xxx' and age = 15 and account= 'xxx'

image.png

无法命中索引

explain select * from test_user where name = 'xxx' or age = 12

image.png

Extra 的相关说明可以参考 https://blog.csdn.net/poxiaonie/article/details/77757471
Type 的相关说明可以参考 https://blog.csdn.net/lilongsy/article/details/95184594

查询优化

当使用索引列进行查询时，不要使用表达式
尽量使用主键查询，查询不用回表
使用前缀索引
create index test_user on test_user(name(7));
使用索引进行排序

  explain select * from test_user where name = 'xxx' order by age 
  Extra : Using index condition; Using where

   explain select * from test_user where name = 'xxx' order by account
   Extra : Using index condition; Using where; Using filesort

union all, in , or 都能使用索引(primary key)，推荐使用in
强制类型转换会触发全表扫描
创建索引的列，不允许为Null，
表连接的的时候最好不好超过3张表，连接字段类型要一致
能使用limit，尽量使用limit
单表索引建议控制在5个以内
联合索引的字段数不要超过5个

CRC全称为Cyclic Redundancy Check，又叫循环冗余校验
参考：https://www.jianshu.com/p/af6cc7b72dac

MySQL 基础算法：HyperLogLog算法经常在数据库中被用来统计某一字段的Distinct Value（下文简称DV），比如Redis的HyperLogLog结构
参考： https://www.jianshu.com/p/55defda6dcd2

参考：
https://blog.csdn.net/b_x_p/article/details/86434387