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_name
ALTER TABLE table_name DROP INDEX index_name
ALTER 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*2
SELECT 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’