MySQL数据库优化漫谈
共 3828字，需浏览 8分钟
·
2021-06-01 23:45
点击上方蓝色字体，选择“标星公众号”
优质文章，第一时间送达
1.表设计
遵循三范式,但必要的时候做数据冗余，举例说明：在权限模型中可能会用到5张表 用户表、角色表、权限表，还有用户角色关联表和角色权限关联表。如果此时要通过用户查询权限则必须关联查询或者使用多条sql查询，此时可以在用户表增加一个字段来存储用户的权限（例如将权限值使用逗号隔开），这样可以如果查询某用户的权限可以直接在用户表中查询，查询后再用程序来处理。
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 使用索引.
对于已经上线的项目可以开启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%';
使用optimize table 表名;命令来优化表,执行时会占用大量资源,所以建议在用户访问量少的时刻执行。
5.配置优化
该值可以通过使用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%左右，当然要考虑内存的整体占用情况。
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：
https://blog.csdn.net/xiaowuc/article/details/11988753
