MySQL学习总结（索引的概述、索引的创建、索引的查看、索引的删除）

目录

一、索引概述

1、普通索引

2、唯一性索引

3、主键索引

4、全文索引

5、空间索引

二、索引的创建

1、创建数据表的同时创建索引

（1）创建单列索引

（2）创建复合索引

2、在已有的数据表上创建索引

 （1）创建单列索引

（2）创建复合索引

3、修改数据表的同时创建索引

（1）创建单列索引

（2）创建复合索引

三、索引的查看

      示例：查看数据表dept_index 中的索引

       示例：使用EXPLAIN 关键字查看语句的执行情况

四、索引的删除

1、使用ALTER TABLE 删除索引

2、使用DROP INDEX 删除索引

---

      学习MySQL的索引的概述、索引的创建、索引的查看、索引的删除

一、索引概述

1、普通索引

        普通索引是MySQL中的基本索引类型，使用KEY或INDEX定义，不需要添加任何限制条件。

2、唯一性索引

       创建唯一性索引的字段允许有NULL值，但需要保证索引对应字段中的值是唯一的。例如，在员工表emp的ename字段上建立唯一性索引，那么ename字段的值必须是唯一的。

3、主键索引

        主键索引是一种特殊的唯一性索引，用于根据主键自身的唯一性标识每一条记录。主键索引的字段不允许有NULL值。

4、全文索引

        全文索引主要用于提高在数据量较大的字段中的查询效率。全文索引和SQL中的LIKE模糊查询类似，不同的是LIKE模糊查询适合用于在内容较少的文本中进行模糊匹配，全文检索更擅长在大量的文本中进行数据检索。全文索引只能创建在CHAR 、VARCHAR或TEXT类型的字段上。

5、空间索引

       空间索引只能创建在空间数据类型的字段上，其中空间数据类型存储的空间数据是指含有位置、大小、形状以及自身分布特征等多方面信息的数据。MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING 和 POLYGON。需要注意的是，对于创建空间索引的字段，必须将其声明为NOT NULL。
       上述5种索引可以在一列或多列字段上进行创建。根据创建索引的字段个数，可以将索引分为单列索引和复合索引，具体介绍如下。

• 单列索引。单列索引指的是在表中单个字段上创建索引，它可以是普通索引、唯一性索引或全文索引，只要保证该索引只对应表中一个字段即可。
• 复合索引。复合索引指的是在表中多个字段上创建一个索引，并且只有在查询条件中使用了这些字段中的第一个字段时，该索引才会被使用。例如，在员工表emp的ename和deptno字段上创建一个复合索引，那么只有查询条件中使用了ename字段时，该索引才会被使用。

需要注意的是，虽然索引可以提高数据的查询速度，但它会占用一定的磁盘空间，并且在创建和维护索引时，其消耗的时间是随数据量的增加而增加。因此、使用索引时，应该综合考虑其优点和缺点。

二、索引的创建

1、创建数据表的同时创建索引

基本语法格式如下

CREATE TABLE 表名(字段名1 数据类型[完整性约束条件],
                ...
     {INDEX | KEY} [索引名] [索引类型] (字段列表)
        | UNIQUE [INDEX | KEY] [索引名] [索引类型] (字段列表)
        | PRIMARY KEY [索引类型] (字段列表)
        | {FULLTEXT | SPATIAL} [INDEX | KEY] [索引名] (字段列表)
               ...
                );

上述语法格式中个选项的含义如下。

• { INDEX | KEY }：INDEX和KEY为同义词，表示索引，二者选一即可。.
• 索引名：可选项，表示为创建的索引定义的名称。不使用该选项时，默认使用建立索引的字段表示，复合索引则使用第一个字段的名称作为索引名称。
• 索引类型：可选项，某些存储引擎允许在创建索引时指定索引类型，使用的语法是USING { BTREE | HASH}。不同的存储引擎支持的索引类型也不同，例如存储引擎InnoDB和MyISAM支持BTREE，而MEMORY则同时支持BTREE和HASH。
• UNIQUE：可选项，表示唯一性索引。
• FULLTEXT：表示全文索引。
• SPATIAL：表示空间索引。

       创建索引时，如果字段列表中为单个字段，则设定的索引为单列索引；如果字段列表中为多个字段，则同时在多个字段上创建一个索引，即创建复合索引。

下面根据CREATE TABLE语句的基本语法格式分别演示单列索引和复合索引的创建

（1）创建单列索引

       示例：演示如何在创建数据表dept_index时创建单列的普通索引、唯一性索引、主键索引、全文索引和空间索引

         通过 （SHOW CREATE TABLE 数据表名 ; ）语句显示创建数据表的语句。

（2）创建复合索引

       示例：创建数据表index_multi ，在数据表中的 id 和 dname 字段上建立索引名为 multi 的普通索引

                 通过 （SHOW CREATE TABLE 数据表名 ; ）语句显示创建数据表的语句。

       需要注意的是，在复合索引中，多个字段的设置顺序要遵守“ 最左前缀原则 ”；也就是在创建索引时，把使用最频繁的字段放在索引字段列表的最左边，使用次频繁的字段放在索引字段列表的第二位，以此类推。

2、在已有的数据表上创建索引

         若想在一个已经存在的数据表上创建索引，可以使用 CREATE INDEX 语句。

       CREATE INDEX 语句创建索引的具体语法格式如下

