不刪庫不跑路 — 數據庫優化

  • 2019 年 10 月 6 日
  • 筆記

數據庫Mysql在後端開發工作中，必不可少，關於mysql優化的知識也是後端工程師必備的。接下來小強將分階段的向大家介紹關於關於Mysql優化的相關知識。

一、優化概述

以下是從官方文檔摘抄出來的關於Mysql優化的概述

Optimization Overview：Database performance depends on several factors at the database level, such as tables, queries, and configuration settings.

由此可見，數據庫的性能是由多方面因素影響的，包括硬件設備、數據庫表設計、查詢語句、數據庫配置等等。

1.1數據庫層面：表格設計，查詢語句，配置信息等

關於這方面的考慮，我們需要問自己這樣幾個問題：

  • 數據庫是否設計合理？
  • 每列是否有正確的數據類型，每列的長度是否合理？ 我們要注意的是經常update的表要少些列，否則性能會變差。

注意InnoDB的一些基本特性如下：

每個InnoDB存儲引擎的表，最大為1000個列，一行數據的大小小於二分之一頁的大小(默認一頁16KB)，即8000個位元組。但是不包括VARBINARY、VARCHAR、BLOB、or TEXT類型.

  • index設計合理？
  • 是否選擇了正確的存儲引擎？InnoDb 還是 MyISAM？(Mysql5.5以上默認都是InnoDB)
  • 我們的應用是否使用了正確的鎖機制？大量請求並行？ 從InnoDB官方文檔中，我們可以看到InnoDB本身解決了很多關於鎖的問題

The InnoDB storage engine handles most locking issues without involvement from you, allowing for better concurrency in the database and reducing the amount of experimentation and tuning for your code.

  • 合適的緩存大小？

以上的這些問題，在接下來的篇章中都會一一介紹。

1.2硬件層面

硬件層面的優化我們考慮的問題主要有以下幾點：

  • 硬盤查找、平均查找時間小於10ms
  • 分佈式解決
  • 讀寫分離
  • CPU 內存帶寬（memory bandwidth）

1.3 MyISAM & InnoDB差別

兩個搜索引擎主要區別如下：

  • MySQL默認採用的是Innodb。(5.5+)
  • MyISAM不支持事務，而InnoDB支持。InnoDB的AUTOCOMMIT默認是打開的，即每條SQL語句會默認被封裝成一個事務，自動提交，這樣會影響速度，所以最好是把多條SQL語句顯示放在begin和commit之間，組成一個事務去提交。
  • InnoDB支持數據行鎖定，MyISAM不支持行鎖定，只支持鎖定整個表。即 MyISAM同一個表上的讀鎖和寫鎖是互斥的，MyISAM並發讀寫時如果等待隊列中既有讀請求又有寫請求，默認寫請求的優先級高，即使讀請求先到，所以 MyISAM不適合於有大量查詢和修改並存的情況，那樣查詢進程會長時間阻塞。因為MyISAM是鎖表，所以某項讀操作比較耗時會使其他寫進程餓死。
  • InnoDB支持外鍵，MyISAM不支持。
  • InnoDB的主鍵範圍更大，最大是MyISAM的2倍。
  • InnoDB不支持全文索引，而MyISAM支持。全文索引是指對char、 varchar和text中的每個詞（停用詞除外）建立倒排序索引。MyISAM的全文索引其實沒啥用，因為它不支持中文分詞，必須由使用者分詞後加入空 格再寫到數據表裡，而且少於4個漢字的詞會和停用詞一樣被忽略掉。
  • 沒有where的count(*)使用MyISAM要比InnoDB快得多。因為MyISAM內置了一個計數器，count(*)時它直接從計數器中讀，而InnoDB必須掃描全表。所以在InnoDB上執行count(*)時一般 要伴隨where，且where中要包含主鍵以外的索引列。為什麼這裡特彆強調「主鍵以外」？因為InnoDB中primary index是和raw data存放在一起的，而secondary index則是單獨存放，然後有個指針指向primary key。所以只是count(*)的話使用secondary index掃描更快，而primary key則主要在掃描索引同時要返回raw data時的作用較大。
  • Innodb 不僅僅緩存索引，同時還會緩存實際的數據。

通過以下指令，我們可以看出Mysql的引擎到底是什麼：

通過下面指令可以知道自己所使用的mysql版本是什麼：

二、優化標準

2.1Mysql 物理結構

這裡，首先看一張圖了解一下Mysql的物理結構：

