MYSQL思维导图
2023-01-15 15:16:51 2 举报AI智能生成
登录查看完整内容
为你推荐
查看更多
用思维导图方式来整理mysql相关技术内容
作者其他创作
大纲/内容
索引的定义：索引是帮助MYSQL高效的获取排好序的数据的数据结构。
树高度普通二叉树最坏n.
二叉树
二叉平衡树，树高度logn+1.以20000000条数据为例，h=log20000000=25
红黑树
对索引的key进行一次hash计算就可以定位出数据存储的位置
很多时候Hash索引要比B+Tree更高效
hash冲突问题(数组+链表)
Hash表
叶子节点具有相同的深度，叶节点的指针为空。
所有索引的元素不重复
节点中的数据索引从左到右递增排列
B-Tree
查看页大小：show global status like 'innodb_page_size';
B+Tree
索引的数据结构
test.frm 存放表结构等信息
test.MYD 存放数据
test.MYI 存放索引
MyISAM：索引文件和数据文件是分离的，属于非聚集索引
表结构文件本身就是按B+Tree组织的一个索引结构文件。
叶子节点包含了完整的数据记录。
建议InnoDB表必须建主键，并且推荐使用整型的自增主键。
保持一致性，若修改记录内容，只需要改一次主键索引中的数据，再更新非主键索引即可。
节省空间，只用保留一份数据即可，若索引中全都要有完整数据，浪费硬盘。
非主键索引(二级索引)结构中的叶子节点存储的是主键值。
InnoDB：索引文件和数据文件是不分离的，属于聚集索引
存储引擎
遵循索引最左前缀原理
如果第一个字段是范围查询需要单独建一个索引；
在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边；
联合索引底层数据结构：多个字段组成的联合索引，按字段顺序进行排序
深入理解MYSQL索引底层数据结构和算法
模拟优化器执行SQL语句
分析你的查询语句或是结构的性能瓶颈
执行查询会返回执行计划的信息
from 中包含子查询，仍会执行该子查询，将结果放入临时表中
explain工具介绍
explain extended
explain partitions
id
simple：简单查询。查询不包含子查询和union
primary：复杂查询中最外层的 select
subquery：子查询，包含在 select 中的子查询（不在 from 子句中）
derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义：衍生）
union：在 union 中的第二个和随后的 select
select_type
table
system是const的特例
system
读取一次
const
primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。
eq_ref
相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行
ref
range
通常扫描二级索引，扫描全索引拿到结果
index
全表扫描，可以考虑通过增加索引进行优化
all
type
这一列显示查询可能使用哪些索引来查找
possible_keys
没有使用索引
NULL
强制使用possible_keys中的索引
force index
忽略使用possible_keys中的索引
ignore index
key
列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列
char(n)：如果存汉字长度就是 3n 字节
varchar(n)：如果存汉字则长度是 3n + 2 字节
字符串
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
数值类型
date：3字节
timestamp：4字节
datetime：8字节
时间类型
key_len
这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
row
Using index：使用覆盖索引
Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖
Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围
Using temporary：mysql需要创建一张临时表来处理查询
Using filesort：将用外部排序而不是索引排序
Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段
extra
explain中的列
全值匹配
最左前缀法则
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句
mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作
字符串不加单引号索引失效
少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
范围查询优化
索引最佳实践
Explain详解与索引最佳实践
命令行连接，linux或者windows下安装的mysql客户端，通过命令行连接。
navicat等连接工具，可视化。
客户端层
mysql -h 数据库地址 -u 用户 -p 密码 -P 端口
show processlist;
kill Id;
默认8小时，28800秒
show global variables like \"wait_timeout\";
set global wait_timeout=28800;
连接器：管理连接与权限校验。
MySQL8已经移除
查询缓存
词法分析
语法分析
语义分析
构造执行树
生产执行计划
计划的执行
词法分析器：词法分析，语法分析
优化器：执行计划生成索引选择
执行器：调用引擎接口获取查询结果
服务端层
Innodb:最常用，从 MySQL 5.5.5 版本开始成为了默认存储引擎。
MyISAM
memory：不常用
存储引擎层
log-bin=/usr/local/mysql/data/binlog/mysql-bin
server-id=123454
#表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定
sync-binlog=1
开启binlog功能，配置my.cnf
show variables like '%log_bin%'; 查看bin-log是否开启
flush logs; 会多一个最新的bin-log日志
show master status; 查看最后一个bin-log日志的相关信息
reset master; 清空所有的bin-log日志
binlog命令
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001
查看binlog内容
bin-log归档
SQL底层执行原理详解
联合索引第一个字段用范围不会走索引
explain select * from employeesforce index(idx_name_age_position)where name > 'LiLei' and age = 22 and position = 'manager';
强制走索引
覆盖索引优化
in和or在表数据比较大的情况会走索引，在表记录不多的情况下会选择全表扫描，MySQL8，记录不多也会走索引
explain select * from employees where name like 'LiLei%' and age = 22 and position = 'manager';
like KK% 一般情况都会走索引
综合例子
MySQL5.6以前，先以LiLei为头，查出所有索引，拿这个索引对应主键逐个回表，找出数据后再对比age和position.
MySQL5.6以后，先拿到索引判断三个字段是否匹配，找到符合的索引，再回表，减少了回表的次数。
针对二级索引有效，如果是主键索引，无效。
索引下推（Index Condition Pushdown，ICP），like用到了索引下推优化。
开启： set session optimizer_trace=\"enabled=on\
select * from employees where name > 'a' order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
关闭：set session optimizer_trace=\"enabled=off\";
trace工具（追踪工具）
1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
2、order by满足两种情况会使用Using index。1) order by语句使用索引最左前列。2) 使用where子句与order by子句条件列组合满足索引最左前列。
3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
4、如果order by的条件不在索引列上，就会产生Using filesort。
5、能用覆盖索引尽量用覆盖索引
6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。
常见SQL深入优化
max_length_for_sort_data 系统变量 1024字节，1K
单路排序：单条记录字段的总长度小于1K，一次性将所有字段取出进行排序。
双路排序: 单条记录字段的总长度大于1K，只将排序字段和主键取出，排序后再回表。
filesort文件排序
代码先行，索引后上
联合索引尽量覆盖条件
不要在小基数数字段上建立索引
where和order by冲突时，优先优化where.
http://note.youdao.com/noteshare?id=c71f1e66b7f91dab989a9d3a7c8ceb8e&sub=0B91DF863FB846AA9A1CDDF431402C7B
基于慢SQL查询优化
索引设计原则
Mysql索引优化实战1
主键自增且连续结果是按照主键排序的
根据自增且连续的主键排序的分页查询
让排序时返回的字段尽可能少
根据非主键字段排序的分页查询
分页查询优化
循环读取驱动表，根据关联条件，取出被驱动表数据，组成结果集
嵌套循环连接 Nested-Loop Join(NLJ) 算法
把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比
基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
Join关联查询优化
关联字段加索引
小表驱动大表
where中时小表则in
from中小表，则exsits
in和exsits优化
count(*)≈count(1)>count(字段)>count(主键 id)
字段有索引
count(*)≈count(1)>count(主键 id)>count(字段)
字段无索引
查询mysql自己维护的总行数
show table status
将总数维护到Redis里
增加数据库计数表
常用优化方式
count(*)查询优化
对于关联sql的优化
如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。
建议使用TINYINT代替ENUM、BITENUM、SET。
避免使用整数的显示宽度(参看文档最后)，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用INT
DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置
建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作
整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT
MySQL能存储的最小时间粒度为秒
建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd
用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串
当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后），MySQL会自动返回记录插入的确切时间
TIMESTAMP是UTC时间戳，与时区相关
DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么
除非有特殊需求，一般的公司建议使用TIMESTAMP，它比DATETIME更节约空间，但是像阿里这样的公司一般会用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题
有时人们把Unix的时间戳保存为整数值，但是这通常没有任何好处，这种格式处理起来不太方便，我们并不推荐它
日期和时间
字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR
CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些要用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题。换句话说，可能影响到计算的准确性和完整性
尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联
BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关
BLOB和TEXT都不能有默认值
阿里巴巴Mysql规范解读
Mysql索引优化实战2
事务及其ACID属性
最后的更新覆盖了由其他事务所做的更新
更新丢失(Lost Update)或脏写
事务A读取到了事务B已经修改但尚未提交的数据
脏读（Dirty Reads）
事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性
不可重复读（Non-Repeatable Reads）
事务A读取到了事务B提交的新增数据，不符合隔离性
幻读（Phantom Reads）
并发事务处理带来的问题
可能有脏读，不可重复读，幻读
读未提交（Read uncommitted）
oracle默认
可能有不可重复读，幻读
读已提交(Read committed)
msyql默认
可能有幻读
可重复读(Repeatable read)
没有问题，但是效率很低，通常不用
可串行化(Serializable)
事务隔离级别
用版本号对比来实现
乐观锁
悲观锁
从性能上
S锁(Shared)
共享锁
读锁
X锁(eXclusive)
排他锁
写锁
操作类型上
表锁
行锁
数据操作粒度上
锁的就是两个值之间的空隙
间隙锁
是行锁与间隙锁的组合
临键锁
子主题
死锁
锁分类
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少检索条件范围，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
尽可能低级别事务隔离
锁优化建议
深入理解Mysql事务隔离级别与锁机制
一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链
undo日志版本链
每次查询前都会生成
读已提交RC
第一次查询生成后就不变了
可重复读RR
一致性视图read view机制
MVCC多版本并发控制
在内存中缓存起来，可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件
Innodb引擎SQL执行的BufferPool缓存机制
深入理解MVCC与BufferPool缓存机制
变长字段长度列表
每个允许为null字段，1代表为null
NULL值列表
预留位1 1 没有使用
预留位2 1 没有使用
delete_mask 1 标记该记录是否被删除
min_rec_mask 1 B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned 4 表示当前记录拥有的记录数
heap_no 13 表示当前记录在页的位置信息
record_type 3 表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录
next_record 16 表示下一条记录的相对位置
记录头信息:5字节，40位
记录的额外信息
先使用用户定义的主键，然后使用唯一索引，最后是自增的row_id
DB_ROW_ID(row_id)：非必须，6字节，表示行ID，唯一标识一条记录
DB_TRX_ID(trx_id)：必须，6字节，表示事务ID
DB_ROLL_PTR(roll_ptr)：必须，7字节，表示回滚指针
隐藏列
列1-列n
记录的真实信息
Compact
Redundant行格式是MySQL5.0之前用的一种行格式，不予深究。
Redundant
MySQL5.7的默认行格式就是Dynamic
Dynamic
Compressed
InnoDB存储引擎设计了4种不同类型的行格式
File Header 文件头部 38字节 页的一些通用信息
Page Header 页面头部 56字节 数据页专有的一些信息
Infimum + Supremum 最小记录和最大记录 26字节 两个虚拟的行记录
User Records 用户记录 大小不确定 实际存储的行记录内容
Free Space 空闲空间 大小不确定 页中尚未使用的空间
Page Directory 页面目录 大小不确定 页中的某些记录的相对位置
File Trailer 文件尾部 8字节 校验页是否完整
索引页格式
控制块
内存碎片
缓存页
free链表管理
flush链表管理
LRU链表管理
Buffer Pool
Change Buffer
Log Buffer
内存结构
数据字典
双写缓冲区
Change Buffer
Undo日志
系统表空间
256个区
组
64个页
区:物理上连续，减少随机I/O
16K，若干记录
页
页节点段:逻辑上的
非页节点段
回滚段
独立表空间
通用表空间
临时表空间
Undo表空间
Redo日志
磁盘结构
InnoDB的体系结构
InnoDB引擎底层存储和缓存原理以及MySQL8新特性
MYSQL
0 条评论
回复 删除
下一页
