MySQL数据库学习（6）

一、MySQL索引简介
索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。
通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。不然的话，数据库系统将读取每条记录的所有信息进行匹配。
因此，使用索引可以很大程度上提高数据库的查询速度，还有效的提高了数据库系统的性能。
索引是 MySQL中十分重要的数据库对象，是数据库性能调优技术的基础，常用于实现数据的快速检索。
二、创建索引
1.方法1:
可以使用专门用于创建索引的 CREATE INDEX语句在一个已有的表上创建索引，但该语句不能创建主键。
语法格式：CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])
<索引名>：指定索引名。一个表可以创建多个索引，但每个索引在该表中的名称是唯一的。
<表名>：指定要创建索引的表名。
<列名>：指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERE 子句里经常出现的列作为索引列。
<长度>：可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。
ASC|DESC：可选项。ASC指定索引按照升序来排列，DESC指定索引按照降序来排列，默认为ASC。

2.方法2:
索引也可以在创建表（CREATE TABLE）的同时创建
(1)CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…)
在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的主键。
(2)语法格式：KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…)
在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的索引。
(3)语法格式：UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)
在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的唯一性索引。
(4)语法格式：FOREIGN KEY <索引名> <列名>
在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的外键。

3.方法3:
ALTER TABLE语句可以在一个已有的表上创建索引。在使用 ALTER TABLE语句修改表的同时，可以向已有的表添加索引。
(1)语法格式：ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)
在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加索引。
(2)语法格式：ADD PRIMARY KEY [<索引类型>] (<列名>,…)
在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加主键。
(3)语法格式：ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)
在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加唯一性索引。
(4)语法格式：ADD FOREIGN KEY [<索引名>] (<列名>,…)
在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加外键。

三、查询索引
索引创建完成后，可以利用 SQL 语句查看已经存在的索引。在 MySQL中，可以使用 SHOW INDEX语句查看表中创建的索引。
语法格式：SHOW INDEX FROM <表名> [ FROM <数据库名>]
<表名>：指定需要查看索引的数据表名。
<数据库名>：指定需要查看索引的数据表所在的数据库，可省略。比如，SHOW INDEX FROM student FROM test; 语句表示查看 test 数据库中 student 数据表的索引。
其使用在前面将创建索引的地方都有使用到，这里就不赘述了。
下面是索引信息里各项参数的说明:
Table                    表示创建索引的数据表名
Non_unique         表示该索引是否是唯一索引。若不是唯一索引，则该列的值为 1；若是唯一索引，则该列的值为 0。
Key_name         表示索引的名称。
Seq_in_index  表示该列在索引中的位置，如果索引是单列的，则该列的值为 1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序。
Column_name          表示定义索引的列字段。
Collation          表示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类。
Cardinality           索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。
Sub_part           表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为 NULL。
Packed                      指示关键字如何被压缩。若没有被压缩，值为 NULL。
Null                      用于显示索引列中是否包含 NULL。若列含有 NULL，该列的值为 YES。若没有，则该列的值为 NO。
Index_type           显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。
Comment                      显示评注。
Index_comment  显示使用COMMENT属性创建索引时指定的索引的注释。
Visible        索引是否对查询优化器可见或不可见; YES如果是，NO如果不是。
Expression     如果索引使用表达式而不是列或列前缀值，则表达式指示键部分的表达式，并且column_name列也为NULL。
四、删除索引
删除索引是指将表中已经存在的索引删除掉。不用的索引建议进行删除，因为它们会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。
在 MySQL 中修改索引可以通过删除原索引，再根据需要创建一个同名的索引，从而实现修改索引的操作。
方法1：使用 DROP INDEX 语句
语法格式：DROP INDEX <索引名> ON <表名>
<索引名>：要删除的索引名。
<表名>：指定该索引所在的表名。
方法2：使用 ALTER TABLE 语句
根据 ALTER TABLE语句的语法可知，该语句也可以用于删除索引。具体使用方法是将 ALTER TABLE 语句的语法中部分指定为以下子句中的某一项。
DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。
DROP INDEX index_name：表示删除名称为 index_name 的索引。
DROP FOREIGN KEY fk_symbol：表示删除外键。

