Mysql 索引

mysql 索引
 
     普通索引 : CREATE INDEX IndexName ON `TableName`(`字段名`(length))

        ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length))

     唯一索引： CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length));
        
                ALTER TABLE TableName ADD UNIQUE (column_list)  
     
     组合索引： CREATE INDEX IndexName On `TableName`(`字段名`(length),`字段名`(length),...);

   
          1、不按索引最左列开始查询（多列索引） 例如index(‘c1’, ‘c2’, ‘c3’) where ‘c2’ = ‘aaa’ 不使用索引,where `c2` = `aaa` and `c3`=`sss` 不能使用索引

          2、查询中某个列有范围查询，则其右边的所有列都无法使用查询。 例如：Where c1= ‘xxx’ and c2 like = ‘aa%’ and c3=’sss’ 改查询只会使用索引中的前两列,因为like是范围查询

          3、不能跳过某个字段来进行查询,这样利用不到索引。

     全文索引： ALTER TABLE tablename ADD FULLTEXT(column1, column2)
      
        SELECT * FROM tablename  WHERE MATCH(column1, column2) AGAINST('xxx','sss','ddd')

     索引删除 ：DROP INDEX IndexName ON `TableName`
         
使用索引的缺点 :

       1.在创建索引和维护索引 会耗费时间,随着数据量的增加而增加
       
       2.索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间

       3.当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度


使用索引需要注意的地方
 
      1.在经常需要搜索的列上,可以加快索引的速度
          
      2.主键列上可以确保列的唯一性
     
      3.在表与表的连接条件上加上索引,可以加快连接查询的速度

      4.在经常需要排序(order by),分组(group by)和的distinct 列上加索引 可以加快排序查询的时间。

      5.在一些where 之后的 < <= > >= BETWEEN IN 以及某个情况下的like 建立字段的索引(B-TREE)

      6.like语句的 如果你对nickname字段建立了一个索引.当查询的时候的语句是 nickname lick     
         
        '%ABC%' 那么这个索引讲不会起到作用.而nickname lick 'ABC%' 那么将可以用到索引

      7.索引不会包含NULL列,如果列中包含NULL值都将不会被包含在索引中,复合索引中如果有一列含有NULL
        值那么这个组合索引都将失效,一般需要给默认值0或者 ' '字符串

      8.使用短索引,如果你的一个字段是Char(32)或者int(32),在创建索引的时候指定前缀长度 比如前10个
        字符 (前提是多数值是唯一的..)那么短索引可以提高查询速度,并且可以减少磁盘的空间,也可以减少     
        I/0操作.

      9.不要在列上进行运算,这样会使得mysql索引失效,也会进行全表扫描

      10.选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,
         处理起来更快


什么情况下不创建索引

     1.查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
        
     2.很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行
       的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率

     3.定义为text和image和bit数据类型的列不应该增加索引,

     4.当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系