数据库索引详解
1 、索引的定义
在数据库中索引（Index）被定义为，在查询表中的数据时可以快速查找到相应的数据。索引本身也是一种数据结构，可以简单理解为排好序的快速查找数据结构。索引你主要是针对列的，一般现在索引的底层都采用B+树。
左边是数据表，一共有两列七条记录，其中包含数据记录的物理地址。为了加快查找，可以维护一个右边所示的二叉查找树（B+树），每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。
2 、索引的基本语法
2.1 查看索引
就是查看表中已经存在的索引，一般情况下，如果在建表时设置了主键约束和唯一约束时，就会自动创建两个索引（主键索引和唯一索引）
具体语法：
show index from table_name;
案例：查看admin表中的索引。
1.创建admin表
CREATE TABLE `admin` (
`id` int(14) NOT NULL AUTO_INCREMENT,
`name` varchar(28) NOT NULL,
`pwd` int(20) NOT NULL,
`careId` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `careId` (`careId`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
2.查看表中已经存在的索引。
mysql> show index from admin;
2.2 创建索引
就是创建一个索引，目的是为了加快检索速度。
具体语法：
CREATE [UNIQUE|FULLTEXT|SPATIAL]INDEX INDEXT_name
on table_name(column_name)
案例：在admin表中为姓名创建一个索引，索引名字为admin_name.
mysql> create index admin_name on admin(name);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
结果：
2.3 删除索引
具体语法：
DROP INDEX INDEX_NAME ON TABLE_NAME;
案例：删除admin_name索引。
mysql> drop index admin_name on admin;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
结果：
2.4 添加索引
具体语法：
该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table tb_name add primary key(column_list);
这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
alter table tb_name add unique index_name(column_list);
添加普通索引， 索引值可以出现多次。
alter table tb_name add index index_name(column_list);
该语句指定了索引为FULLTEXT， 用于全文索引
alter table tb_name add fulltext index_name(column_list);
3、MySQL中的索引结构
MySQL中的索引是在存储引擎中实现的，而不是在服务器中。因为MySQL中的存储引擎不同，所以实现的索引也大致不相同，而且有些存储引擎可能还不能够实现索引。MySQL现在一般有四种索引：
| 索引类型 | 描述 |
|---|---|
| BTree索引 | 最常见的一种索引，大部分存储引擎都支持。 |
| Hash索引 | 最简单的索引，只有Memory存储引擎支持。 |
| R-Tree索引（空间索引） | 用的比较少，主要用于地理空间数据类型，只有MyISAM存储引擎支持。 |
| Full-text索引（全文索引） | 主要用于全文索引，5.6之前只4，有MyISAM支持。5.6之后InnoDB也支持了。 |
注：一般我们平时所说的索引一般都采用B+树结构（多路平衡树）。
3.1 BTree结构
BTree叫做多路平衡树，之所以叫多路平衡树是因为，它是一种特殊的平衡二叉结构，即它除叶节点外，每个节点有多颗子树，且所有节点的左右子树的高度差的绝对值为0，它主要由以下特点：
- B树中所有结点的孩子结点的最大值称为B树的阶，通常用m表示。
- 树中的每个节点至多有m棵子树（最多有m-1个关键字）其中m为B树的阶。
- 树中的每个节点至少有m/2向上取整数棵子树（最多有m/2向上取整数 - 1个关键字）其中m为B树的阶。
- 若根结点不是终端结点，至少有两棵子树。
- 所有叶节点都出现在同一层，并且不包含任何信息。
- 每个非叶子节点由n个key与n+1个指针组成。
3.2 B+Tree结构
- B+树中所有结点的孩子结点的最大值称为B树的阶，通常用m表示。
- 树中的每个节点至多有m棵子树（最多有m个关键字）其中m为B树的阶。
- 树中的每个节点至少有m/2向上取整数棵子树（最多有m/2向上取整数个关键字）其中m为B树的阶。
- 若根结点不是终端结点，至少有两棵子树。
- 所有叶节点都出现在同一层，并且包含所有信息和指向相应记录的指针。
- 每个非叶子节点由n个key与n+1个指针组成。
- 所有非叶结点相当于索引，不包含真实的信息，主要包含指向子结点的指针和最大的关键字。
在MySQL中的索引一般使用的数据结构都是B+树，但是它有和平时见到的B+树不同，主要在于叶结点增加一个指向相邻叶子节点的链表指针，以达到提高区间访问的效率。
4、MySQL索引分类
在说索引分类之前要了解一个关键字：explain，它主要用来查看MySQL怎么执行DQL语句，即执行的性能
具体语法：
EXPLAIN DQL语句
它一般有几个关键的字段：
| 字段 | 描述 |
|---|---|
| id | 查询的序列号，是一组数字，用来表示查询中执行select子句或者是操作表的顺序。 |
| select_type | 表示查询的类型，一般有SIMPLE(简单查询)、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT） |
| table | 查询所用的表 |
| partitions | 显示的为分区表命中的分区情况，非分区表该字段为空（null）。5.7以后就是默认项 |
| type | 连接的类型 |
| possible_keys | 可能使用的索引 |
| key | 实际使用的索引 |
| key_len | 索引字段的长度 |
| rows | 扫描行的数量 |
| extra | 执行情况的说明和描述 |
type连接类型性能由好到差的连接类型为( system —> const -----> eq_ref ------> ref-------> ref_or_null----> index_merge —> index_subquery -----> range -----> index ------>all )
4.1 单值索引
**定义：**是指一个索引只能包含一列，一个表可以建立多个单值索引。
具体语法：
CREATE INDEX INDEXT_name
on table_name(column_name)
案例：查询部门编号为20的员工名字和薪资
没建立索引之间，查找部门编号是需要对比表中的所有数据，即14条记录
mysql> explain select ename,sal from emp where deptno = 20;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 10 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set
在为deptno建立索引后，就只需要对比deptno=20的信息，此时只需要对比三次，加快了检索效率
1.建立索引
mysql> create index deptno_emp on emp(deptno);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select ename,sal from emp where deptno = 20;
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ref | deptno_emp | deptno_emp | 5 | const | 5 | 100 | NULL |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set
4.2 复合索引
**定义：**指一个索引包含多个列。
具体语法：
CREATE INDEX INDEXT_name
on table_name(column_name1,column_name2,column_name3...column_namen)
案例：查询部门编号为30，薪资为1250的员工的名字和工作岗位。
没加索引之前，进行的是全表扫描，检索速度慢
mysql> explain select ename,job from emp where deptno = 30 and sal = 1250;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 7.14 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set
给员工薪资和部门编号创建符合索引，此时检索会通过索引检索，加快检索速度。
1.创建索引
mysql> create index deptno_sal_index on emp(deptno,sal);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
2.使用explain关键字进行性能分析
mysql> explain select ename,job from emp where deptno = 30 and sal = 1250;
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ref | deptno_sal_index | deptno_sal_index | 14 | const,const | 2 | 100 | NULL |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------+------+----------+-------+
1 row in set
注：创建符合==复合索引时，相对于对每个字段进行了单值索引创建。要遵守最左前缀法则，即查询从索引的最左前列开始，并且不跳过索引中的列，如果跳过索引会失效。
最左不跳过，还会使用索引，此时索引不会失效
mysql> explain select ename,job from emp where deptno = 30;
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ref | deptno_sal_index | deptno_sal_index | 5 | const | 6 | 100 | NULL |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set
最左跳过，不会使用索引，此时索引会失效
mysql> explain select ename,job from emp where sal = 1250;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 10 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set
4.3 唯一索引
**定义：**索引列的值必须唯一，但允许有空值
具体语法：
CREATE unique INDEX INDEXT_name
on table_name(column_name)
4.4 主键索引
**定义：**在设置主键后就会自动创建主键，一张表只能有一个主键，
但是如果在创建表时没有设置主键此时就不会创建主键索引，此时需要手动创建。
创建一张没有主键的表
CREATE TABLE `user` (
`id` int(20) NOT NULL,
`name` varchar(20) NOT NULL,
`psd` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
查看表中的索引
mysql> show index from user;
Empty set
建立主键索引：
mysql> alter table user add primary key user(id);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
查看结果：
5、 索引设计的原则
索引虽然可以加快检索效率，但是在有些时候添加索引反而会降低查询效率，因为索引是一种资源，也需要维护，故建立索引时应该遵循一些原则:
- 在需要经常进行删除修改更新的表中少用索引，因为这些操作会改变索引，当数据发生改变时需要维护索引，会造成资源的浪费。
- 在数据量小的表中不要使用索引。
- 在过滤性不好时，不适合使用索引。
- 当表的数据量很大且查询比较频繁时需要建立索引。
- 在创建主键后会自动创建索引。
- 尽量使用复合索引，少使用单值索引。
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速
- … …
6 、索引优缺点
优点：
-
索引就像一本书的目录一样，可以加快检索速度。
-
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
缺点：
-
索引本质上也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
-
虽然索引大大提高了查询速度，但是也降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为
更新表时，MySQL不仅要保存数据，还要保存一下索引文件因每次更新添加的索引列字段。
--------------------------------END------------------------------
事务：------------------------------------------------------------------------《加载完成》------------------------------------------------------------------------
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
