該文章借鑑了不一樣平臺對知識點的描述。java
InnoDB:mysql
MySIAM：sql
操做區別：數據庫
選擇：緩存
索引是對數據庫表中一或多個列的值進行排序的結構，是幫助MySQL高效獲取數據的數據結構安全
（數據庫是磁盤文件，磁盤IO 的代價較高，因此採用索引減小IO 次數）服務器
Mysql 中經常使用的索引有B+ 樹索引（包括普通索引、惟一索引、主鍵索引），哈希索引，全文索引，R-TREE 索引（空間索引，主要用於地理空間數據類型，不多使用）。網絡
Mysql 傳統意義上的索引爲B+ 樹索引，B+ 樹索引的本質就是B+ 樹在數據庫中的實現，因爲B+ 樹的高扇出性，數據庫中的B+ 樹的高通常爲2-4層，所以查找某一鍵值的行記錄只需2-4次IO，大概0.02~0.04秒。session
（扇出性：是指該模塊直接調用的下級模塊的個數。扇出大表示模塊的複雜度高，須要控制和協調過多的下級模塊）
數據結構
B+ 樹索引
主要分爲彙集索引和輔助索引。
彙集索引是根據每張表的主鍵建造的一棵B+ 樹，葉子節點中存放的是整張表的行記錄。一張表只能有一個彙集索引。由於彙集索引在邏輯上是連續的，因此它對於主鍵的排序查找和範圍查找速度很是快。
輔助索引與彙集索引不一樣的地方在於，輔助索引不是惟一的，它的葉子節點只包含行記錄的部分數據以及對應彙集索引的節點位置。經過輔助索引來查找數據時，先遍歷輔助索引找到對應主鍵索引，再經過主鍵索引查找對應記錄。
在MYISAM 中主鍵索引和輔助索引都至關上述輔助索引，索引頁中存放的是主鍵和指向數據頁的偏移量，數據頁中存放的是主鍵和該主鍵所屬行記錄的地址空間。惟一的區別是MYISAM 中主鍵索引不能重複，輔助索引能夠。
從使用上來講還有聯合索引和覆蓋索引。
聯合索引是指對錶上的多個列進行索引。它對對應多個列的指定獲取比較快。另一個好處是聯合索引對第二個鍵已經排好序了，因此對兩個列的排序獲取能夠避免多作一次排序操做。
覆蓋索引其實更算一種思想，可以從輔助索引中獲取信息，就不須要查詢彙集索引中的數據。使用輔助索引的好處在於輔助索引包含的信息少，因此大小遠小於彙集索引，所以能夠大大減小IO 操做。
哈希索引是一種自適應的索引，數據庫會根據表的使用狀況自動生成哈希索引，咱們人爲是沒辦法干預的。
InnoDB 儲存引擎採用的哈希函數爲除法散列方式，採用的衝突處理方法爲鏈地址法。它指定查詢的速度很快，可是範圍查詢就無能爲力了。
全文索引用於實現關鍵詞搜索。但它只能根據空格分詞，所以不支持中文。
索引的優勢：
索引的缺點
擴展
聚簇索引和非聚簇索引：
哪些狀況須要加索引？
哪些狀況不須要加索引？
什麼是事務，它有哪些特性？
事務就是一組原子性的操做，這些操做要麼所有發生，要麼所有不發生。事務把數據庫從一種一致性狀態轉換成另外一種一致性狀態。
事務具備ACID 四種特性，即原子性(atomicity)，一致性(consistency)，隔離性(isolation)，持久性(durability)：
事務的隔離級別，分別解決了什麼問題？
事務有4 個隔離級別，分別是：
隔離級別依次提升，分別解決了髒讀、不可重讀和幻讀。
不可重複讀的和幻讀很容易混淆，不可重複讀側重於修改，幻讀側重於新增或刪除。解決不可重複讀的問題只需鎖住知足條件的行，解決幻讀須要鎖表
事務是基於重作日誌(redo log)文件和回滾日誌(undo log)實現的
每提交一個事務必須先將該事務的全部日誌寫入到重作日誌文件進行持久化，數據庫就能夠經過重作日誌來保證事務的原子性和持久性。
每當有修改事務時， 還會產生undo log，若是須要回滾， 則根據undo log反向語句進行邏輯操做，好比insert一條記錄就delete一條記錄。undo log主要實現數據庫的一致性， 還能夠用來實現MVCC。
a.經過show status命令瞭解各SQL執行的頻率
show [session|global] status like 「Com_%」;
常見的執行參數
1 mysql> show global status like " 2 Slow_queries"; 3 +---------------+-------+ 4 | Variable_name | Value | 5 +---------------+-------+ 6 | Slow_queries | 0 | 7 +---------------+-------+ 8 1 row in set
b. 定位執行效率最低的sql語句
能夠經過兩個辦法定位效率較低的SQL 語句：
c.經過EXPLAIN 分析低效SQL 的執行計劃
查詢到效率低的sql 語句後，能夠經過EXPLAIN 分析低效SQL 的執行計劃。
mysql> explain select * from comment ; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | comment | NULL | ALL | NULL | NULL | NULL | NULL | 92 | 100 | NULL | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set mysql>
d.經過show profile 分析SQL
//默認不開啓profile，使用時先開啓profile mysql> set profiling=1; Query OK, 0 rows affected mysql> select @@profiling; +-------------+ | @@profiling | +-------------+ | 1 | +-------------+ 1 row in set //在InnoDB 下獲取錶行數 mysql> select count(*) from comment; +----------+ | count(*) | +----------+ | 92 | +----------+ 1 row in set // 查開執行時間 mysql> show profiles; +----------+------------+------------------------------+ | Query_ID | Duration | Query | +----------+------------+------------------------------+ | 1 | 0.0109785 | select @@profiling | | 2 | 0.05502275 | select count(*) from comment | +----------+------------+------------------------------+ 2 rows in set //具體查開每一步執行時間 mysql> show profile for query 2 ; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 9.2E-5 | | checking permissions | 9E-6 | | Opening tables | 2E-5 | | init | 1.8E-5 | | System lock | 9E-6 | | optimizing | 0.054635 | //在優化處理耗時最多 | statistics | 5.6E-5 | | preparing | 1.7E-5 | | executing | 5E-6 | | Sending data | 8E-5 | | end | 5E-6 | | query end | 1.5E-5 | | closing tables | 9E-6 | | freeing items | 3.6E-5 | | cleaning up | 1.9E-5 | +----------------------+----------+ 15 rows in set mysql>
f. 還能夠經過trace 分析優化器如何選擇執行計劃的（有興趣能夠看一下）
a.對大批量插入數據
能夠經過DISABLE KEY 和 ENABLE KEY 關閉和打開MYISAM 表索引的更新來提升效率。
1 ALTER TABLE table_name DISABLE KEY //在導入數據前關閉索引更新 2 loading the data 3 ALTER TABLE table_name ENABLE KEY //導入完成後開啓
b.優化INSERT 語句
insert into table_name values(1,2)(1,3)(1,4)...
c 優化order by 語句
d. 優化group by 語句
默認狀況下group by 語句對分組的數據進行排序操做，若是不須要排序操做能夠經過order by null 禁止排序。
e. 優化嵌套語句
若是能夠的話，特別是where 中包含索引的狀況，用join 語法來代替嵌套語法（in）由於join 不須要在MySQL 的內存中建立臨時表。
f. 優化or語句
對於含有or 的查詢語句，若是要利用索引，則or 之間的每個條件都必須用到索引，若是沒用索引，能夠考慮增長索引。不然會全表掃面。
g. 優化分頁查詢
通常分頁查詢時，經過建立覆蓋索引可以比較好的提升性能。一個常見又頭痛的場景是"limit 1000,20" 此時MySQL 排序出前1020 條數據後，只須要返回20條數據，查詢和排序的代價都很高。有兩種優化方案
1 mysql> explain select comment_id,comment_content from comment order by comment_create_time limit 50,5; 2 +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+ 3 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 4 +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+ 5 | 1 | SIMPLE | comment | NULL | ALL | NULL | NULL | NULL | NULL | 92 | 100 | Using filesort | 6 +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+ 7 1 row in set 8 9 mysql> explain select a.comment_id,a.comment_content from comment a inner join(select comment_id from comment order by comment_create_time limit 50,5)b on a.comment_id=b.comment_id ; 10 +----+-------------+------------+------------+--------+---------------+---------+---------+--------------+------+----------+----------------+ 11 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 12 +----+-------------+------------+------------+--------+---------------+---------+---------+--------------+------+----------+----------------+ 13 | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 55 | 100 | NULL | 14 | 1 | PRIMARY | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | b.comment_id | 1 | 100 | NULL | 15 | 2 | DERIVED | comment | NULL | ALL | NULL | NULL | NULL | NULL | 92 | 100 | Using filesort | 16 +----+-------------+------------+------------+--------+---------------+---------+---------+--------------+------+----------+----------------+ 17 3 rows in set
h. 使用SQL 提示
SQL 提示是優化數據庫的一個重要手段，經常使用的SQL 提示：
推薦數據庫使用某個索引，可讓Mysql 再也不考慮其餘可用索引
1 sql語句 use index(index_name);
忽視數據庫某個索引，可讓Mysql 再也不考慮這個索引
1 sql語句 ignore index(index_name);
強迫數據庫使用某個索引，使用use index 數據庫仍是可能不用這個索引，可是force index 數據庫必須使用這個索引
1 sql語句 force index(index_name);
第一範式:
確保每列的原子性(強調的是列的原子性，即列不可以再分紅其餘幾列).若是每列(或者每一個屬性)都是不可再分的最小數據單元(也稱爲最小的原子單元),則知足第一範式.
例如:顧客表(姓名、編號、地址、……)其中"地址"列還能夠細分爲國家、省、市、區等。
第二範式:
在第一範式的基礎上更進一層,目標是確保表中的每列都和主鍵相關(一是表必須有一個主鍵；二是沒有包含在主鍵中的列必須徹底依賴於主鍵，而不能只依賴於主鍵的部分)若是一個關係知足第一範式,而且除了主鍵之外的其它列,都依賴於該主鍵,則知足第二範式.
例如:訂單表(訂單編號、產品編號、定購日期、價格、……)，"訂單編號"爲主鍵，"產品編號"和主鍵列沒有直接的關係，即"產品編號"列不依賴於主鍵列，應刪除該列。
第三範式:
在第二範式的基礎上更進一層,目標是確保每列都和主鍵列直接相關,而不是間接相關(另外非主鍵列必須直接依賴於主鍵，不能存在傳遞依賴).若是一個關係知足第二範式,而且不依賴於除了主鍵之外的其它列,則知足第三範式.
爲了理解第三範式，須要根據Armstrong千米之必定義傳遞依賴。假設A、B和C是關係R的三個屬性，若是A-〉B且B-〉C，則從這些函數依賴中，能夠得出A-〉C，如上所述，
依賴A-〉C是傳遞依賴。
例如:訂單表(訂單編號，定購日期，顧客編號，顧客姓名，……)，初看該表沒有問題，知足第二範式，每列都和主鍵列"訂單編號"相關，再細看你會發現"顧客姓名"和"顧客
編號"相關，"顧客編號"和"訂單編號"又相關，最後通過傳遞依賴，"顧客姓名"也和"訂單編號"相關。爲了知足第三範式，應去掉"顧客姓名"列，放入客戶表中。
存儲過程是一些預編譯的SQL語句。
優勢：
缺點：
視圖是一種虛擬的表，具備和物理表相同的功能，沒有物理存儲。能夠對視圖進行增，改，查，操做，試圖一般是有一個表或者多個表的行或列的子集。對視圖的修改不影響基本表。它使得咱們獲取數據更容易，相比多表查詢。
使用場景：
超鍵：在關係中能惟一標識元組的屬性集稱爲關係模式的超鍵。一個屬性能夠爲做爲一個超鍵，多個屬性組合在一塊兒也能夠做爲一個超鍵。超鍵包含候選鍵和主鍵。
候選鍵：是最小超鍵，即沒有冗餘元素的超鍵。
主鍵：數據庫表中對儲存數據對象予以惟一和完整標識的數據列或屬性的組合。一個數據列只能有一個主鍵，且主鍵的取值不能缺失，即不能爲空值（Null）。
外鍵：在一個表中存在的另外一個表的主鍵稱此表的外鍵
組合兩個表中的記錄，返回關聯字段相符的記錄，也就是返回兩個表的交集（陰影）部分。
left join 是left outer join的簡寫，它的全稱是左外鏈接，是外鏈接中的一種。
左(外)鏈接，左表(a_table)的記錄將會所有表示出來，而右表(b_table)只會顯示符合搜索條件的記錄。右表記錄不足的地方均爲NULL。
right join是right outer join的簡寫，它的全稱是右外鏈接，是外鏈接中的一種。
與左(外)鏈接相反，右(外)鏈接，左表(a_table)只會顯示符合搜索條件的記錄，而右表(b_table)的記錄將會所有表示出來。左表記錄不足的地方均爲NULL。
MySQL目前不支持此種方式，能夠用其餘方式替代解決。
完整性約束是對字段進行限制，從而符合該字段達到咱們指望的效果好比字段含有默認值，不能是NULL等, 主要有惟1、自增、主鍵、外鍵約束
1 PRIMARY KEY (PK) 標識該字段爲該表的主鍵，能夠惟一的標識記錄 2 FOREIGN KEY (FK) 標識該字段爲該表的外鍵 3 NOT NULL 標識該字段不能爲空 4 UNIQUE KEY (UK) 標識該字段的值是惟一的 5 AUTO_INCREMENT 標識該字段的值自動增加（整數類型，並且爲主鍵） 6 DEFAULT 爲該字段設置默認值 7 8 UNSIGNED 無符號 9 ZEROFILL 使用0填充
參考文章：https://blog.csdn.net/qq_36906627/java/article/details/86634518