导图社区 MySQL知识导图
- 30
- 1
- 0
- 举报
MySQL知识导图
关于MySQL知识导图，主要包含MySQL逻辑架构、 MySQL存储数据结构、Mysql存储引擎、 SQL执行顺序、索引优化、SQL优化等内容。
编辑于2023-12-16 22:43:28- L逻辑架构
- 相似推荐
- 大纲
MySQL高级
sql_mode 基本语法及校验规则
ONLY_FULL_GROUP_BY
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中
NO_AUTO_VALUE_ON_ZERO
该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么去掉该选项。
STRICT_ALL_TABLES
STRICT_TRANS_TABLES
对于支持事务的表，这两种模式是一样的：如果发现某个值缺失或非法，MySQL将抛出错误，语句会停止运行并回滚。
NO_ZERO_IN_DATE
在严格模式下，不允许日期和月份为零
NO_ZERO_DATE
设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。
ERROR_FOR_DIVISION_BY_ZERO
除0报错
NO_ENGINE_SUBSTITUTION
如果需要的存储引擎被禁用或不存在，那么抛出错误。不设置此值时，用默认的存储引擎替代
MySQL逻辑架构
逻辑架构总体概览
连接层
最上层是一些客户端和连接服务，包含本地socket通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。
主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。
服务层
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。
在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化：如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作
核心服务
Management Serveices & Utilities： 系统管理和控制工具
SQL Interface: SQL接口
接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
Parser(破搜): 解析器
SQL命令传递到解析器的时候会被解析器验证和解析
Optimizer(啊噗偷马贼): 查询优化器
SQL语句在查询之前会使用查询优化器对查询进行优化。
Cache和Buffer： 查询缓存
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
MySQL8.0之后的版本直接删除了查询缓存
引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取
存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。
SQL执行周期
开启SQL查询缓存
方式一:修改配置文件 追加配置信息
query_cache_type = 1 query_cache_size = 600000
windows下是my.ini
linux下是my.cnf
方式二:使用mysql命令
set global query_cache_type = 1;
查询是否开启缓存
show variables like '%profiling%';
MySQL存储数据结构
追求高效率的查询速度 B+树
B 树
时间复杂度
O (log 2 N)
非叶子结点存储数据
磁盘存的节点元素少,需要更多的磁盘IO操作
查询需一层一层匹配,范围查询需要遍历
删除节点将导致树重排
B+ 树
时间复杂度
O ( log 3 N )
3 度
非叶子结点不存储数据
磁盘存的节点元素多,能减少磁盘IO操作,一次IO操作能获取更多的索引信息
查询数据时只需查询叶子结点 且维护了双向指针,以链表的方式组织,支持范围查询
删除节点B+树不会重排
mysql为了防止B+树索引重排,当索引被删除时,非叶子节点并不会删除,而将叶子节点上的数据标记为无效,当表上的索引越删越多时,就会照成大量索引空洞,能找到非叶子节点的索引信息,但叶子结点上数据无效
注意索引空洞就是由此产生,由于索引不断被删除,导致索引产生空洞效应,解决方式:重新构建索引(删除以前的索引,再建新的索引)
Mysql存储引擎
InnoDB引擎
MySQL5.5之后的版本,默认都是采用InnoDB,InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)
聚蔟索引 (必须存在,且只有一个 )
非聚蔟索引
MyISAM存储引擎
MySQL5.5之前的默认存储引擎,MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
Archive(爱开悟)引擎
只能进行 insert操作和 select 操作,适合日志和数据采集（档案）类应用。Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。
Memory引擎
如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。memory引擎是最快的,适合存放临时数据,数据都保存在内存中 当服务器重启后,数据都会丢失,但会保留表结构 memory使用的hash索引,hash索引不支持范围查询,且不支持排序
SQL执行顺序
from -> on -> join -> where -> group by ->having -> select ->distinct -> orderby -> limit
口诀 : 佛叫我干活.速度ol
索引优化
索引的定义:
索引是帮助MySQL高效获取数据的数据结构.索引本质就是数据结构,排好序的数据结构
存储位置
数据库索引存储在磁盘上,InnoDB读取磁盘数据时以页为基本单位,MySQL默认的页大小是16KB(16384K)
索引的优劣势
优势
提高数据检索效率,减少IO成本
降低数据排序成本,减少 CPU消耗
劣势
索引实际上也是一张表,该表保存了主键和索引字段,并指向实体表的记录,所以索引列也是要占用空间的
索引虽然提高了查询速度,但降低了表的更新速度
MySQL索引
聚蔟索引和非聚蔟索引
聚蔟索引
生成
表中有主键:以表中的主键构造一颗B+树
表中无主键
有唯一索引,用第一个唯一索引作为聚蔟索引
无主键无唯一索引,用表的行号构建聚蔟索引
主键索引一定是聚蔟索引,聚蔟索引不一定是主键索引,还可能是唯一索引以及行号索引
树结构特点:叶子结点存储表行记录数据
查找数据特点: 找到聚蔟索引键,然后在叶子结点IO读取对应的数据
MySQL中一定有聚蔟索引,且只有一个,但目前只有InnoDB支持聚蔟索引,MYISAM不支持
非聚蔟索引(二级索引)
生成:除了作为聚蔟索引外的其他索引都是非聚蔟索引
树结构特点: 叶子结点存储聚蔟索引的索引键
查找数据特点: 找到非聚蔟索引键,获取对应的聚蔟索引键,然后再去聚蔟索引表中根据聚蔟索引键IO读取对应的数据 ( 回 表 ) 应尽量避免回表操作,每一次回表可能都是一次IO操作
索引相关语法
创建索引
create index 索引名(index_xxx) on 表名(字段名)
创建前缀索引
create index index_字段名_前缀数 on 表名( 字段 ( 前缀数 ) )
创建联合索引
create index index_字段名1_字段名n on 表名( 字段名1 , 字段名n )
删除索引
drop index 索引名 on 表名
查询表索引
show INDEX from 表名
索引分类
单值索引
为单独某一个字段建立索引
唯一索引
确定某一列的值一定是唯一的,且经常被用来查询就可以作为唯一索引. 主键索引就是唯一索引
主键索引
MySQL在创建表时,我们指定那个字段作为主键,MySQL默认为主键建立索引,如果在创建表时没有指定主键,MySQL就不会创建主键索引.但是会有一个隐式索引(以行号作为索引)
两者都可作为聚蔟索引,优先主键索引作为聚蔟索引
复合索引(联合索引)
使用多个字段作为索引,联合索引使用得当可以避免回表查询
最左前缀原则 最左边的列必须存在,否则联合索引将失效
使用联合索引时,如果跳过了某一列,后面的列索引将失效
范围查询时,大于小于后的列索引将失效,尽量使用大于等于,或者小于等于
尽量做到覆盖索引
尽量将大字段放在联合索引的最左边
索引的创建与否情况分类
需要创建索引
主键自动创建主键索引
频繁查询的字段应该创建索引
查询中与其他表关联的字段,作为外键的字段需要建立索引
排序的字段
分组的字段
先排序后去重
不需要创建索引
表记录太少
经常增删改的表
where查询中用不到的字段
索引设计原则
针对数据量大,且查询比较频繁的字段建立索引
针对于常作为查询条件的字段,如Group By,where ,order by
如果是字符串类型的字段,且字段的长度较长,可以根据字符串的特点,建立前缀索引
尽量使用联合索引,减少使用单例索引,查询时,联合索引可以覆盖索引,节省磁盘空间,避免回表,提高效率
控制索引的数量,索引并不多越多越好,索引也会占用大量的磁盘空间,且维护索引的待久很大,会影响增删改的效率
如果索引字段不能存储NULL值,在建表的时候,就使用 NOT NULL约束它 . 便于优化器优化
查询时不要用 * ,需要什么字段查询相应的字段 使用覆盖索引
索引失效的情况
对索引字段使用了函数运算,包括普通运算,索引失效
字符串类型的字段不加单引号,造成隐式类型转换,索引失效，相当于给字段加了函数进行转换
模糊查询时,%加在前面,索引也会失效
or 连接的前后必须都有索引,任意一侧没有索引都将造成索引失效
数据分布影响 如果MySQL评估走全表扫描比走全索引更快,索引也将失效
范围查询后的联合索引将失效 < , > , !=
is not null 将导致索引失效 , is null 可以使用索引
索引优化分析
Optimizer优化器
处理掉一些常量表达式的预算，直接换算成常量值。并对 Query 中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等
Explain查看执行计划
语法
Explain sql语句

