本文主要給大家簡(jiǎn)單講講MySQL索引的一些簡(jiǎn)單知識(shí)，相關(guān)專業(yè)術(shù)語大家可以上網(wǎng)查查或者找一些相關(guān)書籍補(bǔ)充一下，這里就不涉獵了，我們就直奔主題吧，希望mysql索引的一些簡(jiǎn)單知識(shí)這篇文章可以給大家?guī)硪恍?shí)際幫助。

藤縣網(wǎng)站制作公司哪家好，找成都創(chuàng)新互聯(lián)公司！從網(wǎng)頁設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、APP開發(fā)、成都響應(yīng)式網(wǎng)站建設(shè)公司等網(wǎng)站項(xiàng)目制作，到程序開發(fā)，運(yùn)營維護(hù)。成都創(chuàng)新互聯(lián)公司2013年開創(chuàng)至今到現(xiàn)在10年的時(shí)間，我們擁有了豐富的建站經(jīng)驗(yàn)和運(yùn)維經(jīng)驗(yàn)，來保證我們的工作的順利進(jìn)行。專注于網(wǎng)站建設(shè)就選成都創(chuàng)新互聯(lián)公司。

索引是什么

索引就像是一本書的目錄

索引用于快速找出在某個(gè)列中有一特定值的行，不使用索引，MySQL必須從第一條記錄開始讀完整個(gè)表，直到找出相關(guān)的行，表越大，查詢數(shù)據(jù)所花費(fèi)的時(shí)間就越多，如果表中查詢的列有一個(gè)索引，MySQL能夠快速到達(dá)一個(gè)位置去搜索數(shù)據(jù)文件，而不必查看所有數(shù)據(jù)，那么將會(huì)節(jié)省很大一部分時(shí)間。

優(yōu)點(diǎn)與缺點(diǎn)

優(yōu)點(diǎn)

1、大大加快查詢速度

2、所有字段類型均可以設(shè)置索引

缺點(diǎn)

1、創(chuàng)建和維護(hù)索引需要時(shí)間，數(shù)據(jù)量越多，耗時(shí)越多

2、索引占用存儲(chǔ)空間，數(shù)據(jù)表中的數(shù)據(jù)也會(huì)有最大上線設(shè)置的，如果我們有大量的索引，索引文件可能會(huì)比數(shù)據(jù)文件更快達(dá)到上線值

3、當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除、修改時(shí)，索引也需要?jiǎng)討B(tài)的維護(hù)，降低了數(shù)據(jù)的維護(hù)速度

使用原則和場(chǎng)景

1、索引不是越多越好，需要視情況而定

2、頻繁更新的表應(yīng)盡量少的索引

3、頻繁用于查詢的字段進(jìn)行構(gòu)建索引

4、數(shù)據(jù)量小的字段盡量不要使用索引，查詢所有數(shù)據(jù)花費(fèi)的時(shí)間比遍歷索引的數(shù)據(jù)要短，索引將沒有優(yōu)化效果

5、字段不同值少的字段盡量不要使用索引，如性別字段僅有男女兩個(gè)不同值。

索引分類

注意：索引是在存儲(chǔ)引擎中實(shí)現(xiàn)的，也就是說不同的存儲(chǔ)引擎，會(huì)使用不同的索引

MyISAM和InnoDB存儲(chǔ)引擎：只支持BTREE索引， 也就是說默認(rèn)使用BTREE，不能夠更換

MEMORY/HEAP存儲(chǔ)引擎：支持HASH和BTREE索引

1. 單列索引

一個(gè)索引只包含單個(gè)列，但一個(gè)表中可以有多個(gè)單列索引

1.1. 普通索引

MySQL中基本索引類型，沒有什么限制，允許在定義索引的列中插入重復(fù)值和空值，純粹為了查詢數(shù)據(jù)更快一點(diǎn)。

1.2. 唯一索引

索引列中的值必須是唯一的，但是允許為空值

1.3. 主鍵索引

是一種特殊的唯一索引，不允許有空值

2. 組合索引

在表中的多個(gè)字段組合上創(chuàng)建的索引，只有在查詢條件中使用了這些字段的左邊字段時(shí)，索引才會(huì)被使用，使用組合索引時(shí)遵循最佳左前綴法則

3. 全文索引

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT類型字段上使用全文索引。全文索引，就是在一堆文字中，通過其中的某個(gè)關(guān)鍵字等，就能找到該字段所屬的記錄行，比如有"你是個(gè)大煞筆，二貨 ..." 通過大煞筆，可能就可以找到該條記錄

4. 空間索引

空間索引是對(duì)空間數(shù)據(jù)類型的字段建立的索引，MySQL中的空間數(shù)據(jù)類型有四種，GEOMETRY、POINT、LINESTRING、POLYGON。在創(chuàng)建空間索引時(shí)，使用SPATIAL關(guān)鍵字。要求，引擎為MyISAM，創(chuàng)建空間索引的列，必須將其聲明為NOT NULL

索引方式

使用原則：如果值的差異性大，并且以等值查找（=、 <=>、in）為主，Hash索引是更高效的選擇，它有O(1)的查找復(fù)雜度；如果值的差異性相對(duì)較差，并且以范圍查找為主，B樹是更好的選擇，它支持范圍查找。

B-Tree索引

B樹索引具有范圍查找和前綴查找的能力，對(duì)于有N節(jié)點(diǎn)的B樹，檢索一條記錄的復(fù)雜度為O(LogN)。相當(dāng)于二分查找。

Hash索引

哈希索引只能做等于查找，但是無論多大的Hash表，查找復(fù)雜度都是O(1)。

索引創(chuàng)建和刪除

創(chuàng)建

建表時(shí)創(chuàng)建

CREATE TABLE 表名[字段名 數(shù)據(jù)類型]  [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 # 組合索引
);

已存在表創(chuàng)建

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. 在索引列上做如下任何操作（計(jì)算，函數(shù)，（自動(dòng)或者手動(dòng)）類型裝換），會(huì)導(dǎo)致索引失效而導(dǎo)致全表掃描

如 sex 字段上添加索引

# 索引失效
select * from `test` where `sex`*0.5  = 1

4. 范圍索引(>,<,between and)后，無法命中組合索引右邊的列

構(gòu)建索引

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. 字符串字段的值不加單引號(hào)（數(shù)字不報(bào)錯(cuò)，英文報(bào)錯(cuò)）索引失效

構(gòu)建索引

ALTER TABLE `test`
ADD INDEX `name` (`name`) USING BTREE ;

示例

# 索引失效
select * from `test` where `name`  =  123;
# 索引生效
select * from `test` where `name`  =  '123';

7. or 條件導(dǎo)致索引失效

構(gòu)建索引

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' )

附錄

最佳左前綴法則

如果索引了多列，要遵守最左前綴法則。指的是查詢要從索引的最左前列開始并且不跳過索引中的列

如下構(gòu)建索引

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索引的一些簡(jiǎn)單知識(shí)就先給大家講到這里，對(duì)于其它相關(guān)問題大家想要了解的可以持續(xù)關(guān)注我們的行業(yè)資訊。我們的板塊內(nèi)容每天都會(huì)捕捉一些行業(yè)新聞及專業(yè)知識(shí)分享給大家的。