MySQL存储结构与优化策略

Eric~~

于 2024-07-17 18:03:16 发布

阅读量868

点赞数 11

文章标签： mysql 数据库性能优化

本文链接：https://blog.csdn.net/weixin_44361941/article/details/140501232

版权

MySQL

1. 基础篇

mysql的优势

开源，可承载大数据量，多系统安装支持多语言

三个范式

第一范式：数据库字段单一属性不可再分
第二范式：在第一范式的基础上，非主键列必须完全依赖主键，而非主键的一部分
第三范式：在第二范式的基础上，非主键列只依赖主键，不能依赖其他列

事务的四大特征（ACID）

原子性：事务操作要么全部成功，要么全部回滚
一致性：总是从一个一致性状态转为另一个一致性状态
隔离性：多个事务之间不受彼此影响
持久性：已经被提交的事物对数据库的修改应该永久保存在数据库中

事务隔离级别

READ UNCOMMITTED（读取未提交）：
最低隔离级别，允许读取未提交的数据变更，会引起脏读，幻读和不可重复读
READ COMMITTED（读取已提交）：
允许读取并发事务已提交的数据，可以阻止脏读，但不能阻止幻读和不可重复读
REPEATABLE READ（可重复读）：
对同一字段的多次读取结果都是一致的，除非数据被本身事务所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生
SERIALIZABLE（可串行化）：
最高隔离级别，完全服从ACID的隔离级别。所有事物依次执行，这样事务之间就不会产生干扰，可以防止脏读，幻读与不可重复读。

MYSQL默认：REPEATABLE READ
ORACLE默认：READ COMMITTED

脏读，幻读，不可重复读

脏读：当一个事务读取某个字段并修改，但未提交至数据库时已经被另一事务读取并使用了修改后的值。可设置隔离级别为读取已提交来避免脏读
不可重复读：一个事务两次读取数据库之间，被另一事务修改并提交，导致同一事务两次读取的值不相同。设置隔离级别可重复读
幻读：事务A依据条件查询结果集N条，但由于事务B的操作导致结果集增加或减少，A后续再次操作时发现结果集又变化。侧重于结果集条数或数据是否存在此类情况的变化。

存储引擎如何选择

如果没有特殊需求，默认使用InnoDB即可
InnoDB引擎由于其对事务参照完整性，以及更高的并发性等优点已经逐步取代MyISAM

MyISAM：
适用于以读写插入为主的应用程序
InnoDB：
- 插入缓冲（insert buffer)
- 二次写(double write)
- 自适应哈希索引(ahi)
- 预读(read ahead)

2. MySQL存储引擎与索引

MySQL架构

连接器->缓存层->解释器->优化器->执行器->存储引擎->内存->磁盘

存储引擎 – InnoDB

mysql默认存储引擎为InnoDB，且存储引擎可以指定，存储引擎指定为表级别，同一个数据库中不同表可以有不同的存储引擎，例如对一些只读的表，可以设置为ISMA提升效率
other create table TBL_A( ...... )ENGINE=INNODB;
InnoDB中，因为直接操作磁盘比较慢，所以加了一层内存提速，叫buffer pool，这里面有很多内存页，每一页16KB，有些内存页存放一行行数据，有些存放索引信息。

Buffer Pool与磁盘

查询sql到了InnoDB中，会根据前面优化器里计算的索引，去查询相应的索引页，如果不在buffer pool中则从磁盘中加载。再通过索引页加速查询得到数据页的具体位置。如果这些数据页不在buffer pool中则从磁盘加载，最后将得到的数据返回给客户端。

MySQL数据存储方式

MySql每一行数据以特定格式存储在磁盘页中
MySql获取数据时需要从磁盘读取，IO消耗性能
MySql默认存储引擎InnoDB存储数据时将数据和聚集索引一同存储

索引

