如何系统优化MySQL【表结构优化、索引优化】(上篇)

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

在这里插入图片描述

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

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

一、前置知识:

1.Explain执行计划

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

typeMySQL查找数据的方式,按效率从好到差的顺序排列:

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

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

rowsMySQL预计需要扫描的行数

Extra:显示额外的执行信息,包括:

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

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

  • 响应时间
    执行到结束花费的时间。时间越短越好。
  • 扫描的行数、返回的行数
    理想情况下扫描的行数和返回的行数应该是相同的。
    可以通过Explain查看
    在这里插入图片描述
    在这里插入图片描述
    通过Explain查看,上面的Sql扫描的行数14条,typeall类型全表扫描,额外的执行信息Using where,返回的行数1条,查询效率较低

3.回表

举例

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

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

二、如何优化

1. 表结构优化

  • 表名推荐使用业务前缀
  • 选择一个合适的字段类型和表示数据的最小数据类型。更小的数据类型通常更快,因为它们占用的磁盘、内存和CPU缓存的空间更少。
  • 禁止使用MySQL关键字orderrangematchdesc
  • 主键索引名为 pk_字段名;唯一索引名为 uk_字段名;普通索引名则为 idx_字段名
  • 小数类型为 decimal,在一些大容量的场景,可以考虑使用bigint代替decimal,将需要存储的货币单位根据小数的位数乘以相应的倍数
  • 如果存储的字符串长度几乎相等,使用char定长字符串类型。当存储char值时,MySQL删除所有尾随空格
    在这里插入图片描述
  • varchar需要额外使用记录字符串的长度。MySQL在排序时,对每一个排序记录都会分配一个足够长的定长空间来存放。这个定长空间必须足够长才能容纳其中最长的字符串,例如,如果是varchar列,则需要分配该字段完整长度,避免varchar设置过大,排序时过度消耗临时空间而降低效率
  • varchar字段长度不要超过5000,如果存储长度大于此值,定义字段类型为text,独立出来一张表,用主键来对应
  • 尽量避免存储nullnull值,需要占用额外空间;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.索引设计

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

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

参考:
https://dev.MySQL.com/doc/refman/5.7/en/
《阿里巴巴java开发手册》
《高性能MySQL(第四版)》
MySQL高性能优化规范建议总结
美团暑期实习一面:MySQl 索引失效的场景有哪些?
聊聊数据库建表的15个小技巧
技术同学必会的MySQL设计规约,都是惨痛的教训

🍉文章不定期持续更新,如果我的文章对你有帮助 关注🙏🏻 👍点赞👍 收藏⭐️

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值