索引是排好序的數(shù)據(jù)結(jié)構(gòu)
1. 索引
索引的作用
提高查詢速度
確保數(shù)據(jù)的唯一性
可以加速表和表之間的連接 , 實(shí)現(xiàn)表與表之間的參照完整性
使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時(shí) , 可以顯著減少分組和排序的時(shí)間
全文檢索字段進(jìn)行搜索優(yōu)化.
分類
主鍵索引 (Primary Key)
唯一索引 (Unique)
常規(guī)索引 (Index)
全文索引 (FullText)
主鍵索引
主鍵 : 某一個(gè)屬性組能唯一標(biāo)識(shí)一條記錄
特點(diǎn) :
最常見(jiàn)的索引類型
確保數(shù)據(jù)記錄的唯一性
確定特定數(shù)據(jù)記錄在數(shù)據(jù)庫(kù)中的位置
--創(chuàng)建表時(shí)創(chuàng)建
create table `test`(
-- 主鍵本身也是一種索引（注:也可以在上面的創(chuàng)建字段時(shí)使該字段主鍵自增）
PRIMARY KEY (id),
)ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '項(xiàng)目附件表';
唯一索引
作用 : 避免同一個(gè)表中某數(shù)據(jù)列中的值重復(fù)
與主鍵索引的區(qū)別
主鍵索引只能有一個(gè)
唯一索引可能有多個(gè)
CREATE TABLE `Grade`(
`GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
`GradeName` VARCHAR(32) NOT NULL UNIQUE
-- 或 UNIQUE INDEX (GradeName),
)
--創(chuàng)建后添加
-- 假設(shè)建表時(shí)fileuploadercode字段沒(méi)創(chuàng)建索引(注:同一個(gè)字段可以創(chuàng)建多個(gè)索引趣斤，但一般情況下意義不大)
-- 給projectfile表中的fileuploadercode創(chuàng)建索引
ALTER TABLE Grade ADD UNIQUE INDEX (GradeName);
常規(guī)索引
作用 : 快速定位特定數(shù)據(jù)
注意 :
index 和 key 關(guān)鍵字都可以設(shè)置常規(guī)索引
應(yīng)加在查詢找條件的字段
不宜添加太多常規(guī)索引,影響數(shù)據(jù)的插入,刪除和修改操作
CREATE TABLE `result`(
-- 省略一些代碼
INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 創(chuàng)建表時(shí)添加耸别，組合索引
)
-- 創(chuàng)建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);
全文索引
百度搜索：全文索引
作用 : 快速定位特定數(shù)據(jù)
注意 :
只能用于MyISAM類型的數(shù)據(jù)表
只能用于CHAR , VARCHAR , TEXT數(shù)據(jù)列類型
適合大型數(shù)據(jù)集
/*
#方法一：創(chuàng)建表時(shí)
CREATE TABLE 表名 (
字段名1 數(shù)據(jù)類型 [完整性約束條件…],
字段名2 數(shù)據(jù)類型 [完整性約束條件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(長(zhǎng)度)] [ASC |DESC])
);
#方法二：CREATE在已存在的表上創(chuàng)建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(長(zhǎng)度)] [ASC |DESC]) ;
?
?
#方法三：ALTER TABLE在已存在的表上創(chuàng)建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(長(zhǎng)度)] [ASC |DESC]) ;
#刪除索引：DROP INDEX 索引名 ON 表名字;
#刪除主鍵索引: ALTER TABLE 表名 DROP PRIMARY KEY;
?
?
#顯示索引信息: SHOW INDEX FROM student;
*/
?
/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);
?
/*EXPLAIN : 分析SQL語(yǔ)句執(zhí)行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';
?
/*使用全文索引*/
-- 全文搜索通過(guò) MATCH() 函數(shù)完成。
-- 搜索字符串作為 against() 的參數(shù)被給定惊豺。搜索以忽略字母大小寫的方式執(zhí)行。對(duì)于表中的每個(gè)記錄行，MATCH() 返回一個(gè)相關(guān)性值。即，在搜索字符串與記錄行在 MATCH() 列表中指定的列的文本之間的相似性尺度堕绩。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');
?
/*
開(kāi)始之前，先說(shuō)一下全文索引的版本邑时、存儲(chǔ)引擎奴紧、數(shù)據(jù)類型的支持情況
?
MySQL 5.6 以前的版本，只有 MyISAM 存儲(chǔ)引擎支持全文索引刁愿；
MySQL 5.6 及以后的版本绰寞，MyISAM 和 InnoDB 存儲(chǔ)引擎均支持全文索引;
只有字段的數(shù)據(jù)類型為 char到逊、varchar铣口、text 及其系列才可以建全文索引滤钱。
測(cè)試或使用全文索引時(shí)，要先看一下自己的 MySQL 版本脑题、存儲(chǔ)引擎和數(shù)據(jù)類型是否支持全文索引件缸。
*/
拓展：測(cè)試索引
建表app_user：
CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用戶昵稱',
`email` varchar(50) NOT NULL COMMENT '用戶郵箱',
`phone` varchar(20) DEFAULT '' COMMENT '手機(jī)號(hào)',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性別（0:男；1：女）',
`password` varchar(100) NOT NULL COMMENT '密碼',
`age` tinyint(4) DEFAULT '0' COMMENT '年齡',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用戶表'
批量插入數(shù)據(jù)：100w
DROP FUNCTION IF EXISTS mock_data;
DELIMITER $
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
VALUES(CONCAT('用戶', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();
索引效率測(cè)試
無(wú)索引
SELECT * FROM app_user WHERE name = '用戶9999'; -- 查看耗時(shí)
SELECT * FROM app_user WHERE name = '用戶9999';
SELECT * FROM app_user WHERE name = '用戶9999';
?
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用戶9999'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 992759 //查詢次數(shù)
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
創(chuàng)建索引
CREATE INDEX idx_app_user_name ON app_user(name);
測(cè)試普通索引
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用戶9999'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ref
possible_keys: idx_app_user_name
key: idx_app_user_name
key_len: 203
ref: const
rows: 1 //查詢次數(shù)
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
?
mysql> SELECT * FROM app_user WHERE name = '用戶9999';
1 row in set (0.00 sec)
?
mysql> SELECT * FROM app_user WHERE name = '用戶9999';
1 row in set (0.00 sec)
?
mysql> SELECT * FROM app_user WHERE name = '用戶9999';
1 row in set (0.00 sec)
索引準(zhǔn)則
索引不是越多越好
不要對(duì)經(jīng)常變動(dòng)的數(shù)據(jù)加索引
小數(shù)據(jù)量的表建議不要加索引
索引一般應(yīng)加在查找條件的字段
索引的數(shù)據(jù)結(jié)構(gòu)
-- 我們可以在創(chuàng)建上述索引的時(shí)候叔遂，為其指定索引類型他炊，分兩類
hash類型的索引：查詢單條快，范圍查詢慢
btree類型的索引：b+樹(shù)已艰，層數(shù)越多痊末，數(shù)據(jù)量指數(shù)級(jí)增長(zhǎng)（我們就用它，因?yàn)閕nnodb默認(rèn)支持它）
?
-- 不同的存儲(chǔ)引擎支持的索引類型也不一樣
InnoDB 支持事務(wù)哩掺，支持行級(jí)別鎖定凿叠，支持 B-tree、Full-text 等索引嚼吞，不支持 Hash 索引盒件；
MyISAM 不支持事務(wù)，支持表級(jí)別鎖定舱禽，支持 B-tree炒刁、Full-text 等索引，不支持 Hash 索引誊稚；
Memory 不支持事務(wù)翔始，支持表級(jí)別鎖定，支持 B-tree里伯、Hash 等索引绽昏，不支持 Full-text 索引；
NDB 支持事務(wù)俏脊，支持行級(jí)別鎖定全谤，支持 Hash 索引，不支持 B-tree爷贫、Full-text 等索引认然；
Archive 不支持事務(wù)，支持表級(jí)別鎖定漫萄，不支持 B-tree卷员、Hash、Full-text 等索引腾务；
2. 為什么MySQl選擇B+樹(shù)構(gòu)建索引
2.1 二叉樹(shù)的弊端
當(dāng)存儲(chǔ)的數(shù)據(jù)一致自增時(shí)毕骡，就會(huì)類似于鏈表的數(shù)據(jù)結(jié)構(gòu)了
2.2 AVL樹(shù)的弊端
每個(gè)子節(jié)點(diǎn)的長(zhǎng)度不超過(guò)其他節(jié)點(diǎn)1個(gè)節(jié)點(diǎn)
缺點(diǎn)：會(huì)經(jīng)常發(fā)生旋轉(zhuǎn)，非常消耗性能
2.3 紅黑樹(shù)的弊端
真對(duì)AVL樹(shù)進(jìn)行修改，取得一個(gè)相對(duì)平衡的情況未巫，最長(zhǎng)的自階段不超過(guò)其他節(jié)點(diǎn)的兩倍窿撬，是的發(fā)生旋轉(zhuǎn)的次數(shù)降低，而且加入了變色的機(jī)制叙凡。
當(dāng)數(shù)據(jù)量足夠大的時(shí)候劈伴，h的深度會(huì)不斷增加，索引向下查找的次數(shù)會(huì)不斷增加握爷。
2.4 B樹(shù)
注意:之前有看到有很多文章把B樹(shù)和B-tree理解成了兩種不同類別的樹(shù)跛璧，其實(shí)這兩個(gè)是同一種樹(shù);
概念
B樹(shù)和平衡二叉樹(shù)稍有不同的是B樹(shù)屬于多叉樹(shù)又名平衡多路查找樹(shù)（查找路徑不只兩個(gè)），數(shù)據(jù)庫(kù)索引技術(shù)里大量使用者B樹(shù)和B+樹(shù)的數(shù)據(jù)結(jié)構(gòu)新啼，讓我們來(lái)看看他有什么特點(diǎn);
規(guī)則
（1）排序方式：所有節(jié)點(diǎn)關(guān)鍵字是按遞增次序排列追城，并遵循左小右大原則； （2）子節(jié)點(diǎn)數(shù)：非葉節(jié)點(diǎn)的子節(jié)點(diǎn)數(shù)>1燥撞，且<=M 漓柑，且M>=2，空樹(shù)除外（注：M階代表一個(gè)樹(shù)節(jié)點(diǎn)最多有多少個(gè)查找路徑叨吮，M=M路,當(dāng)M=2則是2叉樹(shù),M=3則是3叉）辆布； （3）關(guān)鍵字?jǐn)?shù)：枝節(jié)點(diǎn)的關(guān)鍵字?jǐn)?shù)量大于等于ceil(m/2)-1個(gè)且小于等于M-1個(gè)（注：ceil()是個(gè)朝正無(wú)窮方向取整的函數(shù) 如ceil(1.1)結(jié)果為2); （4）所有葉子節(jié)點(diǎn)均在同一層、葉子節(jié)點(diǎn)除了包含了關(guān)鍵字和關(guān)鍵字記錄的指針外也有指向其子節(jié)點(diǎn)的指針只不過(guò)其指針地址都為null對(duì)應(yīng)下圖最后一層節(jié)點(diǎn)的空格子; 最后我們用一個(gè)圖和一個(gè)實(shí)際的例子來(lái)理解B樹(shù)（這里為了理解方便我就直接用實(shí)際字母的大小來(lái)排列C>B>A）
特點(diǎn)：
B樹(shù)相對(duì)于平衡二叉樹(shù)的不同是茶鉴，每個(gè)節(jié)點(diǎn)包含的關(guān)鍵字增多了锋玲，特別是在B樹(shù)應(yīng)用到數(shù)據(jù)庫(kù)中的時(shí)候，數(shù)據(jù)庫(kù)充分利用了磁盤塊的原理（磁盤數(shù)據(jù)存儲(chǔ)是采用塊的形式存儲(chǔ)的涵叮，每個(gè)塊的大小為4K惭蹂，每次IO進(jìn)行數(shù)據(jù)讀取時(shí)，同一個(gè)磁盤塊的數(shù)據(jù)可以一次性讀取出來(lái)）把節(jié)點(diǎn)大小限制和充分使用在磁盤快大小范圍割粮；把樹(shù)的節(jié)點(diǎn)關(guān)鍵字增多后樹(shù)的層級(jí)比原來(lái)的二叉樹(shù)少了盾碗，減少數(shù)據(jù)查找的次數(shù)和復(fù)雜度;
2.4 B+樹(shù)
概念
B+樹(shù)是B樹(shù)的一個(gè)升級(jí)版，相對(duì)于B樹(shù)來(lái)說(shuō)B+樹(shù)更充分的利用了節(jié)點(diǎn)的空間舀瓢，讓查詢速度更加穩(wěn)定廷雅，其速度完全接近于二分法查找。為什么說(shuō)B+樹(shù)查找的效率要比B樹(shù)更高京髓、更穩(wěn)定航缀；我們先看看兩者的區(qū)別
規(guī)則
（1）B+跟B樹(shù)不同B+樹(shù)的非葉子節(jié)點(diǎn)不保存關(guān)鍵字記錄的指針，只進(jìn)行數(shù)據(jù)索引堰怨，這樣使得B+樹(shù)每個(gè)非葉子節(jié)點(diǎn)所能保存的關(guān)鍵字大大增加芥玉； （2）B+樹(shù)葉子節(jié)點(diǎn)保存了父節(jié)點(diǎn)的所有關(guān)鍵字記錄的指針，所有數(shù)據(jù)地址必須要到葉子節(jié)點(diǎn)才能獲取到备图。所以每次數(shù)據(jù)查詢的次數(shù)都一樣灿巧； （3）B+樹(shù)葉子節(jié)點(diǎn)的關(guān)鍵字從小到大有序排列赶袄，有利于范圍查找(在所有的樹(shù)葉節(jié)點(diǎn)的前后維護(hù)前后樹(shù)葉節(jié)點(diǎn)的位置，成為一個(gè)雙向鏈表抠藕，當(dāng)查找到葉子節(jié)點(diǎn)后饿肺，大于或小于可以直接獲取前后的數(shù)據(jù))。
特點(diǎn)
在B+樹(shù)的基礎(chǔ)上因其初始化的容量變大幢痘，使得節(jié)點(diǎn)空間使用率更高唬格，而又存有兄弟節(jié)點(diǎn)的指針家破，可以向兄弟節(jié)點(diǎn)轉(zhuǎn)移關(guān)鍵字的特性使得B*樹(shù)額分解次數(shù)變得更少颜说；
3.MyISAM和InnoDB
3.1 MyISAM和InnoDB存儲(chǔ)的結(jié)構(gòu)信息
3.2 MyISAM索引實(shí)現(xiàn)(非聚集)：
索引文件和數(shù)據(jù)文件是分離的
大致的實(shí)現(xiàn)過(guò)程：
通過(guò)在
.MYI文件，B+Tree的索引方式查詢到對(duì)應(yīng)索引所在的地址根據(jù)查詢到的地址在
.MYD查詢到相對(duì)應(yīng)的地址汰聋，再返回?cái)?shù)據(jù)
3.3 InnoDB索引實(shí)現(xiàn)(聚集)：
索引文件和數(shù)據(jù)文件是聚集的
表數(shù)據(jù)文件本身就是B+Tree組織的一個(gè)索引結(jié)構(gòu)文件
聚集(聚簇)索引-葉節(jié)點(diǎn)包含了完整的數(shù)據(jù)記錄
3.3.1 在InnoDB下的主鍵索引
大致的實(shí)現(xiàn)過(guò)程：
通過(guò).ibd文件门粪，使用B+Tree的索引方式查詢到相應(yīng)的索引數(shù)據(jù)，在索引數(shù)據(jù)下烹困，直接存儲(chǔ)了value的信息玄妈，直接返回。
3.3.2 在InnoDB下的非主鍵索引(二級(jí)索引髓梅、普通索引)
在非主鍵索引中拟蜻，所有葉子節(jié)點(diǎn)存儲(chǔ)的都是主鍵的值
3.4 為什么InnoDB表必須有主鍵，并且推薦使用整型的自增主鍵
必須有主鍵
每個(gè)InnoDB表中枯饿，mysql會(huì)選擇一列不重復(fù)列作為B+樹(shù)索引的構(gòu)建酝锅，如果用戶創(chuàng)建主鍵的話，會(huì)自動(dòng)根據(jù)主鍵創(chuàng)建主鍵索引奢方，如果沒(méi)有主鍵搔扁，mysql會(huì)一列一列往下查找，知道找到有一列的內(nèi)容時(shí)不重復(fù)的蟋字，如果沒(méi)有查找到稿蹲，mysql后端會(huì)自動(dòng)創(chuàng)建一個(gè)隱藏的rowId去構(gòu)建索引，在整個(gè)過(guò)程中每次往下一列一列查找會(huì)銷毀多余的性能鹊奖，因此InnoDB表下苛聘，推薦要設(shè)置主鍵。
整型的自增主鍵
在索引構(gòu)建后忠聚，查找數(shù)據(jù)的時(shí)候會(huì)比較大小焰盗，對(duì)于各種數(shù)據(jù)類型來(lái)說(shuō)，整型式比較適合比大小的咒林，而且存儲(chǔ)的內(nèi)容撐死就8個(gè)字節(jié)熬拒，因此主鍵選擇整型最為合適.
當(dāng)每次插入一個(gè)節(jié)點(diǎn)的時(shí)候，對(duì)于自增來(lái)說(shuō)垫竞，他都會(huì)往后移澎粟，對(duì)于B+樹(shù)的影響最小蛀序，而非自增來(lái)說(shuō)，他每次插入一個(gè)節(jié)點(diǎn)活烙，都有可能導(dǎo)致葉子節(jié)點(diǎn)的分裂徐裸，葉子節(jié)點(diǎn)分裂后需要重新平衡，這對(duì)與mysql來(lái)說(shuō)會(huì)影響性能啸盏，所以整型的自增主鍵最為合適重贺。
4. 索引名詞
4.1 什么是回表查詢
通過(guò)第二索引(普通索引)查找到主鍵對(duì)應(yīng)的值，通過(guò)查詢到的值回到主鍵索引中再次查找回懦，稱之為回表查詢
4.2 索引覆蓋
select * from table where name = ? --需要回表查詢?nèi)孔侄?select id from table where name = ? --直接通過(guò)普通索引查詢到id值气笙，直接返回 索引覆蓋
4.3 索引下推
索引下推的唯一缺點(diǎn)是需要再磁盤上多做數(shù)據(jù)賽選，原來(lái)放在內(nèi)存中的怯晕，現(xiàn)在放到磁盤上進(jìn)行篩選潜圃，在磁盤篩選過(guò)程中可能成本相對(duì)來(lái)說(shuō)較高，但是索引下推創(chuàng)建之后是排序的舟茶，所有數(shù)據(jù)聚集存放谭期，而且io量會(huì)大大減少，查找時(shí)性能大大提高吧凉。
4.4 謂詞下推
select t1.name t2.age from t1 join t2 t1.id = t2.id
--先做表連接隧出，然后查詢需要的字段
--先把需要的字段都拿出來(lái)，然后再做關(guān)聯(lián) 效率要高很多 謂詞下推
4.5 MRR(mult_range read)
通過(guò)B+樹(shù)阀捅，查詢name時(shí)胀瞪，發(fā)現(xiàn)多個(gè)相同的name,這是，會(huì)把name下對(duì)于的id在內(nèi)存中進(jìn)行排序也搓，方便查找
4.6 FIC(fast index create)
插入和刪除數(shù)據(jù)(原來(lái)情況)
先創(chuàng)建臨時(shí)表赏廓，將數(shù)據(jù)導(dǎo)入到臨時(shí)表
刪除原始表
修改臨時(shí)表的名字
FIC:
給當(dāng)前表添加一個(gè)Share鎖，不會(huì)有創(chuàng)建臨時(shí)文件的資源消耗傍妒，還是在源文件中幔摸，但是此時(shí)如果有人發(fā)起DML操作，會(huì)出現(xiàn)數(shù)據(jù)不一致的問(wèn)題颤练，所以添加share鎖既忆，讀取時(shí)不會(huì)有問(wèn)題，但是DML會(huì)有問(wèn)題
5. 索引匹配方式
5.1 最左優(yōu)化匹配原則
···
KEY 'idx_name_age_position' ('name','age','position') USING BTREE
根據(jù)上面創(chuàng)建的聯(lián)合索引嗦玖，判斷以下哪個(gè)用可以用到索引
EXPLAIN SELECT * FROM exployees WHERE name = 'LiLei' AND age = 18; yes
EXPLAIN SELECT * FROM exployees WHERE position = 'manager'; no
EXPLAIN SELECT * FROM exployees WHERE name = 'LiLei'; no</pre>
···
聯(lián)合索引如上圖患雇，他是根據(jù)創(chuàng)建的聯(lián)合索引的的每個(gè)字段進(jìn)行排序的，從第一個(gè)字段開(kāi)始宇挫，第一個(gè)字段相同的情況下苛吱，在第二層使用第二個(gè)字段進(jìn)行排序，所以器瘪，如果是想使用到聯(lián)合索引翠储，首先要知道第一個(gè)字段排序如上述示例就是name绘雁，如果聯(lián)合索引中間斷了一個(gè)，則會(huì)不進(jìn)行聯(lián)合索引查找援所，如name = 'LiLei' AND position = 'manager'則不會(huì)查找到聯(lián)合索引庐舟，mysql內(nèi)部進(jìn)行了優(yōu)化，字段的順序可以不跟索引創(chuàng)建的一致住拭，比如age = 18 AND posiition = 'manager' AND name'Lilei'也可以挪略，mysql會(huì)自己進(jìn)行排序，然后根據(jù)索引查找.
5.2 全值匹配
和所有索引中的列進(jìn)行匹配
--索引中有3個(gè)字段name滔岳、age杠娱、pos
explain select * from staffs where name = 'july' and age = '24' and pos = 'dev'
5.3 匹配列前綴
可以匹配某一列的值的開(kāi)通部分
explain select * from staffs where name like 'j%'--可以匹配
explain select * from staffs where name like '%j%'--不可以匹配
5.4 匹配范圍值
匹配范圍值
explain select * from staff where name > 'july';
------------------------
explain select * from staff where name = 'july' and score > 60 and sex = '女';--在這條語(yǔ)句中只有name和score用到了索引，在范圍查找之后澈蟆，索引失效
5.5 精確匹配到某一列范圍匹配另外一列
explain select * from staff where name = 'july' and score > 60;
5.6 只訪問(wèn)索引的查詢
查詢的時(shí)候只需訪問(wèn)索引墨辛，不需要訪問(wèn)數(shù)據(jù)行卓研，本質(zhì)上就是索引覆蓋
explain select name,age,pos from staffs where name = 'july' and age = 22 and pos = 'dev';
5.7.哈希索引
概述
基于哈希表實(shí)現(xiàn)趴俘，只能緊缺匹配索引索引列的查詢才有效
在mysql中，只有memory的存儲(chǔ)應(yīng)該去顯示支持哈希索引
哈希索引自身秩序存儲(chǔ)對(duì)于的hash值奏赘，所以索引的結(jié)構(gòu)十分緊湊寥闪，這讓哈希索引查找的速度非常快
限制
哈希索引值包括哈希值的行指針磨淌，而不存儲(chǔ)字段值疲憋，索引不能使用索引中的值來(lái)避免讀取行
哈希索引數(shù)據(jù)并不是按照索引值順序存儲(chǔ)的，所以無(wú)法進(jìn)行排序
哈希索引不支持部分匹配查找梁只，哈希索引是使用索引的全部?jī)?nèi)容來(lái)計(jì)算哈希值
哈需索引支持等值比較查詢缚柳，不支持任何范圍查詢
訪問(wèn)哈希索引的數(shù)據(jù)非常快搪锣，除非有很多哈希沖突秋忙，當(dāng)出現(xiàn)哈希沖突的時(shí)候，存儲(chǔ)引擎必須遍歷鏈表中所有的行指針构舟，逐行進(jìn)行比較灰追，知道找到所有符合條件的行
哈希沖突比較多的話，維護(hù)代價(jià)也很高
案例
當(dāng)存儲(chǔ)大量的url并根據(jù)url進(jìn)行搜索行查找狗超，如果使用B+樹(shù)弹澎，存儲(chǔ)的內(nèi)容就會(huì)很大，可以利用CRC32做哈希努咐，select id from url where url = "" and url_crc = crc32("")此查詢吸能較高的原因是使用體積很小的所有完成查找
6. 覆蓋索引
6.1 基本介紹：
如果一個(gè)索引包括所有需要查詢的字段的值苦蒿，我們稱值為覆蓋索引
不是所有類型的所有都可以稱之為覆蓋所有，覆蓋索引必須要存儲(chǔ)所有列的值
不同的存儲(chǔ)實(shí)現(xiàn)覆蓋索引的方式不同渗稍，不是所有的引擎都支持覆蓋所有佩迟，memory不支持覆蓋索引
6.2 優(yōu)勢(shì)：
索引條目通常遠(yuǎn)小于數(shù)據(jù)行大小溃肪，如果只需讀取索引，那么mysql就會(huì)極大的減少數(shù)據(jù)訪問(wèn)量
因?yàn)樗惺前惭b列值順序存儲(chǔ)的音五，所有對(duì)于io密集型的范圍查詢會(huì)比隨機(jī)從磁盤讀取每一行數(shù)據(jù)的io要少得多
一些引擎所有如MYISAM在內(nèi)存中值存儲(chǔ)索引惫撰，數(shù)據(jù)則依賴與操作系統(tǒng)來(lái)春初，因此要訪問(wèn)數(shù)據(jù)需要一次系統(tǒng)調(diào)用躺涝，這可能導(dǎo)致嚴(yán)重的性能問(wèn)題
由于innodb的聚簇索引厨钻，覆蓋所有對(duì)innodb表特別有用
6.3 特殊情況
--表abc有字段a,b,c,創(chuàng)建組合索引a,b,c
explain select * from abc where a = 1 or b = 2;---會(huì)走索引
?
--表abc有字段a,b,c,d,創(chuàng)建組合索引a,b,c
explain select * from abc where a = 1 or b = 2;---不會(huì)走索引
explain select a,b,c from abc where a = 1 or b = 2;---會(huì)走索引</pre>
結(jié)論：
在使用or查詢的使用，如果查詢的字段在索引內(nèi)全部包含坚嗜，是能走索引的夯膀，如果有一個(gè)不包含，是不走索引的苍蔬。
7. 優(yōu)化細(xì)節(jié)
當(dāng)使用索引列進(jìn)行查詢的時(shí)候盡量不要使用表達(dá)式诱建，把計(jì)算放到也無(wú)從而不是數(shù)據(jù)庫(kù)層
盡量使用主鍵查詢，使用其他索引查詢碟绑，可能會(huì)觸發(fā)回表操作
-
使用前綴索引
Snipaste_2020-08-20_14-48-28.png通過(guò)查詢city的前7個(gè)符合進(jìn)行排序俺猿，降低了排序的成本
使用索引掃描來(lái)排序
union all,in,or都能使用索引，但是推薦使用in
-
范圍列可以用到索引
范圍條件是：<,<=,>,>=,between
范圍列可以用到索引格仲，但是范圍列后面的無(wú)法用到索引押袍，索引最多的用于一個(gè)范圍列
不要在索引列上做任何操作，比如計(jì)算凯肋、使用函數(shù)谊惭、自動(dòng)或手動(dòng)進(jìn)行類型轉(zhuǎn)換，會(huì)導(dǎo)致索引失效侮东，從而使查詢轉(zhuǎn)向全表掃描圈盔。
存儲(chǔ)引擎不能使用范圍條件右邊的索引列。
-
盡量使用覆蓋索引（只訪問(wèn)索引的查詢（索引列包含查詢列））悄雅，減少select *語(yǔ)句驱敲。
- 將換成索引列，查詢時(shí)使用了索引煤伟，用索引列覆蓋查詢的癌佩， 叫做覆蓋索引。
mysql在使用不等于（!=或者<>）的時(shí)候無(wú)法使用索引會(huì)導(dǎo)致全表掃描便锨。
is null围辙，is not null 也無(wú)法使用索引。
like以通配符開(kāi)頭（like '%aaa'）mysql索引失效會(huì)變成全表掃描操作放案。
