MySQL优化策略小记

我会带着你远行 2022-06-06 12:47 282阅读 0赞

# MySQL优化 #

## 表的优化与列类型选择 ##

### 一、表的优化 ###

1.  定长与变长分离  
    如id int, 占4个字节，char(4)占4个字符长度，  
    即：每一个单元值占的字节是固定的。核心且常用的字段，宜建成定长，放在一张表中。  
    而varchar、text、blob等。这种变长的字段，适合单放一张表中，用主键与核心表关联起来。
2.  常用字段和不常用字段要分离。  
    需要结合网站具体业务来分析字段的查询场景，查询频度低的字段，单拆出来。
3.  在1对多，需要关联统计的字段上，添加冗余字段。

### 二、列的选择 ###

1.  字段类型优先级。整型>date、time>enum,char>varchar>blob、text  
    列特点分析:  
    
    
     *  整型：定长，没有国家地区之分，没有字符集差异。比如：tinyint 1、2、3、4、5 <-char(1) a、b、c、d、e, 从空间上都是占一个字节，但是order by排序，前者更快。因为后者需要考虑字符集和校对集（就是排序规则）。
     *  time：定长，运算快,节省空间。考考虑时区，写sql时不方便，where ‘2017-09-11’。
     *  enum：能起约束值的目的，内部用整型来存储，但是与char联查时，内部要经历串与值的转化。
     *  char：定长，考虑字符集和（排序）校对集。
     *  varchar：不定长要考虑字符集的转换和排序时的校对集，速度慢。
     *  text/Blob：无法使用内存临时表（排序等操作只能在磁盘一进行）。  
        
        
        > 如：性别，以utf8为例  
        > 1. char(1) 3个这字长字节。  
        > 2. enum(‘男’,’女’) 内部转成数字来存,多了一个转换过程。  
        > 3. tinyint() 定长一个字节 0、1、2。
2.  够用就行，不要慷慨（如smallint, varchar(N)）  
    大的字段浪费内存影响速度。
    
    > 以年龄为例：tinyint unsigned not null，用存储到255岁足够用，用int会浪费三个字节。  
    > varchar(10)和varchar(100)存储的内容相同,但在表联查时,后者会花更多内存。
3.  尽量避免用NULL()  
    NULL不利于索引，要用特殊字节来标注，在磁盘上占据的空间也更大，mysql5.5已对NULL做改进，但查询仍然不便
    
    > 例如：
    > 
    >  *  可以建立两张字段相同的表，一个允许为NULL，一个不允许为NULL，各自加入一万条数据，查看索引文件的大小，可以发现为NULL的索引要大一些。
    >  *  另外：NULL也不便于查询  
    >     
    >     
    >      *  where 列名 = null;
    >      *  where 列名 != null; 都查不到值
    >      *  where 列名 is null 或 is not null 才能查到值
4.  Enum列的查询  
    
    
     *  enum列在内部是用整型来储存的。
     *  enum列与enum列相关联速度最快。
     *  enum列比(var)char 的弱势：在碰到与char关联时，要转化，要花时间; 优势：当char非常长时，enum仍然是整型固定长度。当查询量越大时enum优势越明显。
     *  enum与char/varchar 关联，因为要转化，速度要比enum -> enum、char -> char要慢，但有时也这样用：数据量特别大时，可以节省IO

## 索引优化策略 ##

### 一、索引类型 ###

1.  B-tree 索引  
    名叫B-tree索引，大的方面看都用的是平衡树，但具体实现上，各引擎稍有不同，

> 比如：严格的说，NDB引擎使用的是T-tree，Myisam,InnoDB中默认的是B-tree索引，但抽象一下B-tree系统可理解为“排好序的快速查找结构”。

B-tree的常见误区

