面试：MySQL优化--索引、SQL

卡卡罗特_Kakarrot
306 阅读15分钟

各位读者大家好呀！今天小卡给大家带来的是MySQL优化的一些学习分享~

3e18656f88209fd9c52b6e54b835c7de.jpeg

索引

  1. 避免索引失效

在编写 SQL 语句时，不经意间往往可能会写出导致索引失效的语句。对于刚接触数据库的同学来说，这种情况常常不易察觉。这可能会让人误以为为某个字段添加了索引，却没有得到预期的性能提升，甚至在某些情况下查询性能反而变差了。

遇到这种情况时，我们可以先查看查询计划，确定 SQL 执行时是否使用了索引。如果没有使用索引，可能是因为 SQL 语句的编写方式导致了索引失效。所以在下面我将列出常见的因为sql的编写方式导致的索引失效的一些情况。

  1. 对索引使用左或者左右模糊匹配（like %xx 或者 like %xx%）

    因为索引树是通过有序数据进行多分搜索的，只有最左匹配才符合索引的排序，可以通过最左前缀与索引字段的比较定位目标数据。

  2. 对索引使用函数（length(name)）

    对索引使用函数后得到的过滤条件将不会是索引字段，所以不能利用索引进行查询 但是我们可以对函数建立索引。
alter table t user add key idx name (length(name));

select id from user where length(name)=6;

  1. 对索引进行表达式计算

    索引失效的原因和对索引使用函数的原因是一样的。

  2. 对索引隐式类型转换

    因为对索引进行隐式的类型转换后可能转换后过滤条件的类型与索引字段类型不相同，所以也就不能进行比较，从而导致索引失效。
//假设有一个表 `users`，其中 `id` 字段是一个整数类型（如 `INT`），
//而你执行查询时使用了一个字符串类型的值作为条件：
SELECT * FROM users WHERE id = '123';

不同的数据库管理系统（如 MySQL、PostgreSQL、Oracle）之间的隐式类型转换规则略有差异，但通常都支持一些常见的数据类型之间的隐式转换。以下是 SQL 中常见的隐式类型转换：

字符串与数值类型的转换

  • 字符串 ↔ 整数类型（如 VARCHAR ↔ INT）：
    数据库会尝试将字符串转换为整数。例如，'123' 可以隐式转换为 123。

    示例：

    SELECT * FROM users WHERE id = '123'; -- 如果 id 是整数类型，数据库会尝试将 '123' 转为整数

  • 字符串 ↔ 浮点类型（如 VARCHAR ↔ FLOAT、DECIMAL）：
    类似地，'12.34' 可以隐式转换为浮点数 12.34。

数值类型之间的转换

  • 整数类型 ↔ 浮点类型（如 INT ↔ FLOAT）：
    数据库会将整数自动转换为浮点数进行运算。例如，INT 类型的值可以隐式转换为 FLOAT 进行计算。

    示例：

    SELECT * FROM products WHERE price = 100; -- 如果 price 是浮点类型，这里的 100 会被转为 100.0

  • 精度不同的数值类型之间的转换（如 SMALLINT ↔ INT、INT ↔ BIGINT）：
    数据库会尝试将较小精度的数值类型转换为较大精度的数值类型。

日期与字符串类型的转换

  • 日期 ↔ 字符串（如 DATE ↔ VARCHAR）：
    数据库会尝试将字符串解析为日期格式，或将日期转换为字符串。例如，'2023-01-01' 可以隐式转换为 DATE 类型。

    SELECT * FROM orders WHERE order_date = '2023-01-01'; -- 如果 order_date 是 DATE 类型

  • 日期 ↔ 时间戳（如 DATE ↔ TIMESTAMP）：
    有些数据库支持将日期类型自动转换为带时间戳的类型。

布尔类型与数值或字符串的转换
  • 布尔类型 ↔ 整数类型（如 BOOLEAN ↔ INT）：
    有些数据库会将 TRUE 转换为 1，将 FALSE 转换为 0。
  • 布尔类型 ↔ 字符串（如 BOOLEAN ↔ VARCHAR）：
    有些数据库支持将布尔值转换为字符串（如 TRUE → 'TRUE'）。
特殊类型转换
  • NULL 与任意类型： NULL 可以与任何类型进行比较，但会遵循空值的规则。例如，NULL 与整数、字符串、日期等类型进行运算时，结果仍然是 NULL。

  1. 联合索引非最左匹配

在创建联合索引（a,b,c）时，是根据联合索引字段的大小建立索引树的是根据a的大小进行排序，如果a相等时则根据b的大小进行排序以此类推b相等时根据c的大小进行排序，所以只有符合联合索引列最左匹配的才符合联合索引的排序规则，才能够使用联合索引。（不过mysql新版本队联合索引有优化） 假设我们在 employees 表上创建了一个联合索引 idx_name_age_salary，顺序为 (name, age, salary)：

