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

落日映苍穹つ 2023-10-03 15:36 59阅读 0赞

## 什么是索引： ##

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

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