mysql优化查询效率面面观

话不多说,直接开搞------

从表中数据的查询开始—
看一下表的创建语句--------------------->>>>>

mysql> show create table emp2 \G
*************************** 1. row ***************************
       Table: emp2
Create Table: CREATE TABLE `emp` (
  `EMPNO` int NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` double(7,2) DEFAULT NULL,
  `COMM` double(7,2) DEFAULT NULL,
  `DEPTNO` int DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

在下面的内容开始之前,先说一下sql中的数据访问类型效率排序-------->>>>>>>>
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all;

别着急,先一个一个分析

从效率低的开始------
all------->>>>

mysql> explain select * from emp2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE    -- 查询类型
        table: emp2       -- 表名     
   partitions: NULL
         type: ALL       --访问类型 
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14        -- 扫描行数
     filtered: 100.00    --过滤,100.00表示未过滤
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

使用all这种数据访问方式,表示扫描全表数据,当表中数据很多时一般不建议扫描全表,实际应用中大都也不用这种方式;

对比以下方式----index

mysql> alter table emp2 add index pt_index (empno);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> explain select  empno from emp2  \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp2
   partitions: NULL
         type: index
possible_keys: NULL
          key: pt_index
      key_len: 4
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

-- 未设置主键S索引之前
mysql>  explain select  * from emp2 order by empno desc  \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

--- 普通索引,并不带排序的功能,添加主键索引之后
mysql> alter table emp2 modify empno int primary key ;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> explain select  * from emp2 order by empno desc  \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp2
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: Backward index scan
1 row in set, 1 warning (0.00 sec)

index这种数据扫描方式比all要好一些,因为用到了索引,
一般有两种情况----->>>需要的数据在索引中就可以找到,另一个是通过索引进行排序,要求为主键索引或者唯一索引,只有普通索引的话也是all;

以上两种方式筛选的数据还是很多,在此基础上做一些条件筛选一减少数据的展现量,确保数据的命中率;

range------->>>>>

mysql> explain select * from emp2 where empno>7800 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp2
   partitions: NULL
         type: range
possible_keys: PRIMARY,pt_index
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)


mysql>  explain select * from emp2 where sal>7800 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

筛选条件-----如果是主键索引或者其他索引所在字段,则数据访问类型为range否则可能仍然为all,
筛选条件 的操作主要关键字有 > ,< ,between and ,is null,like ,in(),any(),some() 等等

所以可以小结以下,查询数据时尽量通过主键或者索引所在字段作为where的条件部分,依赖可以缩小数据范围,另一个可以提高查询效率;

index_subquery------>>>>

这种数据访问方式现在很少出现,因为随着mysql优化器的优化(性能改进),这种方式会被mysql优化器优化为eq_ref类型;

unique_subquery------>>>

这种数据访问模式跟index_subquery类似,只是索引对应的时唯一索引;

index_merge---------->>>>>>

-- 添加索引
mysql> alter table emp2 drop  index lh_index ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table emp2 add  unique index un_index (empno) ;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table emp2 add   index pt_index (ename) ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from emp2 where empno=7369 or ename='king'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp2
   partitions: NULL
         type: index_merge
possible_keys: PRIMARY,un_index,pt_index
          key: PRIMARY,pt_index
      key_len: 4,43
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using union(PRIMARY,pt_index); Using where
1 row in set, 1 warning (0.00 sec)

Using union(PRIMARY,pt_index); Using where----表示出现了索引合并优化，通常是将多个索引字段的范围扫描合并为一个。包括单表中多个索引的交集，并集以及交集之间的并集，但不包括跨多张表和全文索引。

index merge这种数据访问方式可以使得我们可以使用多个索引同时进行扫描，然后将结果进行合并,但是这就会产生以下几种结果集-----
1,取交集------即 索引1字段的条件 and 索引2字段的条件 ,如果出现这种情况也就说明我们的索引建立的并不合理,可以通过建立符合索引来进行优化-----

mysql> alter table emp2 drop index un_index ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table emp2 drop index pt_index ;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table emp2 add  unique index un_index  (empno,ename);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0



mysql> explain  select * from emp2 where empno=7839 and ename='king' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp2
   partitions: NULL
         type: const
possible_keys: PRIMARY,un_index
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

> 对比两种索引的扫描方式,第一种两个单独的索引,需要扫描两次(rows:2)然后取交集得到的结果第二种方式是扫描一次就可以得到结果,所以对于多索引字段如果费有必要的话(一般索引是不需要太多)可以设置联合索引代替 多索引的;

ref_or_null---------->>>>>

对于某个字段即需要关联条件也需要查null值的情况会选用这种方式;这种方式跟ref类似,如果表中数据很少的话会进行全表扫描-----

mysql> explain select * from emp2  where empno=7844 or ename is null \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp2
   partitions: NULL
         type: ALL
possible_keys: PRIMARY,no_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14
     filtered: 16.43
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ref------->>>>
使用非唯一性索引进行数据的查找

mysql> explain select * from emp2 e ,dept2 d where e.deptno=d.deptno \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: d
   partitions: NULL
         type: ALL
possible_keys: ptt_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: e
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join) --- 使用连接缓存
2 rows in set, 1 warning (0.00 sec)

这种方式如果数据量少也会进行全表扫描

eq_ref----------------->>>>>
这种方式表示使用了唯一索引进行数据查找

mysql> explain select * from emp2  e ,emp e1 where e.empno=e1.empno \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: e
   partitions: NULL
         type: ALL
possible_keys: PRIMARY,empno
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: e1
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,no_index
          key: PRIMARY
      key_len: 4
          ref: gavin.e.empno
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

const----->>>表示这个表之多有一个匹配记录

mysql> explain select * from emp2  where empno=7839\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp2
   partitions: NULL
         type: const
possible_keys: PRIMARY,empno --  表中存在的索引
          key: PRIMARY ------实际用到的索引
      key_len: 4
          ref: const -- 至多有一条记录则显示const,若非唯一,则显示哪一列用到了索引
         rows: 1    -- 扫描的行数
     filtered: 100.00
        Extra: NULL --额外的信息
1 row in set, 1 warning (0.02 sec)

extra处的值可以有以下取值--------------
using filesort ----->>>>
说明MySQL无法使用索引进行排序,只能利用排序算法进行排序,会消耗一部分额外的资源;

mysql> explain select * from emp2 order by sal \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

using temporary---------------->>>>使用临时表
查询完毕后删除临时表

mysql> explain select ename ,count(*) from emp2  where deptno = 10 group by ename \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14
     filtered: 10.00
        Extra: Using where; Using temporary  --使用where 进行扫描,同时建立临时表
1 row in set, 1 warning (0.00 sec)

using index------------------>>>
表名数据再索引字段就可以取到

ysql> explain select empno from emp2  \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp2
   partitions: NULL
         type: index
possible_keys: NULL
          key: empno
      key_len: 4
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

using join buffer ------>使用连接缓存

mysql> explain select * from emp2 e ,dept2 d where e.deptno=d.deptno \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: d
   partitions: NULL
         type: ALL
possible_keys: ptt_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: e
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join) --- 使用连接缓存
2 rows in set, 1 warning (0.00 sec)

no matching row in const table------
表示没有匹配到数据

mysql> explain select * from emp2  where empno =9999 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: no matching row in const table
1 row in set, 1 warning (0.00 sec)

关于mysql优化查询效率主要有以下几个方面

1,查询时尽量避免使用全字段查询
2,筛选条件----where 处尽量选择带有索引的字段(如果能查到的话)
3,设置索引时 唯一索引,主键索引的查询效率要好于普通索引;
4,多字段索引一般是用联合索引替代