【new】MySQL数据库优化漫谈


2013年写了《mysql数据库优化漫谈》发在老博客地址http://old.zhanglirong.cn/cxrs/4.html,这几年技术变化日新月异,mysql和相关技术架构经过4年的发展演进已经和从前大不相同,今天再次看到原文发现其中有一些观点与现今的技术开发不符,所以有了今天这篇文章。

1、表设计

    遵循三范式,但必要的时候做数据的冗余存储。举例说明:在权限模型中可能会用到5张表 用户表、角色表、权限表,还有用户角色关联表和角色权限关联表。如果此时要通过用户查询权限则必须关联查询或者使用多条sql查询,此时可以在用户表增加 一个字段来存储用户的权限(例如将权限值使用逗号隔开),这样可以如果查询某用户的权限可以直接在用户表中查询,查询后再用程序来处理。

    设计表时要选用合适的存储引擎,mysql比较常用的存储引擎有:MyISAM、InnoDB、Memory等。在以往的架构设计中会把查询量大的并且不需要事务的表选用MyISAM引擎存储,需要事务处理和对稳定性要求高的表优先选用InnoDB存储引擎。目前InnoDB已经成为新版MySQL的默认存储引擎,在高并发写入时的稳定性相比MyISAM更高,在读取速度上也比以前更快,基本上和MyISAM持平,所以没有极其特殊的使用场景外,推荐使用此引擎。不需要永久保存的数据可以选用Memory存储引擎,现在内存存储技术也非常成熟,类似数据可以考虑Memcache、Redis等技术方案。

2、分表技术

    横向分表(各个表的字段类型与数量是完全一致的),例如用户表,可以按用户的UID做取余计算横向切分;横向分表后对于数据的聚合查询、分页查询不好处理,可以使用如MyCat等工具MySQL代理。单表能存储下的数据,尽量不要分多张表。

    纵向分表(各个表的字段不一致,但条数是一致的),例如订单表(据说快的初期的架构中订单表有90多个字段),拆分后可以有orders订单主表 orders_goods订单商品表 orders_list拆分后的订单列表。用户表可以拆分为 user主表  user_account用户账户表  user_openid用户第三方授权openid信息。纵向分表一般有个原则:当20%的字段承担80%的sql操作,可以把其余的80%的字段拆分成新表。这样就可以将主表里的字段分离出来组成多张新表,和主表关联,既满足高效查询,右满足稳定性。

    另外还可以进行分区,或者分库,都是类似的思路。关于mysql分区的文章mysql的分区技术

3、索引优化

    常见的索引有 主键索引、唯一索引、普通索引、全文索引(仅MyISAM存储引擎支持,并且不支持中文,如果需要支持中文需要安装插件,myisam全文索引的详细方案见另一篇文章MySQL全文检索初探)。通常需要在条件字段、排序字 段、分组字段以及关联字段上建立对应索引。但重复率很高的字段不宜建立索引,比如状态字段(是否被删除,是否上架等),emum类型(性别等),另外建立 索引会对增删改操作的速度有影响,因此频繁更新的字段不适合建立索引,例如文章的点击量。

