Mysql大表优化方案

八戒你干嘛

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

单表优化

除非单表数据后来会一直上涨,否则不要考虑拆分,拆分会带来逻辑,部署,运维的各种复杂度.
一般以整型值为主的表在千万级以下,字符型为主的表在500万以下是没有太大问题的.

事实上,Mysql的单表性能还是有很多优化空间的.甚至能正常支撑千万级以上的数据量.

字段优化

• 尽量使用 TINYINT,SMALLINT,MWDIUM_INT作为整数类型而非 INT,如果非负则加上UNSIGNED.
• VARCHAR 的长度只分配需要的空间.
• 使用枚举或者整数代替字符串类型.
• 尽量使用TIMESTAMP而非DATETIME.
• 单表不要有太多字段,建议在20以内.
• 避免使用null字段,很难查询优化且占用额外索引空间.
• 用整形来存ip
  

索引优化

• 索引并不是越多越好,要根据查询有针对性的创建,考虑在 ORDER BY和WHERE命令上设计的列上建立索引可根据EXPLAIN来查看是否用了索引还是全表扫描.
• 应尽量避免在WHERE字句中对字段进行null值判断.否则将导致引擎放弃索引改用全表扫描
• 值分布很少的字段不适合建立索引.比如性别这种只有两三个值得字段.
• 字符字段只建前缀索引.
• 字符字段最好不要做主键.
• 不用外键,用程序保证约束.
• 尽量不用UNIQUE,由程序保证约束.
• 使用多列索引时注意顺序和查询条件保持一致,同时删除不必要的单列索引.
  

查询SQL优化

• 可通过开启慢查询日志查找出较慢的SQL.
• 不做列运算: SELECT id WHERE age+1=10 ,任何对列的操作都将导致全表扫描.它包括数据库教程函数,计算表达式等等,查询时尽量将操作移至等号右边.
• SQL语句尽可能简单:一条SQL只能在一个cpu进行运算;大语句拆分成小语句,减少锁时间;一条大SQL可以堵死整个库.
• 不用 SELECT *.
• 少用 JOIN.
• 避免 %xxx式查询.
• 不用函数和触发器,在应用程序实现.
• OR改写成IN:OR的效率是 n 级别,IN的效率是 log(n)级别.IN的个数建议控制在200以内.
• 使用同类型进行比较:比如’123’和’123’比,123和123比.
• 尽量避免在WHERE 子句中使用!= <>操作,否则将导致引擎放弃索引使用全表扫描.
• 对于连续数值,使用BETWEEN而不用IN
• 列数据不要拿全表,要使用 LIMIT进行分页,每页数量页不要太大.
  

引擎

目前广泛使用的是MyISAM引擎和InnoDB两种引擎.

MyISAM

引擎是Mysql5.1版本之前的默认引擎.他的特点是:

• 不支持行锁,读取时需要对所有的表加锁.写入时则需要对对表加排他锁.
• 不支持事物.
• 不支持外键
• 不支持崩溃后的安全恢复.
• 在表有读取查询的时候,支持往表中添加新纪录.
• 支持BLOB和TEXT的前500个字符的索引.支持全文索引.
• 支持延迟更新索引,极大地提升写入性能.
• 对于不会修改的表,支持压缩表,极大地减少磁盘空间占用.
  

InnoDB

InnoDB在Mysql5.5之后成为默认引擎,他的特点是:

• 支持行锁,采用 MVCC 来支持高并发.
• 支持事物
• 支持外键
• 支持崩溃后的恢复
• 不支持全文索引
  

总的来说,MyISAM适合SELECT密集型的表,而InnoDB适合insert和update密集型的表.

升级硬件

Scale up,这个不必多少,根据MYSQL是CPU密集新还是I/O密集型通过提升CPU和内存,或者使用SSD,都能显著提升MYSQL性能.

读写分离

也是目前常用的优化方式. 从库读主库写.不建议采用双主或者多主引入不必要的复杂性，尽量采用稳重其它方案来提高性能。同时目前很多拆分的解决方案也考虑到了读写分离。

缓存

缓存可以发生在这些层次：

• Mysql内部，对系统参数进行调优。
• 数据访问层：比如Mybatis针对针对SQL语句做缓存，而Hibernate可以针对单个记录做缓存。这里缓存的对象主要是持久化对象 PersistenceObject。
• 应用服务层： 这里可以使用编程的手段对缓存进行更精准的控制和更多的实现策略。这里缓存的对象使数据传输对象：DataTransferObject。
• Web层： 针对web页面做缓存。
• 浏览器客户端： 针对用户端的缓存。
  

可以根据情况结合一个或者多个层次加入缓存。这里主要介绍服务层缓存的实现，目前主要有两种方式：
* 1. 直写式（Write Through）： 在数据写入数据库后，同时更新缓存，位置缓存和数据库的一致性。这也是大多数缓存应用框架如：SpringCache 的工作方式。这种实现非常简单，同步好，但效率一般。
* 2. 回写式（Write Back）： 当有数据要写入数据库时，只会更新缓存，然后异步批量的将缓存同步到数据库中。这种方式实现比较复杂需要较多的应用层逻辑，同时可能会产生数据库和缓存的不同步，但是效率非常高。

垂直拆分

垂直分库是根据数据库里面的数据表的相关性进行拆分，比如：一个数据库里面既存在用户数据，又存在订单数据，那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库。垂直分表是对数据表进行垂直拆分的一种方式，常见的是把一个多字段的大表按常用字段和非常用字段进行拆分，每个表里面的数据记录数一般情况下是相同的，只是字段不一样，使用主键关联