mysql 索引优化器_Mysql高效索引之explain模拟优化器

高效索引

使用索引的理由

由于mysql在默认情况下，表中的数据记录是没有顺序可言的，也就是说在数据检索过程中，符合条件的数据存储在哪里，我们是完全不知情的，如果使用select语句进行查询，数据库会从第一条记录开始检索，即使找到第一条符合条件的数据，数据库的搜索也并不会因此而停止，毕竟符合条件的数据可能并不止一条，也就是说此时检索会把表中的数据全部检索一遍才结束，这样的检索方式也称为全表扫描。但假设表中存在上百上千万条数据呢？这样的检索效率就十分低了，为了解决这个问题，索引的概念就诞生了，索引是为检索而存在的。如一些书的末尾一般会提供专门附录索引，指明了某个关键字在正文中的出现的页码位置或章节的位置，这样只要找到对应页面就能找到要搜索的内容了，数据库的索引也是类似这样的原理，通过创建某个字段或者多个字段的索引，在搜索该字段时就可以根据对应的索引进行快速检索出相应内容而无需全表扫描了。

索引的创建及其基本类型

MySQL 索引可以分为单列索引、复合索引、唯一索引、主键索引等。下面分别介绍

单列索引

单列索引，也称为普通索引，单列索引是最基本的索引，它没有任何限制，创建一个单列索引，语法如下:mysql> create  index index_name on tbl_name(index_col_name);

删除索引DROP INDEX 索引名称 ON 表名

其中index_name为索引的名称，可以自定义，tbl_name则指明要创建索引的表，而index_col_name指明表中那一个列要创建索引。当然我们也可以通过修改表结构的方式添加索引：mysql> alter table tbl_name add index index_name on (index_col_name);

还可在创建表时直接指定：

创建表时直接指定

创建表时直接指定

下面为user表的username字段创建单列索引：mysql> create index index_name on test_user(username);

msyql> show index from test_user;

可见user表中的username字段的索引已被创建，在使用show index from user查看user的索引字段时，我们发现id字段也创建了索引，事实上，当user表被创建时，主键的定义的字段id就会自动创建索引，这是一种特殊的索引，也称为丛生索引，而刚才创建的index_name索引属于单列索引

复合索引

复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。创建一个复合索引的语法如下：

-- index_name代表索引名称，而name和price2为列名，可以多个mysql>create index index_name on test_goods(name,price2);

-- 同样道理，也可以通过修改表结构的方式添加索引，mysql>alter table test_goods add index index_name on (name,price2);

-- 创建表时直接指定mysql>CREATE TABLE `table` (

`id` int(11) NOT NULL AUTO_INCREMENT ,

`name` varchar(32) NOT NULL ,

'pinyin' varchar(32) ,

indexName (name(32),pinyin(32))

);

唯一索引

创建唯一索引必须指定关键字UNIQUE，唯一索引和单列索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许有空值。对于多个字段，唯一索引规定列值的组合必须唯一。如创建username为唯一索引，那么username的值是不可以重复的，

-- 创建唯一索引mysql> create unique index index_name on tbl_name(name);

-- 添加(通过修改表结构)mysql> alter table tbl_name add unique index index_name on (name)

-- 创建表时直接指定mysql> CREATE TABLE `table`(

`id` int(11) NOT NULL AUTO_INCREMENT ,

`name` varchar(32) NOT NULL,

PRIMARY KEY(`id`),

UNIQUE indexName (name(32))

);

下面为user表的username字段创建唯一索引：create unique index idx_name on tbl_name(username);

事实上这里讲username设置为唯一索引是不合理的，毕竟用户可能存在相同username，因此在实际生产环节中username是不应该设置为唯一索引的。否则当有相同的名称插入时，数据库表将会报错。

主键索引

主键索引也称丛生索引，是一种特殊的唯一索引，不允许有空值。创建主键索引语法如下：msyql> alter table tbl_name add  primary key(name);

一般情况下在创建表时，指明了主键时，主键索引就已自动创建了，因此无需我们手动创建。

