2024-07-23

mysql-2-sql优化

什么是最左匹配原则？

最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

打个比方，我们有张student 表，我们根据学院编号+班级建立了一个联合索引 index_magor_class(magor，class)，这个索引由二个字段组成。

索引的底层是一颗B+树，那么联合索引的底层也就是一颗B+树，只不过联合索引的B+树节点中存储的是逗号分隔的多个值。

举例：创建一个 index_magor_class(magor，class) 的联合索引

它是先根据magor排序，再根据class排序，如果索引后面还有字段，继续以此类推

我们查询的where 条件如果只传入了班级，是走不到联合索引的，但是如果只传了学院编号，是可能会走到联合索引的。
（为什么说可能，MYSQL的执行计划和查询的实际执行过程并不完全吻合，比如你数据库数据量很少，可能直接全量遍历速度更快，就不走索引了）

如何查看慢SQL？

MySQL为我们提供了 explain 关键字来直观的查看一条SQL的执行计划。

EXPLAIN SELECT
	ti.*
FROM
	t_model tm
	LEFT JOIN t_images ti ON tm.id = ti.model_id 
WHERE
	tm.id = 1;

执行计划

MariaDB [images]> EXPLAIN SELECT
    -> ti.*
    -> FROM
    -> t_model tm
    -> LEFT JOIN t_images ti ON tm.id = ti.model_id
    -> WHERE
    -> tm.id = 1;
+------+-------------+-------+-------+---------------+---------+---------+-------+-------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows  | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+-------+-------+-------------+
|    1 | SIMPLE      | tm    | const | PRIMARY       | PRIMARY | 8       | const |     1 | Using index |
|    1 | SIMPLE      | ti    | ALL   | NULL          | NULL    | NULL    | NULL  | 41673 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+-------+-------+-------------+
2 rows in set (0.00 sec)

（1）id

SELECT识别符，这个不重要。

（2）select_type

SIMPLE: 指示非子查询和union的简单查询。

PRIMARY:最外面的SELECT。

UNION:UNION中的第二个或后面的SELECT语句

（3）table

输出的行所引用的表。

（4）type

联接类型。下面给出各种联接类型，按照从最佳类型到最坏类型进行排序:

system:表仅有一行(=系统表)。这是const联接类型的一个特例。

const:表最多有一个匹配行，它将在查询开始时被读取。
因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const表很快，因为它们只读取一次!

eq_ref:对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。

ref:对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。

ref_or_null:该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。

index_merge:该联接类型表示使用了索引合并优化方法。

unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。

index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)

range:只检索给定范围的行，使用一个索引来选择行。

index:该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。

ALL:对于每个来自于先前的表的行组合，进行完整的表扫描，说明查询就需要优化了。

一般来说，得保证查询至少达到range级别，最好能达到ref。

（5）possible_keys

表示查询时，可能使用的索引，但是不一定使用了这个索引

（6）key

实际上是使用的索引

如果没有选择索引，键是NULL。PRIMARY表示使用了主键索引。

可以看到上面 t_images 这个表没有命中索引。

（7）key_len

显示MySQL决定使用的键长度。如果key是NULL，则长度为NULL。在不损失精确性的情况下，长度越短越好。

（8）ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值，这里是const 表示，左连接是使用了索引。

（9） rows

很重要的一个字段。mysql 查询优化器根据统计信息，估算 sql 要查找到结果集需要扫描读取的数据行数，这个值非常直观的显示 sql 效率好坏，原则上 rows 越少越好。

可以看到上面t_images 这个表扫描了41673行。

（10）extra

explain 中的很多额外的信息会在 extra 字段显示，常见的有以下几种内容:

using filesort ：表示 mysql 需额外的排序操作，不能通过索引顺序达到排序效果。一般有 using filesort都建议优化去掉，因为这样的查询 cpu 资源消耗大。
using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错。
using temporary：查询有使用临时表，一般出现于排序，分组和多表 join 的情况，查询效率不高，建议优化。
using where ：表名使用了where过滤。