本篇文章給大家帶來的內容是關于mysql索引是什么？mysql索引的相關知識介紹，有一定的參考價值，有需要的朋友可以參考一下，希望對你有所幫助。
索引是什么
索引就像是一本書的目錄
索引用于快速找出在某個列中有一特定值的行，不使用索引，mysql必須從第一條記錄開始讀完整個表，直到找出相關的行，表越大，查詢數據所花費的時間就越多，如果表中查詢的列有一個索引，MySQL能夠快速到達一個位置去搜索數據文件，而不必查看所有數據，那么將會節省很大一部分時間。
優點與缺點
優點
1、大大加快查詢速度
2、所有字段類型均可以設置索引
缺點
1、創建和維護索引需要時間，數據量越多，耗時越多
2、索引占用存儲空間，數據表中的數據也會有最大上線設置的，如果我們有大量的索引，索引文件可能會比數據文件更快達到上線值
3、當對表中的數據進行增加、刪除、修改時，索引也需要動態的維護，降低了數據的維護速度
使用原則和場景
1、索引不是越多越好，需要視情況而定
2、頻繁更新的表應盡量少的索引
3、頻繁用于查詢的字段進行構建索引
4、數據量小的字段盡量不要使用索引，查詢所有數據花費的時間比遍歷索引的數據要短，索引將沒有優化效果
5、字段不同值少的字段盡量不要使用索引，如性別字段僅有男女兩個不同值。
索引分類
注意：索引是在存儲引擎中實現的，也就是說不同的存儲引擎，會使用不同的索引
MyISAM和InnoDB存儲引擎：只支持BTREE索引， 也就是說默認使用BTREE，不能夠更換
MEMORY/HEAP存儲引擎：支持HASH和BTREE索引
1. 單列索引
一個索引只包含單個列，但一個表中可以有多個單列索引
1.1. 普通索引
MySQL中基本索引類型，沒有什么限制，允許在定義索引的列中插入重復值和空值，純粹為了查詢數據更快一點。
1.2. 唯一索引
索引列中的值必須是唯一的，但是允許為空值
1.3. 主鍵索引
是一種特殊的唯一索引，不允許有空值
2. 組合索引
在表中的多個字段組合上創建的索引，只有在查詢條件中使用了這些字段的左邊字段時，索引才會被使用，使用組合索引時遵循最佳左前綴法則
3. 全文索引
全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT類型字段上使用全文索引。全文索引，就是在一堆文字中，通過其中的某個關鍵字等，就能找到該字段所屬的記錄行，比如有”你是個大煞筆，二貨 …” 通過大煞筆，可能就可以找到該條記錄
4. 空間索引
空間索引是對空間數據類型的字段建立的索引，MySQL中的空間數據類型有四種，GEOMETRY、POINT、LINESTRING、POLYGON。在創建空間索引時，使用SPATIAL關鍵字。要求，引擎為MyISAM，創建空間索引的列，必須將其聲明為NOT NULL
索引方式
使用原則：如果值的差異性大，并且以等值查找（=、 、in）為主，Hash索引是更高效的選擇，它有O(1)的查找復雜度；如果值的差異性相對較差，并且以范圍查找為主，B樹是更好的選擇，它支持范圍查找。
B-Tree索引
B樹索引具有范圍查找和前綴查找的能力，對于有N節點的B樹，檢索一條記錄的復雜度為O(LogN)。相當于二分查找。
Hash索引
哈希索引只能做等于查找，但是無論多大的Hash表，查找復雜度都是O(1)。
索引創建和刪除
創建
建表時創建
CREATE TABLE 表名[字段名 數據類型] [UNIQUE|FULLTEXT|SPATIAL|...] [INDEX|KEY] [索引名字] (字段名[length]) [ASC|DESC]
示例：
CREATE TABLE `NewTable` ( `id` INT NOT NULL AUTO_INCREMENT, `username` VARCHAR (255) NOT NULL, `name` VARCHAR (255) NOT NULL, `sex` TINYINT NOT NULL DEFAULT 0, `address` VARCHAR (255) NULL, PRIMARY KEY (`id`), # 主鍵索引 INDEX `name` (`name`) USING BTREE, # 普通索引 UNIQUE INDEX `username` (`username`) USING BTREE # 唯一索引 INDEX `u_n_a` (`username`, `name`,`address`) USING BTREE # 組合索引 );
已存在表創建
ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名)[ASC|DESC]
示例：
ALTER TABLE `test` ADD PRIMARY KEY (`id`), # 主鍵索引 ADD INDEX `name` (`name`) USING BTREE , # 普通索引 ADD UNIQUE INDEX `username` (`username`) USING BTREE , # 唯一索引 ADD INDEX `u_n_a` (`username`, `name`, `address`) USING BTREE ; # 組合索引
刪除索引
ALTER TABLE 表名 DROP INDEX 索引名。
示例：
ALTER TABLE `test` DROP PRIMARY KEY, DROP INDEX `username`, DROP INDEX `name`, DROP INDEX `u_n_a`;
更新索引
先刪后建
ALTER TABLE `test` DROP INDEX `username` , ADD UNIQUE INDEX `username1` (`username`) USING BTREE , DROP INDEX `name` , ADD INDEX `name2` (`name`) USING BTREE , DROP INDEX `u_n_a` , ADD INDEX `u_a_n` (`username`, `address`, `name`) USING BTREE ;
索引失效情況
1. 組合字段不遵循最佳左前綴法則
2. 模糊查詢，如like ‘%test
# 索引生效 select * from `test` where `name` like "123"; # 索引生效 select * from `test` where `name` like "123%"; # 索引失效 select * from `test` where `name` like "%123"; # 索引失效 select * from `test` where `name` like "%123%";
3. 在索引列上做如下任何操作（計算，函數，（自動或者手動）類型裝換），會導致索引失效而導致全表掃描
如?sex?字段上添加索引
# 索引失效 select * from `test` where `sex`*0.5 = 1
4. 范圍索引(>,
構建索引
ALTER TABLE `test` ADD INDEX `s_n` (`sex`, `name`) USING BTREE ;
示例：
# 命中全部 select * from `test` where `sex` = 1 and `name` = 'a'; # 命中部分，sex命中，name失效 select * from `test` where `sex` > 1 and `name` = 'a';
5.?!=,?is null,?is not null?無法使用索引
6. 字符串字段的值不加單引號（數字不報錯，英文報錯）索引失效
構建索引
ALTER TABLE `test` ADD INDEX `name` (`name`) USING BTREE ;
示例
# 索引失效 select * from `test` where `name` = 123; # 索引生效 select * from `test` where `name` = '123';
7.?or?條件導致索引失效
構建索引
ALTER TABLE `test` ADD INDEX `sex` (`sex`) USING BTREE ; ADD INDEX `n_u` (`name`, `username`) USING BTREE ;
示例：
# 索引不生效 select * from `test` where (`name` = 'aa' and `username` = 'aa') or `sex` > 1 # 索引sex生效 select * from `test` where `sex` = 1 and (`id` = 2 or `name` = 'aa' )
附錄
最佳左前綴法則
如果索引了多列，要遵守最左前綴法則。指的是查詢要從索引的最左前列開始并且不跳過索引中的列
如下構建索引
ALTER TABLE `test` ADD INDEX `u_a_n` (`username`, `address`, `name`) USING BTREE ;
如下查詢情況
# 命中部分 select * from `test` where `username` = 'aaa'; # 命中部分 select * from `test` where `username` = 'aa' and `address` = 'aaa'; # 全命中 select * from `test` where `username` = 'aa' and `address` = 'aaa' and `name` = 'a'; # 不命中，第一條件字段不是username select * from `test` where `address` = 'aaa';
