MySQL运维05-MySQL的索引

豪杰笑开怀

已于 2023-04-06 14:52:35 修改

阅读量378

点赞数

分类专栏： MySQL运维文章标签： mysql 运维数据库 sql 服务器

于 2023-04-06 14:40:25 首次发布

本文链接：https://blog.csdn.net/oddrock/article/details/129989143

版权

MySQL运维专栏收录该内容

36 篇文章 6 订阅

订阅专栏

1、聚集索引(cluster index)和非聚集索引(二次索引)

1.1、聚集索引的定义

如果索引键值的逻辑顺序与索引所服务的表中相应行的物理顺序相同，也就是数据和索引（B+树）存储在一起，那么该索引被称为聚集索引（cluster index），也称为聚簇索引、簇索引，反之为非聚集索引或二级索引。InnoDB主键用的就是聚集索引。

1.2、聚集索引的优点

因为索引和数据存储在一起，所以查找数据通常比非聚集索引更快。

1.3、聚集索引的缺点

聚集索引对I/O密集型的负荷性能提升最佳，但如果表数据在内存中，那么聚集索引并没有明显益处。
更新聚集索引列的成本比较高，因为不得不将更新的行移动到新的位置。
全表扫描的性能不佳，尤其是数据存储得不那么紧密时，或者因为页分裂（page split）而导致物理存储不连续。
二级索引的叶节点中存储了主键索引的值，如果主键采用的是较长的字符，那么索引可能会很大。

2、覆盖索引(covering index)

2.1、覆盖索引的定义

所谓“覆盖索引”是指所有数据都可以从索引中得到，而不需要去读取物理记录。例如某个复合索引idx_a_b_c建立在表tb1的a、b、c列上，那么对于如下的SQL语句：select a,b from tb1 where a=? and b=? and c=?; MySQL可以直接从索引idx_a_b_c中获取所有数据。使用覆盖索引也可以避免二次索引查找。
在EXPLAIN命令输出的查询计划里，如果Extra列是“using index”，那就表示使用的是覆盖索引。

2.2、覆盖索引的使用

复合索引只支持一个范围查询，并且一旦出现范围查询，到这个范围查询的条件为止使用复合索引，后面的列不能使用复合索引。所以创建复合索引时，应把涉及范围查找的列放到复合索引的最后。

# 建立索引
CREATE INDEX idx_a ON tb1(a, b, c, d);

# 以下查询使用索引情况
WHERE a=? AND b=? AND c > 10000 AND d<100000;	# d<100000这个筛选操作并不会走索引，因为前面的c>10000已经使用了范围查询
WHERE a>? AND b=? AND c= 10000 AND d = 100;	# a列上有范围查找，那么b、c、d等列上的索引信息将都不能被利用。

3、主键

3.1、基本要求

建议主键是整型：相比字符串更节省存储空间，因为二级索引都会存储主键。
有业务主键就不用自增主键：如果表中包含一列能够确保唯一、非空（NOT NULL），以及能够用来定位一条记录的字段，就不要因为传统而觉得一定要加上一个自增ID做主键。

3.2、自增主键

3.2.1、自增主键的定义

自增列是MySQL里的一种特殊的整型，我们定义一个列为整型的同时，可以设置它是否为自增的。一个表只能有一个列是自增列，且自增列必然是主键列。
示例如下：create table t21(id int auto_increment primary key , name varchar(10));

3.2.2、自增主键的参数

自增列增长受如下两个MySQL全局参数的影响，这两个参数默认值都是1：
auto_increment_offset：自增起始值
auto_increment_increment：自增步长
可以单独定义某个表的自增起始值，如：mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

3.2.3、自增主键可以插入指定的值

自增主键可以插入自行指定的值。如果插入的是0或NULL，则InnoDB会认为没有设定值，然后帮你自增一个值，而且这个自增的值会在目前表里该列最大值的基础上加1。

4、创建索引的注意事项

优先选重复值少的列建索引：所谓高选择性，就是这个列的“基数（cardinality）”值较大。基数指的是一个列中不同值的个数，基数较大，意味着重复值少，索引工作效果好。基数很小的列，如性别就不适合建立索引。存在一种特殊的情况，有些列虽然基数很小，但数据分布很不均匀因此也会导致某些值的记录数很少，那么也适合创建索引加速查找这部分数据。
优先建更短的索引：应避免主键过长，导致二级索引过大。可以考虑前缀索引，前缀索引仅索引前面一部分字符（值），但应确保所选择的前缀的长度可以保证大部分值是唯一的。示例如下：ALTER TABLE test.test1 ADD KEY (col(6))
优先建唯一索引：如果是唯一值的列，创建唯一索引会更佳，也可以确保不会出现重复数据。
主键优先选择整数型：InnoDB的非主键索引(二级索引)会存储主键的值，所以主键最好是整型值，如自增ID。这样可以减小二级索引的大小。
避免创建太多索引或无效索引：单个索引效率不高的时候，MySQL往往就会选择全表扫描。太多的索引可能会导致索引所占用的磁盘空间比实际数据还大得多。
避免定期重建索引：定期重建索引在早期数据库中用得多，主要是经过长期的生产运行索引越来越不平衡。但索引重建将导致服务变得不可用，而且在大部分情况下，重建与否性能上没区别。唯一可能的场景是在大量删除导入数据后，会导致数据表严重变形，可能需要重建索引。数据库索引本来就应该是“不好不坏”的状态，不要期望它始终以一种理想的状态在运行。
导入数据时优先按主键顺序：最好是按主键的顺序导入数据，如果导入大量随机id的数据，那么可能需要运行OPTIMIZE TABLE命令来优化表。

