乐趣区

MySQL使用索引优化

jiezi

一、概述

1.1 大致目的

  1. 对 Mysql 存储引擎使用的索引有一个更加深入的理解。
  2. 对 Mysql 使用索引进行排序来优化查询有个大致了解。

1.2 排序方式

mysql 可以通过两种方式生成有序的结果：

  1. 通过排序操作
  2. 按照索引顺序扫描数据

由于 mysql 引擎如默认的 Innodb 底层使用的是 B + 树这种索引，所以使用索引扫描来进行排序是很快的。但是想要通过索引扫描的方式来优化排序是有很多限制条件的。

二、使用索引优化查询的条件

2.1 条件

  • 索引的列顺序和 Order By 子句的顺序要完全一致。
  • 索引中所有列的方向 (升序、降序) 和 Order by 子句完全一致。
  • 在多个表的关联查询中，Order By 中的字段必须全部在关联表中的第一张表中。

可以看出，使用索引进行排序的要求还是比较苛刻的。

2.2Innodb 中的逻辑顺序

对于使用 Innodb 为引擎的表来说，数据的逻辑顺序和主键的逻辑顺序是一致的（对于聚集索引来说：索引中键值的逻辑顺序决定了表中相应行的物理顺序，这是由于聚集索引文件中存放的是许多棵 B + 树结构）InnoDB 引擎的数据文件本身就是索引文件，叶节点包含了完整的数据记录。因为 InnoDB 的数据文件本身要按主键聚集，（查找非主键时会先在对应的 B + 树索引结构上找到主键，在通过主键那棵关键的树查找所有数据）所以 InnoDB 要求表必须有主键（MyISAM 可以没有），如果没有显式指定，则 MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键。

三、测试比较

我们使用的测试数据库含两个表，分别叫做：xuzhijeitest、xuzhijietest_MyISAM, 以下为见表语句：

使用 InnoDB 创建的表

CREATE TABLE `xuzhijietest` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `country` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `tele` varchar(255) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

使用 MyISAM 创建的表

CREATE TABLE `xuzhijietest_myisam` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `country` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `tele` varchar(255) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=utf8;

并且我们给两张表填入相同的数据，最后表中显示的数据信息如下：

我们上述讲过，对于使用 Innodb 为引擎的表来说，数据的逻辑顺序和主键的逻辑顺序是一致的，所以我们可以使用主键来进行排序
现在我们对 Innodb 引擎表按照主键了进行排序:

explain select * from  where age > 30 order by id

我们对 MyISAM 引擎表按照主键了进行排序:

explain select * from xuzhijietest_MyISAM where age > 30 order by id

分析 EXPLAIN 列的解释：
select_type
1）SIMPLE：简单的 SELECT，不实用 UNION 或者子查询。
2）PRIMARY：最外层 SELECT。
3）UNION：第二层，在 SELECT 之后使用了 UNION。
4）DEPENDENT UNION：UNION 语句中的第二个 SELECT，依赖于外部子查询。
5）UNION RESULT：UNION 的结果。
6）SUBQUERY：子查询中的第一个 SELECT。
7）DEPENDENT SUBQUERY：子查询中的第一个 SELECT，取决于外面的查询。
8）DERIVED：导出表的 SELECT（FROM 子句的子查询）
type
这是重要的列，显示连接使用了何种类型, 告诉我们对表使用的访问方式。
此处 index 要优于 ALL(index：全索引扫描; all：全表扫描)
possible_keys
显示可能应用在这张表中的索引。如果为空，没有可能的索引。
key
实际使用的索引。如果为 NULL，则没有使用索引。
key_len
使用的索引的长度。在不损失精确性的情况下，长度越短越好
Extra
关于 MYSQL 如何解析查询的额外信息。坏的例子是 Using temporary 和 Using filesort，意思 MYSQL 根本不能使用索引，结果是检索会很慢。
explain 显示详细的信息可以参考：Mysql 中 EXPLAIN 的结果参数

我们再看下下面这个例子，对于第二个操作 Extra 显示的时 null, 因为该操作不涉及到除了排序意外的操作，我们可以认为是没有关于 MYSQL 解析查询的额外信息，算是在没有额外操作下最好的情况了，当然若出现额外的操作最好的就是 Extra 显示为：using index(即，额外的操作也使用索引)

我们通过上述几个例子可以看出，Innodb 引擎中使用主键进行排序会使得我们在排序过程中使用索引来进行优化，不适用主键进行排序会使得 MySQL 使用全表扫描，用文件来排序，也即我们所说的 数据库排序操作 ，所谓数据库排序操作，是指数据库在执行过程中，先将满足条件的数据全部读出来，放入内存中，再执行快排，这样的效率是远远低于使用索引的。
对于 MyISAM 引擎是否使用主键则均是无效的。

未写完

退出移动版