MySQL索引

发表于 更新于 分类于 Disqus：
在进行JavaWEB开发的时候，我们少不了要和数据库打交道，在数据量小的时候，不加索引也能查的很快，但是在数据量大的情况下，就出现了慢查询的问题，而使用索引就是解决慢查询的一种方式.

索引

索引是数据结构。对数据库表中一列或多列的值进行排序的结构。类比于字典的目录，如搜索”mysql”，先在目录定位到”m”，然后找到”mysql”所在的页数，找到”mysql”。也可以理解为排好序的快速查找结构，对于MySQL数据库，除了存储着数据，还存储着满足特定查找算法的数据结构(即索引)，这些数据结构以某种方式指向数据。

索引的分类

  • 单值索引：即一个索引只包含单个列，一个表可以有多个单值索引。
  • 唯一索引：索引列的值必须唯一，但允许空值。
  • 复合索引：一个索引包含多个列。

索引的操作

创建

  1. CREATE [UNIQUE] INDEX index_name ON table_name(columnName(length));
  2. ALTER table_name ADD [UNIQUE] INDEX [index_name] ON (columnName(length));

删除

DROP INDEX [index_name] ON table_name;

查看

SHOW INDEX FROM table_name\G

添加不同的索引

  1. ALTER TABLE table_name ADD PRIMARY KEY(column_list);
  2. ALTER TABLE table_name ADD UINIQUE index_name(column_list);
  3. ALTER TABLE table_name ADD INDEX index_name(column_list);
  4. ALTER TABLE table_name ADD FULLTEXT index_name(column_list);

索引结构

按照索引在磁盘上的组织形式区分，分为堆表和索引组织表；
堆表：在磁盘上的顺序是按照插入顺序堆积起来的，代表有MySQL的MyISAM表。
索引组织表：表中的数据按照主键顺序存放（单个数据块内），代表有MySQL的InnoDB表。

聚集索引：即主键索引。innodb存储引擎是索引组织表，即表中的数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的记录数据。聚集索引的叶子节点称为数据页，数据页，数据页！重要的事说三遍。聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。

非聚集索引：MyISAM的索引方式为堆表。这么叫是为了和聚集索引区分。

辅助索引：即非主键索引（二级索引）。叶子节点=键值+书签。Innodb存储引擎的书签就是相应行数据的主键索引值。由于检索数据时，总是先获取到书签值(主键值)，再返回查询，因此辅助索引也被称之为二级索引。

MyISAM索引结构：

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。MyISAM中，辅助索引和主索引，没有任何区别，主索引要求key唯一，而辅助索引的key可以重复。MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

Innodb索引结构：

叶子节点不仅包含索引字段，而且还包含其他所有字段，数据是以主键顺序在磁盘上存储的（单个数据块内）。

Innodb辅助索引结构：

除了存储索引字段外，还存储了主键值。以辅助索引检索数据，必须先根据辅助索引查找到主键值，再去主键索引里查找数据。

创建索引的时机

  1. 主键自动建立唯一索引。
  2. 频繁作为查询条件的字段应该创建索引。
  3. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
  4. 查询中统计或者分组的字段。

不创建索引

  1. 表记录太少。
  2. 经常增删改的表，更新表的时候同时还得去维护索引。
  3. 若某个数据列包含很多重复的内容，就没必要建立索引。
  4. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。

假如一个表有10万条记录，有一个字段A只有T和F两种值，且每个值分布的概率为50%，那么对表A字段建立索引一般不会提高数据库查询效率。索引的选择性是指索引列中不同值的数目与表中记录数的比。若一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近1，这个索引的效率就越高。

性能分析

MySQL查询优化器

MySQL有专门负责查询优化的模块，当客户端向MySQL发送一条Query语句时，MySQL查询优化首先会对整条Query语句进行优化。

Explain（执行计划）

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，对查询语句或表结构进行性能分析。

使用：Explain + SQL Query 语句。如：explain select * from user;

执行计划包含的内容：