4、SQL优化

    在开发时可以使用explain 测试sql语句
    在测试结果会有如下参数,简要解释一下
    select_type   
    查询的方式 SIMPLE表示select类型,没有连接或者子查询,PRIMARY表示主查询(注意:不是主键,例如子查询时的外层查询,UNION查询时的第一个 select),DEPENDENT SUBQUERY表示子查询语句,UNION表示UNION查询时除了PRIMARY(第一条语句)之外的语句
    table        表名
    type        扫描类型(重要)如果是All表示全表扫描,效率低;如果是const表示最多有一行与结果匹配,效率高;system表示表中仅有一条数据,肯定高 效;eq_ref表示所以用到主键或者唯一索引;ref表示用到普通索引;range表示查询一个区间(范围的数据);index表示都是通过索引查询性 能一般
    possible_keys    可能使用到的索引(重要)
    key        实际用到的索引(重要)
    key_len        索引长度
    ref
    rows        MySQL认为它执行查询时必须检查的行数(重要)越小越好       
    Extra        额外信息 Using filesort表示查询中使用了order by 并且无法利用索引排序,如果确实不需要排序可以在SQL语句末尾增加order by null.Using temporary某些操作使用了临时表,不要.Using where 使用索引.

    编写代码以及SQL语句需要注意的如下几项:

    1)如果查询条件中有or,则所有使用到的字段必须都有索引才有效;

    2)like查询条件中如果以%开头则索引失效;

    3)联合(多列)索引 只有使用左边的列索引才能生效;

    4)where后有多个查询条件应优先使用能够过滤数据量大的条件(先后顺序很重要);

    5)不要使用子查询(效率很低,除非数据量非常少),尽可能少用关联查询,宁可份多次查询,有条件可以封装成存储过程。

        6)不要在循环中使用SQL语句,如果是查询尽可能一次查询然后通过程序来计算。如果是insert可使用扩展插入,即类似insert into tablename values (11,22),(33,33),(45,42)。
    检测MySQL的索引使用情况可以使用以下命令
    show global status where Variable_name like 'Handler_read%';
    解释一下主要参数
    Handler_read_first 全索引扫描的次数,使用到了索引,但是全部索引;
    Handler_read_key 越大越好,使用索引的情况;
    Handler_read_rnd_next 表示没有命中,值越大索引利用率越低;

    对于已经上线的项目可以开启MySQL的慢查询来定位低效率的SQL,见 使用MySQL的慢查询日志找到低效的SQL语句并通过explain分析进行优化
    使用如下命令
    show global status like 'Com%';
    show global status like 'InnoDB_rows%';
    show variables like  'long_query_time';
    show variables like  '%slow%';

    查看MySQL各种SQL的执行频度 以及慢查询状态

    使用optimize table 表名;命令来优化表,执行时会占用大量资源,所以建议在用户访问量少的时刻执行。

5、配置优化

    修改mysql的配置文件,通常在windows下为my.ini,在Linux下为my.cnf

    query_cache_size = 64M

    该值可以通过使用SHOW STATUS LIKE 'Qcache%';命令来查看MySQL状态来进行相应的更改,如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情 况需要增加cache值;如果Qcache_hits的值非常大,则表明查询缓冲使用非常频繁,是理想状态,如果该值较小反而会影响效率,那么可以考虑不 用查询缓冲。
    对于MyISAM设置:
    key_buffer_size = 32M
    MyISAM表会使用操作系统的缓存来缓存数据,设置的值取决于系统内存、索引大小、数据量以及负载。
    对于InnoDB设置:
    innodb_buffer_pool_size = 2.4G
    可以设置内存的70%左右,当然要考虑内存的整体占用情况。

6.硬件架构设计优化

    更换硬件 硬盘更换为SSD 可提高几倍的效率,
    延时写入(更新),对于实时性较小的数据可以先将数据写入到队列,每隔一段时间批量写入(更新)到数据库中
    搭建MySQL主从同步架构,使用读写分离技术
    使用sphinx、lucene、ES全文检索服务器, 减轻MySQL服务器的压力

上一篇 下一篇


评论



分享

最新加入

最新评论

逍遥浪子: <script>alert('hello world');</script> 查看原文 11月01日 13:48
逍遥浪子: <script>alert(123);</script> 查看原文 11月01日 13:40
666_e_com: zzz 查看原文 06月09日 18:05
马利时代: <a href="http://www.baidu.com#"onclick="alert('xss')"">用户填写的连接描述</a> 查看原文 05月10日 18:19
马利时代: <img src="http://snoopyxdy.blog.163.com/blog/err" onerr="alert('xss')" /> <script>alert('xss')</script> 查看原文 05月10日 18:17




0.1837s