MySQL秘籍之索引与查询优化实战指南

What is MySQL? - MySQL Relational Databases Explained - AWS

@TOC

文章摘要

最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引，说白了就是对文本的前几个字符建立索引（具体是几个字符在建立索引时去指定），比如以产品名称的前 10 位来建索引，这样建立起来的索引更小，查询效率更快！

版本

2023年6月29日：更新sql正则查询。
2024年12月24日：迭代版本，更新模板目录。
2024年12月25日：更新索引、explain。

1. 初级篇

SQL DML 和 DDL

可以把 SQL 分为两个部分：数据操作语言 (DML) 和数据定义语言 (DDL)。
SQL (结构化查询语言)是用于执行查询的语法。
但是 SQL 语言也包含用于更新、插入和删除记录的语法。

1.1 【练体术】基础

1.1.1 库操作

1.1.1 表操作

创建表

创建一个表

DROP TABLE IF EXISTS key_value;
CREATE TABLE key_value(
    _key VARCHAR(255)    COMMENT '键' ,
    _value VARCHAR(255)    COMMENT '值' 
)  COMMENT = '键值对';

增加表字段

ALTER TABLE

给表条件一个字段

ALTER TABLE 表名 ADD `字段名` VARCHAR ( 128 )   COMMENT '备注';

ALTER TABLE t_user ADD `user_name` VARCHAR ( 128 )   COMMENT '用户名称';

1.1.2 增删改

查询和更新指令构成了 SQL 的 DML 部分：

SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据

插入一条数据

INSERT INTO 语句

INSERT INTO 语句用于向表格中插入新的行。

//语法：
INSERT INTO 表名称 VALUES (值1, 值2,....)
//我们也可以指定所要插入数据的列：
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

INSERT INTO key_value VALUES ("1","2222");
INSERT INTO key_value (_key,_value) VALUES ("2","键值对");

删除一条数据

DELETE 语句

DELETE 语句用于删除表中的行。

//语法:
DELETE FROM 表名称 WHERE 列名称 = 值

DELETE FROM key_value WHERE _key = "2";
SELECT * FROM key_value;

更新一条数据库

Update 语句

Update 语句用于修改表中的数据。

语法：
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

UPDATE key_value set _key = "我不想做主键" WHERE _key= "1";
SELECT * from key_value;

1.1.3 查询

查询所有数据

现在我们希望从 "Persons" 表中选取所有的列。请使用符号 * 取代列的名称，就像这样：

SELECT * FROM Persons

条件查询： user_id = 123 的数据

SELECT
    tu.id,
    tu.user_name
FROM
    tu.t_user AS tu 
WHERE
    tu.user_id = 123;

条件查询：查询 user_id = 123 或 456 的数据

SELECT
    tu.id,
    tu.user_name
FROM
    t_user AS tu 
WHERE
    tu.user_id = 123
    OR tu.user_id = 456;

查询重复的数据sql

查询重复的数据可以使用SQL中的GROUP BY和HAVING子句。以下是一个示例查询，可以检查名为table_name的表中是否有重复的column_name列的值：

SELECT
    column_name,
    COUNT(*) 
FROM
    table_name 
GROUP BY
    column_name 
HAVING
    COUNT(*) > 1;

该查询将按照column_name列的值进行分组，并计算每个值的出现次数。然后使用HAVING子句过滤出现次数大于1的组，这些组中的行即为重复数据。

请注意，上述查询仅检查一个列的重复数据。如果您想要检查多个列的组合是否重复，请在GROUP BY子句中包含这些列的名称。例如：

SELECT
    column_name1,
    column_name2,
    COUNT(*) 
FROM
    table_name 
GROUP BY
    column_name1,
    column_name2 
HAVING
    COUNT(*) > 1;

该查询将按照column_name1和column_name2列的值进行分组，并计算每个组的出现次数。然后使用HAVING子句过滤出现次数大于1的组，这些组中的行即为重复数据。

1.2 【实战】应用题

问：你怎么快速找出两条相同的数据？字段为id

SELECT
    cid.id,
    cid.id,
    cid.name
FROM
    chihiro_id  AS cid
GROUP BY
    cid.id  HAVING COUNT(cid.id )>1;

验证是否正确：

SELECT
    cid.id,
    cid.id ,
    cid.name
FROM
    chihiro_id  AS cid
WHERE
    cid.id  = 34170
OR cid.id  = 15022
;

删除重复的id

DELETE FROM chihiro_id
WHERE
    id = 317021266123 OR id = 
    317021266123
;

2.中级篇

2.1 常用条件查询

2.1.1 模糊查询

select * from chihiro_area;
SELECT * FROM `chihiro_area` WHERE 1=1 and name LIKE '%北';
SELECT name,area_code FROM chihiro_area WHERE 1=1 and area_code LIKE '11%';
select * from chihiro_area where parent_code  LIKE '1100%';
select * from chihiro_area WHERE name LIKE '北京%';

2.1.2 联表查询

SELECT * from sys_user;
SELECT * from sys_dept;

select 
    su.dept_id,
    su.user_name,
    sd.dept_name,
    sd.email 
from sys_user AS su 
INNER JOIN sys_dept AS sd ON su.dept_id = sd.dept_id;

2.1.3 关键字：UNION ALL

多字段查询

-- 用于多字段查询
SELECT
    lc.id,
    lc.first_hearing_address AS hearingAddress
FROM
    t_layer_case AS lc 
WHERE
    lc.first_hearing_address != '' 
UNION ALL
SELECT
    lc.id,
    lc.second_hearing_address AS hearingAddress
FROM
    t_layer_case AS lc 
WHERE
    lc.second_hearing_address != '' 
UNION ALL
SELECT
    lc.id,
    lc.executive_court AS hearingAddress
FROM
    t_layer_case AS lc 
WHERE
    lc.executive_court != ''

Union all 查询完统计

select a,b,c from (
    select a, b, c from aa
    union all 
    select a1 as a, b1 as b, c1 as c from bb
) a group by c

2.1.4 关键字：DISTINCT

-- 去重手机号
SELECT 
    DISTINCT first_economics_officer_contact AS "economicsOfficerContact",
    first_economics_officer AS "economicsOfficer"
FROM
    t_layer_case
WHERE
    first_economics_officer_contact is not null

2.1.5 【Java代码】xml 循环set数组

<if test="caseTypeSet != null">
    AND lc.case_type IN
    <foreach collection="caseTypeSet" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
</if>

2.1.6 关键字：EXISTS

实际场景：查询表a中a.id,在表b中是否存在车辆；

