MySQL索引

索引

索引是数据结构。对数据库表中一列或多列的值进行排序的结构。类比于字典的目录,如搜索”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的辅助索引:辅助索引除了存储索引字段外,还存储了主键值,以辅助索引检索数据,必须根据辅助索引查找到主键值,再去主键索引里面查找数据。也就是说如果你通过辅助索引查找数据,要先在B+树中查找到主键,然后根据主索引查找到对应的记录,查找两次。

  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%