MySQL进阶第二弹。

MySQL进阶-2-索引优化分析

1. 导致SQL慢的原因

  1. 查询语句写的烂
  2. (单值、复合)索引实现
  3. 关联查询太多join
  4. 服务器调优以及各参数的设置

2. 常见通用的join查询

2.1 SQL执行顺序

  1. SQL通用编写语法

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    select distinct
    <select_list>

    from
    <left_table> <join type>

    join <right_table> on <join_condition>

    where
    <where_condition>

    group by
    <group_by_list>

    having
    <having_condition>

    order by
    <order_by_condition>

    limint <limint_number>
  2. 机器读取顺序

    1
    2
    3
    4
    5
    6
    7
    8
    9
    from  <left_table>
    on <join_condition>
    where <where_condition>
    group by <group_by_list>
    having <having_condition>
    select
    distinct <select_list>
    order by <order_by_condition>
    limint <limint_number>

    底层读取顺序

2.2 7种Join

2.2.1 示意图

1597151133049

2.2.2 示例

  1. 建表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    -- 创建数据库
    CREATE DATABASE mysq;
    USE mysq;

    -- 创建部门表
    CREATE TABLE `t_dept`(
    `id`INT(11) NOT NULL AUTO_INCREMENT,
    `deptName`VARCHAR(30)DEFAULT NULL,
    `address`VARCHAR(40)DEFAULT NULL,
    PRIMARY KEY(`id`)
    )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

    -- 创建员工表
    CREATE TABLE `t_emp`(
    `id`INT(11)NOT NULL AUTO_INCREMENT,
    `name`VARCHAR(20)DEFAULT NULL,
    `age`INT(3)DEFAULT NULL,
    `deptId`INT(11)DEFAULT NULL,
    empno INT NOT NULL, PRIMARY KEY(`id`),
    KEY`idx_dept_id`(`deptId`)
    #CONSTRAINT`fk_dept_id`FOREIGNKEY(`deptId`)REFERENCES`t_dept`(`id`)
    )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

    -- 插入数据
    INSERT INTO t_dept(deptName,address)VALUES('华山','华山');
    INSERT INTO t_dept(deptName,address)VALUES('丐帮','洛阳');
    INSERT INTO t_dept(deptName,address)VALUES('峨眉','峨眉山');
    INSERT INTO t_dept(deptName,address)VALUES('武当','武当山');
    INSERT INTO t_dept(deptName,address)VALUES('明教','光明顶');
    INSERT INTO t_dept(deptName,address)VALUES('少林','少林寺');
    INSERT INTO t_emp(NAME,age,deptId,empno)VALUES('风清扬',90,1,100001);
    INSERT INTO t_emp(NAME,age,deptId,empno)VALUES('岳不群',50,1,100002);
    INSERT INTO t_emp(NAME,age,deptId,empno)VALUES('令狐冲',24,1,100003);
    INSERT INTO t_emp(NAME,age,deptId,empno)VALUES('洪七公',70,2,100004);
    INSERT INTO t_emp(NAME,age,deptId,empno)VALUES('乔峰',35,2,100005);
    INSERT INTO t_emp(NAME,age,deptId,empno)VALUES('灭绝师太',70,3,100006);
    INSERT INTO t_emp(NAME,age,deptId,empno)VALUES('周芷若',20,3,100007);
    INSERT INTO t_emp(NAME,age,deptId,empno)VALUES('张三丰',100,4,100008);
    INSERT INTO t_emp(NAME,age,deptId,empno)VALUES('张无忌',25,5,100009);
    INSERT INTO t_emp(NAME,age,deptId,empno)VALUES('韦小宝',18,NULL,100010);

  2. 测试案例

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    -- 【交集】所有有门派人员的信息(要求显示门派名称)
    SELECT e.`name`,d.`deptName` FROM t_emp e INNER JOIN t_dept d ON e.`deptId` = d.`id`;

    -- 【全A】列出所有人员及其门派信息
    SELECT e.`name`, d.`deptName` FROM t_emp e LEFT JOIN t_dept d ON e.`deptId` = d.`id`;

    -- 【全B】列出所有门派
    SELECT * FROM t_dept;

    -- 【A特】所有无门派人士
    SELECT * FROM t_emp WHERE deptId IS NULL;

    -- 【B特】所有无人门派
    SELECT e.`name`,d.`deptName` FROM t_emp e RIGHT JOIN t_dept d ON e.`deptId` = d.`id` WHERE e.`name` IS NULL;

    -- 【全部】所有人员和门派的对应关系
    SELECT e.`name`,d.`deptName` FROM t_emp e LEFT JOIN t_dept d ON e.`deptId` = d.`id`
    UNION
    SELECT e.`name`,d.`deptName` FROM t_emp e RIGHT JOIN t_dept d ON e.`deptId` = d.`id`

    -- 【A特和B特】所有没有入门派的人员和没人入的门派
    SELECT e.`name`,d.`deptName` FROM t_emp e LEFT JOIN t_dept d ON e.`deptId` = d.`id` WHERE d.`deptName` IS NULL
    UNION
    SELECT e.`name`,d.`deptName` FROM t_emp e RIGHT JOIN t_dept d ON e.`deptId` = d.`id` WHERE e.`name` IS NULL

