2_mysql（索引、存储引擎）

最新推荐文章于 2024-05-09 00:12:29 发布

风满楼i

最新推荐文章于 2024-05-09 00:12:29 发布

阅读量139

点赞数

分类专栏： MYSQL 文章标签： mysql

本文链接：https://blog.csdn.net/m0_45204457/article/details/109458950

版权

MYSQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

课程大纲：https://www.jianshu.com/p/edd2e20aeaee

一、索引及执行计划

1、在功能上的分类
① 聚簇索引：建表时，指定了主键列，Mysql Innodb 会将主键作为聚簇索引列；如果没有主键，会选择唯一键作为聚集索引。
② 辅助索引
(1) 单列辅助索引
(2) 联合索引：多个列作为索引条件，生成索引树，理论上设计的好的，可以减少大量的回表查询
注：联合索引注意最左原则，inx(a,b,c)：查询条件中，必须包含最左例。
(3) 前缀索引：使用字段的前部分数据作为建立索引的关键字
③ 唯一索引：索引列的值都是唯一的。
PRI: 主键索引
UNI: 唯一索引
MUL: 辅助索引(单列,联和,前缀)
2、B+tree 索引树高度影响因素
索引列值过长 , 解决方法：前缀索引
数据量级，解决方法：分表、分库、分布式
数据类型：比如，变长长度字符串，使用了char，解决方案：变长字符串使用varchar
3、创建索引

alter table city add index idx_name(name);
联合索引：
alter table city add index idx_co_po(countrycode,population);
前缀索引： 
alter table city add index idx_di(district(5));
唯一索引：  
alter table city add unique index idx_uni1(name);

