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

Recently祝祝
1,156 阅读7分钟

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

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

avatar
文章
阅读
粉丝
相关推荐
MySQl 索引之道
36k阅读
 · 
447点赞
# MySQL索引失效总结
8.9k阅读
 · 
24点赞
MySQL索引-索引匹配规则
2.5k阅读
 · 
3点赞
(四)MySQL之索引初识篇：索引机制、索引分类、索引使用与管理综述
19k阅读
 · 
290点赞
MySQL索引和SQL调优
28k阅读
 · 
612点赞