Sql优化-2 索引

  索引是数据库优化中最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的 SQL 性能问题。下面将对 MySQL 中的索引的分类、存储、使用方法做详细的介绍。

索引的存储分类

  索引的存储类型目前只有两种，BTREE 和 HASH，MyISAM 和 InnoDB 存储引擎都只支持 BTREE 索引。

MySQL 如何使用索引

  索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高 SELECT 操作性能的最佳途径。查询要使用索引最主要的条件是查询条件中需要使用索引关键字，如果是多列索引（复合索引），那么只有查询条件使用了多列关键字最左边的前缀时，才可以使用索引，否则将不能使用索引。

使用索引

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

  首先按 age，name 的顺序创建一个复合索引，具体如下：

CREATE INDEX inx_age_name ON USER(age,NAME);

  然后按 age进行表查询，具体如下：

  

  可以发现即便 where 条件中不是用的 age 与 name 的组合条件，索引仍然能用到，这就是索引的前缀特性。但是如果只按 name 条件查询表，那么索引就不会被用到，具体如下：

  

  2)对于使用 like 的查询，后面如果是常量并且只有％号不在第一个字符，索引才可能会被使用，来看下面两个执行计划:

  

  可以发现第二个例子没有使用索引，而第一例子就能够使用索引，区别就在于“%”的位置不同，前者把“%”放到第一位就不能用到索引，而后者没有放到第一位就使用了索引。另外，如果如果 like 后面跟的是一个列的名字，那么索引也不会被使用。

  3）如果对大的文本进行搜索，使用全文索引而不用使用 like ‘%…%’。

  4）如果列名是索引，使用 column_name is null 将使用索引。如下例中查询 address为 null的记录就用到了索引

  

  在测试时，当我address字段值全为空时并没有使用索引。当我赋值后，就用到了索引。

存在索引但不使用索引

  1)如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。例如如果列key_part1 均匀分布在 1 和 100 之间，下列查询中使用索引就不是很好

SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90;

  2)如果使用 MEMORY/HEAP 表并且 where 条件中不使用“=”进行索引列，那么不会用到索引。heap 表只有在“=”的条件下才会使用索引。

  3)用 or 分割开的条件，如果 or 两边任何一列没有索引，那么涉及到的索引都不会被用到，例如：

  

  phone字段没有索引，所以导致整个查询过程中并没有使用索引。

  4） 如果不是索引列的第一部分，如下例子：

  

  5） 如果 like 是以％开始，例如：

  

  6）如果列类型是字符串，那么一定记得在 where 条件中把字符常量值用引号引起来，否则的话即便这个列上有索引，MySQL 也不会用到的，因为，MySQL 默认把输入的常量值进行转换以后才进行检索。如下面的例子中 user 表中的 address但是 SQL 语句中的条件值 294 是一个数值型值，因此即便在 address 上有索引，MySQL 也不能正确地用上索引，而是继续进行全表扫描。

  

查看索引使用情况

  如果索引正在工作，Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。Handler_read_rnd_next 的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描，Handler_read_rnd_next 的值较高，则通常说明表索引不正确或写入的查询没有利用索引，具体如下。
  

  从上面的例子中可以看出，目前使用的 MySQL 数据库的索引情况并不理想。

  

本文内容主要摘自于Linux公社 -深入浅出MYSQL全文.pdf。

主要为了加强记忆，结合了自己的测试。