MySQL调优
1 索引
定义:索引本质是数据结构。索引在存储引擎中实现。
优缺点:
- 优点是加快了查找速度
- 缺点是增加了维护索引时间开销和存储索引的空间开销
1.1 InnoDB索引方案
索引即数据,数据即索引。
物理存储机制1:将存储空间分页,将数据项(元组及附加信息存储)到页中,称之为数据页。之后有一个页目录(类似于页表),页目录项存储存储元组的页的信息。存储页目录的页是成为目录页。还可以对目录项再定义一个目录,即多级目录2。整体形成一个B+树。
索引分类
类型 | 说明 |
---|---|
聚簇索引 | 1. 聚簇索引是以自增主键为索引键,建立B+树,叶子节点是表的数据,聚簇索引一个表只能有一个 2. 创建聚簇索引,元组的物理排序会按照主键重新排序。 3. InnoDB自动创建。 4. 优点是查询速度快,缺点是插入速度依赖于插入顺序,更新主键代价高 |
二级索引 | 1. 二级索引是以其他键为索引键,建立B+树,叶子节点存的是主键值和索引键的值 2. 回表:根据索引键找其他列的数据会先在二级索引中招到对应叶子节点,后找到主键,再在聚簇索引中找对应的数据 |
联合索引 | 联合索引指的是多个键为索引键,建立B+树,叶子节点存的是主键值和索引键的值 |
特点
- 根页面位置不动:当最开始的页面存储不下数据时,会将其存储的的数据复制到新的页面之后这个页面存储目录。当目录变多是,同样处理。保持其为根目录即可,这个页面称为根页面,其永远不会变。
- 内节点的目录项唯一:内节点中目录项包括索引键和页面位置,用来标记每个内节点。
- 一个页面至少两条记录
- 非叶子节点存储页面位置以及该页面的记录最小值
1.2 MyISAM的索引方案
同InnoDB,不过叶子节点存储的是数据记录地址(非聚簇索引)。索引和数据分开存储,而InnoDB存在一起。
特点:
- 要回表找数据,速度比较快
- 数据索引分离存储
- MyISAM可以没有主键
1.3 与其他数据结构对比
哈希:
等值查找速度极快,但是不能范围查询;数据存储无序,排序要时间。InnoDB有自适应哈希索引,将经常访问的放到哈希表中,通过哈希寻找。
二叉搜索树:
高度过高,搜索效率同线性查找。
平衡二叉树:
保证左右子树高度差不超过1的二叉搜索树。
B树:
平衡M叉树。
B+树:
只需要在叶子节点存储数据的B树。中间节点小,一个页能够存储更多的中间节点,io次数少。
1.4 InnoDB数据存储结构
1.4.1 页结构
页:类似于内存中的分页,不过大小为16KB。是和磁盘和内存之间交换数据的基本单位。
页的特点:在空间中使用双向链表的方式连接,页内数据使用单链表连接。页分布不是连续的。
页结构:
- 文件首部:
类型 | 说明 |
---|---|
偏移量 | 页号,2字节 |
页类型 | 2字节 |
上一页 | 同层使用双向链表连接,为前一个页, 4字节 |
下一页 | 后一个页,4字节 |
校验和 | 判断页面是否相等,校验和相等。4个字节 |
日志序列号 | 页面被最后修改时对应的日志序列的位置。8个字节 |
- 文件尾部:
类型 | 说明 |
---|---|
校验和 | 将修改后的页与外存之间进行数据交换时,首部会将校验和赋给带尾部校验和。 与首部不一致说明页不完整。4个字节 |
日志序列号 | 将修改后的页与外存之间进行数据交换时,首部会将lsn后4字节赋给带尾部lsn。 与首部不一致说明出现了同步问题:如修改时,出现了数据库故障导致修改未修改完全。4个字节 |
- 空闲空间:
页中还没有被使用的空间
- 用户记录:
按照行格式存储的记录
- 最小最大记录:
记录值最小节点和值最大节点。
- 页目录:
记录每个记录组的位置。
-
记录会分组。最小记录单独为一组,其他组6-8个记录为一组。每组最后一个记录该组的记录数。
-
页目录是有若干槽组成,每个槽记录每组最后一个记录的偏移量。
- 页面头部:
记录页的状态信息
1.4.2 行格式
也称记录格式。
COMPACT行格式
- 变长字段长度列表:
将长度可变的列(字段)占用的存储空间大小存储在这里。
- NULL值列表:
记录字段是否为NULL,1为NULL,0为NOT NULL。跳过主键。
如:
对应(value1,value_primary_key,NULL,NULL)
- 记录真实数据:
存储的数据+三个隐藏列(行id,事物id,回滚指针)
- 记录头信息:
类型 | 说明 |
---|---|
预留位 | 未使用 |
delete_mask | 标记记录是否被删除 |
min_rec_mask | 是否为非叶子节点最小记录 |
record_type | 记录类型:0为叶子节点,1为非叶子节点,3最小记录,4最大记录 |
heap_no | 记录当前记录在本页中的位置 |
n_owned | 所处组当前的记录数 |
next_record | 下一条记录的位置 |
Dynamic格式:
- 行溢出:记录长度太长了,一个页存不下。Compact和Reduntant行格式会只存一部分,其余部分存储到其他页。地址和大小在本页中用20字节指出。
Dynamic和Compressed格式只存指针,数据存在单独的数据页。
- 行格式和compact相同
1.4.3 其他概念
区:64个连续的页构成一个区。申请分配空间单位。
- 空闲区
- 有剩余空间的碎片区
- 无剩余空间的碎片区
- 属于某个段的区
段:多个区组成,可以是不连续的区。数据库分配的单位,不同数据库创建是会分配。如表段,索引段。叶子节点和非叶子节点在不同的段中,分别为叶子节点段和非叶子节点段。这样范围查询方便。
碎片区:碎片区直属于表空间,不是段。用来分配给数据量较小的表。分配空间流程:先给段分配到碎片区,当占用32个碎片区后,分配区。
表空间:若干个个段组成。
-
系统表空间
存储系统信息和表的表空间,一个MySQL进程只有一个。
-
独立表空间
每张表一个表空间,用于保存信息。便于数据库之间的迁移。删表会自动删除表空间。
-
撤销表空间
存储撤销(Undo)信息的特殊类型的表空间
-
临时表空间
存储临时数据的区域。如:排序,分组和聚合,连接操作产生的临时数据等。
1.5 索引的实现和设计原则
1.5.1 索引分类
类型 | 说明 |
---|---|
普通索引 | 索引键不做限制 |
唯一性索引 | 索引键为唯一性约束 |
主键索引 | 索引键为主键 |
全文索引 | 索引键为文本类型的字段 |
空间索引 | 索引键为地理信息等空间信息 |
1.5.2 索引的实现
创建:3
-
隐式创建:主键约束,唯一性约束,外键约束自动创建
-
显式创建4
CREATE TABLE XXX (
字段列表,
....
表约束,
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [INDEXNAME] (clo_name [(length)] [ASC|DESC])
); #建表时创建,排序默认升序。length表示字段的前length个字符作为索引,常为文本类型的。
#添加索引
① ALTER TABLE ADD 索引语句;
② CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名(列名);
查看索引
SHOW INDEX FROM 对象;
索引删除5
①
ALTER TABLE 表名 DROP INDEX 索引名;
②
DROP INDEX 索引名 ON 表名;
1.5.3 新特性
隐藏索引6:
将索引隐藏,索引将不起作用6。但,并没有删除。以此可以检测索引的的作用大小。默认可见。
ALTER TABLE 表名 ALTER INDEX 索引名 [INVISIBLE|VISIBLE];
1.5.4 设计原则
适合创建索引情况
情况 | 说明 |
---|---|
唯一性约束的字段 | 值唯一 |
频繁作为where查询条件的字段(DQL和DML) | 查询多 |
经常group by和order by的字段 | 索引也会排序,相同数值的会存在一起 |
DISTINCT语句修饰的字段 | 相当于唯一性约束 |
多表连接时,对连接的字段创建索引 | |
列类型小的字段 | 比较操作越快,一个页存的记录数越多 |
字符串的前缀(前缀索引) | 长度由length指出 |
散列度高的字段7 | |
使用频率高的字段放在联合索引的前面 | 联合索引按照顺序查 |
多字段均要索引,联合索引效率更高 |
不适合创建索引情况
情况 | 说明 |
---|---|
where使用不到的字段 | |
数据量小的表 | 速度已经很快了,会增加维护索引的开销 |
散列度很低的字段 | |
经常更新的表 | 经常维护索引 |
值无序的字段 | 如身份证,索引树需要频繁地进行分裂和合并来维护数据的有序性。索引树是有序的。 |
删除很少使用的索引 | |
不定义冗余或重复的索引 | 冗余:index(a,b,c)相当于index(a),index(a,b),但不相当于index(b)。对于已经有以该字段开头的索引,就不要在单独建立了。 |
2 性能分析工具
2.1 数据库服务器优化步骤
优化方向:
2.2 查看系统性能
show status like '参数'
统计SQL查询成本
- 指标:事后查询SQL语句要读取的页的数量。
show status like 'last_query_cost'
2.3 工具
2.3.1 mysqldumpslow
作用:记录运行时间超过long_query_time(默认10s)的SQL语句。该选项默认关闭。
开启慢查询日志
set @@global.slow_query_log = 1;
查看慢查询次数
show status like 'slow_queries';
分析工具
mysqldumpslow 慢查询日志文件#mysql外命令
查看查询执行动作和时间
set @@profiling=1;#记录执行流程
show profiels;#显示执行的语句
show profile for Query num;#查看第num条语句执行流程
2.3.2 explain
重点
工具:explain(mysql内部工具)。能够查看某个查询语句的具体执行计划。
适用范围:select、update、delete
输出结果8
字段 | 解释 |
---|---|
id | id是用来标记有sql语句的,子查询也算一个语句。 |
table | table是用来标记表的。临时表,如union操作产生的,也标记。 |
slect_type | 标记查询类型 SIMPLE:无union和子查询的查询 PRIMARY:包含UNION [ALL]或子查询的大查询 UNION:A UNION [ALL] B,B为UNION UNION RESULT: union操作产生的临时表 SUBSQUERY:子查询不能优化为多表连接的方式,且不是相关子查询 DEPENDENT SUBQUERY:子查询不能优化为多表连接的方式,且是相关子查询 DERIVED:由查询语句生成的表 MATERIALIZED:子查询被转化为物化表 |
partitions | 对应的分区 |
type9 | 标记MySQL对某个表执行查询时的访问方法。以下性能逐级递减 system:只有一条记录,且存储引擎的统计数据精确(有专门的变量记录,如MyISAM) const:主键或唯一二级索引列进行等值匹配时(优化的最高标准) eq_ref:连接查询时,被驱动表10(一般为被连接表)是主对键或唯一二级索引列进行等值匹配时 ref:普通二级索引等值匹配 ref_or_null:没有非空约束的普通二级索引等值匹配 index_merge:索引合并11 unique_subquery:in 子查询可优化为exists子查询,且子查询有主键等值匹配,子查询的类型为它 range:范围查询(优化的最低标准) index:索引覆盖12 all:全表扫描 |
key | 使用索引 |
posssible_key | 可能的索引,从中选择索引,成本最低 |
key_length | 实际使用到的索引字节数。针对联合索引。 |
ref | 与索引列等值匹配的对象信息 |
rows | 预估读取条目数,越小越好 |
filtered | 满足条件的条目占比,filtered=查询结果条目数/rows,越高越好。对于多表查询效果好。 |
extra13 | 说明如何执行查询语句 |
语法:
explain SQL语句;
explain输出格式
explain [format=[json|tree]] SQL语句;
2.3.3 trace
了解即可。
工具:trace,能够追踪优化器做出的决策。
必要的设置:
SET optimizer_trace="enabled=on",end_markers_in_json=on;#打开trace,并设置为json格式
set optimizer_trace_max_mem_size=1000000;#设置最大占用空间
查看结果
select * from information_schema.optimizer_trace;
3 索引优化和查询优化
优化方向:物理查询优化(索引优化和表连接方式优化),逻辑查询优化(优化SQL)。
3.1 索引失效情况14
情况 | 说明 |
---|---|
最佳左前缀规则15 | where语句中的查询字段只能是联合索引的前k个,一旦跳过后面的字段无法使用。否则一能用到联合索引的一部分。如,index(A,B,C)查B,C则索引失效,查AC则只用到联合索引中的A。 |
主键插入顺序 | 主键最好设置为auto_increment,。即逐渐要按顺序插入 |
未直接对索引键过滤 | where中直接使用索引情况:等值和范围匹配,in和or 。如,name = 123 ;name为字符串,而123为数值型,所以存在类型转换,不是直接使用。如对索引键使用函数,类型转换,计算,均不行 |
非相等判断 | 不相等判断无法使用索引,如!=,is not null。相等判断可以使用。 |
模糊匹配 | 模糊匹配以%开头不行 |
非索引字段 | 非索引字段使用or连接,会失效。如,INDEX(A), A OR B 仍为all 类型。slect语句中有非索引字段可能会失效,因为用回表(看数据量)。 |
索引使用判断:
index(a,b,c);
索引建议:
- 联合索引,过滤性好的放前面,范围查找放后面。且要包括where语句中更多的字段。
- 单列索引,选择对查询过滤性好的索引。
3.2 多表查询优化16
左外连接:
- 索引键为连接字段
- 被驱动表建立连接字段索引会加快,驱动表提升不大。但,建议两个索引均建立。
内连接:17
- 索引键为连接字段
join语句原理:
- 简单嵌套循环连接
被驱动表取出一条数据,遍历被驱动表。直到驱动表的数据全部取完。
- 索引嵌套循环连接
被驱动表取出一条数据,被驱动表使用索引查找。直到驱动表的数据全部取完。
- 块嵌套循环连接
有一个Join Buffer。驱动表将部分数据加载到内存中的Join Buffer中。存储在Join Buffer中的每个元素会,遍历被驱动表。减少了扫描次数和读取记录数。
-
驱动表和被驱动表
判断:表行数*每行大小 值大的为驱动表,值小的为被驱动表。
-
hash Join
大数据集的常用方式。较小的表在内存中建立一个连接字段的散列表,扫描较大的表并探测散列表,找出匹配的行。
3.3 子查询优化
特点:会产生临时表,临时表无法创建索引优化
-
使用连接代替子查询
-
多次查询
3.5 排序优化18
优化方法:
- order by 使用的 字段替换为索引
filesort算法:
用于处理无法通过索引完成的排序任务
-
双路排序
先把索引列加载到内存中,排序。再根据排好序的索引列,按顺序将其余数据加载到内存中。
-
单路排序
将所有列加载到内存中,进行排序。空间要求高,速度快。
3.6 GROUP BY优化
优化方法:
- GROUP BY字段做为索引键
- 能在where中的条件不要在having中
- order by,group by,distinct语句保证where过滤出的结果在1000行以内,否则SQL很慢。
3.7 LIMIT 优化
优化方法:
- 自增主键:将偏移量换位where查询,如 limit 200,10换为 where id > 200 limit 10;
3.8 其他内容
3.8.1 覆盖索引
定义:一个二级索引包含了满足查询结果的所有数据称为覆盖索引。(二级索引中能查到的数据包含主键)。即,索引键包含了slect,where,join中的字段。
利弊:
-
好处
- 避免回表
-
缺点
- 维护开销
3.8.2 索引条件下推
定义:索引下推(ICP)是一种将部分过滤条件直接下推给存储引擎处理的优化技术。
使用场景:联合索引。如(A,B,C)中,where A=10 AND B LIKE ‘%DD’ AND C LIKE ‘%DD%’,不开启索引下推,只会用到联合索引中的A。而,开了后会都用到。
开启/关闭:
SET optimizer_switch = 'index_condition_pushdown=[off|on]';
使用条件:
-
如果表访问的类型为range、ref、eq_ref和ref_or_null可以使用lCP
-
ICP可以用于InnoDB和MyISAM表,包括分区表InnoDB和MyISAM表
-
对于InnoDB表,ICP仅用于二级索引。ICP的目标是减少全行读取次数,从而减少I/O操作。
-
当SQL使用覆盖索引时,不支持ICP。因为这种情况下使用ICP不会减少/O。
-
相关子查询的条件不能使用ICP
3.8.3 IN和EXISTS
选择标准:如果主查询的表较大,则采用IN。否则,采用EXISTS。
3.8.4 COUNT(*)和count(字段)
说明:
- MyISAM为O(1),InnoDB为O(n)
- InnoDB中,count(*)会自动选择占用空间小的二级索引。count(字段)不会,要人为操作。
问题如何判别使用的索引键是什么?
索引下推是什么?
3.8.5 select *
建议:尽量不要写。
3.8.6 LIMIT 1
建议:确定结果只有1条,速度会加快。唯一索引可以不用。
3.8.7 commit
建议:多commit,可以释放内存空间。
3.9 主键设计
-
非核心业务:自增主键
-
核心业务:全局唯一且单调递增。设计方法为UUID19。
4 数据库设计
4.1 范式
相关概念:
名称 | 解释 |
---|---|
超键 | 唯一标识元组的属性集。如一个元组的全部属性构成的属性集也是一个超键。 |
候选键 | 唯一标识元组的最小属性集。是不包含多余属性的超键。 |
主键 | 从候选键中选一个就是。 |
外键 | 其他表的主键 |
主属性 | 任一候选键的属性 |
非主属性 | 不在任意候选键中的属性 |
改造方法:分表
优缺点:
- 查询效率低
- 数据冗余低
4.1.1 第一范式
定义:每个字段有原子性。即每个元组的每个字段只能是一个值,而不是一个集合。
4.1.2 第二范式
定义:非主键属性完全函数依赖于主键+1NF。即,非主键属性,不能有主键属性的子集决定。
4.1.3 第三范式
定义:2NF+非候选键属性与候选键之间不存在传递函数依赖。一般到3NF就足够了。
4.1.4 BC NF
定义: 3NF+非候选键属性完全函数依赖于候选码。2NF中的主键变为了候选键。
4.1.5 反范式化
原因:范式越高,表越多,查询效率越低。
方法:增加冗余字段
使用场景:某个字段频繁访问
存在的问题:
- 冗余字段同步修改
- 存储空间变大
4.2 数据表设计原则
- 数据表个数少,字段少
- 联合主键字段个数少
- 使用主键和外键(外键关系,不是外键约束)越多越好
5 其他优化方法
5.1 数据库优化步骤
- DBMS选择
- 表设计优化
- 查询优化
- 索引优化
- redis缓存
- 库级优化:分布式处理,分库分表
5.2 优化数据库服务器
5.2.1 硬件优化
- 大容量内存
- 多处理器
- 高速磁盘
- 分布式存储
5.2.2 参数优化
- innodb_buffer_pool_size:InnoDB类型的表和索引的最大缓存。
- key_buffer_size:表示索引缓冲区的大小。值太大,就会导致操作系统频繁换页。
- table_cache:表示同时打开的表的个数。不是越大越好,同时时打开的表太多会影响操作系统性能。
- query_cache_type的值是0时,所有的查询都不使用查询缓存区但是query_cache_type=0并不会导致MySQL释放query_cache_size所配置的缓存区内存。
- sort_buffer_size:表示每个需要进行排序的线程分配的缓冲区的大小。增加这个参数的值可以提高ORDER BY或GR0UP BY操作的速度。
- join_buffer_size:表示联合查询操作所能使用的缓冲区大小
- read_buffer_size:表示每个线程连续扫描时为扫描的每个表分配的缓冲区的大小(字节)。
5.3 优化数据库结构
5.3.1 拆分表
将访问频率高的字段从表中拆分出去,单独存储。
5.3.2 增加中间表
经常需要两个表的字段组合起来的数据,则为两个表的字段单独再建一个表。
5.3.3 增加冗余字段
某个属性经常被访问,比如计算学生的总成绩,单独成一个字段。
5.3.4 优化数据类型
字段的类型只需要设为满足该字段的占用空间最小的数据类型即可。
5.3.5 优化插入记录速度
MyISAM:
- 禁用索引
- 禁用唯一性检查
- 批量插入
- LOAD DATA INFILE
InnoDB:
- 禁用唯一性检查
- 禁用外键检查
- 禁止自动提交:插入语句全部执行完毕后,一起提交。
5.3.6 使用非空约束
- compact格式要存null信息
- not null运算无法使用索引
5.3.7 分析表、检查表、优化表
分析表:更新cardinality(字段去重后的值的个数)
分析表加个只读锁。分析InnoDB和MyISAM类型的表,但是不能作用于视图。
analyse table xxx;
检查表:检查表是否存在错误
加只读锁。可以检查试图。
check table xxx;
优化表:
整理磁盘空间。
optimize table xxx;
5.4 大表优化
限制查询范围
读写分离:分布式
垂直拆分
- 垂直分库:表比较多,将关联的表放在一个数据库中,其他的梵高另一个库。
- 垂直分表:字段比较多。拆分表。
水平拆分
- 水平分表:元组比较多,建立一个一模一样的表,将元组分出去一部分。又称数据分片。
索引机制和操作系统中的分页机制类似。innodb中record_type记录是数据项还是目录项。 ↩︎
类似于多级页表。由一级目录,二级目录,…等. ↩︎
不是所有索引都被一个引擎支持。 ↩︎
唯一性索引建立后,就会有唯一性约束。联合索引(col1,col2,col3,…),查找时会按照col1,col2,…的顺序。如果没有col1,就不会触发索引。 ↩︎
当要插入和删除很多数据时,要维护索引很费力。所以,先删除索引,等到操作完毕后添加索引。 ↩︎
列的基数为投影和去重后该列集合中元素的个数。列的基数越大,散列度越高。 ↩︎
优化器会将SQL优化重写,按照重写后的来。 ↩︎
总体来说就是,唯一索引>普通索引,非空约束>无非空约束,普通查询>连接>子查询,索引合并>索引覆盖,全表扫描最差。 ↩︎
驱动表是指多表连接时首先被访问的表,被驱动表时后被访问的表。被驱动表一般是被连接的表。 ↩︎
当查询的WHERE子句中有多个条件,且每个条件都可以使用不同的索引时,MySQL会尝试将这些索引的结果合并,以得到最终的结果集。索引合并的类型主要有三种:交集合并(and连接)、并集合并(or连接)和排序并集合并(对结果进行排序,并且排序的字段也有索引) ↩︎
联合索引包含了WHERE子句和SELECT子句中所有的字段。 ↩︎
where中直接使用索引情况:等值和范围匹配,in和or ,前缀匹配的like,遵循最左前缀规则的联合索引,and连接非索引字段,不存在不等判断。 ↩︎
无法使用索引的情况不是绝对的,要综合考虑成本。比如是否需要回表,表有多大等。 ↩︎
后方字段失效:1. 前面的为范围查询 2. 不为前缀。如 (A,B,C)情况1为A为范围查询,则B,C失效 ;情况2为 查(A,C)则B,C失效 ↩︎
not in 和not exists 可以换为left join xx on xx where xx is null;加快查询速度 ↩︎
优化器选择时间开销小的表作为被驱动表,开销大的作为驱动表。explain中驱动表排在前面。 ↩︎
-
- 联合索引和order by的字段顺序全部相反,效果和全部相同一致。2. where字段索引和order by索引优先where 索引,where没有则order by,二者也可以建立联合索引,更快。3.当范围索引和排序索引二选一时,如果范围筛选后,数据很少,那么选范围索引。反之,选排序索引。
UUID为时间(从低位到高位:秒分时这种)+UUID版本+时钟序列+mac地址。字符串存储,占36字节。结构可以改。 ↩︎