企业官网建设流程全解析

广州白云区网站建设公司,网站设计主题有哪些,郑州企业的网站建设,网站的建设与维护怎么弄一、ICP是什么#xff1f; 索引条件下推是一种MySQL优化技术#xff0c;它允许在存储引擎层#xff08;如InnoDB#xff09;对索引条件进行过滤#xff0c;而不是将所有行都传递给Server层再进行过滤。 核心思想 将WHERE子句中可以用索引进行过滤的条件#xff0c;下推…一、ICP是什么索引条件下推是一种MySQL优化技术它允许在存储引擎层如InnoDB对索引条件进行过滤而不是将所有行都传递给Server层再进行过滤。核心思想将WHERE子句中可以用索引进行过滤的条件下推到存储引擎中执行减少需要回表的记录数。二、ICP的工作原理对比1. 没有ICP的情况MySQL 5.6之前sql复制下载-- 示例查询有一个复合索引 (zipcode, lastname, firstname) SELECT * FROM people WHERE zipcode 95054 AND lastname LIKE %etrunia% AND address LIKE %Main Street%;执行流程text复制下载1. 存储引擎使用索引找到所有 zipcode95054 的行 2. 存储引擎将满足zipcode条件的所有行包括主键返回给Server层 3. Server层对每一行检查 lastname LIKE %etrunia% 条件 4. Server层对满足条件的行检查 address LIKE %Main Street% 5. Server层返回最终结果问题即使lastname是索引的一部分但因为LIKE %etrunia%通配符开头不能使用索引范围扫描所以所有zipcode95054的行都会被返回给Server层。2. 启用ICP的情况MySQL 5.6sql复制下载-- 同样的查询 SELECT * FROM people WHERE zipcode 95054 AND lastname LIKE %etrunia% AND address LIKE %Main Street%;执行流程text复制下载1. 存储引擎使用索引找到所有 zipcode95054 的行 2. 存储引擎在引擎层检查 lastname LIKE %etrunia% 条件 3. 存储引擎只将满足zipcode和lastname条件的行返回给Server层 4. Server层检查 address LIKE %Main Street% 条件 5. Server层返回最终结果优势减少了存储引擎和Server层之间的数据传输量。篇幅限制下面就只能给大家展示小册部分内容了。整理了一份核心面试笔记包括了Java面试、Spring、JVM、MyBatis、Redis、MySQL、并发编程、微服务、Linux、Springboot、SpringCloud、MQ、Kafc需要全套面试笔记及答案【点击此处即可/免费获取】​​​三、ICP的工作机制详解1. 适用条件sql复制下载-- 示例表结构 CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), age INT, department_id INT, salary DECIMAL(10,2), hire_date DATE, INDEX idx_dept_age (department_id, age), INDEX idx_hire_salary (hire_date, salary) );✅ ICP适用的场景sql复制下载-- 场景1复合索引非首列使用范围查询 SELECT * FROM employees WHERE department_id 10 AND age 25; -- age是索引的第二列 -- 场景2复合索引对非首列使用非等值条件 SELECT * FROM employees WHERE department_id 10 AND age BETWEEN 25 AND 30; -- 场景3索引列使用LIKE通配符不在开头 SELECT * FROM employees WHERE department_id 10 AND name LIKE John%; -- 假设name是索引的一部分 -- 场景4多条件混合部分条件可下推 SELECT * FROM employees WHERE hire_date 2020-01-01 AND salary 50000 AND department_id 10;❌ ICP不适用的场景sql复制下载-- 场景1不使用索引的查询 SELECT * FROM employees WHERE salary 50000; -- salary没有索引 -- 场景2索引失效的情况 SELECT * FROM employees WHERE name LIKE %John%; -- 通配符开头 -- 场景3只访问索引就能满足查询覆盖索引 SELECT department_id, age FROM employees WHERE department_id 10 AND age 25; -- 场景4子查询、JOIN中的某些复杂情况2. ICP的具体执行步骤sql复制下载-- 创建测试表 CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE, status ENUM(pending, shipped, delivered), amount DECIMAL(10,2), INDEX idx_customer_status_date (customer_id, status, order_date) ); -- 插入测试数据 INSERT INTO orders VALUES (1, 1001, 2023-01-10, delivered, 150.00), (2, 1001, 2023-01-15, shipped, 200.00), (3, 1001, 2023-01-20, pending, 100.00), (4, 1002, 2023-01-05, delivered, 300.00), (5, 1002, 2023-01-12, shipped, 250.00); -- 使用ICP的查询 EXPLAIN SELECT * FROM orders WHERE customer_id 1001 AND status shipped AND order_date 2023-01-01;执行过程分解text复制下载步骤1索引扫描 - 使用idx_customer_status_date索引 - 定位到customer_id1001的第一条记录 步骤2ICP过滤在存储引擎层 - 检查当前索引条目customer_id1001 ✓ - 检查statusshipped索引第二列✓ - 检查order_date2023-01-01索引第三列✓ - 如果以上都满足获取主键order_id 步骤3回表 - 使用主键获取完整行数据 步骤4Server层过滤 - 检查其他非索引条件如果有四、代码演示ICP效果验证1. 创建测试环境sql复制下载-- 创建大表进行测试 CREATE TABLE icp_test ( id INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT, c VARCHAR(100), d VARCHAR(100), INDEX idx_ab (a, b) ); -- 插入100万条测试数据 DELIMITER $$ CREATE PROCEDURE populate_icp_test() BEGIN DECLARE i INT DEFAULT 0; WHILE i 1000000 DO INSERT INTO icp_test (a, b, c, d) VALUES ( FLOOR(RAND() * 100), -- a: 0-99 FLOOR(RAND() * 1000), -- b: 0-999 CONCAT(data_, i), CONCAT(extra_, i) ); SET i i 1; END WHILE; END$$ DELIMITER ; CALL populate_icp_test();2. 验证ICP效果sql复制下载-- 关闭ICP仅用于对比 SET optimizer_switch index_condition_pushdownoff; -- 查询1不使用ICP EXPLAIN ANALYZE SELECT * FROM icp_test WHERE a 10 AND b BETWEEN 100 AND 200; -- 开启ICP SET optimizer_switch index_condition_pushdownon; -- 查询2使用ICP EXPLAIN ANALYZE SELECT * FROM icp_test WHERE a 10 AND b BETWEEN 100 AND 200;3. 分析执行计划sql复制下载-- 查看优化器开关状态 SELECT optimizer_switch LIKE %index_condition_pushdown%; -- 详细的执行计划对比 EXPLAIN FORMATJSON SELECT * FROM icp_test WHERE a 10 AND b BETWEEN 100 AND 200;执行计划关键字段解读Using index condition: 表示使用了ICProws: 预估需要检查的行数filtered: 存储引擎过滤后剩余的比例五、ICP的优化器决策1. MySQL如何决定是否使用ICPsql复制下载-- 查看查询优化过程 SET optimizer_traceenabledon; SELECT * FROM icp_test WHERE a 10 AND b 500; SELECT * FROM information_schema.optimizer_trace; SET optimizer_traceenabledoff;2. 成本计算模型MySQL优化器会评估使用ICP的成本索引过滤 回表成本不使用ICP的成本全部回表 Server层过滤成本3. 强制/禁用ICP的Hintsql复制下载-- 使用优化器提示强制ICP SELECT /* INDEX_CONDITION_PUSHDOWN(t1) */ * FROM icp_test t1 WHERE a 10 AND b 500; -- 禁用ICP SELECT /* NO_INDEX_CONDITION_PUSHDOWN(t1) */ * FROM icp_test t1 WHERE a 10 AND b 500;六、实际案例分析案例1电商订单查询优化sql复制下载-- 原始表结构 CREATE TABLE orders_before ( order_id BIGINT PRIMARY KEY, user_id INT, create_time DATETIME, status TINYINT, amount DECIMAL(10,2), INDEX idx_user_create (user_id, create_time) ); -- 查询查找用户最近7天待发货的订单 -- 没有ICP时的问题所有最近7天的订单都会回表 SELECT * FROM orders_before WHERE user_id 12345 AND create_time DATE_SUB(NOW(), INTERVAL 7 DAY) AND status 2; -- 2表示待发货 -- 优化方案添加status到索引中 CREATE TABLE orders_after ( order_id BIGINT PRIMARY KEY, user_id INT, create_time DATETIME, status TINYINT, amount DECIMAL(10,2), INDEX idx_user_status_create (user_id, status, create_time) ); -- 现在status可以参与ICP过滤 SELECT * FROM orders_after WHERE user_id 12345 AND status 2 AND create_time DATE_SUB(NOW(), INTERVAL 7 DAY);案例2时间范围查询优化sql复制下载-- 日志表 CREATE TABLE access_log ( id BIGINT PRIMARY KEY, user_id INT, access_time DATETIME, endpoint VARCHAR(200), response_time INT, INDEX idx_time_user (access_time, user_id) ); -- 查询某用户特定时间段内的慢请求 SELECT * FROM access_log WHERE access_time BETWEEN 2023-01-01 00:00:00 AND 2023-01-31 23:59:59 AND user_id 1001 AND response_time 1000; -- 慢请求阈值 -- ICP作用在存储引擎层先过滤user_id1001七、监控和诊断ICP1. 查看ICP使用情况sql复制下载-- 通过performance_schema监控 SELECT * FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST_TEXT LIKE %SELECT%icp_test% ORDER BY SUM_TIMER_WAIT DESC; -- 通过状态变量监控 SHOW STATUS LIKE %handler%; -- 关注 -- Handler_read_next: 顺序读取索引的次数 -- Handler_read_rnd_next: 随机读取数据的次数2. ICP相关的系统变量sql复制下载-- 查看ICP相关设置 SHOW VARIABLES LIKE optimizer_switch; -- 输出中包含index_condition_pushdownon -- 临时关闭ICP不推荐生产使用 SET SESSION optimizer_switchindex_condition_pushdownoff;八、最佳实践和注意事项1. 设计索引时考虑ICPsql复制下载-- 好的索引设计考虑ICP的使用 -- 将等值条件列放在前面范围条件列放在后面 CREATE INDEX idx_good ON table (equality_col1, equality_col2, range_col); -- 反例范围条件放在前面会限制ICP效果 CREATE INDEX idx_bad ON table (range_col, equality_col);篇幅限制下面就只能给大家展示小册部分内容了。整理了一份核心面试笔记包括了Java面试、Spring、JVM、MyBatis、Redis、MySQL、并发编程、微服务、Linux、Springboot、SpringCloud、MQ、Kafc需要全套面试笔记及答案【点击此处即可/免费获取】2. 编写查询时优化sql复制下载-- 优化前范围条件在前 SELECT * FROM table WHERE date_col 2023-01-01 -- 范围条件 AND user_id 1001; -- 等值条件 -- 优化后调整条件顺序对ICP更友好 SELECT * FROM table WHERE user_id 1001 -- 等值条件 AND date_col 2023-01-01; -- 范围条件 -- 注意条件顺序不影响ICP决策但好的索引设计应该匹配查询模式3. 注意事项版本要求MySQL 5.6 支持MariaDB 5.3 支持存储引擎主要针对InnoDBMyISAM也支持限制不适用于虚拟生成列子查询中的外部表条件不能下推对分区表的支持有限制九、性能对比测试测试脚本sql复制下载-- 创建对比测试 CREATE TABLE icp_perf_test ( id INT PRIMARY KEY, col1 INT, col2 INT, col3 VARCHAR(100), col4 TEXT, INDEX idx_col1_col2 (col1, col2) ); -- 批量插入数据 INSERT INTO icp_perf_test SELECT n, FLOOR(RAND() * 1000), FLOOR(RAND() * 10000), CONCAT(data_, n), REPEAT(x, 500) FROM ( SELECT ROW_NUMBER() OVER () as n FROM information_schema.columns a CROSS JOIN information_schema.columns b LIMIT 1000000 ) t; -- 性能测试查询 SET profiling 1; -- 测试1ICP开启 SET optimizer_switch index_condition_pushdownon; SELECT * FROM icp_perf_test WHERE col1 500 AND col2 8000 LIMIT 1000; -- 测试2ICP关闭 SET optimizer_switch index_condition_pushdownoff; SELECT * FROM icp_perf_test WHERE col1 500 AND col2 8000 LIMIT 1000; -- 查看性能对比 SHOW PROFILES;十、总结ICP的核心价值减少IO操作在存储引擎层过滤掉不符合条件的行减少回表次数降低CPU开销减少Server层需要处理的数据量提升缓存效率更少的数据传输意味着更好的缓存利用率使用建议识别ICP机会复合索引 非首列范围查询监控ICP效果通过执行计划和性能监控验证合理设计索引考虑查询模式将高频等值条件放在索引前列保持版本更新新版本的MySQL对ICP有更多优化通过合理利用ICP可以在不修改应用代码的情况下显著提升包含复合索引范围查询的性能特别是在大数据量的场景下效果更为明显。