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中,以此提升效率,目前我们的项目就是如此处理