Msql千万级优化

雨点打透心脏的1/2处 2022-05-11 00:33 305阅读 0赞

## 前言 ##

## 1、索引的使用优化 ##

####  ####

#### 1、 exists 代替 in ####

select id from t where num in(1,2,3) 
    对于连续的数值，能用 between 就不要用 in 了： 
    select id from t where num between 1 and 3 
    
    
    很多时候用 exists 代替 in 是一个好的选择： 
    select num from a where num in(select num from b) 
    用下面的语句替换： 
    select num from a where exists(select 1 from b where num=a.num)

#### 2、对查询进行优化，应尽量避免全表扫描，，首先应考虑再在where和order by涉及列上建立索引 ####

#### 3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，（除非，字段的名称和索引名称相同） ####

select id from t where num is null 
    可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： 
    select id from t where num=0 
    
    
    今后建议 default '',给一个空串，空串不占内存空间，NULL是占内存空间的

#### 4、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。 ####

PS:
    	between and
    	in
    	>   >=  <   <=  操作
    	注意：!= 和 <> 符号 不会使用索引，而是全表扫描

#### 5、应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，（除非，or的字段两边都是单独索引） ####

select id from t where num=10 or num=20 
    可以这样查询： 
    select id from t where num=10 
    union 
    select id from t where num=20

#### 6、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如： ####

select id from t where num/2=100 
    应改为: 
    select id from t where num=100*2

#### 7、并不是所有的查询索引都有效，当sql中有大量数据重复时候，比如性别，sex。这样数据项其实很少。所以一般没有必要在它上面简历索引。 ####

#### 8、任何点不要使用select \* from table ，需要什么返回什么（相当关键，用具体的字段来代替\*） ####

#### 9、其他的请查看 本人博客 索引入门讲解，.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。 ####

#### 10、尽量给where条件and使用大量，尽量创建复合索引 ####

## 1、千万级数据优化 ##

### 1.1、制作千万级数据 ###

#### 注意：尽量使用nvicate执行，不要使用idea ####