CREATE INDEX idx_name_age_salary ON employees (name, age, salary);
示例 1：符合最左前缀规则的查询

以下查询从索引的第一个字段 name 开始，然后依次使用 age 和 salary，符合最左前缀规则，索引可以正常使用：

SELECT * FROM employees WHERE name = 'John' AND age = 30;

此查询使用了 name 和 age 字段，这样查询引擎会利用 idx_name_age_salary 索引，加快查询速度。

示例 2：不符合最左前缀规则，导致索引失效

以下查询跳过了第一个字段 name，直接使用 age 作为条件：

SELECT * FROM employees WHERE age = 30 AND salary = 5000;

由于查询条件中没有包含 name 字段，最左匹配规则被打破，因此 idx_name_age_salary 索引将失效，数据库不会使用这个索引，而是会进行全表扫描。

示例 3：只使用部分最左字段，可以利用部分索引

如果查询条件中只使用了索引的第一个字段 name，即使没有包含其他字段，数据库仍然可以利用该索引：

SELECT * FROM employees WHERE name = 'John';

在这个查询中，虽然未使用 age 和 salary 字段，但由于包含了 name 字段，仍然符合最左前缀规则，因此 idx_name_age_salary 索引会被使用。

  1. where中的or

这是因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条 件列不是索引列，就会进行全表扫描。

  1. 索引优化

前缀索引和索引的选择性

在考虑为某一列建立索引时，如果该列长度过大，例如很长的支付串，通常我们可以用该列的Hash值来代替该列从而达到缩短索引长度的目的，但是有时候这样做还是不够，所以我们还可可以通过为该列建立前缀索引来节省空间和提高查询速度，前缀索引顾名思义就是取某一列的最左前缀作为索引字段建立索引，但是在确定前缀长度时一定要保证前缀的选择性，索引的选择性越高查询时的效率也就越高遍历的列也就越少，所以前缀索引应该要足够长以保证其选择性足够高，但同时又不能太长（为了节省空间），那么多长的前缀它的选择性才叫高呢？只要索引的选择性接近其整个列此时它的选择性就已经足够高了，换句话说就是前缀的技术接近索引整个列的基数。

我们可以通过sql查询来计算我们的前缀长度如下所示计算前缀的基数和整个列的基数

#查询完整列的基数
select count(*) as cnt, city from `city` group by city order by cnt desc

#查询前缀的基数
select count(*) as cnt, left(city,3) as pref from `city` group by pref order by cnt desc

索引覆盖

索引覆盖就是当我们在使用二级索引查询数据时如果索引的索引列或者是二级索引的叶子结点数据能够覆盖我们的查询列那么此时就不再需要回表到聚簇索引去获取查询列，而是直接在查询二级索引时返回，从而大大的提高我们的查询效率。

索引下推

如下查询中就用打了索引下推。

