Mysql优化篇（表结构、查询语句、数据量大三方面）

最新推荐文章于 2024-07-26 14:20:58 发布

Colins~

最新推荐文章于 2024-07-26 14:20:58 发布

阅读量426

点赞数 1

分类专栏： mysql 文章标签：数据库分布式 mysql 索引

本文链接：https://blog.csdn.net/weixin_44102992/article/details/106905298

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

文章目录

表结构优化
查询语句优化
定位SQL语句的性能问题
分库分表
读写分离

关于Mysql的优化网上有很多文章，这篇也是参考了 MySQL数据库面试题（2020最新版）.为什么还要写出来呢？主要是记录一下自己的学习经历，同时是作为自己的一份学习笔记。我将优化分成了三部分：
1.表结构优化
2.查询语句优化
3.大数据优化（分库分表、读写分离）

不够完善的地方，日后慢慢完善补充！

表结构优化

字段类型优化（针对内存优化）：就是定义的字段类型和长度不要过大，如能用int型的尽量不要用bigint；还有关于char和varchar的妥善使用，区别见下：

varchar与char的区别

char的特点
1.char表示定长字符串，长度是固定的；
2.如果插入数据的长度小于char的固定长度时，则用空格填充；
3.因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
4.对于char来说，最多能存放的字符个数为255，和编码无关

varchar的特点

1.varchar表示可变长字符串，长度是可变的； 插入的数据是多长，就按照多长来存储；
2.varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
3.对于varchar来说，最多能存放的字符个数为65532

总之，结合性能角度（char更快）和节省磁盘空间角度（varchar更小），具体情况还需具体来设计数据库才是妥当的做法。

2.表字段避免null值出现，null值很难查询优化且占用额外的索引空间，推荐使用默认值代替null。
3.单表不要有太多字段，建议在20左右，过多的可以根据需求拆分
4.根据业务需求和查询语句创建相应索引，主要是根据查询语句来，如在 where、order by、group by之后的字段上添加（亲身经历，一个有几千万条数据的表，添加索引前查询需要20s以上超时，添加后1s以内，还未优化查询语句，只是单单添加了索引）

1.主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
2.唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
	可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
	可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
3.普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。
	可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
	可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引
4.全文索引： 是目前搜索引擎使用的一种关键技术。
	可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

查询语句优化

1.避免select * from table 查询所有字段数据，最好根据需求将需要查找的字段列出来

将
SELECT * FROM pc_user WHERE uid=1
优化为
SELECT username,password FROM pc_user WHERE uid=1

2.根据需求使用limit对查询结果的记录条数进行限定，如我只需要查询出符合条件的1条数据，那么多余的就不需要查询了

将
SELECT username,password FROM pc_user 
改为
SELECT username,password FROM pc_user LIMIT 1

3.避免使用select count(*) from table查询条数；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。

将
SELECT COUNT(*) FROM pc_user
改为
SELECT COUNT(uid) FROM pc_user

4.OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内

将
SELECT username,password FROM pc_user WHERE uid=1 OR uid=56 OR uid=24
改为
SELECT username,password FROM pc_user WHERE uid IN (1,24,56)

5.对于连续数值，使用BETWEEN不用IN,因为in会导致全表扫描；

将
SELECT username,password FROM pc_user WHERE uid IN (1,2,3,4,5)
改为
SELECT username,password FROM pc_user WHERE uid BETWEEN 1 and 5

6.根据需求选择采用UNION还是UNION ALL；区别如下

1.UNION会自动压缩多个结果集的重复结果，就是会去除掉重复的结果
2.UNION ALL则是会查出所有的结果，不会去除掉重复项

效率上讲：UNION ALL更优

7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

将
select id from t where num/2=100
改为:
select id from t where num=100*2

8.模糊查询也将导致全表扫描：select id from t where name like ‘%李%’若要提高效率，可以考虑全文检索。

将
select id from t where name like ‘%李%’
改为
select id from t where name like ‘李%’（走索引）

9.索引并不是越多越好

索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率， 
因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
 一个表的索引数较好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

10.按需求选择使用drop、delete与truncate删除
在这里插入图片描述
11.应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
12.尽量少使用函数和触发器，最好应用程序中实现。
13.一条sql语句尽可能简单，因为一条sql只能在一个cpu运算，一条大sql可以堵死整个库；所以最好将大语句拆分成小语句，减少锁时间；
14.在业务需求上多变关联查询是常用的，但是在效率上讲应该尽量避免使用JOIN，可以将一个关联查询分成多步骤的单表查询。

总结对于查询慢的地方可以从以下几方面入手：
1.是否添加相应索引
2.是否全表扫描
3.是否查询了多余数据
4.SQL语句是否过大
5.查看慢日志

具体可以看下面的性能定位。

定位SQL语句的性能问题

以下部分复制于MySQL数据库面试题（2020最新版）.

对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划，MySQL提供了explain命令来查看语句的执行计划。我们知道，不管是哪种数据库，或者是哪种数据库引擎，在对一条SQL语句进行执行的过程中都会做很多相关的优化，对于查询语句，最重要的优化方式就是使用索引。而执行计划，就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。
在这里插入图片描述
执行计划包含的信息 id 有一组数字组成。表示一个查询中各个子查询的执行顺序;

