重温MySQL之索引那些事

发布时间：2024年01月01日

文章目录

前言

MySQL 是一种广泛使用的开源关系型数据库，它提供了强大的数据存储和查询功能。在处理大量数据时，数据库的性能是至关重要的，而索引是提高数据库性能的关键因素之一。索引是一种数据结构，用于快速定位和检索数据，类似于书籍的目录。通过合理地创建和使用索引，可以大大提高数据库查询的效率，减少数据扫描的时间。

在本博文中，我们将深入探讨 MySQL 索引的概念、原理以及创建和管理索引的最佳实践。我们还将介绍不同类型的索引，并讨论索引失效场景。此外，还会分享一些优化 MySQL 查询性能的技巧。通过学习本博文，读者将能够更好地理解和利用 MySQL 索引来提高数据库的性能和响应速度。

一、概念

1.1 索引作用

索引用于快速查找具有特定列值的行。在没有索引的情况下，MySQL 必须从第一行开始，然后读取整个表以找到相关的行。表越大，成本就越高。如果表中有该列的索引，MySQL 可以快速确定在数据文件中间查找的位置，而不必查看所有数据，这比按顺序读取每一行要快得多。

使用索引有以下好处：

  • 快速找到匹配 WHERE 子句的行

    假设有一个名为"users"的表，其中包含了百万级别的用户记录。通过在"email"列上创建索引，可以快速找到匹配特定电子邮件地址的用户。

  • 从考虑范围中排除行

    假设有一个包含时间戳的日志表。通过在"timestamp"列上创建索引，可以轻松地排除不在特定时间范围内的日志记录。

  • 如果表具有多列索引，则优化器可以使用索引最左边的任何前缀来查找行

    假设有一个复合索引包含多个列，例如（“last_name”, “first_name”, “age”）。在执行查询时，如果只使用了"last_name"作为过滤条件，MySQL可以利用索引最左边的前缀来快速查找匹配的行。

  • 在执行 join 时从其他表中检索行。如果 MySQL 声明为相同的类型和大小，MySQL 可以更有效地使用索引

    当执行连接操作时，索引可以帮助优化器从其他表中快速检索匹配的行，而不必扫描整个表。

  • 查找特定索引列的最小或最大值

    通过在某一列上创建索引，可以快速找到该列中的最小或最大值。例如，在一个包含订单的表中，通过在"order_date"列上创建索引，可以快速找到最早或最晚的订单日期。

  • 如果排序或分组是在可用索引的最左边前缀上完成的，则对表进行排序或分组

    如果排序或分组操作在可用索引的最左边前缀上完成，索引可以帮助优化查询，避免对整个表进行排序或分组操作，从而提高性能。

  • 在某些情况下，可以优化查询以检索值，而不需要查阅数据行

    在某些情况下，如果查询的结果可以完全通过索引来满足，而不需要查阅实际的数据行，可以避免访问磁盘上的数据块，从而提高查询效率。

对于小表或处理大部分或所有行的大表上的查询，索引不那么重要。当查询需要访问大多数行时，按顺序读取比通过索引要快。

注意：在使用索引时，需要根据具体情况进行权衡和优化。不正确的索引设计和使用可能会导致性能下降甚至出现问题。因此，在实际应用中，务必谨慎选择和管理索引。

1.2 索引类型

MySQL 官方文档中不同的索引类型如下：

  • 列索引(column index)

    根据表中某列建立的索引

  • 复合索引(composite index)

    根据表中多列建立的索引

  • 聚簇索引(clustered index)

    聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。

  • 辅助索引(secondary index)

    除聚簇索引以外的索引称为辅助索引，也称为非聚簇索引(Non-clustered Index)，在InnoDB中，辅助索引中的每条记录都包含该行的主键列，以及为辅助索引指定的列

  • 唯一索引(unique index)

    有唯一约束的索引，该索引不包含任何重复的值

  • 覆盖索引(covering index)

    一种包含由查询检索到的所有列的索引，查询不是使用索引值作为指针来查找完整的表行，而是从索引结构中返回值，从而减少磁盘I/O。只要是合适的查询，任何列索引或复合索引都可以作为覆盖索引

  • 哈希索引(hash index)

    一种索引类型，用于使用相等运算符

  • 自适应哈希索引(adaptive hash index)

    哈希索引的变体，InnoDB表可以根据运行时条件，自动构建

  • 全文索引(FULLTEXT index)

    全文搜索机制中保存搜索索引的特殊类型的索引

根据索引的数据结构可以分为

  • B-Tree，多路平衡(Balance)树，MySQL中用的B-Tree是B+树，是B树的变体，聚簇索引叶子节点保存表所有数据
  • HASH
  • R-Tree 是一种用于空间数据的索引结构，用于加快对空间对象的查询和操作，是一种基于B-Tree算法的树形结构.

