mysql索引总结_mysql 索引类型以及创建

旧城等待， 2022-05-19 20:47 270阅读 0赞

转自：[https://blog.csdn.net/qq\_32862391/article/details/54943993][https_blog.csdn.net_qq_32862391_article_details_54943993]

#### 总结： ####

**概念：索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。**

**合理**的设计自己的数据库表和索引可以大大提高数据的检索速度，如果在大表中滥用索引反而会影响你的数据库性能

#### MySQL目前主要有以下几种索引类型：   1.普通索引   2.唯一索引   3.全文索引   4.单列、多列索引   5.组合索引（最左前缀） ####

#### MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或\_开头的情形)。而理论上每张表里面最多可创建16个索引。 ####

#### 原文： ####

关于[MySQL][]索引的好处，如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表，单表查询可能几十万数据就是瓶颈，而通常大型网站单日就可能会产生几十万甚至几百万的数据，没有索引查询会变的非常缓慢。还是以WordPress来说，其多个数据表都会对经常被查询的字段添加索引，比如wp\_comments表中针对5个字段设计了BTREE（二叉树）索引。

合理的设计自己的数据库表和索引可以大大提高数据的检索速度，如果在大表中滥用索引反而会影响你的数据库性能，下边数据库优化有详细提到。

执行下面的SQL语句：

<table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
 <tbody style="vertical-align:baseline;background:none;"> 
  <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
   <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">1</code></td> 
   <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">mysql&gt;&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">SELECT</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">id,FROM_UNIXTIME(</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">time</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">)&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">FROM</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">article&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">WHERE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">a.title=</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:#0000FF;background:none;">'测试标题'</code></td> 
  </tr> 
 </tbody> 
</table>

如果有20W条数据不加索引查询需要的时间非常恐怖的，如果加上联合查询和其他一些约束条件，[数据库][MySQL]会疯狂的消耗内存，并且会影响前端程序的执行。这时给title字段添加一个BTREE索引：

<table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
 <tbody style="vertical-align:baseline;background:none;"> 
  <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
   <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">1</code></td> 
   <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">mysql&gt;&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">ALTER</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">TABLE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">article&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">ADD</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">INDEX</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">index_article_title&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">ON</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">title(200);</code></td> 
  </tr> 
 </tbody> 
</table>

加上索引再次执行上述查询语句，其对比非常明显。

#####     #####

# MySQL索引的概念 #

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。上述SQL语句，在没有索引的情况下，数据库会遍历全部200万条数据后选择符合条件的；而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。如果我们把SQL语句换成“SELECT \* FROM article WHERE id=2000000”，那么你是希望数据库按照顺序读取完200万行数据以后给你结果还是直接在索引中定位呢？加上索引后查询数据会直接在索引中定位。（注：一般数据库默认都会为主键生成索引）。

索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

1. 普通索引

>      这是最基本的索引，它没有任何限制，比如上文中为title字段创建的索引就是一个普通索引，MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">01</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">–直接创建索引</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">02</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">CREATE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">INDEX</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">index_name&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">ON</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">table</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">column</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(length))</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">03</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">–修改表结构的方式添加索引</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">04</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">ALTER</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">TABLE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">table_name&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">ADD</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">INDEX</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">index_name&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">ON</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">column</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(length))</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">05</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">–创建表的时候同时创建索引</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">06</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">CREATE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">TABLE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">`</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">table</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">` (</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">07</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">`id`&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">int</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(11)&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NOT</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NULL</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">AUTO_INCREMENT ,</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">08</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">`title`&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">char</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(255)&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">CHARACTER</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">SET</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">utf8&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">COLLATE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">utf8_general_ci&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NOT</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NULL</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">,</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">09</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">`content` text&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">CHARACTER</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">SET</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">utf8&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">COLLATE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">utf8_general_ci&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NULL</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">,</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">10</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">`</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">time</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">`&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">int</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(10)&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NULL</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">DEFAULT</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NULL</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">,</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">11</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">PRIMARY</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">KEY</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(`id`),</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">12</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">INDEX</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">index_name (title(length))</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">13</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">)</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">14</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">–删除索引</code></td> 
>   </tr> 
>  </tbody> 
> </table>