CREATE [ UNIQUE | FULLTEXT | SPAYIAL] INDEX 索引名
       [索引类型] ON 数据表名 (字段列表) ;

        在上述语法格式中，UNIQUE 、FULLTEXT 和 SPATIAL 都是可选参数，分别用于表示唯一性索引、全文索引和空间索引。

      先创建一个新数据表dept_index02 

 （1）创建单列索引

       通过CREATE INDEX语句可以创建普通索引、唯一性索引、全文索引和空间索引。由于创建索引的格式都一样。

       示例：在数据表dept_index02中的id字段上建立一个名称为unique_id的唯一性索引

        通过 （SHOW CREATE TABLE 数据表名 ; ）语句显示创建数据表的语句。

（2）创建复合索引

       使用 CREATE INDEX 语句创建复合索引

      示例：在dept_index02中的depyno字段上创建名称为multi_index的复合索引

         通过 （SHOW CREATE TABLE 数据表名 ; ）语句显示创建数据表的语句。

3、修改数据表的同时创建索引

       要在已经存在的数据表中创建索引，除可以使用CREATE INDEX 语句外，还可以使用ALTER TABLE 语句可在修改数据表的同时创建索引

基本语法格式

ALTER TABLE 数据表名
      ADD { INDEX | KEY } [索引类型] (字段列表)
    | ADD UNIQUE [ INDEX | KEY ] [索引名] [索引类型] (字段列表)
    | ADD PRIMARY KEY [索引类型] (字段列表)
    | ADD { FULLTEXT | SPATIAL } [ INDEX | KEY ] [索引名] (字段列表)

       先创建一个数据表

（1）创建单列索引

              使用 ALTER TABLE 语句创建单列索引

       示例：在数据表 dept_index03 中的 id 字段创建名称为 index_id 的唯一性索引

              通过 （SHOW CREATE TABLE 数据表名 ; ）语句显示创建数据表的语句。

（2）创建复合索引

      上面使用ALTER TABLE语句创建的普通索引、唯一性索引、全文索引和空间索引都是对数据表中的单列字段设定的索引。

       使用ALTER TABLE语句演示复合索引的创建

      示例：在dept_index03表中的deptno字段和dname字段上创建一个名称为multi_index的复合唯一性索引

       通过 （SHOW CREATE TABLE 数据表名 ; ）语句显示创建数据表的语句。

三、索引的查看

       如果需要查看数据表中已经创建的索引的信息，除使用SHOW CREATE TABLE 语句在数据表的创建语句中查看外，还可以通过如下语法格式的语句进行查看。

SHOW { INDEXES | INDEX | KEYS } FROM 数据表名 ;

      上述语法格式中，使用INDEXES 、INDEX 、KEYS 含义都一样，都可以查询出数据表中所有的索引信息。

      示例：查看数据表dept_index 中的索引

        通过 （SHOW CREATE TABLE 数据表名 ; ）语句显示创建数据表的语句。

索引信息字段的含义

字段名	描述的含义
Table	索引所在的数据表的名称
Non_unique	索引是否可以重复，0表示不可以，1表示可以
Key_name	索引的名称，如果索引是主键索引，则它的名称为PRIMARY
Seq_in_index	建立索引的字段序号值，默认从1开始
Column_name	建立索引的字段
Collation	索引字段是否排序，A表示有排序，NULL表示没有排序
Cardinality	MySQL连接时使用索引的可能性（精确度不高），值越大可能性越高
Sub_part	前缀索引的长度，如字段值都被索引，则 Sub_part 为NULL
Packed	关键词如何被压缩，如果没有被压缩，则为NULL
Null	索引字段是否含有NULL值，YES表示含有，NO表示不含有
Index_type	索引方式，可选值有FULLTEXT 、HASH 、BTREE 、RTREE
Comment	索引字段的注释信息
Index_comment	创建索引时添加的注释信息
Visible	索引对查询优化器是否可见。YES表示可见，NO表示不可见
Expression	使用什么表达作为建立索引的字段，NULL表示没有

       在MySQL中除了可以查看数据表中的索引信息，还可以通过EXPLAIN 关键字分析SQL语句的执行情况，例如分析SQL语句执行时是否使用了索引。EXPLAIN 可以分析的语句有 SELECT 、UPDATE 、DELETE 、INSERT 和 REPLACE。

       先在数据表dept_index中插入数据

（打开此表的设计表，将address这一行信息删掉即可插入数据）

       示例：使用EXPLAIN 关键字查看语句的执行情况

EXPLAIN SELECT 字段名 FROM 表名 ;

EXPLAIN SELECT 字段名 FROM 表名 WHERE 条件 ;

分析执行语句的字段

字段名	描述
id	查询标识符，默认从1开始，如果使用了联合查询，则该值依次递增
select_type	查询类型，它的值包含多种，如SIMPLE表示简单SELECT，不使用UNION或子查询
table	输出行所引用的数据表的名称
partition	匹配的分区
type	连接的类型，它的值有多种，如 ref 表示使用前缀索引或条件中含有运算符“ = ” 或 “ <=> ” 等
key_len	索引字段的长度
ref	表示哪些字段或常量与索引进行了比较
rows	预计需要检索的记录数
filtered	按条件过滤的百分比
Extra	附件信息，如Using index 表示使用了索引覆盖

四、索引的删除

1、使用ALTER TABLE 删除索引

       基本的语法格式

ALTER TABLE 表名
{ DROP { INDEX | KEY } 索引名
| DROP PRIMARY KEY };

      示例：删除数据表dept_index中名称为 introduction 的全文索引。

         通过 （SHOW CREATE TABLE 数据表名 ; ）语句显示创建数据表的语句。

2、使用DROP INDEX 删除索引

       基本的语法格式

DROP INDEX 索引名 ON 数据表名 ;

      示例：删除数据表dept_index 中名称为dname的索引

          通过 （SHOW CREATE TABLE 数据表名 ; ）语句显示创建数据表的语句。