#yyds干货盘点#MySql数据库保姆级优化方案

原创

低代码布道者 2021-12-10 14:08:50 博主文章分类：数据库 ©著作权

文章标签 字段数据库数据慢查询缓存 文章分类 MySQL 数据库 yyds干货盘点

©著作权归作者所有：来自51CTO博客作者低代码布道者的原创作品，请联系作者获取转载授权，否则将追究法律责任

项目的性能，一般首先会从数据库开始优化，因为系统的数据目前来说还是主要存储在数据库中，该手册也是给真实客户项目Mysql数据库优化方案手册，其它数据库也可以做参考，原理都相差不会大，应该是比较详情的操作步骤了，如果有问题可以留言交流，或者给我发私信也可以。
如果51CTO同行喜欢，还请给个关注

一、数据库设计优化

根据业务需求选择正确的存储引擎，如果对事务性要求高就用InnoDB（默认），读多写少尽量使用MyISAM。
设置合理的字段类型及字段长度，比如说你这个字段就20多个字段你设置成VARCHAR(255)就是对磁盘空间的浪费。
默认值尽可能的使用 NOT NULL，可以通过设置默认值避免NULL值。
尽量少的使用VARCHAR，TEXT，BLOB这三个字段。
添加适当索引(index) [普通索引、主键索引、唯一索引unique、全文索引]。
不要滥用索引，大表索引，小表不索引。
表的设计合理化（符合3NF）。

二、SQL语句优化

优化利器 -- EXPLAIN

EXPLAIN
SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'iacl_cmp' 
UNION SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'iacl_cmp' 
UNION SELECT COUNT(*) FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'iacl_cmp';

运行结果

#yyds干货盘点#MySql数据库保姆级优化方案_数据库

主要关注4个指标：

type：优化目标至少达到range级别，要求是ref级别，如果可以const最好。
type 性能从高到低

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

#yyds干货盘点#MySql数据库保姆级优化方案_字段_02

key：是查询使用到的索引，如果此列为空，要么未建立索引，要么索引失效。
rows：是这条SQL语句扫描的行数，越少越好。
extra：此列为扩展列，如果出现临时表、文件排序则需要优化。

where、= 条件避免类型转换入参类型在传入前转换成跟字段类型一致内关联避免也要避免类型不一致做过滤
避免使用 select *
like引起索引失效，尽量满足最左匹配原则，建议这样写：where display_name like '开始%';
不要在where条件中带有函数计算、类型转换
关于in和exist，如果查询的俩个表大小一致则性能差别可忽略，如果子查询表大用exist，否则使用in
预知查询一条数据要加上limit 1，以便查到数据终止扫描
关联查询尽量不要超过 3 个表关联
数据字段更新频繁、重复度高不适合建索引
explain中的type至少要达到range，建议为ref
尽量用join代替子查询
尽量用union all代替union
数据优化常用命令
查看Mysql服务器的链接
show full processlist;
查询慢查询次数
show status like 'slow_queries';
查看慢查询设置的时间，默认10s
show variables like 'long_query_time';
设置慢查询时间、set long_query_time = 1;
查看建表语句
show create table wf_node;
查看表的字段
desc wf_node;
查看表索引
show indexes from wf_node;
创建索引主键索引
alter table wf_node add primary key(id);
唯一索引
alter table wf_node add UNIQUE (gmt_create);
普通索引
alter table wf_node add index idx_display_name (display_name);
复合索引
ALTER TABLE wf_node ADD INDEX idx_display_name_gmt_create (display_name, gmt_create);
删除索引
drop index idx_display_name on wf_node;
重建索引
OPTIMIZE TABLE wf_node;

硬件优化

CPU：配置多核心和频率高的cpu,多核心可以执行多个线程
内存：配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.
磁盘：配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.

数据库本身参数配置

key_buffer_size：索引缓冲区大小
table_open_cache：指定表高速缓存的大小
query_cache_size和query_cache_type：前者是查询缓冲区大小,后者是前面参数的开关,0表示不使用缓冲区,1表示使用缓冲区,但可以在查询中使用SQL_NO_CACHE表示不要使用缓冲区,2表示在查询中明确指出使用缓冲区才用缓冲区,即SQL_CACHE
sort_buffer_size：排序缓冲区