-- 创建表时直接指定CREATE TABLE `table` (

`id` int(11) NOT NULL AUTO_INCREMENT ,

PRIMARY KEY (`id`), -- 主键索引

);

索引的设计

1、where子句中的列可能最适合做为索引

2、不要尝试为性别或者有无这类字段等建立索引(因为类似性别的列，一般只含有“0”和“1”，无论搜索结果如何都会大约得出一半的数据)

3、如果创建复合索引，要遵守最左前缀法则。即查询从索引的最左前列开始，并且不跳过索引中的列

4、不要过度使用索引。每一次的更新，删除，插入都会维护该表的索引，更多的索引意味着占用更多的空间

5、使用InnoDB存储引擎时，记录(行)默认会按照一定的顺序存储，如果已定义主键，则按照主键顺序存储，由于普通索引都会保存主键的键值，因此主键应尽可能的选择较短的数据类型，以便节省存储空间

6、不要尝试在索引列上使用函数。

ok~，关于索引暂且聊到这，由于索引细说起来内容还是相当多，本篇只对索引的主要知识点进行说明，让我们对索引有个清晰的了解并学会如何去使用索引。

EXPLAIN 的作用

EXPLAIN :模拟Mysql优化器是如何执行SQL查询语句的，从而知道Mysql是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

EXPLAIN :模拟Mysql优化器

(一)id列：

(二)select_type列：数据读取操作的操作类型

1、SIMPLE:简单的select 查询，SQL中不包含子查询或者UNION。

2、PRIMARY:查询中包含复杂的子查询部分，最外层查询被标记为PRIMARY

3、SUBQUERY:在select 或者WHERE 列表中包含了子查询

4、DERIVED:在FROM列表中包含的子查询会被标记为DERIVED(衍生表)，MYSQL会递归执行这些子查询，把结果集放到零时表中。

5、UNION:如果第二个SELECT 出现在UNION之后，则被标记位UNION；如果UNION包含在FROM子句的子查询中，则外层SELECT 将被标记为DERIVED

6、UNION RESULT:从UNION表获取结果的select

(三)table列：该行数据是关于哪张表

(四)type列：访问类型  由好到差system > const > eq_ref > ref > range > index > ALL

1、system:表只有一条记录(等于系统表),这是const类型的特例，平时业务中不会出现。

2、const:通过索引一次查到数据，该类型主要用于比较primary key 或者unique 索引，因为只匹配一行数据，所以很快;如果将主键置于WHERE语句后面，Mysql就能将该查询转换为一个常量。

3、eq_ref:唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描。

4、ref:非唯一索引扫描，返回匹配某个单独值得所有行，本质上是一种索引访问，它返回所有匹配某个单独值的行，就是说它可能会找到多条符合条件的数据，所以他是查找与扫描的混合体。

详解：这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一 一的扫描判断，也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找，索引却是有要求的，要实现这种能快速查找的算法，索引就要满足特定的数据结构。简单说，也就是索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。

5、range：只检索给定范围的行，使用一个索引来选着行。key列显示使用了哪个索引。一般在你的WHERE 语句中出现between 、< 、> 、in 等查询，这种给定范围扫描比全表扫描要好。因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

6、index：FUll Index Scan 扫描遍历索引树(index：这种类型表示是mysql会对整个该索引进行扫描。要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，或者某个复合索引的一部分，mysql都可能会采用index类型的方式扫描。但是呢，缺点是效率不高，mysql会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引)。

7、ALL 全表扫描 从磁盘中获取数据 百万级别的数据ALL类型的数据尽量优化。

(五)possible_keys列:显示可能应用在这张表的索引，一个或者多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。

(六)keys列:实际使用到的索引。如果为NULL，则没有使用索引。查询中如果使用了覆盖索引，则该索引仅出现在key列表中。覆盖索引：select 后的 字段与我们建立索引的字段个数一致。

(七)ken_len列:表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出来的。

(八)ref列:显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

(九)rows列(每张表有多少行被优化器查询):根据表统计信息及索引选用的情况，大致估算找到所需记录需要读取的行数。

(十)Extra列：扩展属性，但是很重要的信息。