mySQL 教程 第11章 索引

决定哪一行进行索引

1.理解数据

在建索引前，必须对数据有全面的理解，包括逻辑和物理设计，数据特征，如何使用数据，为了设计有用和有效的索引，必须依赖于对用户所发出查询的分析。

2.索引准则

索引列：

主关键字所在的裂，外部关键字或在连接表中经常使用的列，为关键字值范围查询的列，以排序顺序访问的列，在集合过程中列被组合到一起的列。

不要索引的列：

在查询中几乎没有涉及到的列

包含较少的唯一值

Text,ntext,或image数据类型定义的列。不能被索引。

3.选择合适的簇索引

经常要更新的表，考虑在识别列的主关键字上创建簇索引。通过将插入放置到表结束处较小的页中，可以使速度加速

排序，在排序的列上建立簇索引是很有好处的。

列的长度和数据类型

在簇索引中限制列的数目

通过使用varchar数据类型来替代char数据类型

尽可能地使用最小的数据类型，如tinyint替代int

4.支持查询的索引

使用查找参数

编写较好的查找参数----在查找中指定where子句，避免使用主要通配符。

5.决定选择性

选择性，从表的行百分比衍生而来。

高选择性和低选择性

6.决定密度

与选择性相关的概念就是密度。

密度是重复行的平均百分比，唯一的索引具有低密度。

密度是如何影响查询的规划-----因为数据不是均匀分布的，要优化查询可能使用也可能不使用索引。通过表扫描来检索高密度记录，通过索引检索低密度记录。

7.决定数据的分布

定义数据的分布

标准的或平均的分布

与数据分布相关的密度

估计返回行的百分比

准备本章环境：

创建新的数据库schoolDB1做以下练习。

 

练习1：创建表和插入数据

在已有表上添加主键、唯一索引、全文索引和普通索引的语法，指定存储引擎和默认字符集。

1. 创建表

CREATE table student

