在mysql如何挑选索引列

  1. 为用于搜索、排序或分组的列创建索引,而对于用作输出显示的列则不用创建索引。这意味着最佳索引候选列是那些出现在 WHERE 子句中的列、连接子句中的列或者出现在 ORDER BY 或 GROUP BY 子句中的列。而那些只是出现在 SELECT 关键字后面的输出列表里的列,则不是很好的索引候选列。

  2. 认真考虑数据列基数。列的基数(cardinality)是指它所容纳的所有非重复值的个数,例如,某个列包含值 1、3、7、4、7、3,那么它的基数为 4。相对于表里面行的总数来说,列的基数越高(也就就是说,它包含的唯一值多,重复值少),索引的使用效果越好。对于包含许多不同年龄值的列,索引可以很容易地将各个行区分开来。但是对于记录性别的列,其中只会包含两个值:’M’和’F’,索引操作毫无用处。如果这两个值出现的频率大致一样,那么不管搜索哪个值,你得到的都是近乎一半的行。在这种情况下,索引可能根本无法使用,因为当查询优化程序确定出某个值在表的行里出现频率很大时,它会跳过索引,直接执行全表扫描操作。

  1. 索引短小值。应该尽量选用较小的数据类型,较短小的值可以在一下几个方面提高索引的处理效率:

    • 短小值可以让比较操作更快,从而加快索引查找速度。
    • 短小值可以让索引短小,从而减少对磁盘 I/O 请求。
    • 对于短小的键值,键缓存里的索引块可以容纳更多的键值。如果 MySQL 能在内存里容纳更多的键,那么就可以在不从磁盘读取更多索引块的前提下,提高找到键值的几率。

      对 InnoDB 存储引擎,因为它使用的是聚簇索引(clustered index),所以让主键尽量短小会带来更多好处。聚簇索引会把数据行和主键值存储在一起(即聚集在一起)。其他的索引都是二级索引,即它们把主键值和二级索引值存储在一起。在二级索引里进行查找,会先得到主键值,然后再通过它在主键索引中定位到相应的行。ps: 该行为被称为回表操作。这意味着,主键值在每一个二级索引里都会重复出现,因此如果主键值较长,则会导致每一个二级索引需要占用更多的存储空间。

  2. 索引字符串的前缀。想要对字符串列进行索引,应当尽可能指定前缀长度。例如,有一个 CHAR(20) 列,如果大多数值的前 10 或 20 个字符都是唯一的,那么就可以不用为整个进行索引,而只为前面的 10 或 20 个字符进行索引,这样可以节省大量的索引空间,而且还能使索引变得更快。不过,只索引列的第一个字符恐怕不行,因为这样做会导致索引无法获得大量的唯一值。

  3. 利用最左前缀。当创建包含 n 个列的符合索引时,实际上会创建 n 个专供 MySQL 使用的索引。复合索引相当于多个索引,因为索引中最左边的任意数据列集合都可用于匹配各个行,这样的集合即为“最左前缀”。(这与对列的前缀进行索引有所不同,它会使用列值的前 n 个字符或字节来创建索引。)

  4. 不要建立过多的索引。不要以为索引“越多越好”,然后就为你所能看到的所有数据列都建立索引,这是因为每增加一个索引都需要占据额外的磁盘空间,而且都会影响写入操作的性能。在对表做了修改之后,索引就会更新,并且可能还会重组,索引越多,整个过程所占用的时间就越长。很少使用或从不使用索引,会大大降低表的修改速度。此外,在为检索生成执行计划时,MySQL 会对索引进行仔细推敲,创建多余的索引,会为查询优化程序增加更多的工作,当表有太多的索引时,MySQL 还有可能(只是存在可能)无法使用最好的索引。

  5. 让参与比较的索引类型保持匹配。在创建索引时,大部分存储存储引擎都会选择它们要使用的索引实现。例如,InnoDB 总会使用 B 树索引; MyISAM 也会使用 B 树索引,但对于空间类型则会改用 R 树索引; MEMORY 存储引擎默认会使用散列索引,但它也支持 B 树索引,并允许在这两者之间进行选择。

    在选择索引类型时,请考虑计划在索引列上执行的是什么类型的比较操作:

    • 对于散列索引,会有一个散列函数应用于每一个列值。最终的散列值都会被存入索引,用于执行查找。(散列函数的算法,会尽量为不同的输入值生成不同的散列值,使用散列值的好处是它们之间的比较比其原始值更有效率。) 在使用运算符=<=>完成精度匹配的比较操作里,散列索引的速度非常快。但在那些用于查找一个范围内的值的比较操作里,它们却表现欠佳,如下列表达式:
      id < 30
      或者
      weight BETWEEN 100 AND 150
      
    • 在使用<<==>=><>!=BETWEEN运算符,进行精确比较或范围比较时,使用 B 树索引会带来高效。如果匹配模式是以一个纯字符串,而不是一个通配符作为开头的,那么 B 树索引还可用在使用运算符LIKE进行模式匹配的操作里。
  6. 使用慢查询日志找出那些性能低劣的查询。这个日志可以帮助我们找出从索引当中获益的查询,慢查询日志是一个文本文件,它可以用任何文件显示程序打开查看,也可以用 mysqldumpslow 工具来汇总其内容。如果某个给定的查询在这个日志里频繁出现,那么这个查询可能就不是最优的,需要改写,以让它运行得更快。在查看慢查询日志时,请记住“慢”是实时测量出来的,因此,与处于低负载时相比,服务器处于高负载时,会往慢查询日志里写入更多的查询。