建站
咨詢
售后
建站咨詢
新聞中心
MySQL 索引使用的注意事項(xiàng)
索引的目的在于提高查詢效率?？梢灶惐茸值?，如果要查“mysql”這個單詞，我們肯定需要定位到m字母，然后從下往下找到y(tǒng)字母，再找到剩下的sql
INSERT 與 UPDATE 語句在擁有索引的表中執(zhí)行會花費(fèi)更多的時間，而SELECT 語句卻會執(zhí)行得更快。這是因?yàn)?，在進(jìn)行插入或更新時，數(shù)據(jù)庫也需要插入或更新索引值。
索引的類型：
-
UNIQUE(唯一索引)：不可以出現(xiàn)相同的值，可以有NULL值
-
INDEX(普通索引)：允許出現(xiàn)相同的索引內(nèi)容
-
PROMARY KEY(主鍵索引)：不允許出現(xiàn)相同的值
-
fulltext index(全文索引)：可以針對值中的某個單詞，但效率確實(shí)不敢恭維
-
組合索引：實(shí)質(zhì)上是將多個字段建到一個索引里，列值的組合必須唯一
SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不會使用索引,因?yàn)樗兴饕袇⑴c了計算
SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不會使用索引,因?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,即使其中有條件帶索引也不會使用。換言之,就是要求使用的所有字段,都必須建立索引, 我們建議大家盡量避免使用or 關(guān)鍵字
-- 如果mysql估計使用全表掃描要比使用索引快,則不使用索引
這里看去看我另外整理的一篇關(guān)于mysql優(yōu)化的 索引不生效替代辦法
說說反模式設(shè)計
這里的反模式針對的是數(shù)據(jù)庫
什么是“反模式”
反模式是一種試圖解決問題的方法，但通常會同時引發(fā)別的問題。
反模式分類
?。?）邏輯數(shù)據(jù)庫設(shè)計反模式
在開始編碼之前，需要決定數(shù)據(jù)庫中存儲什么信息以及最佳的數(shù)據(jù)組織方式和內(nèi)在關(guān)聯(lián)方式。
這包含了如何設(shè)計數(shù)據(jù)庫的表、字段和關(guān)系。
?。?）物理數(shù)據(jù)庫設(shè)計反模式
在確定了需要存儲哪些數(shù)據(jù)之后，使用你所知的RDBMS關(guān)系型數(shù)據(jù)庫技術(shù)特性盡可能高效地實(shí)現(xiàn)數(shù)據(jù)庫管理。
這包含了定義表和索引，以及選擇數(shù)據(jù)類型。也需要是要SQL的“數(shù)據(jù)定義語言”，比如Create Table語句。
（3）查詢反模式
SQL的查詢是使用“數(shù)據(jù)操作語言”來完成，比如：Insert、Select、Update和Delete語句。
（4）應(yīng)用程序開發(fā)反模式
SQL應(yīng)該會用在Java、.Net、C++、Php等語言構(gòu)建的應(yīng)用程序中，在應(yīng)用程序中使用SQL的方式有好有壞。
反模式分解
?。?）目的
這是你可能要去嘗試解決的任務(wù)。意圖使用反模式提供解決方案，但通常會以引起更多問題而告終。
?。?）反模式
這一部分表述了通常使用的解決方案的本質(zhì)，并且展示了那些沒有預(yù)知到的后果，正是這些使得這些方案成為反模式。
?。?）如何識別反模式
一些固定的方式會有助于你辨識在項(xiàng)目中使用的反模式。你遇到的特殊障礙，或是你自己和別人說的一些話，
都能使你提前識別出反模式。
（4）合理使用反模式
規(guī)則總有例外。在某些情況下，本來認(rèn)為是反模式的設(shè)計卻可能是合理的，或者說至少是所有的方案中最合理的。
?。?）解決方案
描述了選擇的最佳解決方案，他們不僅能夠解決原有的問題，同時也不至于引起由反模式導(dǎo)致的新問題。
只能介紹些基礎(chǔ)的了
說說分庫與分表設(shè)計
分表的目的就在于此，減小數(shù)據(jù)庫的負(fù)擔(dān)，縮短查詢時間。
mysql中有一種機(jī)制是表鎖定和行鎖定，為什么要出現(xiàn)這種機(jī)制，是為了保證數(shù)據(jù)的完整性;我舉個例子來說吧，如果有二個sql都要修改同一張表的同一條數(shù)據(jù)，這個時候怎么辦呢，是不是二個sql都可以同時修改這條數(shù)據(jù)呢？很顯然mysql對這種情況的處理是，一種是表鎖定（myisam存儲引擎），一個是行鎖定（innodb存儲引擎）。表鎖定表示你們都不能對這張表進(jìn)行操作，必須等我對表操作完才行。行鎖定也一樣，別的sql必須等我對這條數(shù)據(jù)操作完了，才能對這條數(shù)據(jù)進(jìn)行操作.如果數(shù)據(jù)太多，一次執(zhí)行的時間太長，等待的時間就越長，這也是我們?yōu)槭裁匆直淼脑颉?/pre>怎么單庫分表呢？
這邊只講兩種
預(yù)先估計會出現(xiàn)大數(shù)據(jù)量并且訪問頻繁的表，將其分為若干個表(需要代碼層控制)
事先建100個這樣的表，message_00,message_01,message_02..........message_98,message_99.然后根據(jù)用戶的ID來判斷這個用戶的聊天信息放到哪張表里面，你可以用hash的方式來獲得，可以用求余的方式來獲得
2.利用merge存儲引擎來實(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表，分成了二個表，這個時候有一個問題，代碼中的sql語句怎么辦，以前是一張表，現(xiàn)在變成二張表了，代碼改動很大，這樣給程序員帶來了很大的工作量，有沒有好的辦法解決這一點(diǎn)呢？辦法是把以前的user表備份一下，然后刪除掉，上面的操作中我建立了一個alluser表，只把這個alluser表的表名改成user就行了。但是，不是所有的mysql操作都能用的分庫
推薦中間件 mycat
分庫與分表帶來的分布式困境與應(yīng)對之策
數(shù)據(jù)遷移與擴(kuò)容問題（通過程序先讀出數(shù)據(jù)，然后按照指定的分表策略再將數(shù)據(jù)寫入到各個分表中。）
表關(guān)聯(lián)問題（設(shè)計之初就應(yīng)該盡量避免聯(lián)合查詢，可以通過程序中進(jìn)行拼裝）
分頁與排序問題（需要在不同的分表中將數(shù)據(jù)進(jìn)行排序并返回，并將不同分表返回的結(jié)果集進(jìn)行匯總和再次排序，最后再返回給用戶）
分布式事務(wù)問題（目前，分布式事務(wù)并沒有很好的解決方案）
分布式全局唯一ID（ UUID）
說說 SQL 優(yōu)化之道
常見的簡化規(guī)則如下：
1）不要有超過5個以上的表連接（JOIN）
2）考慮使用臨時表或表變量存放中間結(jié)果。
3）少用子查詢
4）視圖嵌套不要過深,一般視圖嵌套不要超過2個為宜。
這里只能挑簡單的說了
-
限制結(jié)果集（要盡量減少返回的結(jié)果行，包括行數(shù)和字段列數(shù)。）
-
合理的表設(shè)計
-
索引優(yōu)化等等
MySQL 遇到的死鎖問題
死鎖一般是事務(wù)相互等待對方資源，最后形成環(huán)路造成的。
1.不同表相同記錄行鎖沖突
這種情況很好理解，事務(wù)A和事務(wù)B操作兩張表，但出現(xiàn)循環(huán)等待鎖情況。
2.相同表記錄行鎖沖突
這種情況比較常見，之前遇到兩個job在執(zhí)行數(shù)據(jù)批量更新時，jobA處理的的id列表為[1,2,3,4]，而job處理的id列表為[8,9,10,4,2]，這樣就造成了死鎖。
3.不同索引鎖沖突
這種情況比較隱晦，事務(wù)A在執(zhí)行時，除了在二級索引加鎖外，還會在聚簇索引上加鎖，在聚簇索引上加鎖的順序是[1,4,2,3,5]，而事務(wù)B執(zhí)行時，只在聚簇索引上加鎖，加鎖順序是[1,2,3,4,5]，這樣就造成了死鎖的可能性。
4.gap鎖沖突
innodb在RR級別下，如下的情況也會產(chǎn)生死鎖，比較隱晦。不清楚的同學(xué)可以自行根據(jù)上節(jié)的gap鎖原理分析下。
如何避免死鎖
1）以固定的順序訪問表和行。比如對第2節(jié)兩個job批量更新的情形，簡單方法是對id列表先排序，后執(zhí)行，這樣就避免了交叉等待鎖的情形；又比如對于3.1節(jié)的情形，將兩個事務(wù)的sql順序調(diào)整為一致，也能避免死鎖。
2）大事務(wù)拆小。大事務(wù)更傾向于死鎖，如果業(yè)務(wù)允許，將大事務(wù)拆小。
3）在同一個事務(wù)中，盡可能做到一次鎖定所需要的所有資源，減少死鎖概率。
4）降低隔離級別。如果業(yè)務(wù)允許，將隔離級別調(diào)低也是較好的選擇，比如將隔離級別從RR調(diào)整為RC，可以避免掉很多因?yàn)間ap鎖造成的死鎖。
5）為表添加合理的索引。可以看到如果不走索引將會為表的每一行記錄添加上鎖，死鎖的概率大大增大。
存儲引擎的 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 為行級鎖，myisam 為表級鎖。
注意：當(dāng)數(shù)據(jù)庫無法確定，所找的行時，也會變?yōu)殒i定整個表。
如： update table set num = 10 where username like "%test%";
4、查詢表的行數(shù)不同
MyISAM：select count(*) from table,MyISAM只要簡單的讀出保存好的行數(shù)，注意的是，當(dāng)count(*)語句包含where條件時，兩種表的操作是一樣的
InnoDB ： InnoDB 中不保存表的具體行數(shù)，也就是說，執(zhí)行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行
5、物理結(jié)構(gòu)不同
MyISAM ：每個MyISAM在磁盤上存儲成三個文件。第一個文件的名字以表的名字開始，擴(kuò)展名指出文件類型。
.frm文件存儲表定義。
數(shù)據(jù)文件的擴(kuò)展名為.MYD (MYData)。
索引文件的擴(kuò)展名是.MYI (MYIndex)
InnoDB：基于磁盤的資源是InnoDB表空間數(shù)據(jù)文件和它的日志文件，InnoDB 表的大小只受限于操作系統(tǒng)文件的大小，一般為 2GB
6、anto_increment 機(jī)制不同
更好和更快的auto_increment處理
其他：為什么MyISAM會比Innodb 的查詢速度快
INNODB在做SELECT的時候，要維護(hù)的東西比MYISAM引擎多很多；
1）數(shù)據(jù)塊，INNODB要緩存，MYISAM只緩存索引塊， 這中間還有換進(jìn)換出的減少；
2）innodb尋址要映射到塊，再到行，MYISAM 記錄的直接是文件的OFFSET，定位比INNODB要快
3）INNODB還需要維護(hù)MVCC一致；雖然你的場景沒有，但他還是需要去檢查和維護(hù)
MVCC ( Multi-Version Concurrency Control )多版本并發(fā)控制
InnoDB：通過為每一行記錄添加兩個額外的隱藏的值來實(shí)現(xiàn)MVCC，這兩個值一個記錄這行數(shù)據(jù)何時被創(chuàng)建，另外一個記錄這行數(shù)據(jù)何時過期（或者被刪除）。但是InnoDB并不存儲這些事件發(fā)生時的實(shí)際時間，相反它只存儲這些事件發(fā)生時的系統(tǒng)版本號。這是一個隨著事務(wù)的創(chuàng)建而不斷增長的數(shù)字。每個事務(wù)在事務(wù)開始時會記錄它自己的系統(tǒng)版本號。每個查詢必須去檢查每行數(shù)據(jù)的版本號與事務(wù)的版本號是否相同。讓我們來看看當(dāng)隔離級別是REPEATABLE READ時這種策略是如何應(yīng)用到特定的操作的：
SELECT InnoDB必須每行數(shù)據(jù)來保證它符合兩個條件：
1、InnoDB必須找到一個行的版本，它至少要和事務(wù)的版本一樣老(也即它的版本號不大于事務(wù)的版本號)。這保證了不管是事務(wù)開始之前，或者事務(wù)創(chuàng)建時，或者修改了這行數(shù)據(jù)的時候，這行數(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提供了兩種索引：聚集索引和非聚集索引。其中聚集索引表示表中存儲的數(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ù)
缺點(diǎn)：數(shù)據(jù)必須是連續(xù)的，可以說不能有where條件，where條件會篩選數(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 以時間為序，或者ID里包含時間。這樣一是可以少一個索引，二是冷熱數(shù)據(jù)容易分離。
3 可以控制ShardingId。比如某一個用戶的文章要放在同一個分片內(nèi)，這樣查詢效率高，修改也容易。
4 不要太長，最好64bit。使用long比較好操作，如果是96bit，那就要各種移位相當(dāng)?shù)牟环奖?，還有可能有些組件不能支持這么大的ID。
1 41位的時間序列（精確到毫秒，41位的長度可以使用69年）
2 10位的機(jī)器標(biāo)識（10位的長度最多支持部署1024個節(jié)點(diǎn)）
3 12位的計數(shù)順序號（12位的計數(shù)順序號支持每個節(jié)點(diǎn)每毫秒產(chǎn)生4096個ID序號） 最高位是符號位，始終為0。
Flicker在解決全局ID生成方案里就采用了MySQL自增長ID的機(jī)制（auto_increment + replace into + MyISAM）。一個生成64位ID
UUID算法的核心思想是結(jié)合機(jī)器的網(wǎng)卡、當(dāng)?shù)貢r間、一個隨即數(shù)來生成UUID
基于redis的分布式ID生成器
MongoDB文檔（Document）全局唯一ID
聊聊 MongoDB 使 用場景
mongodb的主要目標(biāo)是在鍵/值存儲方式（提供了高性能和高度伸縮性）以及傳統(tǒng)的RDBMS系統(tǒng)（豐富的功能）架起一座橋梁，集兩者的優(yōu)勢于一身。mongo適用于以下場景：
a.網(wǎng)站數(shù)據(jù)：mongo非常適合實(shí)時的插入，更新與查詢，并具備網(wǎng)站實(shí)時數(shù)據(jù)存儲所需的復(fù)制及高度伸縮性。
b.緩存：由于性能很高，mongo也適合作為信息基礎(chǔ)設(shè)施的緩存層。在系統(tǒng)重啟之后，由mongo搭建的持久化緩存可以避免下層的數(shù)據(jù)源過載。
c.大尺寸、低價值的數(shù)據(jù)：使用傳統(tǒng)的關(guān)系數(shù)據(jù)庫存儲一些數(shù)據(jù)時可能會比較貴，在此之前，很多程序員往往會選擇傳統(tǒng)的文件進(jìn)行存儲。
d.高伸縮性的場景：mongo非常適合由數(shù)十或者數(shù)百臺服務(wù)器組成的數(shù)據(jù)庫。
e.用于對象及JSON數(shù)據(jù)的存儲：mongo的BSON數(shù)據(jù)格式非常適合文檔格式化的存儲及查詢。
不適合的場景：
a.高度事物性的系統(tǒng)：例如銀行或會計系統(tǒng)。傳統(tǒng)的關(guān)系型數(shù)據(jù)庫目前還是更適用于需要大量原子性復(fù)雜事務(wù)的應(yīng)用程序。
b.傳統(tǒng)的商業(yè)智能應(yīng)用：針對特定問題的BI數(shù)據(jù)庫會對產(chǎn)生高度優(yōu)化的查詢方式。對于此類應(yīng)用，數(shù)據(jù)倉庫可能是更合適的選擇。
c.需要SQL的問題
倒排索引
常規(guī)的索引是文檔到關(guān)鍵詞的映射：
文檔——>關(guān)鍵詞
但是這樣檢索關(guān)鍵詞的時候很費(fèi)力，要一個文檔一個文檔的遍歷一遍。（這事不能忍~）
于是人們發(fā)明了倒排索引~
倒排索引是關(guān)鍵詞到文檔的映射
關(guān)鍵詞——>文檔
這樣，只要有關(guān)鍵詞，立馬就能找到她在那個文檔里出現(xiàn)過，剩下的事就是把她揪出來了~~~
持續(xù)未完，關(guān)注是資料更新的動力?。?/p>
網(wǎng)站名稱：Java面試要點(diǎn)-數(shù)據(jù)存儲-精簡答案
文章分享：http://www.5511xx.com/article/dhgjhss.html