(

studentID INT,

sname varchar(10),

cardID varchar(18),

address varchar(30),

mobile CHAR(15)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 插入4条记录

INSERT student values (5,'韩立刚','132302197506055634','石家庄青园小区','13803367994');

INSERT student values (10,'韩旭','132302200408095634','石家庄青园小区','13500340430');

INSERT student values (6,'张建','132302198909055634','石家庄维明街','13845454344');

INSERT student values (2,'刘战宏','132302197805095644','石家庄广安街','13234343799')

3. 查看插入的记录

select * from student

可以看到输出顺序和输入的顺序一样。这是没有建立索引的情况。

练习2：创建索引

Alter table table_name add [unique|fulltext] [index|key] index_name (col_name)

1. 创建主键索引

创建主键后,数据存储就按主键顺序存放在数据库.

查看没有主键时的查询计划

explain select * from `student` where studentid=10

添加主键

alter TABLE `student` ADD PRIMARY KEY (studentID)

创建主键后查看查询计划，可以看到

select * from student

可以看到数据按着主键顺序排序。只有表的存储引擎是InnoDB，数据存储才按主键顺序存放。

如果此时插入一条studentID是4的记录，应该放到studentID是2后面。

INSERT student values (4,'张作霖','132302197506055634','东三省','13803367994');4

再次查看

select * from student

注意新插入记录的位置。

2. 创建唯一索引

在cardID列创建唯一索引

alter table `student` add UNIQUE INDEX UniqueIndex(cardID)

查看创建表语句，可以看到创建唯一索引的语句

show create table `student`

explain select * from `student` where cardid='132302197506055634'

3. 创建全文索引

全文索引在 MySQL 中是一个 FULLTEXT 类型索引。FULLTEXT 索引用于 MyISAM 表，可以在 CREATE TABLE 时或之后使用 ALTER TABLE 或 CREATE INDEX 在 CHAR、VARCHAR 或 TEXT 列上创建。对于大的数据库，将数据装载到一个没有 FULLTEXT 索引的表中，然后再使用 ALTER TABLE (或 CREATE INDEX) 创建索引，这将是非常快的。将数据装载到一个已经有 FULLTEXT 索引的表中，将是非常慢的。

全文索引默认不支持中文因此插入几条记录，address列为英文单词

INSERT student values (15,'韩建刚','13230219750605545','student address','13803367994');

INSERT student values (13,'韩旭刚','132302200408095348','Internet work','13500340430');

INSERT student values (16,'张京玲','132302198909055765','education hospital','13845454344');

INSERT student values (12,'刘战虎','132302197805095345','stree fulltext','13234343799')

INSERT student values (115,'张静','12330219750605545','student apple address','13803367994');

INSERT student values (113,'马扬','134342200408095348','Internet JAVA','13500340430');

INSERT student values (116,'孔令伟','132546195454855765','Network hospital','13845454344');

INSERT student values (112,'张德昭','132304565095345','stree Test fulltext','13234343799')

在address列创建全文索引

alter table `student` add FULLTEXT INDEX FulltextAddress(address)

使用全文索引查找address列中包含“work”的单词

SELECT * FROM `student` WHERE MATCH (address) AGAINST ('work');

查看查询计划，可以看到使用全文索引查找的记录

explain SELECT * FROM `student` WHERE MATCH (address) AGAINST ('test');

待解决问题：让mySQL支持中文的全文索引。友情提示：百度一下！

4. 创建普通索引

alter table `student` add index indexName(sname)

EXPLAIN select * from `student` where sname='孔令伟'

5. 查看表的索引

show index from student

双击student表，可以看到该表上的所有索引。

6. 查看有子查询的语句执行计划

EXPLAIN select sname 姓名 from `TStudent` where Studentid in (select StudentID from `TScore` where mark>85 and subjectID in (select subjectID from `TSubject` where subJectName='计算机网络'))

练习3：删除索引

7. 删除删除唯一索引

Alter table `student` drop index uniqueIndex

8. 删除主键

Alter table `student` drop PRIMARY Key

9. 删除普通索引

Alter table `student` drop index IndexName

10. 删除全文索引

Alter table `student` drop index fulltextAddress

11. 再次查看看表中索引

show index from student 已无索引

练习4：创建组合索引

12. 创建组合索引

为studentID和sname列创建组合索引

alter table `student`add INDEX multiIndex(studentID,sname)

查看创建的索引

show index from student

使用符合索引查询数据

explain select * from `student` where studentID=116 and sname='孔令伟'

练习5：创建表时创建索引

13. 创建表时创建索引

create TABLE ts

(

studentid INT,

sname VARCHAR(10),

INDEX(studentid)

)

没有指定索引名

show index from ts

14. 创建表时指定主键

创建表时将studentid设置为主键 自增 不允许为空

create TABLE ts2

(

studentid INT NOT NULL AUTO_INCREMENT,

sname VARCHAR(10),

PRIMARY KEY(studentid)

)

15. 创建表时指定唯一索引

create table ts3

(

studentid INT,

sname VARCHAR(10),

UNIQUE index UniqIdx(studentid)

)

也可以不指定索引名称

create table ts4

(

studentid INT,

sname VARCHAR(10),

UNIQUE index(studentid)

)

16. 创建符合索引

create TABLE ts5

(

studentid INT,

sname VARCHAR(10),

INDEX sidname (studentid,sname)

)

练习6：验证创索引前后查询时间变化

给schoolDB数据的TStudent表插入100000条记录

执行以下语句 查看耗时

select * from `TStudent` where cardID='38922323433437680'

创建给cardID创建索引

alter table TStudent add index cardIDIND (cardID)

在执行

select * from `TStudent` where cardID='38922323433437680'

查看耗时

可以看到建立索引大大降低了查询时间。

本文转自 onesthan 51CTO博客，原文链接：http://blog.51cto.com/91xueit/1137986，如需转载请自行联系原作者