        日常生活中，我们经常会在电话号码簿中查阅“某人”的电话号码，按姓查询或者按字母排序查询；在字典中查阅“某个词”的读音和含义等等，以快速的找到特定记录。在这里，“姓”和“字母”都可看作是索引，而按“姓”或者“字母”查询则是按索引查询！

        索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录，是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。

        通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列，否则数据库系统将读取每条记录的所有信息进行匹配。例如，索引相当于新华字典的音序表，如果要查“过”字，如果不适用音序，就需要从字典的第一页开始翻几百页；如果提取拼音出来，构成音序表，就只需要从10多页的音序表中直接查找，这样就可以大大节省时间。因此，使用索引可以在很大程度上提高数据库的查询速度，有效地提高了数据库系统的性能。

一、索引的概述

1.索引类型

索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。下文中将详细介绍~

2.索引存储

数据库底层索引实现主要有两种存储类型，B树(BTREE)和哈希(HASH)索引，InnoDB和MyISAM 使用BTREE索引；而MEMORY 存储引擎可以使用BTREE 和HASH 索引，默认用BTREE.在没有指定的情况下，数据库使用的引擎是 InnoDB。

3.索引优缺点

优点：

可以提高检索数据的速度。

缺点：

创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态地维护索引，造成数据的维护速度降低了。

4.使用建议

索引可以提高查询的速度，但是会影响插入记录的速度，因为向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。

5.创建和查看索引

创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。创建索引有3种方式，分别是创建表的时候创建索引、在已经存在的表上创建索引和使用ALTER TABLE语句来创建索引。本节将根据具体的索引分类详细的讲解这3种创建方法。

二、普通索引

1.创建表时定义索引

CREATE TABLE tablename(

    propname1 type1,

    propname2 type2,

    ……

    propnamen type..n,

     INDEX | KEY

    [indexname] (propnamen [(length)] [ ASC | DESC ] ) );

其中，参数INDEX和KEY是用来指定字段为索引的，两者选择其中之一就可以了，作用是一样的；参数indexname是索引名字，可省略；参数propnamen是索引对应的字段的名称，该字段必须为前面定义好的字段；参数length是可选参数，其指索引的长度，必须是字符串类型才可以使用；参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列，如果不指定，则为升序。

2.已存在的表上创建索引

(1).指向create语句

CREATE INDEX indexname

ON tablename (propname [(length)] [ASC|DESC]);

参数INDEX是用来指定字段为索引，此处不能为KEY；参数indexname是新创建的索引的名字；参数tablename是指需要创建索引的表的名称，该表必须是已经存在的，如果不存在，需要先创建；参数propname指定索引对应的字段的名称，该字段必须为前面定义好的字段；参数length是可选参数，表示索引的长度，必须是字符串类型才可以使用；参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列,默认升序。

mysql> create database school;   #创建数据库school 

mysql> use school;   #选择数据库school 

mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64));       #创建表class, 并建立为id 字段索引 

mysql> create index index_id on class(id ASC);  #追加升序索引 

mysql> show create table class;  #查看表定义 

mysql> insert into class values(1, '一班', 'Martin');  # 插入记录1

mysql> insert into class values(1, '二班', 'Rock');   # 插入记录2

mysql> select * from class where id > 0 ;   #根据id查询记录，结果将降序排列

(2).指向 alter table 语句

ALTER TABLE tablename ADD INDEX | KEY indexname

(propname [(length)] [ASC|DESC]);

在上述语句中，参数tablename是需要创建索引的表；关键字IDNEX或KEY用来指定创建普通索引；参数indexname用来指定所创建的索引名；参数propname用来指定索引所关联的字段的名称；参数length用来指定索引的长度；参数ASC用来指定升序排序；参数DESC用来指定降序排序。

3.查看索引执行情况

EXPLAIN 查询语句

输出结果：

key：实际使用的索引。如果为NULL，则没有使用索引

possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。此值越短越好！

三、唯一索引

1.创建表定义索引

所谓唯一索引，就是在创建索引时，限制索引的字段值必须是唯一的。通过该类型的索引可以比普通索引更快速地查询某条记录。

CREATE TABLE tablename(

propname1 type1,

……

propnamen type..n,

UNIQUE INDEX | KEY [indexname] (propnamen [(length)] [ ASC | DESC ] ) );

注意：参数UNIQUE INDEX和UNIQUE KEY是用来指定字段为索引的，两者选择其中之一即可；参数indexname是索引名字，可省略；参数propnamen是索引对应的字段的名称，该字段必须为前面定义好的字段且必须定义为 UNIQUE 约束；参数length是可选参数，其指索引的长度，必须是字符串类型才可以使用；参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列，如果不指定，则为升序。

2.已存在的表上创建索引

(1).create命令创建

CREATE UNIQUE INDEX indexname 

    ON tablename (propname [(length)] [ASC|DESC]);

(2).alter命令创建

ALTER TABLE tablename ADD UNIQUE INDEX | KEY indexname (propname [(length)] [ASC|DESC]);

