0


[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_namecol_name** [(length)] [ASC|DESC]**


通过ALTER TABLE语句创建索引

teachers表上建立t_no主键索引(假说还未建立主键),建立t_named_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;**

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

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

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


总结

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


希望你开心,希望你健康,希望你幸福,希望你点赞!

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

喵喵喵,你对我真的很重要!


本文转载自: https://blog.csdn.net/ormstq/article/details/136972913
版权归原作者 猫佛佛 所有, 如有侵权,请联系我们删除。

“[MySQL]数据库原理6——喵喵期末不挂科”的评论:

还没有评论