MySQL优化-云栖社区-阿里云
2019年05月10日
原文
MySQL优化
MySQL
1. 优化SQL语句
MySQL慢日志的开启
查看数据库是否开启了慢查询日志 SHOW VARIABLES LIKE 'slow_query_log'; 查看数据库是否将未使用索引的SQL记录到日志中 SHOW VARIABLES LIKE 'log_queries_not_using_indexes'; 查看慢查询日志记录的慢查询日志时间 SHOW VARIABLES LIKE 'log_query_time'; 查看慢查询日志文件存放位置 SHOW VARIABLES LIKE 'slow_query_log_file'; 设置MySQL数据库开启慢查询日志 SET GLOBAL slow_query_log = on; 设置慢查询日志文件存储位置 SET GLOBAL slow_query_log_file=''; 打开慢查询日志存储为设置索引的SQL SET GLOBAL log_queries_not_using_indexes = on; 设置记录的慢查询日志超时时间 SET GLOBAL log_query_time = 1; 慢查询日志的存储格式 #time: 查询的开始时间 #user@host: 查询主机用户信息 #query_time: SQL的执行信息 #set timestamp: SQL执行时间 #select concat :执行的SQL语句
MySQL慢查询日志分析工具
mysqldumpslow -h
通过慢查询日志发现有问题的SQL
1.发现查询次数多且占用时间长的SQL(多:使用基于内存的数据库或使用缓存,长:优化SQL语句) 2.未命中索引的SQL(优化SQL语句添加特定索引优化)
使用explain查看SQL执行计划:查询结果属性说明
列 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
---|---|---|---|---|---|---|---|---|---|---|
说明 | 查询id | 查询类型 | 使用的表 | 类型 | 可能使用的索引 | 应用索引 | 索引长度 | 引用 | 返回行数 | 扩展说明 |
2.优化索引
选择合适的列建立索引
在where从句,group by从句,order by从句,on从句中出现的的列创建索引。 索引列的字段长度越小越好。 离散度大的列放到联合索引的前面。
索引的优化
1.避免使用重复及冗余的索引 2.去掉因业务变更,未使用的索引
3.数据库结构优化
优化数据大小
设计表以最小化磁盘空间,尽量使用最小化的数据大小来存储数据。
优化MySQL数据库数据类型
1.选择合适类型的数据类型,存储数据 2.使用可以存下你的数据的最小数据类型 3.尽可能使用简单的数据类型 4.尽可能使用not null定义字段,采用默认值方式 5.尽量少使用text类型,非用不可时最好使用分表的方式 6.数据库中不要存储二进制文本,图片,音频等数据。而要存储数据的网络路径 7.使用反范式化设计,设计一定的冗余字段,以减少多表联合查询。