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来分页，每页数量也不要太大

读/写分离

经典的数据库拆分⽅案，主库负责写，从库负责读；

垂直分区

根据数据库⾥⾯数据表的相关性进⾏拆分。 例如，⽤户表中既有⽤户的登录信息⼜有⽤户的基本信息，可以将⽤户表拆分成两个单独的表，甚⾄放到单独的库做分库。
简单来说垂直拆分是指数据表列的拆分，把⼀张列⽐较多的表拆分为多张表。 如下图所示，这样来说⼤家应该就更容易理解了。个人再通俗的理解，就是一块蛋糕竖着切，从上至下，而水平分区就是横着切，从中间切。

垂直拆分的优点：

可以使得列数据变⼩，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点：

主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应⽤
层进⾏Join来解决。此外，垂直分区会让事务变得更加复杂；

水平分区

保持数据表结构不变，通过某种策略存储数据分⽚。这样每⼀⽚数据分散到不同的表或者库中，达到了分布式的⽬的。 ⽔平拆分可以⽀撑⾮常⼤的数据量。
⽔平拆分是指数据表⾏的拆分，表的⾏数超过200万⾏时，就会变慢，这时可以把⼀张的表的数据拆成多张表来存放。举个例⼦：我们可以将⽤户信息表拆分成多个⽤户信息表，这样就可以避免单⼀表数据量过⼤对性能造成影响。
⽔平拆分可以⽀持⾮常⼤的数据量。需要注意的⼀点是：分表仅仅是解决了单⼀表数据过⼤的问题，但由于表的数据还是在同⼀台机器上，其实对于提升MySQL并发能⼒没有什么意义，所以⽔平拆分最好分库 。

参考链接：
https://segmentfault.com/a/1190000006158186