MyISAM 引擎的 B+Tree 的索引
通过上图可以直接的看出, 在 MyISAM 对 B + 树的运用中明显的特点如下:
所有的非叶子节点中存储的全部是索引信息
在叶子节点中存储的 value 值其实是 数据库中某行数据的 index
MyISAM 引擎 索引文件的查看:
在 /var/lib/MySQL 目录中
.myd 即 my data , 数据库中表的数据文件
.myi 即 my index , 数据库中 索引文件
.log 即 MySQL 的日志文件
InnoDB 引擎 索引文件的查看:
同样在 /var/lib/MySQL 目录下面
InnoDB 引擎的 B+Tree 的索引
InnoDB 的实现方式业内也称其为聚簇索引, 什么是聚簇索引呢? 就是相邻的行的简直被存储到一起, 对比上面的两幅图片就会发现, 在 InnDB 中, B + 树的叶子节点中存储的是数据行中的一行行记录, 缺点: 因为索引文件被存放在硬盘上, 所以很占硬盘的空间
一般我们会在每一个表中添加一列 取名 id, 设置它为 primary key , 即将他设置成主键, 如果使用的存储引擎也是 InnoDB 的话, 底层就会建立起主键索引, 也是聚簇索引, 并且会自动按照 id 的大小为我们排好序,(因为它的一个有序的树)
点击查看 参考博文 1
局部性原理
局部性原理是指 CPU 访问存储器时, 无论是存取指令还是存取数据, 所访问的存储单元都趋于聚集在一个较小的连续区域中. 更进一步说, 当我们通过程序向操作系统发送指令让它读取我们指定的数据时, 操作系统会一次性读取一页 (CentOS 每页 4kb 大小, InnoDB 存储引擎中每一页 16kb) 的数据, 它遵循局部性理论, 猜测当用户需要使用某个数据时, 用户很可能会使用这个数据周围的数据, 故而进行一次
InnoDB 的页格式
什么是页呢? 简单说, 就是一条条数据被的存储在磁盘上, 使用数据时需要先将数据从磁盘上读取到内存中, InnoDB 每次读出数据时同样会遵循 局部性原理, 而不是一条条读取, 于是 InnoDB 将数据划分成一个一个的页, 以页作为和磁盘之间交互的基本单位
通过如下 sql, 可以看到, InnoDB 中每一页的大小是 16kb
show global status like 'Innodb_page_size';
名称 | 简述 |
---|---|
File Header | 文件头部, 存储页的一些通用信息 |
Page Header | 页面头部, 存储数据页专有的信息 |
Infinum + supremum | 最大记录和最小记录, 这是两个虚拟的行记录 |
User Records | 用户记录, 用来实际存储行记录中的内容 |
Free Space | 空闲空间, 页中尚位使用的空间 |
Page Directory | 页面目录, 存储页中某些记录的位置 |
File Tailer | 文件尾部 , 用来校验页是否完整 |
InnoDB 的行格式 compact
每一页中存储的行数据越多. 整体的性能就会越强
compact 的行格式如下图所示
可以看到在行格式中在存储真正的数据的前面会存储一些其他信息, 这些信息是为了描述这条记录而不得不添加的一些信息, 这些额外的信息就是上图中的前三行
变长字段的长度列表
在 MySQL 中 char 是固定长度的类型, 同时 MySQL 还支持诸如像 varchar 这样可变长度的类型, 不止 varchar , 想 varbinary text blob 这样的变长数据类型, 因为 变长的数据类型的列存储的数据的长度是不固定的, 所以说我们在存储真正的数据时, 也得将这些数据到底占用了多大的长度也给保存起来
NULL 标志位
compact 行格式会将值可以为 NULL 的列统一标记在 NULL 标志位中, 如果数据表中所有的字段都被标记上 not null , 那么就没有 NULL 值列表
记录头信息
记录头信息, 顾名思义就是用来描述记录头中的信息, 记录头信息由固定的 5 个字节组成, 一共 40 位, 不同位代表的意思也不同, 如下表
名称 | 单位 bit | 简介 |
---|---|---|
预留位 1 | 1 | 未使用 |
预留位 2 | 1 | 未使用 |
delete_mark | 1 | 标记改行记录是否被删除了 |
min_rec_mark | 1 | 标记在 B + 树中每层的非叶子节点中最小的 node |
n_owned | 4 | 表示当前记录拥有的记录数 |
heap_no | 13 | 表示当前记录在堆中的位置 |
record_type | 3 | 表示当前记录的类型 , 0 表示普通记录, 1 表示 B + 树中非叶子节点记录, 2 表示最小记录 ,3 表示最大记录 |
next_record | 16 | 表示下一条记录的相对位置 |
点击查看 参考博文 2 https://www.jianshu.com/p/e13e70b90a45
行溢出
在 MySQL 中每一行, 能存储的最大的字节数是 65535 个字节数, 此时我们使用下面的 sql 执行时就会出现行溢出现象
CREATE TABLE test ( c VARCHAR(65535) ) CHARSET=ascii ROW_FORMAT=Compact;
给 varchar 申请最大 65535 , 再加上 compact 行格式中还有前面三个非数据列占用内存, 所以一准溢出, 如果不想溢出, 可以适当的将 65535 - 3
页溢出
前面说了, InnoDB 中数据的读取按照页为单位, 每一页的大小是 16kb, 换算成字节就是 16384 个字节, 但是每行最多存储 65535 个字节啊, 也就是说一行数据可能需要好几个页来存储
怎么办呢?
compact 行格式会在存储真实数据的列中多存储一部分数据, 这部分数据中存储的就是下一页的地址
dynamic 行格式 中直接存储数据所在的地址, 换句话说就是数据都被存储在了其他页上
compressed 行格式会使用压缩算法对行格式进行压缩处理
一般我们都是将表中的 id 列设置为主键, 这就会形成主键索引, 于是我们需要注意了:
主键的占用的空间越小, 整体的检索效率就会越高
为什么这么说呢? 这就可以结合页的概念来解析, 在 B + 树这种数据结果中, 叶子节点中用来存储数据, 存储数据的格式类似 Key-value key 就是索引值, value 就是数据内容, 如果索引占用的空间太大的话, 单页 16kb 能存储的索引就越小, 这就导致数据被分散在更多的页上, 致使查询的效率降低
建立索引的技巧
为某一列建立索引
给 text 表中的 title 列创建索引, 索引名字 my_index
alter table text add index my_index (title);
虽然建立索引能提升查询的效率, 根据前人的经验看, 这并不是一定的, 建立索引本身会直接消耗内存空间, 同时索, 插入, 删除, 这种写操作就会打破 B + 树的平衡面临索引的重建, 一般出现如下两种情况时, 是不推荐建立索引的
表中的数据本身就很少
我们计算一下索引的选择性很低
兼顾 - 索引的选择性与前缀索引
所谓选择性, 其实就是说不重复出现的索引值(基数, Cardinality) 与 表中的记录数的比值
即: 选择性 = 基数 / 记录数
选择性的取值范围在 (0,1] 之间, 选择性越接近 1 , 说明建立索引的必要性就越强, 比如对 sex 列进行建立索引, 这里面非男即女, 如果对它建立索引的话, 其实是没意义的, 还不如直接进行全表扫描来的快
如何使用 sql 计算选择性呢? 严格遵循上面的公式
- SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
- count(基数 / 记录数)
- DISTINCT(title) //count(*)
更详细的例子看下面的连接
参考博文 https://www.jianshu.com/p/0f6df1b27245
索引失效问题
注意事项
索引无法存储 null 值
如果条件中有 or, 即使条件中存在索引也不会使用索引, 如果既想使用 or, 又想使用索引, 就给所有 or 条件控制的列加上索引
使用 like 查询时, 如果以 % 开头, 肯定是进行全表扫描
使用 like 查询时, 如果 % 在条件后面
对于主键索引, 索引失效
对于普通索引, 索引不失效
如果列的类型是字符串类型, 那么一定要在条件中将数据用引号引起来, 不然也会是索引失效
如果 MySQL 认为全表扫描比用索引块, 同样不会使用索引
联合索引
什么是联合索引
联合索引, 也叫复合索引, 说白了就是多个字段一起组合成一个索引
像下面这样使用 id + title 组合在一起构成一个联合索引
- CREATE TABLE `text` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `title` varchar(255) NOT NULL,
- `content` text NOT NULL,
- PRIMARY KEY (`id`,`title`)
- ) ENGINE=InnoDB AUTO_INCREMENT=3691 DEFAULT CHARSET=utf8
如果我们像上图那样创建了索引, 我们只要保证我们的 id+title 两者结合起来全局唯一就 ok
建立联合索引同样是需要进行排序的, 排序的规则就是按照联合索引所有列组成的字符串的之间的先后顺序进行排序, 如 a 比 b 优先
左前原则
使用联合索引进行查询时一定要遵循左前缀原则, 什么是左前缀原则呢? 就是说想让索引生效的话, 一定要添加上第一个索引, 只使用第二个索引进行查询的话会导致索引失效
比如上面创建的联合索引, 假如我们的查询条件是 where id = '1' 或者 where id = '1' and title = '唐诗宋词' 索引都会不失效
但是如果我们不使用第一个索引 id, 像这样 where title = '唐诗' , 结果就是导致索引失效
联合索引的分组 & 排序
还是使用这个例子:
- CREATE TABLE `text` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `title` varchar(255) NOT NULL,
- `content` text NOT NULL,
- PRIMARY KEY (`id`,`title`)
- ) ENGINE=InnoDB AUTO_INCREMENT=3691 DEFAULT CHARSET=utf8
demo1: 当我们像下面这样写 sql 时, 就会先按照 id 进行排序, 当 id 相同时, 再按照 title 进行排序
select * form text order by id, title;
demo2: 当我们像下面这样写 sql 时, 就会先将 id 相同的划分为一组, 再将 title 相同的划分为一组
select id,title form text group by id, title;
demo3: ASC 和 DESC 混用, 其实大家都知道底层使用 B + 树, 本身就是有序的, 要是不加限制的话, 默认就是 ASC, 反而是混着使用就使得索引失效
select * form text order by id ASC, title DESC;
如何定位慢查询
相关参数
名称 | 简介 |
---|---|
slow_query_log | 慢查询的开启状态 |
slow_query_log_file | 慢查询日志存储的位置 |
long_query_time | 查询超过多少秒才记录下来 |
常用 sql
- # 查看 MySQL 是否开启了慢查询
- show variables like 'slow_query_log';
- # 将全局变量设置为 ON
- set global slow_query_log ='on';
- # 查看慢查询日志存储的位置
- show variables like 'slow_query_log_file';
- # 查看规定的超过多少秒才被算作慢查询记录下来
- show variables like 'long_query_time';
- show variables like 'long_query%';
- # 超过一秒就记录 , 每次修改这个配置都重新建立一次链接
- set global long_query_time=1;
来源: https://www.cnblogs.com/ZhuChangwu/p/11908926.html