数据库索引

数据库索引

 

本文主要多数据库当中得索引，索引得分类以及优化，还有如何查看你得索引是否生效来进行介绍；

首先什么是索引：

  索引是用来快速得寻找那些具有特定值得记录的，所有Mysql的索引都是以B-树的形式；来保存的。如果没有索引再查询的时候则需要从表的第一个记录开始扫描整个表所有的记录，直到找到符合查找条件的记录，而且表里面的记录数量越多，这个操作的代价也就越高。

如果为作为搜索条件的列上面创建索引，则不会再去扫描其它的记录而是直接找到目标记录；

假设我们创建了一个people表，再表当中加入一千个不同name的记录，这些记录在数据库当中没有明确的记录，但是当我们为name这列建立了索引之后，MySql将会在索引当中排序name，对于索引当中的每一项，MySQL的内部为其保存一个数据文件中实际记录所在位置的指针。因此，如果我们要查找name=jake的记录，则此时更具sql命令能够在name的索引当中找到jake的值，然后直接转到数据文件当中对应的行上，准确的放回该条记录。

这个过程当中，Mysql只需要处理一行就可以返回结果。如果没有name的索引，mysql则需要扫描数据库当中的所有记录，显然这种情况之下数据库当中的记录条数是越少越好，但是建立的索引之后，会简化很多；但是也是有缺点的，这点后面会提到；

第二 索引的类型

 

接下来我们来介绍以下索引的类型：

1. 普通索引

这是最基本类型的索引，而且它没有唯一性之类的限制，普通索引可以通过以下几种方式去创建：

1. 直接创建索引

Creat index [索引的名字] ON tablename(列名)

1. 修改表

就是用修改的命令去添加索引

Alter table tablename add index[索引的名字]（列名）

1. 创建表的时候指定索引

Create table tablename ([……….],index[索引的名字]（列名）)

1. 唯一性索引

这种索引和之前的普通索引基本相同，但是有一个区别就是：索引列的所有值只能出现一次，即必须是唯一的。创建索引的方式于普通索引的方式相同

1. 主键

主键是一种唯一性索引，但它必须指定为primary Key，若果你之前用过自增长类型的列，你可能对主键这个概念不陌生。主键实在创建表的时候指定的，如：

Create table （【、、、、、、、】，primary key (主键的列名)）。一般是值这样来做的但是，我们也可以在修改表的时候加入主键；

这里需要注意的是：每个表只有一个主键。

1. 全文索引

Mysql从3.23.23开始支持全文索引和全文检索的，在mysql当中，全文索引的类型为FULLTEXT全文所以是可以在varchar或者text类型的列上创建的。它可以通过Creat table的时候来创建，也可以直接创建索引，当然也可以用改变表的命令去创建，对于大规模的数据集，通过改变表和直接创建索引的方式来创建全文索引要比把记录插入带有全文索引的空表更快；

对于全文索引大家可以去文档中自行查看；

第三 单列索引于多列索引

 

对于所有可以是单列索引也可以是多列索引，下面我们通过具体的例来说明这两种索引的区别：假设我们有这样一个表：

CREATE TABLE people (

peopleid SMALLINT NOT NULL AUTO_INCREMENT,

firstname CHAR(50) NOT NULL,

lastname CHAR(50) NOT NULL,

age SMALLINT NOT NULL,

townid SMALLINT NOT NULL,

PRIMARY KEY (peopleid) );

在进行简单的数据插入之后，我们在表当中加入了四个名字为jake的人（）（其中两个姓Sullivans，两个姓McConnells），有两个年龄为17岁的人，还有一个名字与众不同的Joe Smith。

  这个表的主要用途是，根据指定的用户姓,名，年龄来返回相应的id

假如，我们需要查询姓名为 jake Sullivan、年龄为17岁用户的id(select id from people where firstname=’jake’ and lastname=’Sullivan’ and age=17),因为我们不想让mysql每次执行查询的时候都去扫描整个表，所以在这里我们考虑来使用索引。

 

首先我们来考虑在单个的列上去创建索引，即单列索引，如在firstname, lastname , age 散列上的其中一个列上去创建索引，如我们在firstname上面去创建索引（alert table people  add index firstname(firstname)）前者是所索引的名字括号内是列名；这样Mysql在查询的时候就直接锁定到了firstname=’jake’的这些记录之上，然后再这个中间结果集当中再根据其它条件去查找；原理就是：它首先再结果集当中排除lastname！=“Sullivan”的记录，然后排除age不等于17的记录，当条建满足搜索调价之后，就会返回最终得到的结果；

 

       由于我们建立了firstname列的索引，去与执行表的完全扫描相比，效率提高了很多，但是如果我们实际mysql扫描的记录数量仍旧远远超过了我们预期所需要的。虽然我们可以删除firstname上面的索引，再去创建lastname或者age上的索引，但是总的来说，不论在那个列上去创建索引，它都是单列索引，效率上来说会差的太多。

 

       在这样的一种情况之下，我们就由必要去创建多列索引去满足需求，我们为firstname,lastname,age三列创建多列索引，那么多列索引和分别在这三个列上去创建索引在效率上会有差别吗？答案是肯定的，因为在我们执行查询的时候，mysql只能够使用一个索引，如果你有三个索引那么在查询的时候mysql会选择去执行最严格的一个索引，，但是即使是限制最严格的单列索引在面对多列索引的时候，也是无力的。所以我们需要多列索引这样一种索引防止去解决这种问题；

 