4、执行计划获取及分析
① 查看执行计划：explain + 语句
② 执行计划显示结果的分析
table：查询操作的表，可精确到问题表
type
possible_keys：可能会走的索引
key：真正走的索引
key_len：联合索引覆盖长度
rows
Extra
(1) type ：索引类型
all：全表扫描，不用任何索引
index：全索引扫描 —> 把整个索引树扫描一遍
range：索引范围扫描
—> 辅助索引> < >= <= LIKE IN OR
—> 特殊情况：主键 <> NOT IN
ref：非唯一性索引，辅助索引等值查询
eq_ref：针对多表连接中，非驱动表连接条件是主键或唯一键
const( system )：聚簇索引等值查询
(2) extra
filesort ,文件排序
—> 当我们看到执行计划extra位置出现filesort,说明由文件排序出现
—> 观察需要排序(ORDER BY,GROUP BY ,DISTINCT )的条件,有没有索引
—> 根据子句的执行顺序,去创建联合索引
5、索引应用规范
5.1 建表时一定要有主键,一般是个无关列
5.2 选择唯一性索引
5.3 为经常需要where 、ORDER BY、GROUP BY,join on等操作的字段，建立索引，优化查询
5.4 尽量使用前缀来索引
5.5 限制索引的数目
5.6 删除不再使用或者很少使用的索引
5.7 大表加索引,要在业务不繁忙期间操作
5.8 尽量少在经常更新值的列上建索引
5.9 建索引原则
(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期
6、不走索引的情况（开发规范）
6.1 没有查询条件，或者查询条件没有建立索引
6.2 查询结果集是原表中的大部分数据，应该是25％以上。
6.3 索引本身失效，统计数据不真实
索引有自我维护的能力。对于表内容变化比较频繁的情况下，有可能会出现索引失效，一般是删除重建。
6.4 查询条件使用函数在索引列上，或者对索引列进行运算
6.5 隐式转换导致索引失效
6.6 <> ，not in 不走索引（辅助索引）
单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或in 尽量改成union
6.7 like “%_” 百分号在最前面不走
7、优化器对索引的算法
7.1 MySQL索引的自优化-AHI
MySQL的InnoDB引擎，能够创建只有Btree。AHI作用：自动评估"热"的内存索引page,生成HASH索引表。帮助InnoDB快速读取索引页。加快索引读取的所读。相当与索引的索引。
7.2 MySQL索引的自优化-Change buffer
比如insert，update，delete 数据。对于聚簇索引会立即更新。对于辅助索引，不是实时更新的。
在InnoDB 内存结构中，加入了insert buffer（会话），现在版本叫change buffer。Change buffer 功能是临时缓冲辅助索引需要的数据更新。当我们需要查询新insert 的数据，会在内存中进行merge(合并)操作，此时辅助索引就是最新的。
7.3 ICP 索引下推
作用：解决了联合索引只能部分应用情况。为了使减少没必要的数据页被扫描。将不走索引的条件，在engine层取数据之前先做c二次过滤。一些无关数据就会被提前过滤掉。
例子：index(a,b,c)select * from t1 where a= and c=
7.4 MRR muti range read
mysql> set global optimizer_switch=‘mrr=on,mrr_cost_based=off’;
辅助索引 —回表----》聚簇索引转换为辅助索引 —>sort id --回表–> 聚簇索引
7.5 SNLJ
7.6 BNLJ
在 A和B关联条件匹配时，不再一次一次进行循环。而是采用一次性将驱动表的关联值和非驱动表匹配.一次性返回结果主要优化了， CPU消耗，减少了IO次数。
7.7 BKA
主要作用，使用来优化非驱动表的关联列有辅助索引。BNL+ MRR的功能。

二、存储引擎

1、存储引擎种类
InnoDB、MyISAM 等
TokuDB 、RocksDB、MyRocks，三种存储引擎的共同点:压缩比较高,数据插入性能极高。现在很多的NewSQL,使用比较多的功能特性.
2、InnoDB核心特性
在这里插入图片描述
问题一：请你列举MySQL InnoDB存储优点？
问题二：请你列举 InooDB和MyIsam的区别？
Clustered index
Change buffer
自适应hash索引：AHI
MVCC：多版本并发控制
多缓冲区池
事务
行级锁粒度；MyISAMs是表锁级
外键
更多复制特性
支持热备份
自动故障恢复
alter table x engine = innodb; 进行innodb表的碎片化整理
3、MySql的存储引擎体系结构
3.1 宏观机构
① MyISAM
myt.frm：存放数据字典信息（列的定义和属性）
myt.MYD：存放数据行信息
myt.MYI：存放索引信息
② InnoDB
myt.frm：存放数据字典信息（列的定义和属性）
myt.ibd：独立表空间文件，存放数据行和索引信息
ibdata1：共享表空间文件，存放数字字典信息，undo logs、double write、change buffer磁盘区域
③ 说明：不同版本ibdata1中存储的数据不一样
5.5：ibdata1 中还会存储临时表数据 + 用户数据(数据行+索引)
5.6：ibdata1 中还会存储临时表数据
8.0：ibdata1 取消存储数据字典信息，将 undo logs 独立出去
ibdata1 在慢慢瘦身，将比较关键的数据独立出来了
ib_logfile0 ~ ib_logfile1：InnoDB 事务重做日志（redo logs）
ibtmp1：临时表空间文件（排序、分组、多表连接、子查询、逻辑备份等）
ib_buffer_pool：正常关库时，存储缓冲区的热数据
结论：仅仅拷贝ibd、frm文件到新的数据库是无法正常使用的
在这里插入图片描述
3.2 InnoDB微观结构
① 磁盘

表空间
(1) 什么是表空间？
表空间的概念是引入与Oracle数据库
起初是为了解决存储空间扩展的问题，MySQL5.5引入了共享表空间模式

(2) MySQL表空间类型
	共享表空间：在5.5版本引入了共享表空间(ibdata1)作为默认存储方式，用于存储系统数据、日志、undo、临时表、用户数据和索引
	独立表空间：5.6版本默认独立表空间模式，单表单表空间
	普通表空间：完全和Oracle一致的表空间管理模式，更加自定义化
	undo表空间：存储undo logs(回滚日志)
	临时表空间：存储临时表，5.7默认独立

(3) 表空间管理
	用户数据默认的存储方式：独立表空间模式(5.6后)，独立表空间和共享表空间是可以相互切换的
	-- 查看默认表空间模式
	-- 1代表独立表空间模式；0代表共享表空间模式
	select @@innodb_file_per_table;
	
	-- 切换表空间模式
	-- 说明：修改完成后只影响新创建的表
	set global innodb_file_per_table=0;
	
	-- 查看共享表空间大小和个数
	mysql> select @@innodb_data_file_path;
	+-------------------------+
	| @@innodb_data_file_path |
	+-------------------------+
	| ibdata1:12M:autoextend  |
	+-------------------------+
	1 row in set (0.00 sec)
	
	-- 扩展共享表空间大小和个数
	-- 说明：通常是在初始化数据时就设定号参数
	-- 方法1：初始化之前，在my.cnf加入以下配置
	innodb_data_file_path=idata1:1G;idata2:1G:autoextend
	-- 方法2：在已运行中的数据库上扩展多个ibdata文件
	-- 在设置innodb_data_file_path参数时，已有的ibdata1大小应该和磁盘上真实大小一致，再进行添加
	innodb_data_file_path=idata1:xx;idata2:1G:idata3:1G:autoextend

事务日志：
redo logs：重做日志
(1) 文件位置
	/data/3306/ib_logfile0~ib_logfileN
(2)	控制参数
	-- 设置文件大小
	innodb_log_file_size=50331648
	-- 设置文件个数
	innodb_log_files_in_group=2
	-- 设置存储位置
	innodb_log_group_home_dir=./
(3) 功能：
	用来存储MySQL在做修改类(DML)操作时数据页变化过程及版本号(LSN),属于物理日志。默认两个文件存储redo，是循环覆盖使用的

undo logs：回滚日志
(1) 文件位置
	/data/3306/ibdata1
	/data/3306/ibtmp1
(2) 控制参数
	-- 回滚段的个数
	innodb_rollback_segments=128
(3) 功能:
	用来存储回滚日志，相当于快照，提供InnoDB多版本并发读写，属于逻辑日志。通过记录的每次操作的反操作，提供回滚功能