Mysql基础篇--面试sql优化

小土豆Yuki

发布于 2020-06-15 17:30:23

7690

发布于 2020-06-15 17:30:23

文章被收录于专栏：洁癖是一只狗洁癖是一只狗

索引是优化中最常用的手段之一，通过索引通常可以解决大多数SQL性能问题。

索引使用的场景

1.匹配全值，对索引的值都指定具体的值

mysql> explain select *  from rental where rental_date='2005-05-25 17:22:10' and inventory_id=373 and customer_id=343\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
         type: const
possible_keys: rental_date,idx_fk_inventory_id,idx_fk_customer_id
          key: rental_date
      key_len: 10
          ref: const,const,const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

2.范围匹配查询，对索引进行范围查询

mysql> explain select  *  from rental where customer_id>=373 and customer_id<400\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
         type: range
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: NULL
         rows: 717
        Extra: Using index condition
1 row in set (0.00 sec)

3.匹配最左前缀匹配，仅仅对索引中最左列进行查询，比如复合索引 col1+col2+col3 ,使用索引的是 col1+col2,col1+col3,col1+col2+col3,不会使用索引的是col2+col3,col2.

mysql> explain select  *  from payment where payment_date='2006-02-14 15:16:04' and last_update='2006-02-15 22:12:32'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
         type: ref
possible_keys: id_payment_date
          key: id_payment_date
      key_len: 5
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

4.仅仅对索引进行查询，当查询列都在索引上，查询效率会更快,Extra部分变成了Using index,也就是经常说的覆盖索引，说明直接访问索引就可以获取到必须的数据，不需要进行回表查询，减少不必要的数据访问提高性能。

mysql> explain select last_update from payment where payment_date='2006-02-14 15:16:03' and amount =3.98\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
         type: ref
possible_keys: id_payment_date
          key: id_payment_date
      key_len: 8
          ref: const,const
         rows: 8
        Extra: Using index
1 row in set (0.00 sec)

5.匹配列前缀，使用索引中的第一列，并且包含索引的第一列的开头一部分进行查询。Extra是Using where 说明优化器要通过索引回表查询数据

mysql> create index idx_title_desc_part on film_text (title(10),description(20));
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select  title from film_text  where title like 'AFRICAN%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_text
         type: range
possible_keys: idx_title_desc_part,idx_title_description
          key: idx_title_desc_part
      key_len: 32
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

6.部分精确匹配其他范围匹配

mysql> explain select  inventory_id from rental where rental_date='2006-02-14 15:16:03' and customer_id>=300 and customer_id<=400\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
         type: ref
possible_keys: rental_date,idx_fk_customer_id
          key: rental_date
      key_len: 5
          ref: const
         rows: 181
        Extra: Using where; Using index
1 row in set (0.00 sec)

7.如果列名是索引，那么使用colun_name is null就是会用索引，区别于oracle

mysql> explain select *  from payment where rental_id is null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
         type: ref
possible_keys: fk_payment_rental
          key: fk_payment_rental
      key_len: 5
          ref: const
         rows: 5
        Extra: Using index condition
1 row in set (0.00 sec)

8.mysql5.6引入了 Index Condition Pushdow(ICP)的特性，进一步优化查询，Pushdown表示操作下方，某些情况下的条件过滤操作下放到存储引擎，Using index condition 表示Mysql 使用ICP进一步优化查询，把条件customer_id的过滤条件下推打破存储引擎，这样能够降低不必要的IO访问。

mysql> select  version();
+-----------+
| version() |
+-----------+
| 5.6.45    |
+-----------+
1 row in set (0.00 sec)
mysql> explain select  *  from rental where rental_date='2006-02-14 15:16:03' and customer_id >=300 and customer_id<=400\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
         type: ref
possible_keys: rental_date,idx_fk_customer_id
          key: rental_date
      key_len: 5
          ref: const
         rows: 181
        Extra: Using index condition
1 row in set (0.00 sec)

索引使用不到的情况

