如何系统优化MySQL【表结构优化、索引优化】（上篇）-CSDN博客

本文链接：https://blog.csdn.net/lygcsdn_/article/details/145249993

🍰 个人主页:不摆烂的小劉
🍞文章有不合理的地方请各位大佬指正。
🍉文章不定期持续更新，如果我的文章对你有帮助➡️ 关注🙏🏻 点赞👍 收藏⭐️

在这里插入图片描述

前言：MySQL性能优化是一个复杂过程，通常从发现问题开始。首先，通过监控和分析慢查询日志，找出执行时间较长的 SQL 语句（慢 SQL），针对慢查询，通常从三个方面入手：表结构优化、索引优化、SQL 优化。

如何系统地优化MySQL？优化是否有效？表结构、索引优化有什么注意点？

文章目录

一、前置知识：

1.`Explain`执行计划

用于分析查询语句执行计划的命令，Explain可以查看到MySQL如何选择表、索引、连接方式等细节信息。
有几个需要重点关注的执行计划字段信息：

type：MySQL查找数据的方式，按效率从好到差的顺序排列：

const：常量，表示仅查找一行数据
eq_ref：通过主键或者唯一索引查找数据
ref：通过非唯一索引查找数据
range：索引范围查找
index：全索引扫描
all：全表扫描，效率最差

key：MySQL 实际使用的索引。如果该列为null，表示没有使用索引，可能是全表扫描

rows：MySQL预计需要扫描的行数

Extra：显示额外的执行信息，包括：

Using index：表示查询是通过覆盖索引进行的，避免了回表查询，效率较高。
Using where：表示查询需要通过where 条件过滤数据。一般在没有使用到索引的时候会出现。
Using temporary：表示使用了临时表，通常出现在复杂的join或 group by查询中，可能导致性能下降。
Using filesort：表示需要额外的排序操作，这可能会导致性能下
Using index condition：表示查询优化器选择使用了索引条件下推这个特性
Index Merge：索引合并当一个查询涉及多个索引时，MySQL通过结合多个索引来执行查询。

2.如何查看一条`Sql`执行效率高低？

响应时间
执行到结束花费的时间。时间越短越好。
扫描的行数、返回的行数
理想情况下扫描的行数和返回的行数应该是相同的。
可以通过Explain查看

通过Explain查看，上面的Sql扫描的行数14条，type是all类型全表扫描,额外的执行信息Using where，返回的行数1条，查询效率较低

3.回表

举例

去图书馆，翻阅书单（索引），找到你想借的书编号123
根据书单（索引），你找到这本书的大致位置（行号）
然后你需要回到书架（表）上去，拿到书的实际内容

回表就是通过索引找到了某个数据的位置，但如果需要的字段不在索引里，还得返回原始数据表去查找缺失的字段

二、如何优化

1. 表结构优化

表名推荐使用业务前缀
选择一个合适的字段类型和表示数据的最小数据类型。更小的数据类型通常更快，因为它们占用的磁盘、内存和CPU缓存的空间更少。
禁止使用MySQL关键字order、range、match、desc等
主键索引名为 pk_字段名；唯一索引名为 uk_字段名；普通索引名则为 idx_字段名
小数类型为 decimal，在一些大容量的场景，可以考虑使用bigint代替decimal，将需要存储的货币单位根据小数的位数乘以相应的倍数
如果存储的字符串长度几乎相等，使用char定长字符串类型。当存储char值时,MySQL删除所有尾随空格
varchar需要额外使用记录字符串的长度。MySQL在排序时，对每一个排序记录都会分配一个足够长的定长空间来存放。这个定长空间必须足够长才能容纳其中最长的字符串，例如，如果是varchar列，则需要分配该字段完整长度，避免varchar设置过大，排序时过度消耗临时空间而降低效率
varchar字段长度不要超过5000，如果存储长度大于此值，定义字段类型为text，独立出来一张表，用主键来对应
尽量避免存储null，null值，需要占用额外空间；null值可能会导致索引失效；null值只能用is null或者is not null判断
为整数类型指定宽度，基本没意义，对于存储和计算来说，int（1）和int（20）是相同的
避免使用 enum 类型，enum底层存储的是整数,内部整数值排序的
不要用字符串存储日期，优先推荐使用datetime类型保存日期和时间，可以保存的时间范围更大一些
推荐表中添加自增主键
json数据类型将使用更多空间来存储用于定义json的额外字符（大括号、方括号、冒号等）以及空格。除非特殊业务场景json发挥便捷性，一般不推荐使用。
存储IP地址使用MySQL提供的INET_ATON()和INET_NTOA()函数；INET_ATON()把 IP 转为无符号整型 (4-8 位)，INET_NTOA() 把整型的 IP 转为地址
文件（比如图片）这数据通常存储于文件服务器或云存储，数据库只存储文件地址信息

