回顾上节，网友们会疑问为啥InnoDB存储引擎会选择使用B+Tree索引结构?

1、根据二叉树和红黑树的特点，B+Tree的层级更少，搜索效率高。

2、对B-Tree，无论是叶子节点还是非叶子节点都会保存数据，这样就会导致一页中存储的键值变少，指针跟着变少，在大数据量的情况下B树只能增加树的高度，导致性能降低。

3、相对于hash索引，B+Tree支持范围匹配和排序操作。

MySQL索引分类

MySQL索引主要分为这四类，主键索引、唯一所属、常规索引、全文索引。

主键索引针对于表中主键创建的索引,默认自动创建,只能有一个关键字为primary。

唯一索引是为了避免同一个表中某数据列中的值重复,可以有多个,关键字为unique。

常规索引用于快速定位特定数据,可以有多个。

全文索引,查找的是文本中的关键词，而不是比较索引中的值,可以有多个, fulltext。

在innodb存储引擎中，根据索引的存储方式，又可以分为以下两种，聚集索引和二级索引。

聚集索引(clustered index)将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据，表中聚集索引必须存在且只能有一个。

二级索引(secondary index)将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键,表中可以存在多个二级索引。

聚集索引的选取规则，如果存在主键，则主键索引就是聚集索引，如果不存在主键，将使用第一个唯一unique索引作为聚集索引，如果表中没有主键,或者没有合适的唯一索引，则innodb会自动生成一个rowid作为隐藏的聚集索引。

当执行select * from user where name=‘Arm’时，根据name字段进行查找如上图走二级索引，进行对比走左侧叶子节点定位到Arm和对应的id，但是语句使用的是*返回的是所有字段，此时会回到上面的聚集索引进行查找，对比id值然后走左侧叶子节点找到id=10的这行数据。这种查找现象被称为回表查询，也就是说先走二级索引找到对应的主键值，再根据主键值到聚集索引当中查找到这一行数据。

有两条SQL语句如下,如何判断它们的执行效率? id为主键，name字段创建有普通索引

1、select * from user where id=10;

2、select * from user where name='Arm'；

第一条是根据id来进行查找，根据上图示，可以直接到聚集索引上进行对比，很快就能定位到id=10 这行数据，也就是一次索引扫描就能定位到这行数据。如果根据name字段进行查找则需要到name字段对应的二级索引结构中去查，查到id值再回表查询，需要扫描2个字段的索引结构才能定位到这行数据。很明显当然是根据id字段查询的效率高。

有网友可能会疑惑，innodb主键索引的B+Tree高度为多高,能存多少数据呢?

根据B+Tree的特点，所有的数据都存放在叶子节点中，叶子节点最终形成一个双向链表。因为主键索引是聚集索引，叶子下存放的是行数据。由于每个节点落在磁盘上是以页进行存储的，每页的大小是固定的16k，所以一个节点能够存储的key和指针是有限的。要想计算B+Tree的高度，首先假设一行数据大小为1k，一页中可以存储16行这样的数据，innodb的指针占用6个字节是固定的，主键即使为bigint，占用字节数为8.（key 占用的字节是取决于可以的类型的，int4 个字节）。

如果树的高度为2:

非叶子节点能存多少个key多少个指针,指针永远比key多一个(n+1),n为key的数量。

n*8+(n+1)*6=16*1024===>n=1170==推出指针数为>1171个

1171*16=18736

如果树的高度为3:

1171的平方*16=21939856行记录

可以得出结论，innodb引擎的表即使你存储2千多万行数据，索引树的结果才3层，检索效率是很高的。当大于2千多万的数据时可以考虑分库分表。

索引操作语法

创建索引: create [unique|fulltext] index idx_name on 表名 (表中的字段名,...)

为哪张表中的哪个字段建立索引，如果是一个索引只关联一个字段，这种索引称为单列索引,如果关联多个字段称为联合索引或者叫组合索引。

查看索引: show index from 表名; # 查看指定表中的所有索引

删除索引: drop index idx_name on 表名;

2、创建练习表，完成以下练习

name字段可能会有重复值,给该字段创建索引

phone手机号字段的值，是非空且唯一的，为该字段创建唯一索引

为professional,age,status创建联合索引

为email建立合适的索引来提升查询效率

