松花皮蛋的黑板報
  • 分享在京東工作的技術感悟,還有JAVA技術和業內最佳實踐,大部分都是務實的、能看懂的、可復現的

掃一掃
關注公眾號

Mysql高級使用和技巧

博客首頁文章列表 松花皮蛋me 2019-03-25 22:16

一、普通索引和唯一索引

查詢上來說,普通索引查找到滿足條件的記錄后會接著查找下一個記錄(innodb的數據是按頁讀寫的),判斷是否滿足。然而唯一索引是查詢到了就立即返回了。所以如果你明確知道只有一條結果則應該加上limit 1

更新上來說,普通索引會用到charge buffer優化,將更新操作記錄在charge buffer中,不需要從磁盤中讀取數據然后再更新,當下次查詢該數據頁時再讀入內存然后執行merge相關操作,更新原數據

二、前綴索引

查詢上來說,前綴索引可能會導致在索引樹上命中率變高但是原數據命中并不一定高,造成了一定的查詢浪費。另外對于索引上的信息足夠滿足查詢條件的情況下,前綴索引會多一次回表操作,整體索引則是直接返回(也就是覆蓋索引),所以不要使用select *查詢

但是如果提高數據的區分度,比如倒序存儲、hash處理后存儲等,使用前綴索引存儲空間更小,查詢次數也不會太差,收益可能會更高

三、聯合索引

對于聯合索引來說,遵守最左前綴原則,也就是說如果只有idx-union(type,time,value)聯合索引,單純的type或者type and time作來查詢條件也會命中這條索引,但是單純value作為查詢條件則無法命中。另外如果存在范圍查詢比如between等也會導致無法命中

四、收縮表空間

當需要收縮表空間時,如果只是delete數據,表文件大小是不變的,會被mysql標記為可復用的空間,需要通過alter重建表才能釋放。當然如果是要刪除全部數據的話,首選應該是Truncate操作

五、count(*)操作

InnoDb是索引組織表,主鍵索引樹的葉子節點存的是整行數據,而普通索引樹的葉子節點是主鍵值(需要先查找k索引樹得到ID,然后再到ID索引樹查找,也就是回表),不管是優化器查詢哪個索引樹或者不使用索引,都需要將所有數據查出來然后累加返回,所以不推薦在innodb引擎的數據庫中頻繁執行count(*)操作

有時候某些業務場景并不需要返回一個精確的 COUNT 值,此時我們可以使用近似值來代替。我們可以使用 EXPLAIN 對表進行估算,要知道,執行 EXPLAIN 并不會真正去執行查詢,而是返回一個估算的近似值

六、顯示隨機信息

如果使用order by rand()實現,則需要在臨時表上進行rowid(有主鍵則是主鍵沒有則是系統生成標識行的rowid)排序操作,整體過程涉及全表掃描然后將數據放到內存臨時表再生成sort_buffer排序再從內存臨時表中取數據。如果sort_buffer_size無法存儲數據,則需要使用磁盤文件進行分塊存儲然后再歸并排序
正確的方式應該是:

> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1,1; // 在應用代碼里面取 Y1、Y2、Y3 值,拼出 SQL 后執行
select * from t limit @Y2,1;
select * from t limit @Y3,1;

七、where條件上不要使用函數

對索引字段做函數操作,可能會破壞索引值的有序性,因為b+樹中的同一層兄弟節點是有序的

八、where條件不要使用類型轉換

當字符串和數字做比較時,會將字符串轉換成數字,會觸發CAST等函數操作,觸發上一條規則

九、數據庫用錯索引時可以強制force index

我們知道Mysql結合掃描行數、是否使用臨時表、是否需要排序等綜合考慮選擇索引,當然就會出現用錯索引的情況。平時我們explain sql時顯示的預估掃描行數rows是mysql通過數據采樣,選擇這個索引其中n個數據頁,統計這些頁面的不同值,得到平均值,然后乘以這個索引的頁面數,得到基數,也就是區分度。另外還有如果回表代價過大,也可能會選錯索引