3. 索引简介

3.1 是什么

索引(index)是帮助MySQL实现高效获取数据的数据结构。

​ 我们可以简单理解为:排好序的快速查找数据结构。数据库系统维护着满足特定查找算法的数据结构,这些数据结构一某种方式引用数据,这样就可以在这些数据结构上实现高级查找算法。这便是索引。

无标题

​ 上图中,为了加快Col2的查找,可以维护右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样既可以运用二叉查找树在一定复杂度内获取相应数据,从而快速索引出符合条件的记录。

​ 一般来说,索引本身很大,不可能全部存储到内存中,因此索引往往以文件的形式保存在硬盘中。平时我们提到的索引,如果没有特别说明都是指的是B树(多路搜索树,并不一定是二叉树)结构组织的索引。其中聚集索引、次要索引、覆盖索引、符合索引、前缀索引、唯一索引都是使用B+树索引,统称索引。处理B+树索引还有哈希索引等。

3.2 索引优势

​ 类似大学图书馆建立书目索引,索引可以提高数据检索效率,降低数据库IO成本。通过建立索引还会对数据进行排序,降低数据排序成本,减少CPU的消耗。

3.3 索引劣势

​ 索引实际上是一张表,保存了主键和索引字段,并指向实体表的记录,所以需要占用一定空间。

​ 索引虽然提高了查询速度,但是会降低更新表的速度,如果对表进行insert、update和delete,MySQL不仅要存数据,还要保存胰腺癌丝印文件每次更新添加了索引列的字段,都会调整因为更新而带来的键值变化后的索引信息。

​ 索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就要花时间在研究优秀的索引,或者优化查询语句。

3.4 索引分类

​ 索引又分为:单值索引、唯一索引、复合索引等。

  1. 单值索引:一个索引只包含单个列、一个表可以有多个单列索引。
  2. 唯一索引:索引的值必须唯一,但允许有空值。
  3. 符合索引:一个索引包含多个列。

3.5 索引使用

  1. 创建索引

    1
    2
    3
    4
    5
    -- 方式1
    create [unique] index indexName on mytable(collumname(length))

    -- 方式2
    alter mytable add [unique] index [indexname] on (columname(lenght))
  2. 删除

    1
    drop index [indexName] on mytable
  3. 查看索引

    1
    show index from table_name
  4. 添加

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    -- 添加逐渐、意味着索引值必须唯一,且不能为null
    alter table tb_name add primary key (column_list)

    -- 创建索引值必须唯一(Null可以出现多次)
    alter table tb_name add unique index_name (column_list)

    -- 添加普通索引,索引值可能出现多次
    alter table tb_name add index indx_name(column_list)

    -- 指定索引为FullText、用于全文索引
    alter table tb_name add fulltext index_name(column_list)