> <table style="border-spacing:0px;background-position:0% 0%;border:0px none;vertical-align:baseline;line-height:22px;font-size:14px;background-image:none;background-repeat:repeat;background-attachment:scroll;background-color:transparent;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">15</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"> 
>     <blockquote style="border-left:8px solid rgb(221,223,228);"> 
>      <code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">DROP</code>&nbsp; 
>      <code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">INDEX</code>&nbsp; 
>      <code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">index_name&nbsp;</code> 
>      <code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">ON</code>&nbsp; 
>      <code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">table</code> 
>     </blockquote></td> 
>   </tr> 
>  </tbody> 
> </table>

2. 唯一索引

>       与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">01</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">–创建唯一索引</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">02</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">CREATE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">UNIQUE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">INDEX</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">indexName&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">ON</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">table</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">column</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(length))</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">03</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">–修改表结构</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">04</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">ALTER</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">TABLE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">table_name&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">ADD</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">UNIQUE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">indexName&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">ON</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">column</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(length))</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">05</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">–创建表的时候直接指定</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">06</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">CREATE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">TABLE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">`</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">table</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">` (</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">07</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">`id`&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">int</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(11)&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NOT</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NULL</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">AUTO_INCREMENT ,</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">08</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">`title`&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">char</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(255)&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">CHARACTER</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">SET</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">utf8&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">COLLATE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">utf8_general_ci&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NOT</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NULL</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">,</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">09</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">`content` text&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">CHARACTER</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">SET</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">utf8&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">COLLATE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">utf8_general_ci&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NULL</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">,</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">10</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">`</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">time</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">`&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">int</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(10)&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NULL</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">DEFAULT</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NULL</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">,</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">11</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">PRIMARY</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">KEY</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(`id`),</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">12</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">UNIQUE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">indexName (title(length))</code></td> 
>   </tr> 
>  </tbody> 
> </table>

3. 全文索引（FULLTEXT）

>       MySQL从3.23.23版开始支持全文索引和全文检索，在mysql5.6版本以前FULLTEXT索引仅可用于 MyISAM 表，在5.6之后innodb引擎也支持FULLTEXT索引；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。
> 
>        //对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">01</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">–创建表的适合添加全文索引</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">02</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">CREATE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">TABLE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">`</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">table</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">` (</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">03</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">`id`&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">int</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(11)&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NOT</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NULL</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">AUTO_INCREMENT ,</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">04</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">`title`&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">char</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(255)&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">CHARACTER</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">SET</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">utf8&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">COLLATE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">utf8_general_ci&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NOT</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NULL</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">,</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"> 
>     <blockquote style="border-left:8px solid rgb(221,223,228);"> 
>      <code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">05</code> 
>     </blockquote></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">`content` text&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">CHARACTER</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">SET</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">utf8&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">COLLATE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">utf8_general_ci&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NULL</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">,</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">06</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">`</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">time</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">`&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">int</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(10)&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NULL</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">DEFAULT</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">NULL</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">,</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">07</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">PRIMARY</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">KEY</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">(`id`),</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">08</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">FULLTEXT (content)</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">09</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">);</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;background-position:0% 0%;border:0px none;vertical-align:baseline;line-height:22px;font-size:14px;background-image:none;background-repeat:repeat;background-attachment:scroll;background-color:transparent;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">10</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"> 
>     <blockquote style="border-left:8px solid rgb(221,223,228);background:rgb(238,240,244);"> 
>      <code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">–修改表结构添加全文索引</code> 
>     </blockquote></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">11</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">ALTER</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">TABLE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">article&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">ADD</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">FULLTEXT index_content(content)</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">12</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">–直接创建索引</code></td> 
>   </tr> 
>  </tbody> 
> </table>

> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">13</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">CREATE</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">FULLTEXT&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">INDEX</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">index_content&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">ON</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">article(content)</code></td> 
>   </tr> 
>  </tbody> 
> </table>

4. 单列索引、多列索引

> 多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

5. 组合索引（最左前缀）

> 平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引：ALTER TABLE article ADD INDEX index\_titme\_time (title(50),time(10))。建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：
> 
> –title,time
> 
> –title
> 
> 为什么没有time这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引，如下面的几个SQL所示：
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">1</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">–使用到上面的索引</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">2</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">SELECT</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">*&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">FROM</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">article WHREE title=</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:#0000FF;background:none;">'测试'</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:rgb(128,128,128);background:none;">AND</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">time</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">=1234567890;</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">3</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">SELECT</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">*&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">FROM</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">article WHREE title=</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;color:#0000FF;background:none;">'测试'</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">;</code></td> 
>   </tr> 
>  </tbody> 
> </table>
> 
> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">4</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">–不使用上面的索引</code></td> 
>   </tr> 
>  </tbody> 
> </table>

> <table style="border-spacing:0px;border:0px;vertical-align:baseline;line-height:22px;font-size:14px;background:none;"> 
>  <tbody style="vertical-align:baseline;background:none;"> 
>   <tr style="border-top:0px;background:none;vertical-align:baseline;"> 
>    <td style="border-width:0px;color:rgb(175,175,175);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;text-align:right;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">5</code></td> 
>    <td style="border-width:0px 0px 0px 3px;border-left-color:rgb(108,226,108);vertical-align:top;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;"><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">SELECT</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">*&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">FROM</code>&nbsp;<code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">article WHREE&nbsp;</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-weight:bold;color:rgb(0,102,153);background:none;">time</code><code style="border:0px;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;background:none;">=1234567890;<br></code></td> 
>   </tr> 
>  </tbody> 
> </table>

#     #

# MySQL索引的优化 #

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有[大数据][Link 1]量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。下面是一些总结以及收藏的MySQL索引的注意事项和优化方法。

1. 何时使用聚集索引或非聚集索引？

> > ![Center][]  
> >

> # 聚集索引: #
> 
>   一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。  
> 　　聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。 　　　  
> 
> 
>   聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此 类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节 省成本。 　　　
> 
> 　 当索引值唯一时，使用聚集索引查找特定的行也很有效率。例如，使用唯一雇员 ID 列 emp\_id 查找特定雇员的最快速的方法，是在 emp\_id 列上创建聚集索引或 PRIMARY KEY 约束。
> 
> # 非聚集索引: #
> 
>   非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
> 
>   索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。  
> 
> 
> 备注：聚集索引一张表只能创建一个，非聚集索引一张表可以创建多个，在mysql中InnoDB引擎是唯一支持聚集索引的存储引擎。InnoDB按照主键（Primary Key）进行聚集，如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引，InnoDB就会定义隐藏的主键然后在上面进行聚集。

2. 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

3. 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

例：`CREATE` `INDEX` `index_name ``ON` `table``(``column``(10 or 20))；`

4. 索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

5. like语句操作

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

6. 不要在列上进行运算

例如：select \* from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select \* from users where adddate<’2007-01-01′。关于这一点可以围观：[一个单引号引发的MYSQL性能损失。][MYSQL]

#     #

# 总结：    #

最后总结一下，MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或\_开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的，比如我刚才针对text类型的字段创建索引的时候，系统差点就卡死了。

[https_blog.csdn.net_qq_32862391_article_details_54943993]: https://blog.csdn.net/qq_32862391/article/details/54943993
[MySQL]: http://lib.csdn.net/base/mysql
[Link 1]: http://lib.csdn.net/base/hadoop
[Center]: /images/20220520/944899aee80c420eb68bb0c9603dc5ab.png
[MYSQL]: http://www.zendstudio.net/archives/single-quotes-or-no-single-quotes-in-sql-query