十、join操作

join操作的過程是先遍歷表t1,然后根據從表t1取中的數據到表t2中查找滿足條件的記錄,也就是說驅動表是走全表掃描,而被驅動表是走樹查找(index nested-loop join)。那么理所當然選擇小表(過濾后)作為驅動表效果更好

如果被驅動表沒有可用的索引時,join算法會變為表t1和表t2都走全表掃描放入內存中查找滿足符合的記錄(block nested-loop join)

但是如果驅動表分段,那么被驅動表就被多次讀,那么就有可能把大部分熱點數據都淘汰掉了,導致”buffer poll hit rate”命中率低,其他請求需要讀磁盤,這種情況就非常不推薦使用join操作了

接下來來說說常用的left join\right join\inner join,比如這條語句Select * from t1 left join t2,可以看出是以t1作為驅動表,如果不能保證t1比t2表小盡量使用inner join,優化器會自動選擇較好的那個驅動表

十一、group by使用磁盤臨時表

group by語句是統計不同的值出現的個數,但是每個原數據的操作結果可能都是無序的,那么就需要中間存儲表-臨時表記錄過程了。但是如果數據量過大,會出現先放到內存臨時表,插入一部分數據后,發現不夠用了再轉成磁盤臨時表,這種情況我們可以加上sql_big_result提示優化器,直接使用磁盤文件。另外多說一點,如果結果不要求排序,最好使用order by null跳過內存臨時表的排序

十二、分庫分表

查詢表數據大小,合理分表分庫

SELECT CONCAT(table_schema,’.’,table_name) AS ‘Table Name’, CONCAT(ROUND(table_rows/1000000,2),’M’) AS ‘Number of Rows’, CONCAT(ROUND(data_length/(102410241024),2),’G’) AS ‘Data Size’, CONCAT(ROUND(index_length/(102410241024),2),’G’) AS ‘Index Size’ , CONCAT(ROUND((data_length+index_length)/(102410241024),2),’G’) AS’Total’FROM information_schema.TABLES WHERE table_schema LIKE ‘庫名’;

十三、樂觀鎖

樂觀鎖假設數據一般情況下不會沖突,在數據提交更新的時候才會做沖突檢測,常用version版本號的方法實現

select id,version,… from db where id=#{id}; update db set version=version+1,… where id=#{id} and version=#{version};

而相對的悲觀鎖則是在整體數據處理過程中都加鎖,依賴數據庫的事務,性能較差

MySQL select…for update的Row Lock與Table Lock

十四、MySQL中的行鎖

MySQL的InnoDb引擎是行級鎖,需要注意的是,這不是對記錄進行鎖定,而是對索引進行鎖定。在UPDATE\DELETE操作時,MySQL不僅鎖定WHERE條件掃描過的所有索引記錄,而且會鎖定相依的健值,也就是所謂的next-key locking,如語句update liangsonghua_me_blog_post set update_time = now() where id > 10000會鎖定所有主鍵大于等于1000的所有記錄。另外我們知道通過非主鍵查詢回表時,會先通過二級索引也就是非簇索引查找相應的葉子節點,獲得行的主鍵值,然后使用主鍵去聚簇索引中查找數據行。實際上當非簇索引(non-cluster index)記錄被鎖定時,相關的的簇索引記錄也需要被鎖定才能完成相應的操作

十五、數據庫中的自增主鍵ID

InnoDb引擎的表只把自增主鍵的最大ID記錄到內存中,重啟數據庫或者對表進行OPTIMIZE操作,都會使最大ID丟失

MyISAM引擎的表會自增主鍵的最大ID記錄到數據文件里,重啟MYSQL也不會使其丟失

十六、查詢優化之分頁

使用子查詢代替普通的全表掃描然后只取pageSize行

select * from `demo`.`order` where id> (select id from `www_liangsonghua_me`.`order` order by order_no limit 10000, 1)  limit 20;

推薦閱讀

閱讀 459 次
黑龙江6+1开奖结果查询