分组和排序的效率对比
分组需要先排序后去重,因此排序的效率高,分组的效率低
结果分析
id
代表一次查询的唯一ID
一次sql语句的执行 id相同从上到下，id不同由大到小

从图中可以看到 有两个 id相同都为2 此时执行顺序从上往下 还有个id为1 先大后小 id为2的都执行完毕后执行id为1的
子查询将导致多躺查询,能少用子查询就少用,查询的趟数反应了IO的次数
sql语句中有几个 select id的最大值就为多少 id值相同表示同一趟查询
select_type
代表此处查询的类型
SIMPLE : 本次查询是一次简单查询,没有使用子查询,没有union
PRIMARY : 本次查询是一次主查询
SUBQUERY : 本次查询是一次子查询
UNION : 本次查询使用了 UNION
DERIVED : 派生表
table
此次操作的是哪张表
partitions
分区
type
代表MySQL怎么带着这个表进行查询（连接）
system
此次查询直接返回结果(查询的是MySQL的系统信息) ,无需查询数据库
const
此次查询一次性(读一次索引数据)就查询出来了
eq_ref
和外表关联用到索引，而且主表的关联字段在外表只能搜索到唯一一个数据
ref：引用
eq：等值
eq_ref：等值引用；多表联查的使用，A的数据和B是唯一关联
ref
代表是一个多值引用. 和外表关联两者都用到索引，而且主表的关联字段在外表能搜索到多个数据
range
索引字段进行了区间查询
between and
in (...)
>= and <=
index
全索引扫描,代表只需要遍历索引树就够了
select 的 字段 必须是索引 (非聚蔟索引字段与聚蔟索引字段)
all
全表扫描
连接性能(由高到底)
system -> const -> eq_ref -> ref -> index_merge -> range -> index -> all
possible_keys
可能用到索引的字段
key
查询实际用到的索引
key_len
实际用到的索引长度,字节数
计算公式
int
允许为NULL
4 + 1
不允许为NULL
4
bigint
允许为NULL
8 + 1
不允许为NULL
8
char(n)
允许为NULL
3 * n +1
不允许为NULL
3 * n
varchar(n)
允许为NULL
3 * n + 2(可变预留2个字节) +1
不允许为NULL
3 * n + 2
text(n)
允许为NULL
3 * n + 2(可变预留2个字节) +1
不允许为NULL
3 * n + 2
ref
显示出这个表和哪个表哪个列有引用关系，什么样的引用关系看type即可（ref、eq_ref）
rows
代表操作了这个表的多少行记录,越少越好
filtered
通过查询条件获取的最终记录行数占通过type字段指明的搜索方式搜索出来的记录行数的百分比。
Extra
MySQL给我们这次查询提供其他额外分析信息
Using filesort
使用文件排序； order by 后的字段没索引，或有索引用不上(出现这种情况应该对排序的字段建立索引)
Using where
查询带了where 条件
Using temporary
使用了临时表
Using index
索引覆盖
仅用索引就能得到所有数据了，不用回表查真实数据(select 的字段都是 索引的字段 不用回表查询就满足条件)
要查询的字段正好是非聚蔟索引的键和聚蔟索引的键
Backward 反向索引
对索引字段进行 降序排列
Using index condition
索引下推:
在对联合索引(二级索引 || 非主键索引)遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数
列如对非聚蔟索引的二级索引字段进行排序时,就会使用到索引下推
注意: 索引下推是5.6之后的新特性
impossible where
mysql判断出这个sql的where条件不可能满足
列如 where id =1 and id = 2 这个where条件不可能满足
using join buffer
关联的字段无索引,出现这个提示,应考虑给关联查询的字段添加索引
SQL优化
insert 插入优化 三个方面
批量插入时使用 insert into 表名 values ( ),( )不要多次顺序插入, 使用mybatis的动态sql <foreach>
使用手动提交事务 ,不要每次插入一条数据后就自动提交事务,频繁的创建提交事务损耗性能
插入数据的主键有序,主键顺序插入的速度高于主键乱序插入的速度
主键优化
两种页现象
页分裂现象
当数据页一和数据页二的数据都是满的时候,此时乱序插入一个主键需要插在其中一页的中间,那么innodb将此页百分之五十的位置后的数据移动到新创建的一页数据上,然后再将乱序的主键插入其中,然后再将新创建的一页放到数据页一和数据页二中间,并用双向链表维护 其目的是为了维护叶子结点的有序性
页合并现象
innodb中删除索引数据的时候,叶子结点的数据将标记为被删除(具体可参考索引空洞问题),当被标记为已删除的数据占到此页数据的百分之五十时,innodb将判断是否可以将前后其中一页的数据合并 其目的是为了节省空间
主键优化三个方法
主键长度不宜过长,占用空间,产生更多页,导致多次IO
主键插入的时候,保持有序,避免产生页分裂
不要使用uuid或者其他自然主键如身份证
UUID随机,造成主键乱序,产生页分裂
UUID长度过长,占用空间
order by 优化
避免 Using filesort ,追求 Using index
对排序的字段建立索引
order by排序时,使用 sleect * 是大忌,只查询需要的字段
单路与双路排序
单路排序
是一次性取出满足条件行的所有字段，然后在 排序缓存 中进行排序
速度快,占用内存空间
双路排序
先根据相应的条件取出相应的排序字段和可以直接定位行 数据的行 ID，然后在 排序缓存 中进行排序，排序完后需要再次回表取回其它需要的记录；
速度慢,节省内存空间
group by 优化
分组的字段建立索引
排序分组优化
无过滤,不索引
没有使用where 的时候 可以试着加上 limit
顺序错,必排序
联合索引的字段顺序错了,一定走全表排序
方向反,必排序
联合索引排序的字段排序规则必须一致,要么全升序,要么全降序,否则一定走全表排序
limit 分页优化
通过 覆盖索引与子查询以及范围查询进行优化
例如 select * from t limit 1900000,10
方式一
select id from t order by id limit 1900000,10
首先查询要查询范围内的主键,使用覆盖索引
select t.* from t , ( select id from t order by id limit 1900000,10 ) t1 where t.id = t1.id
再通过子查询 进行主键 eq_ref查询
方式二
select id from t limit 1900000,1
首先进行 const 查询 获取范围内第一个 主键id
select * from t,(select id from t limit 1900000,1) t1 where t.id > t1.id LIMIT 0,10
通过 const,range 查询 最后分页
count 优化
MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回总行数
InnoDB则需要把数据从引擎中读出来,一行一行统计,不为nulll则进行按行累计,返回累计数 或者一个新表 再执行insert时,使用redis 把值count值加一 delete时把值减一
count(主键)
InnoDB引擎会遍历整张,把主键取出,然后返回给服务层,服务层拿到主键后 ,按行进行累加
count(字段)
没有not null约束
InnoDB引擎会遍历整张,把字段取出,然后返回给服务层,服务层拿到主键后,判断是否为null,不为null,则按行进行累加
有not null约束
InnoDB引擎会遍历整张,把字段取出,然后返回给服务层,服务层拿到主键后 ,按行进行累加
count(数字)
InnoDB引擎会遍历整张,不取值,服务器对于每一行都放进去一个对应的数字,直接按行累计
count(*) InnoDB引擎会遍历整张,不取值,服务器进行了专门的优,直接按行累计
两者速度最快,因为不需要取值
count(非索引且没有进行 not null约束字段) 的字段时,特别注意 是会判断是否为null 为null则不累积
update优化
update 表名 set 字段名 = 值 where 字段名(该字段一定要加索引) = 值
更新的条件字段名一定要加索引,否则行锁将升级为表锁
MySQL主从复制
主从复制原理
slave开启线程从master读取binlog日志(二进制日志),拷贝到Readlog日志(中继日志)中,SQL线程再读取Readlog日志的内容
复制的基本原理
每个slave只能有一个master
每个slave都只能有一个唯一的服务器ID
每个master可以有多个slave
主机配置
修改核心配置文件my.cnf 中配置
server-id=1 log-bin=mysql-bin binlog-ignore-db=mysql binlog-ignore-db=infomation_schema binlog-do-db=mytestdb binlog_format=STATEMENT
主从所有配置项都配置在[mysqld]节点下，且都是小写字母
binlog_format
STATEMENT（默认）
语句级
binlog会记录每次一执行写操作的语句,相对于ROW模式节省空间,但会产生数据不一致问题
列如主机执行 update tt set create_date=now() 由于从机的执行时间不一致,造成数据不一致
优点： 节省空间
缺点： 有可能造成数据不一致。
ROW
行级
binlog会记录每次操作后每行记录的变化
优点：保持数据的绝对一致性。因为不管sql是什么，引用了什么函数，他只记录执行后的效果。
缺点：占用较大空间。
MIXED
statement的升级版，一定程度上解决了，因为一些情况而造成的statement模式不一致问题
主机上建立帐户并授权slave
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456'; flush privileges;
grant all privileges on *.* to root@'%' identified by 'root'; # 创建一个root 用户，并授权远程访问！
flush privileges 记下File和Position的值
查询master的状态: show master status
执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化
从机配置
server-id=2 relay-log=mysql-relay
修改核心配置文件my.cnf 中配置
[必须]从服务器唯一ID：server-id=2
[可选]启用中继日志：relay-log=mysql-relay
CHANGE MASTER TO MASTER_HOST='主机ip地址', MASTER_USER='X',MASTER_PASSWORD='X',
CHANGE MASTER TO MASTER_HOST='mall_mysql_master',MASTER_PORT=3306,MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=0;
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;
启动从服务器复制功能
start slave;
查看状态
show slave status\G;
Slave_IO_Running: Yes
lave_SQL_Running: Yes
主从机重启配置生效
systemctl restart mysqld
主从机都关闭防火墙
ystemctl stop firewalld
停止从服务复制功能，重新配置主从
在从机上执行。功能说明：停止I/O 线程和SQL线程的操作
mysql> stop slave;
在从机上执行。功能说明：用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件。
mysql> reset slave;
在主机上执行。功能说明：删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。 用于第一次进行搭建主从库时，进行主库binlog初始化工作；
mysql> reset master;