CREATE TABLE `demo_entity`
    (
      `id`      bigint(20) NOT NULL AUTO_INCREMENT,
      `name`    varchar(128)        DEFAULT NULL,
      `age`     bigint(20)          DEFAULT '0',
      `country` varchar(50)         DEFAULT NULL,
      `a`   varchar(20)         DEFAULT NULL,
      `b`   varchar(20)         DEFAULT NULL,
      `c`   varchar(20)         DEFAULT NULL,
      `d`   varchar(20)         DEFAULT NULL,
      `e`   varchar(20)         DEFAULT NULL,
      `f`   varchar(20)         DEFAULT NULL,
      `g`   varchar(20)         DEFAULT NULL,
      `h`   varchar(20)         DEFAULT NULL,
      `i`   varchar(20)         DEFAULT NULL,
      `j`   varchar(20)         DEFAULT NULL,
      `k`   varchar(20)         DEFAULT NULL,
      `l`   varchar(20)         DEFAULT NULL,
      `m`   varchar(20)         DEFAULT NULL,
      `n`   varchar(20)         DEFAULT NULL,
      `o`   varchar(20)         DEFAULT NULL,
      `p`   varchar(20)         DEFAULT NULL,
      `q`   varchar(20)         DEFAULT NULL,
      `r`   varchar(20)         DEFAULT NULL,
      `s`   varchar(20)         DEFAULT NULL,
      `t`   varchar(20)         DEFAULT NULL,
      `u`   varchar(20)         DEFAULT NULL,
      `v`   varchar(20)         DEFAULT NULL,
      `w`   varchar(20)         DEFAULT NULL,
      `x`   varchar(20)         DEFAULT NULL,
      `y`   varchar(20)         DEFAULT NULL,
      `z`   varchar(20)         DEFAULT NULL,
      `cdate`   timestamp  NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `udate`   timestamp  NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`)
    ) ;
    
    
    
    create procedure aa()
    begin
      DECLARE i INT DEFAULT 1;
      SET AUTOCOMMIT=0;
        WHILE (i <= 10000000) DO
        INSERT INTO demo_entity
        (name, age, country, a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u,
         v, w, x, y, z)
        VALUES (concat('name',i ), i, concat('country',i ) , ROUND(10*rand()), ROUND(10*rand()), ROUND(100*rand()), ROUND(100*rand()), ROUND(1000*rand()), ROUND(1000*rand()), ROUND(1000*rand()), ROUND(10000*rand()), ROUND(10000*rand()), ROUND(100000*rand()), ROUND(100000*rand()), ROUND(100000*rand()), ROUND(1000000*rand()), ROUND(10000000*rand()), ROUND(10000000*rand()), ROUND(100000000*rand()), ROUND(10000000000*rand()), ROUND(10000000000*rand()), ROUND(100000000*rand()),
                '1', '1', '1', '1', '1', '1', '1');
        SET i = i + 1;
        END WHILE;
      SET AUTOCOMMIT=1;
    end;
    
    call aa();

### 1.2、数据量造成的影响 ###

#### 解释：表中的字段越多下面的优化越明显，否则即使使用了下面的优化，也可能没有那么明显 ####

### 1.3、常见分页优化 ###

select * from tb_ams_inf_repay_stat limit 0,10 ; 
    # 0.003s
    
    select * from tb_ams_inf_repay_stat  limit 10000,10 ;  
    # 1万 0.023s
    
    select * from tb_ams_inf_repay_stat  limit 100000,10 ;
    # 10万 0.191s
    
    select * from tb_ams_inf_repay_stat limit 1000000,10 ;
    # 100万 1.942s
    
    select * from tb_ams_inf_repay_stat limit 10000000,10 ;
    # 1000万 37.323s

通过上面的可以观察到 当达到1000万的时候，查询时间到了37s，太可怕了

#### 1.3.1、优化1： 0.23s 简直要飞起来了 ####

0.23s 
    
    select *
    from tb_ams_inf_repay_stat
    where id > (select id from tb_ams_inf_repay_stat limit 1000000, 1)
    limit 0,10 ;

##### 原理： #####

##### 1、先使用覆盖索引index查询 ，我们只查询id索引这一个字段，比`select *` 或者多个字段快多了，因为只要我们写上这些字段，我们只需要10个，但是从第一条开始到 1000万条其实是都要去扫描的 #####

##### 2、然后再进行索引范围内range查询 #####

<table> 
 <tbody> 
  <tr> 
   <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> 
  <tr> 
   <td>1</td> 
   <td>PRIMARY</td> 
   <td>tb_ams_inf_repay_stat</td> 
   <td>range</td> 
   <td>PRIMARY</td> 
   <td>PRIMARY</td> 
   <td>8</td> 
   <td>NULL</td> 
   <td>3258410</td> 
   <td>Using where</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>SUBQUERY</td> 
   <td>tb_ams_inf_repay_stat</td> 
   <td>index</td> 
   <td>NULL</td> 
   <td>idx_orgcd_loannum</td> 
   <td>216</td> 
   <td>NULL</td> 
   <td>19753500</td> 
   <td>Using index</td> 
  </tr> 
 </tbody> 
</table>

\#\#\#\# 1.3.2、优化2: 0.31 jon

SELECT *
    FROM tb_ams_inf_repay_stat a
           JOIN (select id from tb_ams_inf_repay_stat limit 1000010, 10) b ON a.ID = b.id

<table> 
 <tbody> 
  <tr> 
   <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> 
  <tr> 
   <td>1</td> 
   <td>PRIMARY</td> 
   <td>&lt;derived2&gt;</td> 
   <td>ALL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>1000020</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>PRIMARY</td> 
   <td>a</td> 
   <td>eq_ref</td> 
   <td>PRIMARY</td> 
   <td>PRIMARY</td> 
   <td>8</td> 
   <td>b.id</td> 
   <td>1</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>DERIVED</td> 
   <td>tb_ams_inf_repay_stat</td> 
   <td>index</td> 
   <td>NULL</td> 
   <td>idx_orgcd_loannum</td> 
   <td>216</td> 
   <td>NULL</td> 
   <td>19753500</td> 
   <td>Using index</td> 
  </tr> 
 </tbody> 
</table>

### 1.4、其他优化 ###

#### 1.4.1、适合带有条件的，id连续的查询 ####

0.03s 
    select * from tb_ams_inf_repay_stat  where id  between 1000000 and 1000010  	 ;

#### 1.3.2、带有条件id不连续的查询，考虑建立索引 ####

20s 慢死了
    select * from tb_ams_inf_repay_stat  	where org_cd = 'xmsd'  	limit 1000000,10 ;

select *
    from tb_ams_inf_repay_stat
    where org_cd = 'xmsd'
      and id > (select id from tb_ams_inf_repay_stat where org_cd = 'xmsd' limit 1000000,1)
    limit 0,10 ;
    
    0.2s 可以说相当的快了

<table> 
 <tbody> 
  <tr> 
   <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> 
  <tr> 
   <td>1</td> 
   <td>PRIMARY</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>~~~~</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>SUBQUERY</td> 
   <td>tb_ams_inf_repay_stat</td> 
   <td>ref</td> 
   <td>idx_orgcd_loannum</td> 
   <td>idx_orgcd_loannum</td> 
   <td>93</td> 
   <td>const</td> 
   <td>1</td> 
   <td>Using where; Using index</td> 
  </tr> 
 </tbody> 
</table>

![ContactAuthor][]

[ContactAuthor]: /images/20220511/1488b7962eff49738f577dbf7028f182.png