Mysql之索引

索引是一个可以加快Mysql查询速度的数据结构。

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

如何创建索引

CREATE [ UNIQUE（唯一索引）] INDEX index_name ON table_name ( index_col_name,... ) ;

UNIQUE是唯一索引，不加就是普通索引

SHOW INDEX FROM table_name ;查看当前表的所有索引

DROP INDEX index_name ON table_name ; 删除索引

索引的优点和缺点

优点：

• 可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因
• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。（排序更快）
• 将随机IO变为顺序IO。原始数据在磁盘上是乱序存储的，加了索引可以理解为给这些数据加了目录，读取就相当于是快而且有序的了。
• 可以加速表和表之间的连接

缺点：

• 索引列也是要占用空间的
• 虽然查询的速度快了，但是更新表的速度会很慢（insert，update等操作）

索引分类

mysql数据库的分配

• 主键索引：针对表中主键创建的索引，默认自动创建，只可以有一个，一般聚集索引就是主键索引，关键字是primary
• 唯一索引：不允许出现索引值相同的行，可以有多个，关键字是unique
• 常规索引
• 全文索引：全文索引查找的是文本中的关键词，而不是比较索引中的值，关键字是fulltext，用的不多

InnoDB存储引擎分类

聚集索引

表中的数据和索引放在一起，索引结构的叶子结点中存储的是一整行的数据（更专业一点应该是行的物理位置），找到索引也就找到了数据，必须有且只有一个

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引（rowid是一个伪列，它并不实际存在于表中，存储的是行数据的物理地址信息编码，就是说可以通过它rowid定位到行）

辅助索引

数据和索引是分开存储的，索引结构的叶子结点指向数据的对应行（存储的一般是主键值），经常会涉及到回表操作。回表：当我们查的列没有包含在辅助索引中时，需要二次查询聚集索引，增加开销。

B+索引

• B+索引是大部分Mysql存储引擎的默认存储类型

• 非叶子结点只保存索引，叶子结点存储索引和数据项，并且在叶子结点形成双向循环链表。

• 只需要对树进行搜索即可，因此查找速度快很多。除了用于查找，还可以用于排序和分组

• 可以指定多个列作为索引列，多个索引列共同组成键。

• InnoDB 的 B+Tree 索引分为聚集和辅助索引。

哈希索引

• 不支持范围查询，不能完成排序操作
• 在没有hash冲突的情况下，只需要检索一次就可以查询到目标数据
• Innodb虽然不支持哈希索引，但是有一个功能叫自适应哈希，就是当某个索引值经常被使用时，就会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，增加查询效率。
• Memory引擎支持hash索引

为什么使用B+索引

• 相比于二叉树来说，B+索引层级更少，搜索的效率高一些
• 对于B树，无论是叶子结点或是非叶子结点，都会保存数据，单个节点的体积就会很大，每一个磁盘页中存储的节点数就会减少，硬盘一次加载的节点就少，查询时IO次数就会多。而B+树只有叶子结点存储数据，非叶子结点只存索引，叶子结点还会形成双向链表，非叶子结点体积就很小，硬盘一次加载的节点就很多，查询的IO次数就会少。
• 对于hash索引，B+索引支持范围匹配以及排序。
• 相比于跳表skipList，虽然时间复杂度都是logn，但是B+树的数据分布更均匀，检索效率更高
  • 跳跃表是通过二路分治的方式实现logN，B+Tree是通过多路分治的方式实现logN
  • 当数据表的数据足够多的时候，B+tree的根节点到任何一个叶子节点的路径是固定的。而skiplist的头节点到目标节点的路径是不固定的。所以检索的value越大，skiplist的路径就越深，磁盘的io次数就越多。
  • B+tree的所有叶子节点构成了一个双向循环链表，每一块叶子节点可以存储一条或者多条数据。这种结构不管是一条记录、还是多条记录查询都能节省磁盘IO。skiplist的每一个节点只存储一条记录，对于一条记录的查询是比较节省磁盘io，对于多条记录的查询，skiplist的磁盘IO次数会比B+tree要多。

sql性能分析的几种方式

