關於sql

来源:https://www.cnblogs.com/fmhh/archive/2020/07/15/13308615.html
-Advertisement-
Play Games

1. InnoDB支持事務, MyISAM不支持; 2. InnoDB支持外鍵, 而MyISAM不支持; 3. InnoDB是聚集索引,使用B+Tree作為索引結構,數據文件是和(主鍵)索引綁在一起的 MyISAM是非聚集索引, 也是使用B+Tree作為索引結構, 索引和數據文件是分離的, 索引保存 ...


1. InnoDB支持事務, MyISAM不支持; 2. InnoDB支持外鍵, 而MyISAM不支持; 3. InnoDB是聚集索引,使用B+Tree作為索引結構,數據文件是和(主鍵)索引綁在一起的 MyISAM是非聚集索引, 也是使用B+Tree作為索引結構, 索引和數據文件是分離的, 索引保存的是數據文件的指針, 主鍵索引和輔助索引是獨立的 InnoDB的B+樹主鍵索引的葉子節點就是數據文件, 輔助索引的葉子節點是主鍵的值; 而MyISAM的B+樹主鍵索引和輔助索引的葉子節點都是數據文件的地址指針 4. InnoDB支持表、行(預設)級鎖, 而MyISAM支持表級鎖 InnoDB的行鎖是實現在索引上的, 而不是鎖在物理行記錄上. 潛臺詞是, 如果訪問沒有命中索引, 也無法使用行鎖, 將要退化為表鎖 8、InnoDB表必須有主鍵(用戶沒有指定的話會自己找或生產一個主鍵), 而Myisam可以沒有 9、Innodb存儲文件有 .frm. ibd, 而Myisam是 .frm .MYD .MYI Innodb：frm是表定義文件，ibd是數據文件 Myisam：frm是表定義文件，myd是數據文件，myi是索引文件   索引是幫助MySQL高效獲取數據的排好序的數據結構     B-Tree 葉節點具有相同的深度,葉節點的指針為空 所有索引元素不重覆 節點中的數據索引從左到右遞增排列   B+Tree(B-Tree變種) 非葉子節點不存儲data,只存儲索引(冗餘),可以放更多的索引 葉子節點包含所有索引欄位 葉子節點用指針連接,提高區間訪問的性能 MyISAM索引文件和數據文件是分離的(非聚集) InnoDB索引實現(聚集) 表數據文件本身就是按B+Tree組織的一個索引結構文件 聚集索引-葉節點包含了完整的數據記錄     聚集索引 mysql的innodb主鍵索引,如果沒有主鍵索引就是唯一索引 InnoDB聚合索引: 索引欄位在一起存儲到key,按照索引排序排列 innodb聯合索引示例(索引最左首碼原理)   sql執行計劃 explan + sql   id ID可以如果相同認為是同一組,從上往下執行,在所有組中id越大,優先順序越高,越先執行 select_type 查詢類型 1)SIMPLE 簡單查詢,不包括子查詢或UNION 2)PRIMARY 查詢中包含任何複雜的子部分,最外層查詢被標記為 3)SUBQUERY 在select或where里包含了子查詢 4)DERIVED 在from列表中包含了子查詢被標記為DERIVED(衍生),mysql會遞歸執行這些子查詢,把結果放在臨時表 5) UNION 若在第二個select出現在union後,會標記為UNION.若union包含在from子句的查詢中,外層會標記為DERIVED 6)UNION RESULT 從UNION中獲取select table 這一行數據顯示的表,type是null會直接走索引,不會走表,效率最好 type 從最好到最差的順序system > const > eq_ref > ref > range > index > ALL 一般來說最少達到range,最好能達到ref possible_keys 顯示可能應用在這張表中的索引,一個或多個,查詢涉及到的欄位若存在索引,則也列出來,但不一定被查詢實際用到 key 實際使用的索引 ken_len 索引欄位的最大可能長度,並非實際長度,key_len長度越短越好 ref 表示索引的哪一列被使用,也可能是常量 rows 根據表統計信息和索引選用的情況,大致估算出找到所需記錄的讀取行數 Extra 其他的信息 1)Using index: 使用覆蓋索引 2)Using where: 使用 where 語句來處理結果，查詢的列未被索引覆蓋 3)Using index condition: 查詢的列不完全被索引覆蓋, where條件中是一個前導列的範 圍 4)Using temporary: mysql需要創建一張臨時表來處理查詢. 出現這種情況一般是要進行 優化的, 首先是想到用索引來優化 5)Using filesort: 將用外部排序而不是索引排序,數據較小時從記憶體排序,否則需要在磁碟 完成排序. 這種情況下一般也是要考慮使用索引來優化的 6)Select tables optimized away: 使用某些聚合函數(比如 max、min)來訪問存在索引 的某個欄位是   索引失效的情況 1.(複合索引)全值匹配我最愛 2.最佳左首碼法則(帶頭大哥不能死,中間兄弟不能斷) 3.不在索引列上做任何操作(計算,函數,(自動or手動)類型轉換),會導致索引失效而轉向全表掃描 4.存儲引擎不能使用索引中範圍條件右邊的列 5.儘量使用覆蓋索引(只訪問索引的查詢(索引列和查詢列一致)),減少select* 6.mysql在使用不等於(! =或者<>)的時候無法使用索引會導致全表掃描 7.is null,is not nul 也無法使用索引 8.like以通配符開頭(“%abc.…)mysql索引失效會變成全表掃描的操作 9.字元串不加單引號索引失效 10.少用or,用它來連接時會索引失效 全值匹配我最愛(聚合索引)，最左首碼要遵守； 帶頭大哥不能死，中間兄弟不能斷； 索引列上少計算，範圍之後全失效； Like百分寫最右，覆蓋索引不寫星； 不等空值還有or，索引失效要少用； VAR引號不可丟，SQL高級也不難！ 解決like‘%字元串%’時索引不被使用,使用覆蓋索引,即建的索引和查詢的欄位個數順序最好完全一致   sql優化小表驅動大表,非要大表驅動小表用exists mysql支撐Index和FileSort兩種方式排序排序,Index效率高,FileSort效率低 mysql慢查詢是否開啟 > show variables like 'slow_query%'; 開啟慢查詢> set global slow_query_log='ON'; 設置慢查詢時間> set global long_query_time=1; 查看設置後的參數(重新建連或新開回話查看) > show variables like 'long_query_time';   mysql範圍查找要是範圍過大有可能不走索引,同時會出現根據效率考慮走不走索引   trace工具 > set session optimizer_trace="enabled=on",end_markers_in_json=on; -- 開啟trace(以json展示) > select * from employees where name > 'a' order by position; > SELECT * FROM information_schema.OPTIMIZER_TRACE; 查看可能走索引的成本已經索引行數,來判斷具體走的索引   優化總結: 1. MySQL支持兩種方式的排序filesort和index,Using index是指MySQL掃描索引本身完成排序.index 效率高,filesort效率低 2. order by滿足兩種情況會使用Using index 1) order by語句使用索引最左前列 2) 使用where子句與order by子句條件列組合滿足索引最左前列 3. 儘量在索引列上完成排序, 遵循索引建立(索引創建的順序)時的最左首碼法則 4. 如果order by的條件不在索引列上, 就會產生Using filesort 5. 能用覆蓋索引儘量用覆蓋索引 6. group by與order by很類似, 其實質是先排序後分組, 遵照索引創建順序的最左首碼法則.對於group by的優化如果不需要排序的可以加上order by null禁止排序. 註意: where高於having, 能寫在where中 的限定條件就不要去having限定了   filesort文件排序方式 單路排序: 是一次性取出滿足條件行的所有欄位,然後在sort buffer中進行排序;用trace工具可 以看到sort_mode信息里顯示< sort_key, additional_fields >或者< sort_key,packed_additional_fields > 雙路排序(又叫回表排序模式): 是首先根據相應的條件取出相應的排序欄位和可以直接定位行 數據的行 ID,然後在 sort buffer 中進行排序,排序完後需要再次取回其它需要的欄位；用trace工具 可以看到sort_mode信息里顯示< sort_key, rowid >   sql分頁優化 讓查詢儘可能的少,比如覆蓋索引用回表關聯查詢 select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;   mysql表關聯的兩種方式 1. 嵌套迴圈連接(Nested-Loop Join(NLJ)演算法 鏈接欄位是索引 一次一行迴圈地從第一張表(稱為驅動表,一般是小表)中讀取行, 在這行數據中取到關聯欄位, 根據關聯欄位在另一張表(被驅動表,一般是大表)里取出滿足條件的行, 然後取出兩張表的結果合集 2. 基於塊的嵌套迴圈連接 Block Nested-Loop Join(BNL)演算法 鏈接欄位不是索引 把驅動表的數據讀入到 join_buffer 中, 然後掃描被驅動表, 把被驅動表每一行取出來跟 join_buffer 中的數據做對比   對於Join關聯sql的優化 1.關聯欄位加索引, 讓mysql做join操作時儘量選擇嵌套迴圈連接(NLJ)演算法 2.小標驅動大表, 寫多表連接sql時如果明確知道哪張表是小表可以用straight_join寫法固定連接驅動方式, 省去mysql優化器自己判斷的時間   straight_join相當於join類似, 但能讓左邊的表來驅動右邊的表, 能改表優化器對於聯表查詢的執 行順序. 比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表制定mysql選著 t2 表作為驅動表。 straight_join只適用於inner join, 並不適用於left join, right join. (因為left join，right join已經代表指 定了表的執行順序)   in和exsits優化 原則：小表驅動大表，即小的數據集驅動大的數據集 in：當B表的數據集小於A表的數據集時，in優於exists select * from A where id in (select id from B) exists：當A表的數據集小於B表的數據集時，exists優於in 將主查詢A的數據,放到子查詢B中做條件驗證,根據驗證結果(true或false)來決定主查詢的數據是否保留 select * from A where exists (select 1 from B where B.id = A.id)   count count不計算null值 select count(1) from employess; 遍歷二級索引樹,不遍歷索引樹的值 select count(id) from employess; mysql5.7之後走的輔助索引 select count(name) from employess; name不為空 select count(*) from employess; 走輔助索引 count(1) > count(name) == count(*) > count(id)   mysql鎖 手動增加表鎖 > lock table 表名稱 read(write),表名稱2 read(write); 查看表上加過的鎖 > show open tables; 刪除表鎖 > unlock tables; 讀鎖會阻塞寫, 但是不會阻塞讀; 而寫鎖則會把讀和寫都阻塞   行鎖支持事物: 原子性(Atomicity),一致性(Consistent),隔離性(Isolation),持久性(Durable) 併發事務處理帶來的問題 更新丟失, 臟讀(讀其他未提交事物的數據), 不可重讀(修改數據), 幻讀(新增數據) mysql事物級別預設 "不可重覆讀" 常看當前資料庫的事務隔離級別: show variables like 'tx_isolation'; 設置事務隔離級別：set tx_isolation='REPEATABLE-READ'; 可串列化 間隙鎖   InnoDB的行鎖是針對索引加的鎖, 不是針對記錄加的鎖. 並且該索引不能失效, 否則都會從行鎖升級為表鎖   mysql MVVC 為了性能和處理大數據基於快照版本 select * from account(創建了查詢快照, 記錄執行sql這一刻最大的已提交事務id(快照點已提交最大事務id)) 快照基於insert,update,delete     sql -- 新建資料庫 create database `dbname` default character set utf8mb4 collate utf8mb4_unicode_ci; create database `dbname` default character set utf8 collate utf8_general_ci; -- 新建資料庫並授權: grant all privileges on `dbname`.* to 'userName'@'%' identified by 'password'; -- 刷新服務 flush privileges; -- 創建mysql觸發器沒有許可權(log_bin_trust_function_creators 1),root登陸到對應資料庫 set global log_bin_trust_function_creators = 1;   -- 新建資料庫 create database [dbname] default character set utf8 collate utf8_general_ci; -- 新建資料庫並授權: grant all privileges on 'dbname'.* to 'userName'@'%' identified by 'password'; -- 創建用戶 create user 'userName'@'%' identified by 'password'; -- 用戶授權資料庫 grant all privileges on [dbname].* to 'userName'; -- 或 grant select,insert,update,delete,create,drop on [dbname].* to 'userName'; -- 取消用戶所有資料庫(表)的所有許可權 revoke all on *.* from userName; -- 刪除用戶 delete from mysql.user where user='userName'; -- 刪除資料庫 drop database [dbname]; -- 刷新服務 flush privileges; -- 刪除賬戶 drop user hustjhcg@localhost; -- 刷新服務 flush privileges; -- 修改密碼 set password for root=password('123456');     -- 切換資料庫 use mysql; -- 查詢資料庫賬號和許可權 select host,user from user;   //如果為null則改為0 IFNULL( tsmcs.sign_count, 0 ) signCount, ( SELECT count( DISTINCT somo.erp_cust_id )FROM tb_sup_order_main_original somo WHERE somo.supplier_id = sb.supplier_id ) AS custNum,   格式化金額: 四捨五入:CONVERT(sum(od.purchase_num * od.member_price), DECIMAL(10,2)) 千分位:else FORMAT( sod.member_price*sod.purchase_num,2) end as totalPrice,   -- 修改表的創建時間和更新時間欄位 alter table t_users add create_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間'; alter table t_users add update_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新時間';   -- 添加聯合索引 alter table tb_sup_cust add index INDEX_DANW_BRANCHIDY(索引名) (cust_name,branch_id); -- 添加唯一索引 alter table tb_sup_orderaudit_totalprice add unique (supplier_id);   -- 創建臨時表並把已有的表數據添加到臨時表 CREATE TABLE tem_t_users SELECT uid,username,password FROM t_users; -- 查出一張表的欄位插入臨時表 insert into tem_t_users (`uid`,`username`,`password`) values (2,'a',(select password from t_users)); 或 insert into tem_t_users (`uid`,`username`,`password`) (select uid,username,password from t_users); -- 根據查詢臨時表更改已有的表 update out_user as a inner join out_user_copy1 as b on a.tu_id = b.tu_id set a.time_update = a.time_create; explan + sql   id ID可以如果相同認為是同一組,從上往下執行,在所有組中id越大,優先順序越高,越先執行 select_type 查詢類型 1)SIMPLE 簡單查詢,不包括子查詢或UNION 2)PRIMARY 查詢中包含任何複雜的子部分,最外層查詢被標記為 3)SUBQUERY 在select或where里包含了子查詢 4)DERIVED 在from列表中包含了子查詢被標記為DERIVED(衍生),mysql會遞歸執行這些子查詢,把結果放在臨時表 5) UNION 若在第二個select出現在union後,會標記為UNION.若union包含在from子句的查詢中,外層會標記為DERIVED 6)UNION RESULT 從UNION中獲取select table 這一行數據顯示的表 type 從最好到最差的順序system > const > eq_ref > ref > range > index > ALL 一般來說最少達到range,最好能達到ref possible_keys 顯示可能應用在這張表中的索引,一個或多個,查詢涉及到的欄位若存在索引,則也列出來,但不一定被查詢實際用到 key 實際使用的索引 ken_len 索引欄位的最大可能長度,並非實際長度,key_len長度越短越好 ref 表示索引的哪一列被使用,也可能是常量 rows 根據表統計信息和索引選用的情況,大致估算出找到所需記錄的讀取行數 Extra 其他的信息   索引失效的情況 1.(複合索引)全值匹配我最愛 2.最佳左首碼法則(帶頭大哥不能死,中間兄弟不能斷) 3.不在索引列上做任何操作(計算,函數,(自動or手動)類型轉換),會導致索引失效而轉向全表掃描 4.存儲引擎不能使用索引中範圍條件右邊的列 5.儘量使用覆蓋索引(只訪問索引的查詢(索引列和查詢列一致)),減少select* 6.mysql在使用不等於(! =或者<>)的時候無法使用索引會導致全表掃描 7.is null,is not nul 也無法使用索引 8.like以通配符開頭(“%abc.…)mysql索引失效會變成全表掃描的操作 9.字元串不加單引號索引失效 10.少用or,用它來連接時會索引失效 全值匹配我最愛，最左首碼要遵守； 帶頭大哥不能死，中間兄弟不能斷； 索引列上少計算，範圍之後全失效； Like百分寫最右，覆蓋索引不寫星； 不等空值還有or，索引失效要少用； VAR引號不可丟，SQL高級也不難！ 解決like‘%字元串%’時索引不被使用,使用覆蓋索引,即建的索引和查詢的欄位個數順序最 好完全一致

 


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

-Advertisement-
Play Games
更多相關文章
  • DataStream API（一）
    DataStream API（一） 在瞭解DataStream API之前我們先來瞭解一下Flink API的構成。Flink API是分層的。由最底層的Stateful Stream Process到最頂層的SQL分為四層。如下圖： DataStream API 顧名思義，就是DataStream ...
  • java大數據最全課程學習筆記(3)--HDFS 簡介及操作
    目前CSDN,博客園,簡書同步發表中,更多精彩歡迎訪問我的gitee pages HDFS 簡介及操作 HDFS概述 HDFS產出背景及定義 HDFS優缺點 HDFS組成架構 HDFS文件塊大小（重點） 塊在傳輸時，每64K還需要校驗一次，因此塊大小，必須為2的n次方，最接近100M的就是128M！ ...
  • JDBC快速入門
    JDBC快速入門 詳解 １.０DriverManager 功能1 功能2 2.0 connection對象 3.0 statement對象 4.0 ResultSet 遍歷結果集的一個案例 import java.sql.*; public class DQLtest { public static ...
  • MYSQL實現將B表中的欄位值到更新A表中
    根據A表中的id欄位和B表中的id欄位，將B表中name欄位和price欄位值更新到A表中name欄位和price欄位 UPDATE A, BSET A.name = B.name, A.price = B.priceWHERE A.id = B.id ...
  • ORACLE 12C創建wm_concat函數
    0.首先使用dba用戶登錄資料庫，並解鎖wmsys用戶 alter user wmsys account unlock; 1.用WMSYS用戶登錄，不知道密碼可以修改其密碼 alter user wmsys identified by 123456; 2.在wmsys下創建可用的wm_concat函 ...
  • 阿裡：MySQL資料庫規範
    阿裡：MySQL資料庫規範 簡介：基於阿裡資料庫設計規範擴展而來 設計規範 1.【推薦】欄位允許適當冗餘，以提高查詢性能，但必須考慮數據一致。冗餘欄位應遵循: 不是頻繁修改的欄位。 不是 varchar 超長欄位，更不能是 text 欄位。 正例:商品類目名稱使用頻率高，欄位長度短，名稱基本一成不變 ...
  • Navicat 1142 SELECT command denied to user 'sx'@'xxx' for table 'user'
    Navicat 1142 SELECT command denied to user 'sx'@'xxx' for table 'user' 使用Navicat使用sx用戶連接資料庫時或者連接為用戶sx開放的資料庫travel_agency時，Navicat視窗彈出上述問題 ![](D:\博客園\隨 ...
  • MySQL 密碼參數配置與修改 validate_password
    MySQL 密碼參數配置與修改 validate_password 該文章匹配解決MySQL Error中的1819問題 場景 通過root用戶創建travel_agency資料庫，目標是，新建一個用戶然後對僅對該用戶開放travel_agency資料庫而非其他資料庫 過程 創建對所有ip開放的用戶 ...
一周排行
    -Advertisement-
    Play Games
  • C#TMS系統學習（BaseCity頁面）
    C#TMS系統代碼-基礎頁面BaseCity學習 本人純新手，剛進公司跟領導報道，我說我是java全棧，他問我會不會C#，我說大學學過，他說這個TMS系統就給你來管了。外包已經把代碼給我了，這幾天先把增刪改查的代碼背一下，說不定後面就要趕鴨子上架了 Service頁面 //using => impo ...
  • C# - 委托與事件
    委托與事件 委托 委托的定義 委托是C#中的一種類型，用於存儲對方法的引用。它允許將方法作為參數傳遞給其他方法，實現回調、事件處理和動態調用等功能。通俗來講，就是委托包含方法的記憶體地址，方法匹配與委托相同的簽名，因此通過使用正確的參數類型來調用方法。 委托的特性 引用方法：委托允許存儲對方法的引用， ...
  • 解析 ABP vNext 依賴註入實現【屬性註入】的原理
    前言 這幾天閑來沒事看看ABP vNext的文檔和源碼，關於關於依賴註入(屬性註入)這塊兒產生了興趣。 我們都知道。Volo.ABP 依賴註入容器使用了第三方組件Autofac實現的。有三種註入方式，構造函數註入和方法註入和屬性註入。 ABP的屬性註入原則參考如下： 這時候我就開始疑惑了，因為我知道 ...
  • C#TMS系統學習（ShippingNotice頁面）
    C#TMS系統代碼-業務頁面ShippingNotice學習 學一個業務頁面，ok，領導開完會就被裁掉了，很突然啊，他收拾東西的時候我還以為他要旅游提前請假了，還在尋思為什麼回家連自己買的幾箱飲料都要叫跑腿帶走，怕被偷嗎？還好我在他開會之前拿了兩瓶芬達 感覺感覺前面的BaseCity差不太多，這邊的 ...
  • C#動態查詢：巧用Expression組合多條件表達式
    概述：在C#中，通過`Expression`類、`AndAlso`和`OrElse`方法可組合兩個`Expression<Func<T, bool>>`，實現多條件動態查詢。通過創建表達式樹，可輕鬆構建複雜的查詢條件。 在C#中，可以使用AndAlso和OrElse方法組合兩個Expression< ...
  • 在Biwen.QuickApi中整合一個極簡的發佈訂閱(事件匯流排)
    閑來無聊在我的Biwen.QuickApi中實現一下極簡的事件匯流排,其實代碼還是蠻簡單的,對於初學者可能有些幫助 就貼出來,有什麼不足的地方也歡迎板磚交流~ 首先定義一個事件約定的空介面 public interface IEvent{} 然後定義事件訂閱者介面 public interface I ...
  • WPF 穩定的全屏化視窗方法
    1. 案例 成某三甲醫預約系統， 該項目在2024年初進行上線測試，在正常運行了兩天後，業務系統報錯：The connection pool has been exhausted, either raise MaxPoolSize (currently 800) or Timeout (curren ...
  • 在 WPF 中集成 ASP.NET Core 和 WebView2 用於集成 SPA 應用
    背景 我們有些工具在 Web 版中已經有了很好的實踐，而在 WPF 中重新開發也是一種費時費力的操作，那麼直接集成則是最省事省力的方法了。 思路解釋 為什麼要使用 WPF？莫問為什麼，老 C# 開發的堅持，另外因為 Windows 上已經裝了 Webview2/edge 整體打包比 electron ...
  • C#事件
    EDP是一套集組織架構，許可權框架【功能許可權，操作許可權，數據訪問許可權，WebApi許可權】，自動化日誌，動態Interface，WebApi管理等基礎功能於一體的，基於.net的企業應用開發框架。通過友好的編碼方式實現數據行、列許可權的管控。 ...
  • .Net8.0 Blazor Hybird 桌面端 (WPF/Winform) 發佈到 Win7+
    .Net8.0 Blazor Hybird 桌面端 (WPF/Winform) 實測可以完整運行在 win7sp1/win10/win11. 如果用其他工具打包,還可以運行在mac/linux下, 傳送門BlazorHybrid 發佈為無依賴包方式 安裝 WebView2Runtime 1.57 M ...
所有分類