MySQL性能优化基本概念

╰半夏微凉° 2022-06-18 10:24 229阅读 0赞

### 优化简介 ###

db管理员可以使用`SHOW STATUS`语句查询MySQL的性能.

> SHOW STATUS LIKE 'value';
    
    value是以下几个常用统计参数
    
    - Connections: 连接MySQL服务器的次数
    - Uptime: MySQL服务器的上线时间
    - Slow_queries: 慢查询的次数
    - Com_select: 查询操作的次数
    - Com_insert: 插入操作的次数
    - Com_update: 更新操作的次数
    - Com_delete: 删除操作的次数

### 优化查询 ###

#### 分析查询语句 ####

> EXPLAIN|DESCRIBE SELECT * FROM student \G;
    MariaDB [carltest]> explain select * from student \G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: student
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 4
            Extra:
    1 row in set (0.24 sec)
    
    ERROR: No query specified
    
    MariaDB [carltest]> desc select * from student \G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: student
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 4
            Extra:
    1 row in set (0.00 sec)
    
    ERROR: No query specified

*    
    
 *  id: 表示select语句的编号
 *  select\_type: 表示select语句的类型. SIMPLE表示简单查询, 其中不包括子查询和连接查询; PRIMARY表示主查询, 或者是最外层的  
    查询语句; UNION表示连接查询的第二个或后面的查询语句
 *  table: 表示查询的表
 *  type: 表示表的连接类型

>  *  system: 表示表中只有一条记录  
>     
>  *  const: 表示表中有多条记录, 但是只从表中查询一条记录  
>     
>  *  ALL: 表示对表进行了完整的扫描;  
>     
>  *  eq\_ref: 表示多表连接时, 后面的表使用了UNIQUE或PRIMARY KEY  
>     
>  *  ref: 表示多表查询时, 后面的表使用了普通索引;  
>     
>  *  unique\_subquery: 表示子查询中使用了UNIQUE或PRIMARY KEY  
>     
>  *  index\_subquery: 表示子查询中使用了普通索引  
>     
>  *  range: 表示查询语句中给出了查询范围  
>     
>  *  index: 表示对表中的索引进行了完整的扫描
>  *  possible\_keys: 表示查询中可能使用的索引
>  *  key: 表示查询使用到的索引
>  *  key\_len: 表示索引字段的长度
>  *  ref: 表示使用哪个列或常数与索引一起来查询记录
>  *  rows: 表示查询的行数
>  *  Extra: 表示查询过程的附件信息
> 
>   
>

#### 使用索引查询 ####

##### 注意下面添加索引前后, 两次插叙 `rows`的结果, 一个为4, 一个为1. #####

MariaDB [carltest]> explain select * from student where name="李四" \G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: student
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 4
            Extra: Using where
    1 row in set (0.00 sec)
    
    ERROR: No query specified
    
    MariaDB [carltest]> create index index_name on student(name);
    Query OK, 0 rows affected (0.44 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [carltest]> explain select * from student where name="李四" \G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: student
             type: ref
    possible_keys: index_name
              key: index_name
          key_len: 62
              ref: const
             rows: 1
            Extra: Using index condition
    1 row in set (0.00 sec)
    
    ERROR: No query specified

##### LIKE关键字的使用方式. 如果匹配字符串的第一个字符为’%’, 索引不会被使用, 否则, 索引会被使用 #####

MariaDB [carltest]> explain select * from student where name like "%四" \G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: student
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 4
            Extra: Using where
    1 row in set (0.00 sec)
    
    ERROR: No query specified
    
    MariaDB [carltest]> explain select * from student where name like "李%" \G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: student
             type: range
    possible_keys: index_name
              key: index_name
          key_len: 62
              ref: NULL
             rows: 1
            Extra: Using index condition
    1 row in set (0.06 sec)
    
    ERROR: No query specified

##### 多列索引的用法. 只有查询条件中使用了这些字段中第一个字段时, 索引才会被使用. #####

MariaDB [carltest]> create index index_birth_department on student(birth, department);
    Query OK, 0 rows affected (0.12 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [carltest]> explain select * from student where birth=1991 \G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: student
             type: ref
    possible_keys: index_birth_department
              key: index_birth_department
          key_len: 2
              ref: const
             rows: 1
            Extra:
    1 row in set (0.00 sec)
    
    ERROR: No query specified
    
    MariaDB [carltest]> explain select * from student where department="英语系" \G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: student
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 4
            Extra: Using where
    1 row in set (0.00 sec)
    
    ERROR: No query specified

##### OR关键字索引用法. OR前后两个条件的列都是索引时, 查询将使用索引. 只要有一列不是索引, 那么查询就不会使用索引 #####

MariaDB [carltest]> explain select * from student where name='王六' or sex='女' \G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: student
             type: ALL
    possible_keys: index_name
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 4
            Extra: Using where
    1 row in set (0.00 sec)
    
    ERROR: No query specified
    
    # id列是索引
    MariaDB [carltest]> explain select * from student where name='王六' or id=901 \G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: student
             type: ALL
    possible_keys: PRIMARY,index_name
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 4
            Extra: Using where
    1 row in set (0.00 sec)
    
    ERROR: No query specified

##### MySQL参数优化 #####

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>key_buffer_size</td> 
   <td>索引缓存的大小, 该值越大, 使用索引查询速度越快</td> 
  </tr> 
  <tr> 
   <td>table_cache</td> 
   <td>表示同时打开的表的个数. 该值越大, 能同时打开的表的个数越多. 但不是越大越好, 因为同时打开的表太多会影响操作系统的性能</td> 
  </tr> 
  <tr> 
   <td>query_cache_size</td> 
   <td>查询缓存区大小.</td> 
  </tr> 
  <tr> 
   <td>query_cache_type</td> 
   <td>查询缓冲区的开启状态. 0-关闭, 1-开启, 2-按要求使用</td> 
  </tr> 
  <tr> 
   <td>max_connections</td> 
   <td>数据库的最大连接数. 也不是越大越好, 太多连接会浪费内存资源</td> 
  </tr> 
  <tr> 
   <td>sort_buffer_size</td> 
   <td>排序缓冲区大小. 该值越大, 排序速度越快</td> 
  </tr> 
  <tr> 
   <td>innodb_buffer_pool_size</td> 
   <td>InnoDB类型的表和索引的最大缓存. 该值越大, 查询速度越快. 但是太大了也会影响系统性能</td> 
  </tr> 
  <tr> 
   <td>innodb_flush_log_at_trx_commit</td> 
   <td>0-每隔1s将数据写入log, 并将log写入磁盘; 1-每次提交事务时写log写磁盘; 2-每次提交事务时写log, 每隔1s将log写入磁盘, 默认值为1</td> 
  </tr> 
 </tbody> 
</table>