索引概述及分类

​ 在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

  • 什么是索引
1
2
索引是用于快速找出在某个列中拥有特定值的行。
如果没有索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多。
  • 为什么建立索引
1
2
3
1）如果有一张产品表，记录着4W产品的信息。有一个品牌的字段记录产品的品牌，现在想要查询出这个品牌的产品。
2)如果没有索引，那么将从表中第一条记录一条条往下遍历，直到找到该条信息为止。
3）如果拥有索引，那么会将该品牌字段，通过一定的方法进行存储，好让查询该字段上的信息时，能够快4）速找到对应的数据，而不必在遍历4W条产品数据。
  • 索引算法类型
1
2
3
4
5
使用什么算法的索引，和MySQL的存储引擎有关（innodb）
	Btree算法：B树索引（默认）
	HASH算法：HASH索引
	FULLTEXT：全文索引
	RTREE：R树索引
  • 索引优缺点
1
2
3
4
5
6
7
8
9
优点：
	1）所有的字段都可以被索引，也就是可以给任意字段设置索引
	2）加快数据的查询速度

缺点：
	1）创建索引和维护索引要耗费时间
	2）数据量的增加，所耗费的时间也会增加
	3）索引也需要占空间，如果我们的索引量越来越大的话，那么索引文件可能达到我们数据的最大线值
	4）表中数据发生变化时，索引也需要动态维护，降低数据维护效率

索引算法介绍

Btree

QQ_1723705862018

1
2
3
4
5
6
select * from tb1 where id=32; 3次IO

# 范围查询
select * from tb1 where id>25 and id <36; 9次IO

5-20-* 返回  28-28-*  返回 28-35-*  9次io

B+tree

QQ_1723706447211

1
2
3
4
5
6
7
8
9
10
11
12
# 精确查询
select * from tb1 where id=32; 3次IO

# 范围查询
select * from tb1 where id>25 and id <36; 5次IO

5-20-Q-Q-Q  5次io

### Btree算法和B+tree算法的区别
1.B+tree算法，优化了范围查询
2.在相邻的叶子节点上添加了指针

B*tree

QQ_1723706709114

索引的分类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
# 主键索引（聚簇索引）
	- 联合索引
	
# 唯一索引
	- 联合索引
	- 前缀索引
	
# 普通索引
	- 联合索引
	- 前缀索引
	
## 查看索引
1）mysql> desc mysql.user;
+------------------------+--------------+------+-----+-----------------------+-------+
| Field                  | Type         | Null | Key | Default               | Extra |
+------------------------+--------------+------+-----+-----------------------+-------+
| Host                   | char(60)     | NO   | PRI |                       |       |
| User                   | char(16)     | NO   | PRI |                       |       |

2）mysql> show create table mysql.user;
  PRIMARY KEY (`Host`,`User`)
  
3）mysql> show index from mysql.user;
+-------+------------+----------+--------------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+-------+------------+----------+--------------+-------------+
| user  |          0 | PRIMARY  |            1 | Host        |
| user  |          0 | PRIMARY  |            2 | User        |
+-------+------------+----------+--------------+-------------+

索引不是越多越好，避免给大列创建索引
	1.索引会进行排序，大列创建索引速度慢
	2.索引越多占用磁盘空间越大

创建索引

普通 & 唯一键 & 主键索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#统计一个表李有多少条数据
root@localhost:zls 09:52:19> select count(*) from world.city;
+----------+
| count(*) |
+----------+
| 4079 		 |
+----------+

#创建普通索引
alter table 表名 add index 索引名(字段);
mysql> alter table stu2 add index idx_name(name);
mysql> alter table world.city add index idx_population(population);

## 删除普通索引
mysql> alter table stu2 drop index idx_name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
##唯一键索引
#首先判断这个字段里是否唯一，是否唯一
mysql> select count(population) from world.city;
+-------------------+
| count(population) |
+-------------------+
| 4079 							|
+-------------------+
1 row in set (0.00 sec)

#发现去重之后数据和4079比少了，说明数据还是不唯一的，就不能创建唯一键
mysql> select count(distinct(population)) from world.city;
+-----------------------------+
| count(distinct(population)) |
+-----------------------------+
| 3897 											|
+-----------------------------+

## 添加唯一键索引
mysql> alter table stu30 add unique key uni_phone(phone);

## 删除
mysql> alter table stu30 drop index uni_phone;
1
2
3
4
5
6
7
8
9
10
## 主键索引
### 建表时创建主键索引
mysql> create table stu1(id int primary key auto_increment,name varchar(10));
mysql> create table stu1(id int auto_increment,name varchar(10),primary key(id));

### 添加主键索引
mysql> alter table stu2 add primary key(id);

### 删除主键索引
mysql> alter table stu2 drop primary key;

前缀索引 & 联合索引

1
2
3
4
5
6
7
8
9
解决给大列创建索引排序速度慢的问题
	索引前缀多少字段
## 普通索引创建前缀索引
root@localhost:zls 10:24:28> alter table stu30 add index idx_phone(phone(3));

## 唯一索引创建前缀索引
root@localhost:zls 10:56:30> alter table stu30 add unique key uni_phone(phone(3));

