当前位置 博文首页 > 韩超的博客 (hanchao5272)：一篇文章掌握MySql索引的语法、分类

最大化 缩小

    韩超的博客 (hanchao5272)：一篇文章掌握MySql索引的语法、分类

    作者：[db:作者] 时间：2021-09-05 16:11

    1.简介

    1.1.什么是索引

    • 索引是对记录集的多个字段进行排序的方法。
    • 在一张表中为一个字段创建一个索引，将创建另外一个数据结构，包含字段数值以及指向相关记录的指针。
    • 数据库的索引，可以理解为字典的目录，能够帮助我们快速找到需要查询的字。

    1.2.为什么需要索引

    使用索引的目的就是为了提高查询效率。

    假定表中存在5,000,000条记录，共需要1,000,000个磁盘块，当前查找字段为身份证号码，为无序的。

    • 如果不使用索引，因为其无序性，则进行线性查找，即：全表扫描，共计扫描1,000,000个磁盘块。
    • 如果使用索引，因为索引的有序性，可使用二分查找，即：共计扫描log2 (1000000) = 19.93 = 20个数据块。

    实际上，索引使用的B+Tree，其查询算法比二分查找更加高效。

    2.优缺点

    2.1.优点

    • 加快速度：检索、分组、排序等等。

    2.2.缺点

    • 以空间换时间，索引本身也是一种数据结构，故而会占用额外的存储空间，使用者应考虑磁盘空间是否足够。
    • 创建索引时，会对整个数据表加锁，使用者应该考虑加锁对业务造成的影响。
    • 当对数据表本身进行增删改时，也需要相应的对索引树进行操作，故而会降低数据表本身的增删改效率。

    3.分类

    3.1.主键索引(PRIMARY KEY)

    • 主索引。
    • 根据表主键建立的索引。
    • 不允许为空，不允许重复。

    3.2.唯一索引(UNIQUE INDEX)

    • 为了确保某列在表中的值唯一而建立的索引。
    • 允许为空，不允许重复。

    3.3.普通索引(INDEX)

    • 普普通通。
    • 允许为空，允许重复。

    3.4.组合索引(INDEX)

    • 多个列组合而成的索引。
    • 遵循最左前缀原则，依照字段的检索频率的高低排列字段。
    • 组合索引有多重组合方式。例如有索引ci_index(col1,col2,col3)，则可用索引列组合为：col1,col2,col3，col1,col2，col1。
    • 组合索引会导致索引键值边长，可以选择性的只对字段的前几个字符生成索引，如：ci_index(col1(5),col2,col3(5))。
    • 允许为空，允许重复。

    3.5.全文索引(FULLTEXT INDEX)

    • 为了将列用于全文检索而在此列创建的索引。
    • 通常用于text类型。
    • 不同于模糊查询，全文检索有自己的语法，后续会说明。
    • 允许为空，允许重复。

    3.语法

    3.1.建表时创建索引

    下面的创建语句包含了的五种索引：

    • 主键索引：PRIMARY KEY (id)
    • 唯一索引：UNIQUE INDEX ui_isbn (ISBN)
    • 普通索引：INDEX i_author(author), INDEX i_name(name), INDEX i_publisher(publisher), INDEX i_page(page)
    • 组合索引：INDEX ci_author_name(name,author)
    • 全文索引：FULLTEXT INDEX fti_summary(summary)
    CREATE TABLE `book` (
	`id` bigint NOT NULL AUTO_INCREMENT COMMENT '图书ID',
	`name` varchar(50) NOT NULL COMMENT '书名',
	`author` varchar(20) NOT NULL COMMENT '作者',
	`publisher` varchar(50) NOT NULL COMMENT '出版社',
	`ISBN` varchar(16) NOT NULL COMMENT 'ISBN编码',
	`summary` text NULL COMMENT '简介',
	`page` int NOT NULL DEFAULT '1' COMMENT '页数',
	PRIMARY KEY (`id`),
	UNIQUE INDEX ui_isbn (`ISBN`),
	INDEX i_author(`author`),
	INDEX i_name(`name`),
	INDEX i_publisher(`publisher`),
	INDEX i_page(`page`),
	INDEX ci_author_name(`name`,`author`),
	FULLTEXT INDEX fti_summary(`summary`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARSET = utf8mb4 COMMENT '图书表';

    3.2.建表后追加索引

    CREATE TABLE `book` (
	`id` bigint NOT NULL COMMENT '图书ID',
	`name` varchar(50) NOT NULL COMMENT '书名',
	`author` varchar(20) NOT NULL COMMENT '作者',
	`publisher` varchar(50) NOT NULL COMMENT '出版社',
	`ISBN` varchar(16) NOT NULL COMMENT 'ISBN编码',
	`summary` text NULL COMMENT '简介',
	`page` int NOT NULL DEFAULT '1' COMMENT '页数'
) ENGINE = InnoDB  CHARSET = utf8mb4 COMMENT '图书表';

-- 主键索引
alter table `book` add PRIMARY KEY (`id`);
-- 唯一索引
alter table `book` add UNIQUE INDEX ui_isbn (`ISBN`);
-- 普通索引
alter table `book` add (
	INDEX i_author(`author`),
	INDEX i_name(`name`),
	INDEX i_publisher(`publisher`),
	INDEX i_page(`page`)
);
-- 组合索引
alter table `book` add INDEX ci_author_name(`name`,`author`);
-- 全文索引
alter table `book` add FULLTEXT INDEX fti_summary(`summary`);

    3.3.查看索引

    mysql> show index from `book`;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book  |          0 | PRIMARY        |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          0 | ui_isbn        |            1 | ISBN        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | i_author       |            1 | author      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | i_name         |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | i_publisher    |            1 | publisher   | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | i_page         |            1 | page        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | ci_author_name |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | ci_author_name |            2 | author      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | fti_summary    |            1 | summary     | NULL      |           0 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
9 rows in set (0.00 sec)

    3.5.查看索引是否被使用

    explain {Sql Statement}

    3.4.使用索引

    此处只例举几种类型，关于索引的使用注意事项，后面的章节会细说。

    -- 普通查询 key = ui_isbn
mysql> explain select name,author from book where ISBN = 'ISNB00001';
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | book  | const | ui_isbn       | ui_isbn | 66      | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.01 sec)

-- 模糊查询 key = i_name
mysql> explain select name,author from book where name like '张%' limit 1;
+----+-------------+-------+-------+-----------------------+--------+---------+------+------+-----------------------+
| id | select_type | table | type
    上一篇：韩超的博客 (hanchao5272)：MySqL: 并发量大、数据量大的互联网
    下一篇：没有了