首頁 >資料庫 >mysql教程 >mysql索引是什麼? mysql索引的相關知識介紹

mysql索引是什麼? mysql索引的相關知識介紹

不言
不言轉載
2018-12-29 11:32:444774瀏覽

本篇文章帶給大家的內容是關於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` = &#39;a&#39;;
# 命中部分,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中文網其他相關文章!

陳述:
本文轉載於:segmentfault.com。如有侵權,請聯絡admin@php.cn刪除