MySQL优化实战：表结构优化与索引优化完全指南

创作时间:

作者:

@小白创作中心

MySQL优化实战：表结构优化与索引优化完全指南

引用

CSDN

https://m.blog.csdn.net/LYGCSDN_/article/details/145249993

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

前置知识

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通过结合多个索引来执行查询。

如何查看一条SQL执行效率高低？

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

回表

举例：

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

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

表结构优化

表名推荐使用业务前缀
选择一个合适的字段类型和表示数据的最小数据类型。更小的数据类型通常更快，因为它们占用的磁盘、内存和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转为地址
文件（比如图片）这数据通常存储于文件服务器或云存储，数据库只存储文件地址信息

索引设计

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

主键索引：不可为空，不重复，只能有一个主键索引，一般是聚集索引，即数据按主键顺序存储。
普通索引：加快查询
唯一索引: 唯一索引保证索引列的值是唯一的
联合索引:多列创建的索引, 查询时，通常会按索引的列顺序使用
覆盖索引: 查询时所需的所有数据都可以通过索引来提供，避免了回表操作。
前缀索引: 索引列的部分值,常在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值时，可能需要对每个值进行比较，这可能导致索引失效

参考：