一：MySQL資料庫的性能的影響分析及其優化

来源:http://www.cnblogs.com/NiceCui/archive/2017/11/06/7794437.html
-Advertisement-
Play Games

MySQL資料庫的性能的影響分析及其優化 MySQL資料庫的性能的影響 一. 伺服器的硬體的限制 二. 伺服器所使用的操作系統 三. 伺服器的所配置的參數設置不同 四. 資料庫存儲引擎的選擇 五. 資料庫的參數配置的不同 六. (重點)資料庫的結構的設計和SQL語句 1). 伺服器的配置和設置(cp ...


MySQL資料庫的性能的影響分析及其優化

MySQL資料庫的性能的影響

一. 伺服器的硬體的限制

二. 伺服器所使用的操作系統

三. 伺服器的所配置的參數設置不同

四. 資料庫存儲引擎的選擇

五. 資料庫的參數配置的不同

六. (重點)資料庫的結構的設計和SQL語句

1). 伺服器的配置和設置(cpu和可用的記憶體的大小)

 1.網路和I/O資源 
 2.cpu的主頻和核心的數量的選擇
 (對於密集型的應用應該優先考慮主頻高的cpu)
 (對於併發量大的應用優先考慮的多核的cpu)
 3.磁碟的配置和選擇
 (使用傳統的機械硬碟:
    特點：讀寫較慢、存儲空間大、最常見、使用最多、價格低;
    工作過程：移動磁頭到磁碟錶面上的正確位置;
             等待磁碟的旋轉，使得所得所需的數據在磁頭之下;
             等待磁碟旋轉過去，所有所需的數據都被磁頭讀出
    選擇因素：存儲容量、傳輸速度、訪問時間、主軸轉速、物理尺寸)
 (使用RAID增強傳統的機器硬碟的性能：
    特點：利用小的磁碟組成大的磁碟並提供數據的冗餘保證數據的完整性的技術
    資料庫中所使用的RAID的級別：
        RAID0級別、RAID1級別、RAID5級別[分散式奇偶校驗磁碟陣列]、RAID10[分片的鏡像(資料庫最好的方式)]
    RAID級別選擇：如下圖)
 (使用固態存儲的SSD和PCI-E卡:
    特點：相對於機械盤固態磁碟有更好的隨機讀寫性能;
         相對於機械固態磁碟能更好的支持併發;
         相對於機械固態磁碟更容易損壞
    SSD:使用SATA介面可以替換傳統的磁碟而不需要任何的改變[受到介面的速度的限制];
        SATA介面的SSD同樣支持RAID技術
    PCI-E卡(Fusion-IO卡)：無法使用在SATA介面[需要使用獨特的驅動和配置];
                         價格貴,使用了cpu的資源和記憶體
    使用的場景：適用於存在大量的隨機I/O的場景;
              適用於解決單線程負載的I/O瓶頸)
 (使用網路存儲NAS和SAN：
    SAN[光纖接入伺服器]:大量順序讀寫操作、讀寫I/O、緩存、I/O合併、隨機讀寫慢（不如本地的RAID）
    NAS設備使用網路連接，基於文件的協議如NFS或者SMB來訪問
    適合場景：資料庫的備份、)

使用RAID增強傳統的機器硬碟的性能->RAID0級別使用RAID增強傳統的機器硬碟的性能->RAID0級別使用RAID增強傳統的機器硬碟的性能->RAID1級別使用RAID增強傳統的機器硬碟的性能->RAID1級別使用RAID增強傳統的機器硬碟的性能->RAID5級別使用RAID增強傳統的機器硬碟的性能->RAID5級別

不同REAID級別的對比：

註意事項：

 1.64位資料庫的版本使用32位的伺服器的版本
 2.記憶體的主頻的選擇主板所能支持的最大記憶體的頻率
總結：
    對於cpu：
        1.64位的cpu一定能夠要工作在64位的系統下
        2.對於併發比較高的場景cpu的數量比頻率重要
        3.對於cpu密集型的場景和複雜SQL則頻率越高越好
    對於記憶體：
        1.選擇主板所能使用的最高頻率的記憶體
        2.記憶體的大小對性能很重要，所以儘可能的大
    I/O子系統：
        1.PCIe -> SSD -> RAID10 -> 磁碟 -> SAN

2). 操作系統對性能的影響

