[[230870]]

MySQL 索引使用的注意事項

索引的目的在于提高查詢效率。可以類比字典，如果要查“mysql”這個單詞，我們肯定需要定位到m字母，然后從下往下找到y字母，再找到剩下的sql

INSERT 與 UPDATE 語句在擁有索引的表中執行會花費更多的時間，而SELECT 語句卻會執行得更快。這是因為，在進行插入或更新時，數據庫也需要插入或更新索引值。

索引的類型：

• UNIQUE(唯一索引)：不可以出現相同的值，可以有NULL值

• INDEX(普通索引)：允許出現相同的索引內容

• PROMARY KEY(主鍵索引)：不允許出現相同的值

• fulltext index(全文索引)：可以針對值中的某個單詞，但效率確實不敢恭維

• 組合索引：實質上是將多個字段建到一個索引里，列值的組合必須唯一

SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不會使用索引,因為所有索引列參與了計算

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不會使用索引,因為使用了函數運算,原理與上面相同

SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引

SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引

-- 正則表達式不使用索引,這應該很好理解,所以為什么在SQL中很難看到regexp關鍵字的原因

-- 字符串與數字比較不使用索引;

CREATE TABLE `a` (`a` char(10));

EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引

EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引

select * from dept where dname='xxx' or loc='xx' or deptno=45 --如果條件中有or,即使其中有條件帶索引也不會使用。換言之,就是要求使用的所有字段,都必須建立索引, 我們建議大家盡量避免使用or 關鍵字

-- 如果mysql估計使用全表掃描要比使用索引快,則不使用索引

這里看去看我另外整理的一篇關于mysql優化的 索引不生效替代辦法

說說反模式設計

這里的反模式針對的是數據庫

什么是“反模式”

反模式是一種試圖解決問題的方法，但通常會同時引發別的問題。

反模式分類

　　（1）邏輯數據庫設計反模式

在開始編碼之前，需要決定數據庫中存儲什么信息以及最佳的數據組織方式和內在關聯方式。

這包含了如何設計數據庫的表、字段和關系。

　　（2）物理數據庫設計反模式

在確定了需要存儲哪些數據之后，使用你所知的RDBMS關系型數據庫技術特性盡可能高效地實現數據庫管理。

這包含了定義表和索引，以及選擇數據類型。也需要是要SQL的“數據定義語言”，比如Create Table語句。

　　（3）查詢反模式

SQL的查詢是使用“數據操作語言”來完成，比如：Insert、Select、Update和Delete語句。

　　（4）應用程序開發反模式

SQL應該會用在Java、.Net、C++、Php等語言構建的應用程序中，在應用程序中使用SQL的方式有好有壞。

反模式分解

　　（1）目的

這是你可能要去嘗試解決的任務。意圖使用反模式提供解決方案，但通常會以引起更多問題而告終。

　　（2）反模式

這一部分表述了通常使用的解決方案的本質，并且展示了那些沒有預知到的后果，正是這些使得這些方案成為反模式。

　　（3）如何識別反模式

一些固定的方式會有助于你辨識在項目中使用的反模式。你遇到的特殊障礙，或是你自己和別人說的一些話，

都能使你提前識別出反模式。

　　（4）合理使用反模式

規則總有例外。在某些情況下，本來認為是反模式的設計卻可能是合理的，或者說至少是所有的方案中最合理的。

　　（5）解決方案

描述了首選的最佳解決方案，他們不僅能夠解決原有的問題，同時也不至于引起由反模式導致的新問題。

只能介紹些基礎的了

說說分庫與分表設計

分表的目的就在于此，減小數據庫的負擔，縮短查詢時間。

