索引越多越好吗 索引越多越好嘛

索引

About

为何要有索引

为了提高查询速度

什么是索引

索引时帮助Mysql高效获取数据的数据结构。

关于索引的误解

• 索引并不是越多越好，索引太多，应用程序的性能可能会受到影响；索引太少，对查询性能又会产生影响。
• 索引最好是初始时添加，后续添加的话，会处理相当大的一部分数据。
• 时候让DBA添加，由于DBA往往不了解业务的数据流，往往添加得不够精确

索引的原理

索引的目的在于提高查询效率，本质是通过不断的缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的时间编程顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

磁盘IO与预读

考虑到IO的的操作是非常高昂的，计算机操作系统做了一些优化，当一次IO时，不仅把当前磁盘地址的数据加载到了内存缓冲区，而且也把相邻的数据也读到的内存的缓冲区内。

索引的数据结构

b+树的查找过程

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

b+树性质

1. 索引字段要尽量的小

• IO次数取决于B+树的高度H
• 索引字段要尽量的小
• 索引字段区分度一定要高，当数据项为1时回退化为线性表

2. 索引的最左匹配原则
   当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

聚集索引与辅助索引

数据库中，B+树的高度一般都在2-4层，也就是说查找某个键值只需要2-4次IO

B+树索引可以分为 聚集索引 和 辅助索引

• 相同点：都是B+树形式，高度是平衡的，叶子节点存放着所有的数据
• 不同点：叶子节点存放的是否是一整行的信息

聚集索引

InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。
    
1.如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。
    
2.如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

3.由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多少情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。

优点

• 它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录
• 范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可

聚集索引的好处之一:
它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录

mysql> desc s1;  # 最开始没有主键
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | NO   |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.11 sec)

mysql> explain select * from s1 order by id desc limit 10;  # Using filesort，需要二次排序
+----+-------------+-------+------------+------+---------------+------+---------+------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 292066 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+
1 row in set, 1 warning (0.00 sec)

mysql> alter table s1 add primary key(id);  # 添加主键
Query OK, 0 rows affected (20.27 sec)

mysql> explain select * from s1 order by id desc limit 10;  # 基于主键的聚集索引在创建完毕后就已经完成了排序，无需二次排序
+----+-------------+-------+------------+-------+---------------+---------+---------+---+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra               |
+----+-------------+-------+------------+-------+---------------+---------+---------+---+
|  1 | SIMPLE      | s1    | NULL       | index | NULL          | PRIMARY | 4       | NULL |   10 |   100.00 | Backward index scan |
+----+-------------+-------+------------+-------+---------------+---------+---------+---+
1 row in set, 1 warning (0.00 sec)

聚集索引的好处之二:
范围查询(range query)，即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可

mysql> alter table s1 drop primary key;
Query OK, 293108 rows affected (31.45 sec)

mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | NO   |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.11 sec)

mysql> explain select * from s1 where id > 1 and id < 1000000;  # 没有聚集索引，预估需要检索的rows数如下
+----+-------------+-------+------------+------+---------------+------+---------+------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 292352 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+
1 row in set, 1 warning (0.00 sec)

mysql> alter table s1 add primary key(id);
Query OK, 0 rows affected (17.12 sec)

mysql> explain select * from s1 where id > 1 and id < 1000000;  # 有聚集索引，预估需要检索的rows数如下
+----+-------------+-------+------------+-------+---------------+---------+---------+---+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+---+
|  1 | SIMPLE      | s1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 146113 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+---+
1 row in set, 1 warning (0.12 sec)

辅助索引

表中除了聚集索引外，其他索引都是辅助索引，辅助索引的

节点除了包含键值对以外，每个叶子节点中的索引中还包含一个书签，该书签用来告诉INNODB存储引擎去哪里找到与索引相对应的行数据。

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。

因此每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得只想主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

举例来说，如果在一棵高度为3的辅助索引树种查找数据，那需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问才能得到最终的一个数据页。

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得只想主键索引的主键，然后再通过主键索引来找到一个完整的行记录

举例来说，如果在一棵高度为3的辅助索引树种查找数据，那需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问才能得到最终的一个数据页
123

7、MySQL索引管理

