登录
首页 >  数据库 >  MySQL

Mysql-优化篇

来源:SegmentFault

时间:2023-01-28 17:11:28 101浏览 收藏

积累知识,胜过积蓄金银!毕竟在##column_title##开发的过程中,会遇到各种各样的问题,往往都是一些细节知识点还没有掌握好而导致的,因此基础知识点的积累是很重要的。下面本文《Mysql-优化篇》,就带大家讲解一下MySQL、MySQL优化知识点,若是你对本文感兴趣,或者是想搞懂其中某个知识点,就请你继续往下看吧~

mysql的优化实在太多,这里仅仅列一些常见的,不可能完全概括,后续有新学习的内容会持续更新。

explain 分析select语句

主要字段
select_type查询类型 eg:SIMPLE 简单查询,UNION 联合查询,SUBQUERY 子查询
table 查询的表
partitions:
type 索引查询类型 const:使用主键或者唯一索引进行查询的时候只有一行匹配 ref:使用非唯一索引 range:范围查询 all:扫描全表 index:和all的区别是扫描的是索引树 system表只有一行或空表,const的特例 fulltext:全文索引,优先级很高 等等
possible_keys 可能用到的索引
key 实际使用的索引
key_len 查询用到的索引长度(字节数)
ref 等值查询会显示const
rows 扫描的行数
filtered 表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例
extra :

  • Using index 查询不需要回表,并且where筛选条件是索引的是前导列
  • Using where Using index 查询不需要回表,并且where筛选条件是索引列之一但是不是索引的不是前导列,或者是前导列的一个范围查找
  • NULL 查询需要回表,并且where筛选条件是索引的前导列
  • Using where 查询需要回表,where筛选条件非索引的前导列,或者筛选条件非索引列
    等等

索引优化

(1)建立联合索引,讲选择性强唯一性高的字段放在前面,范围查找字段放在最后
(2)索引覆盖,尽量避免回表
(3)合理利用前缀索引,避免长字段索引过长,占用空间


索引下推ICP

这是mysql5.6版本之后内部对于索引过滤数据做的优化,适用于比如范围查询,模糊查询,因为最左前缀原则,导致联合索引后续索引字段失效。
以前的查询都是在存储引擎层先返回条件字段索引相关的数据,然后直接回表,在server层再对where后失效的索引查询条件进行过滤,在5.6之后,在引擎层会对where索引条件进行筛选,进一步增加了对记录的过滤(用通俗的话就是5.6版本之前联合索引中失效的索引字段会回表再去判断,ICP会让判断条件从服务层下推至存储引擎层,对失效的索引字段在存储引擎层进行判断,进一步筛选数据之后再回表)。此时explain select语句的extra为Using index condition(查找使用了索引,并且需要回表查询数据)。

核心是减少回表次数,将一些需要回表的判断放在存储引擎层进行。


MRR「Multi-Range Read Optimization」

随机磁盘读转换为顺序磁盘读


读写分离

将数据库分为主表和从表,主表作为写表,用于处理数据增删改和数据表操作,而从表作为读表一般会有多个,用于同步主表的修改,并且处理读操作。
主表master和从表slave通过binlog来同步。我们知道,对于主表的修改会写入binlog,此时主表会开启一个binlog dump线程,将修改的binlog发送给从表的IO线程,从表会将binlog写入relay log,之后sql线程会将修改的部分同步到从表。

读写分离带来的主从表数据不一致问题怎么解决?
可以采用 1.从主表读取未同步的数据 2.延迟读 等等方法。


分库分表

分表

  • 垂直分表
    按照字段进行分表,使用频率高的字段放在一张表,使用频率低的放另一张表,同时,两张表都要有相同的主键id。
    垂直分表的意义在于,表的最大尺寸是和字段数相关的,如果垂直拆分字段成不同的表,字段数下降了,表的最大尺寸则变大了,能容纳更多数据。
  • 水平分表
    根据一个规则或字段(分片键)将数据分到不同的表里,保证所有表的字段都相同,数据均匀划分。

分库

  • 业务模块分库
    根据不同的业务模块来分库,将不同业务的数据库操作分隔开来
  • 按表分库
    对于上述水平分表产生的多个子表来说,可以将不同的子表分到不同的数据库中

分库分表也会带来一些问题,比如:

  • 不同库表关联查询问题
    首先设计初需要避免这种情况,如果出现这种情况
    1.设计冗余字段,避免跨库的join
    2.添加全局表,保存一些全局数据等很少修改的数据
  • 分布式事务问题
    基于两阶段提交的XA事务
  • 分布式id问题
    可以利用雪花算法,或者专门的id生成服务解决
  • 数据扩容
    因业务量增加需要增加子表,这个时候需要重新设置分片规则,并且做数据迁移,保证数据均匀分布
  • 跨表排序分页
    分片键作为排序字段,则正常使用排序;反之,则需要在不同子表中分别查询结果并汇总,再次排序
  • ER分片
    对于一些关联表,可以合理设置它们的分片字段,使得相同分片数据的表在同一个数据分片上,避免跨库join。比如员工表和公司表,假设他们以公司id关联,可以以公司id来分片,这样能保证同一个库内,公司表和员工表的公司id都是一样的。

此外,这些问题可以利于一些市面上成熟的中间件来解决,比如ShardingSphere,mycat,Sharding-JDBC,DRDS等等。

好了,本文到此结束,带大家了解了《Mysql-优化篇》,希望本文对你有所帮助!关注golang学习网公众号,给大家分享更多数据库知识!

声明:本文转载于:SegmentFault 如有侵犯,请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>