MySQL进阶-索引

1.基本语法

create index 索引名 on 表名(字段名)
drop index 索引名 on 表名
show index from 表名

2.索引结构

由存储引擎实现，包括：

B+tree索引

所有元素都会出现在叶子结点，非叶结点起索引作用，叶子结点之间用指针连接。
hash索引，不支持范围查找
FullText索引
空间数据索引

3.索引分类

主键索引
唯一索引避免某数据列中的值重复
普通索引
全文索引
根据存储形式又分为：
聚集索引（聚簇索引）数据与索引存在一块只会有一个
二级索引数据与索引分开存储存主键
若存在主键主键索引就是聚集索引
若不存在主键使用第一个唯一索引作为聚集索引
若都无 INNODB自动生成一个rowid作为隐藏的聚集索引
覆盖索引要查的数据列正好有索引无需回表
联合索引使用表中的多个字段创建索引

在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成

联合索引，一定要扯最左匹配!

最左匹配

所谓最左原则指的就是如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配，值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配。
假设，我们对(a,b)字段建立一个索引，也就是说，你where后条件为

a = 1
a = 1 and b = 2
是可以匹配索引的。但是要注意的是~你执行

b= 2 and a =1
也是能匹配到索引的，因为Mysql有优化器会自动调整a,b的顺序与索引顺序一致。
相反的，你执行

b = 2
就匹配不到索引了。
而你对(a,b,c,d)建立索引,where后条件为

a = 1 and b = 2 and c > 3 and d = 4
那么，a,b,c三个字段能用到索引，而d就匹配不到。因为遇到了范围查询！

最左匹配的原理？
假设，我们对(a,b)字段建立索引，那么入下图所示

如图所示他们是按照a来进行排序，在a相等的情况下，才按b来排序。

因此，我们可以看到a是有序的1，1，2，2，3，3。而b是一种全局无序，局部相对有序状态!
什么意思呢？
从全局来看，b的值为1，2，1，4，1，2，是无序的，因此直接执行b = 2这种查询条件没有办法利用索引。

从局部来看，当a的值确定的时候，b是有序的。例如a = 1时，b值为1，2是有序的状态。当a=2时候，b的值为1,4也是有序状态。
因此，你执行a = 1 and b = 2是a,b字段能用到索引的。而你执行a > 1 and b = 2时，a字段能用到索引，b字段用不到索引。因为a的值此时是一个范围，不是固定的，在这个范围内b值不是有序的，因此b字段用不上索引。
综上所示，最左匹配原则，在遇到范围查询的时候，就会停止匹配。

4.性能分析

4.1 查看执行频次

show global status like 'Com_______';

4.2 慢查询日志

定位sql语句进行优化

开启慢查询日志

set global slow_query_log=1;        
```     
- 查看慢查询日志            
- 设置慢查询日志的阈值              
```sql                                
set global long_query_time=1;             

4.3 profiling

用来记录每一句sql执行信息需要手动打开

4.4 explain

type: 性能指标

5. 索引使用原则

Warning

被频繁更新的字段应该慎重建立索引
尽可能的考虑建立联合索引而不是单列索引
冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。

6. 索引失效

最左匹配：查询条件中要有联合索引最左边的字段，否则无法使用索引。

范围查询：尽量使用大于等于否则右边条件失效
缺失字段：索引会失效
参与运算
字符串不加引号
模糊查询 xx% 不会失效 %xx失效前面加%就会失效
or 前后都要有索引
全盘扫描效率大于索引则索引失效

7. 索引使用

前缀索引当为一个字符串长度较长的字段建立索引时，可以取前一部分建立索引，这样可以大大节约索引空间，从而提高索引效率。
1
alter table table_name add index index_name(column(n))
至于前缀长度取到多少，可以使用
1
select count(distinct left(column, 长度))/count(*) from table_name;
这个比例可以用来衡量前缀索引的合理性。
单列索引VS联合索引
覆盖索引无需回表查询