Mysql的那些事

爱学习的Chloe
196 阅读10分钟

sql优化

找出慢sql

MySQL 慢查询的相关参数解释：

slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。

log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log

slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log

long_query_time ：慢查询阈值(默认10s)，当查询时间多于设定的阈值时，记录日志。

log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。

log_output：日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。
默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启，如下所示：

mysql> show variables  like '%slow_query_log%';
+---------------------+-----------------------------------------------+
| Variable_name       | Value                                         |
+---------------------+-----------------------------------------------+
| slow_query_log      | OFF                                           |
| slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |
+---------------------+-----------------------------------------------+
2 rows in set (0.00 sec)
 
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.09 sec)
 
mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------------------+
| Variable_name       | Value                                         |
+---------------------+-----------------------------------------------+
| slow_query_log      | ON                                            |
| slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |
+---------------------+-----------------------------------------------+
2 rows in set (0.00 sec)
 
mysql>

mysql日志分析工具：mysqldumpslow、mysqlsla

sql优化常用手段

建立索引原则

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可，当然要考虑原有数据和线上使用情况 juejin.cn/post/684490…

索引失效的情况

  • 以%开头的like查询
  • (not , not in, not like, <>, !=,!>,!<) 不会使用索引
  • 如果条件中有or，即使其中有部分条件带索引也不会使用
  • where 子句里对索引列上有数学运算或者使用函数，用不上索引
  • 索引列的数据类型存在隐形转换则用不上索引。比如字符串，那一定要在条件中将数据使用引号引用起来
    juejin.cn/post/684490…

常见优化场景

www.jianshu.com/p/af48705f2…

读写分离

实现原理

mysql读写分离原理：主从复制
整体上来说，复制有3个步骤：
(1) master将改变记录到二进制日志(binarylog)中（这些记录叫做二进制日志事件，binary log events）；
(2) slave将master的binary log events拷贝到它的中继日志(relay log)；
(3) slave重做中继日志中的事件，将改变反映它自己的数据。
参考 www.cnblogs.com/codehome/p/…

实现方式

实现方式一般是通过中间件来实现，常见的有mysql-proxy，具体配置过程可参考
www.cnblogs.com/phpstudy201…

带来的问题

对实时性要求很高的系统，如订单系统、交易系统，刚写到主库立马就需要读取的情况，这是有可能还未同步到从库，此时可有以下解决方式：

  • 在从库读取数据时，没有读到数据，就去主库进行数据读取。但是这种操作还是将读的压力返还给主库，如果有恶意的攻击，主库就爆了。 一般情况下，通过对数据库访问的API进行封装就能实现这个功能，业务之间没有耦合。
  • 在写了数据后，立马读操作就去访问主库，之后的读操作访问从库，这种业务上会有高度耦合。
  • 根据业务，将重要的业务数据的读写都放在主库，其他的业务进行读写分离。
    参考 www.jianshu.com/p/2d8ff87d0…

分库分表

可分为垂直分表和水平分表，水平分表最为复杂，并会带来很多问题，所以水平分表是最后一道防线了。

垂直分表

垂直分库针对的是一个系统中的不同业务进行拆分，比如用户User一个库，商品Producet一个库，订单Order一个库。 切分后，要放在多个服务器上，而不是一个服务器上。为什么？ 我们想象一下，一个购物网站对外提供服务，会有用户，商品，订单等的CRUD。没拆分之前， 全部都是落到单一的库上的，这会让数据库的单库处理能力成为瓶颈。按垂直分库后，如果还是放在一个数据库服务器上， 随着用户量增大，这会让单个数据库的处理能力成为瓶颈，还有单个服务器的磁盘空间，内存，tps等非常吃紧。 所以我们要拆分到多个服务器上，这样上面的问题都解决了，以后也不会面对单机资源问题。
现代分布式系统基本都是按业务分库了的

水平分表

针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多个库的多张表里面去。

  • 查询分片
    将ID和库的Mapping关系记录在一个单独的库中。
    优点：ID和库的Mapping算法可以随意更改。
    缺点：引入额外的单点。
  • 连续分片

根据特定字段(比如用户ID、订单时间)的范围，值在该区间的，划分到特定节点。

优点：集群扩容后，指定新的范围落在新节点即可，无需进行数据迁移。

