MySQL索引学习笔记

墨蓝 2022-07-13 05:53 191阅读 0赞

### MySQL索引的使用 ###

我们首先讨论索引，因为它是加快查询的最重要的工具。还有其他加快查询的技术，但是最有效的莫过于恰当地使用索引了。在大量的案例中，都是因为表上没有索引，一般只要加上索引就可以立即解决问题。但这样也并非总是有效，因为优化并非总是那样简单。然而，如果不使用索引，在许多情形下，用其他手段改善性能只会是浪费时间。应该首先考虑使用索引取得最大的性能改善，然后再寻求其他可能有帮助的技术。

#### 索引对单个表查询的影响 ####

索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果一个表有1000行，这比顺序读取至少快100倍。注意你需要存取几乎所有1000行，它较快的顺序读取，因为此时我们避免磁盘寻道。

例如对下面这样的一个student表：

mysql>SELECT * FROM student

\+——+———+———+———+———+  
| id | name | english | chinese | history |  
\+——+———+———+———+———+  
| 12 | Tom | 66 | 93 | 67 |  
| 56 | Paul | 78 | 52 | 75 |  
| 10 | Marry | 54 | 89 | 74 |  
| 4 | Tina | 99 | 83 | 48 |  
| 39 | William | 43 | 96 | 52 |  
| 74 | Stone | 42 | 40 | 61 |  
| 86 | Smith | 49 | 85 | 78 |  
| 37 | Black | 49 | 63 | 47 |  
| 89 | White | 94 | 31 | 52 |  
\+——+———+———+———+———+

这样，我们试图对它进行一个特定查询时，就不得不做一个全表的扫描，速度很慢。例如，我们查找出所有english成绩不及格的学生：

mysql>SELECT name,english FROM student WHERE english<60;

\+———+———+  
| name | english |  
\+———+———+  
| Marry | 54 |  
| William | 43 |  
| Stone | 42 |  
| Smith | 49 |  
| Black | 49 |  
\+———+———+

其中，WHERE从句不得不匹配每个记录，以检查是否符合条件。对于这个较小的表也许感觉不到太多的影响。但是对于一个较大的表，例如一个非常大的学校，我们可能需要存储成千上万的记录，这样一个检索的所花的时间是十分可观的。

如果，我们为english列创建一个索引：

mysql>ALTER TABLE student ADD INDEX (english) ;

如上语句，建立的该索引存储在索引文件中，包含表中每行的english列值，但此索引是在 english的基础上排序的。现在，不需要逐行搜索全表查找匹配的条款，而是可以利用索引进行查找。假如我们要查找分数小于60的所有行，由于索引值是排序的，因此在读到大于60的记录时，我们知道不会再有匹配的记录，可以退出了。如果查找一个值，它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而不用进行表的顺序扫描（如二分查找法）。这样，可以快速定位到第一个匹配的值，以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术，索引技术是个好东西。

因此在执行下述查询

mysql>SELECT name,english FROM user WHERE english<60;

其结果为：  
\+———+———+  
| name | english |  
\+———+———+  
| Stone | 42 |  
| William | 43 |  
| Smith | 49 |  
| Black | 49 |  
| Marry | 54 |  
\+———+———+

你应该可以发现，这个结果与未索引english列之前的不同，它是排序的，原因正式如上所述。

#### 索引对多个表查询的影响 ####

前面的讨论描述了单表查询中索引的好处，其中使用索引消除了全表扫描，极大地加快了搜索的速度。在执行涉及多个表的连接查询时，索引甚至会更有价值。在单个表的查询中，每列需要查看的值的数目就是表中行的数目。而在多个表的查询中，可能的组合数目极大，因为这个数目为各表中行数之积。

假如有三个未索引的表 t1、t2、t3，分别只包含列 c1、c2、c3，每个表分别由含有数值 1 到 1000 的 1000 行组成。查找对应值相等的表行组合的查询如下所示：

此查询的结果应该为 1000 行，每个组合包含 3 个相等的值。如果我们在无索引的情况下处理此查询，则不可能知道哪些行包含那些值。因此，必须寻找出所有组合以便得出与 WHERE 子句相配的那些组合。可能的组合数目为 1000×1000×1000（十亿），比匹配数目多一百万倍。很多工作都浪费了，并且这个查询将会非常慢，即使在如像 MySQL 这样快的数据库中执行也会很慢。而这还是每个表中只有 1000 行的情形。如果每个表中有一百万行时，将会怎样？很显然，这样将会产生性能极为低下的结果。如果对每个表进行索引，就能极大地加速查询进程，因为利用索引的查询处理如下：

>  *  1) 如下从表 t1 中选择第一行，查看此行所包含的值。
>  *  2) 使用表 t2 上的索引，直接跳到 t2 中与来自 t1 的值匹配的行。类似，利用表 t3 上的索引，直接跳到 t3 中与来自 t1 的值匹配的行。
>  *  3) 进到表 t1 的下一行并重复前面的过程直到 t1 中所有的行已经查过。

在此情形下，我们仍然对表 t1 执行了一个完全扫描，但能够在表 t2 和 t3 上进行索引查找直接取出这些表中的行。从道理上说，这时的查询比未用索引时要快一百万倍。

如上所述，MySQL 利用索引加速了 WHERE 子句中与条件相配的行的搜索，或者说在执行连接时加快了与其他表中的行匹配的行的搜索。

#### 多列索引以及最左索引原则 ####

假定你发出下列SELECT语句：

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

如果一个多列索引存在于col1和col2上，适当的行可以直接被取出。如果分开的单行列索引存在于col1和col2上，优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。  
你可以这样创建一个多列索引：

mysql>ALTER TABLE tbl_name ADD INDEX(col1,col2);

而你也可以这样创建分开的单行列索引：

mysql>ALTER TABLE tble_name ADD INDEX(col1);
    mysql>ALTER TABLE tble_name ADD INDEX(col1);

**如果表有一个多列索引，任何最左面的索引前缀能被优化器使用以找出行。例如，如果你有一个3行列索引(col1,col2,col3)，则表示你已经索引了在(col1)、(col1,col2)和(col1,col2,col3)上的搜索能力。**

如果列不构成索引的最左面前缀，MySQL不能使用一个部分的索引。假定你下面显示的SELECT语句：

mysql> SELECT * FROM tbl_name WHERE col1=val1;
    mysql> SELECT * FROM tbl_name WHERE col2=val2;
    mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

如果一个索引存在于(col1、col2、col3)上，只有上面显示的第一个查询使用索引。第二个和第三个查询确实包含索引的列，但是(col2)和(col2、col3)不是(col1、col2、col3)的最左面前缀。

对于一个多列索引，如果在WHERE子句的所有AND层次使用索引，将不使用来索引优化查询。为了能够使用索引优化查询，必须把一个多列索引的前缀使用在一个AND条件组中。

#### 索引的弊端 ####

一般情况下，如果 MySQL 能够知道怎样用索引来更快地处理查询，它就会这样做。这表示，在大多数情况下，如果您不对表进行索引，则损害的是您自己的利益。可以看出，作者描绘了索引的诸多好处。但有不利之处吗？是的，有。实际上，这些缺点被优点所掩盖了，但应该对它们有所了解。

首先，索引文件要占磁盘空间。如果有大量的索引，索引文件可能会比数据文件更快地达到最大的文件尺寸。其次，索引文件加快了检索，但增加了插入和删除，以及更新索引列中的值的时间（即，降低了大多数涉及写入的操作的时间），因为写操作不仅涉及数据行，而且还常常涉及索引。一个表拥有的索引越多，则写操作的平均性能下降就越大。

### 索引的操作 ###

分为: 普通索引、唯一索引和主索引

#### 1. 普通索引 ####

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

#### 2. 唯一索引 ####

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

#### 3. 主索引 ####

在前面已经反复多次强调过：必须为主键字段创建一个索引，这个索引就是所谓的”主索引”。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是 PRIMARY而不是UNIQUE。主索引一张表只能有一个。

#### 上述三种索引的创建方法 ####

ALTER TABLE table_name ADD INDEX index_name (column_list)
    
    ALTER TABLE table_name ADD UNIQUE (column_list)
    
    ALTER TABLE table_name ADD PRIMARY KEY (column_list)
    //或者
    CREATE INDEX index_name ON table_name (column_list)
    
    CREATE UNIQUE INDEX index_name ON table_name (column_list)
    //create不能创建primary索引

其中table\_name是要增加索引的表名，column\_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index\_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

#### 查看索引 ####

mysql> show index from table_name;
    
    mysql> show keys from table_name;

· Table

表的名称。

· Non\_unique

如果索引不能包括重复词，则为0。如果可以，则为1。

· Key\_name

索引的名称。

· Seq\_in\_index

索引中的列序列号，从1开始。

· Column\_name

列名称。

· Collation

列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。

· Cardinality

索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。

· Sub\_part

如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

· Packed

指示关键字如何被压缩。如果没有被压缩，则为NULL。

· Null

如果列含有NULL，则含有YES。如果没有，则该列含有NO。

· Index\_type

索引实现（BTREE, FULLTEXT, HASH, RTREE）。

#### 索引的删除 ####

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

DROP INDEX index_name ON talbe_name
    ALTER TABLE table_name DROP INDEX index_name
    ALTER TABLE table_name DROP PRIMARY KEY

其中，前两条语句是等价的，删除掉table\_name中的索引index\_name。

第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

### 聚集索引与非聚集索引 ###

#### 聚集索引 ####

一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。  
　 聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。  
　 聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行 的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此 类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节 省成本。  
　　当索引值唯一时，使用聚集索引查找特定的行也很有效率。例如，使用唯一雇员 ID 列 emp\_id 查找特定雇员的最快速的方法，是在 emp\_id 列上创建聚集索引或 PRIMARY KEY 约束。

#### 非聚集索引 ####

一种索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。  
　　  
索引是通过二叉树的数据结构来描述的，我们可以这么理解聚集索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块

**聚集索引不能随便创建，否则会是性能上的噩梦，实际上在一部情况下你不会遇到需要创建聚集索引的情况。InnoDB按照主键进行聚集，如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引，InnoDB就会定义隐藏的主键然后在上面进行聚集。所以，对于 聚集索引 来说，你创建主键的时候，自动就创建了主键的聚集索引。**

关于聚集索引与非聚集索引，要搞明白下面几个问题:

第一：聚集索引的约束是唯一性，是否要求字段也是唯一的呢？

分析：如果认为是的朋友，可能是受系统默认设置的影响，一般我们指定一个表的主键，如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引,SQL会默认在此字段上创建一个聚集索引，而主键都是唯一的，所以理所当然的认为创建聚集索引的字段也需要唯一。

结论：聚集索引可以创建在任何一列你想创建的字段上，这是从理论上讲，实际情况并不能随便指定，否则在性能上会是恶梦。

第二：为什么聚集索引可以创建在任何一列上，如果此表没有主键约束，即有可能存在重复行数据呢？

粗一看，这还真是和聚集索引的约束相背，但实际情况真可以创建聚集索引，分析其原因是：如果未使用 UNIQUE唯一 属性来创建聚集索引，数据库引擎将向表自动添加一个四字节 uniqueifier 列。必要时，数据库引擎 将向行自动添加一个 uniqueifier 值，使每个键唯一。此列和列值供内部使用，用户不能查看或访问。也就是说，对于用户来说是重复的行，但在数据库引擎中不是重复的。

第三：在数据库中通过什么描述聚集索引与非聚集索引的？

索引是通过二叉树的形式进行描述的，我们可以这样区分聚集与非聚集索引的区别：聚集索引的叶节点就是最终的数据节点，而非聚集索引的叶节仍然是索引节点，但它有一个指向最终数据的指针。

第四：在主键是创建聚集索引的表在数据插入上为什么比主键上创建非聚集索引表速度要慢？

有了上面第三点的认识，我们分析这个问题就有把握了，在有主键的表中插入数据行，由于有主键唯一性的约束，所以需要保证插入的数据没有重复。我们来比较下主键为聚集索引和非聚集索引的查找情况:**聚集索引由于索引叶节点就是数据页，所以如果想检查主键的唯一性，需要遍历所有数据节点才行，但非聚集索引不同，由于非聚集索引上已经包含了主键值，所以查找主键唯一性，只需要遍历所有的索引页就行，这比遍历所有数据行减少了不少IO消耗。这就是为什么主键上创建非聚集索引比主键上创建聚集索引在插入数据时要快的真正原因。**