数据库索引

　　在数据量非常大的情况下，在数据库中加入索引能够提升数据库查找的性能，常见的mysql索引分为以下几类：

　　

①普通索引

　　可以直接创建索引：CREATE INDEX indexName ON table(column(length))  

　　如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length

　　可以通过修改表结构来创建索引：ALTER tableADD INDEX indexName ON (column(length))

　　可以在创建表的同时创建索引：

　　　　　　CREATE TABLE `table` (
　　　　　　`id` int(11) NOT NULL AUTO_INCREMENT ,
　　　　　　`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
　　　　　　`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
　　　　　　`time` int(10) NULL DEFAULT NULL ,
　　　　　　PRIMARY KEY (`id`),
　　　　　　INDEX indexName (title(length))
　　　　　　)

　　删除索引：DROP INDEX indexName ON table

 

②唯一索引

　　与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。

　　直接创建唯一索引：CREATE UNIQUE INDEX indexName ON table(column(length))

　　修改表结构的时候创建唯一索引：ALTER table ADD UNIQUE indexName ON (column(length))

　　创建表的时候指定索引：

　　　　　　CREATE TABLE `table` (
　　　　　　`id` int(11) NOT NULL AUTO_INCREMENT ,
　　　　　　`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
　　　　　　`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
　　　　　　`time` int(10) NULL DEFAULT NULL ,
　　　　　　PRIMARY KEY (`id`),
　　　　　　UNIQUE indexName (title(length))
　　　　　　);

 

③主键索引

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

　　CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );  

 

④全文索引

　　mysql从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

　　创建表的适合添加全文索引：　　　　　

　　　　　　CREATE TABLE `table` (

　　　　　　`id` int(11) NOT NULL AUTO_INCREMENT ,
　　　　　　`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
　　　　　　`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
　　　　　　`time` int(10) NULL DEFAULT NULL ,
　　　　　　PRIMARY KEY (`id`),
　　　　　　FULLTEXT (content)
　　　　　　);
　　修改表结构添加全文索引：ALTER TABLE article ADD FULLTEXT index_content(content)

　　直接创建索引: CREATE FULLTEXT INDEX index_content ON article(content)
　　　　

⑤组合索引（单列索引、多列索引）

　　http://greatwqs.iteye.com/blog/1897118

　　 这一篇文章对这一部分内容作了详细的介绍，注意多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

 

    实例：现在我们想查出满足以下条件的用户id：
      mysql>SELECT ｀uid｀ FROM people WHERE lname｀='Liu'  AND ｀fname｀='Zhiqun' AND ｀age｀=26 ； 因为我们不想扫描整表，故考虑用索引。

       1、单列索引：
       ALTER TABLE people ADD INDEX lname (lname);
     将lname列建索引，这样就把范围限制在lname='Liu'的结果集1上，之后扫描结果集1，产生满足fname='Zhiqun'的结果集2，再扫描结果集2，找到 age=26的结果集3，即最终结果。

      由于建立了lname列的索引，与执行表的完全扫描相比，效率提高了很多，但我们要求扫描的记录数量仍旧远远超过了实际所需 要的。虽然我们可以删除lname列上的索引，再创建fname或者age 列的索引，但是，不论在哪个列上创建索引搜索效率仍旧相似。

     2、多列索引：
     ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);

 

     为了提高搜索效率，我们需要考虑运用多列索引,由于索引文件以B树格式保存，所以我们不用扫描任何记录，即可得到最终结果。

     注：在mysql中执行查询时，只能使用一个索引，如果我们在lname,fname,age上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。

     3.最左前缀：顾名思义，就是最左优先，上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引，(lname,fname)组合索引以及(lname,fname,age)组合索引。

     注：在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

 

转载于:https://www.cnblogs.com/Mr24/p/6683981.html