MySQL索引,语法，索引规则,索引失效（十条规则）|焦点速看

来源：程序猿阿嘴　2023-05-21 05:30:54

本文源自Recently祝祝，创自Recently祝祝。转载请标注出处。

什么是索引：

1.概念实质->根据索引字段排序后与行DB_ID组合而形成的索引表。作用->合理设置索引，可以极大提高查询效率。注意->索引是否启用，与搜索引擎规则相关，这些规则直接关系到SQL脚本逻辑顺序。

(资料图片仅供参考)

2.分类

rust复制代码聚集索引（InnoDB）->索引和数据同在(定位索引即查找到数据)  一张表只能有一个聚集索引(物理排序)    非聚集索引（MYISAM）->索引和数据分离(定位索引后需要返表获取)一张表可以有多个聚集索引    经验->MySql表设计 保留自增列主键字段主键为 集聚索引且唯一 保证查询效率自增列 避免添加数据而产生数据重排

3.原理

css复制代码B树 ->节点中可以容纳多个数据（等于Max_Degree 将第二个数据独立节点引出） 有利于降低树的高度 B+树 ->引入双向链表(范围查询)并配合数据冗余(空间换时间) 合理解决范围查询的需求

我理解的索引就是：有规则的查找，而非无章法的查找，给你所需要查找的事物排序，按照需要拿到我们想要的东西，那个排序所生成的表就叫做索引也称为索引表，但是这个规则也是有条件的而非自己臆想的排列，这就是索引会失效的原因了

索引语法

聚集索引(InnoDB)->添加主键 ALTER TABLE 表 ADD PRIMARY KEY 表(列)

非聚集索引(InnoDB 或 MYISAM)

sql复制代码普通索引 ->CREATE INDEX 索引名 ON 表(列)唯一索引 ->CREATE UNIQUE INDEX 索引名 ON 表(列)组合索引 ->CREATE INDEX 索引名 ON 表(列1,列2)函数索引 ->CREATE INDEX 索引名 ON 表(函数(列))

删除索引 ->DROP INDEX 索引名 ON 表

EXPLAIN SELECT ...

rust复制代码*type->查询分类*             const ->等值查询ref   ->引用查询index ->索引查询range ->范围查询ALL   ->全部查询                            *key->启用索引 | NULL 索引失效*

索引规则

1)查询业务不涉及太多数据筛选 ->不推荐创建索引 2)一张表上不要创建五个以上索引 3)组合索引字段不要超过五个 4)索引尽可能创建字段长度较小的列上

scss复制代码对于字符串 CHAR(可以) VARCHAR(考虑) TEXT(不推荐)对于数字 INT 转化 TINYINT ->大小够用FLOAT 转化 INT ->如果保留两位小数 乘以100 用INT类型对于日期DATETIME(8字节) 转化为 TIMESTAMP(4字节)

5)列数据重复率太高不需要创建索引

markdown复制代码eg:性别

6)考虑查询列合理创建组合索引(效率高于单列索引)

索引失效（十条规则）

索引->CREATE INDEX idx_name_sal_date ON emp(ename,sal,hiredate);

1)全表扫描 ->不涉及条件筛选

sql复制代码所有查询字段都有索引（有效） ->indexeg:EXPLAIN SELECT empno,ename,sal,hiredate FROM emp;条件（索引列表左匹配原则）等值（有效） ->refEXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename="SCOTT";条件范围（失效） ->rangeEXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE sal >0;

综合：

查找所有带有索引的页是存在索引的，若查找的列中加入一个非索引列则查询无索引。查找条件中带有非索引的列也会使得查询非索引。查找列全为索引的，则无论条件如何都为索引的查询查询遵循左匹配原则，若查询无这索引靠左边的列，则此查找也为非索引查找字符串where条件后，>符号存在索引，<不存在索引最左匹配，只有匹配最左边一个就OK，后面的不管为啥，此次查询都为索引查询数字隐式转换，为列匹配一个不属于他的数据类型范围或者等值查询不符合最左匹配则已定位非索引查询 range（范围查找）可能引起失效，也可能不失效，当范围查找的值在索引里时他有效，而值不在里面时则无效，没有固定的规则

