mysql增加索引耗时 mysql添加索引反而变慢

如何优化SQL查询速度大多数人第一时间想到的就会是添加索引，但是添加了索引查询速度就一定会快么，无脑用索引去优化查询真的好么？这往往都是我们忽略的问题。

优点：

• 可以提高查询效率和性能
• 加快排序的效率
• 对于有依赖关系的子表和父表之间的联合查询时，可提高查询速度
• 唯一索引或主键索引可以保证数据库表的唯一性
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

缺点：

• 索引需要占用物理空间 （实际上索引也是一张表会占用一定内存，所以不是越多越好）
• 跟新数据时同时需要维护索引（假设一个字段对应多个索引字段更新时对应所有索引也需要变动，如果更新过多反而会降低更新速度）

索引结构

其实索引的本质就是一种数据结构，设计者希望通过优化查询算法的方式来提升数据库的查询效率。而如二分查找，二叉树查找等等算法都是需要特定数据结构来支持的。 所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法 这种ADT（Abstract Data Type），就是索引。（第一眼都差点被ADT这个词唬住）

目前大部分数据库系统及文件系统都采用B Tree或其变种B+Tree作为索引结构，而我们mysql中主要采用了B+tree的方式实现索引。说到这里就总能想到面试常问的一道题目，为什么不使用B-TREE呢？B+Tree的有点是什么。因为本菜鸟还是个刚毕业的小白所以时常会碰到这样的问题。下面就稍微分析一下B-tree和B+tree的优缺点。

我在这两个数据结构中都添加了20个数，看看他们有什么不同。这个网站大多数人应该都知道吧，是个学习数据结构不错的网站能很形象的帮我们呈现出数据的变化过程。

B-tree

B+tree

我们可以看出B+Tree最大的不同是非叶子节点只存储键值信息且所有叶子节点之间都有一个链指针。

数据记录都存放在叶子节点中。

B*树：在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3；虽然目前好像还没接触过但是了解一下概念以后遇到也不至于一片空白

这里我们来简单分析下为什么没有使用B-tree的原因，又上图我们可以看出B-tree是把数据块指针存储在每一个节点中的这样意味着每一个节点将比B+tree占用更多内存树会更深增大查询时的磁盘I/O次数。同等内存条件下B+tree能取到的索引会更多。我们知道操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页大小通常为4k），主存和磁盘以页为单位交换数据。。所以当加载索引时并不能一次性从磁盘加载到内存，而是逐步加载。这样就会会触发一个缺页异常。什么是缺页异常，就是此时程序要读取的数据不在主存中。此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。这样每加载一次就试一次磁盘I/O。而B+tree因为少了data数据块指针的情况下，相比就能记载更多的节点大大减少了磁盘IO的次数。虽然其遍历次数增加但是内存中的运行速度比磁盘快的多，所以反而更快。

MySQL索引实现：

我们以最常用的InnoDB存储引擎来分析，在这之前我们先看一下InnoDB与MyISAM的索引区别。不管是在工作中还是面试这两种存储引擎都是最常见的。他们两虽然都是用B+tree实现的却有很大的不同。

1.第一个重大区别是InnoDB的数据文件本身就是索引文件。在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引

2.MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

mysql默认表采用的是MyISAM存储引擎，而我们自己建的默认使用innoDB,我们看一下数据文件的区别：

MyISAM

innoDB

frm：表结构 ------------MYD：表数据-------------MYI:表索引

IBD：索引+数据

innodb与myisam简单区别：

• innodeb索引与data存放在一起.db文件,(.db+.frm).myisam(.frm表结构+.MYD表数据+.MYI表索引)
• innodb有事务，myisam没有事务。innodb默认行锁，myisam表锁。innodb同时也可以支持表锁，取决于索引。因为innodb索的是索引而不是数据。没有索引的情况下只能默认启用表级锁。
• innodb支持外键，myisam不支持

索引基础操作：

查看索引：show create table user; --------or---------show index from user;
删除索引：drop index_name on user;-----------or-----------alter TABLE users drop index name_index ;
注意：对于创建索引时如果是CHAR，VARCHAR类型，length可以小于字段实际长度。如果是blob 和 text 类型，必须指定length。

主键索引：

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。

单值索引：

即一个索引只包含单个列
create index indexName ON mytable(column);
alter table table_name add index index_name (column);

唯一索引：

唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
create unique index index_Name ON user(name(length)) ;
alter table user add unique(name(length));

联合索引：