1.最上層是一些客戶端和連接服務，包含本地sock通信和大多數基於客戶端/服務端工具實現的類似於tcp/ip的通信。主要完成一些類似於連接處理、授權認證、及相關的安全方案。在該層上引入了線程池的概念，為通過認證安全接入的客戶端提供線程。同樣在該層上可以實現基於SSL的安全鏈接。服務 器也會為安全接入的每個客戶端驗證它所具有的操作權限。

2.第二層架構主要完成大多少的核心服務功能，如SQL接口，並完成緩存的查詢，SQL的分析和優化及部分內置函數的執行。所有跨存儲引擎的功能也 在這一層實現，如過程、函數等。在該層，服務器會解析查詢並創建相應的內部解析樹，並對其完成相應的優化如確定查詢表的順序，是否利用索引等，最後生成相 應的執行操作。如果是select語句，服務器還會查詢內部的緩存。如果緩存空間足夠大，這樣在解決大量讀操作的環境中能夠很好的提升系統的性能。

3.存儲引擎層，存儲引擎真正的負責了MySQL中數據的存儲和提取，服務器通過API與存儲引擎進行通信。不同的存儲引擎具有的功能不同，這樣我們可以根據自己的實際需要進行選取。

4.數據存儲層，主要是將數據存儲在運行於裸設備的文件系統之上，並完成與存儲引擎的交互。

2.2Explain語句

衡量sql語句的好壞，大家第一個想到的就應該是Explain語句，通過Explain可以看到SQL的基本信息：

有以下幾個重點信息需要我們關註：

select_type: select查詢的類型，主要是區別普通查詢和聯合查詢、子查詢之類的複雜查詢。 type: 訪問類型，是較為重要的一個指標，結果值從好到壞依次是： system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

（1）SYSTEM

CONST的特例，當表上只有一條元組匹配

（2）CONST：

WHERE條件篩選後表上至多有一條元組匹配時，比如WHERE ID = 2 （ID是主鍵，值為2的要麼有一條要麼沒有）

（3）EQ_REF （4）REF

可以用於單表掃描或者連接。參與連接運算的表，是內表。（在代碼實現的算法中，兩表連接時作為循環中的內循環遍歷的對象，這樣的表稱為內表）。 基於索引（連接字段上的索引是非唯一索引，操作符必須是「=」謂詞，連接字段值不可為NULL）做掃描，使得對外表的一條元組，內表可有若干條元組與之對應。

（5）REF_OR_NULL

類似REF，只是搜索條件包括：連接字段的值可以為NULL的情況，比如 where col = 2 or col is null

（6）INDEX_MERGE

多重範圍掃描。兩表連接的每個表的連接字段上均有索引存在且索引有序，結果合併在一起。適用於作集合的並、交操作

（7）RANGE

範圍掃描，基於索引做範圍掃描，為諸如BETWEEN，IN，>=，LIKE類操作提供支持

（8）INDEX_SCAN

索引做掃描，是基於索引在索引的葉子節點上找滿足條件的數據（不需要訪問數據文件）

（9）UNIQUE_SUBQUERY

在子查詢中，基於唯一索引進行掃描，類似於EQ_REF （10）INDEX_SUBQUERY

在子查詢中，基於除唯一索引之外的索引進行掃描 (11) Index

該聯接類型與ALL相同，除了只有索引樹被掃描，掃描全表的順序是按照索引順序掃描

（12）ALL

全表掃描或者範圍掃描：不使用索引，順序掃描，直接讀取表上的數據（訪問數據文件） rows: 這個數表示mysql要遍歷多少數據才能找到。（參考，未必準確） ref: 顯示使用哪個列或常數與key一起從表中選擇行。 extra: Only index：這意味着信息只用索引樹中的信息檢索出的，這比掃描整個表要快。 using where：就是使用上了where限制。 impossible where：表示用不着where，一般就是沒查出來啥。 Using filesort／Using temporary：性能很差，WHERE和ORDER BY的索引經常無法兼顧，如果按照WHERE來確定索引，那麼在ORDER BY時，就必然會引起Using filesort，這就要看是先過濾再排序划算，還是先排序再過濾划算。 union merge：index 交集

文末給大家提一個小的問題，歡迎大家後台留言和小強探討這個問題：

假設一個table中有一列col是varchar(35)類型的，並且這一列有索引，那麼如下的sql會走到索引嗎？以及為什麼呢？

  1. select * from table where col='abc';
  2. select * from table where col=abc;

本文簡單介紹道這裡，接下來的文章，你將能看到以下精彩內容：Mysql中到底哪些語句可以用到索引，InnoDB事務、鎖機制等內容。

VirMach 便宜 VPS

QNews

QNews