Hey Big data
Good wine needs no bush.–酒香不怕巷子深.
前言
现在的开发中,我们首先会想到大数据,高并发,多线程,因为这是这个时代的潮流,那么怎么处理这些大数据,是我们开发者需要考虑的问题,针对于大数据处理,经过网上的搜索资料以及个人经验总结,今天就浅谈一些优化策略.
当 MySQL 单表记录数过大时,增删改查性能都会急剧下降,可以参考以下步骤来优化:
单表优化
字段
1 尽量使用 TINYINT、SMALLINT、MEDUIM_INT 作为整数类型的 INT ,如果非负,那么就加上 UNSIGNED .
2 VARCHAR 分配真正需要的空间.
3 使用枚举或者整数代替字符串类型
4 尽量使用 TIMESTAMP 而非是 DATETIME.
5 单表不要有太多的字段,最好控制在 20 以内.
6 避免使用 NULL 字段,难查询,并且占空间
7 如果有 ip 的话 ,使用整型来存储.
索引
1 索引并不是越多越好,要根据查询有针对性的创建,考虑在 WHERE 和 ORDER BY 命令上涉及到的列建立索引,可以根据 EXPLAIN 来查看是否用了索引还是全表扫描.
2 应该尽量避免在 WHERE 语句中对字段进行 NULL 的查询,否则可能会导致引擎放弃使用索引而进行全表扫描.
3 值分布很少的字段不适合做索引
4 字符字段只建立前缀索引
5 字符字段不能做主键
6 尽量不用 UNIQUE ,由程序保证约束
7 使用多列索引时主意顺序和查询条件保持一致,同时删除不必要的单列索引
查询 SQL
1 可以通过开启慢查询日志来找出较慢的 SQL
2 不做列运算: SELECT id WHERE age + 1 = 10 ,任何对列的操作都将导致表扫描,它包括数据库教程函数、计算表达式等等,查询时要尽可能的将操作移至等号右边.
3 SQL 语句尽量的简单,不能使用 SELECT *
4 OR 改写成 IN : OR 的效率是 n 级别的,而 IN 的级别是 log(n)
5 避免 %xx 的实现
6 少使用 JOIN
7 尽量避免在 WHERE 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描
8 对于连续数值,使用 BETWEEN 不用 IN: SELECT id FROM t WHERE num BETWEEN 1 AND 5
9 列表数据不要拿全表,要使用 LIMIT 来分页,每页数量也不要太大
引擎
目前广泛使用的是 MyISAM 和 InnoDB 两种引擎:
MyISAM
MyISAM 引擎是 MySQL 5.1 及之前版本的默认引擎,它的特点是:
1 不支持行锁,读取时需要对读到的所有表进行加锁,写入时对表加排它锁.
2 不支持事务
3 不支持外键
4 不支持崩溃后的安全恢复
5 在表支持读取查询的同时,支持往表中插入新的记录
6 支持 BLOB 和 TEXT 的前 500 个字符索引,支持全文索引
7 支持延迟更新索引
8 对于不会进行修改的表,支持压缩表,减少磁盘的额空间占用
InnoDB
InnoDB 在 MySQL 5.5 后成为默认索引,它的特点是:
1 支持行锁
2 支持事务
3 支持外键
4 支持崩溃后的安全恢复
5 不支持全文索引
总体来讲,MyISAM 适合 SELECT 密集型的表,而 InnoDB 适合 INSERT 和 UPDATE 密集型的表
系统调优参数
具体的调优参数内容较多,具体可参考官方文档,这里介绍一些比较重要的参数:
1 back_log : back_log 值指出在 MySQL 暂时停止回答新的请求之前的短时间之内多少个请求可以被存在堆栈中,也就是说,如果 MySQL 的连接数达到了 max_connection 时,新的请求将会被存在堆栈中.以等待某一处连接释放资源,该堆栈中的数量就是 back_log 如果等待的数量超过了 back_log 将不会授予连接的资源,可以从默认的 50 升至 500.
2 wait_timeout : 数据库的闲置连接时间,闲置连接会占用内存资源,可以从默认的 8 个小时减到 1 个小时.
3 max_user_connection : 最大连接数,默认为0无上限,最好设一个合理上限
4 thread_concurrency : 并发线程数,设为 CPU 核数的两倍
5 skip_name_resolve : 禁止对外部连接进行 DNS 解析,消除 DNS 解析时间,但需要所有远程主机用 IP 访问
6 key_buffer_size : 索引块的缓存大小,增加会提升索引处理速度,对 MyISAM 表性能影响最大.
7 innodb_buffer_pool_size : 缓存数据块和索引块,对 InnoDB 表性能影响最大.
8 innodb_additional_mem_pool_size : InnoDB 存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小,当数据库对象非常多的时候,适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率,当过小的时候,MySQL 会记录 Warning 信息到数据库的错误日志中,这时就需要该调整这个参数大小.
9 innodb_log_buffer_size : InnoDB 存储引擎的事务日志所使用的缓冲区,一般来说不建议超过 32MB
10 read_buffer_size : MySql 读入缓冲区大小.对表进行顺序扫描的请求将分配一个读入缓冲区,MySql 会为它分配一段内存缓冲区.如果对表的顺序扫描请求非常频繁,可以通过增加该变量值以及内存缓冲区大小提高其性能.
11 table_cache : 类似于thread_cache_size,但用来缓存表文件,对 InnoDB 效果不大,主要用于 MyISAM
读写分离
也是目前常用的优化,从库读主库写,一般不要采用双主或多主引入很多复杂性,尽量采用文中的其他方案来提高性能.同时目前很多拆分的解决方案同时也兼顾考虑了读写分离
缓存
缓存可以发生在这些层次:
1 MySQL内部: 在系统调优参数介绍了相关设置
2 数据访问层: 比如 MyBatis 针对 SQL 语句做缓存,而 Hibernate 可以精确到单个记录,这里缓存的对象主要是持久化对象 Persistence Object
3 应用服务层: 这里可以通过编程手段对缓存做到更精准的控制和更多的实现策略,这里缓存的对象是数据传输对象 Data Transfer Object
4 Web层: 针对web页面做缓存
5 浏览器客户端: 用户端的缓存
可以根据实际情况在一个层次或多个层次结合加入缓存.这里重点介绍下服务层的缓存实现,目前主要有两种方式:
1 直写式( Write Through ): 在数据写入数据库后,同时更新缓存,维持数据库与缓存的一致性.这也是当前大多数应用缓存框架如 Spring Cache 的工作方式.这种实现非常简单,同步好,但效率一般.
2 回写式( Write Back ): 当有数据要写入数据库时,只会更新缓存,然后异步批量的将缓存数据同步到数据库上.这种实现比较复杂,需要较多的应用逻辑,同时可能会产生数据库与缓存的不同步,但效率非常高.
总结
以上优化策略只是对单标,SQL,引擎,系统调优,缓存等几个维度进行了浅谈.当有大数据处理的时候,还有更多的优化策略,下次我会对表的拆分进行浅谈.