下表表示各个存储引擎，可以创建的索引类型

存储引擎允许的索引类型
InnoDBBTREE
MyISAMBTREE
MEMORY/HEAPHASH, BTREE
NDBHASH, BTREE (BTREE索引由NDB存储引擎实现为T-树索引)

Innodb 相关索引限制

索引分类索引类型存储NULL值允许多个NULL值IS NULL 扫描类型IS NOT NULL 扫描类型
Primary keyBTREENoNoN/AN/A
UniqueBTREEYesYesIndexIndex
KeyBTREEYesYesIndexIndex
FULLTEXTN/AYesYesTableTable
SPATIALN/ANoNoN/AN/A

1.3 B+树索引结构

b+tree

Innodb 引擎中b+树在会以 ibd 文件存储

1.4 B+树索引源码分析

MySQL innodb引擎索引创建有以下几种场景

  • 当创建 innodb 表时，默认会创建聚簇索引
  • 使用create index和alter [table] add index 语句也可以创建索引

下面会从源码中查看是如何实现的。下面代码位于innobase/handler/ha_innodb.cc

/** Create a new table to an InnoDB database.
@return error number */
int
create_table_info_t::create_table()
{
	int		error;
	int		primary_key_no;
	uint		i;
	dict_table_t*	innobase_table = NULL;
	const char*	stmt;
	size_t		stmt_len;

	DBUG_ENTER("create_table");

	/* Look for a primary key */
	primary_key_no = (m_form->s->primary_key != MAX_KEY ?
			  (int) m_form->s->primary_key : -1);

	/* Our function innobase_get_mysql_key_number_for_index assumes
	the primary key is always number 0, if it exists */
	ut_a(primary_key_no == -1 || primary_key_no == 0);

	error = create_table_def();
	if (error) {
		DBUG_RETURN(error);
	}

	/* Create the keys */

	if (m_form->s->keys == 0 || primary_key_no == -1) {
		/* Create an index which is used as the clustered index;
		order the rows by their row id which is internally generated
		by InnoDB */

		error = create_clustered_index_when_no_primary(
			m_trx, m_flags, m_table_name);
		if (error) {
			DBUG_RETURN(error);
		}
	}

	if (primary_key_no != -1) {
		/* In InnoDB the clustered index must always be created
		first */
		if ((error = create_index(m_trx, m_form, m_flags, m_table_name,
					  (uint) primary_key_no))) {
			DBUG_RETURN(error);
		}
	}

	...
}

创建索引调用链如下

  • create_index
    • row_create_index_for_mysql
      • dict_create_index_tree_in_mem
        • btr_create

btr_create 部分源码如下：

/** Create the root node for a new index tree.
@param[in]	type			type of the index
@param[in]	space			space where created
@param[in]	page_size		page size
@param[in]	index_id		index id
@param[in]	index			index, or NULL when applying TRUNCATE
log record during recovery
@param[in]	btr_redo_create_info	used for applying TRUNCATE log
@param[in]	mtr			mini-transaction handle
record during recovery
@return page number of the created root, FIL_NULL if did not succeed */
ulint
btr_create(
	ulint			type,
	ulint			space,
	const page_size_t&	page_size,
	index_id_t		index_id,
	dict_index_t*		index,
	const btr_create_t*	btr_redo_create_info,
	mtr_t*			mtr)

下面分析下使用create index和alter table两种创建索引源码

通过sql\sql_parse.cc 中代码分析create index和drop index语句，实际会通过alter table来进行索引的增删，通过mysql_alter_table这个方法实现

