Sql优化

作者：付祥 现居珠海，次要负责 Oracle、MySQL、mongoDB 和 Redis 保护工作。 本文起源：原创投稿 *爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。 1、问题景象开发反馈同一条SQL在qa环境执行须要0.1s,而在dev环境须要0.3~0.5s，SQL如下： SELECT machine.id, machine.asset_number, machine.sn, machine.state, machine.idc_id, machine.cabinet_id, machine.cabinet_order, machine.unit_size, machine.brand_model, machine.buy_time, machine.expiration_time, machine.warranty, machine.renewstart_time, machine.renewend_time, machine.warranty_company_id, machine.renewal_type, machine.check_hardware, machine.machine_purchase_price, machine.tags, machine.memo, machine.cpu_core_count, machine.cpu_model, machine.cpu_count, machine.memory_count, machine.memory_size, machine.wire_standard, machine.disk_num, machine.netcard_total_count, machine.netcard_1g, machine.netcard_10g, machine.os_version, machine.kernel_version, machine.raid, machine.power, machine.firmware, machine.manage_card_ip, machine.hostname, machine.private_mac, machine.public_mac, machine.private_ip, machine.public_ip, machine.other_ips, machine.create_time, machine.update_time, machine.creator, machine.updater, machine.delete_flag, machine.disk_desc_id, res.id res_id, res.owner_company_code, res.owner_company_name, res.project_id, res.project_group_id, res.sub_project_id, res.finance_product_id, res.finance_product_name, res.sub_project_name, res.admin_id, res.admin_name, res.owner_id, res.owner_name, 2 AS resource_type, res.resource_id, res.machine_usage_types, res.machine_usage_names, cdl1.display AS check_hardware_name, cdl2.display AS state_name, cdl3.display AS brand_model_name, cdl4.display AS renewal_type_name, cdl5.display AS power_name, cdl6.display AS unit_size_name, cec.company_name AS warranty_company_name, cc.serial_number AS cabinet_name, ci.name AS idc_name, dd.disk_desc AS disk_desc_name, machine.virtual_ip, machine.qingteng_binded, machine.qingteng_id, machine.remark FROM CMDB_PHYSICAL_MACHINE machine LEFT JOIN cmdb_dropdown_list cdl1 ON (machine.check_hardware=cdl1.code and cdl1.type="HardwareCheck") LEFT JOIN cmdb_dropdown_list cdl2 ON (machine.state=cdl2.code and cdl2.type="DeviceStatus") LEFT JOIN cmdb_dropdown_list cdl3 ON (machine.brand_model=cdl3.code and cdl3.type="BrandModels") LEFT JOIN cmdb_dropdown_list cdl4 ON (machine.renewal_type=cdl4.code and cdl4.type="RenewalType") LEFT JOIN cmdb_dropdown_list cdl5 ON (machine.power=cdl5.code and cdl5.type="PowerInfo") LEFT JOIN cmdb_dropdown_list cdl6 ON (machine.unit_size=cdl6.code and cdl6.type="UnitSize") LEFT JOIN cmdb_external_company cec ON (machine.warranty_company_id=cec.id) LEFT JOIN cmdb_cabinet cc ON (machine.cabinet_id=cc.id) LEFT JOIN cmdb_disk_desc dd ON (machine.disk_desc_id=dd.id) inner JOIN cmdb_idc ci ON (machine.idc_id=ci.id and ci.delete_flag=0) left join cmdb_resource_group res on (machine.id = res.resource_id and res.resource_type = 2) where 1=1 AND machine.delete_flag=0 order by id desc LIMIT 0,302、剖析查看SQL执行打算，发现2个环境执行打算不一样，导致执行效率不同。 ...

