简单了解MYSQL数据库优化阶段

引言

大家在面试的时候，是否遭遇过，面试官询问

你们是如何进行数据库优化的?

那这个问题应该怎么答呢？其实写这个题材的原因是我这几天看到各公众号转的一篇数据库调优的知识（不上链接了），我就稍微翻了几下，上面动不动就来说要对数据库进行水平拆分，我就想反问各位读者，你们几个人经历过水平拆分?现在很多文章，实践性实在太差，只能说纯理论分析。

这篇文章最早来自知乎的一个提问，我在其基础上完善了一下。

第一阶段 优化sql和索引

这才是调优的第一阶段啊，为什么呢？

因为这一步成本最低啊，不需要加什么中间件。你没经过索引优化和SQL优化，就来什么水平拆分，这不是坑人么。

那步骤是什么样呢?我说个大概

(1)用慢查询日志定位执行效率低的SQL语句

(2)用explain分析SQL的执行计划

(3)确定问题，采取相应的优化措施，建立索引啊，等

我就不举例了，因为如何优化SQL的文章，一抓一大把，再贴过来，读者看着也累。

第二阶段 搭建缓存

在优化sql无法解决问题的情况下，才考虑搭建缓存。毕竟你使用缓存的目的，就是将复杂的、耗时的、不常变的执行结果缓存起来，降低数据库的资源消耗。

这里需要注意的是:搭建缓存后，系统的复杂性增加了。你需要考虑很多问题，比如:

缓存和数据库一致性问题？(比如是更缓存，还是删缓存),这点可以看我的一篇文章《数据库和缓存双写一致性方案解析》。
缓存击穿、缓存穿透、缓存雪崩问题如何解决？是否有做缓存预热的必要。不过我猜，大部分中小公司应该都没考虑。

第三阶段 读写分离

缓存也搞不定的情况下，搞主从复制，上读写分离。在应用层，区分读写请求。或者利用现成的中间件mycat或者altas等做读写分离。

需要注意的是,只要你敢说你用了主从架构，有三个问题，你要准备:

(1)主从的好处？

回答:实现数据库备份，实现数据库负载均衡，提交数据库可用性

(2)主从的原理?

回答:如图所示（图片不是自己画的，偷懒了）

主库有一个log dump线程，将binlog传给从库

从库有两个线程，一个I/O线程，一个SQL线程，I/O线程读取主库传过来的binlog内容并写入到relay log,SQL线程从relay log里面读取内容，写入从库的数据库。

(3)如何解决主从一致性?

回答:这个问题，我不建议在数据库层面解决该问题。根据CAP定理，主从架构本来就是一种高可用架构，是无法满足一致性的
哪怕你采用同步复制模式或者半同步复制模式，都是弱一致性，并不是强一致性。所以，推荐还是利用缓存，来解决该问题。

步骤如下:

1、自己通过测试，计算主从延迟时间，建议mysql版本为5.7以后，因为mysql自5.7开始，多线程复制功能比较完善，一般能保证延迟在1s内。不过话说回来，mysql现在都出到8.x了，还有人用5.x的版本么。

2、数据库的写操作，先写数据库，再写cache，但是有效期很短，就比主从延时的时间稍微长一点。

3、读请求的时候，先读缓存，缓存不存在(这时主从同步已经完成)，再读数据库。

第四阶段 利用分区表

说句实在话，你们面试的时候，其实可以略过这个阶段。因为很多互联网公司都不建议用分区表，我自己也不太建议用分区表，采用这个分区表，坑太多。

这里引用一下其他文章的回答:

什么是mysql的分区表？

回答：所有数据还在一个表中，但物理存储根据一定的规则放在不同的文件中。这个是mysql支持的功能，业务代码不需要改动，

但是sql语句需要改动，sql条件需要带上分区的列。

缺点

(1)分区键设计不太灵活，如果不走分区键，很容易出现全表锁

(2)在分区表使用ALTER TABLE … ORDER BY，只能在每个分区内进行order by。

(3)分区表的分区键创建索引，那么这个索引也将被分区。分区键没有全局索引一说。

(4)自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定该去哪个分区查，不太可控。
...不列举了，不推荐

第五阶段 垂直拆分

上面四个阶段都没搞定，就来垂直拆分了。垂直拆分的复杂度还是比水平拆分小的。将你的表，按模块拆分为不同的小表。大家应该都看过《大型网站架构演变之路》，这种类型的文章或者书籍，基本都有提到这一阶段。
如果你有幸能够在什么运营商、银行等公司上班，你会发现他们一个表，几百个字段都是很常见的事情。所以，应该要进行拆分，拆分原则一般是如下三点:

(1)把不常用的字段单独放在一张表。

(2)把常用的字段单独放一张表

(3)经常组合查询的列放在一张表中（联合索引）。

第六阶段 水平拆分

OK,水平拆分是最麻烦的一个阶段，拆分后会有很多的问题，我再强调一次，水平拆分一定是最最最最后的选择。从某种意义上，我觉得还不如垂直拆分。因为你用垂直拆分，分成不同模块后，发现单模块的压力过大，你完全可以给该模块单独做优化，例如提高该模块的机器配置等。如果是水平拆分，拆成两张表，代码需要变动，然后发现两张表还不行，再变代码，再拆成三张表的？水平拆分模块间耦合性太强，成本太大，不是特别推荐。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我们。

赞 (0)

相关推荐

  • MySQL数据库21条最佳性能优化经验

    今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显.关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情. 当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能.这里,我们不会讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库.希望下面的这些优化技巧对你有用. 1. 为查询缓存优化你的查询 大多数的MySQL服务器都开启了查询缓存.这是提高性最有效的方法之一,而且这是被M

  • 数据库管理中19个MySQL优化方法

    数据库管理中19个MySQL优化方法

    MySQL数据库优化以后,不仅可以减少数据库的冗余,而且还可以让数据库运行速度都有所改变,下面使我们整理的19条非常好的MySQL数据库优化方法,参考一下. 声明一下:下面的优化方案都是基于 " Mysql-索引-BTree类型 " 的 一.EXPLAIN 做MySQL优化,我们要善用 EXPLAIN 查看SQL执行计划. 下面来个简单的示例,标注(1,2,3,4,5)我们要重点关注的数据 type列,连接类型.一个好的sql语句至少要达到range级别.杜绝出现all级别 key列,

  • MYSQL数据库表结构优化方法详解

    本文实例讲述了MYSQL数据库表结构优化方法.分享给大家供大家参考,具体如下: 选择合适的数据类型 1.使用可以存下你的数据的最小的数据类型 2.使用简单的数据类型.Int要比varchar类型在mysql处理上简单 3.尽可能的使用not null定义字段 4.尽量少用text类型,非用不可时最好考虑分表 使用int来存储日期时间,利用FROM_UNIXTIME()[将int类型时间戳转换成日期时间格式],UNIX_TIMESTAMP()[将日期时间格式转换成int类型]两个函数进行转换 使用

  • mysql数据库常见的优化操作总结（经验分享）

    前言 对于一个以数据为中心的应用,数据库的好坏直接影响到程序的性能,因此数据库性能至关重要.所以mysql数据库的优化操作大家都要有所了解,本文就主要总结了mysql数据库中常见的优化操作,下面话不多说了,来看看详细的介绍吧. 一.Index索引 将Index放第一位,不用说,这种优化方式我们一直都在悄悄使用,那便是主键索引.有时候我们可能并不在意,如果定义适合的索引,数据库查询性能(速度)将提高几倍甚至几十倍. 普通索引 作用是提高查询速度. 建表,创建索引 CREATE TABLE tbl_

  • 详解MySQL数据库优化的八种方式(经典必看)

    引言: 关于数据库优化,网上有不少资料和方法,但是不少质量参差不齐,有些总结的不够到位,内容冗杂. 偶尔发现了这篇文章,总结得很经典,文章流量也很大,所以拿到自己的总结文集中,积累优质文章,提升个人能力,希望对大家今后开发中也有帮助 1.选取最适用的字段属性 MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快.因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小. 例如,在定义邮政编码这个字段时,如果将其设置为CHAR(

  • Mysql数据库性能优化一

    Mysql数据库性能优化一

    今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显.关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情.当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能.这里,我们不会讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库. mysql的性能优化无法一蹴而就,必须一步一步慢慢来,从各个方面进行优化,最终性能就会有大的提升. Mysql数据库的优化技术 对mysql优化是

  • Mysql数据库中数据表的优化、外键与三范式用法实例分析

    Mysql数据库中数据表的优化、外键与三范式用法实例分析

    本文实例讲述了Mysql数据库中数据表的优化.外键与三范式用法.分享给大家供大家参考,具体如下: 数据表优化 将商品信息表进行优化 1.创建商品种类表: create table if not exists goods_cates( id int unsigned primary key auto_increment, name varchar(40) not null ); 2.将商品种类写入商品种类表中: 注意:插入另一个表的查询结果不需要加values insert into goods_

  • 简单了解MYSQL数据库优化阶段

    引言 大家在面试的时候,是否遭遇过,面试官询问 你们是如何进行数据库优化的? 那这个问题应该怎么答呢?其实写这个题材的原因是我这几天看到各公众号转的一篇数据库调优的知识(不上链接了),我就稍微翻了几下,上面动不动就来说要对数据库进行水平拆分,我就想反问各位读者,你们几个人经历过水平拆分?现在很多文章,实践性实在太差,只能说纯理论分析. 这篇文章最早来自知乎的一个提问,我在其基础上完善了一下. 第一阶段 优化sql和索引 这才是调优的第一阶段啊,为什么呢? 因为这一步成本最低啊,不需要加什么中间件

  • 简单了解MySQL数据库优化技巧

    一个成熟的数据库架构并不是一开始设计就具备高可用.高伸缩等特性的,它是随着用户量的增加,基础架构才逐渐完善.这篇文章主要谈谈MySQL数据库在发展周期中所面临的问题及优化方案,暂且抛开前端应用不说,大致分为以下五个阶段: 阶段一:数据库表设计 项目立项后,开发部门根据产品部门需求开发项目. 开发工程师在开发项目初期会对表结构设计.对于数据库来说,表结构设计很重要,如果设计不当,会直接影响到用户访问网站速度,用户体验不好!这种情况具体影响因素有很多,例如慢查询(低效的查询语句).没有适当建立索引.

  • MySQL数据库优化之分表分库操作实例详解

    本文实例讲述了MySQL数据库优化之分表分库操作.分享给大家供大家参考,具体如下: 分表分库 垂直拆分 垂直拆分就是要把表按模块划分到不同数据库表中(当然原则还是不破坏第三范式),这种拆分在大型网站的演变过程中是很常见的.当一个网站还在很小的时候,只有小量的人来开发和维护,各模块和表都在一起,当网站不断丰富和壮大的时候,也会变成多个子系统来支撑,这时就有按模块和功能把表划分出来的需求.其实,相对于垂直切分更进一步的是服务化改造,说得简单就是要把原来强耦合的系统拆分成多个弱耦合的服务,通过服务间的

  • php简单操作mysql数据库的类

    本文实例讲述了php简单操作mysql数据库的类.分享给大家供大家参考.具体如下: <?php /** * Database class * * @version: 2.2 * @revised: 27 may 2007 * **/ class Database { var $host; var $name; var $user; var $pass; var $prefix; var $linkId; function Database($mysql) { foreach($mysql as

  • mysql数据库优化需要遵守的原则

    这是我在网上看到的一篇不错的mysql数据库优化文章,内容说的很全! 1.使用索引来更快地遍历表 缺省情况下建立的索引是非群集索引,但有时它并不是最佳的.在非群集索引下,数据在物理上随机存放在数据页上.合理的索引设计要建立在对各种查询的分析和预测上.一般来说: a.有大量重复值.且经常有范围查询( > ,< ,> =,< =)和order by.group by发生的列,可考虑建立群集索引; b.经常同时存取多列,且每列都含有重复值可考虑建立组合索引; c.组合索引要尽量使关键查询

  • MySQL数据库优化技术之配置技巧总结

    本文实例讲述了MySQL数据库优化技术的配置方法.分享给大家供大家参考,具体如下: (一)减少数据库访问 对于可以静态化的页面,尽可能静态化 对一个动态页面中可以静态的局部,采用静态化 部分数据可以生成XML,或者文本文件形式保存 使用数据缓存技术,例如: MemCached (二)优化的检测方法 1.用户体验检测 2.Mysql状态检测 在Mysql命令行里面使用show status命令,得到当前mysql状态. 主要关注下列属性: key_read_requests (索引读的请求数)(k

  • MySQL数据库优化技术之索引使用技巧总结

    本文实例总结了MySQL数据库优化技术的索引用法.分享给大家供大家参考,具体如下: 这里紧接上一篇<MySQL数据库优化技术之配置技巧总结>,进一步分析索引优化的技巧: (七)表的优化 1. 选择合适的数据引擎 MyISAM:适用于大量的读操作的表 InnoDB:适用于大量的写读作的表 2.选择合适的列类型 使用 SELECT * FROM TB_TEST PROCEDURE ANALYSE()可以对这个表的每一个字段进行分析,给出优化列类型建议 3.对于不保存NULL值的列使用NOT NUL

  • MySQL数据库优化之索引实现原理与用法分析

    MySQL数据库优化之索引实现原理与用法分析

    本文实例讲述了MySQL数据库优化之索引实现原理与用法.分享给大家供大家参考,具体如下: 索引 什么是索引 索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存.如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录.表里面的记录数量越多,这个操作的代价就越高.如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置.如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍.

  • 9种 MySQL数据库优化的技巧

    9种 MySQL数据库优化的技巧

    目录 1.选择最合适的字段属性 2.尽量把字段设置为NOT NULL 3.使用连接(JOIN)来代替子查询(Sub-Queries) 4.使用联合(UNION)来代替手动创建的临时表 5.事务 6.使用外键 7.锁定表 8.使用索引 9.优化de的查询语句 9.1 不使用子查询 9.2 避免函数索引 9.3 用IN来替换OR 9.4 LIKE双百分号无法使用到索引 9.5 读取适当的记录LIMIT M,N 9.6 避免数据类型不一致 9.7 分组统计可以禁止排序 9.8 避免随机取记录 9.9

随机推荐