關於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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類