[[230870]]

MySQL 索引使用的注意事項

索引的目的在于提高查詢效率?？梢灶惐茸值?，如果要查“mysql”這個單詞，我們肯定需要定位到m字母，然后從下往下找到y(tǒng)字母，再找到剩下的sql

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

索引的類型：

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

• INDEX(普通索引)：允許出現(xiàn)相同的索引內(nèi)容

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

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

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

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

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

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

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

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

-- 字符串與數(shù)字比較不使用索引;

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優(yōu)化的 索引不生效替代辦法

說說反模式設計

這里的反模式針對的是數(shù)據(jù)庫

什么是“反模式”

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

反模式分類

　　（1）邏輯數(shù)據(jù)庫設計反模式

在開始編碼之前，需要決定數(shù)據(jù)庫中存儲什么信息以及最佳的數(shù)據(jù)組織方式和內(nèi)在關聯(lián)方式。

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

　　（2）物理數(shù)據(jù)庫設計反模式

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

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

　?。?）查詢反模式

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

　?。?）應用程序開發(fā)反模式

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

反模式分解

　?。?）目的

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

　?。?）反模式

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

　?。?）如何識別反模式

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

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

　?。?）合理使用反模式

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

　?。?）解決方案

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

只能介紹些基礎的了

說說分庫與分表設計

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

mysql中有一種機制是表鎖定和行鎖定，為什么要出現(xiàn)這種機制，是為了保證數(shù)據(jù)的完整性;我舉個例子來說吧，如果有二個sql都要修改同一張表的同一條數(shù)據(jù)，這個時候怎么辦呢，是不是二個sql都可以同時修改這條數(shù)據(jù)呢？很顯然mysql對這種情況的處理是，一種是表鎖定（myisam存儲引擎），一個是行鎖定（innodb存儲引擎）。表鎖定表示你們都不能對這張表進行操作，必須等我對表操作完才行。行鎖定也一樣，別的sql必須等我對這條數(shù)據(jù)操作完了，才能對這條數(shù)據(jù)進行操作.如果數(shù)據(jù)太多，一次執(zhí)行的時間太長，等待的時間就越長，這也是我們?yōu)槭裁匆直淼脑颉?/pre>
怎么單庫分表呢？
這邊只講兩種

    

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


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

2. 利用merge存儲引擎來實現(xiàn)分表

表一

表二

插入數(shù)據(jù)

建立表-注意看建表語句

查詢

效果
從上面的操作中，我不知道你有沒有發(fā)現(xiàn)點什么？假如我有一張用戶表user，有50W條數(shù)據(jù)，現(xiàn)在要拆成二張表user1和user2，每張表25W條數(shù)據(jù)，
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語句怎么辦，以前是一張表，現(xiàn)在變成二張表了，代碼改動很大，這樣給程序員帶來了很大的工作量，有沒有好的辦法解決這一點呢？辦法是把以前的user表備份一下，然后刪除掉，上面的操作中我建立了一個alluser表，只把這個alluser表的表名改成user就行了。但是，不是所有的mysql操作都能用的
分庫
推薦中間件 mycat

分庫與分表帶來的分布式困境與應對之策
數(shù)據(jù)遷移與擴容問題（通過程序先讀出數(shù)據(jù)，然后按照指定的分表策略再將數(shù)據(jù)寫入到各個分表中。）
表關聯(lián)問題（設計之初就應該盡量避免聯(lián)合查詢，可以通過程序中進行拼裝）
分頁與排序問題（需要在不同的分表中將數(shù)據(jù)進行排序并返回，并將不同分表返回的結果集進行匯總和再次排序，最后再返回給用戶）
分布式事務問題（目前，分布式事務并沒有很好的解決方案）
分布式全局唯一ID（ UUID）
說說 SQL 優(yōu)化之道
常見的簡化規(guī)則如下：
1）不要有超過5個以上的表連接（JOIN）
2）考慮使用臨時表或表變量存放中間結果。
3）少用子查詢
4）視圖嵌套不要過深,一般視圖嵌套不要超過2個為宜。
這里只能挑簡單的說了

    

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

    
合理的表設計
    
    

    
索引優(yōu)化等等
    

MySQL 遇到的死鎖問題
死鎖一般是事務相互等待對方資源，最后形成環(huán)路造成的。
1.不同表相同記錄行鎖沖突
這種情況很好理解，事務A和事務B操作兩張表，但出現(xiàn)循環(huán)等待鎖情況。

