mysql索引

什么是索引？

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

mysql支持的索引结构：

不同存储引擎对于索引的支持：

B+Tree ：

绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。 
红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

 

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。 

hash索引：

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

1.Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，...）
2.无法利用索引完成排序操作
3.查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索
引 

索引分类：

聚集索引&二级索引：

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

聚集索引选取规则:
1.如果存在主键，主键索引就是聚集索引。

2.如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
3.如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索 
引。 

回表查询：    

  先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。 

索引语法：

查看索引：show index from 表名;

创建索引：create [union/unique] index 索引名 on 表名(字段名)；

删除索引：drop index 索引名 on 表名;

sql性能分析：

1.查看sql执行频率：

show session/global status like 'com_______';

2.慢查询日志

 ①查看慢查询日志是否开启：show variables like 'slow_query_log';

 ②开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

 ③执行tail -f localhost-slow.log指令，实时查看执行时间超过2秒的sql语句。

3.查看profile详情

①查看profile是否打开：selct @@profiling;

②打开profile：set profiling = 1;

③通过如下指令查看指令的执行耗时：

4.explain

EXPLAIN 或者DESC命令获取MySQL 如何执行SELECT 语句的信息，包括在SELECT 语句执行过程中表如何连接和连接的顺序。 

Explain 执行计划中各个字段的含义:

索引的使用：

1.最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。 

注意：最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。

2.范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <。

3.索引列运算

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

4.字符串不加引号

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

5.模糊查询

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

6.or连接条件

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

7.数据分布影响

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

8.SQL提示

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

1.use index：建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进 
行评估）。

2.ignore index：忽略指定的索引。

3.force index：强制使用索引。

9.覆盖索引

尽量使用覆盖索引，减少select *。 那么什么是覆盖索引呢？覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

10.前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。 

①语法：create index 索引名 on 表名（字段名（n））;将字段名前5位截取出来，建立索引。

②前缀长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

索引设计原则：