什麼是索引：

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優化

什麼是索引：

索引文法

索引規則

索引失效（十條規則）

代碼解析：

SQL優化（所有的部分查詢（子查詢）都為索引查詢）

代碼實作：

索引重點：sql優化

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述