2.相同表記錄行鎖沖突
這種情況比較常見，之前遇到兩個job在執(zhí)行數(shù)據(jù)批量更新時，jobA處理的的id列表為[1,2,3,4]，而job處理的id列表為[8,9,10,4,2]，這樣就造成了死鎖。

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

4.gap鎖沖突
innodb在RR級別下，如下的情況也會產(chǎn)生死鎖，比較隱晦。不清楚的同學可以自行根據(jù)上節(jié)的gap鎖原理分析下。

如何避免死鎖

1）以固定的順序訪問表和行。比如對第2節(jié)兩個job批量更新的情形，簡單方法是對id列表先排序，后執(zhí)行，這樣就避免了交叉等待鎖的情形；又比如對于3.1節(jié)的情形，將兩個事務的sql順序調(diào)整為一致，也能避免死鎖。
2）大事務拆小。大事務更傾向于死鎖，如果業(yè)務允許，將大事務拆小。
3）在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖概率。
4）降低隔離級別。如果業(yè)務允許，將隔離級別調(diào)低也是較好的選擇，比如將隔離級別從RR調(diào)整為RC，可以避免掉很多因為gap鎖造成的死鎖。
5）為表添加合理的索引。可以看到如果不走索引將會為表的每一行記錄添加上鎖，死鎖的概率大大增大。

存儲引擎的 InnoDB 與 MyISAM

1、事務處理
innodb 支持事務功能，myisam 不支持。
Myisam 的執(zhí)行速度更快，性能更好。
2、select ,update ,insert ,delete 操作
MyISAM：如果執(zhí)行大量的SELECT，MyISAM是更好的選擇
InnoDB：如果你的數(shù)據(jù)執(zhí)行大量的INSERT或UPDATE，出于性能方面的考慮，應該使用InnoDB表
3、鎖機制不同
InnoDB 為行級鎖，myisam 為表級鎖。
注意：當數(shù)據(jù)庫無法確定，所找的行時，也會變?yōu)殒i定整個表。
如： update table set num = 10 where username like "%test%";
4、查詢表的行數(shù)不同
MyISAM：select count(*) from table,MyISAM只要簡單的讀出保存好的行數(shù)，注意的是，當count(*)語句包含where條件時，兩種表的操作是一樣的
InnoDB ： InnoDB 中不保存表的具體行數(shù)，也就是說，執(zhí)行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行
5、物理結構不同
MyISAM ：每個MyISAM在磁盤上存儲成三個文件。第一個文件的名字以表的名字開始，擴展名指出文件類型。
.frm文件存儲表定義。
數(shù)據(jù)文件的擴展名為.MYD (MYData)。
索引文件的擴展名是.MYI (MYIndex)
InnoDB：基于磁盤的資源是InnoDB表空間數(shù)據(jù)文件和它的日志文件，InnoDB 表的大小只受限于操作系統(tǒng)文件的大小，一般為 2GB
6、anto_increment 機制不同
更好和更快的auto_increment處理
其他：為什么MyISAM會比Innodb 的查詢速度快
INNODB在做SELECT的時候，要維護的東西比MYISAM引擎多很多；
1）數(shù)據(jù)塊，INNODB要緩存，MYISAM只緩存索引塊， 這中間還有換進換出的減少；
2）innodb尋址要映射到塊，再到行，MYISAM 記錄的直接是文件的OFFSET，定位比INNODB要快
3）INNODB還需要維護MVCC一致；雖然你的場景沒有，但他還是需要去檢查和維護
MVCC ( Multi-Version Concurrency Control )多版本并發(fā)控制
InnoDB：通過為每一行記錄添加兩個額外的隱藏的值來實現(xiàn)MVCC，這兩個值一個記錄這行數(shù)據(jù)何時被創(chuàng)建，另外一個記錄這行數(shù)據(jù)何時過期（或者被刪除）。但是InnoDB并不存儲這些事件發(fā)生時的實際時間，相反它只存儲這些事件發(fā)生時的系統(tǒng)版本號。這是一個隨著事務的創(chuàng)建而不斷增長的數(shù)字。每個事務在事務開始時會記錄它自己的系統(tǒng)版本號。每個查詢必須去檢查每行數(shù)據(jù)的版本號與事務的版本號是否相同。讓我們來看看當隔離級別是REPEATABLE  READ時這種策略是如何應用到特定的操作的：
SELECT InnoDB必須每行數(shù)據(jù)來保證它符合兩個條件：
1、InnoDB必須找到一個行的版本，它至少要和事務的版本一樣老(也即它的版本號不大于事務的版本號)。這保證了不管是事務開始之前，或者事務創(chuàng)建時，或者修改了這行數(shù)據(jù)的時候，這行數(shù)據(jù)是存在的。
2、這行數(shù)據(jù)的刪除版本必須是未定義的或者比事務版本要大。這可以保證在事務開始之前這行數(shù)據(jù)沒有被刪除。

