全文大約【1888】字，不說(shuō)廢話，只講可以讓你學(xué)到技術(shù)、明白原理的純干貨!本文帶有豐富案例及配圖視頻，讓你更好的理解和運(yùn)用文中的技術(shù)概念，并可以給你帶來(lái)具有足夠啟迪的思考......

　　一. 索引

　　1.什么是索引

　　小編先來(lái)帶各位小伙伴了解下索引的好處是什么呢?那肯定是查詢數(shù)據(jù)塊!通過(guò)索引可以快速的查詢到想要的數(shù)據(jù)。MySQL數(shù)據(jù)庫(kù)中的索引其實(shí)就是一種可以快速獲取數(shù)據(jù)的一種數(shù)據(jù)結(jié)構(gòu)。在表中除了表中的數(shù)據(jù)外，數(shù)據(jù)庫(kù)系統(tǒng)還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這種數(shù)據(jù)結(jié)構(gòu)以某種特定的方式指向數(shù)據(jù)，這種數(shù)據(jù)結(jié)構(gòu)就是索引啦。

　　2.索引的分類

　　按照功能分類：

　　● 普通索引： 最基本的索引，它沒(méi)有任何限制。

　　● 唯一索引：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值組合必須唯一。

　　● 主鍵索引：一種特殊的唯一索引，不允許有空值。一般在建表時(shí)同時(shí)創(chuàng)建主鍵索引。

　　● 組合索引：顧名思義，就是將單列索引進(jìn)行組合。

　　● 外鍵索引：只有InnoDB引擎支持外鍵索引，用來(lái)保證數(shù)據(jù)的一致性、完整性和實(shí)現(xiàn)級(jí)聯(lián)操作。

　　● 全文索引：快速匹配全部文檔的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。

　　3.按數(shù)據(jù)結(jié)構(gòu)分類：

　　● B+Tree索引 ：MySQL使用最頻繁的一個(gè)索引數(shù)據(jù)結(jié)構(gòu)，是InnoDB和MyISAM存儲(chǔ)引擎默認(rèn)的索引類型。

　　● Hash索引 : MySQL中Memory存儲(chǔ)引擎默認(rèn)支持的索引類型。

　　索引的操作

　　3.1數(shù)據(jù)準(zhǔn)備

　　下面各位小伙伴和小編一起來(lái)為演示索引準(zhǔn)備一下表結(jié)構(gòu)和數(shù)據(jù)：　　

