本篇文章帶給大家的內容是關於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. 范围索引(>,<,between and)后,无法命中组合索引右边的列
构建索引
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';</p> <p style="white-space: normal;">5. !=, is null, is not null 无法使用索引</p> <p style="white-space: normal;">6. 字符串字段的值不加单引号(数字不报错,英文报错)索引失效</p> <p>构建索引</p> <pre class="brush:php;toolbar:false">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';
以上是mysql索引是什麼? mysql索引的相關知識介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章!