[toc]

索引的算法

索引的算法类型

BTree索引（Innodb存储引擎）
- Btree
- B+tree(目前MySQL多为次算法)
- B*tree
HASH索引
FULLTEXT索引
RTree索引

Btree算法

三路Btree

根节点
枝节点
叶子节点

只要使用Btree算法，则证明该字段被创建索引了

select * from tb where name='xxx';
50 zhang3 74 f

select * from tb1 where id=30                # 精确查询，产生3次IO
select * from tb1 where id>15 and id<30      # 范围查询，产生9次IO

B+tree算法

1 2	select * from tb1 where id=30 # 精确查询，产生3次IO select * from tb1 where id>15 and id<30 # 同样的范围查询，产生5次IO

B+tree和Btree的区别

在叶子节点上，添加了相邻节点的指针
优化了范围查询

B*tree算法

索引管理

索引类型

主键索引
- 联合索引
唯一键索引
- 前缀索引
- 联合索引
普通索引
- 前缀索引
- 联合索引

索引的”增” “删” “查”

增

# 语法格式
alter table 表名 add 索引类型 索引名(字段名);

# 创建普通索引
## 语法格式
alter table 表名 add index 索引名(字段名);
## 示例
alter table city add index idx_population(population);

# 创建主键索引
## 语法格式
alter table 表名 add primary key(字段名);
## 示例
alter table student add primary key(id);

# 创建唯一键索引
## 语法格式
alter table 表名 add unique key 索引名(字段名);
## 示例
alter table student add unique key uni_doa(doa);

# 判断该列能否创建唯一键索引
## 查询此字段有多少个值
select count(doa) from student3;
## 查询此字段去重后的值
select count(distinct(doa)) from student3;
## 对比两个值，如果一致则是唯一值

删

# 删除普通索引和唯一键索引
## 语法格式
alter table 表名 drop index 索引名;
## 示例
alter table student3 drop index uni_doa;

# 删除主键索引
## 语法格式
alter table 表名 drop primary key;
## 示例
alter table student drop primary key;

查

# 查询表结构
desc 表名;
+--------+---------------------------+------+-----+
| Field  | Type                      | Null | Key |
+--------+---------------------------+------+-----+
| id     | int(10) unsigned zerofill | NO   | PRI |
| name   | varchar(10)               | NO   |     |
| age    | tinyint(3) unsigned       | NO   |     |
| gender | enum('f','m')             | NO   |     |
| phone  | char(11)                  | NO   | UNI |
| doa    | datetime                  | NO   | UNI |
| status | enum('0','1')             | NO   |     |
+--------+---------------------------+------+-----+

# 查询表索引结构
show index from 表名;

前缀索引

# 作用
给内容比较多或比较长的字段创建索引，提升创建索引的排序效率

# 普通索引创建前缀索引
## 语法格式
alter table 表名 add 索引类型 索引名(字段名(索引值));
## 示例
alter table student4 add index idx_name(name(3));

# 唯一键索引创建前缀索引
## 语法格式
alter table 表名 add 索引类型 索引名(字段名(索引值));
## 示例
alter table student4 add unique key uni_name(name(2));

联合索引

原则：把最常用来做为条件查询的列放在最前面

# 创建普通索引的联合索引
## 语法格式
alter table 表名 add 索引类型 索引名(字段名,字段名,字段名);
## 示例
alter table people add index idx_all(gender,age,money);
desc people;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int(11)       | YES  |     | NULL    |       |
| name   | varchar(20)   | YES  |     | NULL    |       |
| age    | tinyint(4)    | YES  |     | NULL    |       |
| money  | int(11)       | YES  |     | NULL    |       |
| gender | enum('m','f') | YES  | MUL | NULL    |       |
+--------+---------------+------+-----+---------+-------+