*  在where常用的列上都加上索引。例如：where id = 3 and price 100; 查询id为3价格大于100的  
    
    
     *  id和price上都加上索引
     *  只能用上id和price索引，因为是独立的索引，同时只能使用上1个
 *  在多列上建立索引后，查询哪个列，索引都将发挥作用  
    
    
     *  多列索引上，索引发挥作用，需要满足左前缀要求，以index(a,b,c)为例（注意只和顺序有关），见下表

<table> 
 <thead> 
  <tr> 
   <th>SQL语句</th> 
   <th align="center">索引能否发挥作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>where a = 3</td> 
   <td align="center">a列有用</td> 
  </tr> 
  <tr> 
   <td>where a = 3 and b = 4</td> 
   <td align="center">a、b列都有用</td> 
  </tr> 
  <tr> 
   <td>where a = 3 and b = 4 and c = 5</td> 
   <td align="center">a、b、c 三列都有用</td> 
  </tr> 
  <tr> 
   <td>where b = 3 / where c = 4</td> 
   <td align="center">都没用</td> 
  </tr> 
  <tr> 
   <td>where a = 3 and c = 5</td> 
   <td align="center">a有用，c没用</td> 
  </tr> 
  <tr> 
   <td>where a = 3 and b &gt; 4 and c = 5</td> 
   <td align="center">a、b有用，c没用</td> 
  </tr> 
  <tr> 
   <td>同上：where a = 3 and b = like ‘xx%’ and c = 5</td> 
   <td align="center">a、b有用，c没用</td> 
  </tr> 
 </tbody> 
</table>

2. Hash 索引  
在memory表中默认的是hash索引，hash的理论查询时间复杂度为O（1）。  
hash 查询的缺点：

*  索引是hash函数计算后的随机结果，如果是在磁盘上放置数据，比如以id为例，那么随着id增长，id对应的行，在磁盘上随机放置。
 *  无法对范围查询进行优化
 *  无法利于前缀索引。比如，在B-tree中field列的值“helloworld”，并加索引查询 xx = helloworld，自然可以利用索引 xx = hello 也可以利用索引。因为hash(“helloworld”)和hash(“hello”),两者的关系为伪随机。
 *  排序也无法优化
 *  必须回行，就是说通过索引只能拿到数据位置，必须要再通过这个地址再回到表中拿数据。

**面试题**

商品表，有主键good\_id，栏目 cat\_id，价格price; 在价格上加了牵引，按价格查询时还是很慢,什么原因，怎么解决？

答：实际场景中一个电商网站商品分类很多，直接在所有商品中，按价格查询商品是极少的，一般客户都是来到分类下再查询

改正：去掉单独的price列的索引，加(cat\_id, price)复合索引，再查询; 如果根据日志统计，发现好多人这样查：

电脑 –> 品牌 –> 价格 index(cat\_id, brand\_id, price)

### 二、非聚簇索引和聚簇索引 ###

1.  非聚簇索引  
    Myisam引擎是使用非聚簇索引
2.  聚簇索引  
    innodb 的主索引文件上，直接存放该行数据，称为聚簇索引，次索引指向对主键的引用  
    myisam 中，主索引和次索引，都指向物理行（磁盘位置）

**注意：**

1.  主键索引，即存储索引值，又在叶子中存储行的数据。
2.  如果没有主键（primary key），则会 Unique key 做主键。
3.  如果没有unique key，则系统生成一个内部的rowid 做主键。
4.  像innodb中，主键的索引结构中，即存储了主键值，又存储了行数据，这种结构称为“聚簇索引”。

聚簇索引：

*  优势：根据主键查询条目较少时，不用回行（数据就在主键节点下面）
 *  劣势：如果碰到不规则数据插入时，造成频繁的页分裂

## 索引覆盖 ##

索引覆盖是指：如果查询的列恰好是索引的一部份，那么查询只需要在索引文件上进行，不需要再到磁盘上找数据。这种查询速度非常快，称为“索引覆盖”。

## 理想索引 ##

