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

综合：

1. 查找一切带有索引的页是存在索引的，若查找的列中加入一个非索引列则查询无索引。
2. 查找条件中带有非索引的列也会使得查询非索引。
3. 查找列全为索引的，则不论条件如何都为索引的查询
4. 查询遵从左匹配准则，若查询无这索引靠左面的列，则此查找也为非索引查找
5. 字符串where条件后，>符号存在索引，<不存在索引
6. 最左匹配，只有匹配最左面一个就OK，后边的不论为啥，此次查询都为索引查询
7. 数字隐式转化，为列匹配一个不属于他的数据类型
8. 规模或者等值查询不契合最左匹配则已定位非索引查询
9. 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》》》

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