五、索引分类
1索引的分类有种，这里主要先说说按字段类型分类。
(1).普通索引
普通索引是MySQL数据库中的一种普通的索引，添加普通索引的列对数据没有特殊要求，普通索引能起到的作用就是加快差查询速度。
添加普通索引后，在使用desc查看表结构时，会发现Key列上出现MUL，这就表示该列添加有普通索引。

(2).唯一索引
唯一性索引，是在普通索引的基础上，要求添加该索引的列所有的值只能出现一次。唯一性索引常用语添加在注入身份证号、学号等字段中，不可以添加在注入名字、等字段中。
唯一性索引的添加与普通索引几乎完全相同，只不过要把普通索引的关键字key和index换成unique key和unique index。
添加唯一性索引的字段，在使用desc命令查询表结构时，Key列中会显示UNI，表示该字段添加了唯一性索引。

(3).主键索引
主键索引，是数据库的所有索引中查询速度最快的，并且每个数据表只能有1个主键索引列。主键索引的列，不允许出现重复的数据，也不允许为空值。

(4).复合索引
如果想要创建一个包含不同的列的索引，我们就可以创建符合索引。其实，复合索引在业务场景中应用的非常频繁，比如，如果我们想要记录数据包的内容，则需要将IP和端口号作为标识数据包的依据，这时就可以把IP地址的列和端口号的列创建为复合索引
注意:
1).在使用desc查看数据表结构时，会在Key列中发现多个PRI，这就表示这些含有PRI的列就是复合索引的列了
2).复合索引相当于一个多列的主键索引，因此，添加复合索引的任何一个列都不允许数据为空，并且这些列不允许数据完全相同，否则MySQL数据库会报错。

(5).全文索引
全文索引主要是用于解决大数据量的情况下模糊匹配的问题。如果数据库中某个字段的数据量非常大，那么如果我们想要使用like+通配符的方式进行查找，速度就会变得非常慢。针对这种情况，我们就可以使用全文索引的方式，来加快模糊查询的速度。全文索引的原理便是通过分词技术，分析处文本中关键字及其出现的频率，并依次建立索引。全文索引的使用，与数据库版本、数据表引擎乃至字段类型息息相关，主要限制如下：
1).MySQL3.2版本以后才支持全文索引。
2).MySQL5.7版本以后MySQL才内置ngram插件，全文索引才开始支持中文。
3).MySQL5.6之前的版本，只有MyISAM引擎才支持全文索引。
4).MySQL5.6以后的版本，MyISAM引擎和InnoDB引擎都支持全文索引。
5).只有字段数据类型为char、varchar、以及text的字段才支持添加全文索引。
在创建了全文索引后，也不能够使用like+通配符的方式进行模糊查询，全文索引的使用有其特定的语法，如下所示：
select * from <表名> where match(索引字段) against (匹配内容);
其中，match后面的括号里是含有全文索引的字段，against后面的括号里是要模糊匹配的内容。
此外，全文索引的作用并不是唯一的，在很多场景下，我们并不会使用MySQL数据库内置的全文索引，而是使用第三方类似的索引以实现相同的功能。

六、索引的优缺点:
优点:
1)通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
2)可以给所有的 MySQL列类型设置索引。
3)可以大大加快数据的查询速度，这是使用索引最主要的原因。
4)在实现数据的参考完整性方面可以加速表与表之间的连接。
5)在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间
缺点:
1)创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
2)索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
3)当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。
七、索引的使用原则:
1)索引是典型的“以空间换时间”的策略，它会消耗计算机存储空间，但是会加快查询速度。
2)索引的添加，尽管加快了在查询时的查询速度，但是会减慢在插入、删除时的速度。因为在插入、删除数据时需要进行额外的索引操作。
3)索引并非越多越好，数据量不大时不需要添加索引。
4)如果一个表的值需要频繁的插入和修改，则不适合建立索引，反制，如果一个表中某个字段的值要经常进行查询、排序和分组的字段则需要建立索引。
5)如果一个字段满足建立唯一性索引的条件，就不要建立普通索引。