索引

MySQL索引

1、什么是索引

# 官方定义:
	一种帮助mysql提高查询效率的数据结构  index
# 索引的数据结构
	B+树

# 索引的优点
	大大加快了数据的查询速度

# 索引的缺点
	维护索引需要耗费数据库的资源
	索引需要占用磁盘空间
	当对表的数据进行增删改的时候,因为要维护索引,速度会受到影响,因为会对底层结构重排序

2、 索引分类

-- 在InnoDB(支持前四种)
# 主键索引
	设定在主键后数据库会自动建立索引，innoDB为聚簇索引

# 单值索引
	即一个索引只包含单个列，一个表可以有多个单列索引

# 唯一索引
	索引列的值必须唯一，但允许有空值，唯一索引索引值可以存在null，但是只能存在一个null

# 复合索引
	即一个索引包含多个列，主键索引列值不能为空



-- MyISAM支持
# Full Text 全文索引  (MySQL 5.7版本之前，只能用于MYISAM引擎)
	全文索引类型为FULLTEXT,在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。

3、 索引使用

3.1、在Linux系统下创建表

启动MySQL
[root@localhost /]# mysql -u root -p
Enter password:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

# 创建数据库
mysql> create database test_index;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_index         |
+--------------------+
5 rows in set (0.00 sec)

# 创建表
mysql> use test_index;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table t_user(id varchar(20) primary key,name varchar(20));
Query OK, 0 rows affected (0.10 sec)

mysql> show tables;
+----------------------+
| Tables_in_test_index |
+----------------------+
| t_user               |
+----------------------+
1 row in set (0.00 sec)

3.2、索引的使用

3.2.1、主键索引

# 主键索引
因为在建表时，将id设为主键，那么数据库就会自动设置主键索引

3.2.2、普通索引

# 普通索引
	建普通索引通常有两种方式

# 建表之后创建
create index nameindex on t_user(name);
语法:create index 索引名 on 表名(列)

# 建表时创建
	我们删除表，重新为id和name建立索引

# 删除表
mysql> show tables;
+----------------------+
| Tables_in_test_index |
+----------------------+
| t_user               |
+----------------------+
1 row in set (0.00 sec)

mysql> drop table t_user;
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
Empty set (0.00 sec)

# 新建表，且建立索引
mysql> create table t_user(id varchar(20) primary key,name varchar(20),key(name));
Query OK, 0 rows affected (0.02 sec)

3.2.3、唯一索引

# 建表时创建
	create table t_user1(id varchar(20) primary key,name varchar(20),unique(name));
# 建表之后创建
	create unique index nameindex on t_user(name);

3.2.4、复合索引

# 最左前缀原则
# MySQL 引擎在查询时为了更好的利用索引，在查询过程中会动态调整查询字段顺序以便利用索引

# 建表时创建
	create table t_user2(id varchar(20) primary key,name varchar(20),age int,key(name,age));
# 建表之后创建
	create index nameageindex on t_user(name,age);

3.3、什么情况下无法利用索引

# 查询语句中使用Like关键字
	在查询语句中使用Like关键字进行查询时，如果匹配字符串的第一个字符为"%",索引不会被使用。如果"%"不是在第一个位置，索引就会被使用
	
# 查询语句中使用多列索引(最左前缀原则)
	多列索引是在表的多个字段上创建索引，只有查询条件中使用了这些字段中的第一个字段，后一个索引才会被使用

# 查询语句中只用OR关键字
	查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询不会只用索引

4、 索引的底层原理

# 删除t_user索引
mysql> drop index name on t_user;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
此时表中只有id 主键索引

# 我们向表中插入数据
insert into t_user values(5,'f');
insert into t_user values(4,'e');
insert into t_user values(3,'d');
insert into t_user values(2,'c');
insert into t_user values(1,'b');
insert into t_user values(0,'a');

# 查询
mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
| 0  | a    |
| 1  | b    |
| 2  | c    |
| 3  | d    |
| 4  | e    |
| 5  | f    |
+----+------+
6 rows in set (0.00 sec)

# 主键索引进行了重排序
排序是为了快速查询	

# 为了进一步提高效率mysql索引又进行了优化
	就是基于页的形式进行管理索引
	如:查询id = 4的  直接先比较页目录中找，再去数据目录中找

# 什么是B+树
B+Tree是在B-Tree(B树)基础上的一种优化，使其更适合实现存储索引结构，InnoDB存储引擎就是其B+Tree实现其索引结构

# 什么是B树
	B树的出现是为了弥合不同的存储级别之间的访问速度的巨大差异，实现高效的I/O。平衡二叉树的查找效率是非常高的，并可以通过降低树的深度来提高查找的效率。但是数据量非常大。树的存储的元素数量是有限的，这样会导致二叉查找树结构由于树的深度过大而造成磁盘I/O读写过于频繁，进而导致查询效率低下。另外数据量过大会导致内存空间不够容纳平衡二叉树所有结点的情况。B树是解决这个问题的很好的结构。
	
在计算机科学中，B树是一种自平衡树数据结构，它维护有序数据并允许以对数时间进行搜索，顺序访问，插入和删除。B树是二叉搜索树的一般化，因为节点可以有两个以上的子节点。与其他自平衡二进制搜索树不同，B树非常适合读取和写入相对较大的数据块（如光盘）的存储系统。它通常用于数据库和文件系统。

# B树的定义
B树是一种平衡的多分树，通常我们说的m阶的B树。他必须满足如下条件
- 每个节点最多只有m个子节点。
- 每个非叶子节点（除了根）具有至少⌈ m/2⌉子节点。
- 如果根不是叶节点，则根至少有两个子节点。
- 具有k个子节点的非叶节点包含k -1个键。
- 所有叶子都出现在同一水平，没有任何信息（高度一致）。

# B+树
B+树的特征：

- 有m个子树的中间节点包含有m个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引；
- 所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接。 (而B 树的叶子节点并没有包括全部需要查找的信息)；
- 所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。 (而B 树的非终节点也包含需要查找的有效信息);

# B树和B+树的区别
B+树只有叶子节点存储数据，B树 每个节点都存储数据
B+Tree非叶子节点只存储键值信息
B+Tree所有的叶子节点之间都有一个链指针