1.  查询频繁
2.  区分度高：100万用户性别上基本男女各50万，区分度低
3.  长度小：牵引长度直接影响索引文件大小，影响增删改的速度，并间接影响查询速度（占用内存多）
4.  尽量能覆盖常用字段

**建索引方法：针对列中的值，从左到右截取部分，来建索引**

*  截的越短，重复度越高，区分度越低，索引效果越不好
 *  截的越长，重复度越低，区分度越高，索引效果越好，带来影响也越大，增删改变慢，并间接影响查询速度。

所以：我们要在 区分度 + 长度 两者取得平衡

惯用手法：截取不同长度测试其区分度;

> 例如：  
> mysql> select count(distinct left(coulm, 6)) / count(\*) from table;

## 索引和排序 ##

排序可能发生的2种情况：

1.  对于覆盖索引，直接在索引上查询时，就是有顺序的，using index，在 innodb引擎中，沿着索引字段排序，也有自然排序的，对于myisam引擎，如果按某索引字段排序。
    
    > 如id，但取出的字段中，有未索引字段，如goods\_name,myisam的做法，不是 索引 -> 回行…  
    > 而是先取出所有行，再进行排序。
2.  先取出数据，形成临时表做filesort（文件排序，但文件可能在磁盘上，也可能在内存中）
    
     *  using where：按照字段索引取出的结果，本身就是有序的
     *  using filesort：用到文件排序，即取出的结果两次排序

**争取目标：** 取出来的数据本身就是有序的，利用索引来排序。

## 重复索引和冗余索引 ##

1.  重复索引：是指在同一个列，或者顺序相同的，几个列，建立多个索引，称为重复索引。其没任何帮助，只会增大索引文件，拖慢更新速度，去掉。
2.  冗余索引：冗余索引是指2个索引覆盖的列有重叠。比较常见。

## 索引碎片和维护 ##

在长期的数据更改过程中，索引文件和数据文件，都将产生空洞，形成碎片。我们可以通过一个nop操作（不产生对数据影响的实质操作）来修改表。

> 比如：表的引擎为innodb，可以 alter table xxx engine innodb  
> optimize table 表名，也可以修复

**注意：** 修复表的数据及索引碎片，就会把所有的数据文件重新整理一遍，使之对齐，这个过程，如果表的行数比较大，也是非常耗费资源的操作。所以，不能频繁修复。如果表的update操作很频繁，可以按周、月来修复。如果不频繁，可以更长的周期来修复。

## explain 列分析 ##

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th align="center">select_type</th> 
   <th align="center">table</th> 
   <th align="center">type</th> 
   <th align="center">possible_keys</th> 
   <th align="center">key</th> 
   <th align="center">key_len</th> 
   <th align="center">ref</th> 
   <th align="center">rows</th> 
   <th align="center">Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>查询编号</td> 
   <td align="center">simple：不含子查询</td> 
   <td align="center">查询针对的表</td> 
   <td align="center">all：从表的第一行，逐行扫描，运气还好可能扫描到最后一行</td> 
   <td align="center">可能用到的索引</td> 
   <td align="center">最终使用的索引</td> 
   <td align="center">使用的索引的最大长度</td> 
   <td align="center">当连接查询时，表字段之间的引用关系</td> 
   <td align="center">估计要扫描多少行</td> 
   <td align="center">index：用到了索引覆盖，效率很高</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td align="center">primary：含子查询或派生查询</td> 
   <td align="center">实际表名</td> 
   <td align="center">index：性能比all稍好，通俗的说：all是扫描所有的数据行，相当于data_all; index是扫描所有的索引节点。相当于 index_all</td> 
   <td align="center">注意：系统估计可能用到的几个索引，但最终只能用一个</td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center">using where：只靠索引定位不了，不要where判断一下</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td align="center">subquery：非from子查询</td> 
   <td align="center">表的别名</td> 
   <td align="center"> 索引覆盖的情况下能利用上索引数据，但利用不上索引查找，必须全索引扫描。 mysql&gt; select goods_id from goods where goods_id + 1 &gt; 30 </td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center">using temporary：指用上了临时表，group by 与 order by不同列时，或group by,order by另的表的列。</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td align="center">derived：from型子查询</td> 
   <td align="center">derived：派生表</td> 
   <td align="center">2.是利用索引进行排序，但是取出所有节点。mysql&gt; select goods_id from goods order by goods_id desc; 分析：没有加where条件，就得取所有索引节点。同时，又没有回行，只取索引节点，再排序，经过所有节点</td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center">using filesort：文件排序（文件可能在磁盘，也可能在内存）</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td align="center">union</td> 
   <td align="center">null：直接计算，不走表</td> 
   <td align="center">rang：查询时能根据索引做范围扫描</td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center">注：如果取出的列包含text，或者更大的如mediumtext等，filesort会发生在磁盘上</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td align="center">union result</td> 
   <td align="center"></td> 
   <td align="center">ref：通过索引列可以直接引用某些数据行</td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center">show status like ‘%_table%’ 查询是否发生在磁盘上</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center">eq ref：通过索引列可以直接引用某一数据行</td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center">const、system、null：查询优化到常量级别，甚至不需要查找时间</td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
   <td align="center"></td> 
  </tr> 
 </tbody> 
