MySql数据库索引介绍

• 写在前面

          数据库索引对我们来说是透明的，因为数据库表创建索引前后，SQL语句都可以正常运行，索引的运用只是数据库引擎工作时候的优化手段。但是，这并不是说数据库索引仅仅是数据库设计开发人员和运维人员的事情，对于一个测试人员，如果对数据库中已有的索引有所了解，可以对测试过程中发现的涉及数据库操作的问题进行深入分析，知其所以然。

• 什么是索引

  索引是一种特殊的文件(MySql数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针，直接在索引中查找符合条件的选项，加快数据库的查询速度，而不是一行一行去遍历数据后才选择出符合条件的。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。

  举个例子，假设我们创建了一个名为people的表：

  CREATE TABLE people ( id SMALLINT NOT NULL, name CHAR(50) NOT NULL );

  然后，我们完全随机把1000个不同name值插入到people表。下图显示了people表所在数据文件的一小部分：

  id	name
  1	Mike
  2	Ella
  ......	......
  1000	Bruce

  可以看到，在数据文件中name列没有任何明确的次序。如果我们创建了name列的索引，MySQL将在索引中排序name列：

  Index
  ......
  Bruce
  Ella
  Evelyn
  ......

  如果我们要查找name等于“Ella”记录的id（SQL命令为“SELECT id FROM people WHERE name='Ella';”），MySQL能够在name的索引中查找“Ella”值，然后直接转到数据文件中相应的行，准确地返回该行的id（2）。在这个过程中，MySQL只需处理一个行就可以返回结果。如果没有“name”列的索引，MySQL要扫描数据文件中的所有记录，即1000个记录！显然，通过索引查找记录至少要比顺序扫描记录快100倍。

• 索引类型

          索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

           MySQL支持的索引包括INDEX、UNIQUE、PRIMARY KEY、FULLTEXT类型的索引。

         1、普通索引INDEX
　           这是最基本的索引类型，而且它没有唯一性之类的限制，多行记录可以包含相同值。普通索引可以通过以下几种方式创建：

• • 创建索引，例如CREATE INDEX indexName ON mytable(username(length));
    （如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。）
  • 修改表，例如ALTER mytable ADD INDEX [indexName] ON (username(length))
  • 创建表的时候指定索引，例如CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   INDEX [indexName] (username(length))   );

          2、唯一性索引

               和“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

                唯一性索引可以用以下几种方式创建：

• • 创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
  • 修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
  • 创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );
      
•     创建了一个 mytable表，来进一步说明下组合索引：
•     CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   city VARCHAR(50) NOT NULL,   age INT NOT NULL  ); 

              为了进一步提供MySQL的效率，就要考虑建立组合索引。如下语句将 name, city, age建到一个索引里：

               ALTER TABLE mytable ADD INDEX name_city_age (username(10),city,age);
               建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

               如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

               建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

               usernname,city,age ；usernname,city；  usernname。

               为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：
               SELECT * FROM mytable WHREE username="admin" AND city="郑州"  SELECT * FROM mytable WHREE username="admin"

               而下面几个则不会用到：

               SELECT * FROM mytable WHREE age=20 AND city="郑州"  SELECT * FROM mytable WHREE city="郑州"

          3、主键
　　       主键是一种特殊的唯一性索引，不允许有空值，但它必须指定为“PRIMARY KEY”。一般是在建表的时候同时创建主键索引，例如CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) );”。也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。注意：每个表只能有一个主键。

          4、全文索引
　　       全文索引FULLTEXT可以在VARCHAR或者TEXT类型的列上创建。它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。

• 建立索引的时机
  一般我们需要在什么情况下建立索引呢？一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：
  SELECT t.Name  FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州'
  此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。
  刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引：
  SELECT * FROM mytable WHERE username like'admin%'
  而下句就不会使用：
  SELECT * FROM mytable WHERE Name like'%admin'
  因此，在使用LIKE时应注意以上的区别。

• 索引的不足之处

  上说了一些使用索引的好处，但索引也不是建立的越多、越长越好，过多的使用索引将会造成滥用。因此索引也会有它的缺点：

   1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
   2.建立索引会占用磁盘空间的索引文件，对后续数据库的增删改都需由额外的操作来更新索引。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
   索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

• 使用索引的注意事项

  使用索引时，有以下一些技巧和注意事项：

  1.索引不会包含有NULL值的列

  只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

  2.使用短索引

  对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。 

  3.索引列排序

  MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

  4.like语句操作

  一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

  5.索引列不能参与计算，保持列“干净”

  比如select * from users where YEAR(adddate)<2007;   
  将在每个行上进行应用函数YEAR(adddate)运算后才能比较，这将导致索引失效而进行全表扫描，因此语句应该改成:select * from users where adddate<‘2007-01-01';

  6.不使用NOT IN和<>操作

  7.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

  8.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。