mysql数据库（七）索引

索引

一、索引的介绍

索引是存储引擎中一种数据结构，或者说数据的组织方式。索引又称之为键key，为数据建立索引就好比是为书建目录，目的是为了便于查找数据。
显然的是为数据建立索引以后需要额外的空间存放建立的键值对关系，因此当对数据进行增删改时同时也需要修改索引关系，这将降低增删改的效率。然而我们平时使用数据库时，查询与增删改的比率大概为10：1，因此索引的建立可以大大提高我们的工作效率。
建立索引的原则：在程序上线前就考虑好索引问题，对那些用户可能频繁使用的查询数据加上索引。索引并发加的越多越好，当数据库中的索引过多时，即便是一个简单 的写操作也可能涉及到大量的I/O操作。

二、索引的数据结构

innodb采用的索引方式是B+树，如下图所示。B+树的非叶子节点中存放的key值，并且这些key值是按一定顺序排序好的，以便于查询时快速定位所需数据的叶子节点；叶子节点存放key value的键值对，同时相邻叶子节点间存在p指针，因此在范围查询时只需要一次从根节点开始的查询定位叶子节点，然后可以通过叶子节点的p指针快速定位相邻叶子节点的位置，从而大大提高范围查询的效率。
B+树与二叉树、平衡二叉树、B树等传统的查询数据结构相比有以下的优势：

• B+树只有叶子节点存放数据，因此在等量的数据之下，B+树的高度更低，索引的效率更高。
• B+树的节点是经过排序的，并且B+树中引入了p指针，因此在范围查询中B+树只需要一次从根节点开始的查询便可定位数据的位置，查询效率更高。

三、索引的分类与区别

B+树的查询方式中存着两种索引方式：聚集索引和辅助索引。聚集索引以主键值作为key，叶子节点存放着主键以及对应的记录；辅助索引以非主键值作为key，叶子节点存放着非主键索引及其对应的主键值。聚集索引一张表只能有一个，而辅助索引一张表可以有多个。

回表索引：通过辅助索引拿到主键值，然后通过主键值再从根节点查出记录。
覆盖索引：不需要回表就能拿不到记录。(只需要一次从根节点开始的查询就能获取需要的值)

#假设t1表中有id、name、age三个字段，且id为主键
#覆盖索引
#通过主键索引就可以直接得到age值
select age from t1 where id=1;
#通过name的辅助索引就可以直接得到主键值
select id from t1 where name='张三';

#回表索引
#先要通过name的辅助索引获取主键值，再通过主键值获取age。
select age from t1 where name='张三';

四、索引的使用

4.1简单索引的创建

#当指定了primary key或者unique以后mysql会自动生成一个主键索引
create table t1(id int unique,name varchar(20),age int);

#如果不指定primary key和unique，可以使用如下方式创建索引
create table t1(id int,name varchar(20),age int);
create index idx on t1(id);

#删除索引
drop index idx on t1;

#explain可以查看查询语句的查询计划，包括查询方式是否命中索引的情况
#从下方的表中可以看到没有辅助索引的情况下，查询明显没有命中索引
explain select age from t1 where name='aa';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

#创建name的辅助索引后再次查看查询计划可以看到此次查询命中了辅助索引namex
create index namex t1(name);
explain select age from t1 where name='aa';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | namex         | namex | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+

注意点：命中索引也未必能起到很好的提速效果，例如某个字段为varchar类型，且存放了大量相似的字符串aa…aab、aa…aac，那么字符串比较的过程需要花费大量的时间，同时B+树每个节点存放的空间有限，一个节点放不了几个key值，且B+树可能会变成直线状。因此索引的使用应尽量满足如下条件：

• 对区分度高并且占用空间小的字段建立索引(减少比较时间，增加B+树非叶子节点存放的key值)
• 针对大范围查询命中了索引查询效率依然很低，可以尝试缩小范围或者将大范围分为多个小的范围查询(例如购物网站查询商品时会先返回一页内容，用户点击下一页以后才返回下一页的内容)
• 不要把查询字段放到函数或者参与运算(例如把select * from t1 where id*12=24改为select * from t1 where id=24/12)

4.2联合索引

联合索引遵从最左匹配原则

create table t1(id int primary key,name varchar(20),age int,num int);
#创建联合索引
create index namex_agex_numx on t1(name,age,num);

对上方的联合索引可以命中联合索引的查询方式有name、name age、name num、name age num，简单来说就是联合索引最左侧的字段必须在查询字段内才能命中索引。

#下面来具体说说联合辅助索引和id主键索引混用的情况
create table t1(id int primary key,name varchar(20),age int,num int);
create index namex_agex on t1(name,age);

#使用联合索引查询内部的字段命中namex_agex
#联合索引是将多个字段绑到一起作为key值，因此对于联合索引内部字段的查询怎么查都可以命中索引                                        
select name from t1 where age=12;
select age from t1 where age=12;

#用id查询其他字段命中主键索引
select * from t1 where id=1;

#联合索引查询id命中namex_agex
#联合索引是将多个字段绑到一起作为key值，因此一次覆盖索引就可以得到id值
select id from t1 where age=12;

#使用联合索引查询非主键非内部值时要遵从最左匹配原则
select num from t1 where name='aa';
#不遵从最左匹配原则不能命中索引
select num from t1 where age=99;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

4.3 索引下推技术

索引下推技术是一种数据库查询优化技术，主要应用于MySQL 5.6及更高版本。这项技术的核心思想是将查询中的部分过滤条件下推到存储引擎层进行处理，从而减少从存储引擎返回给MySQL服务器的数据量，提高查询效率。
在没有使用索引下推的情况下，MySQL会先通过索引找到符合条件的记录，然后将这些记录返回给MySQL服务器层，再由服务器层根据查询中的其他条件进行筛选。而使用索引下推时，MySQL服务器会将部分过滤条件传递给存储引擎，由存储引擎在索引层面筛选出符合条件的数据项，然后只回表查询这些符合条件的数据项。
索引下推技术的引入可以减少数据读取，通过在索引层面进行条件过滤，减少了需要从磁盘读取的数据量；减少了不必要的回表操作，从而减少了I/O操作，提高了查询效率；在大数据量和高并发的数据库系统中，索引下推可以显著提高查询性能和系统吞吐量。