mysql 索引创建与使用

mysql索引作用：

    在索引列上，除了有序查找之外，数据库利用各种各样的快速定位技术，能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。

mysql索引创建规则：

1、表的主键、外键必须有索引；2、数据量超过300的表应该有索引；3、经常与其他表进行连接的表，在连接字段上应该建立索引4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；5、索引的字段必须是经常作为查询条件的字段;6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：    A、正确选择复合索引中的主列字段，一般是选择性较好的字段；    B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以       建立复合索引；否则考虑单字段索引；    C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；    D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；    E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；    F、如果索引多个字段，第一个字段要是经常作为查询条件的。如果只有第二个字段作为查询条件，这个索引不会起到作用;8、频繁进行数据操作的表，不要建立太多的索引；9、删除无用的索引，避免对执行计划造成负面影响；10、不要过度索引。不要以为 索引 “ 越多越好 ” ，什么东西都用索引是错的。每个额外的 索引都要占用额外的磁盘空间，并降低写操作的性能。如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是，则就不要费力去增加这个索引了，因为已经有了。11、考虑在列上进行的比较类型。索引可用于 “ < ” 、 “ < = ” 、 “ = ” 、 “ > = ” 、 “ >” 和 BETWEEN 运算。在模式具有一个直接量前缀时，索引也用于 LIKE 运算。12、对于值唯一不重复的列要添加唯一索引，可以更快速的通过该索引来确定某条记录。唯一索引是最有效的。

索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，
一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。

创建索引：
1、执行CREATE TABLE语句时可以创建索引：
实例：

CREATE TABLE `log_day_url_body` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `body` varchar(255) NOT NULL DEFAULT '',  `p_id` int(11) NOT NULL DEFAULT '0',  `plt_id` int(3) NOT NULL DEFAULT '1',  PRIMARY KEY (`id`),  KEY `body` (`body`),  KEY `categories` (`body`,`plt_id`,`p_id`),  UNIQUE KEY `url` (`id`,`plt_id`,`p_id`),  UNIQUE KEY `url_3` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='注释语句'

2、单独用CREATE INDEX来为表增加索引：
CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list)CREATE UNIQUE INDEX index_name ON table_name (column_list)

table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

3、单独用ALTER TABLE来为表增加索引：
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)ALTER TABLE table_name ADD UNIQUE (column_list)ALTER TABLE table_name ADD PRIMARY KEY (column_list)'添加FULLTEXT(全文索引)'ALTER TABLE `table_name` ADD FULLTEXT ( `column`)ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

索引类型：
在创建索引时，可以规定索引能否包含重复值。如果不包含，则索引应该创建为PRIMARY KEY或UNIQUE索引。
对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。
PRIMARY KEY索引和UNIQUE索引非常类似。事实上，PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。
这表示一个表只能包含一个PRIMARY KEY，因为一个表中不可能具有两个同名的索引。

删除索引：
可利用ALTER TABLE或DROP INDEX语句来删除索引。DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

DROP INDEX index_name ON talbe_nameALTER TABLE table_name DROP INDEX index_nameALTER TABLE table_name DROP PRIMARY KEY

其中，前两条语句是等价的，删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。
如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。
如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。
如果删除组成索引的所有列，则整个索引将被删除。

查看索引：

show index from tblname;'或'show keys from tblname;

查询结果列：

· Table表的名称。· Non_unique如果索引不能包括重复词，则为0。如果可以，则为1。· Key_name索引的名称。· Seq_in_index索引中的列序列号，从1开始。· Column_name列名称。· Collation列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。· Cardinality索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。· Sub_part如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。· Packed指示关键字如何被压缩。如果没有被压缩，则为NULL。· Null如果列含有NULL，则含有YES。如果没有，则该列含有NO。· Index_type用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。· Comment一些关于过引的常用语句

索引使用注意事项：

1、不要对索引字段进行运算，而要想办法做变换，比如

SELECT ID FROM T WHERE NUM/2=100'应改为:'SELECT ID FROM T WHERE NUM = 100*2SELECT ID FROM T WHERE NUM/2 = NUM1'如果NUM有索引应改为:'SELECT ID FROM T WHERE NUM = NUM1*2如果NUM1有索引则不应该改。

SELECT 年,月,金额 FROM 结余表 WHERE 100*年+月=2010*100+10'改为：'SELECT 年,月,金额 FROM 结余表 WHERE 年=2010 AND月=10

2、不要对索引字段进行格式转换

'日期字段的例子'WHERE CONVERT(VARCHAR(10), 日期字段, 120) = '2010-07-15''应该改为'WHERE日期字段〉='2010-07-15' AND 日期字段'ISNULL转换的例子：'WHERE ISNULL(字段,'12')<>'12'应改为:WHERE字段<>'12'WHERE ISNULL(字段,'')=''不应修改WHERE ISNULL(字段,'F') ='T'应改为: WHERE字段='T'WHERE ISNULL(字段,'F')<>'T'不应修改

3、不要对索引字段使用函数

WHERE LEFT(NAME, 3)=’ABC’ 或者WHERE SUBSTRING(NAME,1, 3)=’ABC’应改为: WHERE NAME LIKE ‘ABC%’日期查询的例子：WHERE DATEDIFF(DAY, 日期,’2010-06-30′)=0应改为:WHERE 日期>=’2010-06-30′ AND 日期WHERE DATEDIFF(DAY, 日期,’2010-06-30′)>0应改为:WHERE 日期WHERE DATEDIFF(DAY, 日期,’2010-06-30′)>=0应改为:WHERE 日期WHERE DATEDIFF(DAY, 日期,’2010-06-30′)　　应改为:WHERE 日期>=’2010-07-01′WHERE DATEDIFF(DAY, 日期,’2010-06-30′)应改为:WHERE 日期>=’2010-06-30′

4、不要对索引字段进行多字段连接

比如：WHERE FAME+ ‘. ‘+LNAME=’HAIWEI.YANG’应改为:WHERE FNAME=’HAIWEI’ AND LNAME=’YANG’