1. 索引的重要性
- 索引之于数据表,相当于检字表于汉语字典,设想让你在没有检字表的情况下从字典中找出一个“武”字,你会多么无助,如果用检字表,我们一眼就能找出它在第509页
- 数据库服务器用索引去找到相应的记录,相当于我们使用检字表去找相应的字
2. 预置环境
为了更好的描述相关知识,我们先在数据库中创建一张table_demo表,此表仅用于演示后面的相关操作,其设计合理性不在考虑之列,后面所有的操作均以此为基点
DROP TABLE IF EXISTS `table_demo`;
CREATE TABLE `table_demo` (
`id` int(11) NOT NULL,
`field_1` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`field_2` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`field_3` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`field_4` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3. Mysql索引创建语法
ALTER TABLE 表名
ADD [索引类型] INDEX | KEY [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法];
1. 索引类型
- PRIMARY:主键索引。唯一且不能为空,一张表只能有一个主键索引
ALTER TABLE `table_demo`
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`, `field_1`);
- UNIQUE:唯一索引
ALTER TABLE `table_demo`
ADD UNIQUE INDEX (`field_1`) ;
- FULLTEXT:全文索引
ALTER TABLE `table_demo`
ADD FULLTEXT INDEX (`field_1`) ;
- 不指定索引类型,则为普通索引
ALTER TABLE `table_demo`
ADD INDEX (`field_1`) ;
2. KEY和INDEX
本人查阅了一些资料,发现这个区别对用户来说,有点无聊,目前我们先记住一条:只有创建PRIMARY时,后面跟KEY,其它索引全部是跟INDEX
ALTER TABLE `table_demo`
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`, `field_1`);
关于外键,本人从毕业到现在,从未主动使用过外键,至于原因么:外键带来的好处远小于它带来的坏处
3. ASC | DESC
查阅了一些资料,是MySQL8出来的东西,暂时忽略吧,根据索引原理,这个估计也没啥子用
4. 索引方法
- HASH:顾名思意,就是点击式搜索,譬如“=”,“<>”
ALTER TABLE `table_demo`
ADD UNIQUE INDEX (`field_1`) USING HASH ;
- BTREE:非点击式搜索,都用BTREE吧,譬如>,>=,<,<=、BETWEEN、Like
ALTER TABLE `table_demo`
ADD UNIQUE INDEX (`field_1`) USING BTREE ;
5. 组合索引
创建示范
ALTER TABLE `table_demo`
ADD INDEX (`field_1`, `field_2`, `field_3`, `field_4`) ;
- 组合索引(别名:联合索引或复合索引),是一个索引中包含多个字段。
- Mysql从左到右的使用组合索引中的字段,一个查询可能只使用索引中的前部分字段,以刚刚创建的组合索引为例,一个查询可能会使用(field_1)或(field_1, field_2)或(field_1, field_2, field_3)或(field_1, field_2, field_3, field_4),但不会使用(field_2, field_3, field_4)、(field_3, field_4)等字段作为索引。
- 前一条规则,可以对我们的索引设计形成很好的指导:Mysql相当于把组合索引的前面的字段全部依次建立了索引,这样我们就没必要再对这些字段进行单一的创建索引,下图很好的演示了组合索引