mysql索引

# 索引

## 概述
索引（index）是有序的数据结构。

优点：
高效获取数据，降低数据库IO成本
通过索引对数据进行排序，降低数据排序成本，降低CPU消耗

缺点：
占用磁盘空间
降低更新表的效率（INSERT,UPDATE,DELETE）

## 索引结构
索引在存储引擎层实现，不同的存储引擎有不同的索引结构。

|索引结构|描述|
|---|---|
|B+tree索引|最常见的索引类型，大部分引擎都支持B+树索引|
|hash索引|底层数据结构用哈希表实现，不支持范围索引|
|R-tree索引|空间索引，Myisam引擎的一个索引类型，使用较少。|
|Full-text索引|通过建立倒排索引快速匹配文档的方式。|

### 二叉树
定义一个根，根节点左边大于右边，每个节点只能分出两个节点，树形结构。
进行顺序插入时可能导致出现链表结构，一侧节点增加，另一侧节点没有数据，导致数据查询效率降低。
大量数据下，层级较深，检索速度慢。

平衡二叉树
当两边节点高度差大于1时进行选择和纠正，修正最新插入的节点。

### 红黑树

### B-Tree(多路平衡查找树)
每个节点存储N-1个KEY，和N个指针，指针指向分支节点。
每个KEY都存储数据，中间数向上分裂。

#### B+Tree
叶子节点存储数据，非叶子节点只负责索引。
所有数据在叶子节点通过指针形成单向链表，便于范围查找。
mysql的B+Tree树优化，增加一个指向相邻叶子节点的指针形成双向链表。
磁盘页的大小固定，不存储数据的情况下作为索引效率更高，底层形成链表支持范围查找。

### Hash结构
根据一定的hash算法，将键值换算成hash值，映射到对应的槽位，存储在hash表

## 索引的分类

|分类|含义|特点|关键字|
|---|---|---|---|
|主键索引|针对表中的主键创建的索引|默认自动创建，只能有一个|PRIMARY|
|唯一索引|避免同一个表中某列数据的值重复|可以有多个|UNIQUE|
|常规索引|快速定位特定数据|可以有多个|-|
|全文索引|全文索引查找的是文本中的关键字而不是索引中的值|可以有多个|FULLTEXT|

根据存储形式分为：
聚集索引：将数据存储与索引放到一块，索引结构的叶子节点保存了数据，必须有且只有一个。
二级索引：数据与索引分开，叶子节点关联对应的主键。

回表查询，通过二级索引查询到聚集索引，通过聚集索引查询到数据。

## 索引语法

### 创建索引
```sql
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...)
# 示例
# 常规索引
CREATE INDEX idx_user_name ON tb_user(name);
# 唯一索引
CREATE UNIQUE INDEX idx_user_username ON tb_user(username);
# 联合索引
CREATE INDEX idx_user_gen_dep_tit ON tb_user(gender,department,title);

```
### 查看索引
```sql
SHOW INDEX FROM table_name;
```

### 删除索引
```sql
DROP INDEX index_name ON table_name;
```

## SQL性能优化

```sql
# 语法,下划线代表模糊匹配次数
SHOW [SESSION|GLOBAL] STATUS LIKE 'Come_______'
SHOW global status LIKE 'Com_______';
```

### 慢查询日志
记录了所有执行时间超过指定参数（long_query_time,默认10秒）的所有SQL日志，慢查询日志默认没有开启。
```sql
SHOW VARIABLES LIKE 'slow_query_log'
# 修改my.cnf文件
# 开启Mysql慢查询日志开关
slow_query_log = 1
# 自定义慢日志的时间2秒
long_query_time = 2

# 修改完成后需要重启mysqld服务
# 日志位置/var/lib/mysql/xxx-slow.log
tail - f xxx-slow.log
```

### profile详情
```sql
# 确定服务器是否支持profile,YES代表支持
SELECT @@have_profiling;
# profile默认关闭，需要手动开启
select @@profiling;
SET [SESSION|GLOBAL] profiling = [0|1] 
# 查看每一条SQL的耗时基本情况
SHOW profiles;
# 查看指定query_id的语句各个阶段的耗时情况
SHOW profile for query query_id;
# 查看每个SQL CPU的耗费情况
SHOW profile cpu for query quert_id;
```

### explain执行计划
通过EXPLAIN或者DESC获取SELECT 语句的信息，执行过程。
```sql
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
id:表的执行顺序,相同执行顺序从上到下
select_type:SIMPLE(简单表，单表查询)，PRIMARY(主查询，外层查询)，UNION(UNION的第二个查询或者后面的查询)，SUBQUERY(SELECT/WHERE之后查询，包括子查询)
type:表示连接类型，性能由好到差为：NULL,system,const,eq_ref,range,index,all
possible_key:应用在该表上可能的索引，一个或多个
key:实际使用的索引，NULL没有使用
key_len:索引使用的字段长度
rows:执行查询的行数
filtered:返回结果的行数占读取行数的百分比，值越大越好
```

## 索引使用

验证索引效率。

最左前缀法则，需要满足索引最左边的字段，否则索引失效，如果中间跳过会导致索引部分失效，和条件顺序无关。

范围查询，出现（><），右边的索引失效，使用>=,<=。

索引列运算，索引失效。

字符串类型，查询时需要添加单引号，否则索引失效。

模糊查询，头部模糊查询，索引失效。

数据发布影响，使用索引比全表更慢，则不使用索引。

WHERE使用or，有一个条件不是索引则全部不使用索引。

索引字段是否NULL/NOT NULL与数据分布有关。

SQL提示，在SQL语句加入人为提示达到优化操作。
- use index: 指定使用的索引
`EXPLAIN SELECT * FROM tb_user use index(idx_user_name) WHERE name = 'chuck';`
- ignore index:指定不使用的索引
`EXPLAIN SELECT * FROM tb_user ignore index(idx_user_name) WHERE name = 'chuck';`
- force index:强制使用索引
`EXPLAIN SELECT * FROM tb_user force index(idx_user_name) WHERE name = 'chuck';`

覆盖索引，尽量使用索引列查询数据，减少使用SELECT *，没有索引的列需要回表查询，效率较低。

前缀索引，针对长字符串或者文本类型字段，取字符串的前面一部分建立索引，减少硬盘空间占用。
`create index idx_xxx on table_name(column(n));`
需要确定合适的前缀长度，需要根据字段的选择性。
```sql
select count(distinct email)/count(*) from tb_user;# 先去重，然后除以总的记录数，值越大越好，但是需要考虑性能和空间。
```

单列索引和联合索引的选择：
单列索引：一个索引只包含单个列
联合索引：一个索引包含多个列
业务场景中，如果存在多个查询条件，建议建立联合索引。

索引设计原则

- 针对数据量大，且查询比较频繁的表建立索引
- 针对常作为查询条件(where)，排序(order by),分组(group by)操作的字段设置索引
- 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
- 如果是字符串，针对字段的特点，建立前缀索引
- 尽量使用联合索引，减少单列索引，可以覆盖索引，减少回表
- 控制索引的数量，索引需要维护索引结构，影响增删改的效率
- 针对字段是否存储NULL,可以在建立设置NOT NULL，有利于mysql优化。