读书笔记: 数据库与MySql(2)

目录：
[1] 数据库与Mysql（1）
[2] 数据库与Mysql（2）
[3] 数据库与Mysql（3）

索引在数据库中非常重要，它决定着数据库查询的速度。

---

2 索引

索引是对数据库表中的一列或多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。

2.1 索引简介

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。索引的优点主要有以下几条：

• 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
• 索引可以大大提高数据的查询速度。
• 在实现数据的参考完整性方面，可以加速表与表之间的连接。
• 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间。

当然增加索引也有许多不利的方面，主要如下：

• 创建索引和维护索引需要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
• 索引需要占用磁盘空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
• 对表中的数据进行增删改时，索引也要动态的维护，降低了数据的维护速度。

2.2 索引操作

MySql支持多种方法在单个或多个列上创建索引。在创建表时可以指定索引列，而如果表已经存在，则可以使用ALTER TABLE或者CERATE INDEX语句添加索引。

创建表时指定索引：

CREATE TABLE `example`
(
…
PRIMARY KEY (`id`)
KEY `idx_name` (`name`),
KEY `idx_name_age` (`name`, `age`),
UNIQUE KEY `uk_order` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

本例中，创建了主键索引，普通单列索引(前缀为idx_)，普通复合索引和唯一索引(前缀为uk_)。

添加索引：

ALTER TABLE tableName ADD [UNIQUE] INDEX `idx_xxx` (`xxx`[length], …) [ASC|DESC]

或者

CREATE [UNIQUE] INDEX `idx_xxx ON tableName (`xxx`[length], …) [ASC|DESC]

索引长度和索引排序顺序是可选的。

删除索引：

ALTER TABLE tableName DROP INDEX index_name

或

DROP INDEX index_name ON tableName

2.3 索引的设计原则

索引设计不合理或缺少索引都会对数据库性能造成障碍，高效的索引对于获得良好的性能非常重要。设计索引时应该考虑以下原则：

（1）索引并非越多越好
索引不仅占用磁盘空间，而且会影响INSERT, UPDATE, DELETE等语句的性能。因为当表中的数据更改时，索引也会进行调整和更新。

（2）避免对经常更新的表建立过多的索引
对于经常用于查询的字段应该创建索引，而对于经常更新的表则尽量减少索引的数量，避免建立无效索引。

（3）数据量少的表尽量不使用索引
数据量较少时直接全表遍历查询速度往往比通过索引更快。

（4）列值变化较小时不要建立索引
在列值变化较少时，如性别字段上只有“男”和“女”两个不同值，建立索引不但不会提高查询效率，反而会降低更新速度。

（5）当唯一性是某种数据本身的特征时，指定唯一索引
使用唯一索引能确保定义的列的数据完整性，提高查询速度。

（6）在频繁进行排序或分组的列上建立索引

2.4 MySql索引

索引有很多种类型，可以为不同的场景提供更好的性能。在MySql中，索引是在存储引擎层而不是服务器层实现的。不同存储引擎的索引工作方式和实现方式都是不一样的。

MySql默认的索引类型是BTREE，更准确点来说是B+Tree。

MyISAM索引实现

MyISAM和InnoDB引擎均使用B+Tree索引，但实现方式不一样。
MyISAM的B+Tree索引，叶节点的data域存放的是数据记录的地址，如下图（出自参考资料1）所示：

这里假设表一共有三列，Col1为主键，这是一个MyISAM表的主索引示意图。如果还有辅助索引列如Col2，其索引结构是完全一样的，只是将索引结构中的key换成对应的索引key：

在MyISAM引擎中，索引文件和数据文件是分开的。索引文件具有逻辑顺序，而数据文件则不具有逻辑顺序。查询时先根据索引文件找到数据的物理地址，再继续根据地址找到实际的数据记录。这种索引方式也叫“非聚集索引”。

InnoDB索引实现

InnoDB也使用B+Tree作为索引结果，但其实现方式跟MyISAM却是不同的。
在InnoDB中，表数据文件本身就是一个索引文件，而且key就是数据表的主键。数据记录存储于叶子节点上。如下图所示:

这种数据的物理存放顺序与索引顺序保持一致的索引也叫“聚集索引”。

如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚集索引。

另一个与MyISAM索引不同的是，InnoDB的辅助索引叶子节点数据域存放的是主键值而不是地址，如下图所示：

使用辅助索引查询时需要检索两次，先根据辅助索引获得主键，然后用主键到数据文件（主索引文件）中检索数据记录。

在InnoDB引擎表中，不能使用过长的字段作为主键，因为所有的辅助索引都引用主索引，过长的主索引会使得辅助索引变得很大。另外，非单调字段也不合适作为主键，因为在插入新纪录时为了维持数据文件的B+Tree的特性，会频繁的调整数据存储位置，影响数据插入效率。

2.5 索引使用与优化

上文简单提到过索引的设计原则。事实上，高效地选择和使用索引有很多种方式，如何选择并评估不同索引的性能影响，需要持续不断地学习。

2.5.1 最左前缀原理与相关优化

根据BTree的结构特点可以推测出，BTree索引适用于全键值、键值范围或键前缀查找。而在联合索引中，键前缀也即是最左前缀。
具体点来说，BTree索引适用于以下情况：

(1) 全列匹配
全列匹配即是对索引列进行精确匹配。如果是联合索引，则查询条件中包含全部索引列。

(2) 范围查找
范围查找可以使用索引很好理解，因为根据BTree的索引实现来看，只要找到范围边界值，在边界值内的都是要找的数据。但要注意的是，范围列必须是最左前缀（独立索引可看作只有一个元素的联合索引），范围列后面的列无法用到索引。

(3) 最左前缀匹配
简单点说，就是对于联合索引idx_union(a,b,c)，a，ab，abc均可以使用索引，其他的不行。

(4) 匹配列的前缀字符串
对于索引列col，使用where col like “xxx%”时可以使用索引。键前缀匹配，也可以理解为特殊的最左前缀匹配。

(5) 精确匹配某一列并范围匹配另一列
同样可以理解为特殊的最左前缀匹配。

当查询条件不符合最左前缀时，无法使用索引。当查询条件含有函数，存储过程，条件表达式时，无法使用索引。当查询条件中含有“OR,NOT IN, !=”等特殊符号时不能使用索引。

2.5.2 索引选择性与前缀索引

索引选择性是描述索引对数据记录的区分度的，定义为不重复的索引值和数据表的记录总数（#T）的比值，范围从1/#T到1之间。上文中索引的设计原则里有一条说的是，当列值变化较小时不要建立索引，就是因为此种情况下索引选择性低，在检索时不具有区分度。举个栗子，假设性别列上有三种值，分别为男，女和未知。就算建立了索引，那么找到对应的索引键值之后仍然有大量记录需要继续检索，体现不了索引的高检索效率。

当数据库中索引列的长度较长时，建立索引需要占用很大的空间。对于BLOB、TEXT或者很长的VARCHAR类型的列，甚至不允许使用完整长度的索引。这种情况下可以使用前缀索引。

一般情况下，较长的列的前缀索引的选择性也是足够高的，基本可以满足查询性能。在使用前缀索引时，如何决定前缀的长度呢？可以通过计算索引选择性来进行评估。举个栗子来说，假设要在英文城市名列上建立前缀索引，则可以通过以下方式计算不同长度的前缀的索引选择性：

SELECT COUNT(DISTINCT LEFT(`city`, 3))/COUNT(*) AS sel3, COUNT(DISTINCT LEFT(`city`, 4))/COUNT(*) AS sel4, COUNT(DISTINCT LEFT(`city`, 5))/COUNT(*) AS sel5 from example;

结果如下：

可以看到，当前缀索引长度达到7时，再增加索引长度，选择性提升的幅度已经很小了。当然，这里考虑的是平均选择性(即假设数据分布式均匀的)，在某些情况下数据分布不均匀，需要结合具体情况评估。

找到合适的前缀长度后，则可以创建索引如下：

ALTER TABLE example ADD KEY `idx_city` (city(7))

前缀索引是一种能使索引更小、更快的有效方法，但另一方面也有其缺点，MySql无法使用前缀索引做ORDER BY 和GROUP BY，也无法使用前缀索引做覆盖扫描。

2.5.3 多列索引

经常有一种模糊的建议，即是将where条件里面的列都建上索引。事实上这种做法是错误的。

虽然MySql5.0之后的版本中引入了“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。但是，当MySql使用这种技术优化复杂查询时，往往说明表上的索引建得不够好。

如对于独立的索引列a和b，现有查询语句：SELECT * FROM example WHERE a = xxx AND b = xx，查询时能够同时使用这两个单列索引进行扫描，并将结果合并。通过explain查看执行计划，会在Extra列中看到合并索引。

具体点说，当出现服务器对多个索引做相交操作(多个AND条件)时，意味着需要一个包含所有列的多列索引，而不是多个独立的单列索引；当服务器需要对多个索引做联合操作(多个OR条件)时，通常需要耗费大量的CPU和内存资源在索引合并算法的缓存、排序和合并操作上，特别是当其中有些索引的选择性不高的时候。优化器不会把这些计算到“查询成本”中，这会使得查询成本被低估，导致该执行计划还不如全表扫描。

2.5.4 覆盖索引

MySql也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。也就是说，当索引的叶子节点中已经包含要查询的数据时，则没有必要再回表查询。这种索引中包含所有需要查询字段的值的情况，称之为“覆盖索引”。覆盖索引只需要在索引中检索一次即可。

参考资料：
1.https://blog.csdn.net/u013967628/article/details/84305511