第四 最左前缀

 

多列索引有另外一个优点，就是最左前缀的概念。我们继续来考虑前面的例子，现在我们有一个多列索引，我将这个索引称之为：first_last_age,当搜索条件是以下组合的时候，都将使用wo们定义的这个多列索引：

Firstname  , lastname , age

 

Firstname , lastname

 

Firstname

 

也就是说当查询是firstname在前的时候后都会用到我们所定义的这个主键：

从另一方面去理解，它相当于创建了（firstname lastname  age ）、（firstname, lastname）以及（firstname）这些列组合上的索引，下面的查询都能够使用到这个索引：

 

SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age='17';

SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan';

SELECT peopleid FROM people WHERE firstname='Mike';

The following queries cannot use the index at all:

SELECT peopleid FROM people WHERE lastname='Sullivan';

SELECT peopleid FROM people WHERE age='17';

SELECT peopleid FROM people WHERE lastname='Sullivan' AND age='17';

 

 

第五 选择索引列

 

       在新能优化过程中，选择在那些列上创建索引是重要的步骤之一，可以考虑使用索引的主要是两种类型的列：在where字句中出现的列，在join字句中出现的列；

来看看下面这个查询：

SELECT age ## 不使用索引

FROM people WHERE firstname='Mike' ## 考虑使用索引

AND lastname='Sullivan' ## 考虑使用索引

这个查询与之前的查询略有不同，但仍属于简单查询，由于age是在select部分被引用，mysql不会用它来限制列选择操作，因此，对于这个查询来说创建age索引就没什么必要了；

下面是一个复杂的例子：

 

SELECT people.age, ##不使用索引

town.name ##不使用索引

FROM people LEFT JOIN town ON

people.townid=town.townid ##考虑使用索引

WHERE firstname='Mike' ##考虑使用索引

AND lastname='Sullivan' ##考虑使用索引

 

与前面的例子一样，由于firstname和lastname出现在WHERE子句中，因此这两个列仍旧有创建索引的必要。除此之外，由于town表的townid列出现在join子句中，因此我们需要考虑创建该列的索引。那么，我们是否可以简单地认为应该索引WHERE子句和join子句中出现的每一个列呢？差不多如此，但并不完全。

我们还必须考虑到对列进行比较的操作符类型。MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符（%或者_）开头的情形。例如，“SELECT peopleid FROM people WHERE firstname LIKE 'Mich%';”这个查询将使用索引，但“SELECT peopleid FROM people WHERE firstname LIKE '%ike';”这个查询不会使用索引。

 

所以说，所以就是一种用空间来换取时间的做法，我们应该谨慎使用；

那么当我们使用了所以之后我们怎么去判断我们的索引是否生效了呢？

答案就是用explain关键字：

 

下面我们来介绍以下这个关建字及其用法：

Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。

Explain语法：explain select … from … [where …]

例如：explain select * from news;

输出：

+----+-------------+-------+-------+-------------------+---------+---------+-------+------

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+-------------------+---------+---------+-------+------

下面对各个属性进行了解：

1、id：这是SELECT的查询序列号

2、select_type：select_type就是select的类型，可以有以下几种：

SIMPLE：简单SELECT(不使用UNION或子查询等)

PRIMARY：最外面的SELECT

UNION：UNION中的第二个或后面的SELECT语句

DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询

UNION RESULT：UNION的结果。

SUBQUERY：子查询中的第一个SELECT

DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询

DERIVED：导出表的SELECT(FROM子句的子查询)

3、table：显示这一行的数据是关于哪张表的

4、type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。

结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

5、possible_keys：列指出MySQL能使用哪个索引在该表中找到行

6、key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL

7、key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好

8、ref：显示使用哪个列或常数与key一起从表中选择行。

9、rows：显示MySQL认为它执行查询时必须检查的行数。

10、Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。

Distinct：一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

Not exists： MYSQL 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

Range checked for each Record（index map:#）

没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

Using filesort

看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

Using index

列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

Using temporary

看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

Using where

使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index， 这就会发生，或者是查询有问题

其他一些Tip：

当type 显示为 “index” 时，并且Extra显示为“Using Index”， 表明使用了覆盖索引。

 

 

所以我们可以在使用所以之前使用一次explain关键字来查看一次，在使用之后再去查看一次，如果对于type关键字来说它的等级明显提高，那么你的索引就是有效的，但具体满不满足预期的等级，还需要去根据具体的情况去条；

 

以上就是sql得索引，以及判断是否生效得关键字explain得介绍；

下篇文章会介绍sql不同得优化方式，敬请期待！！！！