关于mysql:MySQL大表优化方案

当 MySQL 单表记录数过大时，增删改查性能都会急剧下降，能够参考以下步骤来优化：

单表优化

除非单表数据将来会始终一直上涨，否则不要一开始就思考拆分，拆分会带来逻辑、部署、运维的各种复杂度，个别以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候 MySQL 单表的性能仍然有不少优化空间，甚至能失常撑持千万级以上的数据量：

字段

• 尽量应用 TINYINT、SMALLINT、MEDIUM_INT 作为整数类型而非 INT，如果非负则加上 UNSIGNED
• VARCHAR 的长度只调配真正须要的空间
• 应用枚举或整数代替字符串类型
• 尽量应用 TIMESTAMP 而非 DATETIME，
• 单表不要有太多字段，倡议在 20 以内
• 防止应用 NULL 字段，很难查问优化且占用额定索引空间
• 用整型来存 IP

索引

• 索引并不是越多越好，要依据查问有针对性的创立，思考在 WHERE 和 ORDER BY 命令上波及的列建设索引，可依据 EXPLAIN 来查看是否用了索引还是全表扫描
• 应尽量避免在 WHERE 子句中对字段进行 NULL 值判断，否则将导致引擎放弃应用索引而进行全表扫描
• 值散布很稀少的字段不适宜建索引，例如 ” 性别 ” 这种只有两三个值的字段
• 字符字段只建前缀索引
• 字符字段最好不要做主键
• 不必外键，由程序保障束缚
• 尽量不必 UNIQUE，由程序保障束缚
• 应用多列索引时主见程序和查问条件保持一致，同时删除不必要的单列索引

查问 SQL

• 可通过开启慢查问日志来找出较慢的 SQL
• 不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包含数据库教程函数、计算表达式等等，查问时要尽可能将操作移至等号左边
• sql 语句尽可能简略：一条 sql 只能在一个 cpu 运算；大语句拆小语句，缩小锁工夫；一条大 sql 能够堵死整个库
• 不必 SELECT *
• OR 改写成 IN：OR 的效率是 n 级别，IN 的效率是 log(n)级别，in 的个数倡议管制在 200 以内
• 不必函数和触发器，在应用程序实现
• 防止 %xxx 式查问
• 少用 JOIN
• 应用同类型进行比拟，比方用’123’和’123’比，123 和 123 比
• 尽量避免在 WHERE 子句中应用!= 或 <> 操作符，否则将引擎放弃应用索引而进行全表扫描
• 对于间断数值，应用 BETWEEN 不必 IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
• 列表数据不要拿全表，要应用 LIMIT 来分页，每页数量也不要太大

引擎
目前宽泛应用的是 MyISAM 和 InnoDB 两种引擎：

MyISAM
MyISAM 引擎是 MySQL 5.1 及之前版本的默认引擎，它的特点是：

• 不反对行锁，读取时对须要读到的所有表加锁，写入时则对表加排它锁
• 不反对事务
• 不反对外键
• 不反对解体后的平安复原
• 在表有读取查问的同时，反对往表中插入新纪录
• 反对 BLOB 和 TEXT 的前 500 个字符索引，反对全文索引
• 反对提早更新索引，极大晋升写入性能
• 对于不会进行批改的表，反对压缩表，极大缩小磁盘空间占用

InnoDB
InnoDB 在 MySQL 5.5 后成为默认索引，它的特点是：

• 反对行锁，采纳 MVCC 来反对高并发
• 反对事务
• 反对外键
• 反对解体后的平安复原
• 不反对全文索引

总体来讲，MyISAM 适宜 SELECT 密集型的表，而 InnoDB 适宜 INSERT 和 UPDATE 密集型的表

零碎调优参数

能够应用上面几个工具来做基准测试：

• sysbench：一个模块化，跨平台以及多线程的性能测试工具
• iibench-mysql：基于 Java 的 MySQL/Percona/MariaDB 索引进行插入性能测试工具
• tpcc-mysql：Percona 开发的 TPC- C 测试工具

具体的调优参数内容较多，具体可参考官网文档，这里介绍一些比拟重要的参数：