1
2
3
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
类型 说明
id select查询的序列号，表示查询中执行select子句或操作表的顺序；
select_type 查询类型；
table 显示这一行的数据是关于哪张表的；
type 访问类型；
possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用；
key 实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引（查询列要被所建的索引覆盖），则该索引仅出现在key列表中；
key_len key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的；
ref 显示索引的哪一列被使用了。哪些列或常量被用于查找索引列上的值；
rows 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数；
Extra 重要的额外信息。

id相同，执行顺序由上到下。执行顺序从上到下，先查询t1，后查询t2。

1
2
3
4
5
6
7
mysql> explain select name, deptName from emp t1, dept t2 where t1.deptId = t2.id;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    8 | NULL                                               |
|  1 | SIMPLE      | t2    | ALL  | PRIMARY       | NULL | NULL    | NULL |    5 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+

id不同，若是子查询，id的序号会递增，id值越大优先级越高，越先被执行。先查询t2，后查询t1。

1
2
3
4
5
6
7
mysql> explain select name from emp t1 where t1.deptId = (select id from dept t2 where t2.deptName = 'RD');
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using where |
|  2 | SUBQUERY    | t2    | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

id不同，相同存在。先执行t3，再执行t1，然后执行。

1
2
3
4
5
6
7
8
mysql> explain select t1.name from (select t3.id from dept t3 where t3.deptName = 'RD') t2, emp t1 where t1.deptId = t2.id;
+----+-------------+------------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+------------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | PRIMARY     | t1         | ALL  | NULL          | NULL | NULL    | NULL |    8 | NULL                                               |
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | t3         | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where                                        |
+----+-------------+------------+------+---------------+------+---------+------+------+----------------------------------------------------+

总的来说，id越大越先执行，id相同，按先后顺序执行。

select_type 说明
SIMPLE 简单的 select 查询，查询中不包含子查询或者UNION。
PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
SUBQUERY 在SELECT或WHERE列表中包含了子查询。
DERIVED 在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表里。
UNION 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED。
UNION RESULT 从UNION表获取结果的SELECT。
type 说明
system 表只有一行记录，是const类型的特列，可以忽略不计。
const 表示通过索引一次就找到了，const用于primary key或者unique索引。因为只匹配一行数据，所以很快；如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录匹配。常见于主键或唯一索引扫描。
ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行。
range 范围扫描。
index 索引扫描。
all 全表扫描。
Extra类型 说明
Using filesort 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
Using temporary 使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
USING index 表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
Using where 表明使用了where过滤
using join buffer 使用了连接缓存。
impossible where where子句的值总是false，不能用来获取任何元组。
select tables optimized away 在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

索引优化

  1. 对于复合索引来说，range 类型查询字段后面的索引无效；
  2. 左连接对右表连接字段建立索引：a left join b on a.columnA = b.columnB; alter table b add index index_b(columnB)；
  3. 右连接对左表连接字段建立索引：a right join b on a.columnA = b.columnB; alter table a add index index_a(columnA)；
  4. 尽可能减少Join语句中的内循环总次数；保证Join语句中被驱动表上的Join条件已经被索引；
  5. 最佳左前缀法则，若是复合索引，需要遵循最左前缀法则：从索引的最左前列开始并且不跳过索引中的列；
  6. 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。如：select * from a where left(cloumnA, 4) = ‘tony’；
  7. 索引中范围条件右边的列中的索引失效；
  8. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))；
  9. 使用不等于(!= 或者<>)的时候会使索引失效，导致全表扫描；
  10. is null ,is not null 也无法使用索引；
  11. like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作；
  12. 字符串不加单引号索引失效，类型转换会导致索引失效；
  13. or会使索引失效；
  14. 索引的可选择性；
  15. 禁用union，如有必要，用union all；
  16. 不需要排序的，请不要用order by；分组后不需要排序的，请用 order by null；
  17. 尽量用join代替子查询；
  18. 数据库尽量少存日志类型的数据，如必须，请定期归档；
  19. 不要增加不必要的列；
  20. DISTINCT，UNION，MINUS，INTERSECT，ORDER BY，GROUP BY都会引起排序操作；
  21. 对count，sum，max外加group by操作，尽量少做，可增加外部缓存来减少在数据层的操作，如排行榜；
  22. 拒绝：大数据，大事务，大批量的操作。

