创建MySQL索引

来源：互联网发布：淘宝怎么预防死人衣服编辑：程序博客网时间：2024/06/05 17:50

1.创建单例索引

单例索引是最基本的索引，这是建立在数据库表中特定列上的索引。MySQL并没有限制在一个表的索引数量，然而创建索引还会对性能有影响。

可以在现有的表上创建单例索引：

  alter table <table> add primary key [index-name](<column>);

  alter table <table> add [unique] key|index [index-name] (<column>)

注意：

当创建非主码索引时，key和index关键字可以互换，但创建主码索引是只能使用key关键字。

2.利用索引限制查询读取的行数

在 explain的结果中看到key=ALL或者key=NULL，则可以判断这条查询扫描整个表。

mysql> explain select id,title,pdate from article where pdate=2014;+----+-------------+---------+------+---------------+------+---------+------+------+-------------+| id | select_type | table   | <span style="color:#ff0000;">type</span> | possible_keys |<span style="color:#ff0000;"> key</span>  | key_len | ref  | rows | Extra       |+----+-------------+---------+------+---------------+------+---------+------+------+-------------+|  1 | SIMPLE      | article |<span style="color:#ff0000;"> ALL</span>  | NULL          | <span style="color:#ff0000;">NULL</span> | NULL    | NULL |   26 | Using where |+----+-------------+---------+------+---------------+------+---------+------+------+-------------+1 row in set

可以在pdate上面创建索引：

mysql> alter table article add index(pdate);

现在重新执行explain select 语句来查看到新添加的索引是否被使用了。下面输出的结果显示新建的索引确实被引用了，并且估计需要读取的行数比以前少很多，这将使查询执行得更快。

mysql> explain select id,title,pdate from article where pdate=2014;+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+| id | select_type | table   | <span style="color:#ff0000;">type </span>| possible_keys |<span style="color:#ff0000;"> key</span>   | key_len | ref   | rows | Extra       |+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+|  1 | SIMPLE      | article | <span style="color:#ff0000;">ref</span>  | pdate         |<span style="color:#ff0000;"> pdate </span>| 9       | const |    1 | Using where |+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+1 row in set

前面提到过MySQL并不限制在一个表上创建索引的数目，用户甚至可以创建重复的索引。如果用户无意中创建了重复索引，将会看到下面的信息：

mysql> alter table article add index(pdate);mysql> alter table article add index(pdate);mysql> explain select id,title,pdate from article where pdate=2014;+----+-------------+---------+------+-----------------------+-------+---------+-------+------+-------------+| id | select_type | table   | <span style="color:#ff0000;">type</span> | <span style="color:#ff0000;">possible_keys</span>         | key   | key_len | ref   | rows | Extra       |+----+-------------+---------+------+-----------------------+-------+---------+-------+------+-------------+|  1 | SIMPLE      | article | <span style="color:#ff0000;">ref </span> | <span style="color:#ff0000;">pdate,pdate_2,pdate_3</span> | pdate | 9       | const |    1 | Using where |+----+-------------+---------+------+-----------------------+-------+---------+-------+------+-------------+1 row in set

注意：

用户并不需要指定索引的名称。MySQL会根据索引所在的首列的名称自动为索引命名，并在名字后面添加可选的附加信息确保唯一性。重复的索引会产生性能开销。在接下来的关于多列索引部分我们将讨论如何发现并删除重复索引。

3.使用索引链接表

索引的另一个好处是可以提高关系表操作性能。

mysql> explain select t_user.name,t_user.username,friend_user.name,friend_user.username from t_user friend_user    -> innner join friend_user using(user_id)    -> where t_user.name='zs';

这个时候，我们可以为链接条件添加索引并重复explain命令

mysql> alter table friend_user add index(user_id);

4.使用索引进行模式匹配

利用通配符可以通过索引来做模式匹配的工作，先看下面的示例：

mysql> explain select user_id,username,name from t_user    ->  where username like 'fa%';+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+| id | select_type | table  | <span style="color:#ff0000;">type</span>  | <span style="color:#ff0000;">possible_keys</span> | <span style="color:#ff0000;">key </span>     | key_len | ref  | rows | Extra       |+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+|  1 | SIMPLE      | t_user | <span style="color:#ff0000;">range</span> | <span style="color:#ff0000;">username</span>      | <span style="color:#ff0000;">username</span> | 103     | NULL |    1 | Using where |+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+

如果查找的词是以通配符开头，则MySQL不会使用索引。看下面的示例：

