服务器雪崩的应对策略之----SQL优化

2024-06-24 11:52

本文主要是介绍服务器雪崩的应对策略之----SQL优化,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

SQL语句的优化是数据库性能优化的重要方面,特别是在处理大规模数据或高频访问时。作为一个C++程序员,理解SQL优化不仅有助于编写高效的数据库操作代码,还能增强对系统性能瓶颈的整体把握。以下是详细的SQL语句优化技巧和策略:

SQL优化

      • 1. 选择合适的数据类型
      • 2. 使用索引
      • 3. 优化查询
      • 4. 范式化和反范式化
      • 5. 查询重写
      • 6. 使用缓存
      • 7. 优化数据库设计
      • 8. 分析和监控
      • 9. 调整配置
          • 1、内存分配
          • 2、连接池

1. 选择合适的数据类型

示例

  • 使用CHAR而不是VARCHAR
 -- 如果用户的状态是固定长度(如'active', 'inactive'),可以使用CHARCREATE TABLE users (id INT PRIMARY KEY,status CHAR(8));
  • 使用TINYINT代替INT
    -- 如果用户的年龄范围在0-255,可以使用TINYINT
    CREATE TABLE users 
    (id INT PRIMARY KEY,age TINYINT
    );
    

2. 使用索引

示例

  • 创建索引

    -- 为用户的年龄创建索引
    CREATE INDEX idx_users_age ON users(age);
    
  • 避免过多的索引

    -- 如果只需查询用户的年龄和名字,不需要对所有列都创建索引
    CREATE INDEX idx_users_age_name ON users(age, name);
    
  • 组合索引

    -- 为用户的年龄和注册日期创建组合索引
    CREATE INDEX idx_users_age_reg_date ON users(age, registration_date);
    

3. 优化查询

示例

  • 选择合适的查询

    -- 避免使用SELECT *
    SELECT name, age FROM users WHERE age > 30;
    
  • 使用子查询和联结

    -- 将子查询改写为JOIN
    SELECT u.name, o.order_date
    FROM users u
    JOIN orders o ON u.id = o.user_id
    WHERE u.age > 30;
    
  • WHERE条件优化

    -- 将最可能过滤大量数据的条件放在前面
    SELECT * FROM users WHERE age > 30 AND status = 'active';
    
  • 避免函数和操作符

    -- 避免对列进行函数操作
    SELECT * FROM users WHERE registration_date >= '2023-01-01';
    

4. 范式化和反范式化

示例

  • 范式化

    -- 第三范式设计:拆分表结构
    CREATE TABLE orders 
    (id INT PRIMARY KEY,user_id INT,product_id INT,order_date DATE,FOREIGN KEY (user_id) REFERENCES users(id)
    );
    
  • 反范式化

    -- 在高频读取的情况下,减少JOIN操作,反范式化
    CREATE TABLE order_details 
    (id INT PRIMARY KEY,user_name VARCHAR(255),product_name VARCHAR(255),order_date DATE
    );
    

5. 查询重写

示例

  • EXISTS vs IN

    -- 使用EXISTS而不是IN
    SELECT name FROM users WHERE EXISTS 
    (SELECT 1 FROM orders WHERE orders.user_id = users.id AND orders.total > 100
    );
    
  • JOIN优化

    -- 使用临时表优化大表JOIN
    CREATE TEMPORARY TABLE temp_orders AS
    SELECT * FROM orders WHERE order_date >= '2023-01-01';SELECT u.name, t.order_date FROM users u JOIN temp_orders t ON u.id = t.user_id;
    

6. 使用缓存

示例

  • 查询缓存

    -- MySQL查询缓存示例(假设MySQL版本支持)
    SET GLOBAL query_cache_size = 1048576; -- 1MB
    
  • 应用缓存

    // 在C++应用层使用Memcached缓存常用数据
    // 使用libmemcached库
    #include <libmemcached/memcached.h>memcached_st *memc;
    memcached_return rc;
    memcached_server_st *servers = NULL;
    char *key = "user_123";
    char *value;memc = memcached_create(NULL);
    servers = memcached_server_list_append(servers, "localhost", 11211, &rc);
    rc = memcached_server_push(memc, servers);
    memcached_server_list_free(servers);value = memcached_get(memc, key, strlen(key), NULL, NULL, &rc);
    

