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 显示评注。


网页名称:mysql索引怎么学,MySQL索引的使用
本文URL:http://pwwzsj.com/article/hegcop.html