• SQL执行频率

  show [session|global] status like ‘com__(七个下划线)_’;可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次

  通过这个指令我们可以查看当前数据库是以查询为主还是更新为主。如果是以查询为主，那么就要考虑对数据库的索引进行优化了。

• 慢查询日志。定位需要对哪些查询语句进行优化。

  MySQL的配置文件（/etc/my.cnf）中配置如下信息

  # 开启MySQL慢日志查询开关
  slow_query_log=1
  # 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
  long_query_time=2
  

  日志信息所在：/var/lib/mysql/localhost-slow.log

  这样在慢查询日志中就会记录那些查询效率较低的sql语句

• profile ：这个可以让我们知道sql语句运行时的时间都消耗在哪里了，用的不是很多

• explain：获取sql的执行计划

explain

使用方式：直接在sql语句前面加explain，或者加desc，作用基本一致，使用expalin多一些

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

各个字段的含义：

• id：select查询的序列号。表示查询中执行select子句或者是操作表的顺序，当id相同的时候，执行的顺序是从上往下，当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 ：表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好

最左前缀法则

前提是使用联合索引（关联了很多列），最左前缀法则就是查询必须包括索引的最左列，并且在查询时如果跳过某一列，这一列后面的字段索引都会失效。举例子：我们创建联合索引，索引列从左往右依次是name，age和sex三个字段，如果我们查询age和sex，整个联合索引就会失效，因为跳过了最左侧name字段，如果我们查name和sex，那么只有name索引会生效，因为跳过了age列，sex索引会失效。

索引失效的情况

• 刚才说过的最左前缀法则有可能会导致索引失效
• 范围查询：在联合索引中，当出现（>或者<），在其右侧的列索引就会失效。举例子：select * from user where age>30 and name=“wkx”; name字段索引就会失效。解决方案：改成 >=
• 在索引列上进行运算操作 ， 比如name列是一个索引，我们使用select * from user where substring(name,10,3) = ‘wkx’;
• 字符串类型字段使用时，不加引号，索引将失效
• 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效
• 使用or时， 如果or两侧有一列没有被创建索引，那么涉及的索引就都会失效，只有两侧都有索引的情况下，索引才会生效。
• 如果MySQL评估使用索引比全表更慢，则不使用索引。比如name列全是null值，我们查询使用is null就不会走索引了。再比如我们条件是age<1000岁，也不会走索引。

索引的使用规则

• 数据量很大，比如超过100多万就可以考虑建立索引
• 对查询比较频繁的表建立索引
• 针对where或者order by之后的字段建立索引
• 尽量建立唯一索引
• 字符串长度太大的话，可以考虑建立前缀索引
• 尽量使用联合索引，查询的时候使用覆盖索引，可以避免回表
• 当然了索引也不是越多越好，毕竟占空间
• 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束，方便数据库优化器选择索引

索引的优化

1. 独立的列

在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。

例如下面的查询不能使用 id 列的索引:

SELECT id FROM 表 WHERE id + 1 = 5;

2. 多列索引

在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actor WHERE actor_id = 1 AND film_id = 1;

3.前缀索引

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

create index idx_email_5 on tb_user(email(5));

前缀的长度需要根据索引的选择性来决定，选择性是指不重复的索引值（基数）和数据表的记录总数的比值。比如对于唯一索引来说，它的选择性就是1

select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;

4.覆盖索引

覆盖索引是指 查询的时候使用了索引，并且需要返回的列，在该索引中都可以找得到，这样的话就可以避免回表

优点

• 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
• 一些存储引擎(例如 MyISAM)在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用(通常比较费时)。
• 对于 InnoDB 引擎，若辅助索引能够覆盖查询了，那么就不用访问主索引了。

回表：一般就是我们先通过辅助索引找到行数据的id，然后通过id去聚集索引查到整个行数据。

冗余索引

冗余索引指的是索引的功能相同，比如已存在索引（A）的情况，ID为主键列时，索引（A,ID）是冗余索引

一般应该尽量扩展已有的索引而不是创建新索引。大部分情况都不需要冗余索引。

MySQL 5.7 版本后，可以通过查询 sys 库的 schema_redundant_indexes 表来查看冗余索引