全文大約【1888】字，不說廢話，只講可以讓你學到技術(shù)、明白原理的純干貨!本文帶有豐富案例及配圖視頻，讓你更好的理解和運用文中的技術(shù)概念，并可以給你帶來具有足夠啟迪的思考......
一. 索引
1.什么是索引
小編先來帶各位小伙伴了解下索引的好處是什么呢?那肯定是查詢數(shù)據(jù)塊!通過索引可以快速的查詢到想要的數(shù)據(jù)。MySQL數(shù)據(jù)庫中的索引其實就是一種可以快速獲取數(shù)據(jù)的一種數(shù)據(jù)結(jié)構(gòu)。在表中除了表中的數(shù)據(jù)外，數(shù)據(jù)庫系統(tǒng)還維護著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這種數(shù)據(jù)結(jié)構(gòu)以某種特定的方式指向數(shù)據(jù)，這種數(shù)據(jù)結(jié)構(gòu)就是索引啦。
2.索引的分類
按照功能分類：
● 普通索引： 最基本的索引，它沒有任何限制。
● 唯一索引：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值組合必須唯一。
● 主鍵索引：一種特殊的唯一索引，不允許有空值。一般在建表時同時創(chuàng)建主鍵索引。
● 組合索引：顧名思義，就是將單列索引進行組合。
● 外鍵索引：只有InnoDB引擎支持外鍵索引，用來保證數(shù)據(jù)的一致性、完整性和實現(xiàn)級聯(lián)操作。
● 全文索引：快速匹配全部文檔的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。
3.按數(shù)據(jù)結(jié)構(gòu)分類：
● B+Tree索引 ：MySQL使用最頻繁的一個索引數(shù)據(jù)結(jié)構(gòu)，是InnoDB和MyISAM存儲引擎默認的索引類型。
● Hash索引 : MySQL中Memory存儲引擎默認支持的索引類型。
索引的操作
3.1數(shù)據(jù)準備
下面各位小伙伴和小編一起來為演示索引準備一下表結(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', '錢電', '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)建索引的語法，各位小伙伴可以和小編一起來試一試。
語法：
CREATE [UNIQUE|FULLTEXT] INDEX 索引名稱
[USING 索引類型] -- 默認是B+TREE
ON 表名(列名...);
注意：如果一個表中有一列是主鍵，那么就會默認為其創(chuàng)建主鍵索引!(主鍵列不需要單獨創(chuàng)建索引)。
舉個栗子：
-- 為student表中姓名列創(chuàng)建一個普通索引
CREATE INDEX idx_name ON student(Sname);
-- 為student表中年齡列創(chuàng)建一個唯一索引
CREATE UNIQUE INDEX idx_age ON student(Sage);
3.3查看索引
創(chuàng)建完索引后可以進行查看索引，如下：
語法：SHOW INDEX FROM 表名;
-- 查看student表中的索引
SHOW INDEX FROM student;
3.4刪除索引
最后是刪除索引語法和案例：
語法：DROP INDEX 索引名稱 ON 表名;
-- 刪除student表中的idx_score索引
DROP INDEX idx_score ON student;
-- 查看student表中的索引
SHOW INDEX FROM student;
4.索引效率的測試
下面是創(chuàng)建product商品表，并添加100萬條數(shù)據(jù)做測試。
-- 創(chuàng)建product商品表
CREATE TABLE product(
id INT PRIMARY KEY AUTO_INCREMENT, -- 商品id
NAME VARCHAR(10), -- 商品名稱
price INT -- 商品價格
);
-- 定義存儲函數(shù)，生成長度為10的隨機字符串并返回
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 ;
-- 定義存儲過程，添加100萬條數(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)用存儲過程
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';
-- 通過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';
/*
范圍查詢
*/
-- 查詢價格為800~1000之間的所有數(shù)據(jù) (0.052)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000;
/*
排序查詢
*/
-- 查詢價格為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);
-- 查詢價格為800~1000之間的所有數(shù)據(jù) (0.011)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000;
-- 查詢價格為800~1000之間的所有數(shù)據(jù),降序排列 (0.001)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000 ORDER BY price DESC;
二. 結(jié)語
小編在這里對本文核心要點進行總結(jié):
1.索引是提高查詢速度的特別有效的工具，建議熟練掌握。
2.請記住各種不同類型的索引，在不同的業(yè)務(wù)場景選擇合適的索引類型使用。
京公網(wǎng)安備 11010802030320號