上篇《MySQL 工夫类分区具体实现》介绍了工夫类分区的实现办法，本篇是对上篇的一个延长，介绍基于此类分区的相干 SQL 编写注意事项。对于分区表的检索无非有两种，一种是带分区键，另一种则不带分区键。一般来讲检索条件带分区键则执行速度快，不带分区键则执行速度变慢。这种论断适应于大多数场景，但不能以偏概全，要针对不同的分区表定义来写最合适的 SQL 语句。用分区表的目标是为了缩小 SQL 语句检索时的记录数，如果没有达到预期成果，则分区表只能带来副作用。 接下来我列举几个经典的 SQL 语句： 仔细的读者在浏览完上篇可能心中就有一些疑难，基于表 ytt_p1 的 SQL 语句如下： select count(*) from ytt_pt1 where log_date >='2018-01-01' and log_date <'2019-01-01';同样是分区表 ytt_pt1_month1 ，基于这张表的 SQL 语句如下： select count(*) from ytt_pt1_month1 where log_date in ('2020-01-01','2020-01-02','2020-01-03','2020-01-04','2020-01-05','2020-01-06','2020-01-07','2020-01-08','2020-01-09','2020-01-10','2020-01-11','2020-01-12','2020-01-13','2020-01-14','2020-01-15');两张表的检索需要相似，为何写法差别不小？ 后者为何要写成列表模式而不持续写成简略的范畴检索模式？带着这点疑难，咱们持续。 MySQL 针对分区表有一项优化技术叫 partition pruning ，翻译过去就是分区裁剪。其大抵含意是 MySQL 会依据 SQL 语句的过滤条件对应的分区函数进行计算，并把计算结果穿透到底层分区表从而减小扫描记录数的一种优化策略。对于工夫类型（DATE,TIMESTAMP,TIME，DATETIME），MySQL 仅反对局部函数的分区裁剪：to_days，to_seconds，year，unix_timestamp。那么咱们再来看之前的疑难：表 ytt_pt1_month1 分区函数为 month ，MySQL 分区表尽管反对 month 函数，然而分区裁剪技术却不蕴含这个函数。 接下来，分两局部来介绍本篇内容。 第一、来体验下 MySQL 的分区裁剪技术，新建一张表 pt_pruning：分区函数为 to_days 。create table pt_pruning (id int,r1 int,r2 int,log_date date)partition by range(to_days(log_date))(PARTITION p_01 VALUES LESS THAN (to_days('2020-02-01')) ENGINE = InnoDB, PARTITION p_02 VALUES LESS THAN (to_days('2020-03-01')) ENGINE = InnoDB, PARTITION p_03 VALUES LESS THAN (to_days('2020-04-01')) ENGINE = InnoDB, PARTITION p_04 VALUES LESS THAN (to_days('2020-05-01')) ENGINE = InnoDB, PARTITION p_05 VALUES LESS THAN (to_days('2020-06-01')) ENGINE = InnoDB, PARTITION p_06 VALUES LESS THAN (to_days('2020-07-01')) ENGINE = InnoDB, PARTITION p_07 VALUES LESS THAN (to_days('2020-08-01')) ENGINE = InnoDB, PARTITION p_08 VALUES LESS THAN (to_days('2020-09-01')) ENGINE = InnoDB, PARTITION p_09 VALUES LESS THAN (to_days('2020-10-01')) ENGINE = InnoDB, PARTITION p_10 VALUES LESS THAN (to_days('2020-11-01')) ENGINE = InnoDB, PARTITION p_11 VALUES LESS THAN (to_days('2020-12-01')) ENGINE = InnoDB, PARTITION p_12 VALUES LESS THAN (to_days('2021-01-01')) ENGINE = InnoDB, PARTITION p_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB)此表蕴含2020年一整年的数据，大略100W条，此处省略造数据过程。(localhost:ytt)<mysql>select min(log_date),max(log_date),count(*) from pt_pruning;+---------------+---------------+----------+| min(log_date) | max(log_date) | count(*) |+---------------+---------------+----------+| 2020-01-02 | 2020-12-31 | 1000000 |+---------------+---------------+----------+1 row in set (0.72 sec)别离执行上面几条 SQL :SQL 1：求日期蕴含 '2020-01-02' 的记录条数。 ...