int
mysql_execute_command(THD *thd, bool first_level)
{
  ...
  case SQLCOM_CREATE_INDEX:
    /* Fall through */
  case SQLCOM_DROP_INDEX:
  /*
    CREATE INDEX and DROP INDEX are implemented by calling ALTER
    TABLE with proper arguments.

    In the future ALTER TABLE will notice that the request is to
    only add indexes and create these one by one for the existing
    table without having to do a full rebuild.
  */
  {
    /* Prepare stack copies to be re-execution safe */
    HA_CREATE_INFO create_info;
    Alter_info alter_info(lex->alter_info, thd->mem_root);

    if (thd->is_fatal_error) /* out of memory creating a copy of alter_info */
      goto error;

    assert(first_table == all_tables && first_table != 0);
    if (check_one_table_access(thd, INDEX_ACL, all_tables))
      goto error; /* purecov: inspected */
    /*
      Currently CREATE INDEX or DROP INDEX cause a full table rebuild
      and thus classify as slow administrative statements just like
      ALTER TABLE.
    */
    thd->enable_slow_log= opt_log_slow_admin_statements;

    create_info.db_type= 0;
    create_info.row_type= ROW_TYPE_NOT_USED;
    create_info.default_table_charset= thd->variables.collation_database;

    /* Push Strict_error_handler */
    Strict_error_handler strict_handler;
    if (thd->is_strict_mode())
      thd->push_internal_handler(&strict_handler);
    assert(!select_lex->order_list.elements);
    res= mysql_alter_table(thd, first_table->db, first_table->table_name,
                           &create_info, first_table, &alter_info);
    /* Pop Strict_error_handler */
    if (thd->is_strict_mode())
      thd->pop_internal_handler();
    break;
  }

mysql_alter_table修改表有两种方式,copy和in-place，可通过check_if_supported_inplace_alter来查看哪些不支持in-place方式

// Ask storage engine whether to use copy or in-place
enum_alter_inplace_result inplace_supported=
    table->file->check_if_supported_inplace_alter(altered_table,
                                                  &ha_alter_info);
...
if (use_inplace)
{
    if (mysql_inplace_alter_table(thd, table_list, table,
                                  altered_table,
                                  &ha_alter_info,
                                  inplace_supported, &target_mdl_request,
                                  &alter_ctx))
    {
        thd->count_cuted_fields= CHECK_FIELD_IGNORE;
        DBUG_RETURN(true);
    }

    goto end_inplace;
}
else
{
    close_temporary_table(thd, altered_table, true, false);
}

二、查询计划

2.1 explain

EXPLAIN 是 MySQL 中的一个关键字，用于分析和优化查询语句的执行计划。通过使用 EXPLAIN 关键字，可以获取有关查询操作的详细信息，包括表的访问顺序、使用的索引、连接类型等。

要使用 EXPLAIN，只需在要分析的查询语句前加上 EXPLAIN 关键字即可，执行explain语句后，MySQL 将返回关于查询执行计划的信息，通常以表格形式显示。这些信息包括以下:

列含义
idselect id,序列号
select_type查询的类型，如简单查询、Union查询、子查询等
table操作涉及的表
partitions匹配的分区
type表示访问表的方式，如全表扫描、索引扫描等
possible_keys可能选择的索引
key实际选择的索引
key_len选择索引的长度(字节数)
ref表示与索引进行比较的列或常量
rows估计要检查的行数量
filtered按表条件筛选的行的百分比
Extra额外信息

explain

2.2 id

id 表示 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序

id 越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行

mysql_index_02_id

2.3 select_type

常见select_type如下：

select_type 值含义
SIMPLE简单SELECT(没有使用 UNION 或子查询)
PRIMARY使用union或子查询 最外层第一个的 SELECT
UNION使用union连接多个select,第二个或之后 SELECT
UNION RESULTunion 结果
SUBQUERY子查询中第一个SELECT
DEPENDENT UNION依赖外部查询，第二层SELECT 或 在SELECT之后使用了UNION
DEPENDENT SUBQUERY依赖外部查询，子查询中第一个SELECT
DERIVED派生表，from子句中定义的查询表
MATERIALIZED物化的子查询

mysql_index_03_select_type

2.4 table

每条id记录对应的单表，可能是张虚拟表，结果如下：

  • <unionM,N> id为M和N两者查询union
  • <derivedN> id为N生成的派生表
  • <subqueryN> id为N生成的子查询

mysql_index_04_dervied

2.5 partitions

每条记录对应分区，非分区表为NULL

mysql_index_05_partitions

2.6 type

type表示访问表的类型(join table)，从最好到最坏常见如下

  • system

    表只有1行，且是系统表，属于const特殊例子

    mysql_index_06_type_01_system

  • const

    通过索引一次就找到了，对于主键或唯一索引的查询返回的是这个结果，是最快的一种查询方式

    mysql_index_06_type_02_const

  • eq_ref

    使用唯一索引或主键进行匹配，对于每个索引键值，表中只有一条记录与之匹配

    mysql_index_06_type_03_eq_ref

    mysql_index_06_type_03_eq_ref01

  • ref

    使用非唯一索引进行匹配，返回所有符合条件的行

    mysql_index_06_type_04_ref

  • range

    使用索引范围查询，常见的如 BETWEEN、IN 等操作

    mysql_index_06_type_05_range

  • index

    全表扫描时使用了索引，比全表扫描更快

    mysql_index_06_type_06_index

  • all

    全表扫描，大多数情况下效率最低，查询太慢，应该优化

    mysql_index_06_type_07_all

2.7 possible_keys

表示可能选择的索引，如果为NULL表示没有相关的索引

2.8 key

实际使用的索引

2.9 key_len

索引使用的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要。

列类型KEY_LEN备注
id intkey_len = 4+1int为4bytes,允许为NULL,加1byte
id bigint not nullkey_len=8bigint为8bytes
user char(30) utf8key_len=30*3+1utf8每个字符为3bytes,允许为NULL,加1byte
user varchar(30) not null utf8key_len=30*3+2utf8每个字符为3bytes,变长数据类型,加2bytes
user varchar(30) utf8key_len=30*3+2+1utf8每个字符为3bytes,允许为NULL,加1byte,变长数据类型,加2bytes
detail text(10) utf8key_len=30*3+2+1TEXT截取部分,被视为动态列类型。

2.10 ref

当使用索引列等值匹配的条件去执行查询时，也就是当 type 是const、eg_ref、ref、ref_or_null、unique_sutbquery、index_subopery其中之一时,ref列展示的就是与索引列作等值匹配的是谁。

ref 这一列的值是告诉你SQL中的被比较对象，是和哪个db库的哪个表的哪个列进行比较

2.11 rows

预计需要扫描的行数

2.12 filtered

预计查询的估计值，百分比

2.13 Extra

详细信息见：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-extra-information，常见如下：

  • Using filesort：当 Extra 中有 Using filesort 时，表示 MySQL 需额外的排序操作，不能通过索引顺序达到排序效果。一般有 Using filesort，都建议优化去掉，因为这样的查询 CPU 资源消耗大。
  • Using index：“覆盖索引扫描”，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错
  • Using index condition：索引条件下推
  • Using where ：全表扫描
  • Using join buffer ：在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度
  • Not exists：当使用左连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列又是不允许存储NULL值的，那么在该表的执行计划的Extra列就会提示Not exists额外信息
  • Using intersect(…) 交集合并、Using union(…) 并集合并 和 Using sort_union(…) 并集有序合并
  • Using temporary：查询有使用临时表，一般出现于排序，分组和多表 join 的情况，查询效率不高，建议优化

三、索引优化

3.1 索引失效

索引失效的常见场景如下：

  • 使用like通配符模糊匹配

    当对一个已经建立复合索引的第一个字段使用like通配符匹配，%放在前方，会导致全表扫描

    mysql_index_07_index_unvalid_01

  • 对索引使用函数或进行表达式计算

    当对加索引字段使用函数时会让索引失效

    mysql_index_07_index_unvalid_02

  • 类型不匹配

    name为varchar字符串类型，如果和数字比较，会导致索引失效

    mysql_index_07_index_unvalid_03

  • where子句使用or

    mysql_index_07_index_unvalid_04

  • 联合索引非最左匹配

    当使用联合索引，没有从最左侧的索引匹配

    mysql_index_07_index_unvalid_05

3.2 单表索引优化

  • like 的模糊匹配表达式不要以%起始

  • 根据最左匹配法则，最好包含最左字段查询

  • 不要在索引列上做任何计算

  • 尽量使用覆盖索引，就是查询出的字段最好就是建索引的字段

  • 字段的is not null 和 is null可能用不到索引

  • 字符串加单引号

  • 减少使用 or

3.3 双表索引优化

MySQL 中两个表之间的关联算法在5.7版本的时候只有 Nested-Loop Join嵌套循环 ，简称NLJ。

其含义大致就是先从表A里拿出来一条记录 R1，完了再用 R1 遍历表 B 里的每一条记录，并且字段 c1 来做匹配是否相同以便输出；再次循环刚才的过程，直到两表的记录数对比完成为止。
一次一行循环地从第一张表（驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

还有一种关联算法叫Block Nested-Loop Join ，块嵌套循环，简称 BNLJ，就是join buffer这块内存起的作用。join_buffer_size 就是在块嵌套循环中为了减少过多的表扫描而设计的，那 BNLJ 比 NLJ 来说，中间多了一块 BUFFER 来缓存外表的对应记录从而减少了外表的循环次数，也就减少了内表的匹配次数。
把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比

  • 永远用小表驱动大表

  • left join 索引建在右表 right join 索引建在左表

  • 保证Join语句中被驱动表中的Join条件字段已经被索引

  • 当无法保证被驱动表中的Join条件字段被索引，并且内存资源充足的情况下，不要太吝啬 JoinBuffer的设置，默认256K，可以设置为4M

    join_buffer_size=4M

参考

  1. https://dev.mysql.com/doc/refman/5.7/en/
  2. MySQL系列（一）：索引篇
  3. MySQL的索引优化分析(二)
  4. (五)MySQL索引应用篇：建立索引的正确姿势与使用索引的最佳指南！
  5. 通过.ibd文件浅析InnoDB中一张表的主键索引B+树结构
  6. MYSQL索引：对聚簇索引和非聚簇索引的认识
  7. 【建议收藏】MySQL 三万字精华总结 —索引（二）
  8. MySQL DDL log与原子DDL的实现
  9. MySQL下使用Inplace和Online方式创建索引的教程
  10. mysql5.7版本的explain解析
  11. MySQL参数优化系列之- join_buffer_size
文章来源:https://blog.csdn.net/qq_23091073/article/details/135320083
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
最新文章