2)全列扫描 ->全字段匹配

sql复制代码有一个非索引字段（失效） ->ALLeg:EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp;

3)!= <>

sql复制代码eg:EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename!="SCOTT";

4)NOT NULL

sql复制代码eg:EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename IS NOT NULL;

5)函数处理

sql复制代码eg:EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE CHAR_LENGTH(ename) = 5;

6)模糊查询 % 开头

sql复制代码eg:EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename LIKE "%a";

7)OR 关键字

ini复制代码eg:EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename = "SCOTT" OR sal = 0;

8)等值判断左匹配

ini复制代码eg:EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE sal = 0 AND hiredate = "2000-1-1 0:00:00";

9)范围查询右忽略

sql复制代码eg:EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename >"SCOTT" AND sal = 0; (有效 range)eg:EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename >"aaa" AND sal = 0; (失效 ALL)

10)数字隐式转换 ->字符串数字缺失引号

sql复制代码EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename = 10

代码解析：

sql复制代码-- 建立组合索引CREATE INDEX ON idex_name_sal_hiredate FROM emp(ename,sal,hiredate);-- 十种索引失效测试-- 第一种-- 条件范围失效EXPLAIN SELECT empno,ename,sal,hiredate ,job FROM emp WHERE sal>0;-- 等值查询有效(左匹配原则，)--refEXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename="scott" AND sal=0;-- 查询所有字段都有索引(有效) --indexEXPLAIN SELECT empno,ename,sal,hiredate FROM emp ;-- 第二种（全字段匹配，全文扫描）-- 存在非索引字段-- 失效 EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp; -- 第三种，不对等号,!=,<>(这个也遵循左匹配原则)（失效）EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename <>"scott";EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE  ename !="scott";-- >尝试 （有效）--rangeEXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename>"scott";-- < 尝试（无效）EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename<"scott";-- = 尝试（）--ref（索引查找)（必须符合最左匹配原则，否则也无效）-- 最左匹配原则，必须先从左边开始匹配，否则无效EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename>"scott";-- 无效  sal>0EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE sal>0;-- 无效 hiredate>"2021-08-31 18:23:55"EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE hiredate>"2021-08-31 18:23:55";-- 最左匹配原则 有效 -- range ename>"scott" AND sal>0EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename>"scott" AND sal>0 ;-- 最左匹配，只有匹配最左边一个就OK，后面的不管为啥，次查询都为索引查询EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename>"scott" AND hiredate<"2021-08-31 18:23:55" ;-- 第四种 is not null (无效)EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename IS NOT NULL;-- is null （有效）-- ref（索引查询）EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename IS NULL ;-- 第五种(函数处理)-- 函数--》 数字函数，字符串函数，日期函数(无效)EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE CHAR_LENGTH(ename) ;-- 第六种 （模糊查询，使用%开头）(无效)EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename LIKE "%a" ;-- 不以%开头（有效）-- range(范围查询)EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename  LIKE "a%" ;-- 第七种  or关键字（无效）EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename="sctto" OR sal=0 ;-- 第八种  等值判断最左匹配（and，最左匹配）(无最左值，无效)EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE sal = 0 AND hiredate = "2000-1-1 0:00:00" ;-- 第九种 范围查询右忽略-- 最左查询无效（前提），则也不考虑右查询了-- range(范围查询)（可能会引起失效）如下-- (有效) -- range(范围查询)-- 查找范围值在范围里时，则有效 EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename>"sctto" AND sal=0 ; EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename>"sctto" AND sal>0 ; EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename>"sctto";-- (无效)EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename>"aaa" AND sal=0 ;EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename>"aaa";-- 第十种 数字隐式转换-- 字段为string给他匹配一个int则存在一个隐式转换 （无效）EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename= 10;-- 匹配一个符合自己数据类型，却不存在的值（有效） -- ref(索引查询)EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename="10";

SQL优化（所有的部分查询（子查询）都为索引查询）

作用 ->处理"慢查询" 用户体验度程序反馈时间三秒以内要求查询效率控制在0.5秒以内注意 ->最后结果一定通过实际数据测试完成

