如何使用索引提高查询速度

使用索引提高查询速度

　　1.前言

　　在web开发中，页面模板，业务逻辑（包括缓存、连接池）和数据库这三个部分，数据库在其中负责执行SQL查询并返回查询结果，是影响网站速度最重要的性能瓶颈。本文主要针对MySql数据库，双十一的电商大战，引发了淘宝技术热议，而淘宝现在去IOE（I代表IBM的缩写，即去IBM的存储设备和小型机；O是代表Oracle的缩写，也即去Oracle数据库，采用MySQL和Hadoop替代的解决方案，；E是代表EMC2,即去EMC2的设备性，用PC Server替代EMC2），大量采用MySql集群！让MySql再次成为耀眼的明星！而优化数据的重要一步就是索引的建立，对于mysql中出现的慢查询，我们可以通过使用索引来提升查询速度。索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL将进行全表扫描，从第1条记录开始然后读完整个表直到找出相关的行。

　　2.mysql索引类型及创建

　　常用的索引类型有

　　（1）主键索引

　　它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

　　代码如下：

　　CREATE TABLE user（

　　id int unsigned not nullauto_increment,

　　name varchar（50） not null,

　　email varchar（40） not null,

　　primary key （id）

　　）；

　　（2）普通索引

　　这是最基本的索引，它没有任何限制。创建方式：

　　代码如下：

　　create index idx_name onuser（

　　name（20）

　　）；

　　mysql支持前缀索引，一般姓名不会超过20个字符，所以我们这里建立索引的时候限定了长度20,这样可以节省索引文件大小

　　（3）唯一索引

　　它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。创建方式：

　　代码如下：

　　CREATE UNIQUE INDEXidx_email ON user（

　　email

　　）；

　　（4）全文索引

　　MySQL支持全文索引和搜索功能。MySQL中的全文索引类型为FULLTEXT的索引。  FULLTEXT 索引仅可用于 MyISAM表；

　　代码如下：

　　CREATE TABLE articles （

　　id INT UNSIGNEDAUTO_INCREMENT NOT NULL PRIMARY KEY,

　　title VARCHAR（200），

　　body TEXT,

　　FULLTEXT （title,body）

　　）；

　　mysql> SELECT * FROMarticles WHERE MATCH （title,body） AGAINST （'database'）；

　　查询结果：

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

　　| id | title             | body                                     |

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

　　|  5 | MySQL vs. YourSQL | In the followingdatabase comparison … |

　　|  1 | MySQL Tutorial    | DBMS stands for DataBase …             |

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

　　2 rows in set （0.00 sec）

　　MATCH（）函数对于一个字符串执行资料库内的自然语言搜索。一个资料库就是1套1个或2个包含在FULLTEXT内的列。搜索字符串作为对AGAINST（）的参数而被给定。对于表中的每一行， MATCH（） 返回一个相关值，即， 搜索字符串和 MATCH（）表中指定列中该行文字之间的一个相似性度量。

　　（5）复合索引

　　代码如下：

　　CREATE TABLE test （

　　id INT NOT NULL,

　　last_name CHAR（30） NOT NULL,

　　first_name CHAR（30） NOT NULL,

　　PRIMARY KEY （id），

　　INDEX name （last_name,first_name）

　　）；

　　name索引是一个对last_name和first_name的索引。索引可以用于为last_name,或者为last_name和first_name在已知范围内指定值的查询。因此，name索引用于下面的查询：

　　SELECT * FROM test WHERElast_name='Widenius';

　　SELECT * FROM test WHERElast_name='Widenius' AND first_name='Michael';

　　但是不能用于SELECT * FROM testWHERE first_name='Michael';这是因为MySQL组合索引为"最左前缀"的结果，简单的理解就是只从最左面的开始组合。

　　3.在什么情况下使用索引

　　（1）为搜索字段建索引，如果在你的表中，某个字段你经常用来做搜索，那么，请为其建立索引吧。一般来说，在WHERE和JOIN中出现的列需要建立索引以提高查询速度。

　　例如从fps表（表中有name字段）中检索姓名为"李武"的人，

　　下面用explain来解释执行建立索引和未建立索引的区别：

　　a.未建立索引前

　　代码如下：

　　explain select name fromfps where name="李武";

　　[SQL] select name fromfps where name="李武";

　　影响的数据栏： 0

　　时间： 0.003ms

　　b.建立索引后

　　代码如下：

　　create index idx_name onfps（

　　name

　　）；

　　explain select name fromfps where name="李武";

　　[SQL] select name fromfps where name="李武";

　　影响的数据栏： 0

　　时间： 0.001ms