MySQL_7.索引概述

1.什么是索引
在关系数据库中，索引是一种单独的、物理的数对数据库表中一列或多列的值进行排序的一种存储结构。
它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单

2.索引的优点
(1)通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
(2)可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
(3)在使用分组和排序子句进行数据查询时,可以减少查询中分组和排序的时间。
(4)通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

3.索引的缺点
(1)创建索引和维护索引要耗费时,这种时间随着数据量的增加而增加。
(2)索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。
(3)当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

4.索引的分类
(1)B树索引 (主要,默认索引)：mysql innodb默认的索引类型就是 Btree索引(B树,B-树,B+树,B*树),Binary Tree,就是一个二叉树。
(2)hash索引：Hash索引结构的特殊性,其检索效率非常高,索引的检索可以一次定位,不像B-Tree索引需要从根节点到枝节点,
最后才能访问到页节点这样多次的IO访问,所以Hash索引的查询效率要远高于B-Tree索引，仅仅能满足"=" "IN" 和 "<=>" 查询,不能使用范围查询。
只有 Memory存储引擎显示支持hash索引。
(3)FULLTEXT索引 (全文检索,MYISAM和INNODB引擎都支持了)。
(4)R-tree索引 (用于对GIS数据类型创建 SPATIAL 空间索引)

5.索引按逻辑的分类
(1)主键索引
(2)普通索引 or 单列索引
(3)多列索引(复合索引)
(4)唯一索引或者非唯一索引
(5)空间索引

6.B-树的由来
为迎合磁盘与内存的速度差，减少磁盘IO次数，提高性能
注(数据量非常大时，内存不够用，大部分数据只能存放在磁盘上，只有需要的数据才加载到内存中。
一般而言内存访问的时间约为 50 ns（纳秒），而磁盘在 10 ms (毫秒))

7.B-树和B+树的区别
(1)B+树内节点不存储数据，所有 data 存储在叶节点导致查询时间复杂度固定为 log(n)。而B-树查询时间复杂度不固定，与 key 在树中的位置有关， 最好为O(1)。
(2)B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等，而B-树每个节点 key 和 data 在一起，则无法区间查找。
(3)B+树更适合外部存储。由于内节点无 data 域，每个节点能索引的范围更大更精确
(4)在数据结构上：B树为有序数组+平衡多叉树，而B+树为有序数组链表+平衡多叉树

8.为什么 Mysql 使用B+树?
Mysql 是一种关系型数据库，区间访问是常见的一种情况，而 B-树并不支持区间访问（可参见上图），
而B+树由于数据全部存储在 叶子节点，并且通过指针串在一起，这样就很容易的进行区间遍历甚至全部遍历。
(1)B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等，而B-树每个节点 key 和 data 在一起，则无法区间查找 
(2)B+树的查询效率更加稳定，数据全部存储在叶子节点，查询时间复杂度固定为 O(log n)
(3)B+树更适合外部存储。由于内节点无 data 域，每个节点能索引的范围更大更精确

9.mysql索引使用
(1)创建索引

create index index_name on table_name(col_name);

(2)为已创建的表增加索引

alter table table_name add index index_name(col_name);

(3)查看已创建索引：

show index from yzjtestdb.yzjtest_m1

(4)索引使用：

explain select * from yzjtestdb.yzjtest_m1 where name = 'yzjtest30000010';
explain select * from yzjtestdb.yzjtest_m1 where tel = '13379855952';

(5)索引删除：

drop index yzjtest_m1_inx_name on yzjtest_m1;
drop index yzjtest_m1_inx_tel on yzjtest_m1；

(6)复合索引使用管理
# 语法：

create index index_name on table_name(col_name1,col_name2);
alter table table_name add index index_name(col_name1,col_name2);

# 案例：

use yzjtestdb;
create index yzjtest_m1_inx_name_tel on yzjtestdb.yzjtest_m1(NAME,TEL);
alter table yzjtestdb.yzjtest_m1 add index yzjtest_m1_inx_name_tel(NAME,TEL);

查看已创建索引：

show index from yzjtestdb.yzjtest_m1;

# 索引使用：

explain select * from yzjtestdb.yzjtest_m1 where name = 'yzjtest30000010'and tel = '13379855952';

# 索引删除：

drop index yzjtest_m1_inx_name_tel on yzjtest_m1;