1.索引作用和優缺點
索引就一種特殊的查詢表，數據庫的搜索可以利用它加速對數據的檢索。它很類似與現實生活中書的目錄，不需要查詢整本書內容就可以找到想要的數據。索引可以是唯一的，創建索引允許指定單個列或者是多個列。缺點是它減慢了數據錄入的速度，同時也增加了數據庫的尺寸大小。


2.什么字段適合建索引
唯一性比較高,不為空,經常查詢


3.常見慢查詢優化
1.建索引 2.減少表之間的關聯 3.優化sql，盡量讓sql很快定位數據，不要讓sql做全表查詢，應該走索引,把數據 量大的表排在前面 4.簡化查詢字段，沒用的字段不要，已經對返回結果的控制，盡量返回少量數據 5.盡量用PreparedStatement來查詢，不要用Statement


4.truncate,delete 和 drop
TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同：二者均刪除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事務日志資源少。 DELETE 語句每次刪除一行，并在事務日志中為所刪除的每行記錄一項。

TRUNCATE TABLE 通過釋放存儲表數據所用的數據頁來刪除數據，并且只在事務日志中記錄頁的釋放。?

TRUNCATE TABLE 刪除表中的所有行，但表結構及其列、約束、索引等保持不變。新行標識所用的計數值重置為該列的種子。如果想保留標識計數值，請改用 DELETE。如果要刪除表定義及其數據，請使用 DROP TABLE 語句。

TRUNCATE,DELETE,DROP 放在一起比較：

TRUNCATE TABLE ：刪除內容、釋放空間但不刪除定義。
DELETE TABLE: 刪除內容不刪除定義，不釋放空間。
DROP TABLE ：刪除內容和定義，釋放空間。


5.列舉幾種表連接方式,有什么區別
左連接、右連接、全連接、 內連接：只有兩個元素表相匹配的才能在結果集中顯示。 左外連接:左邊為驅動表，驅動表的數據全部顯示，匹配表的不匹配的不會顯示。 右外連接:右邊為驅動表，驅動表的數據全部顯示，匹配表的不匹配的不會顯示。 全外連接：連接的表中不匹配的數據全部會顯示出來。 交叉連接： 笛卡爾效應，顯示的結果是鏈接表數的乘積。

6.索引最左原則


SHOW INDEX FROM employees.titles;
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type |
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
| titles | 0 | PRIMARY | 1 | emp_no | A | NULL | | BTREE |
| titles | 0 | PRIMARY | 2 | title | A | NULL | | BTREE |
| titles | 0 | PRIMARY | 3 | from_date | A | 443308 | | BTREE |
| titles | 1 | emp_no | 1 | emp_no | A | 443308 | | BTREE |
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+


6.1,索引是否有問題?
從結果中可以到titles表的主索引為<emp_no, title, from_date>，還有一個輔助索引<emp_no>。為了避免多個索引使事情變復雜（MySQL的SQL優化器在多索引時行為比較復雜），這里我們將輔助索引drop掉：


6.2,全列匹配
全列匹配(此時若沒有按照索引順序時，mysql查詢優化器會自動的調整順序來使用定義好的索引)


6.3,最左前綴匹配
SELECT * FROM employees.titles WHERE emp_no='10001';
SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26';


上面的sql是否用到了索引.


6.4 上面6.3中的第二個sql可以優化一下嗎?


優化方法可以窮舉索引第二列,如果第二列數據有限;或者增加輔助索引<emp_no, from_date>.
SELECT * FROM employees.titles
WHERE emp_no='10001'
AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager')
AND from_date='1986-06-26';


6.5 模糊匹配與索引
SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';


6.6 范圍查詢
SELECT * FROM employees.titles WHERE emp_no < '10010' and title='Senior Engineer';
(范圍查詢后面的列將無法使用索引)
范圍列可以用到索引（必須是最左前綴），但是范圍列后面的列無法用到索引。同時，索引最多用于一個范圍列，因此如果查詢條件中有兩個范圍列則無法全用到索引。


6.7 查詢條件中含有函數或表達式
很不幸，如果查詢條件中含有函數或表達式，則MySQL不會為這列使用索引（雖然某些在數學意義上可以使用）。例如
SELECT * FROM employees.titles WHERE emp_no='10001' AND left(title, 6)='Senior';

SELECT * FROM employees.titles WHERE emp_no - 1='10000';

7. exist 和 in

select * from A where id in(select id from B)

A表比B表大的情況,效率較好;如果a表的id有索引,會被利用到.

select a.* from A a where exists(select 1 from B b where a.id=b.id)

exists()適合B表比A表數據大的情況,如果b.id有索引會被利用到.

當A表數據與B表數據一樣大時,in與exists效率差不多,可結合索引情況使用.

in 和 exists的區別: 如果子查詢得出的結果集記錄較少，主查詢中的表較大且又有索引時應該用in, 反之如果外層的主查詢記錄較少，子查詢中的表大，又有索引時使用exists。其實我們區分in和exists主要是造成了驅動順序的改變(這是性能變化的關鍵)，如果是exists，那么以外層表為驅動表，先被訪問，如果是IN，那么先執行子查詢，所以我們會以驅動表的快速返回為目標，那么就會考慮到索引及結果集的關系了 ，另外IN時不對NULL進行處理。

in 是把外表和內表作hash 連接，而exists是對外表作loop循環，每次loop循環再對內表進行查詢。一直以來認為exists比in效率高的說法是不準確的。

8.mysql數據庫引擎MyISAM和InnoDB的區別

8.1、存儲結構?
每個MyISAM在磁盤上存儲成三個文件。第一個文件的名字以表的名字開始，擴展名指出文件類型。?
.frm文件存儲表定義。?
數據文件的擴展名為.MYD (MYData)。?
索引文件的擴展名是.MYI (MYIndex)。