7. 优化数据库设计

示例

  • 分区

    -- 将大表按月份分区
    CREATE TABLE orders 
    (id INT PRIMARY KEY,user_id INT,order_date DATE
    )
    PARTITION BY RANGE (YEAR(order_date)) 
    (PARTITION p2023 VALUES LESS THAN (2024),PARTITION p2024 VALUES LESS THAN (2025)
    );
    
  • 分片

    -- 数据库分片:例如根据用户ID进行分片
    -- 分片1:存储用户ID 1-1000
    -- 分片2:存储用户ID 1001-2000
    

8. 分析和监控

示例

  • 使用EXPLAIN

    -- 使用EXPLAIN分析查询执行计划
    EXPLAIN SELECT name FROM users WHERE age > 30;
    
  • 监控系统性能

    -- 监控查询时间、锁等待、磁盘IO等指标
    SHOW STATUS LIKE 'Handler_read_rnd_next';
    SHOW ENGINE INNODB STATUS;
    

9. 调整配置

示例

1、内存分配

调整数据库管理系统(DBMS)的内存配置参数,可以显著提高数据库性能。以下是MySQL的示例:

  • 调整InnoDB缓冲池大小:InnoDB缓冲池用于缓存数据和索引,是MySQL最重要的内存分配参数。

    -- 查看当前缓冲池大小
    SHOW VARIABLES LIKE 'innodb_buffer_pool_size';-- 将缓冲池大小设置为1GB
    SET GLOBAL innodb_buffer_pool_size = 1024 * 1024 * 1024;
    
  • 调整查询缓存大小:查询缓存可以缓存常见查询的结果。

    -- 查看当前查询缓存大小
    SHOW VARIABLES LIKE 'query_cache_size';-- 将查询缓存大小设置为64MB
    SET GLOBAL query_cache_size = 64 * 1024 * 1024;-- 启用查询缓存
    SET GLOBAL query_cache_type = 1;
    
2、连接池

使用数据库连接池可以减少数据库连接的建立和关闭开销,提高应用程序的性能。以下是如何在C++应用中使用连接池的示例,使用MySQL Connector/C++库:

  • 使用MySQL Connector/C++库配置连接池
    // 在C++应用中使用连接池
    // 使用MySQL Connector/C++库
    #include <mysql_driver.h>
    #include <mysql_connection.h>
    #include <cppconn/driver.h>
    #include <cppconn/connection.h>
    #include <cppconn/statement.h>
    #include <cppconn/exception.h>
    #include <cppconn/resultset.h>
    #include <mutex>
    #include <queue>
    #include <memory>
    #include <condition_variable>class ConnectionPool 
    {
    public:static ConnectionPool& getInstance() {static ConnectionPool instance;return instance;}std::shared_ptr<sql::Connection> getConnection() {std::unique_lock<std::mutex> lock(mutex_);while (pool_.empty()) {condition_.wait(lock);}auto conn = pool_.front();pool_.pop();return conn;}void releaseConnection(std::shared_ptr<sql::Connection> conn) {std::unique_lock<std::mutex> lock(mutex_);pool_.push(conn);condition_.notify_one();}private:ConnectionPool() {for (int i = 0; i < pool_size_; ++i) {auto conn = driver_->connect(url_, user_, password_);pool_.push(std::shared_ptr<sql::Connection>(conn, [this](sql::Connection* conn) {releaseConnection(std::shared_ptr<sql::Connection>(conn));}));}}~ConnectionPool() {while (!pool_.empty()) {pool_.pop();}}ConnectionPool(const ConnectionPool&) = delete;ConnectionPool& operator=(const ConnectionPool&) = delete;sql::mysql::MySQL_Driver* driver_ = sql::mysql::get_mysql_driver_instance();std::queue<std::shared_ptr<sql::Connection>> pool_;std::mutex mutex_;std::condition_variable condition_;const std::string url_ = "tcp://127.0.0.1:3306";const std::string user_ = "user";const std::string password_ = "password";const int pool_size_ = 10;
    };// 使用连接池获取和释放连接
    int main() 
    {auto& pool = ConnectionPool::getInstance();auto conn = pool.getConnection();try {std::shared_ptr<sql::Statement> stmt(conn->createStatement());std::shared_ptr<sql::ResultSet> res(stmt->executeQuery("SELECT 'Hello, World!' AS _message"));while (res->next()) {std::cout << res->getString("_message") << std::endl;}} catch (sql::SQLException &e) {std::cerr << "SQLException: " << e.what() << std::endl;}// 连接会自动返回到连接池return 0;
    }
    

