门户网站注意事项,网站 绝对路径 相对路径,哪个网站做加盟,查征信怎么查 个人免费查询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 ALL2.1.4 关键字DISTINCT2.1.5 【Java代码】xml 循环set数组2.1.6 关键字EXISTS2.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 JOIN2.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.6.2 不冗余原则尽量扩展索引、不要新建索引 2.6.3 最大选择性原则复合索引计算SQL 2.6.4 【扩展】前缀索引Alibaba《Java开发手册》什么是前缀索引为什么要用前缀索引前缀索引缺点创建前缀索引注意事项 2.6.5 补充2.6.6 中间表ID要不要建立索引 2.7 【命令分析】EXPLAIN2.7.1 用法2.7.2 参数说明2.7.3 常量解释select_typetypeExtra 2.5.4 索引优化线上案例1线上案例2 相关阅读
索引相关 mysql 索引优化 面试官提问什么是前缀索引 高级篇(day07)-MySQL索引的创建与设计原则
EXPLAIN
MySQL explain 应用详解(吐血整理)mysql explain都不懂,还谈什么SQL优化,看这一篇文章就够了MySQL优化之explain用法详解
版本
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 的数据
SELECTtu.id,tu.user_name
FROMtu.t_user AS tu
WHEREtu.user_id 123;条件查询查询 user_id 123 或 456 的数据
SELECTtu.id,tu.user_name
FROMt_user AS tu
WHEREtu.user_id 123OR tu.user_id 456;查询重复的数据sql
查询重复的数据可以使用SQL中的GROUP BY和HAVING子句。以下是一个示例查询可以检查名为table_name的表中是否有重复的column_name列的值
SELECTcolumn_name,COUNT(*)
FROMtable_name
GROUP BYcolumn_name
HAVINGCOUNT(*) 1;该查询将按照column_name列的值进行分组并计算每个值的出现次数。然后使用HAVING子句过滤出现次数大于1的组这些组中的行即为重复数据。
请注意上述查询仅检查一个列的重复数据。如果您想要检查多个列的组合是否重复请在GROUP BY子句中包含这些列的名称。例如
SELECTcolumn_name1,column_name2,COUNT(*)
FROMtable_name
GROUP BYcolumn_name1,column_name2
HAVINGCOUNT(*) 1;该查询将按照column_name1和column_name2列的值进行分组并计算每个组的出现次数。然后使用HAVING子句过滤出现次数大于1的组这些组中的行即为重复数据。
推荐 MySQL日期时间datetime格式查询数据方式
select * from od where date(create_time)2021-02-25;1.2 【实战】应用题
问你怎么快速找出两条相同的数据字段为id
SELECTcid.id,cid.id,cid.name
FROMchihiro_id AS cid
GROUP BYcid.id HAVING COUNT(cid.id )1;验证是否正确
SELECTcid.id,cid.id ,cid.name
FROMchihiro_id AS cid
WHEREcid.id 34170
OR cid.id 15022
;删除重复的id
DELETE FROM chihiro_id
WHEREid 317021266123 OR id 317021266123
;2.中级篇
2.1 常用条件查询
2.1.1 模糊查询
select * from chihiro_area;
SELECT * FROM chihiro_area WHERE 11 and name LIKE %北;
SELECT name,area_code FROM chihiro_area WHERE 11 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
多字段查询
-- 用于多字段查询
SELECTlc.id,lc.first_hearing_address AS hearingAddress
FROMt_layer_case AS lc
WHERElc.first_hearing_address !
UNION ALL
SELECTlc.id,lc.second_hearing_address AS hearingAddress
FROMt_layer_case AS lc
WHERElc.second_hearing_address !
UNION ALL
SELECTlc.id,lc.executive_court AS hearingAddress
FROMt_layer_case AS lc
WHERElc.executive_court ! Union all 查询完统计
select a,b,c from (select a, b, c from aaunion all select a1 as a, b1 as b, c1 as c from bb
) a group by c2.1.4 关键字DISTINCT
-- 去重手机号
SELECT DISTINCT first_economics_officer_contact AS economicsOfficerContact,first_economics_officer AS economicsOfficer
FROMt_layer_case
WHEREfirst_economics_officer_contact is not null2.1.5 【Java代码】xml 循环set数组
if testcaseTypeSet ! nullAND lc.case_type INforeach collectioncaseTypeSet itemitem open( separator, close)#{item}/foreach
/if2.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)
-- 判断
SELECTCOUNT(*) AS number,hearingAddress
FROM(SELECTlc.id,lc.first_hearing_address AS hearingAddress FROMt_layer_case AS lcLEFT JOIN t_entrusted_client AS ec ON ec.id lc.entrusted_client_idLEFT JOIN t_upcoming AS tu ON tu.case_id lc.idWHERE1 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 ALLSELECTlc.id,lc.second_hearing_address AS hearingAddress FROMt_layer_case AS lcLEFT JOIN t_entrusted_client AS ec ON ec.id lc.entrusted_client_idLEFT JOIN t_upcoming AS tu ON tu.case_id lc.idWHERE1 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 ALLSELECTlc.id,lc.executive_court AS hearingAddress FROMt_layer_case AS lcLEFT JOIN t_entrusted_client AS ec ON ec.id lc.entrusted_client_idLEFT JOIN t_upcoming AS tu ON tu.case_id lc.idLEFT 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 WHERE1 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 BYhearingAddress
ORDER BYCOUNT(*) DESC LIMIT 102.1.7 关键字CASE WHEN
查询结果等于0 就返回一1 其他返回0
SELECTtfm.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,
FROMt_financial_management AS tfm2.2 存储过程
2.2.1 存储过程生成假数据
创建存储过程
delimiter //
create procedure batchInsert()
begindeclare num int; set num1;while num1000000 doinsert into key_value(username,password) values(concat(测试用户, num),123456);set numnum1;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
WHEREtlc.first_hearing_time IS NOT NULL
ANDABS(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 1WHEN bn.endDay 30 THEN 2WHEN bn.endDay 15 THEN 3ELSE不提醒END AS level,2.3.7 MySQL内连接INNER JOIN
SELECTtpc.id,tpc.case_id,tpc.entrusted_client_id,tpc.types_of_property_clues,tpc.property_clue_information,CASE WHEN bn.endDay 60 THEN 1WHEN bn.endDay 30 THEN 2WHEN bn.endDay 15 THEN 3ELSE不提醒END AS level,tlc.defendant_name,tlc.first_case_number,tlc.second_case_number,tlc.execution_case_number
FROMt_property_clues AS tpc
INNER JOIN(SELECTtpcc.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 endDayFROMt_property_clues AS tpcc
)AS bn ON bn.id tpc.id
LEFT JOINt_layer_case AS tlc ON tlc.id tpc.case_id
WHERE11
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 表的存储引擎必须是 MyISAMline 字段必须为空间数据类型而且是非空的。
CREATE SPATIAL INDEX index_line ON my_chihiro(line);全文索引FULLTEXT
全文索引主要用来查找文本中的关键字只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。全文索引允许在索引列中插入重复值和空值。不过对于大容量的数据表生成全文索引非常消耗时间和硬盘空间。
基本语法如下index_info 的存储引擎必须是 MyISAMinfo 字段必须是 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子句中经常使用两个或多个列作为过滤器则复合索引将是最佳选择。 一个表可以有多个单列索引但这些索引不是组合索引。 一个组合索引实质上为表的查询提供了多个索引以此来加快查询速度。比如在一个表中创建了一个组合索引(c1c2c3)在实际查询中系统用来实际加速的索引有三个单个索引(c1)、双列索引(c1c2)和多列索引(c1c2c3)。 删除索引
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 role1 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的时候如果typerange需要注意一下因为这个可能因为数据量问题导致索引无效。
九、左右连接关联的字段编码格式不一样
新建两个表一个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
) ENGINEInnoDB AUTO_INCREMENT2 DEFAULT CHARSETutf8;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
) ENGINEInnoDB DEFAULT CHARSETutf8;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.496sselect * from key_value WHERE username 测试用户388888
-- OK
-- 时间: 1.503sselect * 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.005sSELECT * from key_value WHERE username 测试用户3588828;
-- OK
-- 时间: 0.005s-- 测试查找主键id
-- 主键也是有索引的是所以非常快
SELECT * from key_value WHERE id 123333;
-- OK
-- 时间: 0.004s2.6 【总结】索引
根据我们上面提到的理论知识我们总结一下我们在设计索引的思路
最左前缀原则。不冗余原则。最大选择性原则。
2.6.1 最左前缀原则
最左前缀是一个很重要的原则
一般在where条件中两个及以上字段时我们会建联合索引。
高效使用索引的首要条件是知道什么样的查询会使用到索引这个问题和BTree中的最左前缀原理有关下面通过例子说明最左前缀原理。
Mysql中的索引可以以一定顺序引用多个列这种索引叫做联合索引一般的一个联合索引是一个有序元组a1,a2,a3…an其中各个元素均为数据表的一列实际上要严格定义索引需要用到关系代数。
另外单列索引可以看成联合索引元素数为1的特例
举个例子
Mysql会从左至右匹配直到遇到范围查找( like between)就停止。
select * from table1 where a1 and b2 and c3 and d9;建立的联合索引为(a,b,c,d) 实际使用的索引为(a,b,c)。因为遇到了c3就停止了d列就没有用上。
前面讲过联合索引是有序元组则Mysql实际建的索引为(a) (a,b) (a,b,c) (a,b,c,d)。
where b2 and c3 and d9根据最左匹配原则上面这个条件就没法走索引了首先必须有a。
( in)可以乱序查询优化器会帮你优化成索引可以识别的形式。
也就是说
where b2 and a1 and c3;使用的索引任然为(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
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_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]选择性越高的索引价值越大这是由BTree的性质决定的。
线上案例
通过下面的查询我们可以知道单列索引的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 goodOK查询时间: 5.204s根据业务需要我们这里选择prefix_length40的作为前缀索引
alter table lg_word add index idx_name (name(40));加了前缀索引查询的速度一下子提升至0.03s
SELECT * FROM lg_word WHERE name goodOK查询时间: 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主键默认采用btree索引和数据放在同一个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)
) ENGINEInnoDB AUTO_INCREMENT1862389149889859606 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci ROW_FORMATDYNAMIC COMMENT字典-单词中间表;通过下面的语句计算我们可以知道中间表的dict_id选择性值非常低哪这样我们需不需要建立索引。
SELECT count(*) FROM lg_word_dict -- 741190
SELECT COUNT( DISTINCT dict_id ) / COUNT(*) FROM lg_word_dict; -- 0.0006这是没加索引和加了索引的分析报告
时机idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra优化前查询11SIMPLElg_word_dictALL73953110Using where优化后查询21SIMPLElg_word_dictrefidx_dict_ididx_dict_id8const4184100
通过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 参数说明
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLElg_wordALL4520762100.00
字段说明id查询的序列号包含一组数字表示查询中执行select子句或操作表的顺序。select_type查询的类型主要是用于区别普通查询、联合查询、子查询等的复杂查询。table当前执行的表。partitions显示分区表命中的分区情况非分区表该字段为空null。type查询类型从最好到最差依次是system const eq_ref ref range index ALLpossible_keys可能应用在这张表中的索引一个或多个。查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询实际使用。key实际使用的索引如果为NULL则没有使用索引。可能原因包括没有建立索引或索引失效key_len索引中使用的字节数可通过该列计算查询中使用的索引的长度在不损失精确性的情况下长度越短越好。key_len显示的值为索引字段的最大可能长度并非实际使用长度即key_len是根据表定义计算而得不是通过表内检索出的。ref索引的哪一列被使用了如果可能的话最好是一个常数。哪些列或常量被用于查找索引列上的值。rows根据表统计信息及索引选用情况大致估算出找到所需的记录所需要读取的行数也就是说用的越少越好。filtered表示选取的行和读取的行的百分比100表示选取了100%80表示读取了80%。filtered值越高表示过滤效果越好因为这意味着通过索引可以过滤掉更多的无关行从而减少需要进一步处理的数据量提高查询效率。Extra包含不适合在其他列中显示但十分重要的额外信息。
2.7.3 常量解释
select_type
查询的类型主要是用于区别普通查询、联合查询、子查询等的复杂查询。
idselect_type解释1SIMPLE简单的select查询查询中不包含子查询或者UNION。2PRIMARY查询中若包含任何复杂的子部分最外层查询则被标记为PRIMARY 。3SUBQUERY在SELECT或WHERE列表中包含了子查询。4DERIVED在FROM列表中包含的子查询被标记为DERIVED衍生MySQL会递归执行这些子查询把结果放在临时表中。5UNION若第二个SELECT出现在UNION之后则被标记为UNION若UNION包含在FROM子句的子查询中外层SELECT将被标记为DERIVED。6UNION RESULT从UNION表获取结果的SELECT。
type
查询类型从最好到最差依次是system const eq_ref ref range index ALL
idtype解释1system表只有一行记录等于系统表这是const类型的特列平时不会出现这个也可以忽略不计。2const表示通过索引一次就找到了const用于比较primary key或者unique索引。因为只匹配一行数据所以很快。如将主键置于where列表中MySQL就能将该查询转换为一个常量。3eq_ref唯一性索引扫描对于每个索引键表中只有一条记录与之匹配。常见于主键或唯一索引扫描。4ref非唯一性索引扫描返回匹配某个单独值的所有行本质上也是一种索引访问它返回所有匹配某个单独值的行然而它可能会找到多个符合条件的行所以他应该属于查找和扫描的混合体。5range只检索给定范围的行使用一个索引来选择行key列显示使用了哪个索引一般就是在你的where语句中出现between、 、、in等的查询这种范围扫描索引比全表扫描要好因为它只需要开始于索引的某一点而结束于另一点不用扫描全部索引。6indexFull Index ScanIndex与ALL区别为index类型只遍历索引树。这通常比ALL快因为索引文件通常比数据文件小。虽然ALL和Index都是读全表但index是从索引中读取的而ALL是从硬盘读取的。7ALLFull Table Scan将遍历全表以找到匹配的行。
Extra
包含不适合在其他列中显示但十分重要的额外信息。
idtype解释1Using filesort说明MySQL会对数据使用一个外部的索引排序而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。2Using temporary使用了用临时表保存中间结果MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。3Using index condition表示相应的select操作中使用了覆盖索引Covering Index避免访问了表的数据行效率不错。如果同时出现using where表明索引被用来执行索引键值的查找如果没有同时出现using where表明索引用来读取数据而非执行查找动作。4Using where表明使用了where过滤。5Using join buffer表明使用了连接缓存,比如说在查询的时候多表join的次数非常多那么将配置文件中的缓冲区的join buffer调大一些。6impossible wherewhere子句的值总是false不能用来获取任何元组。7select tables optimized away在没有GROUP BY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作不必等到执行阶段再进行计算查询执行计划生成的阶段即完成优化。8distinct优化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)
) ENGINEInnoDB AUTO_INCREMENT1871543739270402050 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci ROW_FORMATDYNAMIC 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)时机idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra优化前查询11SIMPLElg_revlogindex_mergeidx_uid,idx_sid,idx_dayidx_sid,idx_uid9,842.67Using intersect(idx_sid,idx_uid); Using where优化后查询21SIMPLElg_revlogrefidx_uid_wordId_operTime_sididx_uid_wordId_operTime_sid8const22330.11Using index condition; Using where优化后查询31SIMPLElg_revlogrefidx_uid_wordId_operTime_sididx_uid_wordId_operTime_sid8const22331Using 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.0030SELECT 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; -- 总条数237246EXPLAIN SELECT id,word_count,wl_status FROM lg_schedule_detail WHERE del_flag0 AND (sid 1864709381096665089 AND study_date 2024-12-25 00:00:00 AND study_date 2024-12-25 23:59:59)时机idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra优化前查询11SIMPLElg_schedule_detailALL1953040Using where优化后查询21SIMPLElg_schedule_detailrangeidx_uid_sid_studyDateidx_uid_sid_studyDate2061Using 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.0000SELECT 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);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 |通过下面分析我们得出查询字段的选择性值。sql
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.0000SELECT 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);