#逐渐不能创建前缀索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
一张表中，哪些字段需要创建索引，取决于用户需求
当一张表中，需要创建索引的字段比较多的时候

## 普通索引联合索引
create table xiangqin
(id int,name varchar(10),gender enum('0','1'),high int,weight int,body varchar(10),salary int);

#哪个需求比较高就先前面
mysql> alter table xiangqin add index idx_all(gender,salary,body);
mysql> alter table xiangqin add index idx_all(gender,salary(4),body);

#例如需求
where a.女生 and b.身高 and c.体重 and d.身材好
mysql> alter table xiangqin add index idx_all(a,b,c)

特点：前缀生效特性
a,ab,ac,abc,abcd 可以走索引或部分走索引
b bc bcd cd c d ba ... 不走索引
原则：把最常用来做为条件查询的列放在最前面

## 唯一索引创建联合索引
mysql alter table xiangqin add unique key uni_all(gender,salary,body);

## 主键索引创建联合索引
mysql>  alter table xiangqin add primary key(gender,salary,body);

MySQL数据扫描

全表扫描

1
2
3
4
5
6
7
8
1.select * from student;
2.select 查询数据时，where条件接的字段没有创建索引 （不走索引）   type类型的ALL是全表扫描
root@linux50 > explain select * from world.city;
+----+-------------+-------+------+------+---------+------+------+-------+
| id | select_type | table | type | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------------+---------+------+------+-------+
|  1 | SIMPLE      | city  | ALL  | NULL | NULL    | NULL | 4188 | NULL  |
+----+-------------+-------+------+------+---------+------+------+-------+

索引扫描

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
# 走索引的查询
1）index 				// 全索引扫描
+----+-------------+-------+-------+-------------+---------+------+------+
| id | select_type | table | type  | key         | key_len | ref  | rows |
+----+-------------+-------+-------+-------------+---------+------+------+
|  1 | SIMPLE      | city  | index | CountryCode | 3       | NULL | 4188 |
+----+-------------+-------+-------+-------------+---------+------+------+

2）range 			// 范围查询并且创建了索引  (没有索引也是走全扫描)
root@world > explain select * from world.city where population<100;
+----+-------------+-------+-------+---------+------+------+
| id | select_type | table | type  | key_len | ref  | rows |
+----+-------------+-------+-------+---------+------+------+
|  1 | SIMPLE      | city  | range | 4       | NULL |    1 |
+----+-------------+-------+-------+---------+------+------+

3）ref 			// 联合查询， 普通索引的精确查询
root@world > explain select * from world.city where countrycode='CHN';
+----+-------------+-------+------+---------+-------+------+
| id | select_type | table | type | key_len | ref   | rows |
+----+-------------+-------+------+---------+-------+------+
|  1 | SIMPLE      | city  | ref  | 3       | const |  363 |
+----+-------------+-------+------+---------+-------+------+

4）eq_ref // 连表查询使用 join on 且 小表在前，大表在后

5）const ，system // 主键精确查询
+----+-------------+-------+-------+---------+---------+-------+
| id | select_type | table | type  | key     | key_len | ref   |
+----+-------------+-------+-------+---------+---------+-------+
|  1 | SIMPLE      | city  | const | PRIMARY | 4       | const |
+----+-------------+-------+-------+---------+---------+-------+

6)null    超出字段
+----+-------------+-------+------+---------------+------+---------+
| id | select_type | table | type | possible_keys | key  | key_len |
+----+-------------+-------+------+---------------+------+---------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    |
+----+-------------+-------+------+---------------+------+---------+


-key_len: 越小越好 ， 前缀索引去控制
-rows: 越小越好

不走索引排查

索引创建该规范

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。

  • 每张表必须有一个主键索引

    1. 能创建唯一索引就创建唯一索引
    2. 如果重复值比较多的情况下，联合索引
    3. 为经常需要排序、分组和联合操作的字段建立索引
    4. 为常作为查询条件的字段建立索引
    5. 尽量使用前缀来索引

  • 限制索引的数目

1
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
  • 删除不再使用或者很少使用的索引
1
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

不走索引排查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 1.没创建索引
# 2.结果集超过总数据的25%
		explain select * from world.city where population >1000;
#优化：使用limit 分页查询
	explain select * from world.city where population >1000 limit 60,60;

# 3.使用字段做计算
	explain select * from world.city where id+1=10;
	
# 4.隐式转换导致不走索引
	explain select * from stu30 where phone=12312312312;
#优化：根据字段类型查询，是char类型就加上引号
	explain select * from stu30 where phone='12312312312';

# 5.模糊查询时 %不能放在最前面
	explain select * from world.city where countrycode like '%H';
#优化：%不能放在最前面   搜索引擎数据库 用elasticsearch

# 6.范围查询使用 <> 、 not in
	explain select * from world.city where countrycode <> 'CHN';
#优化：使用limit
explain select * from world.city where countrycode <> 'CHN' limit 10;

# 7.联合索引没有按照索引创建顺序查询，导致不走索引
优化方案：调研，日志数据分析，按照用户需求，创建联合索引

# 8.索引本身失效
优化方案：删除重建