作者：王春涛 目前是多点Dmall数据库架构师，更早是聚美数据库团队负责人，善于高并发下数据库架构，运维保障，数据库平台建设。 本文起源：原创投稿 *爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。 慢查问监控是MySQL运维中十分重要的一项，它能够帮忙分析线上数据库性能的抖动或者业务查问响应慢等状况。当集群和实例十分多的状况下，慢查问的收集和存储会变得比拟艰难，而且不太好做到实时的慢查问告警。 罕用计划介绍1、慢日志收集通常状况下会采纳通过定时工作的形式应用 pt-query-digest 将每个实例的慢日志收集写入到 MySQL 数据库。因为是定时工作触发，所以并不是实时的进行收集上报。 2、慢日志统计通过查问 MySQL 数据库能够依据 host 、port 、user 、指纹、工夫范畴等条件进行查问统计 3、慢日志告警从 MySQL 中查问出慢日志而后匹配到对应的 DBA 和研发人员发送告警。但因为 MySQL 中数据是全量存在的只能依据工夫范畴进行批次查问，告警就无奈做到实时。 pt-query-digest 的办法在采集的时候就曾经不是实时了，再加上告警工作是按工夫范畴进行批次查问所以这套架构下的慢查问监控不能做到实时的监控 上面给大家介绍一下多点数据库实时慢查问监控的实现思路。多点实时慢查问监控整体架构 如上图，咱们有一个监听 slowlog 的 agent ，这个 agent 次要是继续的对慢查问 log 文件进行 tail ，将每一个 slowlog 段作为一个 list 的 iterm push 到 redis 。每个 agent 能够监听所在机器的所有 MySQL 实例的慢日志，这样就把扩散在各个机器上的日志会集到了一个 redis 中。而后有一个生产端也就是 slowlog 推送服务，从 redis 中 pop 出组装好的慢日志，依据 pop 出的慢日志解析其中的 host ，port ，dbname 以及 user ，匹配到对应的 dba 或研发，将慢日志实时推送给对应的人员。同时依照 host-port 将慢查问存储为文件。这样就造成了一个流式的解决，再加上 redis 的全内存操作，速度极快，齐全能够做到实时。 ...

上篇文章曾经具体介绍 MySQL 组合索引的概念以及其实用场景，这篇次要介绍 MySQL 组合索引的不实用场景以及革新计划。 回顾下组合索引的语法和必备条件【组合索引的语法】（只探讨默认升序排列） alter table t1 add idx_multi(f1, f2, f3);【必备条件】列 f1 必须存在于 SQL 语句过滤条件中！ 也就是说组合索引的第一个列（最左列）在过滤条件中必须存在，而且最好是等值过滤。看看上面这些 SQL，没有一款适宜组合索引。 # SQL 1select * from t1 where f2 = 1;# SQL 2select * from t1 where f3 = 1;# SQL 3select * from t1 where f2 = 1 and f3 = 1;# SQL 4select * from t1 where f2 = 1 or f3 = 1;# SQL 5select * from t1 where f1 = 1 or f2 = 1;# SQL 6select * from t1 where f1 = 1 or f3 = 1;# SQL 7select * from t1 where f1 = 1 or f2 = 1 or f3 = 1;那接下来对下面的 SQL 一个一个剖析： ...

作者：胡呈清爱可生 DBA 团队成员，善于故障剖析、性能优化，集体博客：https://www.jianshu.com/u/a95...，欢送探讨。 本文起源：原创投稿 *爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。 explain format=json 能够打印具体的执行计划成本，上面两个示例将通知你如何查看老本输入，以及如何计算成本。 表构造如下： mysql> show create table sbtest1\G*************************** 1. row *************************** Table: sbtest1Create Table: CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '', `pad` varchar(90) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4316190 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin##留神sbtest3无主键mysql> show create table sbtest3\G*************************** 1. row *************************** Table: sbtest3Create Table: CREATE TABLE `sbtest3` ( `id` int(11) NOT NULL, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '', `pad` varchar(66) COLLATE utf8mb4_bin DEFAULT NULL, KEY `k_3` (`k`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin示例 1mysql> explain format=json select * from sbtest3 where id<100 and k<200\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "26.21" ##查问总成本 }, "table": { "table_name": "sbtest3", ##表名 "access_type": "range", ##拜访数据的形式是range，即索引范畴查找 "possible_keys": [ "k_3" ], "key": "k_3", ##应用索引 "used_key_parts": [ "k" ], "key_length": "4", "rows_examined_per_scan": 18, ##扫描 k_3 索引的行数：18（满足特定条件时应用index dive可失去真履行数） "rows_produced_per_join": 5, ##在扫描索引后估算满足id<100条件的行数：5 "filtered": "33.33", ##在扫描索引后估算满足其余条件id<100的数据行占比 "index_condition": "(`sbtest`.`sbtest3`.`k` < 200)", ##索引条件 "cost_info": { "read_cost": "25.01", ##这里蕴含了所有的IO老本+局部CPU老本 "eval_cost": "1.20", ##计算扇出的CPU老本 "prefix_cost": "26.21", ##read_cost+eval_cost "data_read_per_join": "4K" }, "used_columns": [ "id", "k", "c", "pad" ], "attached_condition": "(`sbtest`.`sbtest3`.`id` < 100)" } }}eval_cost这个很简略，就是计算扇出的 CPU 老本。利用条件 k<200 时，须要扫描索引 18行，这里 18 是准确值（index dive），而后优化器用了一种叫启发式规定（heuristic）的算法估算出其中满足条件 id<100 的比例为 33.33%，进行 18*33.33% 次计算的 CPU 老本等于 18*33.33%*0.2=1.2，这里 0.2 是老本常数（即 row_evaluate_cost ）。 ...

