数据库优化

1、概述

随着系统规模的不断增加，数据量和并发量不断增大，整个系统架构中最先受到冲击而形成瓶颈的，定然是数据库，因此数据库层面的优化，是一个程序员不可或缺的技能

2、常见的方式

2.1、创建索引

索引类型分四种：
(1)普通索引：允许有重复的值；
(2)唯一索引：不允许有重复的值；
(3)主键索引：特殊的唯一索引，是随着主键的创建而创建，也就是把某个列设为主键的时候，数据库就会给该列创建索引，唯一且不能为null值；
(4)全文索引：用来对表中的文本域(text，varchar)进行索引；只在MyISAM引擎中支持，Innodb不支持；
注：索引缺点是占用磁盘空间，并且会对dml(插入，删除，修改，)操作速度产生影响，变慢；

2.2、分库分表分区

• 水平分表(按行数据进行分表)

mysql数据一般达到百万级别，查询效率会很低，容易造成表锁，甚至堆积很多连接，直接挂掉，水平分表能够很大程序减少这些压力。

水平分表的策略：
a：按时间分表：这种分表方式有一定的局限性，当数据有较强的实效性，如微博发送记录，微信消息。。。这种数据很少有用户会查询几个月前的数据，就可以按月进行分表；
b：按区间范围分表：一般在有严格的自增id需求上，通过一个原始目标的ID或者名称通过一定的hash算法计算出数据存储的表明，然后再访问相应的表；
c：hash分表：(用得最多的)，

• 垂直分表(按列分表)

如果一张表中某个字段值非常多(长文本，二进制等)，而且只有在很少的情况下被查询，这个时候就可以把多个字段单独放到一张表，通过外键关联起来；比如考试详情，一般只关注分数，不关注详情；

2.3、读写分离

基于二八原则：80%的操作都是读，20%s 写。读写操作分开，降低IO压力，一主多从，主库写从库读

2.4、缓存

选择合适的数据库缓存，如redis、memcache、hibernate(不能解决分布式缓存)等

2.5、语句优化

• 尽可能少使用or
• 尽可能少使用LIKE “%XXXX”，以%开头是不能使用索引的，而如果是LIKE "XXX%"的就会使用索引
• 尽可能少使用Order by
• 尽可能少使用子查询
• 尽可能少使用sql语句中带有数学运算、函数运算
• 尽可能的使用数据库规定的字段类型进行查询。如数据库字段version字段定义为str类型，虽然说传递str跟float类型都可以查询到结果，但在使用float时候，数据库对数据进行了校验及处理，从时间上来说，会增加
• 尽可能使用一条命令，如果同时有多条数据需要写入

2.6、数据库引擎

引擎类别分四类：
(1)Innodb：对事务要求高，一般对于重要的数据的存储，建议使用该引擎。比如订单表，账号表等
(2)Myisam：对事务要求不高，同时是以查询和添加为主的，建议使用该引擎，比如BBS系统中的发帖数和回帖数。
(3)Memory：数据变化频繁，不需要入库，同时又频繁的查询和修改，建议使用该引擎，速度相对较快。
(4)Archive：
Myisam与Innodb的区别：
(1)事务安全：MyiSAM不支持事务，Innodb支持事务；
(2)锁机制：前者支持表锁，而后者支持行锁；
(3)外键：前者不支持外键，而后者支持；
(4)查询和添加速度：前者快，后者慢；
(5)全文索引：前者支持，后者不支持；

2.7、预处理

一般来说，实时数据(当天的数据)还是比较有限的，真正数据量比较大的是历史数据，基于大表历史数据的查询，如果再涉及一些大表关联，这种sql是非常难以优化的
a、实时数据(当天数据)
通过对对业务的抽象，可以放在缓存里面，提升系统运行效率
b、历史数据，大数据表历史数据且有表关联，通过常规sql难以优化，但是该数据通常有个共性，就是第二天去查询前一天的数据做分析报表，也就是说对时效性要求不高，这种情况的解决方案是预处理，做法是将这些复杂表关联sql写成个定时任务在半夜执行，将执行的结果存入到一张结果表中，第二天直接查询结果表,如此，效率能得到十分明显提升
c、和b类似，可以将表关联结果存入solr或者elastisearch中，以此提升效率，目前我们的项目就是如此处理