​本文源自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;

综合：

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

索引重点：sql优化

end》》》

生活要永远清醒，永远温柔，永远知进退，慢慢走，沿途有风景，背后亦有阳光。不鸣则已，一鸣惊人-- 司马迁

原文链接：https://juejin.cn/post/7220643710659657785

标签：