四、全文索引

        全文索引主要对字符串类型建立基于分词的索引，主要是基于CHAR、VARCHAR和TEXT的字段上，以便能够更加快速地查询数据量较大的字符串类型的字段。

        全文索引以词为基础的，MySQL默认的分词是所有非字母和数字的特殊符号都是分词符。

        MySQL从3.23.23版本开始支持全文索引，MySQL5.6以前只能在存储引擎为MyISAM的数据表上创建全文索引,5.6之后InnoDB开始支持全文索引(5.7之后支持中文全文索引) 。在默认情况下，全文索引的搜索执行方式为不区分大小写，如果全文索引所关联的字段为二进制数据类型，就以区分大小写的搜索方式执行。

1.创建表时定义索引

以下代码：

CREATE TABLE tablename(

    propname1 type1,

    propname2 type2,

    ……

    propnamen type..n,

    FULLTEXT INDEX | KEY

    [indexname] (propnamen [(length)] ) );

实战代码：

mysql> create database school;   #创建数据库school 

mysql> use school;   #选择数据库school 

mysql>create table class(id int, name varchar(128) UNIQUE, teacher varchar(64),comment varchar(1024),FULLTEXT INDEX index_comm(comment));       #创建表class, 并建立为comment 字段为全文索引 

mysql> insert into class values(1,'1班','Martin','我是一个兵，来自老百姓!');  # 插入记录1

mysql> insert into class values(2,'2班','Rock','此班主任毕业自唐僧系');   # 插入记录2

mysql> insert into class values(3,'3班','Janny','I''m Miss Zhang.');   #插入记录3 

mysql> select * from class where match(comment) AGAINST('我是一个兵');#利用全文检索索引快速查询记录

2.已存在的表上创建索引

(1).执行create语句

在MySQL中创建全文索引除了通过SQL语句FULLTEXT INDEX来实现外，还可以通过SQL语句CREATE FULLTEXT INDEX来实现，其语法形式如下：

CREATE FULLTEXT INDEX indexname

ON tablename( propname1 [ ( length ) ] );

在上述语句中，关键字CREATE FULLTEXT INDEX表示用来创建全文索引。

如下例表已存在，可通过CREATE语句创建全文索引：

mysql> create database school;   #创建数据库school 

mysql> use school;   #选择数据库school 

mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64));       #创建表class, 并建立为id 字段索引 

mysql> create FULLTEXT index index_teacher on class(teacher );  #追加全文索引 

mysql> show create table class;  #查看表定义

(2).执行 alter table 语句

除了上述两种方式来创建全文索引外，在MySQL中创建全文索引还可以通过SQL语句ALTER来实现，其语法形式如下：

ALTER TABLE tablename

ADD FULLTEXT INDEX|KEY indexname(propname [(length)]);

3.使用场景

根据全文索引字段进行全文检索数据：

SELECT * FROM 表名WHERE MATCH(`列名`) AGAINST('关键字');

mysql> create database school;   #创建数据库school 

mysql> use school;   #选择数据库school 

mysql> mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64),info varchar(1024),FULLTEXT INDEX index_des(info));       #创建表class, 并建立为info 字段为全文索引 

mysql> insert into class values(1,'1班','Martin','我是一个兵，来自老百姓!');  # 插入记录1

mysql> insert into class values(2,'2班','Rock','此班主任毕业自唐僧系');   # 插入记录2

mysql> insert into class values(3,'3班','Janny','I'm Miss Zhang.');   # 插入记录3

mysql> select * from class where match(teacher) AGAINST('我是一个兵');#根据id查询记录

4.MySQL8 中文分词支持

配置文件my.ini（Windows 10默认路径: C:ProgramDataMySQLMySQL Server 8.0）中增加如下配置项，同时重启MySQL80 服务:

[mysqld]

ngram_token_size=2

mysql> create database school;   #创建数据库school 

mysql> use school;   #选择数据库school 

mysql> mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64),comment varchar(1024),FULLTEXT INDEX index_des(comment) with parser   ngram);       #创建表class, 并建立为comment 字段为全文索引 

mysql> insert into class values(1,'1班','Martin','我是一个兵，来自老百姓!');  # 插入记录1

mysql> insert into class values(2,'2班','Rock','此班主任毕业自唐僧系');   # 插入记录2

mysql> insert into class values(3,'3班','Janny','I''m Miss Zhang.');   #插入记录3

mysql> select * from class where match(comment) AGAINST('百姓');#利用全文检索索引快速查询记录

mysql> select * from class where match(comment) AGAINST('唐僧');#利用全文检索索引快速查询记录

五、多列索引

多列索引，是指在创建索引时所关联的字段不是一个字段，而是多个字段，虽然可以通过所关联的字段进行查询，但是只有查询条件中使用了所关联字段中的第一个字段，多列索引才会被使用。

1.创建表时定义索引

