MySQL学习笔记整理（四）

发表于 | 分类于

前言

       上篇已经讲了怎么去建立索引，对索引的基本操作，这篇讲如果去适当的使用索引。我们先了解索引的优缺点，然后在这个基础上分析使用索引的恰当时机。

索引的优缺点

       创建索引可以大大提高系统的性能。

  1. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
  2. 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
  3. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
  4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
  5. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

       也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？因为，增加索引也有许多不利的方面。

  1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
  2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
  3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

适合建立索引的情况

一般来说，应该在这些列上创建索引：

  1. 在经常需要搜索的列上，可以加快搜索的速度；
  2. 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
  3. 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
  4. 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
  5. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
  6. 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

不适合建立索引的情况

一般来说，不应该创建索引的的这些列具有下列特点：

  1. 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
  2. 对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
  3. 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
  4. 当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

此处为参考链接。

索引的使用分析

explain

       下图说明利用这个指令出现的每项及其含义：

使用索引的几个注意事项

       我们把dept这张表的deptno列设为主键，然后在把dname和loc列设为复合索引。代码如下：

//添加主键
alter table dept add primary key(deptno);
//  dname 左边的列,loc就是右边的列
alter table dept add index my_ind (dname,loc);
  1. 说明，如果我们的表中有复合索引(索引作用在多列上)， 此时我们注意:对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。
    explain select * from dept where loc='aaa'\G；//此处就不会使用到索引

  1. 对于使用like的查询，查询如果是 ‘%aaa’ 不会使用到索引,‘aaa%’ 会使用到索引。

    explain select * from dept where dname like ‘%aaa’\G

    不能使用索引，即，在like查询时，关键的 ‘关键字’ , 最前面，不能使用 % 或者 _这样的字符.， 如果一定要前面有变化的值，则考虑使用 全文索引->sphinx.

  2. 如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引, 我们建议大家尽量避免使用or 关键字

    select * from dept where dname=’xxx’ or loc=’xx’

    select * from dept where dname=’xxx’ or loc=’xx’ or deptno=45

    这两个语句执行比较就可以等到结果。

  3. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’), 也就是，如果列是字符串类型，就一定要用 ‘’ 把他包括起来.

  4. 如果mysql估计使用全表扫描要比使用索引快，则不使用索引。情况特殊，比如空表或者记录只有一条的时候。

查询索引使用情况

       如何查看索引使用的情况，就像看缓存命中率一样:

show status like ‘Handler_read%’;

tips：

handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。

handler_read_rnd_next:这个值越高，说明查询低效。

说明

       文中出现的图片，文字描述有些来自互联网，但是出处无法考究，如果侵犯您的相关权益，请联系我，核实后我会马上加上转载说明。谢谢！！！