Windows、FreeBSD、Solaris、Linux
centos的參數優化的設置：
    (1)內核相關的參數（/etc/sysctl.conf）
        net.core.somaxconn = 65535
        net.core.netdev_max_backlog = 65535
        net.ipv4.tcp_max_syn_backlog = 65535

        net.ipv4.tcp_fin_timeout = 10
        net.ipv4.tcp_tw_reuse = 1
        net.ipv4.tcp_tw_recycle = 1

        net.core.wmem_defaullt = 87380
        net.core.wmem_max = 16777216
        net.core.rmem_defaullt = 87380
        net.core.rmem_max = 16777216

        net.ipv4.tcp_keepalive_time = 120
        net.ipv4.tcp_keepalive_intvl = 30
        net.ipv4.tcp_keepalive_probes = 3

        kernel.shmmax = 4294967295
        vm.swappiness = 0
    (2)增加資源限制（/etc/security/limit.conf）
        * soft nofile 65535
        * hard nofile 65535
            * 表示對所有的用戶有效
            soft 指的是當前系統的生效的設置
            hard 表明系統中所能設定的最大值
            nofile 表示所限制的資源是打開文件的最大數目
            65535 就是限制的數量
    (3).磁碟調度策略(/sys/block/devname/queue/scheduler)
        noop(電梯式調度策略）、deadline（截止時間調度策略）、anticipatory(預料I/O調度策略)
        cat /sys/block/sda/queue/scheduler
        noop anticipatory deadline [cfq]

            echo deadline > /sys/block/sda/queue/scheduler

3).MySQl的資料庫的體系

MySQl的資料庫的體系MySQl的資料庫的體系

4).MySQl的資料庫的存儲引擎

(1).Mysql之存儲引擎MyISAM
    組成的結構：表為MYD和MYI、frm的文件組成
    特性：併發性和鎖級別
         MyISAM表支持索引類型
         MyISAM表支持數據的壓縮(命令行：myisampack)
             myisampack -b -f myIsam.MYI;
            壓縮後的表不能進行寫操作，只能進行讀操作
    修複:對資料庫中的表進行檢查並修複:
        check table mytable;
        repair table mytable;

        myisamchk工具,修複時資料庫服務必須停止
    限制：使用MySQL5.0之前時預設表的大小4G(存儲大表修改MAX_Rows和AVG_ROW_LENGTH)
         使用MySQL5.0之後的版本預設支持256TB
    適用的場景:非事務型的應用
              只讀類的應用
              空間類的應用(GPS的數據)
(2).Mysql之存儲引擎InnoDB
    mysql5.5.8之後版本預設使用的存儲引擎
    組成結構：通過設置innodb_file_per_table參數存儲的位置不同
                ON:獨立表空間：tablename.ibd
                OFF:系統表空間:ibdataX
    建議：對於mysql中建議使用InnoDB的獨立表空間
    特性：事務性存儲引擎
         完全支持事務的存儲引擎
         Redo log(存儲已經提交的事務)和Undo log(存儲未提交的事務)

         InnoDB支持行級別鎖
         最大程式的支持併發
         行級別的鎖是由存儲引擎層實現的
    鎖：共用鎖(讀鎖)、獨占鎖(寫鎖)
         表級鎖、行級鎖
        阻塞：確保事務併發的正常的執行
        死鎖：兩個或者兩個以上的事務執行過程中相互等待對方的資源而產生的一種異常
    InnoDB狀態檢查：
        show engine innodb status;    
    適用場景：InooDB適用於大多數OLTP應用
(3).Mysql之存儲引擎CSV
    特點：數據以文本的方式存儲在文件中
        .CSV文件存儲表的內容
        .CSM文件存儲表的元數據如表的狀態和數據量
        .frm文件存儲表的結構的信息
        以CSV格式進行數據的存儲
        所有的列必須不能為NULL的
        不支持索引(不適合大表，不適合線上處理)
        可以對數據文件直接進行編輯
    適用的場景：適合作為數據交換的中間表
              mysql數據目錄->csv文件->其他web程式
              excel電子錶格 -> csv文件 -> mysql數據目錄
(4).Mysql之存儲引擎Archive
    特點：以zlib對錶數據進行壓縮，磁碟I/O更少
         數據存儲在ARZ為尾碼的文件中

         只支持insert和select操作
         只支持在自增的ID列上加索引
    適用場景：
         日誌和數據採集類的應用
(4).Mysql之存儲引擎Memory
    特點：數據只保存在記憶體中
         Memory存儲引擎的I/O效率特別高
         支持HASH索引和BTree索引
         所有的欄位為固定長度
         不支持BLOG和TEXT等大欄位

         Memory存儲引擎使用表級鎖
         表中存儲數據的最大值由max_heap_table_size參數決定
    適用場景：用於查找或者映射表，例如郵編和地區
             用於保存數據分析產生的中間表
             用於緩存周期性聚合數據的結果表

5).MySQl的資料庫的伺服器參數

(1).Mysql配置參數作用域
    全局參數 
        set global 參數名=參數值；
        set @@global.參數名：=參數值；
    會話參數
        set[session] 參數名=參數值；
        set @@session.參數名：=參數值；
(2).記憶體配置相關的參數
        確定可以使用的記憶體的上限
        確定MySQL的每個連接使用的記憶體
            sort_buffer_size join_buffer_size
            read_buffer_size read_rnd_buffer_size
        確定需要為操作系統保留多少記憶體
        如何為緩存池分配記憶體
            Innodb_buffer_pool_size
            總記憶體-（每個線程鎖需要的記憶體*連接數）- 系統的保留記憶體
            key_buffer_size
