Mysql索引优化

最新推荐文章于 2024-08-15 19:03:08 发布

胡尚

最新推荐文章于 2024-08-15 19:03:08 发布

阅读量382

点赞数

分类专栏：深入学习总结笔记 Mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_44027353/article/details/129394059

版权

深入学习总结笔记同时被 2 个专栏收录

115 篇文章 8 订阅

订阅专栏

Mysql

25 篇文章 0 订阅

订阅专栏

文章目录

Mysql索引优化

Mysql索引优化

大部分情况下 in or 在数据量小时不走索引，在数据量大时走索引

大部分情况下> 不会走索引而like会走索引

索引下推

Index Condition Pushdown ICP优化只作用于辅助索引

索引下推的原理就是在存储引擎层利用索引筛选过滤出where条件的数据，而不是把数据传输到service层再去进行条件过滤

比如创建了一个辅助索引索引字段为name、age、position。对于下面这条sql

SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager'

这种情况下只使用了name这一个字段，因为根据name字段过滤完后得到个age和position字段是无序的，无法合理的使用索引

在mysql5.6版本之前，这条sql只会通过辅助索引找到**like **开头的索引，然后拿这些索引对应主键逐个回表找到整条数据，在进行age和position字段的的判断

而Mysql5.6版本就有了ICP优化，它会在使用辅助索引时就会对索引包含的所有字段先判断，过滤掉不符合条件的数据之后再进行回表，这样可以减少回表的次数

mysql如何选择合适的索引

-- mysql并没有走索引
-- 如果使用name索引需要遍历name字段的联合索引树，然后在去进行回表操作。成本比全表扫描还高
mysql> EXPLAIN select * from employees where name > 'a';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rDs0HRIB-1678201807659)(picture/性能调优/image-20230225165419619.png)]

-- 当然可以用覆盖索引进行优化，这样直接使用辅助索引就可以拿到结果，就不需要回表操作了
mysql> EXPLAIN select name,age,position from employees where name > 'a' ;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nujx1JTq-1678201807660)(picture/性能调优/image-20230225165755028.png)]

-- 但是同样的sql语句，同样的表，下面这条sql语句却走了索引
mysql> EXPLAIN select * from employees where name > 'zzz' ;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OiQvDET0-1678201807660)(picture/性能调优/image-20230225165854958.png)]

我们可以使用trace工具来查询mysql如何选择合适的索引，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭

mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启trace

-- 两条sql语句一起选中执行
mysql> select * from employees where name > 'a' order by position;
mysql> select * from information_schema.OPTIMIZER_TRACE;

执行结果如下：

