其它MySQL 面試系列：

MySQL 面試系列：MySQL查詢(xún)?nèi)绾芜M(jìn)行優(yōu)化?

MySQL 面試系列：一條select語(yǔ)句在MySQL是這樣執(zhí)行的?
MySQL 面試系列：MySQL 常見(jiàn)的開(kāi)放性問(wèn)題
MySQL 面試系列：MySQL 性能優(yōu)化 & 分布式

MySQL 面試系列：MySQL 命令和內(nèi)置函數(shù)
MySQL 面試系列：MySQL 中日志的面試題總結(jié)
MySQL 面試系列：MySQL 中鎖的面試題總結(jié)

MySQL 面試系列：MySQL 事務(wù)的面試題總結(jié)
MySQL 面試系列：MySQL 索引的面試題總結(jié)
MySQL 面試系列：MySQL 基礎(chǔ)模塊的面試題總結(jié)

有一個(gè)超級(jí)大表，如何優(yōu)化分頁(yè)查詢(xún)？

超級(jí)大表的分頁(yè)優(yōu)化分有以下兩種方式：

• 數(shù)據(jù)庫(kù)層面優(yōu)化：利用子查詢(xún)優(yōu)化超多分頁(yè)場(chǎng)景，比如：SELECT a.* FROM 表 1 a, (select id from 表 1 where 條件 LIMIT 100000,20 ) b where a.id=b.id ，先快速定位需要獲取的 id 段，然后再關(guān)聯(lián)查詢(xún)。MySQL 并不是跳過(guò) offset 行，而是取 offset+N 行，然后返回放棄前 offset 行，返回 N 行，那當(dāng) offset 特別大的時(shí)候，效率就非常的低下，要么控制返回的總頁(yè)數(shù)，要么對(duì)超過(guò)特定閾值的頁(yè)數(shù)進(jìn)行 SQL 改寫(xiě)，利用子查詢(xún)先快速定位需要獲取的 id 段，然后再關(guān)聯(lián)查詢(xún)，就是對(duì)分頁(yè)進(jìn)行 SQL 改寫(xiě)的具體實(shí)現(xiàn)；
• 程序?qū)用鎯?yōu)化：可以利用緩存把查詢(xún)的結(jié)果緩存起來(lái)，這樣再下一次查詢(xún)的時(shí)候性能就非常高了。

線上修改表結(jié)構(gòu)有哪些風(fēng)險(xiǎn)？

線上修改表結(jié)構(gòu)有可能 MySQL 服務(wù)器阻塞，因?yàn)樵趫?zhí)行 DML（select、update、delete、insert）操作時(shí)，會(huì)給表增加一個(gè)元數(shù)據(jù)鎖，這個(gè)元數(shù)據(jù)鎖是為了保證在查詢(xún)期間表結(jié)構(gòu)不會(huì)被修改，而執(zhí)行修改表結(jié)構(gòu)時(shí)，必須要等待元數(shù)據(jù)鎖完成之后才能執(zhí)行，這就可能造成數(shù)據(jù)庫(kù)服務(wù)器的阻塞。

在 MySQL 5.6 開(kāi)始提供了 online ddl 功能，允許一些 DDL（create table/view/index/syn/cluster）語(yǔ)句和 DML 語(yǔ)句并發(fā)，在 5.7 版本對(duì) online ddl 又有了增強(qiáng)，這使得大部分 DDL 操作可以在線進(jìn)行，詳見(jiàn)：https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html，這使得在線上修改表結(jié)構(gòu)的風(fēng)險(xiǎn)變的更大，如果在業(yè)務(wù)開(kāi)發(fā)過(guò)程中必須在線修改表結(jié)構(gòu)，可以參考以下方案：

• 盡量在業(yè)務(wù)量小的時(shí)間段進(jìn)行；
• 查看官方文檔，確認(rèn)要做的表修改可以和 DML 并發(fā)，不會(huì)阻塞線上業(yè)務(wù)；
• 推薦使用 percona 公司的 pt-online-schema-change 工具，該工具被官方的 online ddl 更為強(qiáng)大，它的基本原理是：通過(guò) insert…select… 語(yǔ)句進(jìn)行一次全量拷貝，通過(guò)觸發(fā)器記錄表結(jié)構(gòu)變更過(guò)程中產(chǎn)生的增量，從而達(dá)到表結(jié)構(gòu)變更的目的。比如，要對(duì) A 表進(jìn)行變更，它的主要流程為：

1）創(chuàng)建目的表結(jié)構(gòu)的空表 A_new；

2）在A表上創(chuàng)建觸發(fā)器，包括增、刪、改觸發(fā)器；

3）通過(guò) insert…select…limit N 語(yǔ)句分片拷貝數(shù)據(jù)到目的表；

4）Copy完成后，將 A_new 表 rename 到 A 表。

查詢(xún)長(zhǎng)時(shí)間不返回可能是什么原因？應(yīng)該如何處理？

查詢(xún)速度慢的原因很多，常見(jiàn)如下幾種： 1）查詢(xún)字段沒(méi)有索引或者沒(méi)有觸發(fā)索引查詢(xún)，沒(méi)有觸發(fā)索引查詢(xún)的情況如下： 不會(huì)使用索引的情況如下：