(3).I/O相關配置參數
        InnoDb存儲引擎的I/O參數設置：
        Innodb_log_file_size
        Innodb_log_file_in_group
        Innodb_log_buffer_size
        Innodb_flush_log_at_trx_commit

        Innodb_flush_method = O_DIRECT
        Innodb_file_per_table = 1
        Innodb_doublewrite = 1
      MySIAM存儲引擎的I/O參數設置：
        delay_key_write
            OFF:每次操作後刷新鍵緩衝中的臟塊到磁碟
            ON:只對在鍵表時指定了delay_key_write選項的表使用延遲刷新
            ALL:對所有MYSIAM表都使用延遲鍵寫入
(4).安全相關配置參數
        expire_logs_days 指定自動清理binlog的天數
        max_allowed_packet 控制MySQL可以接受的包的大小（32M）
        skip_name_resolve 禁用DNS查找

        sysdate_is_now 確保sysdate()返回確定性的日期
        read_only 禁止非super許可權的用戶寫許可權
        skip_slave_start 禁止Slave自動恢復
        sql_mode 設置MySQL所使用的SQL模式
            strict_trans_tables
            no_engine_subtitutoion
            no_zero_date
            no_zero_in_date
            only_full_group_by
(5).其他相關配置參數
        sync_binlog = 1控制MySQL如何向磁碟刷新binlog
        tmp_table_size和max_heap_table_size 控制記憶體臨時表的大小(設置一致)
        max_connections = 2000 控制允許的最大連接數

5).MySQl的資料庫的結構設計和SQL的優化

(1).過分的反範式化為表的建立太多的列
(2).過分的範式化造成太多的表關聯
(3).在OLTP環境中使用不恰當的分區表
(4).使用外鍵保證數據的完整性

性能優化的順序

  • 資料庫結構設計和SQL語句優化
  • 資料庫的存儲引擎的選擇和參數的配置
  • 系統的選擇及其優化
  • 硬體升級
 

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Mysql修改已有數據的字元集
    Mysql修改已有數據的字元集 問題 在生產環境中跑了很久,發現MysqlClient連接的字元集是預設的latin1,我們一直以為都是utf8,造成這樣的誤解,是因為在內網環境中,我們是源碼編譯的Mysql,並指定了編譯選項字元集位utf8,這時Mysql的是預設字元接都是utf8. 而在外網,我 ...
  • 二:Redis快速入門及應用
    轉載：https://mp.weixin.qq.com/s/exE_UmZDeeWZKGNhbOFJMQ 作者｜張輝清、楊麗編輯｜雨多田光 文末有 demo 下載 Redis的使用難嗎？不難，Redis用好容易嗎？不容易。Redis的使用雖然不難，但與業務結合的應用場景特別多、特別緊，用好並不容易。 ...
  • Oracle 11g 字元集修改
    服務端字元集修改 1、確認服務端字元集 1 select userenv('language') from dual; 2、修改服務端字元集 首先以 DBA 身份登錄 Oracle。Windows 系統下直接在命令行下運行命令 sqlplus /as sysdba 或在 SQL PLUS 下運行命令 ...
  • RHEL7.2 安裝Hadoop-2.8.2
    創建三台虛擬機，IP地址為：192.168.169.101，192.168.169.102，192.168.169.103 將192.168.169.102為namenode,192.168.169.101,192.168.169.103為datanode 關閉防火牆，安裝JDK1.8，設置SSH無 ...
  • Oracle中的行轉列例子詳解
    --場景1： A B a 1 a 2 a 3 b 1 b 2 b 3 希望實現如下效果： a 1,2,3 b 4,5 create table tmp as select 'a' A, 1 B from dual union all select 'a' A, 2 B from dual union... ...
  • Redis-安裝、啟動(後臺啟動)
    安裝Redis 下載redis安裝包http://download.redis.io/redis-stable.tar.gz 解壓安裝包tar xzf redis-stable.tar.gz 安裝cd redis-stable/srcmake 或者直接從網上下載redis的壓縮包，然後解壓，再用ma ...
  • 一：Redis的7個應用場景
    Redis的7個應用場景 一：緩存——熱數據 熱點數據（經常會被查詢，但是不經常被修改或者刪除的數據），首選是使用redis緩存，畢竟強大到冒泡的QPS和極強的穩定性不是所有類似工具都有的，而且相比於memcached還提供了豐富的數據類型可以使用，另外，記憶體中的數據也提供了AOF和RDB等持久化機 ...
  • mongo資料庫命令簡單學習
    db.getCollection('product').update({status:"offline"},{$set:{status:"online"}},false,true) update更新，把所有status為offline的都改為online,註意加引號 db.collection.up... ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類