1.索引数据结构为B+树，树结构矮胖，一般为2～4层，且根结点常驻内存，所以InnoDB读取数据的IO操作一般为2～3次
2.InnoDB每个表都有一个聚集索引
3.如果存在主键就以主键创建
4. 如果无主键则选择一个非空唯一索引创建
5. 如果都没有则自动创建一个自增列用来创建聚集索引
6. InnoDB数据存储时，将聚集索引和行数据存储在同一个B+树
7. 非叶子结点只存储主键和指针相关信息
8. 叶子结点存储主键，行数据和指针
9. 所以以主键查询时一次树结构遍历即可取到响应数据
10. InnoDB非聚集索引只存储索引和主键数据，先查询主键数据后，再从聚集索引中查询数据，即“回表查询”

3. 慢SQL解决思路

1. 数据库索引设置不合理

索引区分度低（区分度和字段长度互斥，根据实际情况确定长度）
切忌创建过多索引
常用查询/分组/排序/去重字段设置索引
主键和外键创建索引
主键自增与UUID
自增列在数据插入时自动往后追加，UUID每次增加数据时会出现排序，插入的情况，降低性能，因此主键需要使用自增列

2. SQL优化

1. 索引失效的情况

1. 对索引使用函数，计算，<>，!=，not in，not exists，前导模糊查询
2. 对索引进行隐式转换
3. 对索引使用or链接非索引字段
4.  联合索引仅包含复合索引非前置列
5. 索引字段为空可能不会影响索引的使用，但是不建议存在空值
6. 优化建议
7. 关联代替子查询
8.  使用覆盖索引
9. 多表关联时小表在前
10.  where过滤字段，过滤数据多的字段在前
11. 使用小范围事物而非大范围
12.  遵循最左匹配
13. not in不使用索引，not exist效率比not in更高
14. in是将内外标hash关联，exist是对外表循环，所以子表小则使用in，子表大用exist

2. 调优步骤

分析步骤

开启设置
set profiling = on;
正常执行sql
这些SQL语句的执行时间都会被记录下来
查看记录的sql
show profiles
查看某条sql的具体执行过程
步骤3结果中的query_id,通过show profile for query 1;可以查看某一条sql的具体耗时
一般情况下，开发过程中，耗时大部分都在Sending data阶段，这个是指执行器查询数据并将数据发送给客户端的过程，而这一阶段如果慢的话，最容易想到还是索引相关。

索引相关原因

索引相关的原因分析，一般可以使用explain命令帮助分析
一般考虑几个原因：
选择这个索引大概需要扫描多少行（rows）
为了把这些行的数据取出来大概需要读取多少个16KB的内存页
走普通索引需要回表，主键索引不需要，回表成本大不大？

上面的查询分析中显示，type为all表示全表扫描，possible_keys表示可能用到的索引，key表示数据库实际走的索引，此处为null表示此sql不走索引，进行全表扫描
set profiling = on;
show profiles;
show profile for query 1;
explain sql;
查询sql尽量不使用select *而是具体字段
避免在where子句中使用or来连接条件，可以替换为union all
使用or可能会使索引失效，从而全表扫描；
对于or没有索引的条件，假设走了主键索引，但走到无索引的字段条件时，还得全表扫描，即全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定
尽量使用数值类型替换字符串类型
引擎处理查询和连接时会逐个比较字符串中的每一个字符，而对于数字型只需要比较一次就可以
profiling
explain

字段说明：
type：
system：表仅有一行，基本用不到
const：表最多一行数据配合，主键查询时触发较多
eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的连接类型，除了const外。
ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取；
range：质监所给定范围内的行，使用一个索引来选择行。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range。
index：该连接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小；
ALL：全表扫描
性能排名：system>const>eq_ref>ref>range>index>all，实际油画中最后达到ref或range级别。
extra：
using index：只从索引树中获取信息，而不需要回表查询
using where：where子句用于限制哪一个行匹配下一个表或发送到客户。除非专门从表中索取或检查所有行，如果extra值不为using where并且表联结类型为all或index，查询可能有一些错误，需要回表查询。
using temporary：mysql常建一个临时表来容纳结果，典型情况如查询包含可以按照不同情况列出列的group by和order by子句。