建站
咨詢
售后
建站咨詢
新聞中心
一文讓你徹底弄懂MySQL自增列
MYSQL的自增列在實際生產(chǎn)中應(yīng)用的非常廣泛，相信各位所在的公司or團隊，MYSQL開發(fā)規(guī)范中一定會有要求盡量使用自增列去充當表的主鍵，為什么DBA會有這樣的要求，各位在使用MYSQL自增列時遇到過哪些問題？這些問題是由什么原因造成的呢？本文由淺入深，帶領(lǐng)大家徹底弄懂MYSQL的自增機制。
成都創(chuàng)新互聯(lián)公司是一家集網(wǎng)站建設(shè),昭蘇企業(yè)網(wǎng)站建設(shè),昭蘇品牌網(wǎng)站建設(shè),網(wǎng)站定制,昭蘇網(wǎng)站建設(shè)報價,網(wǎng)絡(luò)營銷,網(wǎng)絡(luò)優(yōu)化,昭蘇網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強企業(yè)競爭力。可充分滿足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時我們時刻保持專業(yè)、時尚、前沿，時刻以成就客戶成長自我，堅持不斷學習、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實用型網(wǎng)站。
1.? 通過auto_increment關(guān)鍵字來指定自增的列，并指定自增列的初始值為1。
[root@localhost][test1]Create table t(id int auto_increment ,namevarchar(10),primary key(id))auto_increment=1;
QueryOK, 0 rows affected (0.63 sec)
2.? 自增列上必須有索引，將t表的主鍵索引刪除掉，會報錯
[root@localhost][test1]alter table t drop primary key;
ERROR1075 (42000): Incorrect table definition; there can be only one auto column andit must be defined as a key
3.? 設(shè)定auto_increment_increment參數(shù)，可以調(diào)整自增步長，該參數(shù)有session級跟global級，在分庫分表以及雙主or多主的模式下比較有用。
4.? 一個表上只能有一個自增列
5.? Mysql5.7及以下版本，innodb表的自增值保存在內(nèi)存中，重啟后表的自增值會設(shè)為max(id)+1，而myisam引擎的自增值是保存在文件中，重啟不會丟失。Mysql8.0開始，innodb的自增id能持久化了，重啟mysql，自增ID不會丟。
首先：表中自增列的上限是根據(jù)自增列的字段類型來定的。
若設(shè)定了自增id充當主鍵，當達到了自增id的上限值時，會發(fā)生什么樣的事情呢？還是以上面創(chuàng)建的 t表為例， 先回顧它的表結(jié)構(gòu)：
CREATETABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) COLLATE utf8mb4_binDEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
無符號的int類型，上限是2147483647。這里我們將表的自增值設(shè)為2147483647，再插入兩行數(shù)據(jù)：
[root@localhost][test1]alter table t auto_increment=2147483647;
QueryOK, 0 rows affected (0.01 sec)
Records:0? Duplicates: 0? Warnings: 0
[root@localhost][test1]insert into t(name) values ('test');??????????
QueryOK, 1 row affected (0.01 sec)
[root@localhost][test1]insert into t(name) values ('test');
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'
可以看到，第一個插入沒問題，因為自增列的值為2147483647，這是達到了上限，還沒有超過，第二行數(shù)據(jù)插入時，則報出主鍵重復(fù)，在達到上限后，無法再分配新的更大的自增值，也沒有從1開始從頭分配，在這里表的auto_increment值會一直是2147483647。
對于寫入量大，且經(jīng)常刪除數(shù)據(jù)的表，自增id設(shè)為int類型還是偏小的，所以我們?yōu)榱吮苊獬霈F(xiàn)自增id漲滿的情況，這邊統(tǒng)一建議自增id的類型設(shè)為unsigned bingint，這樣基本可以保障表的自增id是永遠夠用的。
這里內(nèi)容比較多，innodb是索引組織表，所以涉及到索引的知識，但這不是本文的重點，我們快速回顧索引知識：
1.? Innodb索引分為主鍵跟輔助索引，主鍵即全表，輔助索引葉子節(jié)點保存主鍵的值，而主鍵的葉子節(jié)點保存數(shù)據(jù)行，中間節(jié)點存著葉子節(jié)點的路由值。
2.? Innodb存儲數(shù)據(jù)（索引）的單位是頁，這里默認是16K，這也意味著，數(shù)據(jù)本身越小，一個頁中能存數(shù)據(jù)的量越多，而檢索效率不僅僅由索引的層數(shù)來決定，更是由一次能夠緩存的數(shù)據(jù)量來定，也就是說數(shù)據(jù)本身越小，則一次IO能夠提取到緩沖區(qū)的數(shù)據(jù)越多（OS每次IO的量是固定的4K），查詢的效率越好。
其實能夠理解索引的結(jié)構(gòu)及索引寫入插入、更新的原理，則自然就明白為何建議使用自增id。這里我直接列出使用自增id 當主鍵的好處吧：
1.? 順序?qū)懭耄苊饬巳~的分裂，數(shù)據(jù)寫入效率好
2.? 縮小了表的體積，特別是相比于UUID當主鍵，甚至組合字段當主鍵時，效果更明顯
3.? 查詢效率好，原因就是我上面說到索引知識的第二點。
4.? 某些情況下，我們可以利用自增id來統(tǒng)計大表的大致行數(shù)。
5.? 在數(shù)據(jù)歸檔or垃圾數(shù)據(jù)清理時，也可方便的利用這個id去操作，效率高。
容易出現(xiàn)不連續(xù)的id
有的同志會發(fā)現(xiàn)，自己的表中id值存在空洞，如類似于1、2、3、8、9、10這樣，有的適合有想依賴于自增id的連續(xù)性來實現(xiàn)業(yè)務(wù)邏輯，所以會想方設(shè)法去修改id讓其變的連續(xù)，其實，這是沒有必要的，這一塊的業(yè)務(wù)邏輯交由MySQL實現(xiàn)是很不理智的，表的記錄小還好，要是表的數(shù)據(jù)量很大，修改起來就糟糕了。那么，為什么自增id會容易出現(xiàn)空洞呢？
自增id的修改機制如下：
在MySQL里面，如果字段id被定義為AUTO_INCREMENT，在插入一行數(shù)據(jù)的時候，自增值的行為如下：
1. 如果插入數(shù)據(jù)時id字段指定為0、null 或未指定值，那么就把這個表當前的
AUTO_INCREMENT值填到自增字段；
2. 如果插入數(shù)據(jù)時id字段指定了具體的值，就直接使用語句里指定的值。
根據(jù)要插入的值和當前自增值的大小關(guān)系，自增值的變更結(jié)果也會有所不同。假設(shè)，某次要插入的值是X，當前的自增值是Y。
1. 如果XY，那么這個表的自增值不變；
2. 如果X≥Y，就需要把當前自增值修改為 新的自增值 。
新的自增值生成算法是：從auto_increment_offset開始，以auto_increment_increment為步長，持續(xù)疊加，直到找到第一個大于X的值，作為新的自增值。
Insert、update、delete操作會讓id不連續(xù)。
Delete、update：刪除中間數(shù)據(jù)，會造成空動，而修改自增id值，也會造成空洞（這個很少）。
Insert：插入報錯（唯一鍵沖突與事務(wù)回滾），會造成空洞，因為這時候自增id已經(jīng)分配出去了，新的自增值已經(jīng)生成，如下面例子：
[root@localhost][test1] select * fromt;
+----+------+
| id | name |
+----+------+
|? 1| aaa? |
|? 2| aaa? |
|? 3| aaa? |
|? 4| aaa? |
+----+------+
4 rows in set (0.00 sec)
[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';
+----------------+
| Auto_increment |
+----------------+
|????????????? 5 |
+----------------+
1 row in set (0.00 sec)
[root@localhost][test1] begin;
Query OK, 0 rows affected (0.00 sec)
[root@localhost][test1] insert intot(name) values('aaa');
Query OK, 1 row affected (0.00 sec)
[root@localhost][test1] select * fromt;
+----+------+
| id | name |
+----+------+
|? 1| aaa? |
|? 2| aaa? |
|? 3| aaa? |
|? 4| aaa? |
|? 5| aaa? |
+----+------+
5 rows in set (0.00 sec)
[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';
+----------------+
| Auto_increment |
+----------------+
|????????????? 6 |
+----------------+
1 row in set (0.00 sec)
[root@localhost][test1] rollback;
Query OK, 0 rows affected (0.00 sec)
[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';
+----------------+
| Auto_increment |
+----------------+
|????????????? 6 |
+----------------+
1 row in set (0.01 sec)
[root@localhost][test1] select * fromt;
+----+------+
| id | name |
+----+------+
|? 1| aaa? |
|? 2| aaa? |
|? 3| aaa? |
|? 4| aaa? |
+----+------+
4 rows in set (0.00 sec)
可以看到，雖然事務(wù)回滾了，但自增id已經(jīng)回不到從前啦，唯一鍵沖突也是這樣的，這里就不做測試了。
在批量插入時（insert select等），也存在空洞的問題。看下面實驗：
[root@localhost][test1] select * fromt;
+----+------+
| id | name |
+----+------+
|? 1| aaa? |
|? 2| aaa? |
|? 3| aaa? |
|? 4| aaa? |
+----+------+
4 rows in set (0.00 sec)
[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';
+----------------+
| Auto_increment |
+----------------+
|????????????? 5 |
+----------------+
1 row in set (0.00 sec)
[root@localhost][test1] insert intot(name) select name from t;??????????????????????
Query OK, 4 rows affected (0.04 sec)
Records: 4?Duplicates: 0? Warnings: 0
[root@localhost][test1] select * fromt;
+----+------+
| id | name |
+----+------+
|? 1| aaa? |
|? 2| aaa? |
|? 3| aaa? |
|? 4| aaa? |
|? 5| aaa? |
|? 6| aaa? |
|? 7| aaa? |
|? 8| aaa? |
+----+------+
8 rows in set (0.00 sec)
[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';
+----------------+
| Auto_increment |
+----------------+
|???????????? 12 |
+----------------+
1 row in set (0.00 sec)
可以看到，批量插入，導(dǎo)致下一個id值不為9了，再插入數(shù)據(jù)，即產(chǎn)生了空洞，這里是由mysql申請自增值的機制所造成的，MySQL在批量插入時，若一個值申請一個id，效率太慢，影響了批量插入的速度，故mysql采用下面的策略批量申請id。
1.? 語句執(zhí)行過程中，第一次申請自增id，會分配1個；
2.? 1個用完以后，這個語句第二次申請自增id，會分配2個；
3.? 2個用完以后，還是這個語句，第三次申請自增id，會分配4個；
4.? 依此類推，同一個語句去申請自增id，每次申請到的自增id個數(shù)都是上一次的兩倍。
在對自增列進行操作時，存在著自增鎖，mysql的innodb_autoinc_lock_mode參數(shù)控制著自增鎖的上鎖機制。該參數(shù)有0、1、2三種模式：
0：語句執(zhí)行結(jié)束后釋放自增鎖，MySQL5.0時采用這種模式，并發(fā)度較低。
1：mysql的默認設(shè)置。普通的insert語句申請后立馬釋放，insert select、replace insert、load data等批量插入語句要等語句執(zhí)行結(jié)束后才釋放，并發(fā)讀得到提升
2：所有的語句都是申請后立馬釋放，并發(fā)度大大提升！但是在binlog為statement格式時，主從數(shù)據(jù)會發(fā)生不一致。這一塊網(wǎng)上有很多介紹，我不做介紹了。
在徹底了解了MYSQL的自增機制以后，在實際生產(chǎn)中就能靈活避坑，這里建議不要用自增id值去當表的行數(shù)，當需要對大表準確統(tǒng)計行數(shù)時，可以去count(*)從庫，如果業(yè)務(wù)很依賴大表的準確行數(shù)，直接弄個中間表來統(tǒng)計，或者考慮要不要用mysql的innodb來存儲數(shù)據(jù)，這個是需要自己去權(quán)衡。另外對于要求很高的寫入性能，但寫入量又比較大的業(yè)務(wù)，自增id的使用依然存在熱點寫入的問題，存在性能瓶頸，這時候可通過分庫分表來解決。
想在mysql數(shù)據(jù)庫中的表中插入一列，怎么做？
傳統(tǒng)情況
我們先回顧一下，在沒有 "立刻加列" 功能時，加列操作是怎么完成的。我們也借此來熟悉一下本期的圖例：
當進行 加列操作 時，所有的數(shù)據(jù)行 都必須要 增加一段數(shù)據(jù)（圖中的 列 4 數(shù)據(jù)）
如上一期圖解所講，當改變數(shù)據(jù)行的長度，就需要 重建表空間（圖中灰藍的部分為發(fā)生變更的部分）
數(shù)據(jù)字典中的列定義也會被更新
以上操作的問題在于 每次加列 操作都需要重建表空間，這就需要大量 IO以及大量的時間
立刻加列
"立刻加列" 的過程如下圖：
請點擊輸入圖片描述
請點擊輸入圖片描述
"立刻加列" 時，只會變更數(shù)據(jù)字典中的內(nèi)容，包括：
在列定義中增加 新列的定義
增加 新列的默認值
"立刻加列"?后，當要讀取表中的數(shù)據(jù)時：
由于 "立刻加列" 沒有 變更行數(shù)據(jù)，讀取的行數(shù)據(jù)只有 3 列
MySQL 會將 新增的第 4 列的默認值，追加到 讀取的數(shù)據(jù)后
以上過程描述了 如何讀取?在 "立刻加列" 之前寫入的數(shù)據(jù)，其實質(zhì)是：在讀取數(shù)據(jù)的過程中，"偽造"?了一個新列出來
那么如何讀取?在 "立刻加列" 之后?寫入的數(shù)據(jù)呢 ? 過程如下圖：
當讀取 行 4 時：
請點擊輸入圖片描述
請點擊輸入圖片描述
通過判斷?數(shù)據(jù)行的頭信息中的instant?標志位，可以知道該行的格式是 "新格式"：該行頭信息后有一個新字段?"列數(shù)"
通過讀取?數(shù)據(jù)行的?"列數(shù)"?字段，可以知道 該行數(shù)據(jù)中多少列有 "真實" 的數(shù)據(jù)，從而按列數(shù)讀取數(shù)據(jù)
通過上圖可以看到：讀取?在"立刻加列"?前/后寫入的數(shù)據(jù)是不同的流程
通過以上的討論，我們可以總結(jié)?"立刻加列"?之所以高效的原因是：
在執(zhí)行?"立刻加列"?時，不變更數(shù)據(jù)行的結(jié)構(gòu)
讀取 "舊" 數(shù)據(jù)時，"偽造"?新增的列，使結(jié)果正確
寫入 "新" 數(shù)據(jù)時，使用了新的數(shù)據(jù)格式（增加了instant標志位 和?"列數(shù)"?字段)，以區(qū)分新舊數(shù)據(jù)
讀取 "新" 數(shù)據(jù)時，可以如實讀取數(shù)據(jù)
那么?我們是否能一直 "偽造"?下去???"偽造"?何時會被拆穿 ?
考慮以下場景：
用 "立刻加列" 增加列 A
寫入數(shù)據(jù)行 1
用 "立刻加列" 增加列?B
寫入數(shù)據(jù)行?2
刪除列?B
我們推測一下 "刪除列 B" 的最小代價：需要修改 數(shù)據(jù)行中的instant標志位或?"列數(shù)"?字段，這至少會影響到?"立刻加列"?之后寫入的數(shù)據(jù)行，成本類似于重建數(shù)據(jù)
從以上推測可知：當出現(xiàn) 與?"立刻加列"?操作不兼容 的 DDL 操作時，數(shù)據(jù)表需要進行重建，如下圖所示：
請點擊輸入圖片描述
請點擊輸入圖片描述
擴展思考題：是否能設(shè)計其他的數(shù)據(jù)格式，取代instant標志位和?"列數(shù)"?字段，使得 加列/刪列 操作都能 "立刻完成" ?（提示：考慮 加列?- 刪列?- 再加列 的情況）
使用限制
在了解原理之后，我們來看看?"立刻加列"?的使用限制，就很容易能理解其中的前兩項：
"立刻加列"?的加列位置只能在表的最后，而不能加在其他列之間
在元數(shù)據(jù)中，只記錄了 數(shù)據(jù)行 應(yīng)有多少列，而沒有記錄 這些列 應(yīng)出現(xiàn)的位置。所以無法實現(xiàn)指定列的位置
"立刻加列"?不能添加主鍵列
加列 不能涉及聚簇索引的變更，否則就變成了 "重建" 操作，不是 "立刻" 完成了
"立刻加列"不支持壓縮的表格式
按照 WL 的說法："COMPRESSED is no need to supported"（沒必要支持不怎么用的格式）
總結(jié)回顧
我們總結(jié)一下上面的討論：
"立刻加列" 之所以高效的原因是：
在執(zhí)行 "立刻加列" 時，不變更數(shù)據(jù)行的結(jié)構(gòu)
讀取 "舊" 數(shù)據(jù)時，"偽造"?新增的列，使結(jié)果正確
寫入 "新" 數(shù)據(jù)時，使用了新的數(shù)據(jù)格式?(增加了?instant 標志位?和 "列數(shù)" 字段)，以區(qū)分新舊數(shù)據(jù)
讀取 "新" 數(shù)據(jù)時，可以如實讀取數(shù)據(jù)
"立刻加列"?的 "偽造" 手法，不能一直維持下去。當發(fā)生?與 "立刻加列" 操作不兼容?的 DDL?時，表數(shù)據(jù)就會發(fā)生重建
回到之前遺留的兩個問題：
"立刻加列" 是如何工作的 ?
我們已經(jīng)解答了這個問題
所謂 "立刻加列" 是否完全不影響業(yè)務(wù)，是否是真正的 "立刻" 完成 ?
可以看到：就算是 "立刻加列"，也需要變更 數(shù)據(jù)字典，那么 該上的鎖還是逃不掉的。也就是說 這里的 "立刻" 指的是 "不變更數(shù)據(jù)行的結(jié)構(gòu)"，而并非指 "零成本地完成任務(wù)"
mysql給表增加字段會鎖表，怎樣才可以不鎖表嗎
鎖表一般是長時間占用表導(dǎo)致的，
試著使SELECT語句運行得更快；你可能必須創(chuàng)建一些摘要(summary)表做到這點。
用--low-priority-updates啟動mysqld。這將給所有更新(修改)一個表的語句以比SELECT語句低的優(yōu)先級。在這種情況下，在先前情形的最后的SELECT語句將在INSERT語句前執(zhí)行。
你可以用LOW_PRIORITY屬性給與一個特定的INSERT、UPDATE或DELETE語句較低優(yōu)先級。
為max_write_lock_count指定一個低值來啟動mysqld使得在一定數(shù)量的WRITE鎖定后給出READ鎖定。
通過使用SQL命令：SET SQL_LOW_PRIORITY_UPDATES=1，你可從一個特定線程指定所有的更改應(yīng)該由用低優(yōu)先級完成
mysql給表增加字段會鎖表,怎樣才可以不鎖表嗎?
這個是屬于系統(tǒng)遺留問題，也就是一種系統(tǒng)的保護機制。就是為了避免出現(xiàn)這種在線修改系統(tǒng)的操作。
增加字段屬于系統(tǒng)的修改操作。盡量不要在線操作，因為可能出現(xiàn)。未知的漏洞。一定要。離線。修改完畢，然后經(jīng)過測試后。認為已經(jīng)沒有問題了。在。次日的凌晨發(fā)一個通知。停機維護。這樣才能保證系統(tǒng)的正常運轉(zhuǎn)。
如果在前期設(shè)置系統(tǒng)的時候就預(yù)留了。熱升級的空間。這樣才能達到在線操作的目的，而且系統(tǒng)的金融群總是一部分先升級。
很多情況下，你需要使用系統(tǒng)里邊的工具集。在線修改表格。原理其實非常的簡單，新建的和原表的表格結(jié)構(gòu)。要一模一樣。對這個表格進行修改，然后把結(jié)構(gòu)變更的日期。插入進去。而且還建議您盡量在業(yè)務(wù)的低縫隙進行修改。避免發(fā)生不可控的未知狀況。
使用說明：
1、如果是用 MySQL + Apache，使用的又是 FreeBSD 網(wǎng)絡(luò)操作系統(tǒng)的話，安裝時候你應(yīng)按注意到FreeBSD的版本問題，在FreeBSD 的 3.0 以下版本來說，MySQL Source 內(nèi)含的 MIT-pthread 運行是正常的，但在這版本以上，你必須使用 native threads。
2、如果在 COMPILE 過程中出了問題，請先檢查你的 gcc版本是否在 2.81 版本以上，gmake 版本是否在3.75以上。
3、如果不是版本的問題，那可能是你的內(nèi)存不足，請使用configure--with-low-memory 來加入。
4、如果要重新做你的configure，那么你可以鍵入rm config.cache和make clean來清除記錄。
5、把 MySQL 安裝在 /usr/local 目錄下，這是缺省值，您也可以按照你的需要設(shè)定你所安裝的目錄。
mysql主不鎖定的狀況下怎么添加新的從節(jié)點
目前的基本環(huán)境：
主DB：192.168.1.101
從DB1：192.168.1.102
從DB2：192.168.1.103(準備在線添加做為從機的mysql數(shù)據(jù)庫)
數(shù)據(jù)庫版本：mysql 5.1.61(select version())
服務(wù)器版本：red hat linux 6
注：目前主從正常(show slave statusG)，同步的數(shù)據(jù)庫為 login
3臺DB server的備份軟件(xtrbackup)都已經(jīng)安裝完成
同步的表為innodb引擎的表，不然用xtrabackup還是會鎖表
假設(shè)環(huán)境為：現(xiàn)在login庫下的表a,備份的時候插入數(shù)據(jù)，最終行為5418272
注：思路是備份，可是用mysqldump備份會鎖表，如果數(shù)據(jù)量很大的話，線上環(huán)境肯定不允許使用mysqldump備份！
mysql自增id列怎么設(shè)置？
create table cdat
(
localt char(20) not null,
cd char(5) not null,
snosat char(2) not null,
rnorec char(3) not null,
id INT(20) not null AUTO_INCREMENT,
primary key (id)
);
MySQL是一個開放源碼的小型關(guān)聯(lián)式數(shù)據(jù)庫管理系統(tǒng)，開發(fā)者為瑞典MySQL AB公司。目前MySQL被廣泛地應(yīng)用在Internet上的中小型網(wǎng)站中。由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，許多中小型網(wǎng)站為了降低網(wǎng)站總體擁有成本而選擇了MySQL作為網(wǎng)站數(shù)據(jù)庫。
系統(tǒng)特性
1．使用C和C++編寫，并使用了多種編譯器進行測試，保證源代碼的可移植性
2．支持AIX、FreeBSD、HP-UX、Linux、Mac OS、NovellNetware、OpenBSD、OS/2 Wrap、Solaris、Windows等多種操作系統(tǒng)
3．為多種編程語言提供了API。這些編程語言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
4．支持多線程，充分利用CPU資源
5．優(yōu)化的SQL查詢算法，有效地提高查詢速度
6．既能夠作為一個單獨的應(yīng)用程序應(yīng)用在客戶端服務(wù)器網(wǎng)絡(luò)環(huán)境中，也能夠作為一個庫而嵌入到其他的軟件中。
7．提供多語言支持，常見的編碼如中文的GB 2312、BIG5，日文的Shift_JIS等都可以用作數(shù)據(jù)表名和數(shù)據(jù)列名。
8．提供TCP/IP、ODBC和JDBC等多種數(shù)據(jù)庫連接途徑。
9．提供用于管理、檢查、優(yōu)化數(shù)據(jù)庫操作的管理工具。
10．支持大型的數(shù)據(jù)庫。可以處理擁有上千萬條記錄的大型數(shù)據(jù)庫。
11．支持多種存儲引擎。
索引功能
索引是一種特殊的文件（InnoDB數(shù)據(jù)表上的索引是表空間的一個組成部分），它們包含著對數(shù)據(jù)表里所有記錄的引用指針。索引不是萬能的，索引可以加快數(shù)據(jù)檢索操作，但會使數(shù)據(jù)修改操作變慢。每修改數(shù)據(jù)記錄，索引就必須刷新一次。為了在某種程序上彌補這一缺陷，許多SQL命令都有一個DELAY_KEY_WRITE項。這個選項的作用是暫時制止MySQL在該命令每插入一條新記錄和每修改一條現(xiàn)有之后立刻對索引進行刷新，對索引的刷新將等到全部記錄插入/修改完畢之后再進行。在需要把許多新記錄插入某個數(shù)據(jù)表的場合，DELAY_KEY_WRITE選項的作用將非常明顯。另外，索引還會在硬盤上占用相當大的空間。因此應(yīng)該只為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)列建立索引。注意，如果某個數(shù)據(jù)列包含許多重復(fù)的內(nèi)容，為它建立索引就沒有太大的實際效果。從理論上講，完全可以為數(shù)據(jù)表里的每個字段分別建一個索引，但MySQL把同一個數(shù)據(jù)表里的索引總數(shù)限制為16個。
1．InnoDB數(shù)據(jù)表的索引
與InnoDB數(shù)據(jù)表相比，在InnoDB數(shù)據(jù)表上，索引對InnoDB數(shù)據(jù)表的重要性要大得多。在InnoDB數(shù)據(jù)表上，索引不僅會在搜索數(shù)據(jù)記錄時發(fā)揮作用，還是數(shù)據(jù)行級鎖定機制的苊、基礎(chǔ)。“數(shù)據(jù)行級鎖定”的意思是指在事務(wù)操作的執(zhí)行過程中鎖定正在被處理的個別記錄，不讓其他用戶進行訪問。這種鎖定將影響到（但不限于）SELECT、LOCKINSHAREMODE、SELECT、FORUPDATE命令以及INSERT、UPDATE和DELETE命令。出于效率方面的考慮，InnoDB數(shù)據(jù)表的數(shù)據(jù)行級鎖定實際發(fā)生在它們的索引上，而不是數(shù)據(jù)表自身上。顯然，數(shù)據(jù)行級鎖定機制只有在有關(guān)的數(shù)據(jù)表有一個合適的索引可供鎖定的時候才能發(fā)揮效力。
2．限制
如果WHERE子句的查詢條件里有不等號（WHEREcoloum!=），MySQL將無法使用索引。類似地，如果WHERE子句的查詢條件里使用了函數(shù)（WHEREDAY（column）=），MySQL也將無法使用索引。在JOIN操作中（需要從多個數(shù)據(jù)表提取數(shù)據(jù)時），MySQL只有在主鍵和外鍵的數(shù)據(jù)類型相同時才能使用索引。
如果WHERE子句的查詢條件里使用比較操作符LIKE和REGEXP，MySQL只有在搜索模板的第一個字符不是通配符的情況下才能使用索引。比如說，如果查詢條件是LIKE'abc%‘，MySQL將使用索引；如果查詢條件是LIKE'%abc’，MySQL將不使用索引。
在ORDERBY操作中，MySQL只有在排序條件不是一個查詢條件表達式的情況下才使用索引。（雖然如此，在涉及多個數(shù)據(jù)表查詢里，即使有索引可用，那些索引在加快ORDERBY方面也沒什么作用）。如果某個數(shù)據(jù)列里包含許多重復(fù)的值，就算為它建立了索引也不會有很好的效果。比如說，如果某個數(shù)據(jù)列里包含的凈是些諸如“0/1”或“Y/N”等值，就沒有必要為它創(chuàng)建一個索引。
網(wǎng)站標題：mysql不鎖表怎么去增加列,mysql加索引不鎖表
文章地址：http://www.ef60e0e.cn/article/hojjgd.html