create index indexName ON mytable(column1,column2);
alter table table_name add index index_name (column1,column2);

在我们了解过这些基础知识后最关键的还是生产环境中的使用问题，如何能把力气用在刀刃上发挥出它的最大性能。

什么情况下适合建立索引

1.表的主键、外键必须有索引；外键是唯一的，而且经常会用来查询 。主键会自动建立唯一索引
2.频繁作为查询条件的字段
3.经常与其他表进行连接的表，在连接字段上应该建立索引；经常连接查询，需要有索引
4.经常出现在Where子句中的字段，加快判断速度，特别是大表的字段，应该建立索引，建立索引，一般用在select ……where f1 and f2 ，我们在f1或者f2上建立索引是没用的。只有两个使用联合索引才能有用
5.经常用到排序的列上，因为索引已经排序。
6.经常用在范围内搜索的列上创建索引，因为索引已经排序了，其指定的范围是连续的
7.单键和组合索引选择问题组合索引性价比更高

什么场景不适合创建索引

1.对于那些在查询中很少使用或者参考的列不应该创建索引。这是因 为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
2.对于那 些只有很少数据值的列也不应该增加索引。因为本来结果集合就是相当于全表查询了，所以没有必要。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比 例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
3.对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
4.当修改性能远远大于检索性能时，不应该创建索 引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因 此，当修改性能远远大于检索性能时，不应该创建索引。
5.不会出现在where条件中的字段不该建立索引。
6.过滤性不好的，如性别。。。

explain:

EXPLAIN可以帮助开发人员分析SQL问题,explain显示了mysql如何使用索引来处理select语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句.使用方法,在select语句前加上Explain就可以了

mysql在执行一条查询之前,会对发出的每条SQL进行分析优化,决定是否使用索引或全表扫描如果发送一条：

select * from blog where false ；

Mysql是不会执行查询操作的,因为经过SQL分析器的分析后MySQL已经清楚不会有任何语句符合操作;

mariadb> explain select * from user where age=(select max(age) from user);
+----+-------------+-------+------+---------------+---------------+---------+-------+------+------------------------------+
| id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra                        |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+------------------------------+
|  1 | PRIMARY     | user  | ref  | idx_age_phone | idx_age_phone | 4       | const |    2 | Using where                  |
|  2 | SUBQUERY    | NULL  | NULL | NULL          | NULL          | NULL    | NULL  | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+------------------------------+
2 rows in set (0.07 sec)

select_type: PRIMARY     -- 查询类型（简单查询,联合查询,子查询，衍生等）

table: user -- 显示这一行的数据是关于哪张表的

type: ref  -- 这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中,ALL代表扫描了全表才确定结果。一般来说,得保证查询至少达到range级别,最好能达到ref。

possible_keys: birthday  -- 指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。 

key: idx_age_phone -- 实际使用到的索引。如果为NULL,则没有使用索引。如果为primary的话,表示使用了主键。

key_len: 4 -- key_len显示的值为索引字段的可能最大长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

在不损失精确性的情况下，长度越短越好。

ref: const -- 显示哪个字段或常数与key一起被使用。 

rows: 1 -- MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数。这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好。注意：这个不是结果集里的行数。

Extra: Using where; Using index -- 执行状态说明,这里可以看到的坏的例子是Using temporary和Using

id如果相同可认为是同一组，从上往下顺序执行。在所有组中id值越大，优先级越高。越先执行。

想查看各个属性代表信息的可以到这位大神博客查看写的比较详细去吧皮卡丘

把比较重要点的信息拿出来说一下，这些都跟我们SQL优化息息相关

type：

all： 表示 查询是全表扫描，性能是最差的

index ： 全索引扫描。 index 与 All 的区别是 index类型中遍历 索引树。 通过比All快，因为 索引文件 通常比 数据文件 小。（也就是说虽然 all 和 Index 都是读全表，但是 index 是从索引中读取的，而 all 是从硬盘）

range： 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个.这种范围扫描过引扫描 比 index全索引扫描 要好，因为它开始于索引的某一点，而结束于索引的另一点，不用扫描全部索引。

ref： 除唯一或非主键索引外，使用普通索引，返回匹配某个单值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然后，它可能会找到多个符合条件的行，所以他应该属于查询和扫描的混合体。一般情况下，能优化到ref，性能就很不错了。

eq_ref： 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
const 用于 针对 主键 或 唯一索引 的等值查询扫描，最多只返回一行数据，所以很快。

