MySQL-07-索引

7.1 什么是索引？

  • 一句话概括，索引的作用就是书的目录。
    • 比如说新华字典，你要查找一个字，可以按照拼音音节进行查找。如果说你不会读，那么可以通过偏旁部首来查。万一这个字你看不出来是什么部首，那么可以尝试使用生僻字进行查询。数据库中的索引起到的也是同样的作用。
  • 在数据量较少的时候，使用索引对查找数据的速度并没有特别的提升。但在数据量极大的时候，使用索引就可以大大提升性能。
  • 做个对比，大家都知道算法中的暴力法。
    • 暴力法在针对一些规模较小的算法问题时，由于其容易实现，逻辑简单，往往不失为一种优秀的解决方案。但如果问题规模变大的话，暴力算法虽然能解决，但往往耗时就比较久。为此，我们研究出了各种各样的算法，用来提高性能。索引的目的也是如此，用于提高数据库检索数据的性能

7.2 索引的缺陷

  • 既然索引这么有用，那是不是索引越多越好呢？显然不是
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
  • 此外，索引还会占据额外的存储空间

7.3 索引准则

  • 适合创建索引的列
    • 原则：经常被搜索的列
    • 经常作为where子句中条件的列（作为条件，也经常要被搜索）
  • 不适合创建索引的列或表
    • 表
      • 如果一张表经常被修改，那么这张表的索引越少越好，因为索引会降低更新效率
      • 一张表的数据量比较少的时候，建立索引对于性能的提升微乎其微
    • 列
      • 对于数据值很少的列，比如用于记录性别信息的列，非男即女。
      • 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
      • 对于那些在查询中很少使用或者参考的列不应该创建索引。

7.4 索引的种类

  • 主键索引（Primary Key）
    • 最常见的索引
    • 确保数据的唯一性，能唯一标识一条数据
    • 确定特点数据记录的位置
  • 唯一索引（Unique）
    • 避免同一列中的数据重复
    • 主键索引只能有一个，但是唯一索引可以有多个
  • 常规索引（Key/Index）
    • 最基本的索引，没有任何限制
  • 全文索引（Fulltext）
    • MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
    • MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
    • 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
    • 全文索引用于在较多的数据中匹配某些特定的内容，例如，在文章中查找某些词语的位置

7.5 语法

  1. 创建索引

  • 在建表语句中声明索引

    CREATE TABLE 表名 (
	字段名1 数据类型 [完整性约束条件…],
	字段名2 数据类型 [完整性约束条件…],
	[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
	[索引名] (字段名[(长度)] [ASC |DESC])
);

  • 使用create创建索引

    CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]);

  • 使用alter创建索引

    ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]);

  1. 删除索引

    -- 删除索引
DROP INDEX 索引名 ON 表名字;

-- 删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;

  2. 其他操作

    -- 查看索引信息
SHOW INDEX FROM 表名;

7.6 扩展知识

posted @ 2020-10-22 15:59  PrimaBruceXu  阅读(98)  评论(0)  编辑  收藏  举报