泰兴网站制作,网站建设公司的服务器,wordpress 无法播放音乐,蒲县网站建设一. 数据库设计优化
1. 选择合适的字段类型
设计表时#xff0c;尽量选择存储空间小的字段类型#xff1a;
整型字段#xff1a;从TINYINT、SMALLINT、INT到BIGINT。小数类型#xff1a;对于金额等需精确计算的数值使用DECIMAL#xff0c;避免使用FLOAT和DOUBLE。字符串…一. 数据库设计优化
1. 选择合适的字段类型
设计表时尽量选择存储空间小的字段类型
整型字段从TINYINT、SMALLINT、INT到BIGINT。小数类型对于金额等需精确计算的数值使用DECIMAL避免使用FLOAT和DOUBLE。字符串根据实际长度选择CHAR定长或VARCHAR变长。VARCHAR不宜超过5000字符长度需求大的数据建议使用TEXT并将大字段拆分到单独的表中
2. 确定字段的合理长度
字段长度表示字符数或字节数例如VARCHAR(32)适用于用户名字段通常为5到20个字符。建议字段长度设为2的幂如32、64、128等。
3. 控制表的字段数量
一张表的字段数量尽量控制在20个以内以避免数据量过大导致查询效率低。如果字段较多建议分拆为多张表。
4. 尽量定义字段为 NOT NULL
为防止空指针问题并提升查询性能除非有特殊需求字段都应定义为NOT NULL可以通过默认值或常量来填充字段。
5. 使用数值类型代替字符串
数值类型占用存储空间小、比较速度更快。
例子 性别字段建议用数值如0代表女生1代表男生而非字符串如WOMEN、“MAN”。
6. 评估并添加必要的索引
根据表的数据量和查询需求设置索引 索引数量不宜过多单表索引个数不超过5个。 区分度低的字段如性别不适合建立索引。 可通过联合索引优化多列条件查询。 定期分析和优化索引 ANALYZE TABLE user_info_tab;7. 避免使用MySQL保留字
避免库名、表名或字段名使用MySQL的保留字如SELECT、DESC等否则需要使用反引号引用会增加代码复杂性。
8. 统一字符集的选择
字符集推荐使用utf8或utf8mb4以支持中英文及emoji。其他字符集如GBK仅适合中文环境latin1适合仅支持英文的场景。
9. 数据冗余
在某些场景下适当的数据冗余可以提高查询效率例如在读多写少的应用中。
10. 使用分区表
对于大数据量的表使用分区表可以提高查询性能。
示例 范围分区Range Partitioning 根据某个字段的值范围进行分区。适用于时间戳、日期等有序字段。 CREATE TABLE sales (sale_id INT,sale_date DATE,amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (PARTITION p2019 VALUES LESS THAN (2020),PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022)
);列表分区List Partitioning 根据某个字段的特定值列表进行分区。适用于类别、地区等离散字段。 CREATE TABLE employees (emp_id INT,department VARCHAR(20)
) PARTITION BY LIST (department) (PARTITION p_sales VALUES IN (Sales),PARTITION p_marketing VALUES IN (Marketing),PARTITION p_it VALUES IN (IT)
);哈希分区Hash Partitioning 根据某个字段的哈希值进行分区。适用于均匀分布的数据。 CREATE TABLE customers (cust_id INT,name VARCHAR(50)
) PARTITION BY HASH (cust_id) PARTITIONS 4;复合分区Composite Partitioning 结合多种分区策略先按一种策略分区再在每个子分区中按另一种策略分区。适用于复杂的数据分布。 CREATE TABLE sales (sale_id INT,sale_date DATE,region VARCHAR(20)
) PARTITION BY RANGE (YEAR(sale_date))
SUBPARTITION BY LIST (region) (PARTITION p2019 VALUES LESS THAN (2020) (SUBPARTITION p2019_north VALUES IN (North),SUBPARTITION p2019_south VALUES IN (South)),PARTITION p2020 VALUES LESS THAN (2021) (SUBPARTITION p2020_north VALUES IN (North),SUBPARTITION p2020_south VALUES IN (South))
);二. 分析与调优
1. 使用 EXPLAIN 分析查询计划
EXPLAIN 可以帮助你了解查询的执行计划找出潜在的性能瓶颈。
例子
EXPLAIN SELECT * FROM orders WHERE user_id 123;2. 使用 PARTITION PRUNING 优化分区表查询
PARTITION PRUNING 可以显著提高分区表的查询性能因为它可以跳过不需要的分区。
例子
SELECT * FROM sales WHERE sale_date BETWEEN 2023-01-01 AND 2023-12-31;3. 使用 EXPLAIN ANALYZE 深入分析查询性能
EXPLAIN ANALYZE 可以提供详细的查询执行计划和实际执行时间帮助你更好地优化查询。
例子
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id 123;4. 使用 OPTIMIZE TABLE 优化表性能
OPTIMIZE TABLE 可以回收未使用的空间提高表的性能。
OPTIMIZE TABLE users;5. 使用 ANALYZE TABLE 更新统计信息
ANALYZE TABLE 可以更新表的统计信息帮助优化器生成更高效的查询计划。
例子
ANALYZE TABLE users;三. 查询语句优化
1. 避免使用 SELECT *使用具体字段
反例
SELECT * FROM employee;正例
SELECT id, name, age FROM employee;原因 使用具体字段可以节省资源、减少网络开销且能避免回表查询。
2. 避免在 WHERE 子句中使用 OR
反例
SELECT * FROM user WHERE userid1 OR age18;正例
-- 使用 UNION ALL
SELECT * FROM user WHERE userid1
UNION ALL
SELECT * FROM user WHERE age18;原因当 OR 操作符连接的条件涉及多个列时数据库优化器可能无法有效地使用索引。特别是当这些列上有不同的索引时优化器可能无法选择最优的索引组合。
3. 避免在 WHERE 子句中使用函数
反例
SELECT * FROM users WHERE UPPER(username) JOHN;正例
SELECT * FROM users WHERE username JOHN;原因 在 WHERE 子句中使用函数会导致索引失效。
4. 避免在 WHERE 子句中对字段进行表达式操作
反例
SELECT * FROM user WHERE age - 1 10;正例
SELECT * FROM user WHERE age 11;原因 : 表达式操作会增加额外的计算开销。数据库引擎需要对每一行数据进行表达式计算然后再进行过滤这会显著增加查询的执行时间。
5. 使用 LIMIT 避免不必要的数据返回
反例
SELECT id, order_date FROM order_tab WHERE user_id666 ORDER BY create_date DESC;正例
SELECT id, order_date FROM order_tab WHERE user_id666 ORDER BY create_date DESC LIMIT 1;原因 LIMIT 提升查询效率避免多余的数据返回。
6. 批量操作插入、删除、查询
反例
for(User u : list) {INSERT INTO user(name, age) VALUES(#name#, #age#);
}正例
INSERT INTO user(name, age) VALUES
foreach collectionlist itemitem indexindex separator,(#{item.name}, #{item.age})
/foreach原因 批量插入性能更优。
7. 使用 UNION ALL 替换 UNION无重复记录时
反例
SELECT * FROM user WHERE userid1
UNION
SELECT * FROM user WHERE age10;正例
SELECT * FROM user WHERE userid1
UNION ALL
SELECT * FROM user WHERE age10;原因 UNION 会排序和合并UNION ALL 则省去这一步。
8. 避免在索引列上使用内置函数
反例
SELECT * FROM orders WHERE MONTH(order_date) 10 AND YEAR(order_date) 2023;正例
SELECT * FROM orders WHERE order_date 2023-10-01 AND order_date 2023-11-01;原因 索引列上使用函数会导致索引失效。
9. 在 GROUP BY 前进行条件过滤
反例
SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id
HAVING city 北京;正例
SELECT user_id, SUM(amount) AS total_amount
FROM orders
WHERE city 北京
GROUP BY user_id;10. 优化 LIKE 语句
反例
SELECT userId, name FROM user WHERE userId LIKE %123;正例
SELECT userId, name FROM user WHERE userId LIKE 123%;原因 % 放在前面会导致索引失效。
11. 使用小表驱动大表
例子 假设我们有个客户表和一个订单表。其中订单表有10万记录客户表只有1000行记录。现在要查询下单过的客户信息可以这样写
正例
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id c.id
);使用 IN 实现
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders
);原因 EXISTS 会逐行扫描 customers 表即小表对每一行 c.id在 orders 表大表中检查是否有 customer_id c.id 的记录。
12. IN 查询的元素不宜太多
反例
SELECT user_id, name FROM user WHERE user_id IN (1,2,3...1000000);正例 分批进行比如每批200个
SELECT user_id, name FROM user WHERE user_id IN (1,2,3...200);原因 如果 IN 后面的元素过多即使后面的条件加了索引还是会影响性能。
13. 优化 LIMIT 分页
反例
SELECT id, name, balance FROM account WHERE create_time 2020-09-19 LIMIT 100000, 10;正例 使用标签记录法
SELECT id, name, balance FROM account WHERE id 100000 LIMIT 10;延迟关联法
SELECT acct1.id, acct1.name, acct1.balance
FROM account acct1
INNER JOIN (SELECT a.id FROM account a WHERE a.create_time 2020-09-19 LIMIT 100000, 10
) AS acct2
ON acct1.id acct2.id;14. 避免返回过多数据量
反例
SELECT * FROM LivingInfo WHERE watchId userId AND watchTime DATE_SUB(NOW(), INTERVAL 1 YEAR);正例
-- 分页查询
SELECT * FROM LivingInfo WHERE watchId userId AND watchTime DATE_SUB(NOW(), INTERVAL 1 YEAR) LIMIT offset, pageSize;原因
查询效率 当返回的数据量过大时查询所需的时间会显著增加导致数据库性能下降。网络传输 大量数据的传输会占用网络带宽可能导致网络拥堵和延迟。减少返回的数据量可以降低网络传输的负担提高数据传输效率。
15. 优先使用连接查询而非子查询
反例
SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders);正例
SELECT DISTINCT c.*
FROM customers c
JOIN orders o ON c.id o.customer_id;原因 使用子查询可能会创建临时表。
16. INNER JOIN、LEFT JOIN、RIGHT JOIN优先使用 INNER JOIN如果是 LEFT JOIN左边表结果尽量小
反例
SELECT * FROM tab1 t1 LEFT JOIN tab2 t2 ON t1.size t2.size WHERE t1.id 2;正例
SELECT * FROM (SELECT * FROM tab1 WHERE id 2) t1
LEFT JOIN tab2 t2 ON t1.size t2.size;原因 如果 INNER JOIN 是等值连接返回的行数可能较少性能更好。使用 LEFT JOIN 时左边表数据结果尽量小条件尽量放在左边处理。
17. 避免 ! 或 操作符
反例
SELECT age, name FROM user WHERE age 18;正例 分为两个查询
SELECT age, name FROM user WHERE age 18;
SELECT age, name FROM user WHERE age 18;原因 某些情况下使用 ! 或 操作符可能导致索引失效从而影响查询性能。这是因为 ! 和 操作符通常会导致数据库引擎无法高效地利用索引。
18. 使用联合索引时遵循最左匹配原则
例子 联合索引 (userId, age)查询 userId 和 age 时优先使用 userId。
表结构
CREATE TABLE user (id int(11) NOT NULL AUTO_INCREMENT,userId int(11) NOT NULL,age int(11) DEFAULT NULL,name varchar(255) NOT NULL,PRIMARY KEY (id),KEY idx_userid_age (userId, age) USING BTREE
) ENGINEInnoDB AUTO_INCREMENT2 DEFAULT CHARSETutf8;反例
SELECT * FROM user WHERE age 10;正例
SELECT * FROM user WHERE userId 10 AND age 10;正例
SELECT * FROM user WHERE userId 10;原因 使用联合索引时遵循最左匹配原则是非常重要的这有助于数据库引擎高效地利用索引从而提高查询性能。最左匹配原则意味着在查询条件中从联合索引的最左边开始匹配索引列直到遇到不匹配的列为止。
19. 对 WHERE 和 ORDER BY 涉及的列建索引
反例
SELECT * FROM user WHERE address 深圳 ORDER BY age;正例
ALTER TABLE user ADD INDEX idx_address_age (address, age);20. 使用覆盖索引
正例
SELECT id, name FROM user WHERE userId LIKE 123%;21. 删除冗余索引
反例
KEY idx_userId (userId)
KEY idx_userId_age (userId, age)正例
-- 删除 userId 索引因为组合索引AB相当于创建了A和AB索引
KEY idx_userId_age (userId, age)原因 重复的索引需要维护并且优化器在优化查询的时候也需要逐个地进行考虑这会影响性能。
22. 使用 INDEX HINTS 强制使用特定索引
例子
SELECT * FROM users USE INDEX (idx_username) WHERE username john;原因 在某些情况下优化器可能选择错误的索引使用 INDEX HINTS 可以强制使用特定索引。
23. 使用 FULLTEXT 索引进行全文搜索
例子
CREATE FULLTEXT INDEX idx_fulltext ON articles (content);SELECT * FROM articles WHERE MATCH(content) AGAINST(search term);原因 FULLTEXT 索引可以提高全文搜索的性能。
24. 避免在 ORDER BY 子句中使用表达式
反例
SELECT * FROM users ORDER BY LENGTH(username);正例
SELECT * FROM users ORDER BY username;原因 在 ORDER BY 子句中使用表达式会导致索引失效。
25. 避免超过3个以上的表连接
原因 连接表越多编译的时间和开销也就越大。把连接表拆开成较小的几个执行可读性更高。
26. 使用 CASE 语句替代复杂的 IF 条件
例子
SELECT id, CASE WHEN age 18 THEN MinorWHEN age BETWEEN 18 AND 60 THEN AdultELSE SeniorEND AS age_group
FROM users;原因 CASE 语句可以使逻辑更清晰提高可读性和维护性。
27. 使用 WITH 子句Common Table Expressions, CTE
例子
WITH active_users AS (SELECT id FROM users WHERE status active
)
SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users);原因 CTE 可以使查询结构更清晰便于理解和维护。
28. 避免使用 DISTINCT 除非必要
反例
SELECT DISTINCT user_id FROM orders;正例
SELECT user_id FROM orders GROUP BY user_id;原因 DISTINCT 会进行额外的排序和去重操作影响性能。如果只需要去重可以使用 GROUP BY。
29. 使用 PARTITION PRUNING 优化分区表查询
例子
SELECT * FROM sales WHERE sale_date BETWEEN 2023-01-01 AND 2023-12-31;原因 PARTITION PRUNING 可以显著提高分区表的查询性能因为它可以跳过不需要的分区。
四. 补充
1. 合理利用视图View进行复杂查询
正例
CREATE VIEW view_user_orders AS
SELECT u.id, u.name, o.order_id, o.amount
FROM user u JOIN orders o ON u.id o.user_id;-- 使用视图查询
SELECT * FROM view_user_orders WHERE amount 100;2. 使用表分区Partitioning优化大表性能
正例
CREATE TABLE sales (sale_id INT,sale_date DATE,amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (PARTITION p2019 VALUES LESS THAN (2020),PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022)
);3. 合理使用存储过程Stored Procedure来减少多次 SQL 交互
正例
CREATE PROCEDURE update_and_select(IN user_id INT)
BEGINUPDATE users SET last_login NOW() WHERE id user_id;SELECT * FROM users WHERE id user_id;
END;4. 使用临时表Temporary Tables处理复杂查询
正例
CREATE TEMPORARY TABLE temp_users AS
SELECT id FROM users WHERE status active;SELECT * FROM orders WHERE user_id IN (SELECT id FROM temp_users);原因 临时表可以在处理复杂查询时提高性能特别是在多次使用相同子查询结果的情况下。
5. 使用适当的隔离级别
原因 在高并发环境中选择适当的事务隔离级别如 READ COMMITTED可以避免不必要的锁竞争和阻塞提升并发效率。
6. 避免在事务中执行非必要的操作
原因 在事务中应避免执行耗时操作比如网络请求或复杂计算以减少锁的持有时间。优先确保事务操作集中在必要的数据变更上。
7. 使用批量更新或删除
正例
-- 分批删除
DELETE FROM orders WHERE status obsolete LIMIT 1000;