1.以%开头的like查询用不到索引

mysql> explain select  *  from actor where last_name like '%NI'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
        Extra: Using where
1 row in set (0.00 sec)

2.数据类型出现隐式转换特别是当列是字符串，那么一定要在列上加上双引号。

mysql> explain select  *  from actor where last_name =1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ALL
possible_keys: idx_actor_last_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
        Extra: Using where
1 row in set (0.00 sec)

mysql> explain select  *  from actor where last_name ='1'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

3.mysql估计使用索引比全表扫描更慢，不用索引

mysql> update film_text set title =concat('s',title);
Query OK, 1000 rows affected (0.13 sec)
Rows matched: 1000  Changed: 1000  Warnings: 0

mysql> explain select  * from film_text where title like 's%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_text
         type: ALL
possible_keys: idx_title_desc_part,idx_title_description
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra: Using where
1 row in set (0.00 sec)

4.用or分割时候，or前的条件列有索引，or后的条件列没有索引，那么就不会使用索引。

mysql> explain select  *  from payment where  amount=3.96 or customer_id = 203\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
         type: ALL
possible_keys: idx_fk_customer_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
        Extra: Using where
1 row in set (0.00 sec)

简单的优化方式

定期分析表和检查表

analyze [local|no_write_to_binlog] table  [tbl_name]

此语句分析和存储表的关键词分布，分析结果将可以使得系统得到准确的统计信息，使得sql能够生成正确的执行计划。

check tbale tbl_name [,tbl_name]...[option]...option={quick|fast|medium|extended|changed}

检查表的作用就是检查一个或多个表的是否有错误。

定期优化表

optimize [local|no_write_to_binlog] table tbl_name[,tbl_name]

如果已经删除了表的一大部分，或者如果已经对含有可变长度的表，进行了更改，则使用此语法，可以进行优化，把表中的空间进行合并，并且可以消除由于删除或者更新造成的浪费空间。

常用的sql优化

大量插入数据

MyiSam存储引擎的表，进行大量导入数据，可以使用

但是对于Innodb存储引擎这种方式并不能提高导入效率。我们可以使用下面几条规则

1.因为Innodb存储引擎的表示按照主键顺序保存的，我们按照主键顺序排序，可以提高导入顺序

2.在导入数据前执行set unique_checks=0,关闭唯一索引校验，在导入后执行set unique_checks=1,恢复唯一性校验，可以恢复导入的效率

如果应用使用自动提交的方式，建议在导入数据之前执行 set autocommit=0,关闭自动提交，结束之后再执行set autocommit=1.打开自动提交，

优化insert 语句

如果从同一个客户端插入很多行，使用多个值表的insert语句，这种将大大的减少客户端和数据库之间的连接诶，关闭消耗，inset into test values (1,2) ,(3,4),(5,6)
如果从不同的客户端插入很多行，可以通过insert delayed 语句得到更高的速度，delayed的含义是让insert语句马上执行，其实数据都被放到了内存的队列中，并没有进行真正写入磁盘，low_priority刚好相反，在所有用户对表读写完成之后进行插入，
将索引文件和数据文件放在不同的磁盘存放
如果进行批量插入的时候，可以设置bulk_insert_buffer_size变量值的方法来提高速度，但是这个只对MyISAM表使用
当从一个文件装载一个表的时候，可以使用load data infile.

优化order by

优化ordder by 先要了解mysql的排序方式

第一种就是通过有序索引扫描直接返回有序数据，

mysql> explain select customer_id from customer order by store_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
         type: index
possible_keys: NULL
          key: idx_fk_store_id
      key_len: 1
          ref: NULL
         rows: 599
        Extra: Using index
1 row in set (0.00 sec)

第二种通过返回的数据进行排序，也就是Filesort,不通过索引直接返回排序结果豆角Filesort,

mysql> explain select  *  from customer order by store_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
        Extra: Using filesort
1 row in set (0.00 sec)

