mySQL 教程第11章索引-白红宇

mySQL 教程第11章索引

阅读量：6886 次

发布时间：2019-06-27

本文共 4948 字，大约阅读时间需要 16 分钟。

第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='计算机网络'))