mysql中有一種機制是表鎖定和行鎖定，為什么要出現這種機制，是為了保證數據的完整性;我舉個例子來說吧，如果有二個sql都要修改同一張表的同一條數據，這個時候怎么辦呢，是不是二個sql都可以同時修改這條數據呢？很顯然mysql對這種情況的處理是，一種是表鎖定（myisam存儲引擎），一個是行鎖定（innodb存儲引擎）。表鎖定表示你們都不能對這張表進行操作，必須等我對表操作完才行。行鎖定也一樣，別的sql必須等我對這條數據操作完了，才能對這條數據進行操作.如果數據太多，一次執行的時間太長，等待的時間就越長，這也是我們為什么要分表的原因。

怎么單庫分表呢？

這邊只講兩種

1. 預先估計會出現大數據量并且訪問頻繁的表，將其分為若干個表(需要代碼層控制)

事先建100個這樣的表，message_00,message_01,message_02..........message_98,message_99.然后根據用戶的ID來判斷這個用戶的聊天信息放到哪張表里面，你可以用hash的方式來獲得，可以用求余的方式來獲得

2. 利用merge存儲引擎來實現分表

表一

表二

插入數據

建立表-注意看建表語句

查詢

效果

從上面的操作中，我不知道你有沒有發現點什么？假如我有一張用戶表user，有50W條數據，現在要拆成二張表user1和user2，每張表25W條數據，
INSERT INTO user1(user1.id,user1.name,user1.sex)
SELECT (user.id,user.name,user.sex)FROM user where user.id <= 250000
INSERT INTO user2(user2.id,user2.name,user2.sex)
SELECT (user.id,user.name,user.sex)FROM user where user.id > 250000
這樣我就成功的將一張user表，分成了二個表，這個時候有一個問題，代碼中的sql語句怎么辦，以前是一張表，現在變成二張表了，代碼改動很大，這樣給程序員帶來了很大的工作量，有沒有好的辦法解決這一點呢？辦法是把以前的user表備份一下，然后刪除掉，上面的操作中我建立了一個alluser表，只把這個alluser表的表名改成user就行了。但是，不是所有的mysql操作都能用的

分庫

推薦中間件 mycat

分庫與分表帶來的分布式困境與應對之策

數據遷移與擴容問題（通過程序先讀出數據，然后按照指定的分表策略再將數據寫入到各個分表中。）

表關聯問題（設計之初就應該盡量避免聯合查詢，可以通過程序中進行拼裝）

分頁與排序問題（需要在不同的分表中將數據進行排序并返回，并將不同分表返回的結果集進行匯總和再次排序，最后再返回給用戶）

分布式事務問題（目前，分布式事務并沒有很好的解決方案）

分布式全局唯一ID（ UUID）

說說 SQL 優化之道

常見的簡化規則如下：

1）不要有超過5個以上的表連接（JOIN）

2）考慮使用臨時表或表變量存放中間結果。

3）少用子查詢

4）視圖嵌套不要過深,一般視圖嵌套不要超過2個為宜。

這里只能挑簡單的說了

• 限制結果集（要盡量減少返回的結果行，包括行數和字段列數。）

• 合理的表設計

• 索引優化等等

MySQL 遇到的死鎖問題

死鎖一般是事務相互等待對方資源，最后形成環路造成的。

1.不同表相同記錄行鎖沖突

這種情況很好理解，事務A和事務B操作兩張表，但出現循環等待鎖情況。

2.相同表記錄行鎖沖突

這種情況比較常見，之前遇到兩個job在執行數據批量更新時，jobA處理的的id列表為[1,2,3,4]，而job處理的id列表為[8,9,10,4,2]，這樣就造成了死鎖。

3.不同索引鎖沖突

這種情況比較隱晦，事務A在執行時，除了在二級索引加鎖外，還會在聚簇索引上加鎖，在聚簇索引上加鎖的順序是[1,4,2,3,5]，而事務B執行時，只在聚簇索引上加鎖，加鎖順序是[1,2,3,4,5]，這樣就造成了死鎖的可能性。

4.gap鎖沖突

innodb在RR級別下，如下的情況也會產生死鎖，比較隱晦。不清楚的同學可以自行根據上節的gap鎖原理分析下。

如何避免死鎖