# 创建主键联合索引
## 语法格式
alter table 表名 add 索引类型(字段名,字段名);
## 示例
alter table people add primary key(id,name);
desc people;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int(11)       | NO   | PRI | NULL    |       |
| name   | varchar(20)   | NO   | PRI | NULL    |       |
| age    | tinyint(4)    | YES  |     | NULL    |       |
| money  | int(11)       | YES  |     | NULL    |       |
| gender | enum('m','f') | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+

# 唯一键联合索引
## 语法格式
alter table 表名 add 索引类型 索引名(字段名,字段名,字段名);
## 示例
alter table people add unique key uni_all(gender,age,money);
desc people;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int(11)       | NO   |     | NULL    |       |
| name   | varchar(20)   | NO   |     | NULL    |       |
| age    | tinyint(4)    | YES  |     | NULL    |       |
| money  | int(11)       | YES  |     | NULL    |       |
| gender | enum('m','f') | YES  | MUL | NULL    |       |
+--------+---------------+------+-----+---------+-------+

# 注意
联合索引在查询时，必须按照创建索引的顺序进行查询，否则，不走索引
## 示例
gender,age,money
a b c
where a= b= c=
where a= b=
where a= c=
where b= c=
where b= a= c=

索引建立原则和规范

优先选择唯一键索引
1
2
3
# 检查是否可创建唯一索引
select count(字段名) from 表;
select count(distinct(字段名)) from 表;
- 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

重复值较多情况下，优先选择使用联合索引

为经常需要排序、分组和联合操作的字段建立索引

1 2	经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作

经常查询
列值的重复值少

尽量使用前缀索引（在唯一键或联合索引基础之上创建前缀）

注意：

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

企业问题

网站响应速度很慢？如何排查？如何处理？

网站的服务器负载高
- stress是Linux系统压力测试工具，这里我们用作异常进程模拟平均负载升高的场景。
- mpstat是多核CPU性能分析工具，用来实时检查每个CPU的性能指标，以及所有CPU的平均指标。
- pidstat是一个常用的进程性能分析工具，用来实时查看进程的CPU，内存，IO，以及上下文切换等性能指标。
- 查找出哪个进程占用CPU 内存磁盘比较多，就看对应进程的日志
代码逻辑问题、代码有bug
- 查看程序日志
- 日志内容交给开发
网络带宽、网络波动
- 升级带宽
- 指定地区放一台服务器，打通内网，做缓存（CDN）
缓存失效
- 错峰设置缓存失效周期

数据库查询速度慢（慢查询）

找出哪一条SQL语句执行的慢（开启慢查询日志）

[mysqld]
# 指定是否开启慢查询日志
slow_query_log = 1|ON 0|OFF

# 指定慢日志文件存放位置（默认在data）
slow_query_log_file=/application/mysql/data/slow.log

# 设定慢查询的阀值(默认10s)
long_query_time=0.05

# 不使用索引的慢查询是否记录到日志
log_queries_not_using_indexes

# 查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
min_examined_row_limit=100（鸡肋）

分析SQL语句为什么慢

全表扫描
1
type:ALL 全表扫描
该字段没有创建索引
有索引但是没走索引

desc select * from city2 where population<100;
+----+-------------+-------+------------+------+---------------+
| id | select_type | table | partitions | type | possible_keys |
+----+-------------+-------+------------+------+---------------+
| 1  | SIMPLE      | city2 | NULL       | ALL  | NULL          |
+----+-------------+-------+------------+------+---------------+

explain select * from city2 where population<100;
+----+-------------+-------+------------+------+---------------+
| id | select_type | table | partitions | type | possible_keys |
+----+-------------+-------+------------+------+---------------+
| 1  | SIMPLE      | city2 | NULL       | ALL  | NULL          |
+----+-------------+-------+------------+------+---------------+

SQL语句执行效率级别

按顺序排列级别

ALL
1
全盘扫描，效率最低

index

全索引扫描