AND EXISTS(SELECT tpc.types_of_property_clues FROM t_property_clues AS tpc WHERE tpc.types_of_property_clues = '车辆' AND tpc.case_id = lc.id)
-- 判断
SELECT
    COUNT(*) AS number,
    hearingAddress 
FROM
    (
    SELECT
        lc.id,
        lc.first_hearing_address AS hearingAddress 
    FROM
        t_layer_case AS lc
        LEFT JOIN t_entrusted_client AS ec ON ec.id = lc.entrusted_client_id
        LEFT JOIN t_upcoming AS tu ON tu.case_id = lc.id
    WHERE
        1 = 1 
        AND lc.first_hearing_address != '' 
        AND EXISTS(SELECT tpc.types_of_property_clues FROM t_property_clues AS tpc WHERE tpc.types_of_property_clues = '车辆' AND tpc.case_id = lc.id)
    UNION ALL
    SELECT
        lc.id,
        lc.second_hearing_address AS hearingAddress 
    FROM
        t_layer_case AS lc
        LEFT JOIN t_entrusted_client AS ec ON ec.id = lc.entrusted_client_id
        LEFT JOIN t_upcoming AS tu ON tu.case_id = lc.id
    WHERE
        1 = 1 
        AND lc.second_hearing_address != '' 
        AND EXISTS(SELECT tpc.types_of_property_clues FROM t_property_clues AS tpc WHERE tpc.types_of_property_clues = '车辆' AND tpc.case_id = lc.id)
        UNION ALL
    SELECT
        lc.id,
        lc.executive_court AS hearingAddress 
    FROM
        t_layer_case AS lc
        LEFT JOIN t_entrusted_client AS ec ON ec.id = lc.entrusted_client_id
        LEFT JOIN t_upcoming AS tu ON tu.case_id = lc.id
        LEFT JOIN ( SELECT id, case_id, types_of_property_clues FROM t_property_clues WHERE types_of_property_clues = '车辆' GROUP BY case_id ) AS tpc ON tpc.case_id = lc.id 
    WHERE
        1 = 1 
        AND lc.executive_court != '' 
        AND EXISTS(SELECT tpc.types_of_property_clues FROM t_property_clues AS tpc WHERE tpc.types_of_property_clues = '车辆' AND tpc.case_id = lc.id)
    ) table1 
GROUP BY
    hearingAddress 
ORDER BY
    COUNT(*) DESC 
    LIMIT 10

2.1.7 关键字：CASE WHEN

查询结果等于0 就返回一1 ，其他返回0

SELECT
    tfm.id AS id,
    (CASE WHEN SUM(trs.repayment_amount_instalment * (lawyer_fee_proportion/100))-SUM(trs.repayment_amount* (lawyer_fee_proportion/100)) = 0 THEN 1 ELSE 0 END) AS fee_clear,
FROM
    t_financial_management AS tfm

2.2 存储过程

2.2.1 存储过程生成假数据

创建存储过程

delimiter //
create procedure batchInsert()
begin
    declare num int; 
    set num=1;
    while num<=1000000 do
        insert into key_value(`username`,
    `password`) values(concat('测试用户', num),
        '123456');
        set num=num+1;
    end while;
end
// 
delimiter ; #恢复;表示结束

调用存储过程

写好了存储过程就可以进行调用了，可以通过命令调用：

CALL batchInsert;

也可以在数据库工具的中Functions的栏目下，找到刚刚创建的存储过程直接执行。

删除存储过程

drop procedure batchInsert;

2.3 【实战】应用题

2.3.1 有关时间的语句

--  改成日期的时间戳
SELECT NOW();
SELECT UNIX_TIMESTAMP(NOW());
SELECT UNIX_TIMESTAMP('2022-12-27');

2.3.2 x日期 - y日期小于等于 40天

--     当前时间大于开庭时间，代表已开庭 
SELECT 
    tlc.first_hearing_time AS courtDate,
    CASE WHEN NOW()> tlc.first_hearing_time THEN "1" ELSE "0" END AS isOpenACourtSession
FROM 
    t_layer_case  AS tlc
WHERE
    tlc.first_hearing_time IS NOT NULL
AND
    ABS(DATEDIFF(first_hearing_time,"2022-12-27 16:56:13" )) <=40;

2.3.3 计算两个时间相差的天数

ABS(DATEDIFF(tpc.appeal_time_of_closure_and_registration,NOW())) AS "累计查封时间",

2.3.4 sql如何计算一个日期某个周期后的日期

--  查询x日期，y年后的日期
SELECT DATE_ADD(NOW(),INTERVAL 3 YEAR);

2.3.5 select语句查询近一周的数据

select * from table  where 
DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(column_time);

2.3.6 SQL利用Case When Then多条件判断

CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
WHEN 条件3 THEN 结果3
WHEN 条件4 THEN 结果4
.........
WHEN 条件N THEN 结果N
ELSE 结果X
END

Case具有两种格式。简单Case函数和Case搜索函数。
--简单Case函数
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END

CASE 
    WHEN bn.endDay < 60 THEN 1
    WHEN bn.endDay < 30 THEN 2
    WHEN bn.endDay < 15 THEN 3
    ELSE
        "不提醒"
    END AS "level",

2.3.7 MySQL内连接（INNER JOIN）

SELECT
    tpc.id,
    tpc.case_id,
    tpc.entrusted_client_id,
    tpc.types_of_property_clues,
    tpc.property_clue_information,
    CASE 
    WHEN bn.endDay < 60 THEN 1
    WHEN bn.endDay < 30 THEN 2
    WHEN bn.endDay < 15 THEN 3
    ELSE
        "不提醒"
    END AS "level",
    tlc.defendant_name,
    tlc.first_case_number,
    tlc.second_case_number,
    tlc.execution_case_number
FROM
    t_property_clues AS tpc
INNER JOIN(
    SELECT
        tpcc.id AS id,
        DATE_ADD(tpcc.appeal_time_of_closure_and_registration,INTERVAL tpcc.appeal_legal_time_of_closure YEAR) AS endTime,
        ABS(DATEDIFF(DATE_ADD(tpcc.appeal_time_of_closure_and_registration,INTERVAL tpcc.appeal_legal_time_of_closure YEAR),NOW())) AS endDay
    FROM
        t_property_clues AS tpcc
)AS bn ON bn.id = tpc.id
LEFT JOIN
    t_layer_case AS tlc ON tlc.id = tpc.case_id
WHERE
    1=1
AND ABS(DATEDIFF(bn.endTime,NOW())) < 60
;

2.3.8 between

