优化页面访问速度(二)
——数据库优化
一、概述
数据库优化,主要包括数据表设计、索引、sql语句、表拆分、数据库服务器架构等方向的优化。
二、数据库设计
在建表的时候,就需要考虑到将来的使用场景,尽量在建表初期就设计好。
1、存储引擎
Mysql常被提到的存储引擎就是InnoDB和MySIAM,其实现在主要都在用InnoDB了。两者的区别:
InnoDB支持事务,索引和数据存在一个文件,主键查询速度快(主键就是索引B+树的叶子节点,而数据就绑定在叶子节点),行级锁,支持外键,恢复起来较快。
MySIAM不支持事务,支持全文索引,表级锁, 索引和数据文件分开存放。
InnoDB在众多方面都优于MySIAM,且MySIAM的全文索引其实可以使用其他工具来实现,故常用InnoDB引擎。
另外,如果两个表的引擎不一样,一个是MySIAM另一个是InnoDB,则事务的恢复只会恢复InnoDB的表,这样的事务并不完整,故要求所有的表都是InnoDB。
2、字段
1)字符串
定长的字符串用char比较好,这样修改的时候不会产生碎片,而且利用率高。相对来说,varchar则用于长度不一致的字段。
2)枚举
对于只有几种值的字段,用枚举的效果更好。表示状态的字段,常常可以这么设计。枚举在mysql底层,会转成int存储,效率很高。另外,保存的时候,建议不要直接用1、2这种,可以用英文来表示不同的状态,比较好辨认。
3)数字
不需要负数的时候,可以加上unsigned;需要精确的小数才考虑decimal。
三、索引
索引可以加快查找速度,但是对于增删改,还需要去维护对应的B+树,因此索引是按需设置,不能乱加。
InnoDB的索引,分为主键索引和辅助索引。
1、主键索引
InnoDB的主键索引,是和数据绑定在一起的,因此按照主键来查找时,找到值的时候,也就找到了对应的数据,所以说InnoDB的主键索引查找速度特别快。
如下图所示:
另外可以看到,InnoDB引擎下,数据是绑定在主键的,故InnoDB引擎的表,必须要设置主键。
另外,建议设置逐渐增大的数字作为主键,且不要修改主键,这样避免主键的B+树旋转太多。
2、辅助索引
除了主键,其他的索引统称为辅助索引。其他的索引,是用其他的B+树来存储,子节点上存的是这一行对应的主键的信息。
故用辅助索引查找,找到对应的主键后,还需要去主键的B+树上查找,才可以找到对应的数据。
3、联合索引
当多个字段共同组成索引,则成为联合索引。联合索引需要遵循最左前缀原则。例如建立联合索引a_b_c。
下列语句是可以用到索引的:
Select * from xxx where a = ‘xx’ and b = ‘xx’ and c = ‘xx’;
Select * from xxx where a = ‘xx’ and b = ‘xx’;
Select * from xxx where a = ‘xx’;
Select * from xxx where a = ‘xx’ and b = ‘xx’ and c > ‘xx’;
Select * from xxx where a = ‘xx’ and b > ‘xx’;
Select * from xxx where a > ‘xx’;
下列语句是用不到索引的:
Select * from xxx where b = ‘xx’ and c = ‘xx’;
Select * from xxx where a > ‘xx’ and b = ‘xx’ and c = ‘xx’;
需要说明的是,前面一个字段用到的大于,则后面的字段无法用到索引。因为联合索引的存储是多个B+树的连接;1个字段的叶子节点接下一个字段的根节点。故如果出现大于,则后面的字段无法确定范围,即无法使用到索引。
4、唯一索引
唯一索引是辅助索引的一种,除了索引的功能,还能保证字段在数据库中是唯一的,这对于并发新增有防止重复的作用。
5、建索引注意事项
1)对于where、order by、group by,都可以考虑建索引。
2)索引建立在区分度大的字段上,对于性别这类的字段,建立索引没有意义。
3)对于字符串类型,可以考虑建立前缀索引,例如对于description字段的前7个字符建立索引,语句为:alter table xxtable add key (description(7));
四、SQL语句优化
1、尽量用到索引
1)避免在 where 子句中对字段进行 null 值、!=或<>、in 和 not in 、非打头字母like搜索、表达式操作或者函数操作,这些操作都会导致放弃索引,全表扫描。
2)or,左右两边都应该对索引的列进行查询,只要有一边的列不是索引列,就会导致放弃使用索引。可以考虑用union代替or,这样至少可以有一部分数据用到索引。
3)对于字符串类型,如果里面存的是数字,查询的时候也要记得加上引号,强制转成字符串,这样才能用到索引。
2、其他查询优化
1)如果不需要全量数据,可以考虑用limit。
2)避免用子查询,mysql的子查询,内部执行计划器是这样执行的:先查外表再匹配内表,而不是先查内表,当外表的数据很大时,查询速度会非常慢。
3)对于group by,MySQL对所有GROUP BY col1,col2...的字段进行排序。如果查询包括GROUP BY,想要避免排序结果的消耗,则可以指定ORDER BY NULL禁止排序。
五、表拆分
对于数据量太大的表,可以考虑拆分表,以减少扫描的数据量。
1、横向拆表
当数据量太大,且数据有一定的规律,则可以横向分表。例如交易明细表,可以根据用户id进行分表,把用户id进行hash,不同的hash结果对应到不同的表,再编写一个实现算法,根据id到对应的表进行增删改查即可。
2、纵向拆表
当有一些大字段,且这些字段并不是经常需要查询,则可以独立出一个表,例如文章表可以存文章的标题、概要、日期、关键词等,但是对于文章的具体内容,则可以独立一张表,这样文章列表页速度可以改善。
3、分区
分区是mysql自带的功能,其原理是将一个表的数据存在不同的文件中,由mysql根据内部规则,自动去对应的数据文件找数据。
六、数据库服务器架构
1、读写分离
商业应用上,数据库经常都是读写分离的,通常写在主库,读在从库进行。数据库的主从一致性,是通过中继日志实现的。
每当有数据修改,主库会将sql语句写入中继日志,然后从库会把日志搬到对应的从库日志,再逐条执行sql。
负载均衡,可以通过mycat等插件实现,可以理解为数据库是对底层存储文件的抽象,而Mycat是对数据库的抽象。
原文发布于微信公众号 - 爱思考的coder(phpthinker)