MySQL_索引

索引概述

介绍

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

优缺点

索引结构

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

• B+Tree索引：最常见的索引类型，大部分引擎都支持B+Tree索引。
• Hash索引：底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询。
• R-Tree（空间索引）：空间索引是MYISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
• Full-text（全文索引）：是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene，Solr，ES
  

思考：为什么InnoDB存储引擎选择使用B+tree索引结构

 1.相对于二叉树，层级较少，搜索效率高；
 2.对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
 3.相对于Hash索引，B+tree支持范围匹配及排序操作；

索引分类

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

聚集索引选取规则

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

索引语法

创建索引

CREAT [UNIQUE | FULLTEXT] INDEX index_name ON table_name(index_col_name,...);

查看索引

SHOW INDEX FROM table_name;

删除索引

DROP INDEX index_name ON table_name;

SQL性能分析

sql执行频率

查询当前数据库增删改查的访问频次：SHOW SESSION/GLOBAL（当前会话/全局） STATUS LIKE 'COM_______(下划线个数代表字符个数)'

慢查询日志

查看慢查询日志是否打开：SHOW VARIABLES LIKE 'SLOW_QUERY_LOG'
慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位：秒，默认10秒)的所有SQL语句的日志。
MYSQL的慢查询日志默认没有开启，需要在MYSQL的配置文件(/etc/my.cnf)中配置如下信息：
	1.开启MYSQL慢查询日志开关：slow_query_log = 1
	2.设置慢日志的时间为2秒，sql语句执行时间超过2秒，就会被视为慢查询并记录日志：long_query_time = 2

profile详情

show profiles能够在做sql优化时帮助我们了解时间都耗费在哪里。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：SELECT @@have_profiling
默认profiling是关闭的，可以通过set语句在session/global级别开启profiling： SET profiling = 1
查看每一条sql的耗时基本情况：show profiles;
查看指定query_id的sql语句各个阶段的耗时清空：show profile for query query_id;
查看指定query_id的sql语句cpu的使用情况：show profile cpu for query query_id;

explain执行计划

EXPLAIN或者DESC命令获取MySql如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。
语法：直接在select语句前加关键字explain/desc

explain执行计划个字段含义
Id：
select查询的序列号，表示查询中执行select子句或者是操作表的顺序（ id相同，执行顺序从上到下；id不同，值越大，越先执行）。
select_type：
表示SELECT的类型，常见的取值有SIMPLE （简单表，即不使用表连接或者子查询）、PRIMARY （主查询，即外层的查询）、
UNION （ UNION中的第二个或者后面的查询语句）、SUBQUERY （ SELECT/WHERE之后包含了子查询）等
type：
表示连接类型，性能由好到差的连接类型为NULL、system, const. eq_ref. ref. range, index, all o
possible_key：
显示可能贿用在这张表上的索引，一个或多个。
Key：
实际使用的索引，如果为NULL,则没有使用索引。
Key_len：
表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
rows：
MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
filtered：
表示返回结果的行数占需读取行数的百分比，filtered的值越大厚好。

索引使用

一、验证索引效率
1.在未建立索引之前，执行如下sql语句，查看sql的耗时
2.创建索引
3.再次查询相同sql语句，查看sql的耗时
二、最左前缀法则
如果索引了多列（联合索引），要遵守最左前缀法则。
即查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引讲部分失效（后面的字段索引失效）。
三、索引失效
1.范围查询：联合索引中，出现范围查询（>,<）,范围查询右侧的索引失效
2.索引列运算：在索引列上进行运算操作，索引失效
3.字符串不加引号：字符串类型字段使用时，不加引号，索引失效
4.模糊查询：头部模糊查询，索引失效
5.or连接的条件：or两边都需要有索引，索引才不会失效
四、SQL提示：在SQL语句中加入一些人为的提示来达到优化操作的目的

MySQL索引设计原则

1. 针对于数据量较大，且查询比较频繁的表建立索引
2. 针对于常作为查询条件(where)、排序(order by）、分组(group by)操作的字段建立索引
3. 尽量选择区分度高的列(如：身份证号)作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
4. 如果是varchar,text类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表查询，提高查询效率
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价越大，会影响增删改的效率
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询