[MySQL]数据库原理6——喵喵期末不挂科

希望你开心，希望你健康，希望你幸福，希望你点赞！

最后的最后，关注喵，关注喵，关注喵，佬佬会看到更多有趣的博客哦！！！

喵喵喵，你对我真的很重要！

前言

来了来了，回来了~

本任务将从认识索引、索引的分类以及索引的设计原则等方面着手，介绍创建和管理索引的方法。

索引并不是越多越好，要正确认识索引的重要性和设计原则，创建合适的索引。

建立和管理索引

认识索引

索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。

在MySQL中，所有的数据类型都可以被索引。

MySQL支持的索引主要有Hash索引和B-Tree索引。但大部分都是以B-树（BTREE）方式存储，B-Tree索引的存储结构在数据库的数据检索中有着非常优异的表现。MySQL Hash索引相对于B-Tree索引，检索效率要高上不少。

（1）MYISAM里所有键的长度仅支持1000字节，INNODB是767。

（2）BLOB和TEXT字段仅支持前缀索引。

（3）使用“!=”以及“<>”的时候，MySQL不使用索引。

（4）当字段使用函数的时候，MySQL无法使用索引；在连接条件字段类型不一致的时候，MySQL无法使用索引；在组合索引里使用非第一个索引时也不使用索引。

（5）在使用LIKE的时候，以“%”开头，即“%***”的时候无法使用索引；在使用OR的时候，要求or前后字段都有索引。

（6）索引是一个简单的表，MySQL将一个表的索引都保存在同一个索引文件中，所以索引也是要占用物理空间的。如果有大量的索引，索引文件可能会比数据文件更快地达到最大的文件尺寸。

（7）在更新表中索引列上的数据时，MySQL会自动地更新索引，索引树总是和表的内容保持一致。这样就降低了添加、删除、修改和其他写入操作的效率。表中的索引越多，则更新表的时间就越长。

（8）如果从表中删除了列，则索引可能会受到影响。如果所删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

索引分类

（1）普通索引（INDEX）

索引的关键字是INDEX，这是最基本的索引，它没有任何关键字限制。

（2）唯一性索引（UNIQUE）

索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。在一个表上可以创建多个UNIQUE索引。

（3）主键索引（PRIMARY KEY）

一种特殊的唯一索引，不允许有空值。一般在建表时同时，一个表只能有一个主键索引。

（4）全文索引（FULLTEXT）

只能对CHAR、VARCHAR和TEXT类型的列编制索引，并且只能在MyISAM表中编制。全文索引是一种特殊类类型索引，它查找的是文本中的关键词，而不是直接比较索引中的值。在MySQL默认情况下，对于中文作用不大。

（5）空间索引（SPATIAL）

只能对空间列编制索引，并且只能在MyISAM表中编制。本书不讨论。

注：按索引建立在一列还是多列上，又可以分为单列索引、多列索引（复合索引）

索引的设计原则

（1）在主键上创建索引，在InnoDB中如果通过主键来访问数据效率是非常高的。

（2）为经常需要排序、分组和联合操作的字段建立索引，即那些将用于JOIN、 GROUP BY分组和ORDER BY排序的字段上。

（3）为经常作为查询条件的字段建立索引，如用于JOIN、 WHERE判断的字段。

（4）尽量不要对数据库中某个含有大量重复的值的字段建立索引，如“性别”字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。

（5）限制索引的数目。

（6）尽量使用数据量少的索引。

（7）尽量使用前缀来索引。

（8）删除不再使用或者很少使用的索引

创建表时创建

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
          [ ( [column_definition] , ... | [index_definition] ) ]
          [table_option] [select_statement];
其中，index_definition为索引项。
     [CONSTRAINT [symbol]]PRIMARY KEY [index_type] (index_col_name,...)    
    |{INDEX | KEY} [index_name] [index_type] (index_col_name,...)    
    | [CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] [index_type] (index_col_name,...)
     | [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...) 
    | [CONSTRAINT [symbol]] FOREIGN KEY  [index_name] (index_col_name,...) [reference_definition]
        注：index_col_name:  col_name [(length)] [ASC | DESC]

举个例子

//创建student2表， s_no为主键索引，s_name为唯一性索引，并在adress列上前5位字符创建索引。
mysql> CREATE TABLE IF NOT EXISTS student2 
        (s_no char(4) NOT NULL COMMENT '学号',
        s_name char(4) DEFAULT NULL COMMENT '姓名',
        sex char(2) DEFAULT '男' COMMENT '性别',
        birthday date DEFAULT NULL COMMENT '出生日期',
        d_no char(4) DEFAULT NULL COMMENT '所在系部', 
        address varchar(20) DEFAULT NULL COMMENT '家庭地址',
        phone varchar(12) DEFAULT NULL COMMENT '联系电话',
        photo blob COMMENT '照片', 
        PRIMARY KEY (s_no),
        UNIQUE index name_index(s_name),
        INDEX ad_index(address(5)))
        ENGINE=InnoDB   DEFAULT   CHARSET=gb2312;

