mysql索引总结

mysql索引总结

一、mysql数据结构选型

在数据库文件存储在磁盘时，为了提升查询效率，一定会选用合适的数据结构进行文件的存储

1、数组和链表

肯定不能选，这种最基本的数据结构，各自的劣势太明显。

2、hash

类似与咱们的hashmap， 速度快，但是只要是hash就会产生无序的问题，所以不常用但也有。

3、树

看来看去也就是树这种结构比较合理了。

二叉查找树可以吗？

在查找一个数据时，二叉树是读取根节点，小则从左找，大则从右找，每次读取一个数据。没有办法合理的利用局部性原理与磁盘预读，IO次数太多太多，其次就是树的层次还是偏高，所以不适合。

那每次读多个数据，每一个节点存多个数据的结构就只有B-树和B+树了；

接下来就讨论这两种数据结构的选型。

4、B-树

它类似普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点。下图是 B-树的简化图.

B-树有如下特点:

1. 所有键值分布在整颗树中；
2. 任何一个关键字出现且只出现在一个结点中；
3. 搜索有可能在非叶子结点结束；
4. 在关键字全集内做一次查找,性能逼近二分查找；

5、B+ 树

默认是主键，如果没有主键则使用唯一索引，唯一索引也没有则使用rowid，行号。

所以一定要建立主键。

B+树是B-树的变体，也是一种多路搜索树, 它与 B- 树的不同之处在于:

1. 所有关键字存储在叶子节点出现,内部节点(非叶子节点并不存储真正的 data)
2. 为所有叶子结点增加了一个链指针

简化 B+树 如下图

二、什么是索引

• MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
• 创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
• 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引优点：

• 索引大大减少了存储引擎需要扫描的数据量 （INNODB 最小一页 16k）
• 索引可以帮助我们进行排序以避免以避免使用临时表
• 索引可以将随机的I/O转为顺序的I/O 当然 后面两点都是说的B-树索引

索引是不是越多越好？

• 索引会增加数据库服务器写入操作的成本（INNODB对这个 做了一个优化：插入缓存 将多次插入合并成一次插入）
• 太多的索引会影响mysql查询优化器的选择时间（影响查询效率）

三、索引的分类

（1）聚簇索引和非聚簇索引

InnoDB使用的是聚簇索引 将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

**MyISAM使用的是非聚簇索引，**非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

（2）主键索引

也简称主键。它可以提高查询效率，并提供唯一性约束。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。

一种方法在创建表的时候创建，再次是修改表

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

索引都能在创建表是指定

create table student_score(
 id int(10) auto_increment,
 student_id int(10),
 subject_id int(10),
 score int(10),
 primary key (id),
 index idx_student_id (student_id),
 index idx_subject_id (subject_id)
); 

int(n)括号里面的数字表示显示宽度，不是代表能存放多少位数，始终占用4个字节的空间int(M) 跟 int 数据类型是相同的。int(M) 只有跟 zerofill 结合起来，才能使我们清楚的看到不同之处
1.创建表t

mysql> create table t(id int(5) zerofill);

2.插入数据

mysql> insert into t(id) values(10);

3.显示select * from t

00010

（3）普通索引(normal)

就是普普通通的索引，没有什么特殊要求，创建方式如下

CREATE INDEX idx_indexName ON mytable(username(length)); 

create index idx_myDeptIndex on detail(dept_id);

DROP INDEX [idx_indexName] ON mytable; 

ALTER TABLE tbl_name ADD INDEX idx_index_name (column_list);

（4）唯一索引UNIQUE

索引的值不能重复

唯一索引（UNIQUE KEY）

CREATE UNIQUE INDEX ux_indexName ON mytable(username(length)) 

修改表结构

ALTER table mytable ADD UNIQUE [ux_indexName] (username(length))

• 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
• 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
• 唯一性索引列允许空值，而主键列不允许为空值。
• 主键列在创建时，已经默认为非空值 + 唯一索引了。
• 主键可以被其他表引用为外键，而唯一索引不能。
• 一个表最多只能创建一个主键，但可以创建多个唯一索引。
• 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

（5）全文索引FULLTEXT

做全文检索使用的索引，我们有更好的替代品 ElacticSearch

（6）空间索引SPATIAL

MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。这是在地理位置领域使用的一种索引，其他场景用的很少，所以不需要深入学习。

（7）复合索引

当有多个查询条件时，我们推荐使用复合索引。索引的组合使用（索引合并）效率是低于复合索引的。

