因此,MySQL8.0.3引入了直方圖函數,它是壹列數據分布的近似值,為優化器提供了更多的統計信息。例如字段的數量、每個不同值的百分比、最大/最小值等。MySQL的直方圖分為:等寬直方圖和等高直方圖,MySQL會自動分配使用哪種類型的直方圖,所以不能幹涉。
直方圖也有壹定的限制:
創建和刪除直方圖
創建語法
創建直方圖時,可以同時創建多個列的直方圖,但必須指定桶數,範圍為1-1024,默認為100。對於桶數,要綜合考慮有多少個不同的值,數據的傾斜度和精度等。建議從較低的值開始,如果不符合要求,再依次增加。
刪除語法
直方圖信息
MySQL通過字典表column_statistics保存直方圖的定義,每行記錄壹個字段的直方圖,已經以JSON格式保存。
MySQL將輪廓直方圖賦給employees的first_name字段,默認值為100 bucket。
生成直方圖後,MySQL將所有數據加載到內存中,並在內存中執行所有工作。如果直方圖是在大型表上生成的,則可能存在將數百米的數據讀入內存的風險。因此,我們可以通過參數hitogram _ generation _ max _ mem _ size來控制生成直方圖所允許的最大內存,當指定的內存不能滿足所有數據集時,就會采用采樣。
從MySQL8.0.19開始,存儲引擎本身提供了存儲在表中的數據的采樣實現。當存儲引擎不支持時,MySQL需要全表掃描來使用默認采樣,這對大型表來說代價太大。采樣實現避免了全表掃描,提高了采樣性能。
INNODB_METRICS計數器可以用來監控數據頁的采樣情況,這需要提前打開計數器。
采樣率的計算公式為:sampled _ page _ read/(sampled _ pages _ read+sampled _ pages _ skipped)。
優化案例
復制壹個表,源表不加直方圖,新表加直方圖。
分別在兩個表上查看SQL的執行計劃。
可以看出,成本值從30214.45下降到18744.56,掃描行數從299822下降到41654,性能得到了提升。