Skip to content

Mysql优化

设计方面

  • 开启慢查询日志
  • 使用索引(好处是大大减少了服务器需要扫描的数量 帮助服务器避免排序和临时表 将随机的IO变成顺序的IO 需要避免重复索引 冗余索引 冗余索引的检测工具是pt-duplicate-key-checker )
  • 分库分区分表
  • 合理的设计类型(性别使用枚举、ip使用bigint)
  • 尽量避免空值
  • 适当的将数据库的结构优化(表的范式化与反范式化 反范式是为了查询效率的目的把符合三范式的表适当的增加冗余)、尽量避免子查询(使用join查询关联建是否有一对多的关系，要特别注意重复数据、)
  • 擅长使用limit反之select轮训整个数据库、优化like语句的查询(可以使用MyISAM的全文索引 全文索引有个弊端 就是被检索的词只能支持英文且被检索的词需要非文本隔开 这样abcd,efg,hijklmn"中检索"hi"，那么全文检索也没有用，如果你检索efg，那么可以使用全文检索 建议数据量庞大时推荐使用工具)
  • 表的垂直拆分(就是把原来的一个有很多列的表拆分成多个表，这个解决了表的宽度问题。通常垂直拆分可以按以下的原则进行 注1)
  • 表的水平拆分(当表的数据比较多的时候，可以选择将表进行水平拆分，水平拆分的本质并没有改变表的结构仅仅是将原本存放在同一个表中的数据放到了多个结构一样的表中 注2)
  • 系统配置优化(innodb log缓冲的大小,由于日志最长每秒钟就会刷新所以一般不用太大、MySQL的配置文件innodb_flush_log_at_trx_commit关键参数，对IO效率影响很大。默认值为1 可以取0 1 2三个值，一般建议设为2，但如果数据安全性要求比较高 建议使用默认值1、Innodb_read_io_threads / Innodb_write_io_threads这两个参数决定了Innodb读写的IO进程数,默认为4、innodb_stats_on_metada决定了MySQL在什么情况下会刷新innodb表的统计信息 《==配置MySQL的配置文件使用工具更方便)、

  • 服务器硬件优化：MySQL5.5使用的服务器不要超过32核、主从复制、负载均衡、

  • 注1：把不常用的字段单独存放到一个表中。

    1. 把大字段独立存放到一个表中
    2. 把经常一起使用的字段放到一起

  • 注2：对customer_id进行hash预算，如果拆分成5个表则使用mod(customer_id,5)取出0-4个值

    1. 针对不同的hashID把数据存到不同的表中
    2. 挑战：跨分区表进行数据查询、统计及后台报表操作

MySQL数据库优化可以从哪几个方面进行？

  1. 在项目中，优化mysql之前首先要开启慢查询日志，再分析慢查询日志

    • mysql> show variables like '%quer%';
    • 注：可以通过SHOW VARIABLES语句查看系统变量及其值

    • linux启用慢日志位置 

      vim /etc/my.cnf 
[mysqld]
slow-query-log = on # 开启慢查询功能
slow_query_log_file = /usr/lcoal/mysql/data/slow-query.log # 慢查询日志存放的路径与名称
long_query_time = 5 #查询时间超过5s的查询语句
log-queries-not-using-indexes = on  #列出美与使用索引的查询语句

    • 如何发现有问题的SQL？

      • 使用MySQL慢查询日志对有效率问题的SQL进行监控 
        mysql> show variables like 'slow_query_log';
mysql> set global slow_query_log_file = '/home/mysql/sql_log/mysql-slow.log'
mysql> set global log_queries_not_using_indexs = on;
mysql> set global long_query_time = 1

    • 如何分析sql查询 

      Using filesort:看到这个的时候，查询就需要优化了。
possible_keys:显示可能应用在这张表中的索引。如果为空，没有可能的索引。
keyjen:使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref:显示索引的哪一列被使用了，如果可能的话，是一个常数

    • distinct简单来说就是用来去重的，而group by的设计目的则是用来聚合统计的，两者在能够实现的功能上有些相同之处，但应该仔细区分，因为用错场景的话，效率相差可以倍计。

    • 单纯的去重操作使用distinct，速度是快于group by的。

  2. 子查询的优化 通常情况下把子查询优化为join查询，但在优化的时候需要注意关联建是否有一对多的关系，要特别注意重复数据

  3. GROUP BY的优化

  4. SQL查询优先级 from > where > group by > having > order by