mysql大表优化方案
来源:互联网 发布:电脑智能机器人软件 编辑:程序博客网 时间:2024/05/16 10:44
引用:
http://database.51cto.com/art/201608/515550.htm
一、单表优化
除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分,拆分会带来逻辑、部署、运维的各种复杂度,一般以整型值为主的表在千万级以下,字符串为主的表在五百万以下是没有太大问题的。
事实上很多时候MySQL单表的性能依然有不少优化空间,甚至能正常支撑千万级以上的数据量:
(一)字段
l 尽量使用TINYINT、SMALLINT、MEDIUMINT作为整数类型而非INT,如果非负则加上UNSIGNED
l VARCHAR的长度只分配真正需要的空间
l 使用枚举或整数代替字符串类型
l 尽量使用TIMESTAMP而非DATETIME
l 单表不要有太多字段,建议在20以内
l 避免使用NULL字段,很难查询优化且占用额外索引空间
l 用整型来存IP
总结:尽量减少单条记录大小,保证一个Block可以读出更多的记录
(二)索引
l 索引并不是越多越好,要根据查询有针对性的创建,考虑在WHERE和ORDER BY命令上涉及的列建立索引,可根据EXPLAIN来查看是用了索引还是全表扫描
l 应尽量避免在WHERE子句中对字段进行NULL值判断,否则将导致引擎放弃使用索引而进行全表扫描
l 值分布很稀少的字段不适合建索引,例如"性别"这种只有两三个值的字段
l 字符字段只建前缀索引
l 字符字段最好不要做主键
l 不用外键,由程序保证约束
l 尽量不用UNIQUE,由程序保证约束
l 使用多列索引时注意列顺序和查询条件保持一致,同时删除不必要的单列索引
(三)SQL语句
l 通过开启慢查询日志来找出较慢的SQL
l 列上不做运算:SELECT id WHERE age + 1 = 10,任何对列的操作都将导致全表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边
l sql语句尽可能简单:一条sql只能在一个cpu上运算;大语句拆小语句,减少锁时间;一条大sql可以堵死整个库
l 不用SELECT *
l OR改写成IN:OR的效率是n级别,IN的效率是log(n)级别,in的个数建议控制在200以内
l 不用函数和触发器,在应用程序实现
l 避免%xxx式查询
l 少用JOIN
l 使用同类型进行比较,比如用'123'和'123'比,123和123比
l 尽量避免在WHERE中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表扫描
l 对于连续数值,使用BETWEEN不用IN:SELECT id FROM t WHERE num BETWEEN 1 AND 5
l 列表数据不要拿全表,要使用LIMIT来分页,每页数量也不要太大
二、缓存优化
缓存可以发生在这些层次:
l MySQL内部:在系统调优参数介绍了相关设置
l 数据访问层:比如MyBatis针对SQL语句做缓存,而Hibernate可以精确到单个记录,这里缓存的对象主要是持久化对象Persistence Object
l 应用服务层:这里可以通过编程手段对缓存做到更精准的控制和更多的实现策略,这里缓存的对象是数据传输对象Data Transfer Object
l Web层:针对web页面做缓存
l 浏览器客户端:用户端的缓存
可以根据实际情况在一个层次或多个层次结合加入缓存。这里重点介绍下服务层的缓存实现,目前主要有两种方式:
l 直写式(Write Through):在数据写入数据库后,同时更新缓存,维持数据库与缓存的一致性。这也是当前大多数应用缓存框架如Spring Cache的工作方式。这种实现非常简单,同步好,但效率一般。
l 回写式(Write Back):当有数据要写入数据库时,只会更新缓存,然后异步批量的将缓存数据同步到数据库上。这种实现比较复杂,需要较多的应用逻辑,同时可能会产生数据库与缓存的不同步,但效率非常高。
三、表分区
MySQL在5.1版引入的分区是一种简单的水平拆分,用户需要在建表的时候加上分区参数,对应用是透明的无需修改代码.
对用户来说,分区表是一个独立的逻辑表,但是底层由多个物理子表组成,实现分区的代码实际上是通过对一组底层表的对象封装,但对应用层来说是一个完全封装底层的黑盒子。
MySQL实现分区的方式也意味着索引也是按照分区的子表定义,没有全局索引.
分区适合的场景有:
l 最适合的场景是数据的时间序列性比较强,则可以按时间来分区.
l 如果数据有明显的热点,而且除了这部分数据,其他数据很少被访问到,那么可以将热点数据单独放在一个分区,让这个分区的数据能够有机会都缓存在内存中,查询时只访问一个很小的分区表,能够有效使用索引和缓存
四、垂直拆分
垂直拆分分为垂直分库和垂直分表两种.
垂直分库:
垂直分库是根据数据库里面的数据表的相关性进行拆分,比如:一个数据库里面既存在用户数据,又存在订单数据,那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库
垂直分表:
垂直分表是对数据表进行垂直拆分的一种方式,常见的是把一个多字段的大表按常用字段和不常用字段进行拆分,每个表里面的数据记录数一般情况下是相同的,只是字段不一样,使用主键关联.
垂直拆分的优点是:
l 可以使得行数据变小,一个数据块(Block)就能存放更多的数据,在查询时就会减少I/O次数(每次查询时读取的Block就少)
l 可以达到最大化利用Cache的目的,具体在垂直拆分的时候可以将不常变的字段放一起,将经常改变的放一起
l 数据维护简单
缺点是:
l 主键出现冗余,需要管理冗余列
l 会引起表连接JOIN操作(增加CPU开销)可以通过在应用服务器上进行join来减少数据库压力(分次查询,引用层拼装)
l 依然存在单表数据量过大的问题(需要水平拆分)
l 事务处理复杂
五、水平拆分
水平拆分是通过某种策略将数据按记录分片来存储,分为库内分表和分库分表两种,每片数据会分散到不同的MySQL表或库,达到分布式的效果,能够支持非常大的数据量。前面的表分区本质上也是一种特殊的库内分表
库内分表,仅仅是解决了单一表数据过大的问题,由于没有把表的数据分布到不同的机器上,因此对于减轻MySQL服务器的压力来说,并没有太大的作用,大家还是竞争同一个物理机上的IO、CPU、网络,这个就要通过分库来解决
(一)分片原则
l 能不分就不分,参考单表优化
l 分片数量尽量少,分片尽量均匀分布在多个数据结点上,因为一个查询SQL跨分片越多,则总体性能越差,虽然要好于所有数据在一个分片的结果.
l 只在必要的时候进行扩容,增加分片数量
l 分片规则需要慎重选择做好提前规划,分片规则的选择,需要考虑数据的增长模式,数据的访问模式,分片关联性问题,以及分片扩容问题,最近的分片策略为范围分片,枚举分片,一致性Hash分片,这几种分片都有利于扩容
l 尽量不要在一个事务中的SQL跨越多个分片,分布式事务一直是个不好处理的问题
l 查询条件尽量优化,尽量避免Select * 的方式,大量数据结果集下,会消耗大量带宽和CPU资源,查询尽量避免返回大量结果集,并且尽量为频繁使用的查询语句建立索引。
l 通过数据冗余和表分区来降低跨库Join的可能
(二)解决方案
由于水平拆分牵涉的逻辑比较复杂,当前也有了不少比较成熟的解决方案。这些方案分为两大类:客户端架构和代理架构。
客户端架构
通过修改数据访问层,如JDBC、Data Source、MyBatis,通过配置来管理多个数据源,直连数据库,并在模块内完成数据的分片整合,一般以Jar包的方式呈现
代理架构
通过独立的中间件来统一管理所有数据源和数据分片整合,后端数据库集群对前端应用程序透明,需要独立部署和运维代理组件.
- MySQL大表优化方案
- MySQL大表优化方案
- MySQL大表优化方案
- Mysql大表优化方案.
- MySQL 大表优化方案
- MySQL 大表优化方案
- MySQL 大表优化方案
- MySQL大表优化方案
- MySQL大表优化方案
- MySQL大表优化方案
- MySQL 大表优化方案
- MySQL 大表优化方案
- MySQL 大表优化方案
- MySQL大表优化方案
- MySQL大表优化方案
- MySQL大表优化方案
- MySQL大表优化方案
- MySQL大表优化方案
- 队列操作的实现-链式存储
- LintCode 第423题
- Model Tree Structures with Nested Sets
- 阿里云前端周刊
- 2018年才是人工智能元年?今年不是!
- mysql大表优化方案
- 理解朴素贝叶斯
- LinearLayout之weight完美详解
- ftp文件上传和下载
- spring boot-application.properties配置文件属性
- Sublime Text 3 build 3143 LICENSE
- [Text_Mining]notes_3
- Android中"&" 符号,在静态的string.xml中如何表示
- 进程调度API之__wake_up_sync