• back_log：back_log 值指出在 MySQL 临时进行答复新申请之前的短时间内多少个申请能够被存在堆栈中。也就是说，如果 MySql 的连贯数据达到 max_connections 时，新来的申请将会被存在堆栈中，以期待某一连贯开释资源，该堆栈的数量即 back_log，如果期待连贯的数量超过 back_log，将不被授予连贯资源。能够从默认的 50 升至 500
• wait_timeout：数据库连贯闲置工夫，闲置连贯会占用内存资源。能够从默认的 8 小时减到半小时
• max_user_connection: 最大连接数，默认为 0 无下限，最好设一个正当下限
• thread_concurrency：并发线程数，设为 CPU 核数的两倍
• skip_name_resolve：禁止对外部连贯进行 DNS 解析，打消 DNS 解析工夫，但须要所有近程主机用 IP 拜访
• key_buffer_size：索引块的缓存大小，减少会晋升索引处理速度，对 MyISAM 表性能影响最大。对于内存 4G 左右，可设为 256M 或 384M，通过查问 show status like‘key_read%’，保障 key_reads / key_read_requests 在 0.1% 以下最好
• innodb_buffer_pool_size：缓存数据块和索引块，对 InnoDB 表性能影响最大。通过查问 show status like‘Innodb_buffer_pool_read%’，保障 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests 越高越好
• innodb_additional_mem_pool_size：InnoDB 存储引擎用来存放数据字典信息以及一些外部数据结构的内存空间大小，当数据库对象十分多的时候，适当调整该参数的大小以确保所有数据都能寄存在内存中进步拜访效率，当过小的时候，MySQL 会记录 Warning 信息到数据库的谬误日志中，这时就须要该调整这个参数大小
• innodb_log_buffer_size：InnoDB 存储引擎的事务日志所应用的缓冲区，一般来说不倡议超过 32MB
• query_cache_size：缓存 MySQL 中的 ResultSet，也就是一条 SQL 语句执行的后果集，所以仅仅只能针对 select 语句。当某个表的数据有任何任何变动，都会导致所有援用了该表的 select 语句在 Query Cache 中的缓存数据生效。所以，当咱们的数据变动十分频繁的状况下，应用 Query Cache 可能会得失相当。依据命中率 (Qcache_hits/(Qcache_hits+Qcache_inserts)*100)) 进行调整，个别不倡议太大，256MB 可能曾经差不多了，大型的配置型静态数据可适当调大.
  能够通过命令 show status like ‘Qcache_%’ 查看目前零碎 Query catch 应用大小
• read_buffer_size：MySql 读入缓冲区大小。对表进行程序扫描的申请将调配一个读入缓冲区，MySql 会为它调配一段内存缓冲区。如果对表的程序扫描申请十分频繁，能够通过减少该变量值以及内存缓冲区大小进步其性能
• sort_buffer_size：MySql 执行排序应用的缓冲大小。如果想要减少 ORDER BY 的速度，首先看是否能够让 MySQL 应用索引而不是额定的排序阶段。如果不能，能够尝试减少 sort_buffer_size 变量的大小
• read_rnd_buffer_size：MySql 的随机读缓冲区大小。当按任意程序读取行时(例如，依照排序程序)，将调配一个随机读缓存区。进行排序查问时，MySql 会首先扫描一遍该缓冲，以防止磁盘搜寻，进步查问速度，如果须要排序大量数据，可适当调高该值。但 MySql 会为每个客户连贯发放该缓冲空间，所以应尽量适当设置该值，以防止内存开销过大。
• record_buffer：每个进行一个程序扫描的线程为其扫描的每张表调配这个大小的一个缓冲区。如果你做很多程序扫描，可能想要减少该值
• thread_cache_size：保留以后没有与连贯关联然而筹备为前面新的连贯服务的线程，能够疾速响应连贯的线程申请而无需创立新的
• table_cache：相似于 thread_cache_size，但用来缓存表文件，对 InnoDB 成果不大，次要用于 MyISAM

降级硬件

Scale up，这个不多说了，依据 MySQL 是 CPU 密集型还是 I / O 密集型，通过晋升 CPU 和内存、应用 SSD，都能显著晋升 MySQL 性能

读写拆散