8.2、存儲空間?
MyISAM：可被壓縮，存儲空間較小。?
InnoDB：需要更多的內存和存儲，它會在主內存中建立其專用的緩沖池用于高速緩沖數據和索引。?
MyISAM的索引和數據是分開的，并且索引是有壓縮的，內存使用率就對應提高了不少。能加載更多索引，而Innodb是索引和數據是緊密捆綁的，沒有使用壓縮從而會造成Innodb比MyISAM體積龐大不小

8.3、事務處理?
MyISAM類型的表強調的是性能，其執行數度比InnoDB類型更快，但是不支持外鍵、不提供事務支持。?
InnoDB提供事務支持事務，外部鍵（foreign key）等高級數據庫功能。

SELECT、UPDATE、INSERT、Delete操作?
如果執行大量的SELECT，MyISAM是更好的選擇。?
如果你的數據執行大量的INSERT或UPDATE，出于性能方面的考慮，應該使用InnoDB表。?
DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的刪除。而MyISAM則是重新建立表。在innodb上如果要清空保存有大量數據的表，最好使用truncate table這個命令。

AUTO_INCREMENT?
MyISAM：可以和其他字段一起建立聯合索引。引擎的自動增長列必須是索引，如果是組合索引，自動增長可以不是第一列，他可以根據前面幾列進行排序后遞增。?
InnoDB：InnoDB中必須包含只有該字段的索引。引擎的自動增長列必須是索引，如果是組合索引也必須是組合索引的第一列。

8.4、表的具體行數?
MyISAM：保存有表的總行數，如果select count(*) from table;會直接取出該值。?
InnoDB：沒有保存表的總行數，如果使用select count(*) from table；就會遍歷整個表，消耗相當大，但是在加了where后，myisam和innodb處理的方式都一樣。

8.5、全文索引?
MyISAM：支持 FULLTEXT類型的全文索引。不支持中文。?
InnoDB：不支持FULLTEXT類型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

8.6、表鎖差異?
MyISAM：只支持表級鎖，只支持表級鎖，用戶在操作myisam表時，select，update，delete，insert語句都會給表自動加鎖。?
InnoDB：支持事務和行級鎖，是innodb的最大特色。行鎖大幅度提高了多用戶并發操作的新能。但是InnoDB的行鎖也不是絕對的，如果在執行一個SQL語句時MySQL不能確定要掃描的范圍，InnoDB表同樣會鎖全表， 例如update table set num=1 where name like “%aaa%”

一般來說：?
MyISAM適合：?
(1)做很多count 的計算；?
(2)插入不頻繁，查詢非常頻繁；?
(3)沒有事務。?
InnoDB適合：?
(1)可靠性要求比較高，或者要求事務；?
(2)表更新和查詢都相當的頻繁，并且表鎖定的機會比較大的情況。


　兩種類型最主要的差別就是Innodb 支持事務處理與外鍵和行級鎖。而MyISAM不支持.所以MyISAM往往就容易被人認為只適合在小項目中使用。
　　作為使用MySQL的用戶角度出發，Innodb和MyISAM都是比較喜歡的，如果數據庫平臺要達到需求：99.9%的穩定性，方便的擴展性和高可用性來說的話，MyISAM絕對是首選。
　　原因如下：
　　1、平臺上承載的大部分項目是讀多寫少的項目，而MyISAM的讀性能是比Innodb強不少的。
　　2、MyISAM的索引和數據是分開的，并且索引是有壓縮的，內存使用率就對應提高了不少。能加載更多索引，而Innodb是索引和數據是緊密捆綁的，沒有使用壓縮從而會造成Innodb比MyISAM體積龐大不小。
　　3、經常隔1，2個月就會發生應用開發人員不小心update一個表where寫的范圍不對，導致這個表沒法正常用了，這個時候MyISAM的優越性就體現出來了，隨便從當天拷貝的壓縮包取出對應表的文件，隨便放到一個數據庫目錄下，然后dump成sql再導回到主庫，并把對應的binlog補上。如果是Innodb，恐怕不可能有這么快速度，別和我說讓Innodb定期用導出xxx.sql機制備份，因為最小的一個數據庫實例的數據量基本都是幾十G大小。
　　4、從接觸的應用邏輯來說，select count(*) 和order by 是最頻繁的，大概能占了整個sql總語句的60%以上的操作，而這種操作Innodb其實也是會鎖表的，很多人以為Innodb是行級鎖，那個只是where對它主鍵是有效，非主鍵的都會鎖全表的。
　　5、還有就是經常有很多應用部門需要我給他們定期某些表的數據，MyISAM的話很方便，只要發給他們對應那表的frm.MYD,MYI的文件，讓他們自己在對應版本的數據庫啟動就行，而Innodb就需要導出xxx.sql了，因為光給別人文件，受字典數據文件的影響，對方是無法使用的。
　　6、如果和MyISAM比insert寫操作的話，Innodb還達不到MyISAM的寫性能，如果是針對基于索引的update操作，雖然MyISAM可能會遜色Innodb,但是那么高并發的寫，從庫能否追的上也是一個問題，還不如通過多實例分庫分表架構來解決。
　　7、如果是用MyISAM的話，merge引擎可以大大加快應用部門的開發速度，他們只要對這個merge表做一些select count(*)操作，非常適合大項目總量約幾億的rows某一類型(如日志，調查統計)的業務表。
　　當然Innodb也不是絕對不用，用事務的項目就用Innodb的。另外，可能有人會說你MyISAM無法抗太多寫操作，但是可以通過架構來彌補。

總結

以上是生活随笔為你收集整理的interview-db的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。