MySQL
2021-10-19 12:15:31 19 举报AI智能生成
MySQL 总结
作者其他创作
大纲/内容
基础
存储引擎
体系结构
客户端
JDBC
ODBC
服务层
连接管理器
查询缓存
查询解析器
查询优化器
存储引擎
MyISAM
InnoDB
Memory
其他
InnoDB 存储引擎
MySQL 5.5.8 默认引擎
表空间保存文件
系统表空间 ibdataN
所有数据放在一个文件中，不利于管理
会产生IO瓶颈
很难回收存储空间
独立表空间 tablename.idb (推荐)
使用 optimize table 命令回收存储空间
设置innodb_file_per_table 决定表空间模式
支持事务
默认使用行级锁
支持高并发
字符集
utf8mb4 四字节 (推荐)
utf8 为三字节 无法存表情
锁
职责
共享锁 - 读锁
独占锁 排它锁 - 写锁
颗粒度
行级锁
只有利用索引的update、delete 才是行级锁，否则使用表级锁
表级锁
适用场景
支持全文索引与空间函数
适用绝大多数场景
MyISAM 存储引擎
特点
不支持事务
支持全文检索
支持text支持前缀索引
支持数据压缩
紧密存储，顺序读性能很好
表级锁，混合读写性能不佳，并发性差
适用场景
非事务性应用，例如：保存日志
只读类的应用，报表数据，字典数据
空间类应用，开发GIS系统 5.7版本之前
系统临时表，SQL查询、分组的临时表引擎
Memory 存储引擎
不支持事务
内存读写，临时存储
超高的读写效率
表级锁，并发性差
适用场景
读多写少的静态数据
充当缓存使用，保存高频访问静态数据
系统临时表 超过边界值以MyISAM保存到硬盘上
关键参数
max_heap_table_size 控制内存表大小
tmp_table_size 内存临时表最大值
CSV 存储引擎
特点
不支持事务
不支持索引
纯文本保存
适用场景
数据迁移、数据交换
MySQL -> CSV <- Oracle SqlLoader
不依赖MySQL环境
可以导出CSV文件，供其他应用读取
索引调优
目的
为表建立目录
防止全表扫描
存储格式由存储引擎决定
索引存储结构分类
BTree
B-Tree
B+Tree
适用于范围索引
采用树形链表结构建立数据目录
允许前缀查询 like 'aaa%'
不允许后缀与模糊匹配 like '%aaa%'. '%aaa'
Hash索引
精确查找
full-index 全文索引
R-Tree索引
应用层次
普通索引
唯一索引
复合索引
必须包含左侧列
聚集索引
InnoDB 实际数据保存的顺序，和索引一致
非聚集索引
MyISAM 实际数据保存的顺序，和索引不一致
索引优化策略
什么情况下不会用到索引
索引选择性太差
<> not in 无法使用
is null 会使用，is not null 不会使用索引
where 子句跳过左侧列，直接使用右侧列
对索引计算或者使用函数
使用索引优化排序
当排序出现了索引左侧列，则允许使用索引排序
左侧字段单字段排序时，索引支持升降序
多字段情况下，左侧字段必须是升序，且顺序不允许打乱
多表关联优化
嵌套循环关联 Nested Loop Join
驱动表越少越好
外键上加索引，条件上加索引
慢查询
通过日志可以看到
EXPLAIN 参数
id select 序列号，多表关联情况下 id=1 就是驱动表
select_type
simple 最普通查询
derived 包含在from 子句中的子查询
union
union result : union 计算结果 会产生临时表 没有索引
subquery 在select出现的子查询
primary 复杂查询中最外层的select
table
访问的表
partitions
分区表
type 表示关联类型或访问类型，即MySQL决定如何查找表中的行。
ALL
const
ref
eq_ref
rang
index
ref_or_null
possible_keys 显示查询可能使用那些索引来查找
key 显示mysql实际采用哪个索引来优化对表的访问
key_len 在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列
ref 显示在key列记录的索引中，表查找值所用到的列或常量
row 估计预读并检测的行数
filtered 百分比 ，代表 （rows * filtered ）/100 ,这个结果将于前表产生交互
extra
using index 使用到了索引
分区表
0 条评论
下一页
