[[230870]]

MySQL 索引使用的注意事項(xiàng)

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

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

索引的類型：

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

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

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

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

• 組合索引：實(shí)質(zhì)上是將多個(gè)字段建到一個(gè)索引里，列值的組合必須唯一

SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不會(huì)使用索引,因?yàn)樗兴饕袇⑴c了計(jì)算

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不會(huì)使用索引,因?yàn)槭褂昧撕瘮?shù)運(yùn)算,原理與上面相同

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

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

-- 正則表達(dá)式不使用索引,這應(yīng)該很好理解,所以為什么在SQL中很難看到regexp關(guān)鍵字的原因

-- 字符串與數(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,即使其中有條件帶索引也不會(huì)使用。換言之,就是要求使用的所有字段,都必須建立索引, 我們建議大家盡量避免使用or 關(guān)鍵字

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

這里看去看我另外整理的一篇關(guān)于mysql優(yōu)化的 索引不生效替代辦法

說說反模式設(shè)計(jì)

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

什么是“反模式”

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

反模式分類

　?。?）邏輯數(shù)據(jù)庫設(shè)計(jì)反模式

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

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

　?。?）物理數(shù)據(jù)庫設(shè)計(jì)反模式

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

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

　?。?）查詢反模式

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

　　（4）應(yīng)用程序開發(fā)反模式

SQL應(yīng)該會(huì)用在Java、.Net、C++、Php等語言構(gòu)建的應(yīng)用程序中，在應(yīng)用程序中使用SQL的方式有好有壞。

反模式分解

　?。?）目的

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

　?。?）反模式

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

　?。?）如何識(shí)別反模式

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

都能使你提前識(shí)別出反模式。

　?。?）合理使用反模式

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

　　（5）解決方案

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

只能介紹些基礎(chǔ)的了

說說分庫與分表設(shè)計(jì)

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

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

    

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


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

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

表一

表二

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

建立表-注意看建表語句

查詢

效果
從上面的操作中，我不知道你有沒有發(fā)現(xiàn)點(diǎ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表，分成了二個(gè)表，這個(gè)時(shí)候有一個(gè)問題，代碼中的sql語句怎么辦，以前是一張表，現(xiàn)在變成二張表了，代碼改動(dòng)很大，這樣給程序員帶來了很大的工作量，有沒有好的辦法解決這一點(diǎn)呢？辦法是把以前的user表備份一下，然后刪除掉，上面的操作中我建立了一個(gè)alluser表，只把這個(gè)alluser表的表名改成user就行了。但是，不是所有的mysql操作都能用的
分庫
推薦中間件 mycat

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

    

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

    
合理的表設(shè)計(jì)
    
    

    
索引優(yōu)化等等
    

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

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

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

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

如何避免死鎖

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

存儲(chǔ)引擎的 InnoDB 與 MyISAM

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

為什要 用 B-tree

　B-Tree就是我們常說的B樹，一定不要讀成B減樹，否則就很丟人了。B樹這種數(shù)據(jù)結(jié)構(gòu)常常用于實(shí)現(xiàn)數(shù)據(jù)庫索引，因?yàn)樗牟檎倚时容^高 。理論太多了，全靠百度

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

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

limit 20000 加載很慢怎么解決
舉個(gè)例子
日常分頁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ù)
缺點(diǎn)：數(shù)據(jù)必須是連續(xù)的，可以說不能有where條件，where條件會(huì)篩選數(shù)據(jù)，導(dǎo)致數(shù)據(jù)失去連續(xù)性

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

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

Flicker在解決全局ID生成方案里就采用了MySQL自增長(zhǎng)ID的機(jī)制（auto_increment + replace into + MyISAM）。一個(gè)生成64位ID

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

基于redis的分布式ID生成器

MongoDB文檔（Document）全局唯一ID

聊聊 MongoDB 使 用場(chǎng)景

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

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