建设在多个列上的索引即组合索引（联结索引），实用在多个列必须一起应用或者是从左到右方向局部间断列一起应用的业务场景。 组合索引和单值索引相似，索引上的每个键值依照肯定的大小排序。比方针对三个字段的组合索引有以下组合： (f1, f2, f3)(f1, f2, f3 desc)(f1, f2 desc, f3)(f1 desc, f2, f3)(f1 desc, f2 desc, f3 desc)...明天探讨的组合索引只基于默认排序形式，也就是 (f1,f2,f3)，等价于 (f1 asc, f2 asc, f3 asc)。 组合索引的语法： alter table t1 add key idx_multi(f1 [asc/desc],f2 [asc/desc],f3 [asc/desc]) [using btree/using hash]MySQL 里，组合索引最大反对 16 个列。能够基于 B+ 树，也能够基于哈希，这篇次要探讨基于 B 树，并且索引程序默认升序，基于 HASH 只有一种用法，就是所有列的都必须等值过滤【仅限于上面 SQL 3】。 应用组合索引的必备条件为：列 f1 必须存在于 SQL 语句过滤条件中！也就是说组合索引的第一个列（最左列）在过滤条件中必须存在，而且最好是等值过滤。 思考以下 15 条 SQL 语句， 别离对表 t1 字段 f1、f2、f3 有不同的组合过滤，并且都蕴含了列 f1，也就是说满足了组合索引应用的必备条件。 # SQL 1select * from t1 where f1 = 1;# SQL 2select * from t1 where f1 = 1 and f2 = 1;# SQL 3select * from t1 where f1 = 1 and f2 = 1 and f3 = 1 ;# SQL 4select f1,f2 from t1 where 1 order by f1,f2;# SQL 5select f1,f2,f3 from t1 where 1 order by f1,f2,f3;# SQL 6select f1,f2,count(*) from t1 group by f1,f2;# SQL 7select f1,f2,f3,count(*) from t1 group by f1,f2,f3;# SQL 8select * from t1 where f1 = 10 and f2 = 5 and f3 > 10# SQL 9select * from t1 where f1 = 10 and f2 > 5;# SQL 10select * from t1 where f1 < 10;# SQL 11select * from t1 where f1 < 10 and f2 > 5;# SQL 12select * from t1 where f1 < 10 and f2 > 5 and f3 < 10;# SQL 13select * from t1 where f1 < 10 and f2 = 5 and f3 < 10;# SQL 14select * from t1 where f1 < 10 and f2 = 5 and f3 = 10;# SQL 15select * from t1 where f1 = 1 and f3 = 1;SQL 1、SQL 2、 SQL 3 三条 SQL 别离基于组合索引 idx_multi 过滤后回表；其中 SQL 3 是组合索引中每个字段都能过滤到的最完满查问。来看看 SQL 3的执行打算： ...

