MySQL索引分类及其使用实例详解
一、MySQL索引分类
MySQL索引主要分为B-tree索引和哈希索引两种,其中B-tree索引又包括主键索引、唯一索引、普通索引、全文索引、空间索引等。
1. B-tree索引
B-tree索引是MySQL中最常用的索引类型,其优点是检索速度快,缺点是对于索引列有前缀的字段,需要存储较多的内容。
B-tree索引又包括以下几种:
- 主键索引:用于唯一标识每一行数据,具有唯一性和非空性,每个表只能有一个主键索引,且不能为NULL。
- 唯一索引:是指列中的值必须唯一,可以为空。
- 普通索引:对于普通查询的列可以建立。
- 全文索引:用于全文检索,只能在MyISAM存储引擎下使用。
- 空间索引:用于空间数据类型的检索。
2. 哈希索引
哈希索引通过将索引值作为哈希函数的输入,然后将计算得到的哈希值与哈希表进行匹配,如果存在则返回地址,否则返回空。哈希索引适用于等值查询场景,如WHERE column = value
。
二、MySQL索引使用实例
1. 普通索引示例
假设我们有一个用户表,包含以下字段:id、name、age、gender和email:
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` enum('male','female') DEFAULT NULL,
`email` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
我们为name字段建立普通索引:
ALTER TABLE `user` ADD INDEX `idx_name` (`name`);
之后可以使用以下SQL语句对name进行模糊查询,其效率将大大提升:
SELECT * FROM `user` WHERE `name` LIKE 'Tom%';
2. 唯一索引示例
假设我们有一个学生表,包含以下字段:id、name、age和email:
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`email` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
我们为email字段建立唯一索引:
ALTER TABLE `student` ADD UNIQUE INDEX `idx_email` (`email`);
之后插入以下重复记录将会失败:
INSERT INTO `student` (`name`, `age`, `email`) VALUES ('Tom', 18, 'test@test.com');
INSERT INTO `student` (`name`, `age`, `email`) VALUES ('Jerry', 19, 'test@test.com');
三、总结
MySQL索引分类及其实例已经讲解完毕,需要注意的是,在选择索引类型时需根据场景和需求进行选择,以达到最优的效果。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:Mysql索引分类及其使用实例详解 - Python技术站