也是目前罕用的优化，从库读主库写，个别不要采纳双主或多主引入很多复杂性，尽量采纳文中的其余计划来进步性能。同时目前很多拆分的解决方案同时也兼顾思考了读写拆散

缓存

缓存能够产生在这些档次：

• MySQL 外部：在零碎调优参数介绍了相干设置
• 数据拜访层：比方 MyBatis 针对 SQL 语句做缓存，而 Hibernate 能够准确到单个记录，这里缓存的对象次要是长久化对象 Persistence Object
• 应用服务层：这里能够通过编程伎俩对缓存做到更精准的管制和更多的实现策略，这里缓存的对象是数据传输对象 Data Transfer Object
• Web 层：针对 web 页面做缓存
• 浏览器客户端：用户端的缓存

能够依据理论状况在一个档次或多个档次联合退出缓存。这里重点介绍下服务层的缓存实现，目前次要有两种形式：

• 直写式（Write Through）：在数据写入数据库后，同时更新缓存，维持数据库与缓存的一致性。这也是以后大多数利用缓存框架如 Spring Cache 的工作形式。这种实现非常简单，同步好，但效率个别。
• 回写式（Write Back）：当有数据要写入数据库时，只会更新缓存，而后异步批量的将缓存数据同步到数据库上。这种实现比较复杂，须要较多的应用逻辑，同时可能会产生数据库与缓存的不同步，但效率十分高。

表分区

MySQL 在 5.1 版引入的分区是一种简略的程度拆分，用户须要在建表的时候加上分区参数，对利用是通明的无需批改代码

对用户来说，分区表是一个独立的逻辑表，然而底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对 SQL 层来说是一个齐全封装底层的黑盒子。MySQL 实现分区的形式也意味着索引也是依照分区的子表定义，没有全局索引

用户的 SQL 语句是须要针对分区表做优化，SQL 条件中要带上分区条件的列，从而使查问定位到大量的分区上，否则就会扫描全副分区，能够通过 EXPLAIN PARTITIONS 来查看某条 SQL 语句会落在那些分区上，从而进行 SQL 优化，如下图 5 条记录落在两个分区上：

mysql> explain partitions select count(1) from user_partition where id in (1,2,3,4,5);
±—±————±—————±———–±——±————–±——–±——–±—–±—–±————————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
±—±————±—————±———–±——±————–±——–±——–±—–±—–±————————-+
| 1 | SIMPLE | user_partition | p1,p4 | range | PRIMARY | PRIMARY | 8 | NULL | 5 | Using where; Using index |
±—±————±—————±———–±——±————–±——–±——–±—–±—–±————————-+
1 row in set (0.00 sec)

分区的益处是：

• 能够让单表存储更多的数据
• 分区表的数据更容易保护，能够通过分明整个分区批量删除大量数据，也能够减少新的分区来反对新插入的数据。另外，还能够对一个独立分区进行优化、查看、修复等操作
• 局部查问可能从查问条件确定只落在多数分区上，速度会很快
• 分区表的数据还能够散布在不同的物理设施上，从而搞笑利用多个硬件设施
• 能够应用分区表赖防止某些非凡瓶颈，例如 InnoDB 单个索引的互斥拜访、ext3 文件系统的 inode 锁竞争
• 能够备份和复原单个分区

分区的限度和毛病：

• 一个表最多只能有 1024 个分区
• 如果分区字段中有主键或者惟一索引的列，那么所有主键列和惟一索引列都必须蕴含进来
• 分区表无奈应用外键束缚
• NULL 值会使分区过滤有效
• 所有分区必须应用雷同的存储引擎

分区的类型：

• RANGE 分区：基于属于一个给定间断区间的列值，把多行调配给分区
• LIST 分区：相似于按 RANGE 分区，区别在于 LIST 分区是基于列值匹配一个离散值汇合中的某个值来进行抉择
• HASH 分区：基于用户定义的表达式的返回值来进行抉择的分区，该表达式应用将要插入到表中的这些行的列值进行计算。这个函数能够蕴含 MySQL 中无效的、产生非负整数值的任何表达式
• KEY 分区：相似于按 HASH 分区，区别在于 KEY 分区只反对计算一列或多列，且 MySQL 服务器提供其本身的哈希函数。必须有一列或多列蕴含整数值

分区适宜的场景有：