mysql> desc king_user;
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | MUL | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| phone | char(11) | NO | UNI | NULL | |
| email | varchar(100) | NO | MUL | NULL | |
| profession | varchar(20) | NO | MUL | NULL | |
| status | char(1) | NO | | NULL | |
| createtime | datetime | YES | | NULL | |
+------------+---------------------+------+-----+---------+----------------+
9 rows in set (0.02 sec)
mysql> select * from king_user;
+----+-----------+------+--------+-------------+---------------------+----------------------------------------------+--------+---------------------+
| id | name | age | gender | phone | email | profession | status | createtime |
+----+-----------+------+--------+-------------+---------------------+----------------------------------------------+--------+---------------------+
| 1 | 吕布 | 23 | 男 | 13180809090 | lvbu@king.com | 如雷三叉戟 | 1 | 2021-12-10 22:23:10 |
| 2 | 陈宫 | 25 | 男 | 13260819110 | chengong@king.com | 县令/参谋 | 1 | 2021-08-10 12:20:20 |
| 3 | 赵云 | 21 | 男 | 13666888866 | zhaoyun@king.com | 保镖/忠义/独门赵家枪法独步天下 | 2 | 2020-08-10 12:20:20 |
| 4 | 董卓 | 45 | 男 | 13126384266 | dongzhuo@king.com | 西凉贵族/篡汉者/好色 | 3 | 2021-08-10 12:20:20 |
| 5 | 石秀 | 25 | 男 | 13012384216 | shixiu@king.com | 打柴的/梦回长坂坡之战/忠义 | 2 | 2022-08-10 12:20:20 |
| 6 | 曹公公 | 55 | 男 | 13716586266 | caogongong@king.com | 太监/武功高强 | 1 | 2020-02-10 12:20:20 |
| 7 | 曹操 | 45 | 男 | 13576384226 | caomengde@king.com | 枭雄/汉相/诗人 | 1 | 2020-02-10 12:20:20 |
| 8 | 刘备 | 42 | 男 | 13676289266 | liuxiande@king.com | 织席小儿 | 2 | 2021-08-10 12:20:20 |
| 9 | 小乔 | 25 | 女 | 13056980266 | xiaoqiao@king.com | 美女 | 2 | 2021-08-10 12:20:20 |
| 10 | 糜夫人 | 30 | 女 | 13846384266 | mifuren@king.com | 美女/被织席小儿抛弃 | 3 | 2021-08-10 12:20:20 |
+----+-----------+------+--------+-------------+---------------------+----------------------------------------------+--------+---------------------+
10 rows in set (0.00 sec)

查看当前表king_user中的索引: show index from king_user;

# 默认只有主键索引
mysql> show index from king_user;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| king_user | 0 | PRIMARY | 1 | id | A | 10 | NULL | NULL | | BTREE | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)
mysql>

建立name索引：create index idx_user_name on king_user (name);

mysql> create index idx_user_name on king_user (name);
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 1

mysql> show index from king_user;
+-----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| king_user |          0 | PRIMARY       |            1 | id          | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
| king_user |          1 | idx_user_name |            1 | name        | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

给手机号创建唯一索引:create unique index idx_user_phone on king_user (phone);

mysql> create unique index idx_user_phone on king_user (phone);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show index from king_user;
+-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| king_user | 0 | PRIMARY | 1 | id | A | 10 | NULL | NULL | | BTREE | | |
| king_user | 0 | idx_user_phone | 1 | phone | A | 10 | NULL | NULL | | BTREE | | |
| king_user | 1 | idx_user_name | 1 | name | A | 10 | NULL | NULL | YES | BTREE | | |
+-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)
mysql>

为professional,age,status创建联合索引(字段顺序按最左优先，后续章节讲解)

create index idx_user_pro_age_sts on king_user (profession,age,status);

mysql> create index idx_user_pro_age_sts on king_user (profession,age,status);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from king_user;
+-----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| king_user | 0 | PRIMARY | 1 | id | A | 10 | NULL | NULL | | BTREE | | |
| king_user | 0 | idx_user_phone | 1 | phone | A | 10 | NULL | NULL | | BTREE | | |
| king_user | 1 | idx_user_name | 1 | name | A | 10 | NULL | NULL | YES | BTREE | | |
| king_user | 1 | idx_user_pro_age_sts | 1 | profession | A | 10 | NULL | NULL | | BTREE | | |
| king_user | 1 | idx_user_pro_age_sts | 2 | age | A | 10 | NULL | NULL | YES | BTREE | | |
| king_user | 1 | idx_user_pro_age_sts | 3 | status | A | 10 | NULL | NULL | | BTREE | | |
+-----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)
mysql>

给email字段建立索引：create index idx_user_email on king_user (email);

mysql> create index idx_user_email on king_user (email);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from king_user;
+-----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| king_user | 0 | PRIMARY | 1 | id | A | 10 | NULL | NULL | | BTREE | | |
| king_user | 0 | idx_user_phone | 1 | phone | A | 10 | NULL | NULL | | BTREE | | |
| king_user | 1 | idx_user_name | 1 | name | A | 10 | NULL | NULL | YES | BTREE | | |
| king_user | 1 | idx_user_pro_age_sts | 1 | profession | A | 10 | NULL | NULL | | BTREE | | |
| king_user | 1 | idx_user_pro_age_sts | 2 | age | A | 10 | NULL | NULL | YES | BTREE | | |
| king_user | 1 | idx_user_pro_age_sts | 3 | status | A | 10 | NULL | NULL | | BTREE | | |
| king_user | 1 | idx_user_email | 1 | email | A | 10 | NULL | NULL | | BTREE | | |
+-----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.02 sec)
mysql>

删除索引操作

mysql> drop index idx_user_phone on king_user;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> drop index idx_user_name on king_user;
Query OK, 0 rows affected (0.35 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> drop index idx_email on king_user;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> drop index idx_prof_age_sts on king_user;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

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

四时宝库

程序员的知识宝库

MySQL 从入门到精通(五) 索引性能分析及索引的使用

MySQL索引分类

索引操作语法