Mysql index及 orderby 优化

前言：优化是指通过建立合理的索引来更快地查询出结果，由于优化会增加索引文件，所以会增加额外的磁盘消耗；而且在增删改的时候，由于数据发生变化，索引也会相应的发生变化，所以会增加额外的消耗。

单列索引和多列索引
分析索引的效率
根据where语句及orderby语句进行优化
use index
不要使用哪些语句造成系统不适用索引
如何建立索引及where字段顺序

首先认识索引的类别

1)普通索引

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

创建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表);
修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );

2)唯一性索引

　　这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种方式创建：

创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );

　　主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。

4)全文索引

　　MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快

单列索引和多列索引

单列索引就是建立在一个字段上的索引，多列就是建立在多个字段上的索引。

多列索引还有另外一个优点，它通过称为最左前缀（Leftmost Prefixing）的概念体现出来。继续考虑前面的例子，现在我们有一个firstname、lastname、age列上的多列索引，我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时，MySQL将使用fname_lname_age索引：

firstname，lastname，age
firstname，lastname
firstname

从另一方面理解，它相当于我们创建了(firstname，lastname，age)、(firstname，lastname)以及(firstname)这些列组合上的索引

分析索引的效率

现在我们已经知道了一些如何选择索引列的知识，但还无法判断哪一个最有效。MySQL提供了一个内建的SQL命令帮助我们完成这个任务，这就是EXPLAIN命令。EXPLAIN命令的一般语法是：EXPLAIN <SQL命令>。你可以在MySQL文档找到有关该命令的更多说明。下面是一个例子：

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

这个命令将返回下面这种分析结果：

　　下面我们就来看看这个EXPLAIN分析结果的含义。

table：这是表的名字。

type：连接操作的类型。下面是MySQL文档关于ref连接类型的说明：

“对于每一种与另一个表中记录的组合，MySQL将从当前的表读取所有带有匹配索引值的记录。如果连接操作只使用键的最左前缀，或者如果键不是UNIQUE或PRIMARY KEY类型（换句话说，如果连接操作不能根据键值选择出唯一行），则MySQL使用ref连接类型。如果连接操作所用的键只匹配少量的记录，则ref是一种好的连接类型。”

在本例中，由于索引不是UNIQUE类型，ref是我们能够得到的最好连接类型。

如果EXPLAIN显示连接类型是“ALL”，而且你并不想从表里面选择出大多数记录，那么MySQL的操作效率将非常低，因为它要扫描整个表。你可以加入更多的索引来解决这个问题。预知更多信息，请参见MySQL的手册说明。

possible_keys：可能可以利用的索引的名字。这里的索引名字是创建索引时指定的索引昵称；如果索引没有昵称，则默认显示的是索引中第一个列的名字（在本例中，它是“firstname”）。默认索引名字的含义往往不是很明显。

Key：它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。

key_len：索引中被使用部分的长度，以字节计。在本例中，key_len是102，其中firstname占50字节，lastname占50字节，age占2字节。如果MySQL只使用索引中的firstname部分，则key_len将是50。

ref：它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行。在本例中，MySQL根据三个常量选择行。

rows： MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1。

Extra：这里可能出现许多不同的选项，其中大多数将对查询产生负面影响。在本例中，MySQL只是提醒我们它将用WHERE子句限制搜索结果集。

根据where语句及orderby语句进行优化

1)SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort]; 在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。

2)SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort]; WHERE + ORDER BY的索引优化,建立一个联合索引(columnX,sort)来实现order by 优化。

注意：如果columnX对应多个值，如下面语句就无法利用索引来实现order by的优化

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];

3)WHERE+ 多个字段ORDER BY

SELECT * FROM WHERE uid=1 ORDER x,y LIMIT 0,10;

建立索引(uid,x,y)实现order by的优化,比建立(x,y,uid)索引效果要好得多

在某些情况中，MySQL可以使用一个索引来满足ORDER BY子句，而不需要额外的排序。where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者都是降序。

use index

当我们查询时，Mysql可能使用错误的索引进行查询，我们可以手动的指定索引，来提高效率

SELECT columns FROM tablename use index (`indexname`) WHERE

不要使用哪些语句造成系统不适用索引

1)like语句操作一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

2)不要在列上进行运算 select * from users where YEAR(adddate)

3)不要使用不使用NOT IN操作

4）如果条件中有or，即使其中有条件带索引也不会使用

5）对于多列索引，不是遵循前缀规则，则不会使用索引

6）如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

7）如果mysql估计使用全表扫描要比使用索引快,则不使用索引

8）>, >=, =, <, <=, IF NULL和BETWEEN 将要使用索引

在MySQL中，有Handler_read_key和Handler_read_rnd_key两个变量，如果Handler_read_key值很高而Handler_read_rnd_key的值很低，则表明索引经常不被使用，应该重新考虑建立索引。可以通过:show status like 'Handler_read%'来查看着连个参数的值。

根据查询语句的where条件与orderby语句，进行合理的index建立，并且要避免不使用索引和使用索引造成得不偿失的情况。

Mysql index及 orderby 优化