数据准备
-- 创建db12数据库 CREATE DATABASE db12; -- 使用db12数据库 USE db12; -- 创建student表 CREATE TABLE student( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), age INT, score INT ); -- 添加数据 INSERT INTO student VALUES (NULL,'张三',23,98),(NULL,'李四',24,95), (NULL,'王五',25,96),(NULL,'赵六',26,94),(NULL,'周七',27,99);创建索引
注意：如果一个表中有一列是主键，那么就会默认为其创建主键索引！(主键列不需要单独创建索引)
-- 标准语法 CREATE [UNIQUE|FULLTEXT] INDEX 索引名称 [USING 索引类型] -- 默认是B+TREE ON 表名(列名...); -- 为student表中姓名列创建一个普通索引 CREATE INDEX idx_name ON student(NAME); -- 为student表中年龄列创建一个唯一索引 CREATE UNIQUE INDEX idx_age ON student(age);查看索引
-- 标准语法 SHOW INDEX FROM 表名; -- 查看student表中的索引 SHOW INDEX FROM student;alter语句添加索引
-- 普通索引 ALTER TABLE 表名 ADD INDEX 索引名称(列名); -- 组合索引 ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...); -- 主键索引 ALTER TABLE 表名 ADD PRIMARY KEY(主键列名); -- 外键索引(添加外键约束，就是外键索引) ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名); -- 唯一索引 ALTER TABLE 表名 ADD UNIQUE 索引名称(列名); -- 全文索引(mysql只支持文本类型) ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名); -- 为student表中name列添加全文索引 ALTER TABLE student ADD FULLTEXT idx_fulltext_name(name); -- 查看student表中的索引 SHOW INDEX FROM student;删除索引
-- 标准语法 DROP INDEX 索引名称 ON 表名; -- 删除student表中的idx_score索引 DROP INDEX idx_score ON student; -- 查看student表中的索引 SHOW INDEX FROM student; 索引效率的测试 -- 创建product商品表 CREATE TABLE product( id INT PRIMARY KEY AUTO_INCREMENT, -- 商品id NAME VARCHAR(10), -- 商品名称 price INT -- 商品价格 ); -- 定义存储函数，生成长度为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万条数据到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 ; -- 调用存储过程 CALL pro_test(); -- 查询总记录条数 SELECT COUNT(*) FROM product; -- 查询product表的索引 SHOW INDEX FROM product; -- 查询name为OkIKDLVwtG的数据 (0.049) SELECT * FROM product WHERE; -- 通过id列查询OkIKDLVwtG的数据 (1毫秒) SELECT * FROM product WHERE id=999998; -- 为name列添加索引 ALTER TABLE product ADD INDEX idx_name(NAME); -- 查询name为OkIKDLVwtG的数据 (0.001) SELECT * FROM product WHERE; /* 范围查询 */ -- 查询价格为800~1000之间的所有数据 (0.052) SELECT * FROM product WHERE price BETWEEN 800 AND 1000; /* 排序查询 */ -- 查询价格为800~1000之间的所有数据,降序排列 (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之间的所有数据 (0.011) SELECT * FROM product WHERE price BETWEEN 800 AND 1000; -- 查询价格为800~1000之间的所有数据,降序排列 (0.001) SELECT * FROM product WHERE price BETWEEN 800 AND 1000 ORDER BY price DESC; 索引的实现原则索引是在MySQL的存储引擎中实现的，所以每种存储引擎的索引不一定完全相同，也不是所有的引擎支持所有的索引类型。这里我们主要介绍InnoDB引擎的实现的B+Tree索引。
B+Tree是一种树型数据结构，是B-Tree的变种。通常使用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序。我们逐步的来了解一下。
磁盘存储系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的
位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB。