程序员应该怎样使用MYSQL索引

灰太狼 2022-06-10 06:10 311阅读 0赞

** MySQL索引的概念**

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。它分为聚簇索引和非聚簇索引：聚簇索引能提高多行检索的速度、非聚簇索引对于单行的检索很快。

**     ** " 聚簇索引"平时习惯逛图书馆的童鞋可能比较清楚，如果你要去图书馆借一本书，最开始是去电脑里面查书名然后根据书名来定位藏书在那个区，哪个书柜，哪一行，第多少本。清晰明确，因为藏书的结构与图书室的位置，书架的顺序，书本的摆放顺序与书籍的编号都是从大到小一致的顺序摆放的，所以很容易找到。例如：你的目标藏书在C区2柜3排5仓，那么你走到B区你就很快知道前面就快到C区，你直接奔着2柜区就能找到。 这就同于" 聚簇索引"的功效，" 聚簇索引"实际存储的循序结构与数据存储的物理机构是一致的索引，所以通常来说物理顺序结构只有一种，那么一个表的" 聚簇索引"也只能有一个，通常默认都是主键，设置了主键，系统默认就为你加上了聚簇索引，当然有人说我不想拿主键作为聚簇索引，我需要用其他字段作为索引，当然这也是可以的，这就需要你在设置主键之前自己手动的先添加上唯一的聚簇索引，然后再设置主键，这样就木有问题啦。聚簇索引是顺序结构与数据存储物理结构一致的一种索引，并且一个表的聚簇索引只能有唯一的一条**。**

**  ** " 聚簇索引"同样的，如果你去的不是图书馆，而是某城市的商业性质的图书城，那么你想找的书就摆放比较随意了，由于商业图书城空间比较紧正，藏书通常按照藏书上架的先后顺序来摆放的，所以如果查询到某书籍放在C区2柜3排5仓，但你可能要绕过F区，而不是A.B.C.D...连贯一致的，也可能同在C区的2柜，书柜上第一排是计算机类的书记，也可能最后一排就是医学类书籍；那么对照着来看非聚簇索引的概念就比较好理解了，非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系，与数据的存储物理结构没有关系；一个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引。

##### MySQL索引的类型 #####

1、**普通索引(normal)**

普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。

2、**唯一索引(unique)**

唯一索引（unique）不允许重复的索引，但允许有空值（注意和主键不同）。例如：身份证号用作索引时，可设置为unique。

**   3、全文索引(fulltext)**

全文索引（fulltext）是从MySQL从3.23.23版开始支持全文索引，fulltext索引仅可用于 MyISAM 引擎表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建或者使用ALTER TABLE 或CREATE INDEX被添加。针对文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头（LIKE 'word%'）的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成 的较大段文字，普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。这类场合正是全文索引(fulltext)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数 据记录。

全文索引（fulltext）索引是按照分词原理建立索引的。西文中，大部分为字母文字，分词可以很方便的按照空格进行分割。但很明显，中文不能按照这种方式进行分词。那又怎么办呢？这个向大家介绍一个mysql的中文分词插件MYSQLCFT，有了它，就可以对中文进行分词。

##### MySQL索引的方式 #####

**1、BTREE方式**

BTREE方式就是一种将索引值按一定的算法，存入一个树形的数据结构中，相信学过数据结构都对当初学习二叉树这种数据结构的经历记忆犹新。例如：二叉树，每次查询都是从树的入口root开始，依次遍历node，获取leaf。

BTREE方式在MyISAM引擎和InnoDB引擎里，稍微有点不同。 InnoDB里，有两种形态：一是：primary key形态（主键索引），其leaf node里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据。二是：secondary index(普通索引)，其leaf node和普通的BTREE差不多，只是还存放了指向主键的信息。MyISAM里，主键和其他的并没有太大区别。不过和InnoDB不太一样的地方是在MYISAM里，leaf node里存放的不是主键的信息，而是指向数据文件里的对应数据行的信息。

**2、HASH方式**

Hash这个词，可以说，自打我们开始码的那一天起，就开始不停地见到和使用到了。其实，hash就是一种（key=>value）形式的键值对，如数学中的函数映射，允许多个key对应相同的value，但不允许一个key对应多个value。正是由于这个特性，hash很适合做索引，为某一列或几列建立hash索引，就会利用这一列或几列的值通过一定的算法计算出一个hash值，对应一行或几行数据（这里在概念上和函数映射有区别，不要混淆）。在JAVA语言中，每个类都有自己的hashcode()方法，没有显示定义的都继承自object类，该方法使得每一个对象都是唯一的，在进行对象间equal比较，和序列化传输中起到了很重要的作用。hash的生成方法有很多种，足可以保证hash码的唯一性，例如在MongoDB中，每一个document都有系统为其生成的唯一的objectID（包含时间戳，主机散列值，进程PID和自增ID）也是一种hash的表现。

当我们为某一列或某几列建立hash索引时（目前就只有MEMORY引擎显式地支持这种索引），会在硬盘上生成类似如下的文件：

<table> 
 <tbody> 
  <tr> 
   <td>hash值&nbsp;</td> 
   <td>存储地址&nbsp;&nbsp;&nbsp;&nbsp;</td> 
  </tr> 
  <tr> 
   <td>1db54bc745a1</td> 
   <td>77#45b5&nbsp;</td> 
  </tr> 
  <tr> 
   <td>4bca452157d4</td> 
   <td>76#4556,77#45cc…</td> 
  </tr> 
 </tbody> 
</table>

hash值即为通过特定算法由指定列数据计算出来，磁盘地址即为所在数据行存储在硬盘上的地址（也有可能是其他存储地址，其实MEMORY会将hash表导入内存）。这样，当我们进行WHERE age = 18 时，会将18通过相同的算法计算出一个hash值==>在hash表中找到对应的储存地址==>根据存储地址取得数据。所以，每次查询时都要遍历hash表，直到找到对应的hash值，例如：BTREE和HASH方式区分的第4条，数据量大了之后，hash表也会变得庞大起来，性能下降，遍历耗时增加，例如：BTREE和HASH方式区分的第5条。

由于hash索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。那为什么还需要其他的树形索引呢？

**3、BTREE和HASH方式区分**

（1）Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。  
        由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

（2）Hash 索引无法被用来避免数据的排序操作。  
      由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。

（3）Hash 索引不能利用部分索引键查询。  
     对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

（4）Hash 索引在任何时候都不能避免表扫描。  
     前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。  
       对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

##### MySQL索引的应用 #####

**  1、普通索引**

**2、唯一索引**

**    ** 如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简 化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在 某个记录的这个字段里出现过了；如果是MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯 一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

**   3、主索引**

**   **  必须为主键字段创建一个索引，这个索引就是所谓的”主索引”。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是 PRIMARY而不是UNIQUE。

**   4、外键索引**

**   ** 如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

**   5、复合索引**

**    ** 索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引。这种索引的特点是MySQL可以有选择地使用一个这样的索引。如果查询操作只需要用到columnA数据列上的一个索引，就可以使 用复合索引INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX(A, B, C)可以当做A或(A, B)的索引来使用，但不能当做B、C或(B, C)的索引来使用。

**   6、索引的长度**

**      ** 在为CHAR和VARCHAR类型的数据列定义索引时，可以把索引的长度限制为一个给定的字符个数(这个数字必须小于这个字段所允许的最大字符个数)。这 么做的好处是可以生成一个尺寸比较小、检索速度却比较快的索引文件。在绝大多数应用里，数据库中的字符串数据大都以各种各样的名字为主，把索引的长度设置 为10~15个字符已经足以把搜索范围缩小到很少的几条数据记录了。  
      在为BLOB和TEXT类型的数据列创建索引时，必须对索引的长度做出限制；MySQL所允许的最大索引长度是255个字符。

##### MySQL索引建立时机 #####

在WHERE和JOIN中出现的列需要建立索引，MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。在以通配符%和\_开头作查询时，MySQL不会使用索引。

使用索引：

SELECT \* FROM mytable WHERE username like'admin%'

不使用索引：

SELECT \* FROM mytable WHEREt Name like'%admin'

##### MySQL索引不足之处 #####

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

##### 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

查询时将在每个行上进行运算，这将导致索引失效而进行全表扫描。

**  6、不使用NOT IN和<>操作**

##### MySQL索引优化 #####

只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令 之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了 MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

在不确定应该在哪些数据列上创建索引的时候，人们从EXPLAIN SELECT命令那里往往可以获得一些帮助。这其实只是简单地给一条普通的SELECT命令加一个EXPLAIN关键字作为前缀而已。有了这个关键 字，MySQL将不是去执行那条SELECT命令，而是去对它进行分析。MySQL将以表格的形式把查询的执行过程和用到的索引(如果有的话)等信息列出 来。