MySQL使用索引优化

一、概述

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引擎是否使用主键则均是无效的。

未写完