定义

---

索引是一种数据结构，通过使用索引，可以加快检索、排序。
底层通常是BTree，通过BTree可以将无序的数据相对有序。
索引的缺点是：

1. 如果表中数据增删改，那么索引也要修改。
2. 索引占用物理空间。
3. 创建索引和维护索引耗费时间。

索引分类

---

1. 单值索引

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

2. 唯一索引

   索引列的值必须唯一，允许为空值

3. 复合索引

   一个索引包含多个列

基本语法

---

1. 创建

   create [unique] index index_name on table(column_name(length));
   alter table_name add [unique] index [index_name] on (column_name(length))

2. 删除

   drop index [index_name] on table_name;

3. 查看

   show index from table_name;

索引结构

---

1. BTree索引
   原理：

2. Hash索引

3. full-text索引

4. R-Tree索引

建立索引注意事项

---

1. 主键自动建立唯一索引
2. 频繁作为查询的字段应该创建索引
3. 外键建立索引
4. 频繁更新的字段不要建立索引
5. where条件里用不到的字段不创建索引
6. 优先组合索引
7. 表记录少不要建立索引
8. 经常增删改的表不要建立索引
9. 数据重复且平均的字段不要建立索引（比如说性别字段）

Explain关键字

---

Explain关键字用于查询MySQL如何执行SQL语句的，进而依此优化表结构或查询语句。

作用

• 表的读取顺序
• 数据读取操作的操作类型.
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

表的读取顺序

1. id相同，从上到下依次执行
2. id不同，由大到小依次执行

   数字大的优先执行，数字相同的顺序执行
   derived 衍生

select_type

1. Simple简单查询
2. Primary最外层查询标记为Primary
3. SubQUERY子查询
4. Derived
5. Union联合查询
6. Union Result

Type

访问类型：
最好到最差：

system > const > eq_ref > ref > range > index > ALL
至少达到range级别

1. system表只有一条记录（等于系统表）
2. const比较唯一索引（如where id = 1）
3. eq_ref唯一性索引扫描（如where t1.id = t2.id）
4. ref非唯一性索引扫描
5. range 索引范围查询（如查询索引大于10小于20）
6. index遍历全部索引
7. ALL全表查询

possibble_kesy和key

possible_keys显示可能应用在这张表的索引
key实际使用的索引
如果key为null，说明索引失效或不存在。

key_len

表示索引字段中使用的字节数，长度越短越好

ref

显示索引的那一列被使用了。如果可能，最好是const。

rows

根据表统计信息和索引选用情况，大致估算出找到所需的记录需要读取的行数。

extra

额外的重要信息。

1. Using Filesort ：文件排序（不要出现）

2. Using temporary：使用了临时表（order by和group by，不要出现这个）

   优化这两个，group by一定要按照索引的顺序和个数来。如idx_col1_col2，使用group by col1,col2，而不是group by col2，否则会使用临时表或文件排序（对索引外部排序）

3. Using index： 使用了索引

   覆盖索引：查询的列就是所建的索引

索引优化

---

2. 左连接右表建立索引，右连接左表建立索引
3. join语句，小结果集驱动大结果集。
4. 优先优化内层循环
5. 保证join字段已经被索引

索引失效

1. 全值匹配我最爱
2. 最佳左前缀法则：查询从索引的最左前列并且不跳过索引中的列
3. 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫
4. 存储引擎不能使用索引中范围条件右边的列
5. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select*
6. mysq|在使用不等于**(!=或者<>)**的时候无法使用索引会导致全表扫描
7. is null ,is not null也无法使用索引
8. like以通配符开头(’%abc… ')mysq|索引失效会变成全表扫描的操作（使用覆盖索引可以避免通配符匹配开头索引失效的问题）
9. 字符串不加单引号索引失效
10. 少用or,用它来连接时会索引失效

查询优化

1. 慢查询

   通过开启慢查询，可以把执行速度慢的SQL筛选出来

2. 小表驱动大表

3. order by 的顺序和复合索引顺序一致就不会产生filesort

   如index_AB 索引，使用order by A,B不会产生filesort ，使用order by B或者order by B,A就会filesort
   order by 不要和select * 一起使用
   尝试提高sort_buffer_size和max_length_for_sort_data参数

   

慢查询

慢查询用于筛选响应时间超过阈值的语句。阈值默认是10秒，可以配合explain语句进行分析。

select variables like ‘%slow_query_log%’;
查询是否开启慢查询

set global slow_query_log =1;
开启慢查询

如果要永久开启慢查询，则需要更改my.cnf文件

set global long_query_time = 3;
阈值设置为3秒

日志分析工具mysqldumpslow

Show Profile

mysql 提供用来分析当前会话中语句执行的资源消耗情况，用于SQL调优。

查询是否开启 show variables like ‘profiling’;
开启set profiling = on;
查看结果 show profiles;

诊断SQL:show profiles cpu,block io for query ;

如果结果表status中出现以下：
则说明出现问题。