【摘】MySQL调优

Posted on | In

SQL语句优化

ORDER BY，GROUP BY和DISTINCT优化

  • ORDER BY的实现与优化

    • 优化Query语句中的ORDER BY的时候，尽可能利用已有的索引来避免实际的排序计算，可以很大幅度的提升ORDER BY操作的性能。 优化排序：
      1.加大max_length_for_sort_data参数的设置；
      2.去掉不必要的返回字段；
      3.增大sort_buffer_size参数设置；

  • GROUP BY的实现与优化

    • 由于GROUP BY实际上也同样需要进行排序操作，而且与ORDER BY相比，GROUP BY主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY的实现过程中，与ORDER BY一样也可以利用到索引。

  • DISTINCT的实现与优化

    • DISTINCT实际上和GROUP BY的操作非常相似，只不过是在GROUP BY之后的每组中只取出一条记录而已。所以，DISTINCT的实现和GROUP BY的实现也基本差不多，没有太大的区别。同样可以通过松散索引扫描或者是紧凑索引扫描来实现，当然，在无法仅仅使用索引即能完成DISTINCT的时候，MySQL只能通过临时表来完成。但是，和GROUP BY有一点差别的是，DISTINCT并不需要进行排序。也就是说，在仅仅只是DISTINCT操作的Query如果无法仅仅利用索引完成操作的时候，MySQL会利用临时表来做一次数据的“缓存”，但是不会对临时表中的数据进行filesort操作。

Join语句的优化

…

缓存

Mysql8.0已去掉查询缓存

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。绝大部分场景下启用查询缓存弊大于利，除非你的业务就是有一张静态表，很长时间才会更新一次。

Mysql缓存主要包括关键字缓存（key cache）和查询缓存（query cache）

查询缓存优化

  • 对于一些不常改变的数据且有大量相同sql查询的表，查询缓存会节约很大的性能
  • 如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：
    1.用多个小表代替一个大表，注意不要过度设计
    2.批量插入代替循环单条插入
    3.合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适
    4.可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存
  • show status like ‘%qcache_hits%’;

  • 配置查询缓存

    • query_cache_type可以是0,1,2，0代表不使用缓存，1代表使用缓存，2代表根据需要使用
      query_cache_size
      query_cache_limit
      • query_cache_type=1
        query_cache_size=67108864
        query_cache_limit=67108864

  • MySql中可以在SQL中指定SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存

    • select SQL_NO_CACHE * from … 不走缓存，不查询缓存，也不写入缓存
  • Query Cache 命中率= Qcache_hits / ( Qcache_hits + Qcache_inserts )；

分库分表

垂直，水平分库分表 …

使用redis或memcache

1.获得数据 2.处理数据 3.缓存数据 4.读取缓存数据

Mysql Query Cache没有接口放定制数据（2，3），只能放搜索结果集，只是对1的优化，在具体业务场景里没有可用性；Memcache里可以放任意结构的业务数据；因此程序员可以通过合理地设计，提升cache的使用效率

OLTP（联机事务处理 On-Line Transaction Processing）场景

并发量大，整体数据量比较多，但每次访问的数据比较少，且访问的数据比较离散，活跃数据占总体数据的比例不是太大

OLAP（联机分析处理 On-Line Analytical Processing）场景

数据量非常大，并发访问不多，但每次访问所需要检索的数据量都比较多，而且数据访问相对较为集中，没有太明显的活跃数据概念

并发大，cpu要好
内存要大一些，以缓存更多活跃的数据
磁盘iops要好，吞吐能力其次
网络交互非常频繁

OLTP负责基本业务的正常运转，而业务数据积累时所产生的价值信息则被OLAP不断呈现，企业高层通过参考这些信息会不断调整经营方针，也会促进基础业务的不断优化

事务

隔离级别

  • READ_UNCOMMITTED
  • READ_COMMITED
  • REPEATABLE_READ
  • SERLALIZABLE

特点

  • 原子性
  • 一致性
  • 隔离性
  • 持久性

并发问题

  • 第一类更新
  • 第二类更新
  • 脏读
  • 不可重复读
  • 幻读

1、脏读

所谓脏读，就是指事务A读到了事务B还没有提交的数据，比如银行取钱，事务A开启事务，此时切换到事务B，事务B开启事务–>取走100元，此时切换回事务A，事务A读取的肯定是数据库里面的原始数据，因为事务B取走了100块钱，并没有提交，数据库里面的账务余额肯定还是原始余额，这就是脏读。

2、不可重复读

所谓不可重复读，就是指在一个事务里面读取了两次某个数据，读出来的数据不一致。还是以银行取钱为例，事务A开启事务–>查出银行卡余额为1000元，此时切换到事务B事务B开启事务–>事务B取走100元–>提交，数据库里面余额变为900元，此时切换回事务A，事务A再查一次查出账户余额为900元，这样对事务A而言，在同一个事务内两次读取账户余额数据不一致，这就是不可重复读。

3、幻读

所谓幻读，就是指在一个事务里面的操作中发现了未被操作的数据。比如学生信息，事务A开启事务–>修改所有学生当天签到状况为false，此时切换到事务B，事务B开启事务–>事务B插入了一条学生数据，此时切换回事务A，事务A提交的时候发现了一条自己没有修改过的数据，这就是幻读，就好像发生了幻觉一样。幻读出现的前提是并发的事务中有事务发生了插入、删除操作。

0%