CREATE TABLE tablename(

    propname1 type1,

    ……

    propnamen type..n,

    INDEX | KEY [indexname] (propname1 [(length)] [ ASC | DESC ],

                             Propname2 [(length)] [ ASC | DESC ],

                              ... ...

                             Propnamen [(length)] [ ASC | DESC ])

);

注意：和普通索引定义基本相同，不同之处就是增加了多个索引列。

mysql> create database school;   #创建数据库school 

mysql> use school;   #选择数据库school 

mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64), INDEX index_mult_columns(id, teacher));       #创建表class, 并建立包含id,teacher字段的多列索引 

mysql> show create table class;  #查看表定义 

mysql> insert into class values(1, '一班', 'Martin');  # 插入记录1

mysql> insert into class values(1, '二班', 'Rock');   # 插入记录2

mysql> select * from class where id > 0 ;   #仅根据id查询记录会启用多列索引

2.已存在的表上创建索引

(1).执行create语句

CREATE INDEX indexname

    ON tablename( propname1 [(length)] [ ASC | DESC ],

                   Propname2 [(length)] [ ASC | DESC ],

                              ... ...

                         Propnamen [(length)] [ ASC | DESC ] );

在上述语句中，关键字CREATE INDEX表示用来创建多列索引。

如下例表已存在，可通过CREATE语句创建多列索引：

mysql> create database school;   #创建数据库school 

mysql> use school;   #选择数据库school 

mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64));       #创建表class, 并建立为id 字段索引 

mysql> create  index index_id on class(id, name );  #追加多列索引 

mysql> show create table class;  #查看表定义

(2).执行 alter table 语句

除了上述两种方式来创建全文索引外，在MySQL中创建全文索引还可以通过SQL语句ALTER来实现，其语法形式如下：

ALTER TABLE tablename

    ADD INDEX|KEY indexname(propname1 [(length)] [ ASC | DESC ],

                   Propname2 [(length)] [ ASC | DESC ],

                              ... ...

                         Propnamen [(length)] [ ASC | DESC ] );

六、隐藏、删除和修改索引

1.隐藏索引

MySQL 8开始支持隐藏索引。隐藏索引提供了更人性化的数据库操作。

隐藏索引，顾名思义，让索引暂时不可见，不会被优化器使用。默认情况下索引是可见的。隐藏索引可以用来测试索引的性能。验证索引的必要性时不需要删除索引，可以先将索引隐藏，如果优化器性能无影响就可以真正地删除索引。

ALTER TABLE tablename ALTER  INDEX  index_name INVISIBLE;  #隐藏索引

ALTER TABLE tablename ALTER  INDEX  index_name VISIBLE;    #取消隐藏

2.删除索引

所谓删除索引，就是删除表中已经创建的索引。之所以要删除索引，是因为这些索引会降低表的更新速度，影响数据库的性能。

在MySQL中删除索引通过SQL语句DROP INDEX来实现，其语法形式如下：

DROP INDEX indexname ON tablename;

七、索引的设计原则

1.选择唯一性索引

唯一性索引的值是唯一的，可以更快速地通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段，为该字段建立唯一性索引可以很快确定某个学生的信息，如果使用姓名的话，可能存在同名现象，从而降低查询速度

2.为经常需要排序、分组和联合操作的字段建议索引

经常需要使用ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间，如果为其建立索引，可以有效地避免排序操作。

3.为经常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度，为这样的字段建立索引可以提高整个表的查询速度。

4.限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大，修改表时，对索引的重构和更新很麻烦。

5.尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间多。

6.尽量使用前缀来索引

如果索引的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间，如果只检索字段前面的若干字符，这样可以提高检索速度。

7.删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

总结

✨✨✨以上就是我学习的总结，希望大家一起探讨、探讨，一起加油，迈向更好的明天！！！

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。

THE END

mysql 数据库

二维码

强推！30个遥感数据下载网站整理分享

< <上一篇

explain都不懂？搞什么数据库优化，快进来学习了

下一篇>>

Mysql表索引(总结篇)

前言

一、索引的概述

1.索引类型

2.索引存储

3.索引优缺点

4.使用建议

5.创建和查看索引

二、普通索引

1.创建表时定义索引

2.已存在的表上创建索引

(1).指向create语句

(2).指向 alter table 语句

3.查看索引执行情况

三、唯一索引

1.创建表定义索引

2.已存在的表上创建索引

(1).create命令创建

(2).alter命令创建

四、全文索引

1.创建表时定义索引

2.已存在的表上创建索引

(1).执行create语句

(2).执行 alter table 语句

3.使用场景

4.MySQL8 中文分词支持

五、多列索引

1.创建表时定义索引

2.已存在的表上创建索引

(1).执行create语句

(2).执行 alter table 语句

六、隐藏、删除和修改索引

1.隐藏索引

2.删除索引

七、索引的设计原则

1.选择唯一性索引

2.为经常需要排序、分组和联合操作的字段建议索引

3.为经常作为查询条件的字段建立索引

4.限制索引的数目

5.尽量使用数据量少的索引

6.尽量使用前缀来索引

7.删除不再使用或者很少使用的索引

总结

最新文章

分类

标签云