跳至主要內容

MySQL优化

zhangtq2023年8月13日大约 4 分钟技术栈数据库mysql

MySQL优化

优化方向

  • 存储引擎的选择：Innoddb、MyISAM
  • 数据库设计尽量遵循三范式
  • 建立合适的索引
  • 使用explain检查索引命中率，分析这条sql是否可以优化
  • 数据量大的时候，主从分离、分库分表、垂直/水平分割
  • 尽量采用贵的SSD硬盘，不选择机械硬盘
  • 一个和你项目有关数据库优化的例子

MySQL优化

  • 什么是MySQL的慢查询？
  • 如何去分析 慢查询的日志？
  • 遇到了慢查询如何解决？（最好有故事）
  • 索引的建立是越多越好？为什么不是越多越好？
  • 一个精彩的故事

简单说明

MySQL索引优化

# 1、最左前缀匹配原则，上面讲到了
# 2、主键外键一定要建索引
# 3、对 where,on,group by,order by 中出现的列使用索引
# 4、尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，
# 5、为较长的字符串使用前缀索引

'''
# 6、不要过多创建索引, 权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。
    这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，
    会影响插入、删除数据的速度，因为我们修改的表数据，索引也需要进行调整重建
'''

'''
# 7、对于like查询，”%”不要放在前面。
    SELECT * FROMhoudunwangWHEREunameLIKE'后盾%' -- 走索引 
    SELECT * FROMhoudunwangWHEREunameLIKE "%后盾%" -- 不走索引
'''

'''
# 8、查询where条件数据类型不匹配也无法使用索引 
    字符串与数字比较不使用索引; 
    CREATE TABLEa(achar(10)); 
    EXPLAIN SELECT * FROMaWHEREa="1" – 走索引 
    EXPLAIN SELECT * FROM a WHERE a=1 – 不走索引 
    正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因
'''

分库分表

应用场景

  • 传统的单库单表架构在面对互联网业务的巨大数据规模时存在性能瓶颈。其中，以下是一些常见的问题：

    1. 读写性能压力：随着数据量的增加，数据库的读写操作变得越来越慢。处理更大量的数据会导致查询速度下降，对数据库的并发操作也会受到限制。

    2. 存储空间限制：单个数据库的存储容量有限，无法承载巨大的数据量。因此，当数据量超过单个数据库的限制时，无法进一步扩展存储空间，造成存储瓶颈

  • 为了应对这些问题，使用读写分离和分库分表是常见的优化方案。

  • 单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表

分库分表原理

  • 将原本存储于单个数据库上的数据拆分到多个数据库，把原来存储在单张数据表的数据拆分到多张数据表中，实现数据切分，从而提升数据库操作性能

水平切分和垂直切分

  • 垂直拆分一般是按照业务和功能的维度进行拆分，把数据分别放到不同的数据库。
img
img
  • 将相同表结构的数据分散到不同的数据库和数据表中，目的是避免集中访问单个数据库或数据表，提高系统的并发处理能力和性能。

规则

  • 具体的分库和分表规则可以根据业务主键进行哈希取模操作。例如，在电商业务中，可以将订单表进行水平拆分到多个数据库中，每个数据库可以再继续拆分成多个数据表。路由策略可以基于订单ID或用户ID进行取模运算，将订单数据路由到不同的数据库和数据表中。

缺点

分布式事务、跨库查询以及合并排序等是在分库分表后常见的挑战。借助分布式事务中间件和分库分表中间件等工具，可以提供解决方案，并确保数据操作的正确性和性能的可控性。

分库分表中间件实现

  • 比较推荐 Apache ShardingSphere，另外也可以参考淘宝的 TDDL