比如：我们经常按照 A列 B列 C列进行查询时，通常的做法是建立一个由三个列共同组成的复合索引而不是对每一个列建立普通索引。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Po1QKgbn-1627565091893)(C:/Users/越努力，越幸运。/Desktop/mysql/mysql.assets/image-20201130174603592.png)]

关于复合索引的一些知识：

为什么要使用联合索引

**减少开销。**建一个联合索引(Gid,Cid,SId)，实际相当于建了(Gid)、(Gid,Cid)、(Gid,Cid,SId)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

**覆盖索引。**对联合索引(Gid,Cid,SId)，如果有如下的sql: select Gid,Cid,SId from student where Gid=1 and Cid=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

**效率高。**索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where Gid=1 and Cid=2 and SId=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合Gid=2 and Cid= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！

**缺点。**联合索引越多，索引列越多，则创建的索引越多，索引都是存储在磁盘里的，通过索引算法(Btree代表索引算法使用二叉树的形式来做索引的)来查找数据，的确可以极大的提高查询效率，但是与此同时增删改的同时，需要更新索引，同样是需要花时间的，并且索引所占的磁盘空间也不小。

**建议。**单表尽可能不要超过一个联合索引，单个联合索引不超过3个

联合索引的创建注意事项

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

把范围查询的字段放在后边

创建联合索引，就是括号里多了几个列

alert table test add idx_clo1_col2 table (a1,a2,a3) 

create index idx_clo1_col2 on table(a1,a2,a3);

（8）hash索引

InnoDB和myIsam都不支持hash索引。若存储引擎不支持hash索引，又想拥有hash索引所带来的性能提升，则可以模拟InnoDB一样创建哈希索引。

优点

所以我们可以知道的是hash索引适用于快速选取某一行的数据，超级大表中定位某一行特别快。只要是只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引。

缺点
1、需要维护hash值，可以手动维护，也可以使用触发器实现。
2、若数据表非常大的话，CRC32（）会出现大量hash冲突。

三、索引的问题

1、哪些情况下适合建索引

• 频繁作为where条件语句查询的字段

• 关联字段需要建立索引，例如外键字段，student表中的classid, classes表中的schoolid 等

• 排序字段可以建立索引

• 分组字段可以建立索引，因为分组的前提是排序

• 统计字段可以建立索引，例如count(),max()

2、哪些情况下不适合建索引

• 频繁更新的字段不适合建立索引

• where条件中用不到的字段不适合建立索引

• 表数据可以确定比较少的不需要建索引

• 数据重复且发布比较均匀的的字段不适合建索引（唯一性太差的字段不适合建立索引），例如性别，真假值

• 参与列计算的列不适合建索引，索引会失效

3、索引失效的几种情况

• 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
• 复合索引不满足最左原则就不能使用索引
• like查询以%开头
• 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

4、mysql主要提供2种方式的索引：B-Tree索引，Hash索引

• B树索引具有范围查找和前缀查找的能力，对于有N节点的B树，检索一条记录的复杂度为O(LogN)。相当于二分查找。
• 哈希索引只能做等于查找，但是无论多大的Hash表，查找复杂度都是O(1)。
• 显然，如果值的差异性大，并且以等值查找（=、 <、>、in）为主，Hash索引是更高效的选择，它有O(1)的查找复杂度。
• 如果值的差异性相对较差，并且以范围查找为主，B树是更好的选择，它支持范围查找。

六、explain关键字

explain关键字可以模拟MySQL优化器执行SQL语句，可以很好的分析SQL语句或表结构的性能瓶颈。

1、explain的用途

1. 表的读取顺序如何
2. 数据读取操作有哪些操作类型
3. 哪些索引可以使用
4. 哪些索引被实际使用
5. 表之间是如何引用
6. 每张表有多少行被优化器查询
......

2、explain的执行效果

mysql> explain select * from subject where id = 1 
******************************************************
           id: 1
  select_type: SIMPLE
        table: subject
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
******************************************************

3、explain包含的字段

1. id //select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
2. select_type //查询类型
3. table //正在访问哪个表
4. partitions //匹配的分区
5. type //访问的类型
6. possible_keys //显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
7. key //实际使用到的索引，如果为NULL，则没有使用索引
8. key_len //表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
9. ref //显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
10. rows //根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
11. filtered //查询的表行占表的百分比
12. Extra //包含不适合在其它列中显示但十分重要的额外信息

（1）id字段

1. id相同

执行顺序从上至下
例子：
explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;
读取顺序：subject > teacher > student_score

2. id不同

如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行
例子：
explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));
读取顺序：teacher > subject > student_score

3. id相同又不同

id如果相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行
例子：
explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id
nion 
 -> select subject.* from subject right join teacher on subject.teacher_id = teacher.id;
 读取顺序：2.teacher > 2.subject > 1.subject > 1.teacher

