MySQL优化

素颜马尾好姑娘i 2022-02-12 23:13 209阅读 0赞

#  #

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执行计划：查询结果属性说明 ###

<table> 
 <thead> 
  <tr> 
   <th>列</th> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>说明</td> 
   <td>查询id</td> 
   <td>查询类型</td> 
   <td>使用的表</td> 
   <td>类型</td> 
   <td>可能使用的索引</td> 
   <td>应用索引</td> 
   <td>索引长度</td> 
   <td>引用</td> 
   <td>返回行数</td> 
   <td>扩展说明</td> 
  </tr> 
 </tbody> 
</table>

## 2.优化索引 ##

### 选择合适的列建立索引 ###

在where从句，group by从句，order by从句，on从句中出现的的列创建索引。
    索引列的字段长度越小越好。
    离散度大的列放到联合索引的前面。

### 索引的优化 ###

1.避免使用重复及冗余的索引
    2.去掉因业务变更，未使用的索引

## 3.数据库结构优化 ##

### 优化数据大小 ###

设计表以最小化磁盘空间，尽量使用最小化的数据大小来存储数据。

### 优化MySQL数据库数据类型 ###

1.选择合适类型的数据类型，存储数据
    2.使用可以存下你的数据的最小数据类型
    3.尽可能使用简单的数据类型
    4.尽可能使用not null定义字段，采用默认值方式
    5.尽量少使用text类型，非用不可时最好使用分表的方式
    6.数据库中不要存储二进制文本，图片，音频等数据。而要存储数据的网络路径
    7.使用反范式化设计，设计一定的冗余字段，以减少多表联合查询。