為什要 用 B-tree

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

聚集索引與 非聚集索引的區(qū)別

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

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.子查詢優(yōu)化法
先找出第一條數(shù)據(jù)，然后大于等于這條數(shù)據(jù)的id就是要獲取的數(shù)據(jù)
缺點：數(shù)據(jù)必須是連續(xù)的，可以說不能有where條件，where條件會篩選數(shù)據(jù)，導致數(shù)據(jù)失去連續(xù)性

select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100
從結果中可以得知，當偏移1000以上使用子查詢法可以有效的提高性能。
選擇合適的分布式主鍵 方案
1 不能有單點故障。
2 以時間為序，或者ID里包含時間。這樣一是可以少一個索引，二是冷熱數(shù)據(jù)容易分離。
3 可以控制ShardingId。比如某一個用戶的文章要放在同一個分片內(nèi)，這樣查詢效率高，修改也容易。
4 不要太長，最好64bit。使用long比較好操作，如果是96bit，那就要各種移位相當?shù)牟环奖?，還有可能有些組件不能支持這么大的ID。

twitter
1 41位的時間序列（精確到毫秒，41位的長度可以使用69年）
2 10位的機器標識（10位的長度最多支持部署1024個節(jié)點）
3 12位的計數(shù)順序號（12位的計數(shù)順序號支持每個節(jié)點每毫秒產(chǎn)生4096個ID序號） 最高位是符號位，始終為0。

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

UUID算法的核心思想是結合機器的網(wǎng)卡、當?shù)貢r間、一個隨即數(shù)來生成UUID

基于redis的分布式ID生成器

MongoDB文檔（Document）全局唯一ID

聊聊 MongoDB 使 用場景

mongodb的主要目標是在鍵/值存儲方式（提供了高性能和高度伸縮性）以及傳統(tǒng)的RDBMS系統(tǒng)（豐富的功能）架起一座橋梁，集兩者的優(yōu)勢于一身。mongo適用于以下場景：
a.網(wǎng)站數(shù)據(jù)：mongo非常適合實時的插入，更新與查詢，并具備網(wǎng)站實時數(shù)據(jù)存儲所需的復制及高度伸縮性。
b.緩存：由于性能很高，mongo也適合作為信息基礎設施的緩存層。在系統(tǒng)重啟之后，由mongo搭建的持久化緩存可以避免下層的數(shù)據(jù)源過載。
c.大尺寸、低價值的數(shù)據(jù)：使用傳統(tǒng)的關系數(shù)據(jù)庫存儲一些數(shù)據(jù)時可能會比較貴，在此之前，很多程序員往往會選擇傳統(tǒng)的文件進行存儲。
d.高伸縮性的場景：mongo非常適合由數(shù)十或者數(shù)百臺服務器組成的數(shù)據(jù)庫。
e.用于對象及JSON數(shù)據(jù)的存儲：mongo的BSON數(shù)據(jù)格式非常適合文檔格式化的存儲及查詢。
不適合的場景：
a.高度事物性的系統(tǒng)：例如銀行或會計系統(tǒng)。傳統(tǒng)的關系型數(shù)據(jù)庫目前還是更適用于需要大量原子性復雜事務的應用程序。
b.傳統(tǒng)的商業(yè)智能應用：針對特定問題的BI數(shù)據(jù)庫會對產(chǎn)生高度優(yōu)化的查詢方式。對于此類應用，數(shù)據(jù)倉庫可能是更合適的選擇。
c.需要SQL的問題

倒排索引
常規(guī)的索引是文檔到關鍵詞的映射：
 文檔——>關鍵詞
但是這樣檢索關鍵詞的時候很費力，要一個文檔一個文檔的遍歷一遍。（這事不能忍~）
于是人們發(fā)明了倒排索引~
倒排索引是關鍵詞到文檔的映射
 關鍵詞——>文檔
這樣，只要有關鍵詞，立馬就能找到她在那個文檔里出現(xiàn)過，剩下的事就是把她揪出來了~~~