（2）select_type字段

1. SIMPLE

简单查询，不包含子查询或Union查询
例子：
explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;

2. PRIMARY

查询中若包含任何复杂的子部分，最外层查询则被标记为主查询
例子：
explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));

3. SUBQUERY

在select或where中包含子查询
例子：
explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));

4. DERIVED

在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL
会递归执行这些子查询，把结果放在临时表中
备注：
MySQL5.7+ 进行优化了，增加了derived_merge（派生合并），默认开启，可加快查询效率

5. UNION

若第二个select出现在uion之后，则被标记为UNION
例子：
explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id
 -> union 
 -> select subject.* from subject right join teacher on subject.teacher_id = teacher.id;

6. UNION RESULT

从UNION表获取结果的select
例子：
explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id
 -> union 
 -> select subject.* from subject right join teacher on subject.teacher_id = teacher.id;

（3）type字段

NULL>system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL //最好到最差
备注：掌握以下10种常见的即可
NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL

1. NULL

MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引，比如通过id没有找到
例子：
explain select min(id) from subject;

2. system

表只有一行记录（等于系统表），这是const类型的特列，平时不大会出现，可以忽略

3. const

表示通过索引一次就找到了，const用于比较primary key或uique索引，因为只匹配一行数据，所以很快，如主键置于where列表中，MySQL就能将该查询转换为一个常量
例子：
explain select * from teacher where teacher_no = 'T2010001';

4. eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
例子：
explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id;

5. ref

非唯一性索引扫描，返回匹配某个单独值的所有行
本质上也是一种索引访问，返回所有匹配某个单独值的行
然而可能会找到多个符合条件的行，应该属于查找和扫描的混合体
例子：
explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;

6. ref_or_null

类似ref，但是可以搜索值为NULL的行
例子：
explain select * from teacher where name = 'wangsi' or name is null;

7. index_merge

表示使用了索引合并的优化方法
例子：
explain select * from teacher where id = 1 or teacher_no = 'T2010001' .

8. range

只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引
一般就是在你的where语句中出现between、<>、in等的查询。
例子：
explain select * from subject where id between 1 and 3;

9. index（全索引扫描）

Full index Scan，Index与All区别：index只遍历索引树，通常比All快
因为索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。
例子：
explain select id from subject;

10. ALL（全表扫）

Full Table Scan，将遍历全表以找到匹配行
例子：
explain select * from subject;

（4）table字段

数据来自哪张表

（5）possible_keys字段

显示可能应用在这张表中的索引，一个或多个
查询涉及到的字段若存在索引，则该索引将被列出，但不一定被实际使用

（6）key字段

实际使用到的索引，如果为NULL，则没有使用索引
查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表

（7）key_len字段

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
在不损失精确度的情况下，长度越短越好
key_len显示的值为索引字段最大的可能长度，并非实际使用长度
即key_len是根据定义计算而得，不是通过表内检索出的

（8）ref字段

显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值

（9）rows字段

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数

（10）partitions字段

匹配的分区

（11）filtered字段

查询的表行占表的百分比

（12）Extra字段

包含不适合在其它列中显示但十分重要的额外信息

1. Using filesort

说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取
MySQL中无法利用索引完成的排序操作称为“文件排序”
例子：
explain select * from subject order by name;

2. Using temporary

使用了临时表保存中间结果，MySQL在对结果排序时使用临时表，常见于排序order by 和分组查询group by
例子：
explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id
 -> union 
 -> select subject.* from subject right join teacher on subject.teacher_id = teacher.id;

表示相应的select操作中使用了覆盖索引（Covering Index）,避免访问了表的数据行，效率不错！
如果同时出现using where，表明索引被用来执行索引键值的查找
如果没有同时出现using where，表明索引用来读取数据而非执行查找动作
例子：
explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;
备注：
覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，即查询列要被所建的索引覆盖

3. Using where

使用了where条件
例子：
explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;

4. Using join buffer

使用了连接缓存
例子：
explain select student.*,teacher.*,subject.* from student,teacher,subject;

5. impossible where

where子句的值总是false，不能用来获取任何元组
例子：
explain select * from teacher where name = 'wangsi' and name = 'lisi';

6. distinct

一旦mysql找到了与行相联合匹配的行，就不再搜索了
例子：
explain select distinct teacher.name from teacher left join subject on teacher.id = subject.teacher_id;

7. Select tables optimized away

SELECT操作已经优化到不能再优化了（MySQL根本没有遍历表或索引就返回数据了）
例子：
explain select min(id) from subject;