1) != <>

sql复制代码SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename!="SCOTT"; -->半优化SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename >"SCOTT"UNIONSELECT empno,ename,sal,hiredate,job FROM emp WHERE ename < "SCOTT"-->全优化SELECT empno,ename,sal,hiredate,job FROM emp WHERE empno <>(SELECT empno FROM emp WHERE ename = "SCOTT")

2) OR

sql复制代码SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename = "SCOTT" OR sal = 800-->SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename = "SCOTT"UNIONSELECT empno,ename,sal,hiredate,job FROM emp WHERE sal = 800-->SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename = "SCOTT"UNIONSELECT empno,ename,sal,hiredate,job FROM emp WHERE empno = (SELECT empno FROM emp WHERE sal = 800)

3) 表联接效率高于子查询

sql复制代码SELECT ename,dname FROM emp LEFT JOIN dept ON emp.DEPTNO = dept.DEPTNO SELECT ename,(SELECT dname FROM dept WHERE emp.DEPTNO = dept.DEPTNO) dname FROM emp

4) 表联接数量不要超过三张最好控制在两张

markdown复制代码表联接比较复杂 考虑添加数据冗余 ->空间换时间

5) 尽量避免全表扫描即便存在也要进行分页

6) 分页( ****) 越靠后的数据查询效率越低**

sql复制代码SELECT empno,ename,sal from emp LIMIT 100000,10SELECT empno,ename,sal,job FROM emp WHERE empno >(SELECT empno FROM emp LIMIT 10000,1)AND empno < (SELECT empno FROM emp LIMIT 10010,1);

7) 杜绝使用 * 根据需求填写必要的列

8) 模糊查询

arduino复制代码开头匹配 "A%" 结尾匹配 表中添加反转列 使用反转列匹配 "A%"

9) 函数处理

markdown复制代码参照结尾匹配处理过程 添加函数结果列 并设置索引

10)NoSql数据库进行有力的支撑

markdown复制代码Redis

代码实现：

sql复制代码-- sql优化 (可用explaoin查看索引)-- 1：!= <>-- 查询不叫Scott的员工信息-- 非优化SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename != "SCOTT";SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename <>"scott";-- 优化SELECT empno,ename,sal,hiredate,job FROM emp WHERE empno <>(SELECT empno FROM emp WHERE ename="scott");-- 半优化SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename >"scott"UNION SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename <"scott";-- 2：or-- 查找员工名字叫scott工资或者800的员工-- 非优化SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename="scott" OR sal="800";-- 优化SELECT  empno,ename,sal,hiredate,job FROM emp WHERE ename="scott"UNION SELECT  empno,ename,sal,hiredate,job FROM emp WHERE sal = "800"; -- 优化SELECT  empno,ename,sal,hiredate,job FROM emp WHERE ename="scott"UNION empno,ename,sal,hiredate,job FROM emp WHERE empno = (SELECT empno FROM emp WHERE sal=800);-- 3:表联接-- 员工对应部门-- 优化SELECT ename,dname FROM emp LEFT JOIN dept ON emp.deptno=dept.deptno;-- 优化USE mytest;SELECT ename,(SELECT dname FROM dept WHERE emp.deptno=dept.deptno) dname FROM emp;-- 4:表联接的数量不要超过三张，最好控制在两张-- 5:尽量避免全表扫描，即便存在也要进行分页-- 6：分页越靠后的数据查询效率越低-- 查询10万条数据最后十行-- 非优化SELECT  empno,ename,sal FROM emp LIMIT 100000,10;-- 优化SELECT empno,ename,sal FROM emp WHERE empno>(SELECT empno FROM emp LIMIT 100000,1)AND empno <(SELECT empno FROM emp LIMIT 100010,1);-- 7:杜绝使用* 根据需求 填写必要的列-- 8:模糊查询-- 开头匹配 "A%"-- 结尾匹配 表中添加反转列 ，使用反转列"A%"-- 9:函数处理-- 根据函数处理，生成一个函数结果列，并设置索引，查找根据新列查找-- 10：nosql数据库进行有力支撑-- Redis