3.6 索引结构

  1. BTree索引

    ​ BTree索引(Balanced Tree),是一种很普遍的数据库索引结构,是oracle默认的索引结构类型。其特点是定位高效、利用率高、自我平衡,特别适用于高基数字段,定位单条或小范围数据非常高效。理论上,适用BTree在亿条数据与100条数据中定位记录花销相同。BTree索引的数据结构如下图:

    1597285909182

    ​ BTree索引的主要特点:

    • 树形结构:有根节点(root)、分支(branches)、叶(leaves)三级节点组成,其中分支节点可以有多层。

    • 多分支结构:与binary tree 不同,BTree索引中一个root、branch可以有多个子节点(超过2个)。

    • 双向链表:整个叶子节点部分是一个双向链表

  2. Hash索引【了解】

  3. Full-Text索引【了解】

  4. R-Tree索引【了解】

3.7 创建索引的场景

  1. 主键自动创建索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 查询中与其他表关联的字段,外加关系建立索引
  4. 查询中排序的字段时候创建索引,排序字段若通过索引去访问将大大提高排序速度
  5. 查询中统计或者分组字段

3.8 不创建索引的场景

  1. 表记录太少(没必要)
  2. 经常增删改的表
  3. 数据重复且分布平均的表字段
  4. where条件用不到的字段

4. 索引性能分析

4.1 MySQL Query Optimizer

  1. MySQL中专门负责优化SELECT语句的优化器模块,主要功能:通过计算分析系统中收集到的统计信息,为客户端请求的Query提供他任务最优的计划
  1. 客户端向MySQL请求一条Query,命令解析器模块完成请求分类,区别出是SELECT并转发给MySQL Query Optimizer是,MySQL Query Optimizer会将整条Query进行优化,处理掉一些常量表达式的预算,直接换成常量值,并对Query中的查询条件进行简化和转换如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息,看现实Hint信息是否可以完全确定Query的执行计划。如果没Hint或Hint还不足以完全确定该Query的执行计划,则会读取涉及对象的统计信息,根据Query进行写相应计算分析,然后在得出最后的执行计划。

4.2 MySQl常见瓶颈

  1. CPU:CPU饱和一般发生在数据装入内存或从磁盘上读取数据
  2. IO:磁盘I/O瓶颈发生在装入数据远大于内存容量
  3. 服务器硬件:top、free、iostat和vmstat等查看系统性能状态

4.3 Explain

  1. 定义

    ​ 可以使用Explain来模拟优化器执行SQL语句,从而知道MySQL是如何处理你的SQL语句,进而分析查询语句或者结构性能瓶颈。

  2. 用处

    ​ 查看表的读取顺序、数据读取操作的操作类型、那些索引可用、那些索引被实际使用,表之间的引用,每张表有多少行被优化器执行

  3. 用法

    1
    explain sql语句

    返回信息

    1
    2
    3
    4
    5
    6
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
    | 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

4.4 Explain 字段详情

  1. id

    id是SELECT查询的序列号,包含一组数字,表示查询中执行SELECT子句或者操作表的顺序。通常包含3中情况:

    【1】id相同,执行顺序由上而下

    1597322406715

    【2】id不同,如果是子查询,id序号会递增,id值越大,优先级越高,越先执行。

    1597322426189

    【3】id相同和不同并存