{
    "steps": [
        {
            "join_preparation": { // 第一阶段：SQL准备阶段，格式化sql
                "select#": 1,
                "steps": [
                    {              "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from`employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
                    }
                ] 
            } 
        },
        {
            "join_optimization": { // 第二阶段：SQL优化阶段
                "select#": 1,
                "steps": [
                    {
                        "condition_processing": { // 条件处理
                            "condition": "WHERE",
                            "original_condition": "(`employees`.`name` > 'a')",
                            "steps": [
                                {
                                    "transformation": "equality_propagation",
                                    "resulting_condition": "(`employees`.`name` > 'a')"
                                },
                                {
                                    "transformation": "constant_propagation",
                                    "resulting_condition": "(`employees`.`name` > 'a')"
                                },
                                {
                                    "transformation": "trivial_condition_removal",
                                    "resulting_condition": "(`employees`.`name` > 'a')"
                                }
                            ] 
                        } 
                    },
                    {
                        "substitute_generated_columns": {} /* substitute_generated_columns */
                    },
                    {
                        "table_dependencies": [ // 表依赖详情
                            {
                                "table": "`employees`",
                                "row_may_be_null": false,
                                "map_bit": 0,
                                "depends_on_map_bits": [] /* depends_on_map_bits */
                            }
                        ] /* table_dependencies */
                    },
                    {
                        "ref_optimizer_key_uses": [] /* ref_optimizer_key_uses */
                    },
                    {
                        "rows_estimation": [ // 预估表的访问成本
                            {
                                "table": "`employees`",
                                "range_analysis": {
                                    "table_scan": { // 全表扫描情况
                                        "rows": 10123, // 扫描行数
                                        "cost": 2054.7 // 查询成本
                                    } /* table_scan */,
                                    "potential_range_indexes": [ // 查询可能使用的索引
                                        {
                                            "index": "PRIMARY", // 主键索引，因为where条件中没有主键，所以这里usable是false 用不了主键索引
                                            "usable": false,
                                            "cause": "not_applicable"
                                        },
                                        {
                                            "index": "idx_name_age_position", // 辅助索引
                                            "usable": true,
                                            "key_parts": [
                                                "name",
                                                "age",
                                                "position",
                                                "id"
                                            ]
                                        }
                                    ],
                                    "setup_range_conditions": [],
                                    "group_index_range": {
                                        "chosen": false,
                                        "cause": "not_group_by_or_distinct"
                                    },
                                    "analyzing_range_alternatives": { // 分析各个索引使用成本
                                        "range_scan_alternatives": [
                                            {
                                                "index": "idx_name_age_position",
                                                "ranges": [
                                                    "a < name" // 索引使用范围
                                                ] /* ranges */,
                                                "index_dives_for_eq_ranges": true,
                                                "rowid_ordered": false, // 使用该索引获取的记录是否按照主键排序
                                                "using_mrr": false,
                                                "index_only": false, // 是否使用覆盖索引
                                                "rows": 5061, // 索引扫描行数
                                                "cost": 6074.2, // 索引使用成本
                                                "chosen": false, // 是否选择该索引
                                                "cause": "cost"
                                            }
                                        ] ,
                                        "analyzing_roworder_intersect": {
                                            "usable": false,
                                            "cause": "too_few_roworder_scans"
                                        } 
                                    } 
                                } 
                            }
                        ]
                    },
                    {
                        "considered_execution_plans": [
                            {
                                "plan_prefix": [] ,
                                "table": "`employees`",
                                "best_access_path": { // 最优访问路径
                                    "considered_access_paths": [ // 最终选择的访问路径
                                        {
                                            "rows_to_scan": 10123,
                                            "access_type": "scan", // 访问类型：为scan，全表扫描
                                            "resulting_rows": 10123,
                                            "cost": 2052.6,
                                            "chosen": true, // 确定选择
                                            "use_tmp_table": true
                                        }
                                    ] /* considered_access_paths */
                                } /* best_access_path */,
                                "condition_filtering_pct": 100,
                                "rows_for_plan": 10123,
                                "cost_for_plan": 2052.6,
                                "sort_cost": 10123,
                                "new_cost_for_plan": 12176,
                                "chosen": true
                            }
                        ] /* considered_execution_plans */
                    },
                    {
                        "attaching_conditions_to_tables": {
                            "original_condition": "(`employees`.`name` > 'a')",
                            "attached_conditions_computation": [] /* attached_conditions_computation */,
                            "attached_conditions_summary": [
                                {
                                    "table": "`employees`",
                                    "attached": "(`employees`.`name` > 'a')"
                                }
                            ] /* attached_conditions_summary */
                        } /* attaching_conditions_to_tables */
                    },
                    {
                        "clause_processing": {
                            "clause": "ORDER BY",
                            "original_clause": "`employees`.`position`",
                            "items": [
                                {
                                    "item": "`employees`.`position`"
                                }
                            ] /* items */,
                            "resulting_clause_is_simple": true,
                            "resulting_clause": "`employees`.`position`"
                        } /* clause_processing */
                    },
                    {
                        "reconsidering_access_paths_for_index_ordering": {
                            "clause": "ORDER BY",
                            "steps": [] /* steps */,
                            "index_order_summary": {
                                "table": "`employees`",
                                "index_provides_order": false,
                                "order_direction": "undefined",
                                "index": "unknown",
                                "plan_changed": false
                            } /* index_order_summary */
                        } /* reconsidering_access_paths_for_index_ordering */
                    },
                    {
                        "refine_plan": [
                            {
                                "table": "`employees`"
                            }
                        ] /* refine_plan */
                    }
                ] /* steps */
            } /* join_optimization */
        },
        {
            "join_execution": { // 第三阶段：SQL执行阶段
                "select#": 1,
                "steps": [] /* steps */
            } /* join_execution */
        }
    ] /* steps */
}

结论：全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描

mysql> select * from employees where name > 'zzz' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
-- 查看trace字段可知索引扫描的成本低于全表扫描，所以mysql最终选择索引扫描

  
mysql> set session optimizer_trace="enabled=off"; ‐‐关闭trace