这个示例展示了如何创建一个简单的连接池类,并在C++应用中使用该连接池来管理和复用数据库连接。连接池的使用可以显著减少数据库连接的建立和销毁时间,从而提高应用程序的性能和响应速度。

通过这些示例,可以更直观地理解各项SQL优化策略的具体应用及其效果。

这篇关于服务器雪崩的应对策略之----SQL优化的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1090074

相关文章

Vue3 的 shallowRef 和 shallowReactive:优化性能

大家对 Vue3 的 ref 和 reactive 都很熟悉,那么对 shallowRef 和 shallowReactive 是否了解呢? 在编程和数据结构中,“shallow”(浅层)通常指对数据结构的最外层进行操作,而不递归地处理其内部或嵌套的数据。这种处理方式关注的是数据结构的第一层属性或元素,而忽略更深层次的嵌套内容。 1. 浅层与深层的对比 1.1 浅层(Shallow) 定义

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

服务器集群同步时间手记

1.时间服务器配置(必须root用户) (1)检查ntp是否安装 [root@node1 桌面]# rpm -qa|grep ntpntp-4.2.6p5-10.el6.centos.x86_64fontpackages-filesystem-1.41-1.1.el6.noarchntpdate-4.2.6p5-10.el6.centos.x86_64 (2)修改ntp配置文件 [r

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数,谈性能都是耍流氓。MySQL没有限制单表最大记录数,它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐分库分表。性能由综合因素决定,抛开业务复杂度,影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考,并非铁律。 博主曾经操作过超过4亿行数据

HDFS—存储优化(纠删码)

纠删码原理 HDFS 默认情况下,一个文件有3个副本,这样提高了数据的可靠性,但也带来了2倍的冗余开销。 Hadoop3.x 引入了纠删码,采用计算的方式,可以节省约50%左右的存储空间。 此种方式节约了空间,但是会增加 cpu 的计算。 纠删码策略是给具体一个路径设置。所有往此路径下存储的文件,都会执行此策略。 默认只开启对 RS-6-3-1024k

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

使用opencv优化图片(画面变清晰)

文章目录 需求影响照片清晰度的因素 实现降噪测试代码 锐化空间锐化Unsharp Masking频率域锐化对比测试 对比度增强常用算法对比测试 需求 对图像进行优化,使其看起来更清晰,同时保持尺寸不变,通常涉及到图像处理技术如锐化、降噪、对比度增强等 影响照片清晰度的因素 影响照片清晰度的因素有很多,主要可以从以下几个方面来分析 1. 拍摄设备 相机传感器:相机传

MySQL数据库宕机,启动不起来,教你一招搞定!

作者介绍:老苏,10余年DBA工作运维经验,擅长Oracle、MySQL、PG、Mongodb数据库运维(如安装迁移,性能优化、故障应急处理等)公众号:老苏畅谈运维欢迎关注本人公众号,更多精彩与您分享。 MySQL数据库宕机,数据页损坏问题,启动不起来,该如何排查和解决,本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志,排查哪个表(表空间

在JS中的设计模式的单例模式、策略模式、代理模式、原型模式浅讲

1. 单例模式(Singleton Pattern) 确保一个类只有一个实例,并提供一个全局访问点。 示例代码: class Singleton {constructor() {if (Singleton.instance) {return Singleton.instance;}Singleton.instance = this;this.data = [];}addData(value)