mysql - 7.索引

最新推荐文章于 2021-02-02 08:18:05 发布

开发小头目

最新推荐文章于 2021-02-02 08:18:05 发布

阅读量235

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_33199919/article/details/89295412

版权

mysql 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

当数据量增加到一定程度：查询操作必然会变得缓慢，这时候建立一个索引，用索引表来空间换时间，是目前来说最常用的优化查询方式，下面介绍以下索引吧！！

索引是什么？解释的话，就相当于书的目录，我们看书想要看某一章节，就去翻看章节目录对应第几页，这就是一个通过索引查找内容的过程。

一.索引的定义

索引，就是帮助数据库快速定位的数据结构

二.索引的结构 - （Mysql）

学过数据结构都知道，计算机系统中涉及到数据保存/查找这对关系，都会用到一个树的概念。

学习这个索引也有2各阶段，知其然？知其所以然。Mysql 默认实现的方式 B-tree (技术上其实使用B+tree)

-------了解B-tree与B+tree的区别非常有必要，网上非常多，我就不再浪费篇幅了

三.索引的分类

注：注意空格，如-${tableName}(${字段名}) 中间没有空格

1.增加索引

普通索引	普通字段建立索引例：create index name_index on user(buyer_name);
CREATE INDEX ${ndexName} ON ${tableName}(${字段名}) 或者 ALTER TABLE ${tableName} ADD ${ndexName}（${字段名}）
唯一索引	建立索引之前的字段value不能有重复，不然报错；建立之后字段不能在插入重复字段，允许为空
CREATE UNIQUE INDEX ${UniqueIndexName} ON ${tableName}(${字段名}) 或者 ALTER TABLE ${tableName} ADD UNIQUE ${ndexName}（${字段名}）
主键索引	主键上建立索引(mysql ID 自带主键索引) ----主键只能作用在一个列上，默认不能为空
ALTER TABLE ${tableName} ADD PRIMARY KEY(${字段名}) ------没有create语法针对主键索引
组(复)合索引	多个字段一起创建索引，有集合概念,一个（1,2,3）包含了（1)（1,2）(1,3) （2,3)（1,2,3) 这么多子组合索引
复合索引就是对普通索引和唯一索引的一个横向扩展 ---- (${字段名}) ----> (${字段名1},${字段名2},${字段名3})
非聚簇/聚簇索引	聚簇，字面就是聚集的意思喽！将索引字段和对应的信息存放在一起，就叫聚簇；将索引字段和对应信息存放的地址放到一起就是非聚簇
存储引擎-Myisam创建的就是非聚簇索引，存储引擎-InnoDB默认会维护一张Id的聚簇索引表，具体介绍我看到一篇文章还不错引用下

那我最喜欢实践了喽，每个都来实践一遍找找感觉：

普通（复合）索引：

#普通索引
mysql> create index name_index on mylock(name);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from mylock;
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mylock |          0 | PRIMARY    |            1 | id          | A         |           4 | NULL     | NULL   |      | BTREE      |         |               |
| mylock |          1 | name_index |            1 | name        | A         |           4 | NULL     | NULL   | YES  | BTREE      |         |               |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set
#组合索引
mysql> alter table mylock add index name_age_index (name,age);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from mylock;
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mylock |          0 | PRIMARY        |            1 | id          | A         |           4 | NULL     | NULL   |      | BTREE      |         |               |
| mylock |          1 | name_age_index |            1 | name        | A         |           4 | NULL     | NULL   | YES  | BTREE      |         |               |
| mylock |          1 | name_age_index |            2 | age         | A         |           4 | NULL     | NULL   | YES  | BTREE      |         |               |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set

唯一索引：

mysql> create unique index unique_name_age_index on mylock(name,age);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from mylock;
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mylock |          0 | PRIMARY               |            1 | id          | A         |           4 | NULL     | NULL   |      | BTREE      |         |               |
| mylock |          0 | unique_name_age_index |            1 | name        | A         |           4 | NULL     | NULL   | YES  | BTREE      |         |               |
| mylock |          0 | unique_name_age_index |            2 | age         | A         |           4 | NULL     | NULL   | YES  | BTREE      |         |               |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set

2.删除索引

DROP INDEX ${indexName} ON ${tableName}

ALTER TABLE ${tableName} DROP INDEX ${indexName}

+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mylock |          0 | PRIMARY        |            1 | id          | A         |           4 | NULL     | NULL   |      | BTREE      |         |               |
| mylock |          1 | name_age_index |            1 | name        | A         |           4 | NULL     | NULL   | YES  | BTREE      |         |               |
| mylock |          1 | name_age_index |            2 | age         | A         |           4 | NULL     | NULL   | YES  | BTREE      |         |               |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set

mysql> drop index name_age_index on mylock;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from mylock;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mylock |          0 | PRIMARY  |            1 | id          | A         |           4 | NULL     | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set

mysql> show index from mylock;
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mylock |          0 | PRIMARY        |            1 | id          | A         |           4 | NULL     | NULL   |      | BTREE      |         |               |
| mylock |          1 | name_age_index |            1 | name        | A         |           4 | NULL     | NULL   | YES  | BTREE      |         |               |
| mylock |          1 | name_age_index |            2 | age         | A         |           4 | NULL     | NULL   | YES  | BTREE      |         |               |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set

mysql> alter table mylock drop index name_age_index;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from mylock;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mylock |          0 | PRIMARY  |            1 | id          | A         |           4 | NULL     | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set