-- 創(chuàng)建student表
CREATE TABLE `student` (
  `SId` int(10) NOT NULL AUTO_INCREMENT,
  `Sname` varchar(10) DEFAULT NULL,
  `Sage` int(255) DEFAULT NULL,
  `Ssex` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`SId`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

-- 添加數(shù)據(jù)
INSERT INTO `student` VALUES ('1', '趙雷', '23', '男');
INSERT INTO `student` VALUES ('2', '錢(qián)電', '22', '男');
INSERT INTO `student` VALUES ('3', '窮奇', '12', '男');
INSERT INTO `student` VALUES ('4', '饕鬄', '23', '男');
INSERT INTO `student` VALUES ('5', '周梅', '45', '女');
INSERT INTO `student` VALUES ('6', '吳蘭', '12', '女');
INSERT INTO `student` VALUES ('7', '鄭竹', '43', '女');
INSERT INTO `student` VALUES ('9', '張三', '33', '女');
INSERT INTO `student` VALUES ('10', '李四', '44', '女');
INSERT INTO `student` VALUES ('11', '李四', '24', '女');
INSERT INTO `student` VALUES ('12', '趙六', '54', '女');
INSERT INTO `student` VALUES ('13', '孫七', '23', '女');
INSERT INTO `student` VALUES ('14', '青龍', '12', '男');
INSERT INTO `student` VALUES ('15', '白虎', '34', '女');
INSERT INTO `student` VALUES ('16', '梼杌', '54', '男');

　　3.2創(chuàng)建索引：

　　下面是創(chuàng)建索引的語(yǔ)法，各位小伙伴可以和小編一起來(lái)試一試。

　　語(yǔ)法：

　　CREATE [UNIQUE|FULLTEXT] INDEX 索引名稱

　　[USING 索引類型] -- 默認(rèn)是B+TREE

　　ON 表名(列名...);

　　注意：如果一個(gè)表中有一列是主鍵，那么就會(huì)默認(rèn)為其創(chuàng)建主鍵索引!(主鍵列不需要單獨(dú)創(chuàng)建索引)。

　　舉個(gè)栗子：　

-- 為student表中姓名列創(chuàng)建一個(gè)普通索引
CREATE INDEX idx_name ON student(Sname);

-- 為student表中年齡列創(chuàng)建一個(gè)唯一索引
CREATE UNIQUE INDEX idx_age ON student(Sage);

　　3.3查看索引

　　創(chuàng)建完索引后可以進(jìn)行查看索引，如下：

　　語(yǔ)法：SHOW INDEX FROM 表名;　　

-- 查看student表中的索引
SHOW INDEX FROM student;

　　3.4刪除索引

　　最后是刪除索引語(yǔ)法和案例：

　　語(yǔ)法：DROP INDEX 索引名稱 ON 表名;　　

-- 刪除student表中的idx_score索引
DROP INDEX idx_score ON student;

-- 查看student表中的索引
SHOW INDEX FROM student;

　　4.索引效率的測(cè)試

　　下面是創(chuàng)建product商品表，并添加100萬(wàn)條數(shù)據(jù)做測(cè)試。　

-- 創(chuàng)建product商品表
CREATE TABLE product(
  id INT PRIMARY KEY AUTO_INCREMENT,  -- 商品id
  NAME VARCHAR(10),                   -- 商品名稱
  price INT                           -- 商品價(jià)格
);

-- 定義存儲(chǔ)函數(shù)，生成長(zhǎng)度為10的隨機(jī)字符串并返回
DELIMITER $

CREATE FUNCTION rand_string() 
RETURNS VARCHAR(255)
BEGIN
DECLARE big_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ';
DECLARE small_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 1;

WHILE i <= 10 DO
SET small_str =CONCAT(small_str,SUBSTRING(big_str,FLOOR(1+RAND()*52),1));
SET i=i+1;
END WHILE;

RETURN small_str;
END$

DELIMITER ;



-- 定義存儲(chǔ)過(guò)程，添加100萬(wàn)條數(shù)據(jù)到product表中
DELIMITER $

CREATE PROCEDURE pro_test()
BEGIN
DECLARE num INT DEFAULT 1;

WHILE num <= 1000000 DO
INSERT INTO product VALUES (NULL,rand_string(),num);
SET num = num + 1;
END WHILE;
END$

DELIMITER ;

-- 調(diào)用存儲(chǔ)過(guò)程
CALL pro_test();


-- 查詢總記錄條數(shù)
SELECT COUNT(*) FROM product;



-- 查詢product表的索引
SHOW INDEX FROM product;

-- 查詢name為OkIKDLVwtG的數(shù)據(jù)   (0.049)
SELECT * FROM product WHERE NAME='OkIKDLVwtG';

-- 通過(guò)id列查詢OkIKDLVwtG的數(shù)據(jù)  (1毫秒)
SELECT * FROM product WHERE id=999998;

-- 為name列添加索引
ALTER TABLE product ADD INDEX idx_name(NAME);

-- 查詢name為OkIKDLVwtG的數(shù)據(jù)   (0.001)
SELECT * FROM product WHERE NAME='OkIKDLVwtG';


/*
范圍查詢
*/
-- 查詢價(jià)格為800~1000之間的所有數(shù)據(jù) (0.052)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000;

/*
排序查詢
*/
-- 查詢價(jià)格為800~1000之間的所有數(shù)據(jù),降序排列  (0.083)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000 ORDER BY price DESC;

-- 為price列添加索引
ALTER TABLE product ADD INDEX idx_price(price);

-- 查詢價(jià)格為800~1000之間的所有數(shù)據(jù) (0.011)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000;

-- 查詢價(jià)格為800~1000之間的所有數(shù)據(jù),降序排列  (0.001)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000 ORDER BY price DESC;

　　二. 結(jié)語(yǔ)

　　小編在這里對(duì)本文核心要點(diǎn)進(jìn)行總結(jié):

　　1.索引是提高查詢速度的特別有效的工具，建議熟練掌握。

　　2.請(qǐng)記住各種不同類型的索引，在不同的業(yè)務(wù)場(chǎng)景選擇合適的索引類型使用。