前言：最近在测试环境中点击一个图表展现页面时，半天才失去后盾响应的数据进行页面渲染展现，后盾的响应很慢，这样极大的升高了用户的体验；发现这个问题后马上进行了排查 ，通过排查发现是由一个查问很慢的 group by 语句导致的； 本文主线： ①、简略形容下排查步骤； ②、对 group by 查问慢进行优化； 简略形容下排查步骤：排查次要分为了两个步骤： 后盾接口的监控，看看哪个办法调用时耗时多数据库开启慢查问日志，记录执行很慢的SQL举荐应用阿里开源的Java线上诊断工具 Arthas ，应用其 trace 命令统计办法调用链路上各个办法节点的耗时； Arthas 工具的具体应用办法可参考： 线上服务响应工夫太长的排查心路 ； 通过应用Arthas工具统计到一个进行数据库的 group by查问 办法耗时很重大； 为了进一步确定是这个查问SQL 很耗时，将MySql 的慢查问日志开启了，而后再次调用后盾这个接口，发现慢查问日志中的确存在了这个SQL语句； SQL语句如下： SELECT date_format(createts, '%Y') AS YEARFROM t_test_logGROUP BY date_format(createts, '%Y')ORDER BY createts DESC这个SQL语句是用来统计表中所有数据被创立时的年份；上面就来聊聊这个SQL为什么会比较慢，而后进行了怎么的优化； 对 group by 查问慢进行优化：在优化group by查问的时候，个别会想到上面这两个名词，通过上面这两种索引扫描能够高效疾速的实现group by操作： 涣散索引扫描（Loose Index Scan）紧凑索引扫描（Tight Index Scan）group by操作在没有适合的索引可用时，通常先扫描整个表提取数据并创立一个长期表，而后依照group by指定的列进行排序；在这个长期表外面，对于每一个group 分组的数据行来说是间断在一起的。 实现排序之后，就能够失去所有的groups 分组，并能够执行汇集函数（aggregate function）。 能够看到，在没有应用索引的时候，须要创立长期表和排序；那在执行打算的 Extra 额定信息中通常就会看到这些信息 Using temporary; Using filesort 呈现 。 ...

小Hub领读：当页数比拟大的时候，查问效率直线降落，有什么方法能够优化吗？看完这篇文章！ 作者：悠悠i起源：cnblogs.com/youyoui/p/7851007.html 筹备工作个别分页查问应用子查问优化应用 id 限定优化应用长期表优化对于数据表的 id 阐明*当须要从数据库查问的表有上万条记录的时候，一次性查问所有后果会变得很慢，特地是随着数据量的减少特地显著，这时须要应用分页查问。对于数据库分页查问，也有很多种办法和优化的点。上面简略说一下我晓得的一些办法。 筹备工作为了对上面列举的一些优化进行测试，上面针对已有的一张表进行阐明。 表名：order_history形容：某个业务的订单历史表次要字段：unsigned int id，tinyint(4) int type字段状况：该表一共 37 个字段，不蕴含 text 等大型数据，最大为 varchar(500)，id 字段为索引，且为递增。数据量：5709294MySQL 版本：5.7.16 线下找一张百万级的测试表可不容易，如果须要本人测试的话，能够写 shell 脚本什么的插入数据进行测试。以下的 sql 所有语句执行的环境没有产生扭转，上面是根本测试后果：select count(*) from orders_history;返回后果：5709294 三次查问工夫别离为： 8903 ms8323 ms8401 ms个别分页查问个别的分页查问应用简略的 limit 子句就能够实现。limit 子句申明如下： SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offsetLIMIT 子句能够被用于指定 SELECT 语句返回的记录数。需注意以下几点： 第一个参数指定第一个返回记录行的偏移量，留神从0开始第二个参数指定返回记录行的最大数目如果只给定一个参数：它示意返回最大的记录行数目第二个参数为 -1 示意检索从某一个偏移量到记录集的完结所有的记录行初始记录行的偏移量是 0(而不是 1)上面是一个利用实例： select * from orders_history where type=8 limit 1000,10;该条语句将会从表 orders_history 中查问offset: 1000开始之后的 10 条数据，也就是第 1001 条到第 1010 条数据（1001 <= id <= 1010）。 数据表中的记录默认应用主键（个别为 id）排序，下面的后果相当于： select * from orders_history where type=8 order by id limit 10000,10;三次查问工夫别离为： 3040 ms3063 ms3018 ms针对这种查问形式，上面测试查问记录量对工夫的影响： select * from orders_history where type=8 limit 10000,1;select * from orders_history where type=8 limit 10000,10;select * from orders_history where type=8 limit 10000,100;select * from orders_history where type=8 limit 10000,1000;select * from orders_history where type=8 limit 10000,10000;三次查问工夫如下： 查问 1 条记录：3072ms 3092ms 3002ms查问 10 条记录：3081ms 3077ms 3032ms查问 100 条记录：3118ms 3200ms 3128ms查问 1000 条记录：3412ms 3468ms 3394ms查问 10000 条记录：3749ms 3802ms 3696ms另外我还做了十来次查问，从查问工夫来看，根本能够确定，在查问记录量低于 100 时，查问工夫根本没有差距，随着查问记录量越来越大，所破费的工夫也会越来越多。 ...