Order by与Group by优化

Explain的Extra中有两两种排序Usring index和Usring filesort；Usring index是指扫描索引本身完成排序，效率高；而Usring filesort效率低
where子句和order by子句条件列满足索引的最左前缀列就会使用Usring index
如果order by的条件不在索引列上就会产生Usring filesort
能用覆盖索引就用覆盖索引，尽量在索引列上完成排序，遵循索引创建时的最左前缀法则
Group by和Order by类似，其本质是先排序后分组。对于Group by的优化如果不需要排序就可以加上order by null禁止排序。

注意where高于having，能写在where中的限定条件就不要去having中写了。

Usring filesort文件排序原理

文件排序方式：

单路排序：一次性取出满足满足条件行的所有字段，然后在sort buffer中进行排序返回。这种方式的优点是不需要再进行回表
双路排序：首先根据相应的条件取出相应的排序字段和直接定位行的rowId或主键id，然后在sort buffer中进行排序，排序完成后再回表把其他需要查询的字段取出来。因为sort buffer的内存容量是有限的，如果表的字段太多，当sort buffer用完后就会去创建临时文件来帮助排序。这种方式的优点是减少创建临时文件的个数

mysql是通过max_length_for_sort_data(默认1024字节)，如果表所有字段的总长度小于这个值那么就使用单路排序，如果所有字段总长度大于了这个变量的值则使用双路排序。

sort buffer默认1M内存

创建索引的设计思路

业务SQL在前，索引在后

我们应该等主体业务功能开发完成后把涉及到该表相关sql都要拿出来分析之后再建立索引
联合索引尽可能多的覆盖查询条件

让每个联合索引尽可能的去包含sql语句中的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足最左前缀原则
不要在小基数上建索引

比如性别字段
长字符串尽量采用前缀索引

建议一般使用前20个字符就足够了
使用索引时，where和order by冲突时，优先满足where条件

当辅助索引不能同时满足where和order by使用时，我们往往都是让where条件去使用索引来快速筛选出一部分指定的数据，然后再去排序。因为经过where条件筛选后数据量一遍不会太大了，然后再去做排序成本可能小很多。
基于慢sql查询做优化

核心思想是：尽量利用一两个复杂的多字段联合索引，抗下你系统80%以上的查询，然后再用一两个辅助索引尽量抗下剩下的20%非典型的查询。保证大数据量的表尽可能多的都能充分利用索引

limit分页优化

如下一条分页查询sql语句

select * from employees limit 10000,10;

mysql底层其实是读取了10010条数据，然后再丢弃前10000条数据，如果数据量很大时则分页查询就会变得很慢。

优化方式：

第一种情况：

如果查询的主键是自增且连续的，那么我们可以加上where条件直接获取10条数据，例如可以改写为如下的sql

select * from employees where id>10000 limit 10;

所以这种改写得满足以下两个条件：

主键自增且连续
结果是按照主键排序的

第二种情况

在我们的业务场景中，一般情况下是非主键自增且连续的，对于这种情况可以进行如下的优化

首先利用辅助索引(但需要使用覆盖索引，不然select回大量回表就不会走索引了)，先把id拿出来

再通过结果临时表的id去进行关联查询

select * from employees 
join (select id from employees where limit 10000,10) t1
on employees.id = t1.id

join关联查询优化

Mysql常见表关联有两种算法：

Nested Loop Join算法
Block Nested Loop Join 算法

现在有t1 t2两张数据表，其中t1表有10000条数据 t2表有100条数据 t1表为a字段建了一个辅助索引

一. 嵌套循环连接算法(Nested Loop Join NLJ)

NLJ算法：一次一行的从第一张表**(驱动表)中读取行，在通过关联条件中的关联字段去第二张表(被驱动表)**里取出满足条件的行，然后取出两张表关联的结果集

-- 可以看到先从t2表中取数据，通过关联字段  利用t1表的辅助索引a再去找到t1表中对应的数据
mysql> EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jtNCvZhp-1678201807661)(picture/性能调优/image-20230227201650615.png)]

上面sql大致的执行过程是

先从t2表中取出一行，然后再通过这一行数据关联字段的值，去t1表中找到相关联的数据
返回结果
再去从t2表中取数据循环进行前面几步。

这里t2表进行全表扫描查询了100次，t1表是通过辅助索引一次读取一行，相当于也扫描了100次，加起来共读取了200次磁盘