Filesort是通过响应的排序算法，将取得的数据在sort_buffer_size系统变量设置的内存排序去中进行排序，如果内存装载不下，他讲在磁盘上进行数据进行分块，在对每个数据块进行排序，然后将各个块进行合并成有序的集合，sort_buffer_size是对每一个线程独有的，所以在mysql的同一时刻，会存在多个sort_buffer_size.

所以我们优化order by 的策略就是尽量的不进行额外的排序，通过索引直接返回有序的数据，where 条件和order by 使用相同的索引，且order by 的顺序和索引的顺序相同，order by 是降序或升序，否则就会进行额外的排序，这样就会出现Filesort,

总结一下使用sql会使用索引

select * from table order by key_part1 ,key_part2
select * from table where key_part1=1 order by key_part1 desc, key_part2 desc
select * from table order by key_part1 desc ,key_part2 desc

以下不会用到索引

select *  from table order by key1 desc , key2 asc --混用desc asc
select * from table where key2=constant order by key1  --where 条件关键字和order by 关键字不同
select *  from table order by  key1 ,key2 --order by 使用不同的关键字

Filesort优化

某些情况下还是无法避免Filesort ，所以要加快Filesort操作。Mysql有两种排序算法，

两次扫扫描算法，首先是取出需要的字段和行指针信息，之后再sort_buffer中就进行排序，如果sort buffer 不够就使用临时表存储中进行排序，完成排序后在使用行指针回表查询数据，需要两次访问数据，第一次排序字段和获取行指针信心，第二次，需要那行信息回表查询记录，缺点是需要多次IO操作，优点内存消耗少
一次扫描算法一次性取出满足条件的左右字段，在sor buffer中进行排序，直接返回结果，缺点内存消耗大，优点，排序效率高，

mysql是使用系统变量max_length_for_sort_data的大小和query语句的字段总大小判断使用哪一种算法，如果max_length_for_sort_data更大，使用第二种优化之后算法，否则使用第一种算法。

适当的时候可以增加max_length_for_sort_data，能够让mysql选择更优化的算法，也可以适当的加大sort_buffer_size排序区，尽可能的在内存中进行排序，而不是通过临时表进行排序，max_length_for_data和sort_buffer_size都不能设置过大，否则会带来其他问题。也查询的的时候，尽量select 具体的字段，不要使用select *.

优化group by

默认情况下，mysql对group by col1,col2 字段进行排序，这与order by col1 col2类似，如果显式的堆一个包含相同列的order by 子句，实际上没有什么影响，如果查询group by 但是用户想要避免不必要的排序，则可以指定order by null.

优化分页查询

一般查询是，通过创建覆盖索引能够比较好的提高性能，一个常见的问题就是limit 1000,20 查询出1020行，但是返回的是1000到1020条数据，其他数据都进行抛弃了

1.使用主键回表查询原表的记录,下面我们发现直接查询是进行全表查询，而使用主键关联回表查询可以提高查询效率

mysql> explain select  film_id, description from film order by title limit 50,5 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra: Using filesort
1 row in set (0.00 sec)

mysql> explain select a.film_id,a.description from film a inner join (select film_id from film order by title limit 50,5) b on a.film_id=b.film_id \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 55
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: b.film_id
         rows: 1
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: film
         type: index
possible_keys: NULL
          key: idx_title
      key_len: 767
          ref: NULL
         rows: 1000
        Extra: Using index
3 rows in set (0.00 sec)

2.记录上一次的某个位置，用记录上一页的最后一行的字段，在使用limit n ,

mysql> explain select  *  from payment where rental_id<15640 order by rental_id desc limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
         type: range
possible_keys: fk_payment_rental
          key: fk_payment_rental
      key_len: 5
          ref: NULL
         rows: 8043
        Extra: Using index condition
1 row in set (0.00 sec)

使用排序rental_id 记录上一页的最后位置，在根据这个位置过滤且使用limit n，可以有效提高查询的效率，但是在rental_id有大量重复的情况下，这种优化会丢失数据。