MYSQL索引

1.索引概述

1.介绍

索引（index）是帮助MYSQL高效获取数据的数据结构。数据库系统还维护着满足满足特定查找算法的数据结构，这种数据结构就是索引。

2.索引演示

3.索引的优缺点

2.索引的数据结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

我们平时所说的索引，如果没有特殊说明，都指B+树组织结构的索引；

1.索引结构（B-Tree）

B-Tree(多路平衡查找树)

以一颗最大度数为5的b-Tree为例（每个节点最多存储4个数据域，5个指针域）；

2.索引结构（B+Tree）

特点：所有的元素（数据）都会出现在叶子节点,叶子节点存放数据。

          叶子节点是一个单链表。

3索引结构（hash索引）

哈希索引就是采用一定的hsah算法，将键值换算成hash值，映射到对应的槽位上，然后存储在hash表上。

3.索引的分类

分类	含义	特点	关键字
主键索引	针对于表中的主键的索引	默认自动创建，只能有一个	primary
唯一索引	避免同一个表中的数据列中值重复	可以有多个	unique
常规索引	快速定位特殊数据	可以有多个
全文索引	全文查找的是在文本中的关键词，而不是比较索引的值	可以有多个	fulltext

在InnoDB中存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引	将数据存储和索引放到了一块，索引结构的叶子节点保存了整行数据	必须有，而且只有一个
二级索引	将数据和索引分开，索引结构的叶子节点关联的是对应的主键	可以存在多个

4.索引的语法

1.创建索引

create [unique(唯一索引)|fulltext（全文索引）] index 索引名称 on 表名（字段名1，...）;

2.查看索引

show index from 表名;

3.删除索引

drop index 索引名 on 表名;

5.SQL性能分析

1.SQL执行频率

show global status like 'Com_____';

2.慢查询日志

• 开启慢查询日志开关

slow_query_log=1;

• 设置慢查询日志时间为2秒，SQL语句执行时间超过两秒就会视为满查询，记录慢查询日志

long_query_time=2;

3.profile详情

通过 show profile 能够在做SQL优化的时候帮助我们了解时间都耗费在哪里去了。通过have_profliling参数，能够看到当前MYSQL是否支持profile操作：

SELECT @@have_profiling;

默认profiling是关闭的，可以通过set 语句在session/隔离global级别开启profile;

set profiling=1;

4profile查看耗时指令

show profile cpu for query_id;

查看每一条SQL的耗时基本情况

show profiles;

查询指定query_id的SQL语句各阶段的耗时情况

show profile for query query_id;

查询指定query-id的SQLCPU耗时情况

how profile cpu for query query_id;

5.explian执行计划

explian或者desc的命令获取MysSQL如何执行select语句信息，包括select语句执行过程中如何建立连接和连接顺序；

语法：在任意 select语句之前加上关键字explian/desc

explian select 字段列表 from 表名 where 条件;

6.索引的使用原则

索引使查询效率更高

1.最左前缀法则

如果索引多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是是查询索引的最左列开始，并不跳过索引中的列。

2.索引失效

1.跳跃某一列

如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

2.范围查询

在联合索引当中，出现范围查询（>,<）,范围查询右侧索引列失效

在使用联合索引使如果业务允许使用>=,<=,可避免有侧索引列失效

3.索引列运算

不要在索引列上进行运算操作，索引列失效。

3.字符串不加引号

字符串类型使用时，不加引号，索引列将失效；

4.模糊查询

如果仅仅是尾部进行模糊匹配，索引列不会失效。如果是头部进行匹配，索引失效

5.or连接的索引条件

用or分割开的条件，如果前面的索引列有索引，而后面的列没有索引，那么索引都不会用到（失效）

age字段没有索引

6.数据分布影响

如果MySQL使用评估使用索引比全表扫描更慢，则不要使用索引。

3.SQL提示

SQL提示是优化数据库的重要手段，简单来说，就是在SQL中加入一些人为的提示来达到优化操作的目的。

1.use index:

explian select *from 表名 use index(索引名) where 条件;

use index 是建议MySQL

2.ignore index:

explian select *from 表名 ignore index(索引名) where 条件;

忽略索引

3.force index:

explian select *from 表名 force index(索引名) where 条件;

强制MySQL使用给的索引

4.覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少

selelct *。

覆盖索引 ：查询返回的字段在该索引能够全部找到

5.前缀索引

语法：

create index 索引名 on 表名(字段名（n）);

6.单列&联合索引

单列索引：即一个索引包含了单个列

联合索引：即一个索引包含了多个列。

在于业务使用场景中，如果存在多个查询条件，考虑针对查询建立索引时，建议使用联合索引，而非单列索引。

1.单列索引的情况：

2.联合索引的情况：

7.索引的设计原则