缺点：如果按时间划分，数据热点分布不均(历史数冷当前数据热)，导致节点负荷不均。

  • hash取模分片

缺点：扩容后需要迁移数据。

实际比较常用的是hash取模分片，而且使用一致性hash算法可以无需迁移数据，具体应用实例可以参照大众点评的文章

水平分库分表带来的问题

划分维度（sharading key)

假如用户购买了商品,需要将交易记录保存取来，如果按照用户的纬度分表，则每个用户的交易记录都保存在同一表中，所以很快很方便的查找到某用户的 购买情况，但是某商品被购买的情况则很有可能分布在多张表中，查找起来比较麻烦。反之，按照商品维度分表，可以很方便的查找到此商品的购买情况，但要查找到买人的交易记录比较麻烦。
解决方案：记录两份数据，一份按照用户纬度分表，一份按照商品维度分表。【可以参考大众点评的实例】，如果查询条件不包含sharading key，则可以在应用层转化为sharading key

全局id定义

在分库分表环境中，由于表中数据同时存在不同数据库中，主键值平时使用的自增长将无用武之地，某个分区数据库自生成的ID无法保证全局唯一。因此需要单独设计全局主键，以避免跨库主键重复问题。有一些常见的主键生成策略：

  • UUID
    UUID标准形式包含32个16进制数字，分为5段，形式为8-4-4-4-12的36个字符，例如：550e8400-e29b-41d4-a716-446655440000 UUID是主键是最简单的方案，本地生成，性能高，没有网络耗时。但缺点也很明显，由于UUID非常长，会占用大量的存储空间；另外，作为主键建立索引和基于索引进行查询时都会存在性能问题，在InnoDB下，UUID的无序性会引起数据位置频繁变动，导致分页。
  • 设置自增偏移和步长
## 假设总共有 10 个分表
## 级别可选: SESSION(会话级), GLOBAL(全局)
SET @@SESSION.auto_increment_offset = 1; ## 起始值, 分别取值为 1~10
SET @@SESSION.auto_increment_increment = 10; ## 步长增量

如果采用该方案，在扩容时需要迁移已有数据至新的所属分片。

  • redis
    在全局 Redis 中为每张数据表创建一个 ID 的键，记录该表当前最大 ID； 每次申请 ID 时，都自增 1 并返回给应用； Redis 要定期持久至全局数据库。
  • Snowflake(雪花) 算法
  • 类Snowflake(雪花) 算法
    可参考 美团的解决方法

分布式事务

解决方法：二段式提交（rocketmq）,三段式提交，事务补偿

跨库join

切分之前，系统中很多列表和详情页所需的数据可以通过sql join来完成。而切分之后，数据可能分布在不同的节点上，此时join带来的问题就比较麻烦了，考虑到性能，尽量避免使用join查询。

解决这个问题的一些方法：

1）全局表
全局表，也可看做是"数据字典表"，就是系统中所有模块都可能依赖的一些表，为了避免跨库join查询，可以将这类表在每个数据库中都保存一份。这些数据通常很少会进行修改，所以也不担心一致性的问题。

2）字段冗余
一种典型的反范式设计，利用空间换时间，为了性能而避免join查询。例如：订单表保存userId时候，也将userName冗余保存一份，这样查询订单详情时就不需要再去查询"买家user表"了。

但这种方法适用场景也有限，比较适用于依赖字段比较少的情况。而冗余字段的数据一致性也较难保证，就像上面订单表的例子，买家修改了userName后，是否需要在历史订单中同步更新呢？这也要结合实际业务场景进行考虑。

3）数据组装
在系统层面，分两次查询，第一次查询的结果集中找出关联数据id，然后根据id发起第二次请求得到关联数据。最后将获得到的数据进行字段拼装。

已有的分库分表方案

sharding-jdbc（当当）
TSharding（蘑菇街）
Atlas（奇虎360 ）
Cobar（阿里巴巴）
MyCAT（基于Cobar）
Oceanus（58同城）
Vitess（谷歌）

参考文章：
tech.meituan.com/2016/11/18/…
www.cnblogs.com/butterfly10…
zhuanlan.zhihu.com/p/50650224 database.51cto.com/art/201809/…
juejin.cn/post/684490…

avatar
文章
阅读
粉丝