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

  

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

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

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

目录

前言

建立和管理索引

认识索引

索引分类

索引的设计原则

创建表时创建

用CREATE INDEX语句创建

创建普通索引

创建唯一性索引

创建多列索引

注

通过ALTER TABLE语句创建索引

通过ALTER TABLE语句创建索引

索引的查看

索引的删除

索引的删除

总结

---

前言

来了来了，回来了~

本任务将从认识索引、索引的分类以及索引的设计原则等方面着手，介绍创建和管理索引的方法。 索引并不是越多越好，要正确认识索引的重要性和设计原则，创建合适的索引。

---

建立和管理索引

认识索引

索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。 在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子句进行删除，不需要提供索引名称，因为一个表中只有一个主键。 

---

总结

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

---

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

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

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