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

什么是索引：

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

2.分类

集合索引（InnoDB）-> 索引和数据同在(定位索引即查找到数据)	  一张表只能有一个集合索引(物理排序)
非集合索引（MYISAM）-> 索引和数据分离(定位索引后需要返表获取)	一张表能够有多个集合索引
经历-> MySql表规划 保留自增列主键字段
	主键为 集聚索引且仅有 保证查询功率
	自增列 防止增加数据而发生数据重排

3.原理

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

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

索引语法

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

非集合索引(InnoDB 或 MYISAM)

	一般索引 -> CREATE INDEX 索引名 ON 表(列)
	仅有索引 -> CREATE UNIQUE INDEX 索引名 ON 表(列)
	组合索引 -> CREATE INDEX 索引名 ON 表(列1,列2)
	函数索引 -> CREATE INDEX 索引名 ON 表(函数(列))

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

EXPLAIN SELECT …

	*type->查询分类*
			const -> 等值查询
			ref   -> 引用查询
			index -> 索引查询
			range -> 规模查询
			ALL   -> 全部查询
	*key->启用索引 | NULL 索引失效*

索引规矩

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

	关于字符串
		CHAR(能够) 
		VARCHAR(考虑) 
		TEXT(不推荐)
	关于数字 
		INT 转化 TINYINT -> 巨细够用
		FLOAT 转化 INT	 -> 如果保留两位小数 乘以100 用INT类型
	关于日期
		DATETIME(8字节) 转化为 TIMESTAMP(4字节)

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

	eg:性别

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

索引失效（十条规矩）

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

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

	一切查询字段都有索引（有用） -> index
	eg:EXPLAIN SELECT empno,ename,sal,hiredate FROM emp;
	条件（索引列表左匹配准则）等值（有用） -> ref
	EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename='SCOTT';
	条件规模（失效） -> range
	EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE sal > 0;

综合：

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

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

	有一个非索引字段（失效） ->ALL
	eg:EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp;

3)!= <>

	eg:EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename!='SCOTT';

4)NOT NULL

	eg:EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename IS NOT NULL;

5)函数处理

	eg:EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE CHAR_LENGTH(ename) = 5;

6)含糊查询 % 最初

	eg:EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename LIKE '%a';

7)OR 关键字

	eg:EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename = 'SCOTT' OR sal = 0;

8)等值判别左匹配

	eg:EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE sal = 0 AND hiredate = '2000-1-1 0:00:00';

9)规模查询右疏忽

	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)数字隐式转化 -> 字符串数字缺失引号

	EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename = 10

代码解析：

-- 树立组合索引
CREATE INDEX ON idex_name_sal_hiredate FROM emp(ename,sal,hiredate);
-- 十种索引失效测试
-- 第一种
-- 条件规模失效
EXPLAIN SELECT empno,ename,sal,hiredate ,job FROM emp WHERE sal>0;
-- 等值查询有用(左匹配准则，)--ref
EXPLAIN SELECT empno,ename,sal,hiredate,job FROM emp WHERE ename='scott' AND sal=0;
-- 查询一切字段都有索引(有用) --index
EXPLAIN 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';
-- >测验 （有用）--range
EXPLAIN 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>0
EXPLAIN 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>0
EXPLAIN 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) != <>

	SELECT empno,ename,sal,hiredate,job 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'	
	-->	全优化
	SELECT empno,ename,sal,hiredate,job FROM emp WHERE empno <> 
	(SELECT empno FROM emp WHERE ename = 'SCOTT')

2) OR

	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
	SELECT 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
	SELECT ename,(SELECT dname FROM dept WHERE emp.DEPTNO = dept.DEPTNO) dname FROM emp

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

	表联接比较复杂 考虑增加数据冗余 -> 空间换时刻

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

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

	SELECT empno,ename,sal from emp LIMIT 100000,10
	SELECT 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) 含糊查询

	最初匹配 "A%"
	结束匹配 表中增加反转列 运用反转列匹配 "A%"

9) 函数处理

	参照结束匹配处理过程 增加函数成果列 并设置索引

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

	Redis

代码完成：

-- 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》》》

日子要永久清醒，永久温柔，永久知进退，慢慢走，沿途有景色，背后亦有阳光。不鸣则已，一鸣惊人– 司马迁

MySQL索引,语法，索引规则,索引失效（十条规则）

什么是索引：

索引语法

索引规矩

索引失效（十条规矩）

代码解析：

SQL优化（一切的部分查询（子查询）都为索引查询）

代码完成：

索引重点：sql优化

作者信息

MySQL索引,语法，索引规则,索引失效（十条规则）

什么是索引：

索引语法

索引规矩

索引失效（十条规矩）

代码解析：

SQL优化（一切的部分查询（子查询）都为索引查询）

代码完成：

索引重点：sql优化

相关文章

ConcurrentHashMap是如何实现的？

java获取到heapdump文件后，如何快速分析？

上海三年半社招，PDD 、字节及证券面经

视频 QoE 的平衡之道—揭秘网易云信 NERTC 视频质量控制系统

作者信息