希望你开心，希望你健康，希望你幸福，希望你点赞！
最后的最后，关注喵，关注喵，关注喵，佬佬会看到更多有趣的博客哦！！！
喵喵喵，你对我真的很重要！
目录
前言
建立和管理索引
认识索引
索引分类
索引的设计原则
创建表时创建
用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子句进行删除，不需要提供索引名称，因为一个表中只有一个主键。
总结
人生苦短一天当作两天卷~
希望你开心，希望你健康，希望你幸福，希望你点赞！
最后的最后，关注喵，关注喵，关注喵，佬佬会看到更多有趣的博客哦！！！
喵喵喵，你对我真的很重要！
精彩内容
您阅读本篇文章共花了：
发表评论