id相同执行顺序由上至下。
id不同，id值越大优先级越高，越先被执行。
id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中。

select_type 每个子查询的查询类型，一些常见的查询类型。
在这里插入图片描述
table 查询的数据表，当从衍生表中查数据时会显示 x 表示对应的执行计划id partitions 表分区、表创建的时候可以指定通过那个列进行表分区。举个例子：

create table tmp (
    id int unsigned not null AUTO_INCREMENT,
    name varchar(255),
    PRIMARY KEY (id)
) engine = innodb
partition by key (id) partitions 5;

type(非常重要，可以看到有没有走索引) 访问类型

ALL 扫描全表数据
index 遍历索引
range 索引范围查找
index_subquery 在子查询中使用 ref
unique_subquery 在子查询中使用 eq_ref
ref_or_null 对Null进行索引的优化的 ref
fulltext 使用全文索引
ref 使用非唯一索引查找数据
eq_ref 在join查询中使用PRIMARY KEYorUNIQUE NOT NULL索引关联。

possible_keys :可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。
key: 显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。
TIPS:查询中若使用了覆盖索引(覆盖索引：索引的数据覆盖了需要查询的所有数据)，则该索引仅出现在key列表中
key_length: 索引长度
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
rows: 返回估算的结果集数目，并不是一个准确的值。
extra :的信息非常丰富，常见的有：
1.Using index 使用覆盖索引
2.Using where 使用了用where子句来过滤结果集
3.Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。
4.Using temporary 使用了临时表 sql优化的目标可以参考阿里开发手册

【推荐】SQL性能优化的目标：至少要达到 range 级别，要求是ref级别，如果可以是consts最好。 
说明： 
1） consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。 
2） ref 指的是使用普通的索引（normal index）。 
3） range 对索引进行范围检索。 
反例：explain表的结果，type=index，索引物理文件全扫描，速度非常慢，这个index级别比较range还低，与全表扫描是小巫见大巫。

分库分表

垂直拆分

垂直分库：解决单库表过多的问题，例如单库中有商品主表以及相关的小表、有文章主表以及相关的小表，这就可以拆分为商品库和文章库。一般根据业务拆分，达到专库专用
在这里插入图片描述
垂直分表：解决单表列过多问题，例如：一个表内又有用户信息，又有用户账号信息，那么就可以拆分为用户信息表和用户账号信息表。
一般的拆分原则有：

把不常用字段放一张表，常用字段放一张表
把表中大字段的提取放在一张附表，如text等大字段

水平拆分

水平分表：解决单表数据量过大的问题，我们知道在Mysql中单表数据量超过1000w后性能会逐步降低，所以可以将单表数据水平拆分为多表存储，如一个订单表可以拆分为订单表1，订单表2，订单表3

拆分策略：
1.ID拆分：根据ID分段拆分、ID取余拆分。
2.日期拆分：根据时间范围拆分。
3.客户端代理：拆分逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
4.中间件代理：在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

拆分后的问题

分布式事务问题：分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

跨分片数据排序分页问题：一般来讲，分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候，我们通过分片规则可以比较容易定位到指定的分片，而当排序字段非分片字段的时候，情况就会变得比较复杂了

唯一主键问题：一旦数据库被切分到多个物理结点上，我们将不能再依赖数据库自身的主键生成机制。一方面，某个分区数据库自生成的ID无法保证在全局上是唯一的；另一方面，应用程序在插入数据之前需要先获得ID,以便进行SQL路由。
使用UUID 但性能较低。
使用Twitter的分布式自增ID算法Snowflake

跨库join问题：只要是进行切分，跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据

读写分离

为什么要读写分离？因为大多数业务都是读多写少，而读的效率远远高于写的效率，为了防止写的时候影响读的效率，从而进行读写分离。
读写分离是依赖于主从复制，而主从复制又是为读写分离服务的。因为主从复制要求从数据库不能写只能读。

主从复制作用

主数据库出现问题，可以切换到从数据库。
可以进行数据库层面的读写分离。
可以在从数据库上进行日常备份

主从复制原理

在主库上把数据更改记录到二进制日志
从库将主库的日志复制到自己的中继日志
从库读取中继日志的事件，将其重放到从库数据中

主从复制基本流程

1.主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
2.从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中；
3.从：sql执行线程——执行relay log中的语句；在这里插入图片描述
详细流程：
1.master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务
2.slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志
3.SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致

主从同步的延迟问题

主从同步延迟是怎么产生的
1.当master库tps比较高的时候，产生的DDL数量超过slave一个sql线程所能承受的范围，或者slave的大型query语句产生锁等待
2.网络传输： bin文件的传输延迟
3.磁盘的读写耗时：文件通知更新、磁盘读取延迟、磁盘写入延迟
解决方案
1.在数据库和应用层增加缓存处理，优先从缓存中读取数据
2.减少slave同步延迟，可以修改slave库sync_binlog属性；
sync_binlog=0 文件系统来调度把binlog_cache刷新到磁盘
sync_binlog=n