1597322445848
  1. select_type

    ​ select_type,查询类型,主要用于区别普通查询、联合查询、子查询等复杂查询,一共有6种,包含了:

    • SIMPLE:简单SELECT查询,查询中不包含子查询或者union
    • PRIMARY:查询中若包含了任何复杂的子部分,最外层查询则会被标记为Primary
    • SUBQUERY:在SELECT或者WHERE列表中包含了子查询
    • DERIVED:在FROM列表中包含的子查询会被标注为DERIVED(衍生)MySQL会递归执行这些子查询,把结果放在临时表
    • UNION:若第二个SELECT出现union之后,则会被标记为union;若union包含在from的子查询中,外层SELECT将被标记为DERIVED。
    • UNION RESULT:从union表活动结果的SELECT
  1. table

    ​ 显示一行的数据是关于哪张表的

  1. type

    ​ type显示的是访问类型,是较为重要的一项指标,,结果值从好到坏依次是:

    1
    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

    我们一般只需要记得:system > const > eq_ref > ref > range > index > ALL

    一般来说保证达到range、最好到ref。

    类型 说明
    system 表中只有一行,是const类型特例,平时不会出现,可以忽略不计。
    const 表示通过索引一次就能找到,用于比较primary key或unique索引。因为只匹配一行,所以很快。
    eq_ref 唯一性索引,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引。
    ref 非唯一索引,返回匹配某个单独值的所有行。本质上也是索引访问,他返回所有匹配单个值的行,然而可以会有多个符合条件的行。
    range 检索给定范围,使用一个索引选择行。一般where中出现between、>、<、in等。
    index Full Index Scan,与ALL区别是index类型只遍历索引数。索引文件比数据文件小,读取比ALL快。
    all Full Table Scan,将遍历全表以找到匹配的行。
  1. possible_keys

    ​ 显示可能应用到这张表的索引,一个或者多个;查询涉及的字段若存在索引,则该索引将会被列出,但不一定被查询实际使用

  1. key

    ​ 实际使用的索引。如果为null,则没有使用索引。查询中若使用了覆盖索引,则索引和查询的SELECT字段你重叠。

  1. key_len

    ​ 表示索引中使用的字节数,可以通过计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好;key_len显示的值为索引最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。

  1. ref

    ​ 显示索引那一列被使用了,如果可能的话,是一个常数。那些列或常量被用于查找索引列上的值。

  1. rows

    ​ 根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数

  1. Extra

    ​ 包含不适合在其他列中显示但十分重要的额外信息.

4.5 Explain测试

  1. 阐述下图的执行顺序

    1597482346266
  2. 执行顺序

    ​ 【1】第四行(selec name,id from t2),select_type为union、说明第三个select是union里面的第二个select。

    ​ 【2】第二行(selec id,name from t1 wehre ohter_column = ‘ ’),查询包含在from,因此是衍生表DERIVED。

    ​ 【3】第三行(select id from t3),违章查询,说为整个查询的第二个select。

    ​ 【4】第一行(select d1.name ….),表示union里面的第一个select,select_type为primary 表示该查询为外层查询,table列被标记为derived3说明查询结果来着一个衍生表,3表示衍生来自第三个查询。

    ​ 【5】第五行(…union…)代表从union的临时表中读取行的阶段,table列的union1,4代表用第一个好第四个select结果进行union。

5. 索引优化