between value1 and value2 (筛选出的条件中包括value1,但是不包括vaule2，也就是说

2.4 【理论】索引

2.4.1 储方式区分

MySQL 索引可以从存储方式、逻辑角度和实际使用的角度来进行分类。
根据存储方式的不同，MySQL 中常用的索引在物理上分为 B-树索引和HASH索引两类，两种不同类型的索引各有其不同的适用范围。

B-树索引：BTREE

B-树索引又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。
B-树索引是一个典型的数据结构，其包含的组件主要有以下几个。

叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼此相连，一个叶子节点有一个指向下一个叶子节点的指针。

分支节点：包含的条目指向索引里其他的分支节点或者叶子节点。

根节点：一个 B-树索引只有一个根节点，实际上就是位于树的最顶端的分支节点。

基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

B-树索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序。但 B-树索引必须遵循左边前缀原则，要考虑以下几点约束：

查询必须从索引的最左边的列开始。
查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
存储引擎不能使用索引中范围条件右边的列。

哈希索引：Hash

哈希（Hash）一般翻译为“散列”，也有直接音译成“哈希”的，就是把任意长度的输入（又叫作预映射，pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值。
哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B-树索引和 HASH 索引，且将 HASH 当成默认索引。
HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快，但也存在下面的一些缺点：

MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B-树索引来说，建立哈希索引会耗费更多的时间。

不能使用 HASH 索引排序。

HASH 索引只支持等值比较，如“=”“IN()”或“<=>”。

HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

2.4.2 逻辑区分

根据索引的具体用途，MySQL 中的索引在逻辑上分为以下五类

普通索引：INDEX
唯一索引：UNIQUE
主键索引：PRIMARY KEY
空间索引：SPATIAL
全文索引：FULLTEXT

普通索引：INDEX

普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。
普通索引允许在定义索引的列中插入重复值和空值。
创建普通索引时，通常使用的关键字是 INDEX 或 KEY。

基本语法如下：

CREATE INDEX index_id  ON my_chihiro(id);

唯一索引：UNIQUE

唯一索引与普通索引类似，不同的是唯一索引不仅用于提高性能，而且还用于数据完整性，唯一索引不允许将任何重复的值插入表中
唯一索引列的值必须唯一，允许有空值。
如果是组合索引，则列值的组合必须唯一。
创建唯一索引通常使用 UNIQUE 关键字。

基本语法如下：

CREATE UNIQUE INDEX index_id  ON my_chihiro(id);

主键索引：PRIMARY KEY

主键索引就是专门为主键字段创建的索引，也属于索引的一种。
主键索引是一种特殊的唯一索引，不允许值重复或者值为空。
创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

空间索引：SPATIAL

空间索引是对空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行扩展。
创建空间索引的列必须将其声明为 NOT NULL，空间索引只能在存储引擎为 MyISAM 的表中创建。
空间索引主要用于地理空间数据类型 GEOMETRY。

基本语法如下：my_chihiro 表的存储引擎必须是 MyISAM，line 字段必须为空间数据类型，而且是非空的。

CREATE SPATIAL INDEX index_line ON my_chihiro(line);

全文索引：FULLTEXT

全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。
全文索引允许在索引列中插入重复值和空值。
不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。

基本语法如下：index_info 的存储引擎必须是 MyISAM，info 字段必须是 CHAR、VARCHAR 和 TEXT。

CREATE FULLTEXT INDEX index_info ON my_chihiro(info);

2.4.3 实际开发场景

在实际应用中，索引通常分为

单列索引
复合索引/多列索引/组合索引

单列索引

单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。
单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

基本语法如下：address 字段的数据类型为 VARCHAR(20)，索引的数据类型为 CHAR(6)，查询时可以只查询 address 字段的前 6 个字符，而不需要全部查询。

CREATE INDEX index_addr ON my_chihiro(address(6));

多列索引/复合索引/联合索引

组合索引也称为复合索引或多列索引。
相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。
多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。
注意只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

基本语法如下：索引创建好了以后，查询条件中必须有 name 字段才能使用索引

CREATE INDEX idx_name_address ON tb_student(name,address);

无论是创建单列索引还是复合索引，都应考虑在查询的WHERE子句中可能经常使用的列作为过滤条件。
如果仅使用一列，则应选择单列索引，如果在WHERE子句中经常使用两个或多个列作为过滤器，则复合索引将是最佳选择。

一个表可以有多个单列索引，但这些索引不是组合索引。

一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。比如，在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。

删除索引

DROP INDEX命令，可以使用SQL DROP 命令删除索引，删除索引时应小心，因为性能可能会降低或提高。

基本语法如下：

DROP INDEX index_name;

2.5 【实践】索引

2.5.1 增删查

添加索引

alter table chihiro_member_info add index idx_name (name);

查看索引

SHOW INDEX FROM chihiro_member_info;

删除索引

DROP INDEX <索引名> ON <表名>
DROP INDEX idx_name ON chihiro_member_info;

2.5.2 索引失效

有时候我们明明加了索引了，但是索引却不生效。在哪些场景，索引会不生效呢？主要有以下十大经典场景：

一、隐式的类型转换，索引失效

我们有一个索引，字段（name）类型为varchar字符串类型，如果查询条件传了一个数字去，会导致索引失效。

EXPLAIN SELECT *    FROM chihiro_member_info WHERE name = 1;

如果给数字加上''，也就是说，传的是一个字符串，就正常走索引。

EXPLAIN SELECT *    FROM chihiro_member_info WHERE name = 1;

分析：为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。隐式的类型转换，索引会失效。

二、查询条件包含or，可能导致索引失效

我们在来看一条sql语句，name添加了索引，但是openid没有添加索引。我们使用or，下面的sql是不走索引的。

EXPLAIN SELECT *    FROM chihiro_member_info WHERE name = "123" or openid = "123";

分析：对于 or+没有索引的openid这种情况，假设它走 name的索引，但是走到 openid查询条件时，它还得全表扫描，也就是需要三步过程： 全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就完事。Mysql优化器处于效率与成本考虑，遇到 or条件，让索引失效。

当 name和role都是索引时，使用一张表中的多个索引时，mysql会将多个索引合并在一起。

EXPLAIN SELECT * FROM chihiro_member_info WHERE name = "123" or role = "123";

注意：如果or条件的列都加了索引，索引可能会走也可能不走，大家可以自己试一试哈。但是平时大家使用的时候，还是要注意一下这个or，学会用explain分析。遇到不走索引的时候，考虑拆开两条SQL。

三、like通配符可能导致索引失效

并不是用了 like通配符索引一定会失效，而是 like查询是以 %开头，才会导致索引失效。

EXPLAIN SELECT * FROM chihiro_member_info WHERE name LIKE '%陈';

把 %放到后面，索引还是正常走的。

EXPLAIN SELECT * FROM chihiro_member_info WHERE name LIKE '陈%';

分析：既然 like查询以 %开头，会导致索引失效。我们如何优化？

使用覆盖索。

把 %放后面。

四、查询条件不满足联合索引的最左匹配原则

Mysql建立联合索引时，会遵循左前缀匹配原则，既最左优先。如果你建立一个（a,b,c）的联合索引，相当于建立了(a)、(a,b)、(a,b,c)。

我们先添加一个联合索引：

alter table chihiro_member_info add index idx_name_role_openid (name,role,openid);

查看表的索引：

SHOW INDEX FROM chihiro_member_info;

有一个联合索引idx_name_role_openid，我们执行这个SQL，查询条件是role，索引是无效：

EXPLAIN SELECT * FROM chihiro_member_info WHERE role = 0;

在联合索引中，查询条件满足最左匹配原则时，索引才正常生效。

EXPLAIN SELECT * FROM chihiro_member_info WHERE name = "刘";

五、在索引列上使用mysql的内置函数

我们先给创建时间添加一个索引。

ALTER TABLE chihiro_member_info ADD INDEX idx_create_time(create_time);

虽然create_time加了索引，但是因为使用了mysql的内置函数DATE_ADD()，导致直接全表扫描了。

EXPLAIN SELECT * FROM chihiro_member_info WHERE DATE_ADD(create_time,INTERVAL 1 DAY) = '2022-10-10 00:00:00';

分析：一般这种情况怎么优化呢？可以把内置函数的逻辑转移到右边，如下：

EXPLAIN SELECT * FROM chihiro_member_info WHERE create_time = DATE_ADD('2022-10-10 00:00:00',INTERVAL -1 DAY);

六、对索引进行列运算（如，+、-、*、/）,索引不生效

给 role字段（tinyint）添加一个索引。

-- 添加索引
ALTER TABLE chihiro_member_info ADD INDEX idex_role(role);

虽然role加了索引，但是因为它进行运算，索引直接迷路了。如图：

EXPLAIN SELECT * FROM chihiro_member_info WHERE role+1 = 1;

分析：不可以对索引列进行运算，可以在代码处理好，再传参进去。

七、索引字段上使用（！= 或者 < >），索引可能失效

给 role字段（tinyint）添加一个索引。

-- 添加索引
ALTER TABLE chihiro_member_info ADD INDEX idex_role(role);

注意：我在mysql 5.7.26测试，测试结果有所不同，可以根据mysql版本去测试。

查看mysql版本

SELECT VERSION() FROM DUAL;

!=：正常走的索引。

EXPLAIN SELECT * FROM chihiro_member_info WHERE role != 2;

<>：正常走的索引。

EXPLAIN SELECT * FROM chihiro_member_info WHERE role <> 2;

分析：其实这个也是跟mySQL优化器有关，如果优化器觉得即使走了索引，还是需要扫描很多很多行的哈，它觉得不划算，不如直接不走索引。平时我们用！= 或者< >，not in的时候，可以先使用 EXPLAIN去看看索引是否生效。

八、索引字段上使用is null， is not null，索引可能失效

给 role字段（tinyint）添加一个索引和 name字段（varchar）添加索引。

-- 添加索引
ALTER TABLE chihiro_member_info ADD INDEX idex_role(role);
ALTER TABLE chihiro_member_info ADD INDEX idex_name(name);

单个字段 role字段加上索引，查询 role 为空的语句，会走索引：

EXPLAIN SELECT * FROM chihiro_member_info WHERE role is not null;

两字字段用 or链接起来，索引就失效了。分析：很多时候，也是因为数据量问题，导致了MySQL优化器放弃走索引。同时，平时我们用explain分析SQL的时候，如果type=range，需要注意一下，因为这个可能因为数据量问题，导致索引无效。

九、左右连接，关联的字段编码格式不一样

新建两个表，一个user，一个user_job：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `user_job` (
  `id` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

user表的name字段编码是utf8mb4，而user_job表的name字段编码为utf8。

执行左外连接查询，user_job表还是走全表扫描。

如果把它们的name字段改为编码一致，相同的SQL，还是会走索引。

分析：所以大家在做表关联时，注意一下关联字段的编码问题。

十、优化器选错了索引

MySQL 中一张表是可以支持多个索引的。你写SQL语句的时候，没有主动指定使用哪个索引的话，用哪个索引是由MySQL来确定的。

我们日常开发中，不断地删除历史数据和新增数据的场景，有可能会导致MySQL选错索引。那么有哪些解决方案呢？

使用force index 强行选择某个索引；
修改你的SQl，引导它使用我们期望的索引；
优化你的业务逻辑；
优化你的索引，新建一个更合适的索引，或者删除误用的索引。

2.5.3 索引速度对比

测试数据量量400万，字段包含：id、username、password

-- 数据量量400万，字段包含：id、username、password

-- 没有索引下查询
SELECT * FROM key_value;

select * from key_value WHERE username = '测试用户388888'
-- > OK
-- > 时间: 1.496s

select * from key_value WHERE username = '测试用户388888'
-- > OK
-- > 时间: 1.503s

select * from key_value WHERE username = '测试用户388888'
-- > OK
-- > 时间: 1.475s

-- 创建索引后：
SELECT * from key_value WHERE username = '测试用户388888';

SELECT * from key_value WHERE username = '测试用户388888'
-- > OK
-- > 时间: 0.005s

SELECT * from key_value WHERE username = '测试用户3588828';
-- > OK
-- > 时间: 0.005s

-- 测试查找主键id 
-- 主键也是有索引的是，所以非常快
SELECT * from key_value WHERE id = 123333;
-- > OK
-- > 时间: 0.004s

2.6 【总结】索引

根据我们上面提到的理论知识，我们总结一下我们在设计索引的思路：

最左前缀原则。
不冗余原则。
最大选择性原则。

2.6.1 最左前缀原则

最左前缀是一个很重要的原则

一般在where条件中两个及以上字段时，我们会建联合索引。

高效使用索引的首要条件是知道什么样的查询会使用到索引，这个问题和B+Tree中的最左前缀原理有关，下面通过例子说明最左前缀原理。

Mysql中的索引可以以一定顺序引用多个列，这种索引叫做联合索引，一般的，一个联合索引是一个有序元组，其中各个元素均为数据表的一列，实际上要严格定义索引需要用到关系代数。

另外，单列索引可以看成联合索引元素数为1的特例

举个例子：

Mysql会从左至右匹配，直到遇到范围查找(> < like between)就停止。

select * from table1 where a=1 and b=2 and c<3 and d=9;

建立的联合索引为：(a,b,c,d) 实际使用的索引为(a,b,c)。因为遇到了c<3就停止了，d列就没有用上。

前面讲过联合索引是有序元组，则Mysql实际建的索引为：(a) (a,b) (a,b,c) (a,b,c,d)。

where b=2 and c=3 and d=9；

根据最左匹配原则，上面这个条件就没法走索引了，首先必须有a。

(= in)可以乱序，查询优化器会帮你优化成索引可以识别的形式。

也就是说：

where b=2 and a=1 and c<3;

使用的索引任然为(a,b,c)组合。

线上案例：

索引：idx_whid_distributionorderid(wh_id,distribution_order_id)
索引组合：(wh_id) ,(wh_id,distribution_order_id)

相当于建了一个wh_id的单列索引，也就是说当你要根据wh_id查询时，是不需要再新建索引了。

2.6.2 不冗余原则

尽量扩展索引、不要新建索引

能用单索引，不用联合索引；
能用窄索引，不用宽索引；
能复用索引，不新建索引。

MySQL目前主要索引有：FULLTEXT,HASH,BTREE

好的索引可以提高我们的查询效率，不好的索引不但不会起作用，反而给DB带来负担。基于BTREE结构，插入、修改都会重新调整索引结构，存储成本增加，写效率降低，同时DB系统也要消耗资源去维护。

基于刚才的最左匹配原则，尽量在原有基础上扩展索引，不要新增索引。

线上案例：

建表语句如下

CREATE TABLE person_info(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
    KEY idx_name (name(10))
);

我们知道，通过 idx_name_birthday_phone_number 复合索引就可以对name列进行快速搜索，再创建一个专门针对name列的索引就算是一个冗余索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。

重复索引

CREATE TABLE repeat_index_demo (
    col1 INT PRIMARY KEY,
    col2 INT,
    UNIQUE uk_idx_c1 (col1),
    INDEX idx_c1 (col1)
)

我们看到对col1列重复建立索引，col1既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。

来看这个建表语句有什么问题：

CREATE TABLE `lg_schedule_detail` (
  `id` bigint NOT NULL COMMENT '主键',
  `sid` bigint NOT NULL COMMENT '计划id',
  `uid` bigint unsigned NOT NULL DEFAULT '0' COMMENT '用户uid',
  `wlid` bigint DEFAULT NULL COMMENT '学习词组id',
  `study_date` date DEFAULT NULL COMMENT '学习日期',
  `day` int DEFAULT NULL COMMENT '学习天数',
  `wl_status` int DEFAULT NULL COMMENT '词组状态（1：记忆 2：复习）',
  `plan_status` int DEFAULT NULL COMMENT '计划状态（1：未开始 2：学习中 3：已完成 ）',
  `del_flag` int DEFAULT '0' COMMENT '删除标志',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_sid_uid_wlid_day` (`sid`,`uid`,`wlid`,`day`),
  KEY `idx_sid_study_date_wl_status_del_flag` (`sid`,`study_date`,`wl_status`,`del_flag`,`uid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='计划详细表';

第一个问题：冗余索引两个sid、uid。

第二个问题：索引的选择性低sid、uid、day、wl_status、del_flag、study_date

SELECT COUNT(*) FROM lg_schedule_detail; -- 总条数237246
SELECT COUNT( DISTINCT sid ) / COUNT(*) FROM lg_schedule_detail; -- 0.0016
SELECT COUNT( DISTINCT uid ) / COUNT(*) FROM lg_schedule_detail; -- 0.0009
SELECT COUNT( DISTINCT wlid ) / COUNT(*) FROM lg_schedule_detail; -- 0.1667
SELECT COUNT( DISTINCT day ) / COUNT(*) FROM lg_schedule_detail; -- 0.0000
SELECT COUNT( DISTINCT wl_status ) / COUNT(*) FROM lg_schedule_detail; -- 0.0000
SELECT COUNT( DISTINCT del_flag ) / COUNT(*) FROM lg_schedule_detail; -- 0.0000
SELECT COUNT( DISTINCT study_date ) / COUNT(*) FROM lg_schedule_detail; -- 0.0018

第三个问题：status、is_deleted列不建议建索引。

2.6.3 最大选择性原则

接下来我们说说那些字段适合建索引。

选择区分度高列做索引

什么是区分度高的字段呢？

一般两种情况不建议建索引：

一两千条甚至几百条，没必要建索引，让查询做全表扫描就好了。

因为不是你建了就一定会走索引，执行计划会选择一个最优的方式，MySQL辅助索引的叶子节点并不直接存储实际数据，只是主建ID，再通过主键索引二次查找。这么一来全表可能很有可能效率更高。

索引选择性较低的情况。

所谓选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值。

计算公式：

Index Selectivity = Cardinality / #T

显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。

线上案例：

通过下面的查询，我们可以知道单列索引的source字段最好不用做索引字段，因为索引选择性（Selectivity = 0.0000）太低了。

而name字段的索引选择性（Selectivity = 0.9214）值很高，我们建议加上索引。

SELECT count( DISTINCT ( NAME ))/ count(*) AS Selectivity  FROM lg_word;
// ---------------------------------------------------
SELECT count( DISTINCT ( source ))/ count(*) AS Selectivity  FROM lg_word;

复合索引计算SQL

接下来我们看看复合索引的查询。

SELECT count(DISTINCT ( concat( NAME, example_id ) ))/ count(*) AS Selectivity  FROM lg_word

从值来看，这里建联合索引的价值不大。一个name搞定。

那么我们在建一个索引或联合索引的时候拿不准的时候可以先计算下选择性值以及通过explain测试。

一般情况，status、is_deleted列不建议建索引。

创建复合索引，需要注意把区分度最大的放到最前面。也就是值越大的放前面，当然需根据时间场景和sql通过执行计划进行优化。
前缀索引：有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。

2.6.4 【扩展】前缀索引

通过上面的介绍，我们知道了前缀索引，我们这节主要介绍下前缀索引的使用方式。

Alibaba《Java开发手册》

【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

什么是前缀索引

所谓前缀索引，说白了就是对文本的前几个字符建立索引（具体是几个字符在建立索引时去指定），比如以产品名称的前 10 位来建索引，这样建立起来的索引更小，查询效率更快！

为什么要用前缀索引？

一般来说，当某个字段的数据量太大，而且查询又非常的频繁时，使用前缀索引能有效的减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度。

比如，客户店铺名称，有的名称很长，有的很短，如果完全按照全覆盖来建索引，索引的存储空间可能会非常的大，有的表如果索引创建的很多，甚至会出现索引存储的空间都比数据表的存储空间大很多，因此对于这种文本很长的字段，我们可以截取前几个字符来建索引，在一定程度上，既能满足数据的查询效率要求，又能节省索引存储空间。

前缀索引缺点

MySQL 中无法使用前缀索引进行ORDER BY 和 GROUP BY，也无法用来进行覆盖扫描，当字符串本身可能比较长，而且前几个字符完全相同，这个时候前缀索引的优势已经不明显了，就没有创建前缀索引的必要了。

因此这又回到前面所说的，那就是索引的选择性！

索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行，数据查询速度更快！

当某个字段内容的前几位区分度很高的时候，这个时候采用前缀索引，可以在查询性能和空间存储方面达到一个很高的性价比。

创建前缀索引

创建前缀索引之前我们先计算索引字段的选择性（Selectivity）值的大小[0-1]。

计算某字段全列的区分度。

SELECT COUNT( DISTINCT NAME ) / COUNT(*) FROM lg_word; -- 0.9214

再计算前缀长度为多少时和全列的区分度最相似。

SELECT COUNT(*) FROM lg_word; -- 总数据量：4661298条
SELECT COUNT( DISTINCT NAME ) / COUNT(*) FROM lg_word; -- 0.9214
SELECT COUNT(DISTINCT LEFT ( NAME, 5 )) / COUNT(*)  FROM lg_word; -- 0.0811
SELECT COUNT(DISTINCT LEFT ( NAME, 10 )) / COUNT(*)  FROM lg_word; -- 0.4916
SELECT COUNT(DISTINCT LEFT ( NAME, 15 )) / COUNT(*)  FROM lg_word; -- 0.7770
SELECT COUNT(DISTINCT LEFT ( NAME, 20 )) / COUNT(*)  FROM lg_word; -- 0.8745
SELECT COUNT(DISTINCT LEFT ( NAME, 25 )) / COUNT(*)  FROM lg_word; -- 0.9055
SELECT COUNT(DISTINCT LEFT ( NAME, 30 )) / COUNT(*)  FROM lg_word; -- 0.9154
SELECT COUNT(DISTINCT LEFT ( NAME, 35 )) / COUNT(*)  FROM lg_word; -- 0.9190
SELECT COUNT(DISTINCT LEFT ( NAME, 40 )) / COUNT(*)  FROM lg_word; -- 0.9203
SELECT COUNT(DISTINCT LEFT ( NAME, 45 )) / COUNT(*)  FROM lg_word; -- 0.9209
SELECT COUNT(DISTINCT LEFT ( NAME, 50 )) / COUNT(*)  FROM lg_word; -- 0.9211
SELECT COUNT(DISTINCT LEFT ( NAME, 60 )) / COUNT(*)  FROM lg_word; -- 0.9213
SELECT COUNT(DISTINCT LEFT ( NAME, 100 )) / COUNT(*)  FROM lg_word; -- 0.9214

从上面查询结果来看，当prefix_length为40时，区分度为0.9203，与全列的区分度0.9214非常接近，但索引文件的大小和维护开销会比全列索引小。

我们先查询一遍没加前缀索引的速度：

SELECT * FROM lg_word WHERE `name` = "good"
> OK
> 查询时间: 5.204s

根据业务需要，我们这里选择prefix_length40的作为前缀索引：

alter table lg_word add index idx_name (name(40));

加了前缀索引查询的速度，一下子提升至0.03s：

SELECT * FROM lg_word WHERE `name` = "good"
> OK
> 查询时间: 0.03s

注意事项

是不是所有的字段，都适合用前缀索引呢？

显然不是，当某个索引的字符串列很大时，创建的索引也就变得很大，为了减小索引体积，提高索引的扫描速度，使用索引的前部分字符串作为索引值，这样索引占用的空间就会大大减少，并且索引的选择性也不会降低很多，这时前缀索引显现的作用就会非常明显，前缀索引本质是索引查询性能和存储空间的一种平衡。

对于BLOB和TEXT列进行索引，或者非常长的VARCHAR列，就必须使用前缀索引，因为 MySQL 不允许索引它们的全部长度。

但是如果某个字段内容，比如前缀部分相似度很高，此时的前缀索引显现效果就不会很明显，采用覆盖索引效果会更好！

2.6.5 补充

索引列不能参与计算

比如：

SELECT * FROM lg_dict WHERE from_unixtime( create_time ) = '2024-06-06'
--     FROM_UNIXTIME() 是 MySQL 中的一个函数，用于将 Unix 时间戳转换为日期时间格式。Unix 时间戳是自 1970 年 1 月 1 日以来的秒数。

就不能使用到索引，语句应该写成：

SELECT *  FROM lg_dict WHERE create_time = unix_timestamp('2024-06-06');

主键最好使用自增型

保证数据连续性（MySQL innodb主键默认采用b+tree，索引和数据放在同一个btree中），不要使用uuid、hash、md5等做主键。

不要使用前匹配的like查询

不要使用前匹配的like查询，会导致索引失效。可以使用后匹配like，如"xxx%"。

字符串尽量使用前缀索引

在字符串列上创建索引，尽量使用前缀索引。前缀基数根据具体业务，在匹配度和存储量（索引的存储量）之前做一个平衡。

不要使用not inlike

不要使用not inlike，会导致索引失效。not in可以用not exists替换。in和or所在列最好有索引

其实数据库索引调优，光靠理论是不行的，需要结合实际情况。MySQL机制复杂，如查询优化策略和各种引擎的实现差异等都会使情况变复杂。我们在了解这些原则和基础之上，要不断的实践和总结，从而真正达到高效使用MySQL索引的目的。

2.6.6 中间表ID要不要建立索引

建表SQL

CREATE TABLE `lg_word_dict` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `word_id` bigint NOT NULL COMMENT '单词id',
  `dict_id` bigint NOT NULL COMMENT '字典id',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_dict_id` (`dict_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1862389149889859606 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='字典-单词中间表';

通过下面的语句计算，我们可以知道中间表的dict_id选择性值非常低，哪这样我们需不需要建立索引。

SELECT count(*) FROM `lg_word_dict` -- 741190
SELECT COUNT( DISTINCT dict_id ) / COUNT(*) FROM lg_word_dict; -- 0.0006

这是没加索引和加了索引的分析报告

时机	id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
优化前查询1	1	SIMPLE	lg_word_dict		ALL					739531	10	Using where
优化后查询2	1	SIMPLE	lg_word_dict		ref	idx_dict_id	idx_dict_id	8	const	4184	100

通过explain我们发现，加了索引的rows行数明显降低，rows是根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好。

filtered表示选取的行和读取的行的百分比，100表示选取了100%，80表示读取了80%。filtered值越高，表示过滤效果越好，因为这意味着通过索引可以过滤掉更多的无关行，从而减少需要进一步处理的数据量，提高查询效率。

2.7 【命令分析】EXPLAIN

线上业务最怕出现慢SQL，慢SQL可能会导致系统响应变慢，甚至出现系统崩溃的情况，从而影响用户体验和业务正常运行。

慢SQL的主要原因包括：SQL语句设计不合理、数据库索引设置不当、数据库表结构设计不合理、数据库服务器资源不足等。

为了提高线上业务的性能，我们需要对慢SQL进行优化。我们这节主要介绍使用EXPLAIN优化SQL语句。

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

2.7.1 用法

通过EXPLAIN，我们可以分析出以下结果：

表的读取顺序。
数据读取操作的操作类型。
哪些索引可以使用。
哪些索引被实际使用。
表之间的引用。
每张表有多少行被优化器查询。

EXPLAIN +SQL语句

EXPLAIN SELECT * FROM `lg_word`;

2.7.2 参数说明

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	lg_word		ALL					4520762	100.00

字段	说明
`id`	查询的序列号，包含一组数字，表示查询中执行`select`子句或操作表的顺序。
`select_type`	查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
`table`	当前执行的表。
`partitions`	显示分区表命中的分区情况，非分区表该字段为空（null）。
`type`	查询类型，从最好到最差依次是：`system > const > eq_ref > ref > range > index > ALL`
`possible_keys`	可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
`key`	实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）
`key_len`	索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在`不损失精确性的情况下，长度越短越好`。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
`ref`	索引的哪一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。
`rows`	根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好。
`filtered`	表示选取的行和读取的行的百分比，100表示选取了100%，80表示读取了80%。 `filtered`值越高，表示过滤效果越好，因为这意味着通过索引可以过滤掉更多的无关行，从而减少需要进一步处理的数据量，提高查询效率。
`Extra`	包含不适合在其他列中显示但十分重要的额外信息。

2.7.3 常量解释

select_type

查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

id	select_type	解释
1	`SIMPLE`	简单的select查询`，查询中`不包含子查询或者`UNION`。
2	`PRIMARY`	查询中若`包含任何复杂的`子部分，最外层查询则被标记为`PRIMARY` 。
3	`SUBQUERY`	在SELECT或WHERE列表中包含了子查询。
4	`DERIVED`	在FROM列表中包含的`子查询被标记为DERIVED`（衍生），MySQL会递归执行这些子查询，把`结果放在临时表`中。
5	`UNION`	若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED。
6	`UNION RESULT`	从UNION表获取结果的SELECT。

type

查询类型，从最好到最差依次是：system > const > eq_ref > ref > range > index > ALL

id	type	解释
1	`system`	表只有一行记录（等于系统表），这是`const`类型的特列，平时不会出现，这个也可以忽略不计。
2	`const`	表示通过索引一次就找到了，`const`用于比较`primary key`或者`unique`索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
3	`eq_ref`	唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
4	`ref`	非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
5	`range`	只检索给定范围的行，使用一个索引来选择行，`key`列显示使用了哪个索引，一般就是在你的`where`语句中出现`between、< 、>、in`等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
6	`index`	`Full Index Scan`，`Index`与`ALL`区别为`index`类型只遍历索引树。这通常比`ALL`快，因为索引文件通常比数据文件小。虽然`ALL`和`Index`都是读全表，但`index`是从索引中读取的，而`ALL`是从硬盘读取的。
7	`ALL`	`Full Table Scan`将遍历全表以找到匹配的行。

Extra

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

id	type	解释
1	`Using filesort`	说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
2	`Using temporary`	使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序`order by`和分组查询`group by`。
3	`Using index condition`	表示相应的`select`操作中使用了覆盖索引（`Covering Index`），避免访问了表的数据行，效率不错。如果同时出现`using where`，表明索引被用来执行索引键值的查找；如果没有同时出现`using where`，表明索引用来读取数据而非执行查找动作。
4	`Using where`	表明使用了`where`过滤。
5	`Using join buffer`	表明使用了连接缓存,比如说在查询的时候，多表`join`的次数非常多，那么将配置文件中的缓冲区的j`oin buffer`调大一些。
6	`impossible where`	`where`子句的值总是`false`，不能用来获取任何元组。
7	`select tables optimized away`	在没有`GROUP BY`子句的情况下，基于索引优化`MIN/MAX`操作或者对于MyISAM存储引擎优化`COUNT(*)`操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
8	`distinct`	优化`distinct`操作，在找到第一匹配的元组后即停止找同样值的动作。

2.5.4 索引优化

线上案例1

优化前建表语句，目前都是单例索引。

CREATE TABLE `lg_revlog` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `uid` bigint NOT NULL COMMENT '用户id',
  `sid` bigint unsigned DEFAULT '0' COMMENT '计划 id',
  `sd_id` bigint unsigned DEFAULT '0' COMMENT '计划详细 id',
  `dict_id` bigint DEFAULT NULL COMMENT '字典 id',
  `word_id` bigint NOT NULL COMMENT '单词id',
  `word_type` int DEFAULT NULL COMMENT '学习天数（1：记忆 2：复习）',
  `oper_time` datetime DEFAULT NULL COMMENT '操作时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_uid` (`uid`),
  KEY `idx_word_id` (`word_id`),
  KEY `idx_sid` (`sid`),
  KEY `idx_day` (`word_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1871543739270402050 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='单词复习记录';

根据上面的建表语句，我们现查询一遍现在的速度。

-- 优化前查询1：这里用的是单例索引
EXPLAIN SELECT COUNT( * ) AS total FROM lg_revlog WHERE (uid = 1737120070143250433 AND sid = 1864709381096665089 AND oper_time >= '2024-12-25 00:00:00' AND oper_time <= '2024-12-25 23:59:59' AND word_type = 1)

-- 优化后查询2
EXPLAIN SELECT COUNT( * ) AS total FROM lg_revlog WHERE (uid = 1737120070143250433 AND sid = 1864709381096665089 AND oper_time >= '2024-12-25 00:00:00' AND oper_time <= '2024-12-25 23:59:59' AND word_type = 1)

-- 优化后查询3
EXPLAIN SELECT word_id FROM lg_revlog WHERE (uid = 1737120070143250433 AND sid = 1864709381096665089 AND word_type = 1)

时机	id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
优化前查询1	1	SIMPLE	lg_revlog	index_merge	idx_uid,idx_sid,idx_day	idx_sid,idx_uid	9,8		4	2.67	Using intersect(idx_sid,idx_uid); Using where
优化后查询2	1	SIMPLE	lg_revlog	ref	idx_uid_wordId_operTime_sid	idx_uid_wordId_operTime_sid	8	const	2233	0.11	Using index condition; Using where
优化后查询3	1	SIMPLE	lg_revlog	ref	idx_uid_wordId_operTime_sid	idx_uid_wordId_operTime_sid	8	const	2233	1	Using index condition; Using where

通过下面分析，我们得出查询字段的选择性值。

SELECT COUNT( DISTINCT word_id ) / COUNT(*) FROM lg_revlog; -- 0.3282
SELECT COUNT( DISTINCT oper_time ) / COUNT(*) FROM lg_revlog; -- 0.0903
SELECT COUNT( DISTINCT uid ) / COUNT(*) FROM lg_revlog; -- 0.0042
SELECT COUNT( DISTINCT sid ) / COUNT(*) FROM lg_revlog; -- 0.0030

SELECT count(DISTINCT ( concat( uid, word_id ) ))/ count(*) AS Selectivity  FROM lg_revlog; -- 0.5244
SELECT count(DISTINCT ( concat( uid, word_id,oper_time ) ))/ count(*) AS Selectivity  FROM lg_revlog; -- 1.0000
SELECT count(DISTINCT ( concat( uid, word_id,oper_time,sid ) ))/ count(*) AS Selectivity  FROM lg_revlog; -- 1.0000

根据这个值我们去建立联合索引，来替代现在的单列索引。

根据业务需求判断，每次查询必须携带uid因此我们把uid权重放在第一，其余的根据选择性的值去高到低排序。

CREATE INDEX idx_uid_wordId_operTime_sid ON lg_revlog(uid, word_id,oper_time,sid);

线上案例2

根据线上的sql创建合适的索引，我们现查询一遍数据分析下。

SELECT COUNT(*) FROM lg_schedule_detail; -- 总条数237246

EXPLAIN SELECT id,word_count,wl_status FROM lg_schedule_detail WHERE del_flag=0 AND (sid = 1864709381096665089 AND study_date >= '2024-12-25 00:00:00' AND study_date <= '2024-12-25 23:59:59')

时机	id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
优化前查询1	1	SIMPLE	lg_schedule_detail		ALL					195304	0	Using where
优化后查询2	1	SIMPLE	lg_schedule_detail		range	idx_uid_sid_studyDate	idx_uid_sid_studyDate	20		6	1	Using index condition; Using where

通过下面分析，我们得出查询字段的选择性值。

SELECT COUNT( DISTINCT study_date ) / COUNT(*) FROM lg_schedule_detail; -- 0.0018
SELECT COUNT( DISTINCT sid ) / COUNT(*) FROM lg_schedule_detail; -- 0.0016
SELECT COUNT( DISTINCT uid ) / COUNT(*) FROM lg_schedule_detail; -- 0.0009
SELECT COUNT( DISTINCT wl_status ) / COUNT(*) FROM lg_schedule_detail; -- 0.0000

SELECT count(DISTINCT ( concat( uid, sid  ) ))/ count(*) AS Selectivity  FROM lg_schedule_detail; -- 0.0016
SELECT count(DISTINCT ( concat( uid, sid, study_date ) ))/ count(*) AS Selectivity  FROM lg_schedule_detail; -- 0.1896

根据这个值我们去建立联合索引，来替代现在的单列索引。

根据业务需求判断，每次查询必须携带uid因此我们把uid权重放在第一，其余的根据选择性的值去高到低排序。

CREATE INDEX idx_uid_sid_studyDate ON lg_schedule_detail(uid,sid,study_date);

MySQL秘籍之索引与查询优化实战指南

MySQL秘籍之索引与查询优化实战指南

文章摘要

相关阅读

索引相关

EXPLAIN

版本

1. 初级篇

1.1 【练体术】基础

1.1.1 库操作

1.1.1 表操作

创建一个表

增加表字段

1.1.2 增删改

插入一条数据

删除一条数据

更新一条数据库

1.1.3 查询

查询所有数据

条件查询： user_id = 123 的数据

条件查询：查询 user_id = 123 或 456 的数据

查询重复的数据sql

推荐 MySQL日期时间datetime格式查询数据方式

1.2 【实战】应用题

2.中级篇

2.1 常用条件查询

2.1.1 模糊查询

2.1.2 联表查询

2.1.3 关键字：UNION ALL

2.1.4 关键字：DISTINCT

2.1.5 【Java代码】xml 循环set数组

2.1.6 关键字：EXISTS

2.1.7 关键字：CASE WHEN

2.2 存储过程

2.2.1 存储过程生成假数据

创建存储过程

调用存储过程

删除存储过程

2.3 【实战】应用题

2.3.1 有关时间的语句

2.3.2 x日期 - y日期 小于等于 40天

2.3.3 计算两个时间相差的天数

2.3.4 sql如何计算一个日期某个周期后的日期

2.3.5 select语句查询近一周的数据

2.3.6 SQL利用Case When Then多条件判断

2.3.7 MySQL内连接（INNER JOIN）

2.3.8 between

2.4 【理论】索引

2.4.1 储方式区分

B-树索引：BTREE

哈希索引：Hash

2.4.2 逻辑区分

普通索引：INDEX

唯一索引：UNIQUE

主键索引：PRIMARY KEY

空间索引：SPATIAL

全文索引：FULLTEXT

2.4.3 实际开发场景

单列索引

多列索引/复合索引/联合索引

删除索引

2.5 【实践】索引

2.5.1 增删查

添加索引

查看索引

删除索引

2.5.2 索引失效

一、隐式的类型转换，索引失效

二、查询条件包含or，可能导致索引失效

三、like通配符可能导致索引失效

四、查询条件不满足联合索引的最左匹配原则

五、在索引列上使用mysql的内置函数

六、对索引进行列运算（如，+、-、*、/）,索引不生效

七、索引字段上使用（！= 或者 < >），索引可能失效

八、索引字段上使用is null， is not null，索引可能失效

九、左右连接，关联的字段编码格式不一样

十、优化器选错了索引

2.5.3 索引速度对比

2.6 【总结】索引

2.6.1 最左前缀原则

2.3.2 x日期 - y日期小于等于 40天