1）以固定的順序訪問表和行。比如對第2節兩個job批量更新的情形，簡單方法是對id列表先排序，后執行，這樣就避免了交叉等待鎖的情形；又比如對于3.1節的情形，將兩個事務的sql順序調整為一致，也能避免死鎖。

2）大事務拆小。大事務更傾向于死鎖，如果業務允許，將大事務拆小。

3）在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖概率。

4）降低隔離級別。如果業務允許，將隔離級別調低也是較好的選擇，比如將隔離級別從RR調整為RC，可以避免掉很多因為gap鎖造成的死鎖。

5）為表添加合理的索引。可以看到如果不走索引將會為表的每一行記錄添加上鎖，死鎖的概率大大增大。

存儲引擎的 InnoDB 與 MyISAM

1、事務處理

innodb 支持事務功能，myisam 不支持。

Myisam 的執行速度更快，性能更好。

2、select ,update ,insert ,delete 操作

MyISAM：如果執行大量的SELECT，MyISAM是更好的選擇

InnoDB：如果你的數據執行大量的INSERT或UPDATE，出于性能方面的考慮，應該使用InnoDB表

3、鎖機制不同

InnoDB 為行級鎖，myisam 為表級鎖。

注意：當數據庫無法確定，所找的行時，也會變為鎖定整個表。

如： update table set num = 10 where username like "%test%";

4、查詢表的行數不同

MyISAM：select count(*) from table,MyISAM只要簡單的讀出保存好的行數，注意的是，當count(*)語句包含where條件時，兩種表的操作是一樣的

InnoDB ： InnoDB 中不保存表的具體行數，也就是說，執行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行

5、物理結構不同

MyISAM ：每個MyISAM在磁盤上存儲成三個文件。第一個文件的名字以表的名字開始，擴展名指出文件類型。

.frm文件存儲表定義。

數據文件的擴展名為.MYD (MYData)。

索引文件的擴展名是.MYI (MYIndex)

InnoDB：基于磁盤的資源是InnoDB表空間數據文件和它的日志文件，InnoDB 表的大小只受限于操作系統文件的大小，一般為 2GB

6、anto_increment 機制不同

更好和更快的auto_increment處理

其他：為什么MyISAM會比Innodb 的查詢速度快

INNODB在做SELECT的時候，要維護的東西比MYISAM引擎多很多；

1）數據塊，INNODB要緩存，MYISAM只緩存索引塊， 這中間還有換進換出的減少；

2）innodb尋址要映射到塊，再到行，MYISAM 記錄的直接是文件的OFFSET，定位比INNODB要快

3）INNODB還需要維護MVCC一致；雖然你的場景沒有，但他還是需要去檢查和維護

MVCC ( Multi-Version Concurrency Control )多版本并發控制

InnoDB：通過為每一行記錄添加兩個額外的隱藏的值來實現MVCC，這兩個值一個記錄這行數據何時被創建，另外一個記錄這行數據何時過期（或者被刪除）。但是InnoDB并不存儲這些事件發生時的實際時間，相反它只存儲這些事件發生時的系統版本號。這是一個隨著事務的創建而不斷增長的數字。每個事務在事務開始時會記錄它自己的系統版本號。每個查詢必須去檢查每行數據的版本號與事務的版本號是否相同。讓我們來看看當隔離級別是REPEATABLE READ時這種策略是如何應用到特定的操作的：

SELECT InnoDB必須每行數據來保證它符合兩個條件：

1、InnoDB必須找到一個行的版本，它至少要和事務的版本一樣老(也即它的版本號不大于事務的版本號)。這保證了不管是事務開始之前，或者事務創建時，或者修改了這行數據的時候，這行數據是存在的。

2、這行數據的刪除版本必須是未定義的或者比事務版本要大。這可以保證在事務開始之前這行數據沒有被刪除。

為什要 用 B-tree

　B-Tree就是我們常說的B樹，一定不要讀成B減樹，否則就很丟人了。B樹這種數據結構常常用于實現數據庫索引，因為它的查找效率比較高 。理論太多了，全靠百度