• 以 % 開(kāi)頭的 like 查詢(xún)不會(huì)使用 b-tree 索引；
• 數(shù)據(jù)類(lèi)型出現(xiàn)隱式轉(zhuǎn)換時(shí)不會(huì)使用索引，比如，某列是 varchar 類(lèi)型，卻使用了columnname=1 的查詢(xún)語(yǔ)句，這是不會(huì)使用索引，正確觸發(fā)索引的查詢(xún)語(yǔ)句為：columnname='1' ；
• 不符合最左前綴原則；
• 如果查詢(xún)條件有 or 分割，or 前面的使用索引，or 后面的未使用索引，則不會(huì)使用索引，因?yàn)榧词?or 之前的使用了索引，但是 or 之后的也需要全表查詢(xún)，索引就忽略索引，直接全表查詢(xún)；
• 如果 MySQL 認(rèn)為使用索引會(huì)比全表查詢(xún)更慢，則不會(huì)使用索引。

2）I/O 壓力大，讀取磁盤(pán)速度變慢。 3）內(nèi)存不足 4）網(wǎng)絡(luò)速度慢 5）查詢(xún)出的數(shù)據(jù)量過(guò)大，可以采用多次查詢(xún)或其他的方法降低數(shù)據(jù)量 6）死鎖，一般碰到這種情況的話，大概率是表被鎖住了，可以使用 show processlist; 命令，看看 SQL 語(yǔ)句的狀態(tài)，再針對(duì)不同的狀態(tài)做相應(yīng)的處理。

avatar

其中，當(dāng) State 列值為 Locked 時(shí)，表示被鎖定。 其它關(guān)于查看死鎖的命令： a）查看當(dāng)前的事務(wù)：

select * from informationschema.innodbtrx;

b）查看當(dāng)前鎖定的事務(wù)：

select * from informationschema.innodblocks;

c）查看當(dāng)前等鎖的事務(wù)

select * from informationschema.innodblock_waits;

以上問(wèn)題的解決方案如下：

1）正確創(chuàng)建和使用索引。 2）把數(shù)據(jù)、日志、索引放到不同的 IO 設(shè)備上，減少主數(shù)據(jù)庫(kù)的 IO 操作。更換 MySQL 的磁盤(pán)為固態(tài)硬盤(pán)，以提高磁盤(pán)的 IO 性能。 3）升級(jí)內(nèi)存，更換更大的內(nèi)存。 4）提升網(wǎng)速，升級(jí)帶寬。 5）用 Profiler 來(lái)跟蹤查詢(xún)，得到查詢(xún)所需的時(shí)間，找出有問(wèn)題的 SQL 語(yǔ)句，優(yōu)化 SQL。 6）查詢(xún)時(shí)值返回需要的字段。 7）設(shè)置死鎖的超時(shí)時(shí)間，限制和避免死鎖消耗過(guò)多服務(wù)器的資源。 8）盡量少用視圖，它的效率低，對(duì)視圖操作比直接對(duì)表操作慢,可以用存儲(chǔ)過(guò)程來(lái)代替視圖。不要用視圖嵌套，嵌套視圖增加了尋找原始數(shù)據(jù)的難度。

MySQL 主從延遲的原因有哪些？

主從延遲可以根據(jù) MySQL 提供的命令判斷，比如，在從服務(wù)器使用命令： show slave status;，其中 SecondsBehindMaster 如果為 0 表示主從復(fù)制狀態(tài)正常。 導(dǎo)致主從延遲的原因有以下幾個(gè)：

• 主庫(kù)有大事務(wù)處理；
• 主庫(kù)做大量的增、刪、改操作；
• 主庫(kù)對(duì)大表進(jìn)行字段新增、修改或添加索引等操作；
• 主庫(kù)的從庫(kù)太多，導(dǎo)致復(fù)制延遲。從庫(kù)數(shù)量一般 3-5 個(gè)為宜，要復(fù)制的節(jié)點(diǎn)過(guò)多，導(dǎo)致復(fù)制延遲；
• 從庫(kù)硬件配置比主庫(kù)差，導(dǎo)致延遲。查看 Master 和 Slave 的配置，可能因?yàn)閺膸?kù)的配置過(guò)低，執(zhí)行時(shí)間長(zhǎng)，由此導(dǎo)致的復(fù)制延遲時(shí)間長(zhǎng)；
• 主庫(kù)讀寫(xiě)壓力大，導(dǎo)致復(fù)制延遲；
• 從庫(kù)之間的網(wǎng)絡(luò)延遲。主從庫(kù)網(wǎng)卡、網(wǎng)線、連接的交換機(jī)等網(wǎng)絡(luò)設(shè)備都可能成為復(fù)制的瓶頸，導(dǎo)致復(fù)制延遲，另外跨公網(wǎng)主從復(fù)制很容易導(dǎo)致主從復(fù)制延遲。

如何保證數(shù)據(jù)不被誤刪？

保證數(shù)據(jù)不被誤刪的方法如下列表：