MySQL数据库优化漫谈

点击上方蓝色字体，选择“标星公众号”

优质文章，第一时间送达

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

    设计表时要选用合适的存储引擎，对于经常查询的表，并且不需要事务等应用，优先选用MyISAM（例如新闻表），如果需要事务处理对稳定性要求高的表优先选用InnoDB存储引擎（例如订单表，订单商品表），目前InnoDB已经成为新版MySQL的默认存储引擎，在高并发写入时的稳定性相比MyISAM更高,在读取速度上速度也比较快，所以推荐使用此引擎。不需要永久保存的数据可以选用Memory存储引擎（例如session，购物车）

    对于不同的字段选用合适的字段类型，例如IP地址应该存储为整型，见将IP以整型的方式存储到数据库中。固定长度的字符串要使用char，例如md5加密的密码。

2.分表技术
    横向分表（各个表的字段类型与数量是完全一致的），例如用户表，可以按用户首字母横向切分；交易记录可以按照年份或者月份切分，每张表的字段一致，至于按什么规则来来区分可以看具体的业务。
    纵向分表（各个表的字段不一致，但条数是一致的），例如某商城网站用户表使用MyISAM引擎，可以应对用户登录时的查询操作，但是用户之间转账需要事务来保证安全，这样就可以将用户余额字段分离出来组成一张InnoDB引擎的新表，和用户表关联，既满足高效查询，又满足稳定性。
    另外还可以进行分区，或者分库，都是类似的思路。
3.索引优化
    常见的索引有 主键索引、唯一索引、普通索引、全文索引（仅MyISAM存储引擎支持，并且不支持中文，如果需要支持中文需要安装插件）。通常需要在条件字段、排序字段、分组字段以及关联字段上建立对应索引。但重复率很高的字段不宜建立索引,比如状态字段(是否被删除,是否上架等),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 全文检索服务器， 减轻MySQL服务器的压力

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文链接：

https://blog.csdn.net/xiaowuc/article/details/11988753