mysql索引怎么学,MySQL索引的使用

「Mysql索引原理（七）」覆盖索引

       通常大家都会根据查询的WHERE条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是WHERE条件部分。索引确实是一种查找数据的高效方式，但是MySQL也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回到表中查询呢？ 如果一个索引覆盖所有需要查询的字段的值，我们就称之为“覆盖索引”。

覆盖索引是非常有用的工具，能够极大地提高性能：

       在所有这些场景中，在索引中满足查询的成本一般比查询行要小得多。

       不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引都不存储索引列的值，所以MySQL只能使用B+Tree索引所覆盖索引。另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引。

       当发起一个呗索引覆盖的查询是，在EXPLAIN的Extra列可以看到“Using index”的信息。

如： explain select col1 from layout_test where col2=99

       索引覆盖查询还有很多陷阱可能会导致无法实现优化。MySQL查询优化器会在执行查询前判断是否有一个索引能进行覆盖。假设索引覆盖了wehre条件中的字段，但不是整个查询涉及的字段。mysql5.5和更早的版本也总是会回表获取数据行，尽管并不需要这一行且最终会被过滤掉。

如： EXPLAIN select * from people where last_name='Allen' and first_name like '%Kim%'

这里索引无法覆盖该查询，有两个原因：

这条语句只检索1行，而之前的 like '%Kim%'要检索3行。

也有办法解决上面所说的两个问题，需要重写查询并巧妙设计索引。

       这种方式叫做延迟关联，因为延迟了对列的访问。在查询第一个阶段MySQL可以使用覆盖索引，因为索引包含了主键id的值，不需要做二次查找。

       在FROM子句的子查询中找到匹配的id，然后根据这些id值在外层查询匹配获取需要的所有列值。虽然无法使用索引覆盖整个查询，但总算比完全无法利用索引覆盖的好吧。

数据量大了怎么办？

       这样优化的效果取决于WHERE条件匹配返回的行数。假设这个people表有100万行，我们看一下上面两个查询在三个不同的数据集上的表现，每个数据集都包含100万行。

实例1中 ，查询返回了一个很大的结果集，因此看不到优化的效果。大部分时间都花在读取和发送数据上了。

实例2中 ，经过索引过滤，尤其是第二个条件过滤后只返回了很少的结果集，优化的效果非常明显：在这个数据及上性能提高了很多，优化后的查询效率主要得益于只需读取40行完整数据行，而不是原查询中需要的30000行。

实例3中 ，子查询效率反而下降。因为索引过滤时符合第一个条件的结果集已经很小了，所以子查询带来的成本反而比从表中直接提取完整行更高。

       在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中部分列的查询。不过，可以更进一步优化InnoDB。回想一下，InnoDB的二级索引的叶子节点都包含了主键的值，这意味着InnoDB的二级索引可以有效地利用这些额外的主键列来覆盖查询。

       例如，people表中last_name字段有一个二级索引，虽然该索引的列不包括主键id，但也能够用于对id做覆盖查询：

select id,last_name from people where last_name='hua'

mysql索引怎么学

索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。

可以理解为“排好序的快速查找数据结构”

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据,

这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

mysql如何建立索引

我们可以通过查看索引的属性来判断创建索引的方法。

查看索引的语法格式如下：

SHOW INDEX FROM 表名 [ FROM 数据库名]

语法说明如下：

表名：指定需要查看索引的数据表名。

数据库名：指定需要查看索引的数据表所在的数据库，可省略。比如，SHOW INDEX FROM student FROM test; 语句表示查看 test 数据库中 student 数据表的索引。

示例

使用 SHOW INDEX 语句查看《MySQL创建索引》一节中 tb_stu_info2 数据表的索引信息，SQL 语句和运行结果如下所示。

mysql SHOW INDEX FROM tb_stu_info2\G

1. row

Table: tb_stu_info2

Non_unique: 0

Key_name: height

Seq_in_index: 1

Column_name: height

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.03 sec)

其中各主要参数说明如下：

参数 说明

Table 表示创建索引的数据表名，这里是 tb_stu_info2 数据表。

Non_unique 表示该索引是否是唯一索引。若不是唯一索引，则该列的值为 1；若是唯一索引，则该列的值为 0。

Key_name 表示索引的名称。

Seq_in_index 表示该列在索引中的位置，如果索引是单列的，则该列的值为 1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序。

Column_name 表示定义索引的列字段。

Collation 表示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类。

Cardinality 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。

Sub_part 表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为 NULL。

Packed 指示关键字如何被压缩。若没有被压缩，值为 NULL。

Null 用于显示索引列中是否包含 NULL。若列含有 NULL，该列的值为 YES。若没有，则该列的值为 NO。

Index_type 显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。

Comment 显示评注。