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服务器的压力