MySQL索引优化笔记

    索引是数据库优化中最常用也是最重要的手段之一。通过索引通常可以帮助用户解决大多数的SQL性能问题。

 

    在对数据库进行查询select操作时，要知道查询效率的高低，我们可以通过explain 或 desc命令来分析MySQL是如何执行select语句的。确定问题后，再可以采取相应的索引优化措施。MyISAM存储引擎的表的数据文件和索引文件是自动分开存储的。在目前MySQL中索引

的存储类型只有两种BTREE和HASH，具体和表的存储引擎有关，MyISAMHE InnoDB存储引擎都支持BTREE索引。

 

    MySQL是如何使用索引

    查询要使用索引最主要的条件是查询条件中需要使用索引关键字，如果是多列索引，那么只有查询条件使用了多列关键字最左边的前缀时，才可以使用索引，否则将不能使用索引。

 

     使用索引的情况

      (1)对于创建的多列索引，只要查询的条件中用到了最左边的列，索引一般就会被使用。

      (2)对于使用like的查询，后面若果是常量，并且只有%号不在第一个字符，索引索引才可能被使用。如：where varname like '3%' 

      另外：如果like后面跟的是一个列的名字，那么索引也会被使用。

      (3)若果对大的文本进行搜索，使用全文索引而不使用like'%....%'这种形式。

      (4)如果列是索引，使用column_name is null 将使用索引。

 

      存在索引但没有使用索引的情况

      (1)如果MySQL估计使用索引比全表扫描更慢，则不会使用索引。 例如这种情况：select * from table where column > 1 and column < 90;

      (2)用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

      (3)在复合索引的使用中，如果使用的列不是复合索引的第一列，那么在查询中这个索引页不会被用到。

      (4)如果like是以%开头的，也不会使用到索引。

      (5)如果列类型是字符串，那么一定记得在where条件中吧把字符串常量值用引号引起来。否则得花即使这个列上有索引，MySQL也不会用到的。

 

 

    查看索引的使用情况：show status like 'Handler_read%';

    结果：

Handler_read_first	0
Handler_read_key	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	0
Handler_read_rnd_next	194

    若果索引在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数，很低的值表名增加索引得到的性能改善不高。因为索引并没有经常使用。Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。

   

 

    两个简单实用的优化方法

    定期分析表和检查表：analyze table tablename;     check table tablename;

    定期优化表：optimize table tablename;

    注意：analyze check optimize 执行期间将对表进行锁定，因此一定要注意在数据库不繁忙的时候执行相关操作。