第9章索引

索引

索引用于快速找出在某个列中有一 特定值的行.

内容导读

• 了解什么是索引
• 掌握创建索引的方法和技巧
• 熟悉如何删除索引
• 掌握综合案例中索引创建的方法和技巧
• 熟志操作索引的常见问题

---

索引简介

索引的含义和特点

索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可提高数据库中特定数据的查询速度。

索引是一个单独的、 存储在磁盘上的数据库结构,它们包含着对数据表里所有记录的引用指针

索引是在存储引擎中实现的

大多数存储引擎有更高的限制.MySQL中索引的存储类型有两种：BTREE和HASH,具体和表的存储引擎相关：MyISAM和I lnnoDB
存储引擎只支持BTREE索’引： MEMORY/HEAP存储引擎可以支持 HASH和BTREE索引 。

索引的优点主要以下儿条：
保证每一行数据唯一性 加快查询速度 加快表与表之间的连接 减少分组和排序时间‘

增加索引也有许多不利的方面：
创建索引和维护索引要耗费时间，索引需要占磁盘空间，增删改索引也要动态的维护

索引的分类

1.普通索引和唯一索引
普通索引是MySQL中的基本索引类型,允许在定义索引的列中插入重复值和空值
唯一索引,索引列的值必须唯一,但允许有空值.如果是组合索引,则列值的组合必须唯一．

2.单列索引和组合索引
单列索引即一 个索引只包含单个列 ，一 个表可以有多个单列索引。
组合索引指在表的多个字段组合上创建的索引， 只有在查询条件中使用了这些字段的左边字 段时， 索引才会被使用。

3. 全文索引
   全文索引类型为FULLTEXT， 在定义索引的列上支持值的全文查找， 允许在这些索引列中插 入重复值和空值．

4. 空间索引
   空间索引是对空间数据类型的字段建立的索引， MySQL中的空间数据类型有4种， 分别是： GEOMETRY、PO剧 T、LINESTRING和 POLYGON.

索引的设计原则

1.当表中的数据更改的同时， 索引也会进行调整和更新。
2.避免对经常更新的表进行过多的索引
3.数据量小的表最好不要使用索引，
4.在条件表达式中经常用到的不同值较多的列上建立索引
5.当唯一－性是某种数据本身的特征时， 指定唯一索引
6.在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引

创建索引

创建普通索引

CREATE TABLE book
(
bookid int not null,
index(bookid)
);

使用 EXPLAIN 话，句查看索寻｜是否正在使用

explain select * from book where bookid=990\G

2.创建唯一索引

CREATE TABLE tl(
id int not null,
name char(30) not null,
unique index uniqueIndex(id)
);

3.创建单列索引

单列索引是在数据表中的某一个字段上创建的索钊

CREATE TABLE t2 
id INT NOT NULL, 
name CHAR(50) NULL, 
INDEX Singleldx(name(20))
); 

4.创建组合索引
组合索引是在多个字段上创建一个索引。

5. 创建全文索引

CREATE TABLE t4 (
id INT NOT NULL, 
name CHAR(30) NOT NULL, 
age INT NOT NULL, 
info VARCHAR(255), 
FULL TEXT INDEX FullTxtldx(info)
);

6.创建空间索引

CREA TE TABLE t5 
(g GEOMETRY NOT NULL, 
SPATIAL INDEX spatldx(g))
ENGINE=MyISAM; 

在已经存在的表上创建索引

ALTER TABLE book ADD INDEX BkNameldx ( bookname(30));

ALTER TABLE book ADD UNIQUE INDEX Uruqidldx ( bookld ); 

ALTER TABLE book ADD INDEX Bkcmtidx ( comment(50) ); 

ALTER TABLE book ADD INDEX BkAuAndlnfoldx(authors(20),info(50) ); 

ALTER TABLE t6 ADD FULL TEXT INDEX infofidx(info);

ALTER TABLE t7 ADD SPATIAL INDEX spatldx(g);  

2.使用CREATE INDEX创建索引

CREATE INDEX 语句可以在已经存在的表上添加索引

CREATE INDEX BkNameidx ON book(bookname); 

CREATE UNLQUE INDEX UniqueIdx ON book ( bookid );

CREATE INDEX BkAuAridlnfoldx ON book (authors(20),info(50)); 

CREATE FULL TEXT INDEX ON t6(info); 

CREATE SPATIAL INDEX spatldx ON t7 (g); 

删除索引

添加 AUTO CNCREMENT 约束字段的唯一索引不能被删除。

使用ALTERTABLE 删除索引

ALTER TABLE table_name DROP INDEX index_narne; 

ALTER TABLE book DROP INDEX Unigidldx; 

使用 DROP INDE×语旬删除索引

DROP INDEX index_name ON table_name;

drop Index  BkAuAridlnfoldx On book;