5.0 数据准备

  1. 建表语句

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    CREATE DATABASE sqlplus;
    USE sqlplus;

    -- 创建部门表
    CREATE TABLE`dept`(
    `id`INT(11)NOT NULL AUTO_INCREMENT,
    `deptName`VARCHAR(30)DEFAULT NULL,
    `address`VARCHAR(40)DEFAULT NULL,
    ceo INT NULL, PRIMARY KEY(`id`)
    )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

    -- 创建员工表
    CREATE TABLE`emp`(
    `id`INT(11)NOT NULL AUTO_INCREMENT,
    `empno`INT NOT NULL,
    `name`VARCHAR(20)DEFAULT NULL,
    `age`INT(3)DEFAULT NULL,
    `deptId`INT(11)DEFAULT NULL,
    PRIMARY KEY(`id`)
    #CONSTRAINT`fk_dept_id`FOREIGNKEY(`deptId`)REFERENCES`t_dept`(`id`)
    )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  2. 设置参数

    1
    2
    3
    -- 设置参数
    SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
    SET GLOBAL log_bin_trust_function_creators = 1;
  3. 编写函数用于生产随机字符串

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    -- 产生随机字符串
    DELIMITER $$
    CREATE FUNCTION rand_string(n INT)RETURNS VARCHAR(255)
    BEGIN
    DECLARE chars_str VARCHAR(100)DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255)DEFAULT' ';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
    SET return_str=CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
    SET i=i+1;
    END WHILE;
    RETURN return_str;
    END $$
  4. 随机生产部门编号

    1
    2
    3
    4
    5
    6
    7
    8
    -- 随机产生部门编号
    DELIMITER $$
    CREATE FUNCTION rand_num(from_num INT,to_num INT)RETURNS INT(11)
    BEGIN
    DECLARE i INT DEFAULT 0;
    SET i=FLOOR(from_num+RAND()*(to_num-from_num+1)) ;
    RETURN i;
    END $$
  5. 编写存储过程用于给emp表插入数据

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    DELIMITER $$
    CREATE PROCEDURE insert_emp( START INT, max_num INT)
    BEGIN
    DECLARE i INT DEFAULT 0;
    -- setautocommit=0 把 autocommit 设置成 0
    SET autocommit = 0;
    REPEAT
    SET i=i+1;
    INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6) , rand_num(30,50),rand_num(1,10000));
    UNTIL i=max_num
    END REPEAT;
    COMMIT;
    END $$
    -- 删除
    -- DELIMITER;
    -- dropPROCEDUREinsert_emp;
  6. 编写存储过程用于给dept表插入数据

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    -- 执行存储过程,往 dept 表添加随机数据
    DELIMITER $$
    CREATE PROCEDURE`insert_dept`( max_num INT)
    BEGIN DECLARE i INT DEFAULT 0;
    SET autocommit=0;
    REPEAT SET i=i+1;
    INSERT INTO dept(deptname,address,ceo)VALUES(rand_string(8),rand_string(10),rand_num(1,500000));
    UNTIL i=max_num
    END REPEAT;
    COMMIT;
    END $$
    -- 删除
    -- DELIMITER;
    -- dropPROCEDUREinsert_dept;
  7. 调用存储过程,插入数据

    1
    2
    3
    4
    5
    6
    7
    -- 执行存储过程,往 dept 表添加 1 万条数据
    DELIMITER ;
    CALL insert_dept(10000);

    -- 执行存储过程,往 emp 表添加 50 万条数据
    DELIMITER;
    CALL insert_emp(100000,500000);