</table>

## in型子查询引出的陷阱 ##

mysql的查询优化器，针对in做了优化，被改成了exists子查询的执行效果，当表越大，查询越慢。exists子查询和in子查询在mysql底层相互转换。

改进：用连接查询来代替子查询。

## from子查询 ##

注意：内层from语句查到的临时表，是没有索引的。所以，from的返回内容要尽量小，需要排序，在内层先排好序。

## count优化 ##

1.  误区：myisam 的count()非常快！  
    答：是比较快，但仅限于查询表的“所有行”比较快，因为myisam对行数进行了存储。一旦有条件的查询，速度就不再快了，尤其是where条件的列上没有索引。
2.  假如，id<100的商家都是我们内部测试的，我们想查查真实的商家有多少？

**小技巧：**

*  select count(id) from table; 快
 *  select count(id) from table where id < 100; 快
 *  select( (select count(id)from table) - (select count(id) from table where id < 100) ); 快

## group by ##

注意：

1.  分组用于统计，而更是用于去重的  
    不重复的行，分组统计数据，而不要让查询产生N多重复数据。group by的列要有索引，可以防止临时表和文件排序。  
    order by 的列要和group by的列一致，否则也会引起临时表。
2.  以A、B表连接为例，主要查询A表的列  
    那么group by, order by的列尽量相同，而且列应该显示声明为A的列
    
    > 例：mysql> select A.id, A.cat\_id from inner join B group by A.cat\_id order by A.cat\_id;

## union 优化 ##

union总是要产生临时表

**注意：**

*  union的子句条件要尽量具体，即要查询更少的行
 *  子句的结果在内存时并成结果集，需要去重，去重就要先排序，而加了all之后，不需要去重，union尽量加all

## limit 及翻页优化 ##

limit offset,N 当offset非常大时，效率及低。原因是因为mysql并不是跳过offset行，然后单取N行，而是取offset+N行，放弃前offset行，返回N行。效率及低，当offset越大，效率越低。

优化办法：

1.  从业务上解决：不允许翻过100页，以百度为例，一般翻到70页左右。
2.  不用offset，用条件查询
    
    > 比如： mysql> select id, name from table limit 10000000, 10; 可以这样  
    > mysql> select id, name from table where id > 10000000 limit 10;
3.  如果数据要作物理删除，无法用id作为条件查询，不要offset精确查询，还不限制用户分页，可以这样：  
    分析：优化思路：不查，少查，查索引，少取列，我们现在必须要查，则只查索引，不查数据，得到id，再用尖支查具体条目，这种技巧就是：**延迟关联**
    
    > 比如：mysql> select id,name from table inner join (select id from table limit 10000000, 10) as tmp on table.id = tmp.id;