“这是我参与8月更文挑战的第6天，活动详情查看：8月更文挑战”

1. 索引的作用

2. 索引的分类（算法）

BTree
- 默认使用的索引类型
R 树
Hash
FullText
GIS
- 地理位置索引

3. BTree 索引算法演变（了解）

B 树 -> B+ 树 -> B* 树

4. BTree 索引功能上的分类

4.1 辅助索引

如何生成：

提取索引列的所有值，进行排序；
将排好序的值，均匀的存放在叶子节点，进一步生成枝节点和根节点；
在叶子节点中的值，都会对应存储主键 ID；

4.2 聚集索引

如何生成：

MySQL 会自动选择主键作为聚集索引列，没有主键会选择唯一键，如果都没有会生成隐藏的；
MySQL 进行存储数据时，会按照聚集索引列值的顺序，有序存储数据行；
聚集索引直接将原表数据页，作为叶子节点，然后提取聚集索引列向上生成枝和根；

4.3 聚集索引和辅助索引的区别

表中任何一个列都可以创建辅助索引，在你有需要的时候，只要名字不同即可；
在一张表中，聚集索引只能有一个，一般是主键；
副主索引叶子节点只存储索引列的有序值 + 聚集索引列值；
聚集索引，叶子节点存储的是有序的整行数据；
MySQL 的表数据存储是聚集索引组织表；

5. 辅助索引细分

5.1 单列辅助索引

5.2 联合索引（覆盖索引）

5.3 唯一索引

6. 索引树高度

索引树高度应当越低越好，一般维持在 3-4 最佳。

6.1 数据行数较多

分表：parttion（目前用的比较少了）
分片、分布式架构

6.2 字段长度

业务允许的条件下，尽量选择字符长度短的列作为索引列
业务不允许，采用前缀索引

6.3 数据类型

char 和 varchar
enum

7. 索引的命令操作

7.1 查询索引

使用 desc table 命令查看索引：

mysql> desc student;
+--------+---------------------+------+-----+-------------------+----------------+
| Field  | Type                | Null | Key | Default           | Extra          |
+--------+---------------------+------+-----+-------------------+----------------+
| id     | int(11)             | NO   | PRI | NULL              | auto_increment |
| sname  | varchar(64)         | NO   |     | NULL              |                |
| age    | tinyint(3) unsigned | NO   |     | 0                 |                |
| sex    | char(4)             | NO   |     | NULL              |                |
| intime | datetime            | NO   |     | CURRENT_TIMESTAMP |                |
+--------+---------------------+------+-----+-------------------+----------------+
5 rows in set (0.01 sec)

PRI：主键索引
MUL：辅助索引
UNI：唯一索引

还可以使用 show index from table 命令查看索引：

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

7.2 创建索引

-- 单列辅助索引
mysql> alter table student add index idx_name(sname);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 联合索引
mysql> alter table student add index idx_name_sex(sname,sex);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 创建唯一索引
mysql> alter table student add unique index idx_name_unique(sname);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 创建前缀索引
mysql> alter table student add index idx_name_pre(sname(5));
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

7.3 删除索引

mysql> alter table student drop index idx_name_pre;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

8. 执行计划

8.1 作用

将优化器选择后的执行计划截取出来，便于管理判断语句的执行效率。

8.2 获取执行

desc SQL 语句
explain SQL 语句

mysql> explain select * from student where id > 3;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

8.3 分析执行计划

8.3.1 table

表名

8.3.2 type

查询类型：

全表扫描：ALL
索引扫描：index，range，ref，eq_ref，const(system)，NULL

index：全索引扫描
range：索引范围扫描（<，>，<=，>=，between and，or，in，like）
ref：辅助索引等值查询
eq_ref：多表连接时，字表使用主键或唯一列作为连接条件
const(system)：主键索引或者唯一索引的等值查询

8.3.3 possible_keys：可能会用到的索引

8.3.4 keys：真正选择了的索引

8.3.5 key_len：索引覆盖长度

utf8mb4 varchar(10) => 可以存 10 个中文，或 10 个英文，或 10 个数字，最多 40 个字节。

varchar(20) utf8mb4

能存 20 个任意字符
不管存储的是字符、数字、中文，都有 1 个预留字符，最大预留长度是 4 个字节
对于中文，1 个占 4 个字节
对于数字和字母，1 个实际占用大小是 1 个字节。
select length() from table_name;

key_len 长度计算

字符集 utf8mb4：

varchar(10)：没有 not null(1) + 4 * 10 + 2（前后开始符、结束符）= 43
char(10)：没有 not null(1) + 4 * 10 = 41
int ：没有 not null(1) + 4 = 4

用途：判断联合索引的覆盖长度，一般情况是越长越好

8.3.6 Extra

重点关注：Using filesort

出现 Using filesort，说明在查询中有关排序的条件列没有合理的应用索引，比如：

order by
group by
distinct
union

关注 key_len 长度

9. explain(desc) 使用场景（面试题）

你做过哪些优化？
你用过什么优化工具？
你对索引这块怎么优化的？

MySQL 出现性能问题，我总结有两种情况：
1. 应急性的慢：突然夯住；
  1. 处理过程：
    1. show processlist；获取到导致数据库 hang 的语句
    2. explain 分析 SQL 的执行计划，有没有走索引，索引的类型情况
    3. 建索引，改语句
2. 一段时间慢（持续性的）
  1. 记录慢日志 slowlog，分析 slowlog
  2. explain 分析 SQL 的执行计划，有没有走索引，索引的类型情况
  3. 建索引，改语句

10. 索引应用规范

10.1 建立索引的原则

建表必须要有主键，一般是无关列，自增长
经常作为 where 条件列，order by，group by，join on，distinct 的条件
最好使用唯一值多的列作为联合索引前导列，其他的按照联合索引优化细节来做
列值长度较长的索引列，我们建议使用前缀索引
降低索引条目，一方面不要创建没用索引，不常使用的索引清理
索引维护要避开业务繁忙期
小表不建索引

10.2 不走索引的情况

没有查询条件，或者查询条件没有建立索引
查询结果集是原表中的大部分数据，应该是 25% 以上。
索引本身失效，统计数据不真实
查询条件使用函数在索引列上，或者对索引列进行计算，运算符包括（+，-，、*，/）等
隐式转换导致索引失效。
<>，not in 不走索引（辅助索引）
like "%aa%" 百分号在最前面不走
联合索引

10.3 联合索引 t1(id,k1,k2,k3) idx(k1,k2,k3)

在 where 条件中都是等值的 where k1=xx k2=xx k3=xx
1. 无关 where 条件的顺序，只要把控建索引时，需要把唯一值较多的放在最左侧
在条件查询中没有最左列条件时，没有 k1 列的查询，都是不走索引的
如果查询中出现（> < >= <= like）
1. a= and b<xxx and c=xxx
2. 建索引时，将等值查询条件往前放，不等值的放在最后
多子句时，where c xxx order by a,b
1. idx(c,a,b)

MySQL 学习（6）—— 索引及执行计划 | 8月更文挑战