MySQL-索引及其原理

MySQL的索引

索引概述

索引是帮助MySQL高效获取数据的数据结构。在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用指向数据，这样就可以在数据结构上高效查找算法，这种数据结构就是索引。

优势
提高数据检索效率降低IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

劣势
索引也要占用空间‘
索引提高了查询效率但是却降低了更新表的速度

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构

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

二叉树：顺序插入时，会形成一个链表，查询性能大大降低。大数据情况下，层级较深，检索速度慢

红黑树：大数据情况下，层级较深，检索速度慢
B-Tree：多路平衡查找树

B+Tree

相对较B-Tree
1、所有的数据都会出现在叶子节点
2、叶子节点形成一个单向链表

MySQL中的索引结构
在B+Tree的基础上，增加了一个指向叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能

哈希索引
1、哈希索引只能用于对等比较(=,in)，不支持范围查询
2、无法利用索引完成排序操作
3、查询效率高，通常只需要一次检索就可以了，效率高于B+Tree

为什么InnoDB存储引擎选择B+Tree索引结构
相比较二叉树，层级更少，搜索效率高
相对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度。导致性能降低
相对于Hash索引，Hash索引不支持范围

索引分类

主键索引：针对于主键创建的索引(唯一)
唯一索引：避免对于同一个表中某列中的值重复
常规索引：快速定位特定数据
全文索引：全文索引查找的是文本中的关键字，而不是比较索引中的值

在InnoDB存储引擎中，根据索引的存储形式
聚集索引：将数据存储与索引放到一块，索引的叶子节点保存了行数据(必须有)
非聚集索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应主键

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

索引语法

创建索引
CREATE [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 [GLOBAL|SESSION] STATUS命令提供服务器状态信息
show global status like ‘com_______’

慢查询日志
慢查询日记记录了所有执行时间超过指定参数(long_query_time,单位:秒 ,默认10秒)的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:
show variables like ‘slow_query_log’
开启MySQL慢日志查询开关 slow_query_log = 1;
设置慢日志时间为2秒,SQL语句执行时间超过两秒,就会视为慢查询,记录慢查询日志 long_query_time = 2

profile
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数,能够查看当前MYSQL是否支持profile操作:
select @@have_profiling;
默认profiling是关闭的,可以通过set语句在session/global级别开启profiling:
set profiling = 1;
查看是否开启 select @@profiling;
查看每一条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 select 字段列表 from 表名 where 条件
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
possible_key:显示可能应用在这张表上的索引,一个或多个
key:实际使用的索引,如果为null,则没有使用索引
key_len:表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下,长度越短越好
rows:Mysql认为必须执行查询的行数,在innodb引擎中是一个估计值,可能并不总是准确的
filtered:表示返回结果的行数占需要读取行数的百分比,值越大越好

索引使用

最左前缀法则
如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列
如果跳跃某一列，索引将部分失效(后面的字段失效)
范围查询
联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效
索引列失效
不要在索引列上进行运算操作，索引将失效
字符串不加引号
字符串类型字段使用时，不加引号，索引将失效
模糊查询
如果只有尾部模糊，那么索引将不会失效，如果头部模糊则索引将会失效
or连接的条件
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引将不会被使用
数据影响
如果MySQL评估走索引比较慢则不使用索引
SQL提示
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的
use/ignore/force index
覆盖索引
尽量使用覆盖索引，不要select *
前缀索引
当字符串类型为字符串时，有时需要索引很长的字符串，这会让索引变得很大，查询时候浪费大量磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率

联合索引的结构

索引设计原则

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