查询优化

  1. 小表驱动大表，小结果集驱动大结果集；
  2. order by子句，尽量使用Index方式排序（①ORDER BY 语句使用索引最左前列；②使用where子句与order by子句条件列组合满足索引最左前列），尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀，避免使用filesort方式排序；
  3. group by子句，实质是先排序后进行分组，遵照建索引的最佳左前缀法则。where高于having，能写在where限定的条件就不要去having限定了。

问题

  1. 为什么B+Tree比B-Tree更适合数据库的索引结构？
    B+Tree是B-Tree的一个变种，相较于B-Tree来说，B+Tree有两个特点：1.叶子节点包含了所有的关键字信息，且有横向链表连接；2.所有非叶子节点仅包含其子树根节点中最大或最小关键字。相较于B-Tree，B+Tree有三个优势：1.IO次数更少；2.查询性能稳定；3.范围查询简单。

  2. 为什么单调递增字段(AUTO_INCREMENT)比非单调字段作为InnoDB的主键更好？
    InnoDB在磁盘上的数据是按照主键排序存储的（单个数据块内）。

  3. 为什么可选择性高的字段创建索引效率更高？
    选择性高的字段具有更高的筛选性。

  4. InnoDB和MyISAM的索引结构有什么异同？

  1. InnoDB的主键和辅助索引的关系是什么？
    InnoDB的主键索引：索引即数据，索引的叶子结点不仅包含索引字段，而且还包含其他所有字段，数据是以主键顺序在磁盘上存储的（单个数据块内）。
    InnoDB的辅助索引：辅助索引除了存储索引字段外，还存储了主键值，以辅助索引检索数据，必须根据辅助索引查找到主键值，再去主键索引里面查找数据。也就是说如果你通过辅助索引查找数据，要先在Ｂ＋树中查找到主键，然后根据主索引查找到对应的记录，查找两次。

  2. InnoDB中为什么不建议过长的字段作为主键？
    因为辅助索引中叶子节点存储主键值，太长会导致辅助索引的额外存储空间太大。

  3. 索引都有哪些类型？常用的有哪些？

  • B+Tree、Hash、全文；
  • 主键、唯一索引、非唯一索引；
  • 单列索引、组合索引；

  1. 什么是索引的最左前缀和可选择性？
    最左前缀：从索引的最左边开始组合；
    可选择性：不重复的索引值（基数）和表总行数的比值。

  2. 为什么查询要遵守索引的最左前缀原则？
    索引扫描需要先在叶子节点中确定一个起点和终点。

  3. 为什么范围查询字段最好放到组合索引的最后？
    组合索引中，范围查询字段后的索引会失效。

  4. 什么是覆盖索引？
    索引数据包含了需要查找的、筛选的所有字段，只扫描索引不回表！

  5. 数据库索引为什么要使用树结构存储呢？
    树的查询效率高，而且可以保持有序。

  6. 什么是小表，什么是大表？什么是驱动表？什么是被驱动表？

  7. 解决like ‘%字符串%’时索引不被使用的方法？？
    使用覆盖索引。

  8. 索引为什么没有使用二叉树查找树来实现？
    从算法逻辑上来讲，二叉查找树的查找速度和比较次数都是最小的。但是我们不得不考虑一个问题，磁盘IO。数据库的索引是存储在磁盘上的，当数据量比较大的时候，索引的大小可能有几个G甚至更多。当我们利用索引查询的时候，不可能把所有索引全部加载到内存中，能做的只有逐一加载每一个磁盘页，这里的磁盘页对应着索引树的节点。

  9. exist和in的区别和使用

    1
    select * from a where id in (select id from b);

    当b表数据小于a表时，用in优于exists。

    1
    select * from a where exists (select 1 from b where b.id = a.id);

    当a表的数据小于b表的数据时，用exists。

17.Innodb和MyISAM有什么区别？

  • Innodb支持事务，MyISAM不支持
  • Innodb支持外键，MyISAM不支持
  • Innodb是行锁，MyISAM是表锁
  • Innodb不支持全文索引，MyISAM支持全文索引
  • Innodb不保存表的具体行数，MyISAM用一个变量保存了整个表的行数
0%