CREATE TABLE `user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(11) NOT NULL DEFAULT '未填写',
  `gender` CHAR(1) NOT NULL CHECK (`gender` IN ('男', '女')),
  `address` VARCHAR(30) NOT NULL DEFAULT '无',
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modify_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  index idx_create_time_modify_time (`create_time`,`modify_time`) 
);

selcet * from `user` where create_time<2022 and modfi_tiem=2023

在上面的例子中虽然在建立了联合索引（create_time,modfiy_time）但是由于在条件列中create_time是做<比较的所以后面的字段就会失效，但是由于有索引下推的缘故，在回表之前还是会对结果进行第二个字段的判断过滤掉不符合的结果，从而减少了回表的次数。

使用索引扫描来做排序

在mysql中在没有使用索引的情况下对数据进行排序，对我们查询语句的查询效率是有一定影响的特别是对大量数据进行排序时由于sort_buffer_size内存空间的不足将会进行外部文件排序，而外部文件排序将会增加磁盘io的次数。

外部文化排序:内存放不下时，会把一部分排序的结果放在磁盘上，空出内存空间，继续排序，所以外部排序一般使用【归并排序】可以这么简单理解，MySQL将需要排序的数据分成 12 份，每一份单独排序后存在这些临时文件中，然后把这 12 个有序文件再合并成一个有序的大文件。

所以利用索引扫描来做排序相比之下是一种非常高效的做法。

但是也并不是所有的场景下用索引扫描来做排序性能都比filesort高，因为通过二级索引扫描来做排序时如果如果数据量十分庞大，那么需要回表的次数也将十分庞大，性能也就会变得十分糟糕，此时filesort的效率反而会比使用索引扫描排序效率高，所以执行优化将不会选择使用索引，那么此时为了提高查询效率我们可以适当的调大sort_buffer_size和

SQL优化

1.优化数据访问

首先确认查询是否返回了不必要的行，可以通过limit来限制返回的数据行数，尽量避免使用select * 只返回必要的列。

因为在执行select * 是要先通过查询数据字典将*替换成具体的列，这会大大的耗费资源和时间

2.查询拆分

查询拆分，将大查询拆分成小查询，小查询只返回部分数据。

3.分表联查

针对连表查询的sql语句，可以拆分成多个单表查询，然后在业务层；来聚合数据。或者对数据库表进行反范式设计，增加冗余字段。

如果sql和索引都没有问题查询还是很慢怎么办？

1.增加缓存

针对频繁读取的热点数据,将其放到redis缓存中，避免每次查询都要访问数据库

2.主从复制

在读多写少的场景，可以设计mysql主从复制，来分担访问压力

3.分库

在写多读少的场景下，单库的性能无法扛住高并发的流量，就需要分库，把请求分发到多个实例中

其他优化

1.分页查询的优化

在分页查询的过程中如果数据偏移量比较大的话其实，查询的效率是比较低的例如下面的查询语句：

SELECT * FROM `user` LIMIT 1000000,20;

因为该语句会遍历前1000000条数据然后丢弃返回后面20条数据也就是说此次查询扫描了至少1000020条数据，这样的查询效率是非常底下的。那么在不同的场景下也有不同的优化方案。如过需求明确要求必须分页，并且明确需要页码此时我们可以如下改写sql

SELECT * FROM `user` where id>1000000 LIMIT 20;

但是这样的前提条件就是索引必须是自增的。如果业务需求没有明确要求说需要页码，那么我们可以如下改写sql

SELECT * FROM `user` where id>${上一批数据的最大id} LIMIT 20;

2.count(*),count(1),count(字段)选择

在MyISAM存储引擎中count(*)和count（1）的效率是相同的都是O(1)的时间复杂度，这是因为每张MyISAM表都有一个meta信息保存了row_count的值，这个值是通过表级锁来保证它的并发安全的。

而在innodb存储引擎中count()和count(1)以及count(字段)的时间复杂度是O(n),但是在性能上count（）=count(1)>count(字段)这是因为count(*)和count(1)会自动找寻占用空间最小的索引树扫描，而count(字段)如果字段上有建立字段就会扫描该字段的索引树，而如果该字段没有建立索引就会选择聚簇索引树扫描。

如果在多查询结果精度不高的请况用下面语句查询效率会更高

explain select * from `user`;

这里执行优化器有估计表数据的行数效率还是非常高的。

LIMIT 1对优化的影响

针对的是会扫描全表的 SQL语句，如果你可以确定结果集只有一条，那么加上 LIMIT1的时候，当找到一条结

果的时候就不会继续扫描了，这样会加快查询速度。

如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上LIMIT

4.选择合适的驱动表

驱动表与被驱动表的定义

  • 驱动表：数据库在连接操作中首先查询的表。驱动表的记录会作为基准，逐条与被驱动表进行匹配。
  • 被驱动表：在连接操作中用来与驱动表进行匹配的表。被驱动表会根据驱动表的每条记录去查找匹配的记录。

而mysql中连接查询分为外连接和内连接。

内连接（INNER JOIN）

内连接只返回俩个表所匹配的行，也就是说内连接只返回两个表说共有的数据

外连接（OUTER JOIN)

而无论是外连接还是内连接他的查询过程都是从驱动表中获取一条数据，然后去被驱动表中查找匹配的行，然后循环该过程直到驱动表中的数据都已经在被驱动表中查找过了。那么次时就会存在一个问题一但驱动表和被驱动表选择不当那么就会导致查询变得很慢。因为因为被驱动走的是索引那么它单条数据的查询速度大表小表都差不多，假设被驱动表单条数据的查询时间是t,那么整个连表查询的时间和t的数量成正比，连表是从驱动表中拿一条数据去被驱动表匹配那么整个查询的时间是驱动表记录数*t,而t其实在走索引的情况下都是差不多那么整个查询时间和驱动表记录数成正比。

所以连表查询中一般都是用小表驱动大表，对于左外连接select * from A left join B on B.id = A.id,因为左外连接要保留A表所有的数据所以mysql就会让A表作为驱动表，对于右外连接select * from B right join A on A .id = A.id要保留B表所有的数据所以mysql就会让B表作为驱动表。而内连接则是根据查询成本选择驱动表和被驱动表。所以在没有特殊需求的情况下内连接是一种比较好的选择，

如果连接查询很慢的话，1.可能是由于大表驱动小表导致的，2.可能是没有添加索引，3.可能是由于连表查询条件比较复杂并且表数据量大。而优化方案可以由大表驱动小表，添加索引，或者拆分查询，在业务逻辑中进行聚合，又或者通过添加冗余字段避免连表查询。

好的本期内容就讲到这里，如果读者们在阅读中有什么不同地意见欢迎到评论区进行指正哈~

avatar
文章
阅读
粉丝