# 常見MySQL問題 **什么影響了數據庫查詢速度** 影響數據庫查詢速度的四個因素 風險分析 QPS： QueriesPerSecond意思是“每秒查詢率”，是一臺服務器每秒能夠相應的查詢次數，是對一個特定的查詢服務器在規定時間內所處理流量多少的衡量標準。 TPS：是 TransactionsPerSecond的縮寫，也就是事務數/秒。它是軟件測試結果的測量單位。客戶機在發送請求時開始計時，收到服務器響應后結束計時，以此來計算使用的時間和完成的事務個數。網站性能測試指標詳解，更多看這篇文章。 Tips：最好不要在主庫上數據庫備份，大型活動前取消這樣的計劃。 1、效率低下的 sql：超高的 QPS與 TPS。 2、大量的并發：數據連接數被占滿（ max\_connection默認 100，一般把連接數設置得大一些）。 并發量 : 同一時刻數據庫服務器處理的請求數量 3、超高的 CPU使用率：CPU資源耗盡出現宕機。 4、磁盤 IO：磁盤 IO性能突然下降、大量消耗磁盤性能的計劃任務。解決：更快磁盤設備、調整計劃任務、做好磁盤維護。 網卡流量：如何避免無法連接數據庫的情況 1、減少從服務器的數量（從服務器會從主服務器復制日志） 2、進行分級緩存（避免前端大量緩存失效） 3、避免使用 select 進行查詢 4、分離業務網絡和服務器網絡 大表帶來的問題（重要） 1.4.1 大表的特點 1、記錄行數巨大，單表超千萬 2、表數據文件巨大，超過 10個 G 大表的危害 1、慢查詢：很難在短時間內過濾出需要的數據 查詢字區分度低 -> 要在大數據量的表中篩選出來其中一部分數據會產生大量的磁盤 io -> 降低磁盤效率 2.對 DDL影響： 建立索引需要很長時間： MySQL-v=5.5 建立索引會造成主從延遲（ mysql建立索引，先在組上執行，再在庫上執行） 修改表結構需要長時間的鎖表：會造成長時間的主從延遲(‘480秒延遲’) 如何處理數據庫上的大表 分庫分表把一張大表分成多個小表 難點： 1、分表主鍵的選擇 2、分表后跨分區數據的查詢和統計 **大事務帶來的問題（重要） 1、 什么是事務** 事務是數據庫系統區別于其他一切文件系統的重要特性之一 事務是一組具有原子性的SQL語句，或是一個獨立的工作單元 事務要求符合：原子性、一致性、隔離性、持久性 2、事務的 ACID屬性 1、原子性（ atomicity)：全部成功，全部回滾失敗。銀行存取款。 2、一致性（consistent)：銀行轉賬的總金額不變。3、隔離性（isolation)： **隔離性等級：** 未提交讀( READ UNCOMMITED) 臟讀,兩個事務之間互相可見；已提交讀( READ COMMITED)符合隔離性的基本概念,一個事務進行時，其它已提交的事物對于該事務是可見的，即可以獲取其它事務提交的數據。可重復讀( REPEATABLE READ) InnoDB的默認隔離等級。事務進行時，其它所有事務對其不可見，即多次執行讀，得到的結果是一樣的！可串行化（ SERIALIZABLE） 在讀取的每一行數據上都加鎖，會造成大量的鎖超時和鎖征用，嚴格數據一致性且沒有并發是可使用。 查看系統的事務隔離級別： show variables like’%iso%’;開啟一個新事務： begin;提交一個事務： commit;修改事物的隔離級別： setsession tx\_isolation=‘read-committed’;推薦：面試問爛的 MySQL 四種隔離級別，看完吊打面試官！關注Java技術棧微信公眾號，在后臺回復關鍵字：mysql，可以獲取更多棧長整理的MySQL技術干貨。 4、持久性( DURABILITY)：從數據庫的角度的持久性，磁盤損壞就不行了 redolog機制保證事務更新的一致性和持久性 **大事務** 運行時間長，操作數據比較多的事務； 風險：鎖定數據太多，回滾時間長，執行時間長。 1、鎖定太多數據，造成大量阻塞和鎖超時； 2、回滾時所需時間比較長，且數據仍然會處于鎖定； 3、如果執行時間長，將造成主從延遲，因為只有當主服務器全部執行完寫入日志時，從服務器才會開始進行同步，造成延遲。關注Java技術棧微信公眾號，在后臺回復關鍵字：mysql，可以獲取更多棧長整理的MySQL技術干貨。 解決思路： 1、避免一次處理太多數據，可以分批次處理； 2、移出不必要的 SELECT操作，保證事務中只有必要的寫操作。 **什么影響了MySQL性能（非常重要）** 2.1 影響性能的幾個方面 1、服務器硬件。 2、服務器系統（系統參數優化）。 3、存儲引擎。MyISAM：不支持事務，表級鎖。InnoDB: 支持事務，支持行級鎖，事務 ACID。 4、數據庫參數配置。 5、 數據庫結構設計和SQL語句。（重點優化） 2.2 MySQL體系結構 分三層：客戶端->服務層->存儲引擎 1、 MySQL是插件式的存儲引擎，其中存儲引擎分很多種。只要實現符合mysql存儲引擎的接口，可以開發自己的存儲引擎! 2、所有跨存儲引擎的功能都是在服務層實現的。 3、MySQL的存儲引擎是針對表的，不是針對庫的。也就是說在一個數據庫中可以使用不同的存儲引擎。但是不建議這樣做。 2.3 InnoDB存儲引擎 MySQL5.5及之后版本默認的存儲引擎：InnoDB。 2.3.1 InnoDB使用表空間進行數據存儲。 show variables like’innodb\_file\_per\_table 如果innodbfileper\_table 為 ON 將建立獨立的表空間，文件為tablename.ibd； 如果innodbfileper\_table 為 OFF 將數據存儲到系統的共享表空間，文件為ibdataX（X為從1開始的整數）； .frm ：是服務器層面產生的文件，類似服務器層的數據字典，記錄表結構。 2.3.2 (MySQL5.5默認)系統表空間與( MySQL5.6及以后默認)獨立表空間 1、系統表空間無法簡單的收縮文件大小，造成空間浪費，并會產生大量的磁盤碎片。 2、獨立表空間可以通過 optimeze table 收縮系統文件，不需要重啟服務器也不會影響對表的正常訪問。 3、如果對多個表進行刷新時，實際上是順序進行的，會產生IO瓶頸。 4、獨立表空間可以同時向多個文件刷新數據。 強烈建立對Innodb 使用獨立表空間，優化什么的更方便，可控。 2.3.3 系統表空間的表轉移到獨立表空間中的方法 1、使用mysqldump 導出所有數據庫數據（存儲過程、觸發器、計劃任務一起都要導出 ）可以在從服務器上操作。 2、停止MYsql 服務器，修改參數（my.cnf加入innodbfileper\_table），并刪除Inoodb相關文件（可以重建Data目錄）。 3、重啟MYSQL，并重建Innodb系統表空間。 4、 重新導入數據。 或者 Altertable 同樣可以的轉移，但是無法回收系統表空間中占用的空間。 2.4 InnoDB存儲引擎的特性 2.4.1 特性一：事務性存儲引擎及兩個特殊日志類型：Redo Log 和 Undo Log 1、 Innodb 是一種事務性存儲引擎。 2、完全支持事務的 ACID特性。3、支持事務所需要的兩個特殊日志類型：RedoLog 和 UndoLog Redo Log：實現事務的持久性(已提交的事務)。Undo Log：未提交的事務，獨立于表空間，需要隨機訪問，可以存儲在高性能io設備上。 Undo日志記錄某數據被修改前的值，可以用來在事務失敗時進行 rollback；Redo日志記錄某數據塊被修改后的值，可以用來恢復未寫入 data file的已成功事務更新的數據。 2.4.2 特性二：支持行級鎖 1、InnoDB支持行級鎖。 2、行級鎖可以最大程度地支持并發。 3、行級鎖是由存儲引擎層實現的。 2.5 什么是鎖 2.5.1 鎖 鎖的主要作用是管理共享資源的并發訪問 鎖用于實現事務的隔離性 2.5.2 鎖類型 **2.5.3 鎖的粒度** MySQL的事務支持不是綁定在MySQL服務器本身， 而是與存儲引擎相關 表級鎖 行級鎖 將table\_name加表級鎖命令：locktable table\_name write; 寫鎖會阻塞其它用戶對該表的‘讀寫’操作，直到寫鎖被釋放：unlock tables； 1、鎖的開銷越大，粒度越小，并發度越高。2、表級鎖通常是在服務器層實現的。3、行級鎖是存儲引擎層實現的。innodb的鎖機制，服務器層是不知道的 2.5.4 阻塞和死鎖 1、阻塞是由于資源不足引起的排隊等待現象。 2、死鎖是由于兩個對象在擁有一份資源的情況下申請另一份資源，而另一份資源恰好又是這兩對象正持有的，導致兩對象無法完成操作，且所持資源無法釋放。 **2.6 如何選擇正確的存儲引擎** 參考條件： 1、事務 2、備份( Innobd免費在線備份) 3、崩潰恢復 4、存儲引擎的特有特性 總結: nnodb大法好。注意: 別使用混合存儲引擎，比如回滾會出問題在線熱備問題。 **2.7 配置參數** 2.7.1 內存配置相關參數 確定可以使用的內存上限。 內存的使用上限不能超過物理內存，否則容易造成內存溢出；（對于32位操作系統，MySQL只能試用3G以下的內存。37 個 MySQL 數據庫小技巧，推薦看一下。關注Java技術棧微信公眾號，在后臺回復關鍵字：mysql，可以獲取更多棧長整理的MySQL技術干貨。 確定MySQL的每個連接 單獨使用的內存。 sort\_buffer\_size 定義了每個線程排序緩存區的大小，MySQL在有查詢、需要做排序操作時才會為每個緩沖區分配內存（直接分配該參數的全部內存）； join\_buffer\_size 定義了每個線程所使用的連接緩沖區的大小，如果一個查詢關聯了多張表，MySQL會為每張表分配一個連接緩沖，導致一個查詢產生了多個連接緩沖； read\_buffer\_size 定義了當對一張MyISAM進行全表掃描時所分配讀緩沖池大小，MySQL有查詢需要時會為其分配內存，其必須是4k的倍數； read\_rnd\_buffer\_size 索引緩沖區大小，MySQL有查詢需要時會為其分配內存，只會分配需要的大小。 注意：以上四個參數是為一個線程分配的，如果有100個連接，那么需要×100。 MySQL數據庫實例： MySQL是單進程多線程（而oracle是多進程），也就是說 MySQL實例在系統上表現就是一個服務進程，即進程； MySQL實例是線程和內存組成，實例才是真正用于操作數據庫文件的； 一般情況下一個實例操作一個或多個數據庫；集群情況下多個實例操作一個或多個數據庫。 如何為緩存池分配內存： Innodb\_buffer\_pool\_size 定義了Innodb所使用緩存池的大小，對其性能十分重要，必須足夠大，但是過大時，使得Innodb 關閉時候需要更多時間把臟頁從緩沖池中刷新到磁盤中； 總內存-（每個線程所需要的內存\*連接數）- 統保留內存 key\_buffer\_size 定義了MyISAM所使用的緩存池的大小，由于數據是依賴存儲操作系統緩存的，所以要為操作系統預留更大的內存空間； select sum(index\_length) from information\_schema.talbes where engine=‘myisam’ 注意：即使開發使用的表全部是Innodb表，也要為MyISAM預留內存，因為MySQL系統使用的表仍然是MyISAM表。 max\_connections 控制允許的最大連接數， 一般2000更大。不要使用外鍵約束保證數據的完整性 **2.8 性能優化順序** 庫結構設計和SQL語句 數據庫存儲引擎的選擇和參數配置 系統選擇及優化 硬件升級 **你目前接觸的mysql版本是什么？除了官方版本，還接觸過其他的mysql分支版本嘛？** 三個流行MySQL分支：Drizzle、MariaDB和Percona Server（包括XtraDB引擎） MariaDB不僅是mysql的替代品，主要還是創新和提高mysql自有技術。 新功能介紹 multi-source replication 多源復制 表的并行復制 galera cluster集群 spider水平分片 tokuDB存儲引擎 XtraDB是innodb存儲引擎的增強版，可用來更好地發揮最新的計算機硬件系統性能，還包含在高性能模式下的新特性。它可以向下兼容，因為它是在innodb基礎上構建，所以他有更多的指標和擴展功能。而且它在cpu多核的條件下，可以更好地使用內存，時數據庫性能提到更高！ Drizzle與mysql的差別就比較大了，并且不能兼容，如果想運行此環境，就需要重寫一些代碼了！ **mysql主要的存儲引擎myisam和innodb的不同之處？** 事務的支持不同（innodb支持事務，myisam不支持事務） 鎖粒度（innodb行鎖應用，myisam表鎖） 存儲空間（innodb既緩存索引文件又緩存數據文件，myisam只能緩存索引文件） 存儲結構 （myisam：數據文件的擴展名為.MYD myData ，索引文件的擴展名是.MYI myIndex） （innodb：所有的表都保存在同一個數據文件里面 即為.Ibd） 5. 統計記錄行數 （myisam：保存有表的總行數，select count(\*) from table;會直接取出出該值） （innodb：沒有保存表的總行數，select count(\*) from table；就會遍歷整個表，消耗相當大） **兩種最長使用的 Innodb 和 Myisam 區別** 1. 事務的支持不同（innodb支持事務，myisam不支持事務） 2. 鎖粒度（innodb行鎖應用，myisam表鎖） 3. 存儲空間（innodb既緩存索引文件又緩存數據文件，myisam只能緩存索引文件） 4. 存儲結構 （myisam：數據文件的擴展名為.MYD myData ，索引文件的擴展名是.MYI myIndex） （innodb：所有的表都保存在同一個數據文件里面 即為.Ibd） 5. 統計記錄行數 （myisam：保存有表的總行數，select count(*) from table;會直接取出出該值） （innodb：沒有保存表的總行數，select count(*) from table；就會遍歷整個表，消耗相當大） **Innodb的體系結構簡單介紹一下？** 談及到innodb的體系結構，首先要考慮mysql的體系結構，分為兩部分mysql的server層和存儲引擎層 先要跟面試官聊清楚mysql的整體方向，然后再去涉及innodb體系結構 建議從三方面介紹innodb體系結構：內存----線程-----磁盤 內存中包含insert\_buffer,data\_buffer,index\_buffer,redo\_log\_buffer,double\_write 內存刷新到磁盤的機制，redo，臟頁，binlog的刷新條件 各種線程的作用，master\_thread,purge\_thread,redo log thread,read thread,write thread,page cleaner thread 磁盤中存放著數據文件，redo log，undo log，binlog **mysql有哪些索引類型：** 數據結構角度上可以分：B+tree索引，hash索引，fulltext索引（innodb，myisam都支持） 存儲角度上可以分：聚集索引，非聚集索引 邏輯角度上可以分：primary key，normal key，單列，復合，覆蓋索引 mysql binlog有幾種格式： 1. statement 優點：不需要記錄每一行的變化，減少了binlog日志量，節約了IO，提高性能 缺點：當使用一些特殊函數的時候，或者跨庫操作的時候容易丟失數據 注：在生產中不建議使用 2. row 優點：清晰記錄每行的數據信息，不會出現跨庫丟數據的情況 缺點：內容當記錄到日志中的時候，都將以每行記錄的修改來記錄，但就會產生大量的binlog，對于網絡開銷也比較大 注：生產中推薦使用 3. mixed 是mysql5.1的時候，一個過渡版本，DDL語句會記錄成statement，DML會記錄row。 注：生產中不建議使用 **mysql主從復制的具體原理 是什么？** 主服務器把數據更新記錄到二進制日志中，從服務器通過io thread向主庫發起binlog請求，主服務器通過IO dump thread把二進制日志傳遞給從庫，從庫通過io thread記錄到自己的中繼日志中。然后再通過sql thread應用中繼日志中sql的內容。 **數據庫中雙一是什么？** sync\_binlog=1 innodb\_flush\_log\_at\_trx\_commit=1 innodb\_flush\_log\_at\_trx\_commit和sync\_binlog 兩個參數是控制MySQL 磁盤寫入策略以及數據安全性的關鍵參數 innodb\_flush\_log\_at\_trx\_commit設置為1，每次事務提交時MySQL都會把log buffer的數據寫入log file，并且刷到磁盤中去。 sync\_binlog =N (N>0) ，MySQL 在每寫 N次 二進制日志binary log時，會使用fdatasync()函數將它的寫二進制日志binary log同步到磁盤中去 **如何監控mysql replication復制延遲？** 可以通過第三方工具 業界中的瑞士×××percona-toolkit中的命令，pt-heartbeat進行主從延遲監控。 傳統方法，通過比較主從服務器之間的position號的差異值。 還可以通過查看seconds\_behind\_master估算一下主從延遲時間 **大表DDL語句，如何實施，才能把性能影響降到最低？** 可以通過傳統方法導入導出數據，新建一張與原表一樣的表結構，把需要執行的ddl語句在無數據的新表執行，然后把老表中的數據導入到新表中，把新表改成老表的名字 通過第三方工具 業界中的瑞士×××percona-toolkit中的命令，pt-online-schema-change進行在線操作 對于新版本的mysql（5.7）可以直接在線online ddl **為什么要為innodb表設置自增列做主鍵？** 1.使用自增列做主鍵，寫入順序是自增的，和B+數葉子節點分裂順序一致 2.表不指定自增列做主鍵，同時也沒有可以被選為主鍵的唯一索引，InnoDB就會選擇內置的rowid作為主鍵，寫入順序和rowid增長順序一致 所以InnoDB表的數據寫入順序能和B+樹索引的葉子節點順序一致的話，這時候存取效率是最高 **如何優化一條有問題的sql語句？** 針對sql語句的優化，我們不要上來就回答添加索引，這樣顯得太不專業。我們可以從如下幾個角度去分析 回歸到表的設計層面，數據類型選擇是否合理 大表碎片的整理是否完善 表的統計信息，是不是準確的 審查表的執行計劃，判斷字段上面有沒有合適的索引 針對索引的選擇性，建立合適的索引（就又涉及到大表DDL的操作問題） **服務器負載過高或者網頁打開緩慢，簡單說說你的優化思路 ？** 首先我們要發現問題的過程，通過操作系統，數據庫，程序設計，硬件角度四個維度找到問題所在 找到瓶頸點的位置 制定好優化方案，形成處理問題的體系 體系制定好之后，在測試環境進行優化方案的測試 測試環境如果優化效果很好，再實施到生產環境 做好處理問題的記錄 **接觸過哪些mysql的主流架構？架構應用中有哪些問題需要考慮？** M-S MHA MM keepalived PXC 共同存在的問題：主從延遲問題的存在，在主庫宕機，切換過程中要考慮數據一致性的問題，避免出現主從復制不一致 **什么是死鎖？鎖等待？如何優化這類問題？通過數據庫哪些表可以監控？** 死鎖是指兩個或多個事務在同一資源上互相占用，并請求加鎖時，而導致的惡性循環現象。當多個事務以不同順序試圖加鎖同一資源時，就會產生死鎖。 鎖等待：mysql數據庫中，不同session在更新同行數據中，會出現鎖等待 重要的三張鎖的監控表innodb\_trx，innodb\_locks，innodb\_lock\_waits **處理過mysql哪些案例** 我們可以簡單從mysql四個知識模塊跟他聊聊mysql體系結構，數據備份恢復，優化，高可用集群架構 **mysql版本的升級** 處理mysql集群的各種坑和問題 根據公司業務類型，設計合理mysql庫，表，架構。 定期進行災備恢復演練 誤刪除數據之后，恢復數據 簡單先從這幾個方向說說，每個問題再展開分析。 當然還會有一些人事上面的問題，例如為啥選我們的公司，你覺得你自己的優勢是什么？你期望的薪資大概是多少？這些問題，就很簡單了。我們只要過了技術面試，這些都不是啥問題了！ **MySQL 中的 latin1 是什么字符集？** 這個字符集相信大家都見過，一般在創建數據庫的時候會進行設置。它在 Java 中代表的就是 ISO-8859-1。共收錄256個字符，是在ASCII 字符集的基礎上又擴充了 128 個西歐常用字符(包括德法兩國的字母)，也可以使用 1 個字節來進行編碼。ISO-8859-1 這個字符集在 MySQL 中的別名就是 latin1。 **為什么我們通常推薦使用 utf8mb4 字符集？** 比如當我們存儲 emoji 表情時，會出現無法存入的問題。但是沒有人從原理上來說為什么會丟失。這個我們可以通過 SHOW CHARSET like ‘utf8%’; 命令查看一下 utf8 和 utf8mb4 的區別。 之所以無法存儲的原因就是，UTF-8 編碼有可能是兩個、三個、四個字節。Emoji 表情是 4 個字節，而 Mysql 的 utf8 編碼最多 3 個字節，所以數據插不進去。 **表 xttblog 存在且該表中不存在 name 字段，那么執行 select \* from xttblog where name = ‘業余草’ 肯定會報錯，請問是在連接器，分析器，優化器，執行器等哪個階段報錯？** 答案是分析器。因為，連接器是負責處理管理連接，權限驗證的；分析器是進行詞法分析，語法分析的；優化器是進行語句優化，生成執行計劃，選擇索引的；執行器是真正執行 SQL 語句的，并返回結果集的。所以，回答分析器才是對的。 **MySQL 5.8 中為什么把查詢緩存這一塊移除了？** 這個查詢緩存，這一塊估計很多人都沒注意到。新版本的 5.8 版本的 MySQL 數據庫已經移除了查詢緩存這一塊的設計。而且在 5.7 版本中也不推薦使用了。移除的原因是，雖然查詢緩存有時候能比較快的返回數據，但是維護起來太麻煩了。而且緩存命中率太低了。如果對應的表有 insert、update、delete 等，那么緩存就得失效。如果查詢語句中有函數，則放棄查詢緩存。因為函數會設計的計算等有太多的不確定性。還有一些函數根本不能緩存，或沒必要緩存。比如，select now() 就不能緩存，再比如，select version() 就沒必要緩存。綜合考慮，MySQL 把它給移除了。 **1\. 如何設計一個高并發的系統** ① 數據庫的優化，包括合理的事務隔離級別、SQL語句優化、索引的優化 ② 使用緩存，盡量減少數據庫 IO ③ 分布式數據庫、分布式緩存 ④ 服務器的負載均衡 **2\. 鎖的優化策略** ① 讀寫分離 ② 分段加鎖 ③ 減少鎖持有的時間 ④ 多個線程盡量以相同的順序去獲取資源 等等，這些都不是絕對原則，都要根據情況，比如不能將鎖的粒度過于細化，不然可能會出現線程的加鎖和釋放次數過多，反而效率不如一次加一把大鎖。這部分跟面試官談了很久 **3\. 索引的底層實現原理和優化** B+樹，經過優化的B+樹 主要是在所有的葉子結點中增加了指向下一個葉子節點的指針，因此InnoDB建議為大部分表使用默認自增的主鍵作為主索引。 **4\. 什么情況下設置了索引但無法使用** ① 以“%”開頭的LIKE語句，模糊匹配 ② OR語句前后沒有同時使用索引 ③ 數據類型出現隱式轉化（如varchar不加單引號的話可能會自動轉換為int型） **5\. SQL語句的優化** order by要怎么處理 alter盡量將多次合并為一次 insert和delete也需要合并 **6\. 實踐中如何優化MySQL** 我當時是按以下四條依次回答的，他們四條從效果上第一條影響最大，后面越來越小。 ① SQL語句及索引的優化 ② 數據庫表結構的優化 ③ 系統配置的優化 ④ 硬件的優化 **8\. sql注入的主要特點** 變種極多，攻擊簡單，危害極大 **9\. sql注入的主要危害** 未經授權操作數據庫的數據 惡意纂改網頁 私自添加系統賬號或者是數據庫使用者賬號 網頁掛木馬 **10、優化數據庫的方法** · 選取最適用的字段屬性，盡可能減少定義字段寬度，盡量把字段設置NOTNULL，例如’省份’、'性別’最好適用ENUM · 使用連接(JOIN)來代替子查詢 · 適用聯合(UNION)來代替手動創建的臨時表 · 事務處理 · 鎖定表、優化事務處理 · 適用外鍵，優化鎖定表 · 建立索引 · 優化查詢語句 **11\. 簡單描述mysql中，索引，主鍵，唯一索引，聯合索引的區別，對數據庫的性能有什么影響（從讀寫兩方面）** 索引是一種特殊的文件(InnoDB數據表上的索引是表空間的一個組成部分)，它們包含著對數據表里所有記錄的引用指針。 普通索引(由關鍵字KEY或INDEX定義的索引)的唯一任務是加快對數據的訪問速度。 普通索引允許被索引的數據列包含重復的值。如果能確定某個數據列將只包含彼此各不相同的值，在為這個數據列創建索引的時候就應該用關鍵字UNIQUE把它定義為一個唯一索引。也就是說，唯一索引可以保證數據記錄的唯一性。 主鍵，是一種特殊的唯一索引，在一張表中只能定義一個主鍵索引，主鍵用于唯一標識一條記錄，使用關鍵字 PRIMARY KEY 來創建。 索引可以覆蓋多個數據列，如像INDEX(columnA, columnB)索引，這就是聯合索引。 索引可以極大的提高數據的查詢速度，但是會降低插入、刪除、更新表的速度，因為在執行這些寫操作時，還要操作索引文件。 **12.數據庫中的事務是什么?** 事務（transaction）是作為一個單元的一組有序的數據庫操作。如果組中的所有操作都成功，則認為事務成功，即使只有一個操作失敗，事務也不成功。如果所有操作完成，事務則提交，其修改將作用于所有其他數據庫進程。如果一個操作失敗，則事務將回滾，該事務所有操作的影響都將取消。ACID 四大特性,原子性、隔離性、一致性、持久性。 **13.了解XSS攻擊嗎？如何防止？** XSS是跨站腳本攻擊，首先是利用跨站腳本漏洞以一個特權模式去執行攻擊者構造的腳本，然后利用不安全的Activex控件執行惡意的行為。 使用htmlspecialchars()函數對提交的內容進行過濾，使字符串里面的特殊符號實體化。 14.SQL注入漏洞產生的原因？如何防止？ SQL注入產生的原因：程序開發過程中不注意規范書寫sql語句和對特殊字符進行過濾，導致客戶端可以通過全局變量POST和GET提交一些sql語句正常執行。 防止SQL注入的方式： 開啟配置文件中的magic\_quotes\_gpc 和 magic\_quotes\_runtime設置 執行sql語句時使用addslashes進行sql語句轉換 Sql語句書寫盡量不要省略雙引號和單引號。 過濾掉sql語句中的一些關鍵詞：update、insert、delete、select、 \* 。 提高數據庫表和字段的命名技巧，對一些重要的字段根據程序的特點命名，取不易被猜到的。 Php配置文件中設置register\_globals為off,關閉全局變量注冊 控制錯誤信息，不要在瀏覽器上輸出錯誤信息，將錯誤信息寫到日志文件中。 **15、 對于關系型數據庫而言，索引是相當重要的概念，請回答有關索引的幾個問題：** a)、索引的目的是什么？ 快速訪問數據表中的特定信息，提高檢索速度 創建唯一性索引，保證數據庫表中每一行數據的唯一性。 加速表和表之間的連接 使用分組和排序子句進行數據檢索時，可以顯著減少查詢中分組和排序的時間 b)、索引對數據庫系統的負面影響是什么？ 負面影響： 創建索引和維護索引需要耗費時間，這個時間隨著數據量的增加而增加；索引需要占用物理空間，不光是表需要占用數據空間，每個索引也需要占用物理空間；當對表進行增、刪、改、的時候索引也要動態維護，這樣就降低了數據的維護速度。 c)、為數據表建立索引的原則有哪些？ 在最頻繁使用的、用以縮小查詢范圍的字段上建立索引。 在頻繁使用的、需要排序的字段上建立索引 d)、 什么情況下不宜建立索引？ 對于查詢中很少涉及的列或者重復值比較多的列，不宜建立索引。 對于一些特殊的數據類型，不宜建立索引，比如文本字段（text）等 **16、 簡述在MySQL數據庫中MyISAM和InnoDB的區別** 區別于其他數據庫的最重要的特點就是其插件式的表存儲引擎。切記：存儲引擎是基于表的，而不是數據庫。 **InnoDB與MyISAM的區別：** InnoDB存儲引擎: 主要面向OLTP(Online Transaction Processing，在線事務處理)方面的應用，是第一個完整支持ACID事務的存儲引擎(BDB第一個支持事務的存儲引擎，已經停止開發)。 特點： · 行鎖設計、支持外鍵,支持事務，支持并發，鎖粒度是支持mvcc得行級鎖； MyISAM存儲引擎: 是MySQL官方提供的存儲引擎，主要面向OLAP(Online Analytical Processing,在線分析處理)方面的應用。 特點： 不支持事務，鎖粒度是支持并發插入得表級鎖，支持表所和全文索引。操作速度快，不能讀寫操作太頻繁； **17、 解釋MySQL外連接、內連接與自連接的區別** 先說什么是交叉連接: 交叉連接又叫笛卡爾積，它是指不使用任何條件，直接將一個表的所有記錄和另一個表中的所有記錄一一匹配。 內連接 則是只有條件的交叉連接，根據某個條件篩選出符合條件的記錄，不符合條件的記錄不會出現在結果集中，即內連接只連接匹配的行。 外連接 其結果集中不僅包含符合連接條件的行，而且還會包括左表、右表或兩個表中的所有數據行，這三種情況依次稱之為左外連接，右外連接，和全外連接。 左外連接，也稱左連接，左表為主表，左表中的所有記錄都會出現在結果集中，對于那些在右表中并沒有匹配的記錄，仍然要顯示，右邊對應的那些字段值以NULL來填充。 右外連接，也稱右連接，右表為主表，右表中的所有記錄都會出現在結果集中。左連接和右連接可以互換，MySQL目前還不支持全外連接。 **18、 寫出三種以上MySQL數據庫存儲引擎的名稱（提示：不區分大小寫）** MyISAM、InnoDB、BDB（BerkeleyDB）、Merge、Memory（Heap）、Example、Federated、 Archive、CSV、Blackhole、MaxDB 等等十幾個引擎 **19、Myql中的事務回滾機制概述** 事務是用戶定義的一個數據庫操作序列，這些操作要么全做要么全不做，是一個不可分割的工作單位，事務回滾是指將該事務已經完成的對數據庫的更新操作撤銷。 要同時修改數據庫中兩個不同表時，如果它們不是一個事務的話，當第一個表修改完，可能第二個表修改過程中出現了異常而沒能修改，此時就只有第二個表依舊是未修改之前的狀態，而第一個表已經被修改完畢。而當你把它們設定為一個事務的時候，當第一個表修改完，第二表修改出現異常而沒能修改，第一個表和第二個表都要回到未修改的狀態，這就是所謂的事務回滾 **20\. SQL語言包括哪幾部分？每部分都有哪些操作關鍵字？** 答：SQL語言包括數據定義(DDL)、數據操縱(DML),數據控制(DCL)和數據查詢（DQL）四個部分。 數據定義：Create Table,Alter Table,Drop Table, Craete/Drop Index等 數據操縱：Select ,insert,update,delete, 數據控制：grant,revoke 數據查詢：select **21\. 完整性約束包括哪些？** 答：數據完整性(Data Integrity)是指數據的精確(Accuracy)和可靠性(Reliability)。 分為以下四類： 1. 實體完整性：規定表的每一行在表中是惟一的實體。 2. 域完整性：是指表中的列必須滿足某種特定的數據類型約束，其中約束又包括取值范圍、精度等規定。 3. 參照完整性：是指兩個表的主關鍵字和外關鍵字的數據應一致，保證了表之間的數據的一致性，防止了數據丟失或無意義的數據在數據庫中擴散。 4. 用戶定義的完整性：不同的關系數據庫系統根據其應用環境的不同，往往還需要一些特殊的約束條件。用戶定義的完整性即是針對某個特定關系數據庫的約束條件，它反映某一具體應用必須滿足的語義要求。 與表有關的約束：包括列約束(NOT NULL（非空約束）)和表約束(PRIMARY KEY、foreign key、check、UNIQUE) 。 **22\. 什么是事務？及其特性？** 答：事務：是一系列的數據庫操作，是數據庫應用的基本邏輯單位。 事務特性： （1）原子性：即不可分割性，事務要么全部被執行，要么就全部不被執行。 （2）一致性或可串性。事務的執行使得數據庫從一種正確狀態轉換成另一種正確狀態 （3）隔離性。在事務正確提交之前，不允許把該事務對數據的任何改變提供給任何其他事務， （4） 持久性。事務正確提交后，其結果將永久保存在數據庫中，即使在事務提交后有了其他故障，事務的處理結果也會得到保存。 或者這樣理解： 事務就是被綁定在一起作為一個邏輯工作單元的SQL語句分組，如果任何一個語句操作失敗那么整個操作就被失敗，以后操作就會回滾到操作前狀態，或者是上有個節點。為了確保要么執行，要么不執行，就可以使用事務。要將有組語句作為事務考慮，就需要通過ACID測試，即原子性，一致性，隔離性和持久性。 **23\. 什么是鎖？** 答：數據庫是一個多用戶使用的共享資源。當多個用戶并發地存取數據時，在數據庫中就會產生多個事務同時存取同一數據的情況。若對并發操作不加控制就可能會讀取和存儲不正確的數據，破壞數據庫的一致性。 ~~~ 加鎖是實現數據庫并發控制的一個非常重要的技術。當事務在對某個數據對象進行操作前，先向系統發出請求，對其加鎖。加鎖后事務就對該數據對象有了一定的控制，在該事務釋放鎖之前，其他的事務不能對此數據對象進行更新操作。 ~~~ 基本鎖類型：鎖包括行級鎖和表級鎖 **24\. 什么叫視圖？游標是什么？** 答：視圖是一種虛擬的表，具有和物理表相同的功能。可以對視圖進行增，改，查，操作，視圖通常是有一個表或者多個表的行或列的子集。對視圖的修改不影響基本表。它使得我們獲取數據更容易，相比多表查詢。 游標：是對查詢出來的結果集作為一個單元來有效的處理。游標可以定在該單元中的特定行，從結果集的當前行檢索一行或多行。可以對結果集當前行做修改。一般不使用游標，但是需要逐條處理數據的時候，游標顯得十分重要。 **25\. 什么是存儲過程？用什么來調用？** 答：存儲過程是一個預編譯的SQL語句，優點是允許模塊化的設計，就是說只需創建一次，以后在該程序中就可以調用多次。如果某次操作需要執行多次SQL，使用存儲過程比單純SQL語句執行要快。可以用一個命令對象來調用存儲過程。 **26\. 索引的作用？和它的優點缺點是什么？** 答：索引就一種特殊的查詢表，數據庫的搜索引擎可以利用它加速對數據的檢索。它很類似與現實生活中書的目錄，不需要查詢整本書內容就可以找到想要的數據。索引可以是唯一的，創建索引允許指定單個列或者是多個列。缺點是它減慢了數據錄入的速度，同時也增加了數據庫的尺寸大小。 **27\. 如何通俗地理解三個范式？** 答：第一范式：1NF是對屬性的原子性約束，要求屬性具有原子性，不可再分解； 第二范式：2NF是對記錄的惟一性約束，要求記錄有惟一標識，即實體的惟一性； 第三范式：3NF是對字段冗余性的約束，即任何字段不能由其他字段派生出來，它要求字段沒有冗余。。 范式化設計優缺點: 優點: 可以盡量得減少數據冗余，使得更新快，體積小 缺點: 對于查詢需要多個表進行關聯，減少寫得效率增加讀得效率，更難進行索引優化 反范式化: 優點:可以減少表得關聯，可以更好得進行索引優化 缺點:數據冗余以及數據異常，數據得修改需要更多的成本 **28\. 什么是基本表？什么是視圖？** 答：基本表是本身獨立存在的表，在 SQL 中一個關系就對應一個表。 視圖是從一個或幾個基本表導出的表。視圖本身不獨立存儲在數據庫中，是一個虛表 **29\. 試述視圖的優點？** 答：(1) 視圖能夠簡化用戶的操作 (2) 視圖使用戶能以多種角度看待同一數據； (3) 視圖為數據庫提供了一定程度的邏輯獨立性； (4) 視圖能夠對機密數據提供安全保護。 **30\. NULL是什么意思** 答：NULL這個值表示UNKNOWN(未知):它不表示“”(空字符串)。對NULL這個值的任何比較都會生產一個NULL值。您不能把任何值與一個 NULL值進行比較，并在邏輯上希望獲得一個答案。 使用IS NULL來進行NULL判斷 **31\. 主鍵、外鍵和索引的區別？** 主鍵、外鍵和索引的區別 定義： 主鍵–唯一標識一條記錄，不能有重復的，不允許為空 外鍵–表的外鍵是另一表的主鍵, 外鍵可以有重復的, 可以是空值 索引–該字段沒有重復值，但可以有一個空值 作用： 主鍵–用來保證數據完整性 外鍵–用來和其他表建立聯系用的 索引–是提高查詢排序的速度 個數： 主鍵–主鍵只能有一個 外鍵–一個表可以有多個外鍵 索引–一個表可以有多個唯一索引 **32\. 你可以用什么來確保表格里的字段只接受特定范圍里的值?** 答：Check限制，它在數據庫表格里被定義，用來限制輸入該列的值。 觸發器也可以被用來限制數據庫表格里的字段能夠接受的值，但是這種辦法要求觸發器在表格里被定義，這可能會在某些情況下影響到性能。 **33\. 說說對SQL語句優化有哪些方法？（選擇幾條）** （1）Where子句中：where表之間的連接必須寫在其他Where條件之前，那些可以過濾掉最大數量記錄的條件必須寫在Where子句的末尾.HAVING最后。 （2）用EXISTS替代IN、用NOT EXISTS替代NOT IN。 （3） 避免在索引列上使用計算 （4）避免在索引列上使用IS NULL和IS NOT NULL （5）對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。 （6）應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描 （7）應盡量避免在 where 子句中對字段進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描 **34\. SQL語句中‘相關子查詢’與‘非相關子查詢’有什么區別？** 答：子查詢：嵌套在其他查詢中的查詢稱之。 子查詢又稱內部，而包含子查詢的語句稱之外部查詢（又稱主查詢）。 所有的子查詢可以分為兩類，即相關子查詢和非相關子查詢 （1）非相關子查詢是獨立于外部查詢的子查詢，子查詢總共執行一次，執行完畢后將值傳遞給外部查詢。 （2）相關子查詢的執行依賴于外部查詢的數據，外部查詢執行一行，子查詢就執行一次。 故非相關子查詢比相關子查詢效率高 **35\. char和varchar的區別？** 答：是一種固定長度的類型，varchar則是一種可變長度的類型，它們的區別是： char(M)類型的數據列里，每個值都占用M個字節，如果某個長度小于M，MySQL就會在它的右邊用空格字符補足．（在檢索操作中那些填補出來的空格字符將被去掉）在varchar(M)類型的數據列里，每個值只占用剛好夠用的字節再加上一個用來記錄其長度的字節（即總長度為L+1字節）． varchar的適用場景: 字符串列的最大長度比平均長度大很多 字符串很少被更新，容易產生存儲碎片 使用多字節字符集存儲字符串 Char的場景: ~~~ 存儲具有近似得長度（md5值,身份證，手機號）,長度比較短小得字符串（因為varchar需要額外空間記錄字符串長度），更適合經常更新得字符串，更新時不會出現頁分裂得情況，避免出現存儲碎片，獲得更好的io性能 ~~~ **36\. Mysql 的存儲引擎,myisam和innodb的區別。** 答：簡單的表達： MyISAM 是非事務的存儲引擎；適合用于頻繁查詢的應用；表鎖，不會出現死鎖；適合小數據，小并發 innodb是支持事務的存儲引擎；合于插入和更新操作比較多的應用；設計合理的話是行鎖（最大區別就在鎖的級別上）；適合大數據，大并發。 **37\. 數據表類型有哪些** 答：MyISAM、InnoDB、HEAP、BOB,ARCHIVE,CSV等。 ~~~ MyISAM：成熟、穩定、易于管理，快速讀取。一些功能不支持（事務等），表級鎖。 InnoDB：支持事務、外鍵等特性、數據行鎖定。空間占用大，不支持全文索引等。 ~~~ **38\. MySQL數據庫作發布系統的存儲，一天五萬條以上的增量，預計運維三年,怎么優化？** a. 設計良好的數據庫結構，允許部分數據冗余，盡量避免join查詢，提高效率。 b. 選擇合適的表字段數據類型和存儲引擎，適當的添加索引。 c. mysql庫主從讀寫分離。 d. 找規律分表，減少單表中的數據量提高查詢速度。 e。添加緩存機制，比如memcached，apc等。 f. 不經常改動的頁面，生成靜態頁面。 g. 書寫高效率的SQL。比如 SELECT \* FROM TABEL 改為 SELECT field\_1, field\_2, field\_3 FROM TABLE. **39\. 對于大流量的網站,您采用什么樣的方法來解決各頁面訪問量統計問題？** 答：a. 確認服務器是否能支撐當前訪問量。 b. 優化數據庫訪問。 c. 禁止外部訪問鏈接（盜鏈）, 比如圖片盜鏈。 d. 控制文件下載。 e. 使用不同主機分流。 f. 使用瀏覽統計軟件，了解訪問量，有針對性的進行優化。 **40、如何進行SQL優化？（關于后邊的解釋同學們可以進行理解，到時根據自己的理解把大體意思說出來即可）** （1）選擇正確的存儲引擎 以 MySQL為例，包括有兩個存儲引擎 MyISAM 和 InnoDB，每個引擎都有利有弊。 MyISAM 適合于一些需要大量查詢的應用，但其對于有大量寫操作并不是很好。甚至你只是需要update一個字段，整個表都會被鎖起來，而別的進程，就算是讀進程都無法操作直到讀操作完成。另外，MyISAM 對于 SELECT COUNT(\*) 這類的計算是超快無比的。 InnoDB 的趨勢會是一個非常復雜的存儲引擎，對于一些小的應用，它會比 MyISAM 還慢。但是它支持“行鎖” ，于是在寫操作比較多的時候，會更優秀。并且，他還支持更多的高級應用，比如：事務。 （2）優化字段的數據類型 記住一個原則，越小的列會越快。如果一個表只會有幾列罷了（比如說字典表，配置表），那么，我們就沒有理由使用 INT 來做主鍵，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 會更經濟一些。如果你不需要記錄時間，使用 DATE 要比 DATETIME 好得多。當然，你也需要留夠足夠的擴展空間。 （3）為搜索字段添加索引 索引并不一定就是給主鍵或是唯一的字段。如果在你的表中，有某個字段你總要會經常用來做搜索，那么最好是為其建立索引，除非你要搜索的字段是大的文本字段，那應該建立全文索引。 (4)避免使用Select*從數據庫里讀出越多的數據，那么查詢就會變得越慢。并且，如果你的數據庫服務器和WEB服務器是兩臺獨立的服務器的話，這還會增加網絡傳輸的負載。即使你要查詢數據表的所有字段，也盡量不要用*通配符，善用內置提供的字段排除定義也許能給帶來更多的便利。 (5)使用 ENUM 而不是 VARCHAR ENUM 類型是非常快和緊湊的。在實際上，其保存的是 TINYINT，但其外表上顯示為字符串。這樣一來，用這個字段來做一些選項列表變得相當的完美。例如，性別、民族、部門和狀態之類的這些字段的取值是有限而且固定的，那么，你應該使用 ENUM 而不是 VARCHAR。 (6)盡可能的使用 NOT NULL 除非你有一個很特別的原因去使用 NULL 值，你應該總是讓你的字段保持 NOT NULL。 NULL其實需要額外的空間，并且，在你進行比較的時候，你的程序會更復雜。 當然，這里并不是說你就不能使用NULL了，現實情況是很復雜的，依然會有些情況下，你需要使用NULL值。 (7)固定長度的表會更快 如果表中的所有字段都是“固定長度”的，整個表會被認為是 “static” 或 “fixed-length”。 例如，表中沒有如下類型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一個這些字段，那么這個表就不是“固定長度靜態表”了，這樣，MySQL 引擎會用另一種方法來處理。 固定長度的表會提高性能，因為MySQL搜尋得會更快一些，因為這些固定的長度是很容易計算下一個數據的偏移量的，所以讀取的自然也會很快。而如果字段不是定長的，那么，每一次要找下一條的話，需要程序找到主鍵。并且，固定長度的表也更容易被緩存和重建。不過，唯一的副作用是，固定長度的字段會浪費一些空間，因為定長的字段無論你用不用，他都是要分配那么多的空間。 **41，為表中得字段選擇合適得數據類型（物理設計）** 字段類型優先級: 整形>date,time>enum,char>varchar>blob,text 優先考慮數字類型，其次是日期或者二進制類型，最后是字符串類型，同級別得數據類型，應該優先選擇占用空間小的數據類型 **42:存儲時期** Datatime:以 YYYY-MM-DD HH:MM:SS 格式存儲時期時間，精確到秒，占用8個字節得存儲空間，datatime類型與時區無關 Timestamp:以時間戳格式存儲，占用4個字節，范圍小1970-1-1到2038-1-19，顯示依賴于所指定得時區，默認在第一個列行的數據修改時可以自動得修改timestamp列得值 Date:（生日）占用得字節數比使用字符串.datatime.int儲存要少，使用date只需要3個字節，存儲日期月份，還可以利用日期時間函數進行日期間得計算 Time:存儲時間部分得數據 注意:不要使用字符串類型來存儲日期時間數據（通常比字符串占用得儲存空間小，在進行查找過濾可以利用日期得函數） 使用int存儲日期時間不如使用timestamp類型 **問題1：char、varchar的區別是什么？** varchar是變長而char的長度是固定的。如果你的內容是固定大小的，你會得到更好的性能。 **問題2: TRUNCATE和DELETE的區別是什么？** DELETE命令從一個表中刪除某一行，或多行，TRUNCATE命令永久地從表中刪除每一行。 **問題3：什么是觸發器，MySQL中都有哪些觸發器？** 觸發器是指一段代碼，當觸發某個事件時，自動執行這些代碼。在MySQL數據庫中有如下六種觸發器： 1、Before Insert 2、After Insert 3、Before Update 4、After Update 5、Before Delete 6、After Delete **問題4：FLOAT和DOUBLE的區別是什么？** FLOAT類型數據可以存儲至多8位十進制數，并在內存中占4字節。 DOUBLE類型數據可以存儲至多18位十進制數，并在內存中占8字節。 **問題5：如何在MySQL種獲取當前日期？**  **問題6：如何查詢第n高的工資？**  **問題7：請寫出下面MySQL數據類型表達的意義（int(0)、char(16)、varchar(16)、datetime、text）** int(0)表示數據是INT類型，長度是0、char(16)表示固定長度字符串，長度為16、varchar(16)表示可變長度字符串，長度為16、datetime表示時間類型、text表示字符串類型，能存儲大字符串，最多存儲65535字節數據） **問題8：請說明InnoDB和MyISAM的區別** 1、InnoDB支持事務，MyISAM不支持； 2、InnoDB數據存儲在共享表空間，MyISAM數據存儲在文件中； 3、InnoDB支持行級鎖，MyISAM只支持表鎖； 4、InnoDB支持崩潰后的恢復，MyISAM不支持； 5、InnoDB支持外鍵，MyISAM不支持； 6、InnoDB不支持全文索引，MyISAM支持全文索引； **問題9：innodb引擎的特性** 1、插入緩沖（insert buffer) 2、二次寫(double write) 3、自適應哈希索引(ahi) 4、預讀(read ahead) **問題10：請列舉3個以上表引擎** InnoDB、MyISAM、Memory **問題11：請說明varchar和text的區別** 1、varchar可指定字符數，text不能指定，內部存儲varchar是存入的實際字符數+1個字節（n255)，text是實際字符數+2個字節。 2、text類型不能有默認值。 3、varchar可直接創建索引，text創建索引要指定前多少個字符。varchar查詢速度快于text,在都創建索引的情況下，text的索引幾乎不起作用。 4、查詢text需要創建臨時表。 **問題12：varchar(50)中50的含義** 最多存放50個字符，varchar(50)和(200)存儲hello所占空間一樣，但后者在排序時會消耗更多內存，因為order by col采用fixed\_length計算col長度(memory引擎也一樣)。 **問題13：int(20)中20的含義** 是指顯示字符的長度，不影響內部存儲，只是當定義了ZEROFILL時，前面補多少個 0 **問題14：簡單描述MySQL中，索引，主鍵，唯一索引，聯合索引的區別，對數據庫的性能有什么影響？** 一個表只能有一個主鍵索引，但是可以有多個唯一索引。 1、主鍵索引一定是唯一索引，唯一索引不是主鍵索引。 2、主鍵可以與外鍵構成參照完整性約束，防止數據不一致。 3、聯合索引：將多個列組合在一起創建索引，可以覆蓋多個列。（也叫復合索引，組合索引） 4、外鍵索引：只有InnoDB類型的表才可以使用外鍵索引，保證數據的一致性、完整性、和實現級聯操作（基本不用）。 5、全文索引：MySQL自帶的全文索引只能用于MyISAM，并且只能對英文進行全文檢索 （基本不用） **問題15：創建MySQL聯合索引應該注意什么？** 需遵循前綴原則 **問題16：列值為NULL時，查詢是否會用到索引？** 在MySQL里NULL值的列也是走索引的。當然，如果計劃對列進行索引，就要盡量避免把它設置為可空，MySQL難以優化引用了可空列的查詢,它會使索引、索引統計和值更加復雜。 **問題17：以下語句是否會應用索引：SELECT FROM users WHERE YEAR(adddate) < 2019;**\* 不會，因為只要列涉及到運算，MySQL就不會使用索引。 **問題18：MyISAM索引實現？** MyISAM存儲引擎使用B+Tree作為索引結構，葉節點的data域存放的是數據記錄的地址。MyISAM的索引方式也叫做非聚簇索引的，之所以這么稱呼是為了與InnoDB的聚簇索引區分。 **問題19：MyISAM索引與InnoDB索引的區別？** 1、InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。 2、InnoDB的主鍵索引的葉子節點存儲著行數據，因此主鍵索引非常高效。 3、MyISAM索引的葉子節點存儲的是行數據地址，需要再尋址一次才能得到數據。 4、InnoDB非主鍵索引的葉子節點存儲的是主鍵和其他帶索引的列數據，因此查詢時做到覆蓋索引會非常高效。 **問題20：有A(id,sex,par,c1,c2),B(id,age,c1,c2)兩張表，其中A.id與B.id關聯，現在要求寫出一條SQL語句，將B中age>50的記錄的c1,c2更新到A表中同一記錄中的c1,c2字段中**  **問題21 ：MySQL的關聯查詢語句你會那些？** 六種關聯查詢 1、交叉連接（CROSS JOIN） 2、內連接（INNER JOIN） 3、外連接（LEFT JOIN/RIGHT JOIN） 4、聯合查詢（UNION與UNION ALL） 5、全連接（FULL JOIN） 6、交叉連接（CROSS JOIN） **內連接分為三類** 1、等值連接：ON[A.id=B.id](http://a.id%3Db.id/) 2、不等值連接：ON[A.id](http://a.id/)\>[B.id](http://b.id/) 3、自連接：SELECT \* FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid **外連接（LEFT JOIN/RIGHT JOIN）** 1、左外連接：LEFT OUTER JOIN, 以左表為主，先查詢出左表，按照ON后的關聯條件匹配右表，沒有匹配到的用NULL填充，可以簡寫成LEFT JOIN 2、右外連接：RIGHT OUTER JOIN, 以右表為主，先查詢出右表，按照ON后的關聯條件匹配左表，沒有匹配到的用NULL填充，可以簡寫成RIGHT JOIN **聯合查詢（UNION與UNION ALL）**  1、就是把多個結果集集中在一起，UNION前的結果為基準，需要注意的是聯合查詢的列數要相等，相同的記錄行會合并 2、如果使用UNION ALL，不會合并重復的記錄行 3、效率 UNION 高于 UNION ALL **全連接（FULL JOIN）** 1、MySQL不支持全連接 2、可以使用LEFT JOIN 和UNION和RIGHT JOIN聯合使用 **嵌套查詢** 用一條SQL語句得結果作為另外一條SQL語句得條件，效率不好把握 解題方法 根據考題要搞清楚表的結果和多表之間的關系，根據想要的結果思考使用那種關聯方式，通常把要查詢的列先寫出來，然后分析這些列都屬于哪些表，才考慮使用關聯查詢 **問題22：UNION與UNION ALL的區別？** 1、如果使用UNION ALL，不會合并重復的記錄行 2、效率 UNION 高于 UNION ALL **問題23：一個6億的表a，一個3億的表b，通過外鍵tid關聯，你如何最快的查詢出滿足條件的第50000到第50200中的這200條數據記錄。** 1、如果A表TID是自增長,并且是連續的,B表的ID為索引  2、如果A表的TID不是連續的,那么就需要使用覆蓋索引.TID要么是主鍵,要么是輔助索引,B表ID也需要有索引。  問題24：拷貝表( 拷貝數據, 源表名：a 目標表名：b) **問題25：Student(S#,Sname,Sage,Ssex) 學生表 Course(C#,Cname,T#) 課程表 SC(S#,C#,score) 成績表 Teacher(T#,Tname) 教師表 查詢沒學過“葉平”老師課的同學的學號、姓名**  問題26：隨機取出10條數據 **問題27：請簡述項目中優化SQL語句執行效率的方法，從哪些方面，SQL語句性能如何分析？** **考點分析：** 這道題主要考察的是查找分析SQL語句查詢速度慢的方法 **延伸考點：** 1、優化查詢過程中的數據訪問 2、優化長難的查詢語句 3、優化特定類型的查詢語句 **如何查找查詢速度慢的原因** 記錄慢查詢日志，分析查詢日志，不要直接打開慢查詢日志進行分析，這樣比較浪費時間和精力，可以使用pt-query-digest工具進行分析 **使用show profile**  **使用show status** show status會返回一些計數器，show global status會查看所有服務器級別的所有計數 有時根據這些計數，可以推測出哪些操作代價較高或者消耗時間多 show processlist 觀察是否有大量線程處于不正常的狀態或特征  最常問的MySQL面試題五——每個開發人員都應該知道 **使用explain** 分析單條SQL語句  **優化查詢過程中的數據訪問** 1、訪問數據太多導致查詢性能下降 2、確定應用程序是否在檢索大量超過需要的數據，可能是太多行或列 3、確認MySQL服務器是否在分析大量不必要的數據行 4、避免犯如下SQL語句錯誤 5、查詢不需要的數據。解決辦法：使用limit解決 6、多表關聯返回全部列。解決辦法：指定列名 7、總是返回全部列。解決辦法：避免使用SELECT \* 8、重復查詢相同的數據。解決辦法：可以緩存數據，下次直接讀取緩存 9、是否在掃描額外的記錄。解決辦法： 10、使用explain進行分析，如果發現查詢需要掃描大量的數據，但只返回少數的行，可以通過如下技巧去優化： 11、使用索引覆蓋掃描，把所有的列都放到索引中，這樣存儲引擎不需要回表獲取對應行就可以返回結果。 12、改變數據庫和表的結構，修改數據表范式 13、重寫SQL語句，讓優化器可以以更優的方式執行查詢。 **優化長難的查詢語句** 1、一個復雜查詢還是多個簡單查詢 2、MySQL內部每秒能掃描內存中上百萬行數據，相比之下，響應數據給客戶端就要慢得多 3、使用盡可能小的查詢是好的，但是有時將一個大的查詢分解為多個小的查詢是很有必要的。 4、切分查詢 5、將一個大的查詢分為多個小的相同的查詢 6、一次性刪除1000萬的數據要比一次刪除1萬，暫停一會的方案更加損耗服務器開銷。 7、分解關聯查詢，讓緩存的效率更高。 8、執行單個查詢可以減少鎖的競爭。 9、在應用層做關聯更容易對數據庫進行拆分。 10、查詢效率會有大幅提升。 11、較少冗余記錄的查詢。 **優化特定類型的查詢語句** 1、count(\*)會忽略所有的列，直接統計所有列數，不要使用count(列名) 2、MyISAM中，沒有任何where條件的count(\*)非常快。 3、當有where條件時，MyISAM的count統計不一定比其它引擎快。 4、可以使用explain查詢近似值，用近似值替代count(\*) 5、增加匯總表 6、使用緩存 **優化關聯查詢** 1、確定ON或者USING子句中是否有索引。 2、確保GROUP BY和ORDER BY只有一個表中的列，這樣MySQL才有可能使用索引。 **優化子查詢** 1、用關聯查詢替代 2、優化GROUP BY和DISTINCT 3、這兩種查詢據可以使用索引來優化，是最有效的優化方法 4、關聯查詢中，使用標識列分組的效率更高 5、如果不需要ORDER BY，進行GROUP BY時加ORDER BY NULL，MySQL不會再進行文件排序。 6、WITH ROLLUP超級聚合，可以挪到應用程序處理 **優化LIMIT分頁** 1、LIMIT偏移量大的時候，查詢效率較低 2、可以記錄上次查詢的最大ID，下次查詢時直接根據該ID來查詢 優化UNION查詢 UNION ALL的效率高于UNION 優化WHERE子句 解題方法 對于此類考題，先說明如何定位低效SQL語句，然后根據SQL語句可能低效的原因做排查，先從索引著手，如果索引沒有問題，考慮以上幾個方面，數據訪問的問題，長難查詢句的問題還是一些特定類型優化的問題，逐一回答。 **問題28：SQL語句優化的一些方法？** 1、對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。 2、應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：  3、應盡量避免在 where 子句中使用!=或<>操作符，否則引擎將放棄使用索引而進行全表掃描。 4、應盡量避免在 where 子句中使用or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如：  5、in 和 not in 也要慎用，否則會導致全表掃描，如：  6、下面的查詢也將導致全表掃描：select id from t where name like ‘%李%’若要提高效率，可以考慮全文檢索。 7、 如果在 where 子句中使用參數，也會導致全表掃描。因為SQL只有在運行時才會解析局部變量，但優化程序不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇。然 而，如果在編譯時建立訪問計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描：  8、應盡量避免在 where 子句中對字段進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描。如：  9、應盡量避免在where子句中對字段進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。如：  10、不要在 where 子句中的“=”左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引