2.索引设计

索引最主要作用是快速的找到数据位置，这不是唯一作用，覆盖索引还可以存储数据信息

主键索引：不可为空，不重复，只能有一个主键索引，一般是聚集索引，即数据按主键顺序存储。
普通索引：加快查询
唯一索引: 唯一索引保证索引列的值是唯一的
联合索引:多列创建的索引, 查询时，通常会按索引的列顺序使用
覆盖索引: 查询时所需的所有数据都可以通过索引来提供，避免了回表操作。
前缀索引: 索引列的部分值,常在varchar或text类型的列上创建
全文索引：全文索引是一种特殊类型的索引，主要用于文本搜索。它允许对长文本字段（如 text类型的列），一般不使用

在varchar字段上建立索引时，指定索引长度，没必要对全字段建立索引,使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定
避免在多列上独立地创建多个单列索引，复合索引通常比单列索引更加高效，一个表中有 a,b,c，建立索引（a,b,c）复合索引包含了（a）(a,b)(a,b,c)三个索引
避免重复索引,设置某个字段id既是主键索引，又设置该字段id唯一索引
建议在一个表中创建的索引数量不超过5个，修改数据时，数据库需要更新索引，会增加写操作的时间，增删改操作性能下降；每个索引都会占用额外的存储空间，多个索引会导致数据库需要占用大量的磁盘空间；多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间
推荐建立索引字段：最频繁使用的列放在联合索引的左侧；where后的列 order by 、group by、distinct中的字段;join的关联列
覆盖索引，包含了查询所需要的所有列的索引，假设有一个表users，包含 id、name 和 age三个字段，并且你创建了一个索引idx_name_age，该索引包含了name 和 age 两个字段。select name, age from users where name = 'John'; 数据库就可以直接从这个索引中获取查询所需的所有数据，避免回表操作

三、索引失效

索引失效：查询语句的某些操作使得数据库无法利用已有的索引来加速查询

使用like进行前缀匹配时，like '%xxx'用不到索引，需要扫描整个表来匹配；%开头会使得索引失效。避免使用%开头的like模糊匹配
多个条件通过or连接，当or右的条件包含不同列时，索引不能覆盖所有条件，所以尽量避免尽量避免使用or
索引列进行了计算、函数、类型转换等操作，索引是基于原始列的值构建的，函数等操作需要先计算再查询，导致索引不能使用
索引列的数据类型和查询条件类型不匹配，例如索引列是int类型，而查询条件是字符串类型时,可能会失效，即使走索引但类型转换会有额外开销
使用不等于。当进行相等 =、范围 <, >, <=, >=查询时，B+树可以通过查找和范围扫描定位，但使用不等于会全表扫描
使用between索引失效，between 本质上是一个范围查询，在复合索引(a, b)上，如果查询条件是a = 1 and b between 10 and 20，那么B+树能够利用索引。但如果查询条件是 b between 10 and 20，则无法利用复合索引 (a, b) 来加速查询，必须扫描整个 b的范围
in中包含了过多的值，MySQL处理大量的in值时，可能需要对每个值进行比较，这可能导致索引失效