5.1 常见单表索引失效

  1. 全值匹配我最爱

    语句

    1
    2
    3
    4
    5
    6
    7
    8
    9
    -- 创建复合索引
    CREATE INDEX idx_emp_agedeptIdname ON emp(age, deptId, NAME);

    -- 全值索引我最爱
    EXPLAIN SELECT * FROM emp WHERE emp.age = 30;
    EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND deptId = 4;
    EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND deptId = 4 AND emp.name = 'abcd';
    -- 顺序调换也不要紧,执行前优化器会自动优化
    EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND emp.name = 'abcd' AND deptId = 4;

    结果

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    mysql> EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND deptId = 4 AND emp.name = 'abcd';
    +----+-------------+-------+------------+------+------------------------------------+-----------------------+---------+-------------------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+------------------------------------+-----------------------+---------+-------------------+------+----------+-------+
    | 1 | SIMPLE | emp | NULL | ref | idx_emp_agedeptIdname,idx_emp_name | idx_emp_agedeptIdname | 73 | const,const,const | 1 | 100.00 | NULL |
    +----+-------------+-------+------------+------+------------------------------------+-----------------------+---------+-------------------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)

    mysql> EXPLAIN SELECT * FROM emp WHERE emp.age = 30;
    +----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+-------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+-------+----------+-------+
    | 1 | SIMPLE | emp | NULL | ref | idx_emp_agedeptIdname | idx_emp_agedeptIdname | 5 | const | 47832 | 100.00 | NULL |
    +----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+-------+----------+-------+
    1 row in set, 1 warning (0.00 sec)

    mysql> EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND deptId = 4;
    +----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
    | 1 | SIMPLE | emp | NULL | ref | idx_emp_agedeptIdname | idx_emp_agedeptIdname | 10 | const,const | 1 | 100.00 | NULL |
    +----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)

    mysql> EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND deptId = 4 AND emp.name = 'abcd';
    +----+-------------+-------+------------+------+------------------------------------+-----------------------+---------+-------------------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+------------------------------------+-----------------------+---------+-------------------+------+----------+-------+
    | 1 | SIMPLE | emp | NULL | ref | idx_emp_agedeptIdname,idx_emp_name | idx_emp_agedeptIdname | 73 | const,const,const | 1 | 100.00 | NULL |
    +----+-------------+-------+------------+------+------------------------------------+-----------------------+---------+-------------------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)

    全职匹配我最爱指的是,查询的字段按照顺序在索引中都可以匹配到!

    SQL 中查询字段的顺序,跟使用索引中字段的顺序,没有关系。优化器会在不影响 SQL 执行结果的前提下,给 你自动地优化。

  1. 最佳左前缀法则

    语句

    1
    2
    3
    4
    -- 最佳左前缀法则
    EXPLAIN SELECT * FROM emp WHERE deptId = 4;
    EXPLAIN SELECT * FROM emp WHERE emp.name = 'abcd' AND deptId = 4;
    EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND emp.name = 'abcd';

    结果

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    mysql> EXPLAIN SELECT * FROM emp WHERE deptId = 4;
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 499070 | 10.00 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)

    mysql> EXPLAIN SELECT * FROM emp WHERE emp.name = 'abcd' AND deptId = 4;
    +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
    | 1 | SIMPLE | emp | NULL | ref | idx_emp_name | idx_emp_name | 63 | const | 1 | 10.00 | Using where |
    +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)

    mysql> EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND emp.name = 'abcd';
    +----+-------------+-------+------------+------+------------------------------------+--------------+---------+-------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+------------------------------------+--------------+---------+-------+------+----------+-------------+
    | 1 | SIMPLE | emp | NULL | ref | idx_emp_agedeptIdname,idx_emp_name | idx_emp_name | 63 | const | 1 | 9.58 | Using where |
    +----+-------------+-------+------------+------+------------------------------------+--------------+---------+-------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)

    查询字段与索引字段顺序的不同会导致,索引无法充分使用,甚至索引失效

    使用复合索引,需要遵循最佳左前缀法则,即如果索引了多列,要遵守最左前缀法则。指的是查询从索 引的最左前列开始并且不跳过索引中的列

  1. 不在索引列做任何操作

    语句

    1
    2
    3
    4
    5
    6
    7
    8
    -- 不要再索引列上做任何计算
    -- 不要使用函数
    EXPLAIN SELECT * FROM emp WHERE emp.age = 30;
    EXPLAIN SELECT * FROM emp WHERE LEFT(age,3)= 30;
    -- 不要使用转换
    CREATE INDEX idx_emp_name ON emp(NAME);
    EXPLAIN SELECT * FROM emp WHERE emp.name = '30000';
    EXPLAIN SELECT * FROM emp WHERE emp.name = 30000;

    结果

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    mysql> EXPLAIN SELECT * FROM emp WHERE emp.age = 30;
    +----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+-------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+-------+----------+-------+
    | 1 | SIMPLE | emp | NULL | ref | idx_emp_agedeptIdname | idx_emp_agedeptIdname | 5 | const | 47832 | 100.00 | NULL |
    +----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+-------+----------+-------+
    1 row in set, 1 warning (0.00 sec)

    mysql> EXPLAIN SELECT * FROM emp WHERE LEFT(age,3)= 30;
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 499070 | 100.00 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    mysql> EXPLAIN SELECT * FROM emp WHERE emp.name = '30000';
    +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
    | 1 | SIMPLE | emp | NULL | ref | idx_emp_name | idx_emp_name | 63 | const | 1 | 100.00 | NULL |
    +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)

    mysql> EXPLAIN SELECT * FROM emp WHERE emp.name = 30000;
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | 1 | SIMPLE | emp | NULL | ALL | idx_emp_name | NULL | NULL | NULL | 499070 | 10.00 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    1 row in set, 3 warnings (0.00 sec)

    不在索引列上做任何操作(计算、函数、(自动 or 手动)类型转换) ,会导致索引失效而转向全表扫描。

    字符串不加单引号,则会在 name 列上做一次转换!

  1. 存储引擎不能使用索引中范围条件右边的列

    语句

    1
    2
    3
    -- 索引列上不能有范围查询
    EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND deptId = 4 AND emp.name = 'abcd';
    EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND deptId > 4 AND emp.name = 'abcd';

    结果

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND deptId = 4 AND emp.name = 'abcd';
    +----+-------------+-------+------------+------+------------------------------------+-----------------------+---------+-------------------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+------------------------------------+-----------------------+---------+-------------------+------+----------+-------+
    | 1 | SIMPLE | emp | NULL | ref | idx_emp_agedeptIdname,idx_emp_name | idx_emp_agedeptIdname | 73 | const,const,const | 1 | 100.00 | NULL |
    +----+-------------+-------+------------+------+------------------------------------+-----------------------+---------+-------------------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)

    mysql> EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND deptId > 4 AND emp.name = 'abcd';
    +----+-------------+-------+------------+------+------------------------------------+--------------+---------+-------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+------------------------------------+--------------+---------+-------+------+----------+-------------+
    | 1 | SIMPLE | emp | NULL | ref | idx_emp_agedeptIdname,idx_emp_name | idx_emp_name | 63 | const | 1 | 9.57 | Using where |
    +----+-------------+-------+------------+------+------------------------------------+--------------+---------+-------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)

    范围之后的索引全部失效

    将可能做范围查询的字段的索引顺序放在最后

  1. 尽量使用覆盖索引,减少select *

    语句

    1
    2
    3
    -- 尽量使用索引覆盖
    EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND deptId = 4 AND emp.name = 'abcd';
    EXPLAIN SELECT age,deptId,NAME FROM emp WHERE emp.age = 30 AND deptId = 4 AND emp.name = 'abcd';

    结果

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND deptId = 4 AND emp.name = 'abcd';
    +----+-------------+-------+------------+------+------------------------------------+-----------------------+---------+-------------------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+------------------------------------+-----------------------+---------+-------------------+------+----------+-------+
    | 1 | SIMPLE | emp | NULL | ref | idx_emp_agedeptIdname,idx_emp_name | idx_emp_agedeptIdname | 73 | const,const,const | 1 | 100.00 | NULL |
    +----+-------------+-------+------------+------+------------------------------------+-----------------------+---------+-------------------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)

    mysql> EXPLAIN SELECT age,deptId,NAME FROM emp WHERE emp.age = 30 AND deptId = 4 AND emp.name = 'abcd';
    +----+-------------+-------+------------+------+------------------------------------+-----------------------+---------+-------------------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+------------------------------------+-----------------------+---------+-------------------+------+----------+-------------+
    | 1 | SIMPLE | emp | NULL | ref | idx_emp_agedeptIdname,idx_emp_name | idx_emp_agedeptIdname | 73 | const,const,const | 1 | 100.00 | Using index |
    +----+-------------+-------+------------+------+------------------------------------+-----------------------+---------+-------------------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
  1. mysql在使用不等于是无法使用索引

    语句

    1
    2
    3
    -- 不要使用不等于
    EXPLAIN SELECT * FROM emp WHERE emp.name != '30000';
    EXPLAIN SELECT * FROM emp WHERE emp.name <> '30000';

    结果

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> EXPLAIN SELECT * FROM emp WHERE emp.name != '30000';
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | 1 | SIMPLE | emp | NULL | ALL | idx_emp_name | NULL | NULL | NULL | 499070 | 50.00 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)

    mysql> EXPLAIN SELECT * FROM emp WHERE emp.name <> '30000';
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | 1 | SIMPLE | emp | NULL | ALL | idx_emp_name | NULL | NULL | NULL | 499070 | 50.00 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)

    mysql 在使用不等于(!= 或者<>)时,有时会无法使用索引会导致全表扫描。

  1. is null、is not null也无法使用索引

    语句

    1
    2
    3
    -- 不要使用is not null、is null
    EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL;
    EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL;

    结果

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL;
    +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
    | 1 | SIMPLE | emp | NULL | ref | idx_emp_name | idx_emp_name | 63 | const | 1 | 100.00 | Using index condition |
    +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)

    mysql> EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL;
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | 1 | SIMPLE | emp | NULL | ALL | idx_emp_name | NULL | NULL | NULL | 499070 | 50.00 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
  1. like以通配符开头也不能使用索引

    语句

    1
    2
    3
    4
    -- like匹配
    EXPLAIN SELECT * FROM emp WHERE emp.name LIKE '%30000';
    EXPLAIN SELECT * FROM emp WHERE emp.name LIKE '%30000%';
    EXPLAIN SELECT * FROM emp WHERE emp.name LIKE '30000%';

    结果

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    mysql> EXPLAIN SELECT * FROM emp WHERE emp.name LIKE '%30000';
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 499070 | 11.11 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)

    mysql> EXPLAIN SELECT * FROM emp WHERE emp.name LIKE '%30000%';
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 499070 | 11.11 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)

    mysql> EXPLAIN SELECT * FROM emp WHERE emp.name LIKE '30000%';
    +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
    | 1 | SIMPLE | emp | NULL | range | idx_emp_name | idx_emp_name | 63 | NULL | 1 | 100.00 | Using index condition |
    +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)

    前缀不能出现模糊匹配!

  1. 少用or,用它链接是会索引失效

    语句

    1
    2
    3
    -- 减少使用or
    EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND deptId = 4 OR emp.name = 'abcd';
    EXPLAIN SELECT * FROM emp WHERE emp.age = 30 OR deptId = 4;

    结果

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    mysql> EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND deptId = 4 OR emp.name = 'abcd';
    +----+-------------+-------+------------+-------------+------------------------------------+------------------------------------+---------+------+------+----------+-------------------------------------------------------------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+-------------+------------------------------------+------------------------------------+---------+------+------+----------+-------------------------------------------------------------------+
    | 1 | SIMPLE | emp | NULL | index_merge | idx_emp_agedeptIdname,idx_emp_name | idx_emp_agedeptIdname,idx_emp_name | 10,63 | NULL | 2 | 100.00 | Using sort_union(idx_emp_agedeptIdname,idx_emp_name); Using where |
    +----+-------------+-------+------------+-------------+------------------------------------+------------------------------------+---------+------+------+----------+-------------------------------------------------------------------+
    1 row in set, 1 warning (0.00 sec)

    mysql> EXPLAIN SELECT * FROM emp WHERE emp.age = 30 OR deptId = 4;
    +----+-------------+-------+------------+------+-----------------------+------+---------+------+--------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+-----------------------+------+---------+------+--------+----------+-------------+
    | 1 | SIMPLE | emp | NULL | ALL | idx_emp_agedeptIdname | NULL | NULL | NULL | 499070 | 14.50 | Using where |
    +----+-------------+-------+------------+------+-----------------------+------+---------+------+--------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)

    使用 unionall 或者 union 来替代:

  1. 口诀

    1
    2
    3
    4
    5
    6
    全职匹配我最爱,最左前缀要遵守;
    带头大哥不能死,中间兄弟不能断;
    索引列上少计算,范围之后全失效;
    LIKE百分写最右,覆盖索引不写*;
    不等空值还有OR,索引影响要注意;
    VAR引号不可丢,SQL优化有诀窍。

评论