MySQL 私房菜————数据库索引|创建方法|查看索引|

1.Mysql索引

1.1 索引简介

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于c语言的链表通过指针指向数据记录的内存地址）。

使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。

索引就像是一本书的目录一样，可以让你根据目录快速找到指定的内容

索引是表中一列或者若干列值排序的方法。

建立索引的目的是加快对表中记录的查找或排序。

1.2 索引的作用

• 在数据量特别大的时候减少查询时间极大提升效率
• 降低数据库IO成本 降低数据库的排序成本 降低服务器的成本
• 关键字段的值有规律可循会进行排序
• 多表联合查询时，加快表与表的连接
• 在使用分组和排序时，可以大大减少排序时间

1.3 索引的副作用

• 索引需要占用额外的磁盘空间。

对于 MyISAM 引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址。而 InnoDB 引擎的表数据文件本身就是索引文件。

• 在插入和修改数据时要花费更多的时间，因为索引也要随之变动。

1.4 创建索引的原则依据

• 有索引时先看索引，没有索引时才会去检索全文，如果检索使用不当会增加负担
• 表的主键、外键必须有索引，因为主键具有唯一性
• 记录数超过300行的表应该有索引，因为每次查询都会把表遍历一边，就会造成负担
• 经常和其他表连接的表
• 唯一性太差的字段不适合建立索引
• 更新太频繁的字段不适合建立索引
• 经常出现在where子句中的字段应该建立索引
• 索引应该建在选择性高的字段上[唯一性不是太差的字段]
• 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建立索引

1.5 索引的分类

普通索引、唯一索引、主键索引、组合索引、全文索引

2.索引的分类和创建

create table member (id int(10),name varchar(10),cardid int(18),phone int(11),address
varchar(50),remark text);

2.1 普通索引:最基本的索引类型，没有唯一性之 类的限制。

直接创建索引

CREATE INDEX 索引名 ON 表名 (列名[(length)]);
# (列名(length)):length是可选项，下同。如果忽略length的值，则使用整个列的值作为索引。如果指定，使用列的前length个字符来创建索引，这样有利于减小索引文件的大小。

#索引名建议以“index"结尾。
create index name_index on member (name) ;

修改表方式创建

ALTER TABLE 表名 ADIN INDEX 索引名 (列名);

创建表的时候指定索引

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[...],INDEX 索引名 (列名));

2.2 唯一索引:与普通索引类似，但区别是唯一索引列的每个值都唯一。唯一索引允许有空值(注意和主键不同)。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引。

直接创建唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名(列名);
create unique index cardid_index on member (cardid);

修改表方式创建

ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);

创建表的时候指定

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));

2.3 主键索引:是一种特殊的唯一索引，必须指 定为“PRIMARY KEY"。一个表只能有一个主键，不允许有空值。添加主键将自动创建主键索引。

创建表的时候指定

CREATE TABL E表名 ([...],PRIMARY KEY (列名));

修改表方式创建

ALTER TABLE 表名 ADD PRIMARY KEY (列名) ;

alter table member add primary key (id) ;

2.4 组合索引( 单列索引与多列索引) :可以是单列上创建的索引，也可以是在多列上创建的索引。需要满足最左原则，因为select语句的where条件是依次从左往右执行的，所以在使用select 语句查询时where条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。

CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));

select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='..

3.删除索引

3.1 直接删除索引

drop index 索引名 on 表名;

3.2 修改表方式删除索引

alter table 表名 drop index 索引名;

3.3 删除主键索引

alter table 表名 drop primary key;

4.查看索引

show index from 表名;
show keys from 表名;
show index from member\G;

4.1 各个字段的含义

字段	含义
Table	表的名称
Non_unique	如果索引不能包括重复词，则为 0；如果可以，则为 1
Key_name	索引的名称
Seq_in_index	索引中的列序号，从 1 开始
Column_name	列名称
Collation	列以什么方式存储在索引中。在 MySQL 中，有值‘A’（升序）或 NULL（无分类）
Cardinality	索引中唯一值数目的估计值
Sub_part	如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为 NULL
Packed	指示关键字如何被压缩。如果没有被压缩，则为 NULL
Null	如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO
Index_type	用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）

5.总结

5.1 索引的优缺点

优点	缺点
减少创建临时表和排序对服务器的负担	索引需要占用物理空间
减少需要扫描的数据量，大大加快检索速度	后期维护随着数据量增大会逐渐繁重
将随机IO变成顺序IO提高性能	对于非常小的表，大部分情况下全表扫描会更高效

5.2 随机IO和顺序IO

• 顺序IO比随机IO快
• 顺序IO一般只扫描一次数据
• 随机IO查询特定的行
• 顺序IO查询数据块上所有的行