mysql> explain select user_id,username,name from t_user    -> where username like '%a%';+----+-------------+--------+------+---------------+------+---------+------+------+-------------+| id | select_type | table  | type | possible_keys |<span style="color:#ff0000;"> key  </span>| key_len | ref  | rows | Extra       |+----+-------------+--------+------+---------------+------+---------+------+------+-------------+|  1 | SIMPLE      | t_user | ALL  | NULL          |<span style="color:#ff0000;"> NULL</span> | NULL    | NULL |    8 | Using where |+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

技巧

如果经常使用通配符开头查询，常用的方法是在数据库中保存需要查询的值的反序值。例如，假设想要找到所有以.com结尾的电子邮件地址，当搜索email like '%.com'时MySQL不会使用索引，而搜索reverse_email like reverse('%.com')就可以使用定义在reverse_email列上的索引。

MySQL不支持基于索引的函数。如果想创建一个带有列函数的索引，那是不可能的，会导致语法错误。不同数据库产品背景知识的开发者在执行下面语句时会遇到一个共同的问题，希望在username列上的一个索引能够被用来满足这个查询：

mysql> explain select user_id username,name from t_user     -> where UPPER(username)=UPPER('zs');+----+-------------+--------+------+---------------+------+---------+------+------+-------------+| id | select_type | table  | type | possible_keys |<span style="color:#ff0000;"> key</span>  | key_len | ref  | rows | Extra       |+----+-------------+--------+------+---------------+------+---------+------+------+-------------+|  1 | SIMPLE      | t_user | ALL  | NULL          | <span style="color:#ff0000;">NULL</span> | NULL    | NULL |    8 | Using where |+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

原因是在username列上使用了UPPER函数，MySQL不会使用username上的索引。
5.选择唯一行

如果想保证username有唯一的名字，可以创建唯一索引。唯一索引的目的是：

*提供数据完整性以保证在列中任何值都只出现一次

* 告知优化器对给定的记录最多只能有一行结果返回，这点很重要，因为有了这些信息就可以避免额外的索引扫描。

用show status 查看一般索引和唯一索引在查询内部造成的影响，下面是非唯一索引：

mysql> show session status like 'Handler_read_next';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Handler_read_next | 0     |+-------------------+-------+1 row in setmysql> select username from t_user where username='zs';+----------+| username |+----------+| zs       |+----------+1 row in setmysql> show session status like 'Handler_read_next';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Handler_read_next | 1    |+-------------------+-------+1 row in set

在内部，MySQL会去读索引中下一项记录来判断username索引的下一个值不是那个指定的值。创建唯一索引并运行同一查询的结果为：

mysql> show session status like 'Handler_read_next';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Handler_read_next | 0     |+-------------------+-------+1 row in setmysql> select username from t_user where username='zs';+----------+| username |+----------+| zs       |+----------+1 row in setmysql> show session status like 'Handler_read_next';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| <span style="color:#ff0000;">Handler_read_next</span> |<span style="color:#ff0000;"> 0</span>     |+-------------------+-------+1 row in set

从结果中可以知道，唯一索引是Mysql知道最多只可能返回一行数据，当找到一个匹配结果后就不需要再扫描了。
6.结果排序

索引也可以对查询结果进行排序。如果没有索引，mysql会使用内部文件排序算法对返回的行按照指定顺序进行排序。请看下面示例：

mysql> explain select user_id,username,name from t_user    -> where username like '%a%'    -> order by name;+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------------+| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | <span style="color:#ff0000;">Extra      </span>                 |+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------------+|  1 | SIMPLE      | t_user | ALL  | NULL          | NULL | NULL    | NULL |    8 | <span style="color:#ff0000;">Using where; Using filesort</span> |+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------------+

可以看到，通过Extra的属性中设置了Using filesort信息，Mysql内部使用sort_buffer来对结果进行排序。

也可以通过下面的命令从内部确认上述结论：

mysql> show session status like '%sort%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Sort_merge_passes | 0     || Sort_range        | 1     || Sort_rows         | 22     || Sort_scan         | 0     |+-------------------+-------+

通过基于索引的排序方法，就可以免去分类的过程，如下所示：

mysql> show session status like '%sort%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Sort_merge_passes | 0     || Sort_range        | 0     || Sort_rows         | 0     || Sort_scan         | 0     |+-------------------+-------+

下一节，我们来讨论如何使用索引来限制返回行并使用多列索引对结果排序。

0 0