用CREATE INDEX语句创建

如果表已建好，可以使用CREATE INDEX语句建立索引。 基本形式如下。

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name (index_col_name,...)

其中，index_col_name的格式如下。
col_name [(length)] [ASC | DESC]

创建普通索引

为便于按地址进行查询，为students表的address列上的前6个字符建立一个升序索引adress_index。

mysql> CREATE INDEX adress_index

** ON students(address(6) ASC);**

为经常作为查询条件的字段建立索引。例如，STUDENTS的D_NO字段经常作为查询条件，建立普通索引。

** mysql> CREATE INDEX D_N0_index**

** ON students(D_NO);**

创建唯一性索引

像学生的姓名，课程表的课程名，部门表的部门名，商品表的商品名之类的字段，一般情况下是，可建立一个唯一性索引。

在course表的c_name列上建立一个唯一性索引c_name_index。

mysql> CREATE UNIQUE index c_name_index

** ON course(c_name);**

在teachers表的t_name字段建立一个唯一性索引c_name_index。

mysql> CREATE UNIQUE index t_name_index

** ON teachers(t_name);**

创建多列索引

可以在一个索引的定义中包含多个列，中间用逗号隔开，但是它们要属于同一个表。这样的索引叫做复合索引。

** 在score表的s_no和c_no列上建立一个复合索引score_index。**

mysql> CREATE INDEX score_index

** ON score(s_no, c_no)；**

注

（1）对于CHAR和VARCHAR列，只用一列的一部分就可创建索引。创建索引时，使用col_name(length)语法，对前缀编制索引。前缀包括每列值的前length个字符。BLOB和TEXT列也可以编制索引，但是必须给出前缀长度。

（2）因为多数名称的前10个字符通常不同，所以前缀索引不会比使用列的全名创建的索引速度慢很多。另外，使用列的一部分创建索引可以使索引文件大大减小，从而节省了大量的磁盘空间，有可能提高INSERT操作的速度。

（3）CREATE INDEX语句并不能创建主键。

（4）索引名可以不写 ，若不写索引名，默认与列名相同。

（5）部分储存引擎允许在创建索引时指定索引类型，在索引名后面加上USING index_type。如果列有多个索引类型，当没有指定index_type时，第一个类型是默认值。

** 不同的储存引擎所支持的type_name值不同：**

** MyISAM：BTREE**

** InnoDB：BTREE**

** MEMORY/HEAP：HASH、BTREE**

通过ALTER TABLE语句创建索引

基本形式如下。

**ALTER TABLE **tbl_name

** ADD [PRIMARY KEY| UNIQUE | FULLTEXT | SPATIAL ] INDEX **

index_name（col_name** [(length)] [ASC|DESC]**）

通过ALTER TABLE语句创建索引

在teachers表上建立t_no主键索引（假说还未建立主键），建立t_name和d_no的复合索引，以加速表的检索速度。

mysql> ALTER TABLE teachers

** ADD PRIMARY KEY(t_no),**

** ADD INDEX mark(t_name, d_no);**

在departments表中的d_name创建唯一性索引。

mysql> ALTER TABLE departments

** ADD UNIQUE INDEX d_name_index(d_name);**

（1）主键索引必定是唯一的，唯一性索引不一定是主键。

（2）一张表上只能一个主键，但可以有一个或者多个唯一性索引。

索引的查看

*如果想要查看表中创建的索引的情况，可以使用**SHOW INDEX FROM tbl_name语句，例如：*

mysql> SHOW INDEX FROM COURSE;

mysql> SHOW INDEX FROM SCORE;

索引的删除

删除索引是指将表中已经存在的索引删除掉。一些不再使用的索引会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。

索引的删除

用DROP INDEX语句删除索引

语法格式：

** DROP INDEX index_name ON tbl_name ;**

index_name****为要删除的索引名

** tb1_name**为索引所在的表。

删除course表的mark索引。

**mysql****> DROP INDEX mark ON course;**

（1）DROP INDEX子句可以删除各种类型的索引。

（2）删除唯一性索引，如同删除普通索引一样，用DROP INDEX语句即可，不能写成DROP UNIQUE INDEX，但是创建唯一性索引要写成ADD UNIQUE INDEX。

（3）如要删除主键索引，则直接使用DROP PRIMARY KEY子句进行删除，不需要提供索引名称，因为一个表中只有一个主键。

总结

人生苦短一天当作两天卷~

希望你开心，希望你健康，希望你幸福，希望你点赞！

最后的最后，关注喵，关注喵，关注喵，佬佬会看到更多有趣的博客哦！！！

喵喵喵，你对我真的很重要！