const： 用于 针对 主键 或 唯一索引 的等值查询扫描，最多只返回一行数据，所以很快。因为它只读取一次即可。

Extra：

Using filesort： 用了文件排序，表示 MySQL 不能通过索引顺序达到排序效果，需额外的排序操作。一般有 Using filesort， 都建议优化去掉， 因为这样的查询 CPU 资源消耗大。

Using index： 用了索引，"覆盖索引扫描”， 表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错。

Using temporary： 查询有使用临时表， 一般出现于排序（order by），分组（group by）和多表 join 的情况， 查询效率不高， 建议优化。

Using where： 表明使用了where过滤

Using join buffer： 表明使用了连接缓存，比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的 join buffer 调大一些。

impossible where： where 子句的值总是 false，不能用来获取任何元组（如，SELECT * FROM t_user WHERE id = ‘1’ and id = ‘2’）

select tables optimized away： 在没有 group by 子句的情况下，基于索引优化 min / max 操作或者对于MyISAM存储引擎优化 count(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

distinct： 优化 distinct 操作，在找到第一匹配的元组后即停止找同样值的动作

索引实践

索引使用建议：

• 对于单键索引尽量选择当前查询过滤性更好的索引
• 在选择组合索引时，当前查询中过滤性最好的字段位置越靠前越好
• 尽量选择能包含当前查询中where条件下更多字段的索引
• 如果某个字段可能出现范围查询时，尽量把这个字段放在索引查询最后面
• 为较长的字符串使用前缀索引
• 不要过多创建索引,除了增加额外的磁盘空间外,对于DML操作的速度影响很大,因为其每增删改一次就得从新建立索引
• 使用组合索引,可以减少文件索引大小,在使用时速度要优于多个单列索引

索引失败场景：

• 字符串不加单引号
• 使用 ！= 或者 <>会导致索引失败全表扫描
• is not null无法使用索引但是 is null可以
• like通配符不能以%开头会导致全表扫描
• 最佳左前缀法则
• 所有索引列参与计算时
• 正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因
• 如果mysql估计使用全表扫描要比使用索引快,则不使用索引
• 如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引, 我们建议大家尽量避免使用or 关键字
  最左前缀原则其实很好理解，我们知道索引其实就是创建的一种数据结构，根据数据结构的特性达到快速查找排序等目的。假设我们当前索引中有三个字段，A、B、C。在建立索引时就会首先根据A字段的大小进行排序，当对A排序后才会对B进行排序。假设当前A字段都为‘0’的数据有十条，这十条数据就会再次根据B字段进行排序。以此类推，当我们建立查询语句时，第一个字段条件假设为B时。因为B字段并没有单独的数据结构所以并不能达到利用索引快速查询的目的。

关联查询优化

在做关联查询时我们大多情况都会在被驱动表建立索引，因为驱动表逃不开全表扫描。

• 保证被驱动表的字段已经被索引
• left join 时选择小表作为驱动表，大表作为被驱动表
• inner join 查询优化器会自动帮我们选择小的表作为驱动表
• 子查询尽量不要放在被驱动表，有可能是用不到索引
• 能够直接多表关联的尽量直接关联，不用子查询

子查询优化

• 尽量不要使用not in 或者 not exists,可以用join优化

order by,group by 优化:

• 尽可能在索引列上完成排序操作，避免使用filesort方式
• 如果不在索引列上，filesort有两种算法。mysql就要启动单路排序和双路排序
• 减少select后面查询字段
• 通过参数配置增大排序缓存区内存大小

慢查询日志

SHOW VARIABLES LIKE ‘%slow_query_log%’;

SET GLOBAL slow_query_log=1;

show variables like ‘long_query_time’;查看默认阈值

覆盖索引

我们知道MySQL的B+Tree索引是用我们字段的数据来建立索引的，比如说我们的主键id字段，就是用所有的id来组织这颗索引树，如果我们再对name字段建立索引的话，这个二级索引就是用name字段的数据来组织这颗索引树。那么问题就来了，我们知道对于二级索引而言他的叶子节点存储了对应数据行的id，也就是说最后我们的查询还是要通过主键id来进行查询获取数据。如果我们只需要name这个字段呢？比如说 select name from table where name>‘aaa’; 我们这个二级索引上保存了的name字段的所有数据，那么就没有必要再通过id去访问数据行了，直接从索引上获取数据即可。称之为覆盖索引，有的也翻译为索引覆盖。

当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时，在EXPLAIN的Extra列可以看到“Using index”的信息

流程图