聚集索引與 非聚集索引的區別

SQL SERVER提供了兩種索引：聚集索引和非聚集索引。其中聚集索引表示表中存儲的數據按照索引的順序存儲，檢索效率比非聚集索引高，但對數據更新影響較大。非聚集索引表示數據存儲在一個地方，索引存儲在另一個地方，索引帶有指針指向數據的存儲位置，非聚集索引檢索效率比聚集索引低，但對數據更新影響較小。

limit 20000 加載很慢怎么解決

舉個例子

日常分頁SQL語句

select id,name,content from users order by id asc limit 100000,20

掃描100020行

如果記錄了上次的最大ID

select id,name,content from users where id>100073 order by id asc limit 20

掃描20行。

1.子查詢優化法

先找出第一條數據，然后大于等于這條數據的id就是要獲取的數據

缺點：數據必須是連續的，可以說不能有where條件，where條件會篩選數據，導致數據失去連續性

select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100

從結果中可以得知，當偏移1000以上使用子查詢法可以有效的提高性能。

選擇合適的分布式主鍵 方案

1 不能有單點故障。

2 以時間為序，或者ID里包含時間。這樣一是可以少一個索引，二是冷熱數據容易分離。

3 可以控制ShardingId。比如某一個用戶的文章要放在同一個分片內，這樣查詢效率高，修改也容易。

4 不要太長，最好64bit。使用long比較好操作，如果是96bit，那就要各種移位相當的不方便，還有可能有些組件不能支持這么大的ID。

twitter

1 41位的時間序列（精確到毫秒，41位的長度可以使用69年）

2 10位的機器標識（10位的長度最多支持部署1024個節點）

3 12位的計數順序號（12位的計數順序號支持每個節點每毫秒產生4096個ID序號） 最高位是符號位，始終為0。

Flicker在解決全局ID生成方案里就采用了MySQL自增長ID的機制（auto_increment + replace into + MyISAM）。一個生成64位ID

UUID算法的核心思想是結合機器的網卡、當地時間、一個隨即數來生成UUID

基于redis的分布式ID生成器

MongoDB文檔（Document）全局唯一ID

聊聊 MongoDB 使 用場景

mongodb的主要目標是在鍵/值存儲方式（提供了高性能和高度伸縮性）以及傳統的RDBMS系統（豐富的功能）架起一座橋梁，集兩者的優勢于一身。mongo適用于以下場景：

a.網站數據：mongo非常適合實時的插入，更新與查詢，并具備網站實時數據存儲所需的復制及高度伸縮性。

b.緩存：由于性能很高，mongo也適合作為信息基礎設施的緩存層。在系統重啟之后，由mongo搭建的持久化緩存可以避免下層的數據源過載。

c.大尺寸、低價值的數據：使用傳統的關系數據庫存儲一些數據時可能會比較貴，在此之前，很多程序員往往會選擇傳統的文件進行存儲。

d.高伸縮性的場景：mongo非常適合由數十或者數百臺服務器組成的數據庫。

e.用于對象及JSON數據的存儲：mongo的BSON數據格式非常適合文檔格式化的存儲及查詢。

不適合的場景：

a.高度事物性的系統：例如銀行或會計系統。傳統的關系型數據庫目前還是更適用于需要大量原子性復雜事務的應用程序。

b.傳統的商業智能應用：針對特定問題的BI數據庫會對產生高度優化的查詢方式。對于此類應用，數據倉庫可能是更合適的選擇。

c.需要SQL的問題

倒排索引

常規的索引是文檔到關鍵詞的映射：

文檔——>關鍵詞

但是這樣檢索關鍵詞的時候很費力，要一個文檔一個文檔的遍歷一遍。（這事不能忍~）

于是人們發明了倒排索引~

倒排索引是關鍵詞到文檔的映射

關鍵詞——>文檔

這樣，只要有關鍵詞，立馬就能找到她在那個文檔里出現過，剩下的事就是把她揪出來了~~~

---