Mysql_索引详细介绍

概念

索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构。就好比三个人读作“众”，三个木叫“森”，三个鬼，当然不叫“救命”，而叫做“mei”,第二声。跟梅的读音一样。当你没学过怎么查字典的时候只能从头翻到尾，学过后你就知道要么通过“部首查字法”或者“拼音查字法”来查询这个字。这两种方式就是今天所说的索引概念。

内容

索引的原理
索引的优势
索引的劣势
设计原则
索引的类型
索引的增删方式
索引的使用场景及优化
索引的优化
案例

索引的原理

把无序的数据变成有序的查询.

索引的优势

1.可以保证每一行数据的唯一性；
2.加大查询效率；
3.在分组和排序子句查询时，可以减少分组和排序的时间

索引的劣势

1.创建索引和维护索引好费时间，随着数量的增加所耗费的时间也会增长。
2.索引占磁盘空间。
3.增删改的时候，索引也要动态维护，降低了数据的维护速度。

设计原则

1.并非越多越好。
2.避免对经常更新的表进行过多的索引。
3.数量小的表不要使用索引。
4.在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。例如： 性别字段。
5.在频繁进行排序或者分组的列上应该建立组合索引。

索引的类型

唯一索引：索引列的值必须唯一，允许空值，组合索引，列值的组合必须唯一。主索引列值不允许有控制。
单列索引：一个索引只包含单个列，一个表可以有多个单列索引。
组合索引：在多个字段上创建的索引，只有在查询条件中使用了这些字段的左边子段时，索引才会被使用，遵循最左前缀集合。
全文索引：fulltext，在定义索引的列上支持值的全文查找，允许插入重复值和空值。只有myISAM支持全文索引。
空间索引：对空间数据类型的字段建立的索引，Mysql中的空间数据类型有4种，分别是：Geometry,point,linestring,polygon

索引的增删方式

创建索引

create index 索引名称 on 表名(column_name);
1

修改索引

index 普通索引
alter table 表名 add index index_name(column);
最基本的索引，没有任何限制
primary key 主键索引
alter table 表名 add primary key(column);
是一种特殊的唯一索引，不允许有空值
unique 唯一索引
alter table 表名 add unique(column);
与“普通索引”类似，不同的就是，索引列的值必须是唯一，但允许有空值
filltext 全文索引
alter table 表名 add fulltext(column);
仅可用于MyISAM表，针对较大的数据，生成全文索引很耗时和耗空间
组合索引
alter table 表名 add index index_name(column1,column2,column3);
遵循“最左前缀”原则

删除索引

-- 方式一
drop index 索引名称 on 表名;  
-- 方式二
Alter table  表名 drop index 索引名;  
1
2
3
4

索引的使用场景及优化

适合的使用场景

1、频繁作为WHERE查询条件的字段
2、经常GROUP BY和ORDER BY的列
3、UPDATE、DELETE的WHERE条件列
4、DISTINCT字段需要创建索引
5、多表JOIN连接操作时
6、频繁的列放到联合索引的左侧
7、区分度高（散列性高）的列适合作为索引

索引的优化

1、在WHERE中使用不到的字段，不要设置索引
2、数据量小的表最好不要使用索引
3、有大量重复数据的列上不要建立索引
4、避免对经常更新的表创建过多的索引
5、不建议用无序的值作为索引
6、删除不再使用或者很少使用的索引
7、不要定义冗余或重复的索引

案例

– 创建数据库

create database index_test;
1

– 选择数据库

use index_test;
1

– 创建表

create table index_table(
id int(11) not null primary key auto_increment,
name char(100) not null,
address char(100),
description char(100),
unique index uniqIdx(id),
index multiColIdx(name(20),address(30)),
index ComIdx(description(30))
);
1
2
3
4
5
6
7
8
9

– 查看索引信息

show create table index_table;
1

– 查看索引信息

show create table index_table;
1

– 创建表2

create table test_table2(
id int(11) not null primary key,
firstname char(50) not null,
middlename char(50) not null,
lastname char(50) not null,
birth date not null,
title char(100)
)engine =MyISAM
1
2
3
4
5
6
7
8

– 在birth字段上创建普通索引,名称为 sy1

alter table test_table2 add index sy1(birth);
1

– 在test_table2的id上添加索引，名称为 sy2并降序排列;

alter table test_table2 add unique index sy2(id desc);
1

– 在字段（firstname,lastname,middlename）列上添加组合索引 sy3

create index sy3 on test_table2(firstname,middlename,lastname);
1

– 在字段title上添加sy4 全文索引

create fulltext index sy4 on test_table2(title);
1

– 删除index_table表中名称为UniqIdx的索引

alter table index_table drop index UniqIdx;
1

– 删除test_table2中的sy3索引

Drop index sy3 on test_table2;
1