注意：

inner join 内连接查询，mysql它会自动的统计各个关联表参与关联的数据量，然后自动选择数据量小的表去驱动数据量大的表，也就是小表驱动大表
如果是left join左连接查询则是左边是驱动表，右边是被驱动表；right join右连接查询则右边是驱动表，左边是被驱动表
使用explain执行计划查询关联查询时，如果Extra中未出现Using join buffer则表示是使用的NLJ算法

二、基于块的嵌套循环连接算法 (Block Nested Look Join BNL)

BNL算法：把驱动表中的所有数据读取到join buffer中，然后扫描被驱动表，把被驱动表中的每一行数据与join buffer中的数据做关联条件匹配

如果连接查询的字段没有索引则会使用BNL算法，

mysql>EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QHgkPhso-1678201807661)(picture/性能调优/image-20230227211152626.png)]

上面sql大致的执行流程是：

先把t2表中100行数据全加载到join buffer中
再把t1表中的每一行数据读取出来，跟join buffer中的数据做对比
返回满足的条件

这里t1 t2表都做了一次全表扫描，相当于总共进行了 t1表数据10000次 + t2表数据100次 = 10100次磁盘读取。并且join buffer中的数据是乱序的，相当于进行了10000*100=100万次内存比较操作

如果t2表数据量比较大，join buffer中内存空间不够存放所有数据怎么办？

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。

mysql的解决方法是分段放。首先从t2表中读取一部分数据到join buffer中，与t1表进行数据比较得到部分连接查询结果。然后清空join buffer 再从t2表中取出剩下的数据再和t1表进行匹配，最终得到完整结果。所以这里t1表就多进行了一次全表扫描

被驱动表的关联字段没有索引为什么要选择BNL算法，而不是使用NLJ算法嘞？

按照上面的案例来说，如果使用NLJ算法，则会进行100*10000次磁盘扫描，很显然使用BNL算法磁盘扫描次数要少很多。

因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高

join连接查询优化

关联查询字段加索引
小表驱动大表，我们使用inner join时如果明确知道哪一张表数据量小，则可以使用straight_join写法固定连接驱动方式，省去mysql优化器自己判断谁驱动谁

对于小表的定义：在决定哪个表为驱动表时，是通过各自表where条件过滤之后，参与join的连接字段的总数据量，如果数据量小则就是小表，作为驱动表

in和exists的优化

原则还是小表驱动大表。

-- 这里会先算出B表的数据，然后再去找A表的数据
select * from A where id in (select id from B)

在使用in时，如果表的数据量太小或太大，mysql都不会走索引。一般建议in中的数据量不要超过1000

如果左边表的数据量少，in条件中的数据量要多则可以使用exists

EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别
EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析

count(*)查询优化

‐‐ 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
mysql> set global query_cache_size=0;
mysql> set global query_cache_type=0;

select count(*) from employees;
select count(1) from employees;
select count(字段名) from employees; -- 这种方式它不会统计该字段为null的数据行数
select count(id) from employees;

上面四条sql语句的执行计划是一样的，执行效率其实也是差不多

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uWzb40Du-1678201807661)(picture/性能调优/image-20230227215800607.png)]

如果字段有索引：count(*) ≈ count(1) > count(字段) > count(id) 。辅助索引每页存储的数据比主键索引多，所以这里执行效率是大于走主键索引的

如果字段没有索引：count(*) ≈ count(1) > count(id) > count(字段)

count(1) 与count(字段)执行过程类似，但count(1)不会取出字段进行统计，而是直接使用的常量1做统计；而count(字段)需要取出字段的值。所以理论上count(1)要快

count(*)是例外，不会把全部字段的值取出来，mysql专门做了优化，不取值，按行累加，效率很高。

常见的优化方式：

对应MyISAM存储引擎，它自己维护总行数，直接用count(*)查询就行。InnoDB存储引擎之所以没有存储表的总记录数是因为MVCC机制，查询count需要实时计算。
show table status

这种方式查询的行数不准确，只是一个预估值
将表的中行数维护在Redis中

再进行insert或delete时同步更新redis中的行数，但这种方式会存在一定时间内的缓存与数据库数据不一致问题
新建一张数据表，专门维护各个数据表的总行数

将insert或delete操作与更新计数表的sql 放在同一个本地事务中。这样是能保证不会出现数据不一致的问题。