• 最适宜的场景数据的工夫序列性比拟强，则能够按工夫来分区，如下所示：

CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
)
PARTITION BY RANGE(YEAR(joined) ) (
PARTITION p0 VALUES LESS THAN (1960),
PARTITION p1 VALUES LESS THAN (1970),
PARTITION p2 VALUES LESS THAN (1980),
PARTITION p3 VALUES LESS THAN (1990),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
查问时加上工夫范畴条件效率会十分高，同时对于不须要的历史数据能很容的批量删除。

• 如果数据有显著的热点，而且除了这部分数据，其余数据很少被拜访到，那么能够将热点数据独自放在一个分区，让这个分区的数据可能有机会都缓存在内存中，查问时只拜访一个很小的分区表，可能无效应用索引和缓存

另外 MySQL 有一种晚期的简略的分区实现 – 合并表（merge table），限度较多且不足优化，不倡议应用，应该用新的分区机制来代替

垂直拆分

垂直分库是依据数据库外面的数据表的相关性进行拆分，比方：一个数据库外面既存在用户数据，又存在订单数据，那么垂直拆分能够把用户数据放到用户库、把订单数据放到订单库。垂直分表是对数据表进行垂直拆分的一种形式，常见的是把一个多字段的大表按常用字段和十分用字段进行拆分，每个表外面的数据记录数个别状况下是雷同的，只是字段不一样，应用主键关联
比方原始的用户表是：

垂直拆分后是：

垂直拆分的长处是：

• 能够使得行数据变小，一个数据块 (Block) 就能寄存更多的数据，在查问时就会缩小 I / O 次数(每次查问时读取的 Block 就少)
• 能够达到最大化利用 Cache 的目标，具体在垂直拆分的时候能够将不常变的字段放一起，将常常扭转的放一起
• 数据保护简略

毛病是：

• 主键呈现冗余，须要治理冗余列
• 会引起表连贯 JOIN 操作（减少 CPU 开销）能够通过在业务服务器上进行 join 来缩小数据库压力
• 仍然存在单表数据量过大的问题（须要程度拆分）
• 事务处理简单

程度拆分

概述
程度拆分是通过某种策略将数据分片来存储，分库内分表和分库两局部，每片数据会扩散到不同的 MySQL 表或库，达到分布式的成果，可能反对十分大的数据量。后面的表分区实质上也是一种非凡的库内分表

库内分表，仅仅是单纯的解决了繁多表数据过大的问题，因为没有把表的数据分布到不同的机器上，因而对于加重 MySQL 服务器的压力来说，并没有太大的作用，大家还是竞争同一个物理机上的 IO、CPU、网络，这个就要通过分库来解决

后面垂直拆分的用户表如果进行程度拆分，后果是：

理论状况中往往会是垂直拆分和程度拆分的联合，行将 Users_A_M 和 Users_N_Z 再拆成 Users 和 UserExtras，这样一共四张表

程度拆分的长处是:

• 不存在单库大数据和高并发的性能瓶颈
• 利用端革新较少
• 进步了零碎的稳定性和负载能力

毛病是：

• 分片事务一致性难以解决
• 跨节点 Join 性能差，逻辑简单
• 数据屡次扩大难度跟保护量极大

分片准则

• 能不分就不分，参考单表优化
• 分片数量尽量少，分片尽量均匀分布在多个数据结点上，因为一个查问 SQL 跨分片越多，则总体性能越差，尽管要好于所有数据在一个分片的后果，只在必要的时候进行扩容，减少分片数量
• 分片规定须要谨慎抉择做好提前布局，分片规定的抉择，须要思考数据的增长模式，数据的拜访模式，分片关联性问题，以及分片扩容问题，最近的分片策略为范畴分片，枚举分片，一致性 Hash 分片，这几种分片都有利于扩容
• 尽量不要在一个事务中的 SQL 逾越多个分片，分布式事务始终是个不好解决的问题
• 查问条件尽量优化，尽量避免 Select * 的形式，大量数据后果集下，会耗费大量带宽和 CPU 资源，查问尽量避免返回大量后果集，并且尽量为频繁应用的查问语句建设索引。
• 通过数据冗余和表分区赖升高跨库 Join 的可能

这里特别强调一下分片规定的抉择问题，如果某个表的数据有显著的工夫特色，比方订单、交易记录等，则他们通常比拟适合用工夫范畴分片，因为具备时效性的数据，咱们往往关注其近期的数据，查问条件中往往带有工夫字段进行过滤，比拟好的计划是，以后沉闷的数据，采纳跨度比拟短的时间段进行分片，而历史性的数据，则采纳比拟长的跨度存储。

总体上来说，分片的抉择是取决于最频繁的查问 SQL 的条件，因为不带任何 Where 语句的查问 SQL，会遍历所有的分片，性能绝对最差，因而这种 SQL 越多，对系统的影响越大，所以咱们要尽量避免这种 SQL 的产生。

解决方案

因为程度拆分关涉的逻辑比较复杂，以后也有了不少比拟成熟的解决方案。这些计划分为两大类：客户端架构和代理架构。

客户端架构
通过批改数据拜访层，如 JDBC、Data Source、MyBatis，通过配置来治理多个数据源，直连数据库，并在模块内实现数据的分片整合，个别以 Jar 包的形式出现

这是一个客户端架构的例子：

能够看到分片的实现是和应用服务器在一起的，通过批改 Spring JDBC 层来实现

客户端架构的长处是：

• 利用直连数据库，升高外围零碎依赖所带来的宕机危险
• 集成成本低，无需额定运维的组件

毛病是：

• 限于只能在数据库拜访层上做文章，扩展性个别，对于比较复杂的零碎可能会力不从心
• 将分片逻辑的压力放在应用服务器上，造成额定危险

代理架构

通过独立的中间件来对立治理所有数据源和数据分片整合，后端数据库集群对前端应用程序通明，须要独立部署和运维代理组件
这是一个代理架构的例子：

代理组件为了分流和避免单点，个别以集群模式存在，同时可能须要 Zookeeper 之类的服务组件来治理

代理架构的长处是：

• 可能解决非常复杂的需要，不受数据库拜访层原来实现的限度，扩展性强
• 对于应用服务器通明且没有减少任何额定负载

毛病是：

• 需部署和运维独立的代理中间件，老本高
• 利用需通过代理来连贯数据库，网络上多了一跳，性能有损失且有额定危险

各计划比拟




如此多的计划，如何进行抉择？能够按以下思路来思考：
1. 确定是应用代理架构还是客户端架构。中小型规模或是比较简单的场景偏向于抉择客户端架构，简单场景或大规模零碎偏向抉择代理架构

2. 具体性能是否满足，比方须要跨节点 ORDER BY，那么反对该性能的优先思考

3. 不思考一年内没有更新的产品，阐明开发停滞，甚至无人保护和技术支持

4. 最好按大公司 -> 社区 -> 小公司 -> 集体这样的出品方程序来抉择

5. 抉择口碑较好的，比方 github 星数、使用者数量质量和使用者反馈

6. 开源的优先，往往我的项目有非凡需要可能须要改变源代码

依照上述思路，举荐以下抉择：

• 客户端架构：ShardingJDBC
• 代理架构：MyCat 或者 Atlas

兼容 MySQL 且可程度扩大的数据库

目前也有一些开源数据库兼容 MySQL 协定，如：

• TiDB
• Cubrid

但其工业品质和 MySQL 尚有差距，且须要较大的运维投入，如果想将原始的 MySQL 迁徙到可程度扩大的新数据库中，能够思考一些云数据库：

• 阿里云 PetaData
• 阿里云 OceanBase
• 腾讯云 DCDB

NoSQL

在 MySQL 上做 Sharding 是一种戴着镣铐的跳舞，事实上很多大表自身对 MySQL 这种 RDBMS 的需要并不大，并不要求 ACID，能够思考将这些表迁徙到 NoSQL，彻底解决程度扩大问题，例如：

• 日志类、监控类、统计类数据
• 非结构化或弱结构化数据
• 对事务要求不强，且无太多关联操作的数据

起源：segmentfault.com/a/1190000006158186

欢送关注我的微信公众号「码农解围」，分享 Python、Java、大数据、机器学习、人工智能等技术，关注码农技术晋升•职场解围•思维跃迁，20 万 + 码农成长充电第一站，陪有幻想的你一起成长