7.1、功能

1、索引的功能就是加速查找
2、mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能
12

7.2、MySQL常用的索引

普通索引INDEX: 加速查找

唯一索引:
    -主键索引PRIMARY KEY: 加速查找+约束(不为空、不能重复)
    -唯一索引UNIQUE: 加速查找+约束(不能重复)

联合索引:
    -PRIMARY KEY(id,name): 联合主键索引
    -UNIQUE(id,name): 联合唯一索引
    -INDEX(id,name): 联合普通索引

# 举例说明
举个例子来说，比如你在为某商场做一个会员卡的系统
这个系统有一个会员表
有下列字段:
会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT

那么这个 会员编号，作为主键，使用 PRIMARY
会员姓名 如果要建索引的话，那么就是普通的 INDEX
会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE(唯一的，不允许重复)

# 除此之外还有全文索引，即FULLTEXT
会员备注信息，如果需要建索引的话，可以选择全文搜索
用于搜索很长一篇文章的时候，效果最好
用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以
但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索

# 其他的如空间索引SPATIAL，几乎不用
123456789101112131415161718192021222324252627282930313233

7.3、索引的两大类型hash与btree

# 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引: 查询单条快，范围查询慢
btree类型的索引: b+树，层数越多，数据量指数级增长(我们就用它，因为innodb默认支持它)

# 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引
12345678910

7.4、创建/删除索引的语法

# 方法一: 创建表时
    　　CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC]) 
                );


# 方法二: CREATE在已存在的表上创建索引
        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;


# 方法三: ALTER TABLE在已存在的表上创建索引
        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                             索引名 (字段名[(长度)]  [ASC |DESC]) ;
                             
# 删除索引: DROP INDEX 索引名 ON 表名字;
12345678910111213141516171819

结果:

PS: 一个字段上可以创建多个索引. 索引的名字也是字段名加序号查看:

show create table table-name

删除:

通常不做通常主键上有自动增长, 不能直接删除主键, 需要先去掉自动增长特性

后期添加索引:

7.5、总结

1、一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引

2、在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快
比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中
建完以后，再查询就会很快了

3、需要注意的是: innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI

MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance True）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引
因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型
12345678910

8、索引使用场景

8.1、数据准备

测试数据量: 800W左右

8.2、条件过滤 where

查询字段 first_name没有索引, 执行计划如下:

增加 查询字段 first_name 索引一但有索引, 就可以被使用!

8.3、排序 order by|group by

利用某个字段排序, 检索:

在没有索引的情况下:

创建该排序字段的索引存在索引的情况下:

8.4、关联 join

table1 join table2 ON table1.field = table2.field

table1.field , table2.field 需要在关联字段上建立索引

测试, 连接查询:

添加关联字段的索引

查询: 时间也很长, 但是比较而言, 索引,已经有效果!

实际中的limit的配合使用:

如果数据量大, 连接查询, 导致大量的结果, 以上的统计, 进一步的优化的可选方案: 逆范式

9、索引使用条件

9.1、字段独立原则

如果字段上存在索引, 要求使用, 字段必须要独立出现在表达式的一侧

测试, 主键索引的检索:

id-1 没有字段独立, 通过时间 没有用索引:

9.2、左原则

9.2.1、like不能以通配符开头

执行计划

like %keyword% 不能使用索引, 内容包含某个关键字的业务逻辑, 使用全文索引完成. mysql不支持中文的全文索引, 只能使用第三方的全文索引引擎实现

9.2.2、复合索引, 左边的字段才可以使用索引

A Z
B X
原因: 如果是复合索引, 先根据第一个字段排序, 如果相同, 再根据第二个字段做

左边字段:

右边字段:

除非, 左边的字段已经可以确认，first_name = XXX AND last_name=YYY的查询才可以

9.3、自动判断

MySQL会判断使用索引, 比未使用索引, 更消耗资源

主要原因:

大量的随机磁盘开销, 会大于 全表扫描的顺序开销

9.4、OR 全索引

如果条件出现了OR逻辑, 保证, 每个条件都需要存在可以使用索引才可以
first_name = ‘murry’ OR user=”ABCD%”
以上的查询 first_name 和 user 都有所以才可以