5、使用索引的注意事项

5.1、索引会按最左前缀原则进行匹配

对于非复合索引，按最左前缀原则进行匹配：

# 建立索引
CREATE INDEX idx_a ON tb1(a);

# 以下查询使用索引情况
WHERE a=?		# 会用到索引
WHERE a like ‘xxx%’		# 会用到索引，因为最左前缀原则
WHERE a like ‘%xxx’		# 不会用到索引

对于复合索引，筛选记录的条件应能组成复合索引最左边的部分，即也按最左前缀的原则进行匹配。

# 建立索引
CREATE INDEX idx_a_b_c ON tb1(a,b,c);

# 以下查询都会用到复合索引，但情况有所不同
WHERE a=?
WHERE a=? AND b=?
WHERE a=? AND b=? AND c=?
WHERE a=? AND c=?    #注意这个查询仅仅利用了MySQL索引的a列信息

# 以下查询无法用到复合索引：
WHERE b=? AND c=?

5.2、不支持表达式和函数索引

WHERE条件中的索引列不能是表达式，也不能是函数。

5.3、优先使用主键和聚集索引

基于主键存取数据是最高效的，使用二级索引存取数据则需要进行两次索引查找。

5.4、尽量利用索引排序，避免文件排序

5.4.1、索引排序和文件排序

MySQL有两种方式可以产生有序的结果。一种是使用文件排序（filesort）来对记录集进行排序，另一种是扫描有序的索引。
当没有可以利用的有序索引来取得有序的数据时，MySQL只能将取得的数据在内存中进行排序，然后再将数据返回给客户端，这就是文件排序。使用文件排序的方式，对小结果集进行排序会很快，但是如果是对大量的数据排序，速度将会很慢。我们应尽量利用索引来排序。

5.4.2、如何使用索引排序

尽量保证索引列和ORDER BY的列相同，且各列均按相同的方向排序。
如果要连接多张表，那么ORDER BY引用的列需要在表连接的顺序的首张表内。
利用索引来排序同样要遵循最左前缀的规则，前导列（等于确定值）加上排序列（ORDER BY的列）可以组合成最左前缀的也行。比如，对于创建在表table1上的复合索引idx_a_b_c（创建在列a、b、c上），如下两种情况都能使用索引排序：

SELECT * FROM  table1 ORDER BY a,b,c;
SELECT * FROM table1 WHERE a=? AND b=? ORDER BY c;

5.4.3、如何查看是否使用了索引排序

可以用EXPLAIN工具确认查询是否使用了文件排序，如果extra列显示using filesort，就表示使用了文件排序。

6、索引优化步骤

生产环境中数据库出现性能问题，有80%的原因是索引导致的，表结构不易变动，而调整索引或SQL往往可以很快就能解决问题。在开发或上线后，可遵循以下的方法和步骤进行优化。

测量性能,，找到慢SQL：最好在应用程序中记录访问数据库的性能日志，这样就可以有一个直观全面的统计，特别是那些对数据库操作频繁、耗时的SQL。但如果暂时没有应用级别的SQL监控，也可以靠数据库自身的慢查询日志来定位问题，只是这样做可能存在滞后，不能及时发现性能问题。
查看执行计划：找到消耗资源最多的查询请求后，可以使用EXPLAIN工具查看其执行计划，检查是否走的是合适的索引。
优化索引：根据数据量、数据类型等信息及表之间的关系，按照索引使用和优化经验，调整或增加索引。
测试验证：不要在线上环境进行测试验证，除非是非常紧急的情况。应该选择在开发环境中尽量使用和线上环境一样的数据规模，来进行验证测试。
上线：当确认优化达到了预期的效果后，就可以安排上线了。

7、总结

MySQL的索引分为聚集索引和非聚集索引，聚集索引就是索引和数据存储在一起，这样不需要查完索引以后再第二次去查询数据。也正因为需要二次查找这个缘故，非聚集索引也叫二级索引。因此，尽量使用聚集索引。InnoDB的主键就是聚集索引。
覆盖索引是查询的数据列都包含在索引中，不需要再去读元组数据了，同样也尽量应该使用覆盖索引。
MySQL的索引是按最左前缀原则匹配的，就是一是非复合索引不支持字符串匹配时最左边有百分号的查找，二是对复合索引where和order条件里的列的顺序要和复合索引的列的顺序保持一致。
MySQL不支持包含表达式、函数的列使用索引。
如果排序没有使用到索引，就会发生文件排序(using filesort)，这是尽量要避免的情况。
创建索引时，优先选重复值少的列建索引，优先建更短的索引，优先创建唯一索引，避免创建更多索引，避免定期重构索引（除非导入了大量没有按主键顺序排列的数据）。
自增主键的起始值和步长默认都是1，一个表里最多只有一个自增列，而且自增列必然是主键。