课程咨询 :13623629309

太原PHP培训 > 达内新闻 > 太原php培训机构:单机数据库优化的一些实践
  • 太原php培训机构:单机数据库优化的一些实践

    发布:太原PHP培训      来源:达内新闻      时间:2016-09-18

  • 数据库优化有很多可以讲,按照支撑的数据量来分可以分为两个阶段:单机数据库和分库分表,前者一般可以支撑500W或者10G以内的数据,超过这个值则需要考虑分库分表。另外,一般大企业面试往往会从单机数据库问起,一步一步问到分库分表,中间会穿插很多数据库优化的问题。本文试图描述单机数据库优化的一些实践,数据库基于mysql,如有不合理的地方,欢迎指正。

    太原php培训机构

    1、表结构优化

    在开始做一个应用的时候,数据库的表结构设计往往会影响应用后期的性能,特别是用户量上来了以后的性能。因此,表结构优化是一个很重要的步骤。

    1.1、字符集

    一般来说尽量选择UTF-8,虽然在存中午的时候GBK比UTF-8使用的存储空间少,但是UTF-8兼容各国语言,其实我们不必为了这点存储空间而牺牲了扩展性。事实上,后期如果要从GBK转为UTF-8所要付出的代价是很高的,需要进行数据迁移,而存储空间完全可以用花钱扩充硬盘来解决。

    1.2、主键

    在使用mysql的innodb的时候,innodb的底层存储模型是B+树,它使用主键作为聚簇索引,使用插入的数据作为叶子节点,通过主键可以很快找到叶子节点,从而快速获取记录。因此在设计表的时候需要增加一个主键,而且最好要自增。因为自增主键可以让插入的数据按主键顺序插入到底层的B+树的叶子节点中,由于是按序的,这种插入几乎不需要去移动已有的其它数据,所以插入效率很高。如果主键不是自增的,那么每次主键的值近似随机,这时候就有可能需要移动大量数据来保证B+树的特性,增加了不必要的开销。

    1.3、字段

    1.3.1、建了索引的字段必须加上not null约束,并且设置default值

    1.3.2、不建议使用float、double来存小数,防止精度损失,建议使用decimal

    1.3.3、不建议使用Text/blob来保存大量数据,因为对大文本的读写会造成比较大的I/O开销,同时占用mysql的缓存,高并发下会极大的降低数据库的吞吐量,建议将大文本数据保存在专门的文件存储系统中,mysql中只保存这个文件的访问地址,比如博客文章可以保存在文件中,mysql中只保存文件的相对地址。

    1.3.4、varchar类型长度建议不要超过8K。

    1.3.5、时间类型建议使用Datetime,不要使用timestamp,虽然Datetime占用8个字节,而timestamp只占用4个字节,但是后者要保证非空,而且后者是对时区敏感的。

    1.3.6、建议表中增加gmt_create和gmt_modified两个字段,用来记录数据创建的修改时间。这两个字段建立的原因是方便查问题。

    1.4、索引创建

    1.4.1、这个阶段由于对业务并不了解,所以尽量不要盲目加索引,只为一些一定会用到索引的字段加普通索引。

    1.4.2、创建innodb单列索引的长度不要超过767bytes,如果超过会用前255bytes作为前缀索引

    1.4.3、创建innodb组合索引的各列索引长度不要超过767bytes,一共加起来不要超过3072bytes

    2、SQL优化

    一般来说sql就那么几种:基本的增删改查,分页查询,范围查询,模糊搜索,多表连接

    2.1、基本查询

    一般查询需要走索引,如果没有索引建议修改查询,把有索引的那个字段加上,如果由于业务场景没法使用这个字段,那么需要看这个查询调用量大不大,如果大,比如每天调用10W+,这就需要新增索引,如果不大,比如每天调用100+,则可以考虑保持原样。另外,select * 尽量少用,用到什么字段就在sql语句中加什么,不必要的字段就别查了,浪费I/O和内存空间。

    2.2、高效分页

    limit m,n其实质就是先执行limit m+n,然后从第m行取n行,这样当limit翻页越往后翻m越大,性能越低。比如

    select * from A limit 100000,10,这种sql语句的性能是很差的,建议改成下面的版本:

    selec id,name,age from A where id >=(select id from A limit 100000,1) limit 10

    2.3、范围查询

    范围查询包括between、大于、小于以及in。Mysql中的in查询的条件有数量的限制,若数量较小可以走索引查询,若数量较大,就成了全表扫描了。而between、大于、小于等,这些查询不会走索引,所以尽量放在走索引的查询条件之后。

    2.4、模糊查询like

    使用 like %name%这样的语句是不会走索引的,相当于全表扫描,数据量小的时候不会有太大的问题,数据量大了以后性能会下降的很厉害,建议数据量大了以后使用搜索引擎来代替这种模糊搜索,实在不行也要在模糊查询前加个能走索引的条件。

    2.5、多表连接

    子查询和join都可以实现在多张表之间取数据,但是子查询性能较差,建议将子查询改成join。对于mysql的join,它用的是Nested Loop Join算法,也就是通过前一个表查询的结果集去后一个表中查询,比如前一个表的结果集是100条数据,后一个表有10W数据,那么就需要在100*10W的数据集合中去过滤得到最终的结果集。因此,尽量用小结果集的表去和大表做join,同时在join的字段上建立索引,如果建不了索引,就需要设置足够大的join buffer size。如果以上的技巧都无法解决join所带来的性能下降的问题,那干脆就别用join了,将一次join查询拆分成两次简单查询。另外,多表连接尽量不要超过三张表,超过三张表一般来说性能会很差,建议拆分sql。

    3、数据库连接池优化

    数据库连接池本质上是一种缓存,它是一种抗高并发的手段。数据库连接池优化主要是对参数进行优化,一般我们使用DBCP连接池,它的具体参数如下:

    3.1 initialSize

    初始连接数,这里的初始指的是第一次getConnection的时候,而不是应用启动的时候。初始值可以设置为并发量的历史平均值

    3.2、minIdle

    最小保留的空闲连接数。DBCP会在后台开启一个回收空闲连接的线程,当该线程进行空闲连接回收的时候,会保留minIdle个连接数。一般设置为5,并发量实在很小可以设置为1.

    3.3、maxIdle

    最大保留的空闲连接数,按照业务并发高峰设置。比如并发高峰为20,那么当高峰过去后,这些连接不会马上被回收,如果过一小段时间又来一个高峰,那么连接池就可以复用这些空闲连接而不需要频繁创建和关闭连接。

    3.4、maxActive

    最大活跃连接数,按照可以接受的并发极值设置。比如单机并发量可接受的极值是100,那么这个maxActive设置成100后,就只能同时为100个请求服务,多余的请求会在最大等待时间之后被抛弃。这个值必须设置,可以防止恶意的并发攻击,保护数据库。

    3.5、maxWait

    获取连接的最大等待时间,建议设置的短一点,比如3s,这样可以让请求快速失败,因为一个请求在等待获取连接的时候,线程是不可以被释放的,而单机的线程并发量是有限的,如果这个时间设置的过长,比如网上建议的60s,那么这个线程在这60s内是无法被释放的,只要这种请求一多,应用的可用线程就少了,服务就变得不可用了。

    3.6、minEvictableIdleTimeMillis

    连接保持空闲而不被回收的时间,默认30分钟。

    3.7、validationQuery

    用于检测连接是否有效的sql语句,一般是一条简单的sql,建议设置

    3.8、testOnBorrow

    申请连接的时候对连接进行检测,不建议开启,严重影响性能

    3.9、testOnReturn

    归还连接的时候对连接进行检测,不建议开启,严重影响性能

    3.10、testWhileIdle

    开启了以后,后台清理连接的线程会没隔一段时间对空闲连接进行validateObject,如果连接失效则会进行清除,不影响性能,建议开启

    3.11、numTestsPerEvictionRun

    代表每次检查链接的数量,建议设置和maxActive一样大,这样每次可以有效检查所有的链接。

    3.12、预热连接池

    对于连接池,建议在启动应用的时候进行预热,在还未对外提供访问之前进行简单的sql查询,让连接池充满必要的连接数。

    4、索引优化

    当数据量增加到一定程度后,靠sql优化已经无法提升性能了,这时候就需要祭出大招:索引。索引有三级,一般来说掌握这三级就足够了,另外,对于建立索引的字段,需要考虑其选择性。

    4.1、一级索引

    在where后面的条件上建立索引,单列可以建立普通索引,多列则建立组合索引。组合索引需要注意最左前缀原则。

    4.2、二级索引

    如果有被order by或者group by用到的字段,则可以考虑在这个字段上建索引,这样一来,由于索引天然有序,可以避免order by以及group by所带来的排序,从而提高性能。

    4.3、三级索引

    如果上面两招还不行,那么就把所查询的字段也加上索引,这时候就形成了所谓的索引覆盖,这样做可以减少一次I/O操作,因为mysql在查询数据的时候,是先查主键索引,然后根据主键索引去查普通索引,然后根据普通索引去查相对应的记录。如果我们所需要的记录在普通索引里都有,那就不需要第三步了。当然,这种建索引的方式比较极端,不适合一般场景。

    4.4、索引的选择性

    在建立索引的时候,尽量在选择性高的字段上建立。什么是选择性高呢?所谓选择性高就是通过这个字段查出来的数据量少,比如按照名字查一个人的信息,查出来的数据量一般会很少,而按照性别查则可能会把数据库一半的数据都查出来,所以,名字是一个选择性高的字段,而性别是个选择性低的字段。

    5、历史数据归档

    当数据量到了一年增加500W条的时候,索引也无能为力,这时候一般的思路都是考虑分库分表。如果业务没有爆发式增长,但是数据的确在缓慢增加,则可以不考虑分库分表这种复杂的技术手段,而是进行历史数据归档。我们针对生命周期已经完结的历史数据,比如6个月之前的数据,进行归档。我们可以使用quartz的调度任务在凌晨定时将6个月之前的数据查出来,然后存入远程的hbase服务器。当然,我们也需要提供历史数据的查询接口,以备不时之需。

    达内时代科技集团致力于培养面向电信和金融领域JavaC++C#/.Net3G/Android3G/IOSPHP、嵌入式、软件测试、UID、网络营销、网络工程、会计、UEDwebUnity3D、大数据、童程童美等17大方向中高端软件人才课程与少儿教育课程。选择太原php培训,不再孤军奋战,轻轻松松做IT高薪白领。太原达内培训带领有明确目标的学子迈向成功之路!想找工作的求职者可以加QQ3373924515(太原达内就业服务部)咨询了解。

上一篇:太原php培训机构:程序员爱写脚本是种病

下一篇:太原php培训机构:在 PHP 7 中不要做的 10 件事

最新开班日期  |  更多

php高级开发名企定制班(剩2个名额)

php高级开发名企定制班(剩2个名额)

开班日期:12-30

php高级开发周末班(剩5个名额)

php高级开发周末班(剩5个名额)

开班日期:12-30

php高级开发免费试听(剩5个名额)

php高级开发免费试听(剩5个名额)

开班日期:12-30

更多高级开发工程师精品班

更多高级开发工程师精品班

开班日期:12-30

  • 地址:山西省太原市小店区学府街长治路高新国际A座24层
  • 课程培训电话:13623629309     全国服务监督电话:400-827-0010
  • 服务邮箱 ts@tedu.cn
  • 2001-2016 达内国际公司(TARENA INTERNATIONAL,INC.) 版权所有 京ICP证08000853号-56

    在线客服系统