<sub id="gqw76"><listing id="gqw76"></listing></sub>
      <sub id="gqw76"><listing id="gqw76"></listing></sub>

    1. <form id="gqw76"><legend id="gqw76"></legend></form>
    2. MySQL深入研究--學習總結(4)

      前言

      接上文,繼續學習后續章節。細心的同學已經發現,我整理的并不一定是作者講的內容,更多是結合自己的理解,加以闡述,所以建議結合原文一起理解。

      第13章《為什么表數據刪除一般,表文件大小不變?》

      我們在知道MySQL表的存儲,在8.0之前的版本,表結構相關數據存在.frm文件中,表數據存在.ibd文件中??梢酝ㄟ^innoDB_file_per_table控制,OFF表示表數據頁放在系統共享表空間,這時候刪除表數據,文件大小也不會變。

      但即使,表數據單獨存在.ibd文件中,當我們通過delete刪除時,發現文件大小也不會變。

      這是因為表數據在B+結構中,當我們刪除一條數據時,并不會真把這個數據給徹底刪除,只是在這個位置上做個刪除的標記。被標記刪除的位置,可以被復用,等待下次有數據插入時,可以保存在該位置,但實際文件大小不會表的。不僅刪除,插入也是如此,當插入時,出現也分裂,就可能出現空洞。也就是說一個表經過大量的增刪改,就會出現大量的空洞。所以我們會發現但我們刪除表數據,文件大小也不會表。

      那么如何解決這個問題,把表壓縮下呢?

      可以通過:重建表

      這里,你可以使用alter table A engine=innoDB命令來重建表。

      在5.5版本版本之前,這個命令做了如下幾個操作:

      1、新建一個與表A一樣結構的表B。

      2、將表A的數據按ID自增的順序寫入表B。

      但是在這個過程中,增刪改是會丟失的。

      所以在5.6版本之后,引入了Online DDL。

      1、新增一個臨時文件,掃描A主鍵的所有數據頁。

      2、根據數據頁中表A的記錄生成B+樹,存儲到臨時文件。

      3、在生成過程中,將對A的所有更新操作,記錄在row log 文件中。

      4、臨時文件生成完畢后,將row log中的數據,維護到臨時文件中,然后用臨時文件替代表A的數據文件。

      第14章 《count(*)特別慢,怎么辦?》

      當一張數據量很大的表,比如一張1千萬數據量的表,做count(*)時,很慢這是什么原因呢?

      首先我們要從存儲引擎區分,在MyISAM中,count(*)的結果記錄在文件中,所以會直接返回。

      而innoDB中,count(*)時,需要一行一行掃描統計行數。所以當數據量很大時,就會變的很慢。

      那為什么innoDB不跟MyISAM一樣呢,這是因為MyISAM是不支持事務的。而在innoDB中,同一時刻,由于可重復讀的隔離級別特性,不同事務做統計查詢,查詢的結果可能是不一樣的。

      所以不能直接用一個統計給所有事務通用。

      那業務上我們的確需要統計全表的數量,怎么辦?

      1、把count(*)的數據保存在Redis中,每當插入刪除時,更新緩存,但這不適用于帶where條件的查詢。

      2、把count(*)的數值存在mysql表中。

      優缺點:

      如果把統計數據放在Redis,在單機器下,宕機就沒法用了,雖然可以通過集群來達到高可用。當數據庫的操作與Redis的操作,存在數據一致性的問題。比如既要返回count數據,又要返回最新的100條數據,就可能出現最新的數據不一定在count統計中,或者統計了最新的數據,但并不在表中,想一想這是為什么?

      所以建議,如果對count的實時性不是特別高的時候,可以使用該方案。

      正因為有Redis和MySQL沒法同時支持分布式事務,如果把count數據存在MySQL表中,通過事務就可以解決這樣的問題,但顯然兩次表操作也是缺點所在。

      count(*) 、count(1) 、count(id)、 count(字段)不同方式有什么區別?

      count(*)和count(1)都是只統計行數,不返回數據,性能最優,MySQL官方也說明了兩者本質沒有區別。

      count(id)掃描所有id,再統計行數,多了id字段的返回,索引性能比count(*)和count(1)略差.

      count(字段)掃描該字段并返回,如果該字段允許為null.就得統計不為null的數據,所以比count(id)性能差。

      第16章《order by是如何工作的?》

      背景條件:

      有這樣一張表

      CREATE TABLE `t` (
      `id` int(11) NOT NULL,
      `city` varchar(16) NOT NULL,
      `name` varchar(16) NOT NULL,
      `age` int(11) NOT NULL,
      `addr` varchar(128) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `city` (`city`)
      ) ENGINE=InnoDB;
      
      --執行這樣的一個查詢語句,MySQL是怎么工作的呢?
      select city,name,age from t where city='杭州' order by name limit 1000;
      

      1、首先根據輔助索引查詢出city="杭州"的數據。

      2、發現第一個city=“杭州”數據時,得到對應的id,再根據id去主鍵索引中查出該行的完整數據。

      3、查詢得到完整數據后,取city,name,age三個字段,放入sort buffer中。

      4、重復上述動作,查詢出索引city=“杭州”的數據。

      5、將sort buffer中的數據,按name排序,取出前1000條并返回。查詢結果。

      這個稱為全字段排序。

      幾個概念:

      sort buffer:MySQL為了排序的高效,為每個線程會在內存中開辟一塊內存,專門用于排序。

      sort buffer size:決定了排序緩沖區的大小,超過排序緩沖區的數據,便只能使用額外的文件用于排序。

      max_length_for_sort_data:限定用于排序時,單行數據的大小,超過這個大小的行,就會使用另外一個排序方法,如下。

      幾個疑問:

      如果單行的數據很大,sort buffer中可存放的數據就少了,那么就要分成很多臨時文件(歸并排序),排序的性能就會很低,怎么辦?

      MySQL發現當行數據的大小超過設置的max_length_for_sort_data時,就會采用另外一種算法,整個排序過程如下:

      1、首先根據輔助索引查詢出city="杭州"的數據。

      2、發現第一個city=“杭州”數據時,得到對應的id,再根據id去主鍵索引中查出該行的完整數據。

      3、查詢得到完整數據后,取id,name兩個字段,放入sort buffer中。

      4、重復上述動作,查詢出索引city=“杭州”的數據。

      5、將sort buffer中的數據,按name排序,取出前1000條。

      6、再根據sort buffer中name與id的對應關系,根據id再回表遍歷查詢出整行數據,取name,city,age三個字段返回給客戶端。

      我們會發現比第一種方法,多了一次回表查詢。這個成為rowid排序。

      第18章《為什么這些SQL語句邏輯相同,性能卻差異巨大?》

      主要講了三個場景,其實SQL優化的核心就是,能不能用到索引,能不能減少回表查詢,能不能使用到覆蓋索引,本質就是以空間換時間。

      場景一:條件字段做函數操作

      當一個查詢語句的where條件字段做了函數操作,是無法走索引的,比如where id +1 =2;

      本質上就是因為MySQL無法判斷該字段函數操作后不再有序了,只能全索引掃描。

      場景二:隱式類型轉換

      就是類型轉換,比如原本字段是varchar,缺沒有帶“”查詢,就會針對這個字段做類型轉換函數,MySQL發現字符類型和數值類型比較時,會把字符串轉換成數值。

      顯然當一個查詢語句的where條件字段做了函數操作,是無法走索引的。

      場景三:隱式字符編碼轉換

      當兩個表關聯查詢,關聯條件的兩個字段,字符集編碼不一致時,也需要進行函數轉換,同樣的也不會走索引。

      posted @ 2021-03-09 19:37  有夢想的老王  閱讀(346)  評論(0編輯  收藏  舉報
      最新chease0ldman老人|无码亚洲人妻下载|大香蕉在线看好吊妞视频这里有精品www|亚洲色情综合网

        <sub id="gqw76"><listing id="gqw76"></listing></sub>
        <sub id="gqw76"><listing id="gqw76"></listing></sub>

      1. <form id="gqw76"><legend id="gqw76"></legend></form>