# 示例
explain select population from city;
+----+-------------+-------+------------+-------+
| id | select_type | table | partitions | type  |
+----+-------------+-------+------------+-------+
| 1  | SIMPLE      | city  | NULL       | index |
+----+-------------+-------+------------+-------+

range

通常我们认为只要一条SQL语句优化到range级别

# 示例
explain select * from city where population<1000;
+----+-------------+-------+------------+-------+
| id | select_type | table | partitions | type  |
+----+-------------+-------+------------+-------+
| 1  | SIMPLE      | city  | NULL       | range |
+----+-------------+-------+------------+-------+
explain select * from city where countrycode in ('CHN','USA');
explain select * from city where countrycode='CHN' or countrycode='USA';

ref

使用联合查询时，才会出现的级别

# 示例
explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
+------+--------------+------------+------+
| id   | select_type  | table      | type |
+------+--------------+------------+------+
|  1   | PRIMARY      | city       | ref  |
|  2   | UNION        | city       | ref  |
| NULL | UNION RESULT | <union1,2> | ALL  | 
+------+--------------+------------+------+
select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

eq_ref

只有在连表查询时，并且使用 join on语句才能达到

# 示例
A join B on 条件

const

主键索引使用精确查询

# 示例
explain select * from city where population=1000;

system

主键索引使用精确查询

# 示例
explain select * from city where id=1;

null

整个表中没有该数据

# 示例
explain select * from city where population>1000000000000000000000;

总结
- key_len: 越小越好
- 尽量使用前缀索引（在唯一键或联合索引基础之上创建前缀）
- rows: 越小越好

不走索引的情况

全表扫描

1 2	# 不接条件(不建议使用)，请在后面加上where条件 select * from city;

没有创建索引

1 2	# 效率低，建议给需要经常查询的字段创建索引 explain select * from city where district='shanghai';

有索引，不走索引

查询的结果集大于原表的数据25%以上

# 示例
explain select * from city where population>100;

# 优化：翻页方式解决
explain select * from city where population>100 limit 120,60;

使用字段进行计算

# 示例
explain select * from city where id-1=10;

# 优化：程序员，杀了祭天

隐式转换导致不走索引

# 示例
## 创建示例表
create table test_index(id int,name varchar(10),phone char(11));
## 创建索引
alter table test_index add index idx_phone(phone);
## 插入数据
insert into test_index value(1,'aaa','12345678911');
insert into test_index value(2,'bbb','12341678911');
insert into test_index value(3,'ccc','12341578911');
## 查询表内容
select * from test_index;
+------+------+-------------+
| id   | name | phone       |
+------+------+-------------+
| 1    | aaa  | 12345678911 |
| 2    | bbb  | 12341678911 |
| 3    | ccc  | 12341578911 |
+------+------+-------------+
## 查看索引状态
explain select * from test_index where phone=12341578911;
mysql> select * from test_index where phone='12341578911';
+------+------+-------------+
| id   | name | phone       |
+------+------+-------------+
| 3    | ccc  | 12341578911 |
+------+------+-------------+

# 优化：查看建表语句，查看该字段的数据类型，查询时，根据数据类型选择是否加引号
explain select * from test_index where phone='12341578911';

不等于、not in

# 示例
explain select * from city where population <> 100;

# 优化：
## 1.加limit优化结果集
explain select * from city where population <> 100 limit 10;
## 2.使用联合查询
explain select * from city where population>100 union all select * from city where population <100;

使用like模糊查询，%在前面的

# 示例
explain select * from city where countrycode like '%HN';
explain select * from city where countrycode like 'C%N';
explain select * from city where countrycode like 'CH%';

# 优化：不要使用MySQL，使用elasticsearch（倒排索引）

使用联合索引时，不按创建索引的顺序查询

1
2
3

# 优化：
## 1.首先要对数据进行分析，了解用户喜好
## 2.按照顺序创建索引

索引本身失效
1
# 优化：删了重建