定期分析表

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]

本語句用于分析和存儲表的關(guān)鍵字分布。在分析期間，使用一個讀取鎖定對表進行鎖定。這對于MyISAM, BDB和InnoDB表有作用。對于MyISAM表，本語句與使用myisamchk -a相當(dāng)。

MySQL使用已存儲的關(guān)鍵字分布來決定，當(dāng)您對除常數(shù)以外的對象執(zhí)行聯(lián)合時，表按什么順序進行聯(lián)合。

mysql> analyze table a;
+--------+---------+----------+-----------------------------+
| Table  | Op      | Msg_type | Msg_text                    |
+--------+---------+----------+-----------------------------+
| test.a | analyze | status   | Table is already up to date |
+--------+---------+----------+-----------------------------+
1 row in set (0.00 sec)

定期檢查表

CHECK TABLE tbl_name [, tbl_name]  [option]

option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
檢查一個或多個表是否有錯誤。CHECK TABLE對MyISAM和InnoDB表有作用。對于MyISAM表，關(guān)鍵字統(tǒng)計數(shù)據(jù)被更新。

mysql> check table a;
+--------+-------+----------+----------+
| Table  | Op    | Msg_type | Msg_text |
+--------+-------+----------+----------+
| test.a | check | status   | OK       |
+--------+-------+----------+----------+
1 row in set (0.00 sec)
CHECK TABLE也可以檢查視圖是否有錯誤，比如在視圖定義中被引用的表已不存在。
我們?yōu)樯厦娴谋韆創(chuàng)建一個視圖

mysql> create view a_view as select * from a;
Query OK, 0 rows affected (0.02 sec)

然后CHECK一下該視圖，發(fā)現(xiàn)沒有問題

mysql> check table a_view;
+-------------+-------+----------+----------+
| Table       | Op    | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| test.a_view | check | status   | OK       |
+-------------+-------+----------+----------+
1 row in set (0.00 sec)
現(xiàn)在刪掉視圖依賴的表

mysql> drop table a;
Query OK, 0 rows affected (0.01 sec)
再CHECK一下剛才的視圖，發(fā)現(xiàn)報錯了

mysql> check table a_view\G;
*************************** 1. row ***************************
   Table: test.a_view
      Op: check
Msg_type: Error
Msg_text: Table 'test.a' doesn't exist
*************************** 2. row ***************************
   Table: test.a_view
      Op: check
Msg_type: Error
Msg_text: View 'test.a_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
*************************** 3. row ***************************
   Table: test.a_view
      Op: check
Msg_type: error
Msg_text: Corrupt
3 rows in set (0.00 sec)

ERROR:
No query specified
定期優(yōu)化表

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]
如果您已經(jīng)刪除了表的一大部分，或者如果您已經(jīng)對含有可變長度行的表（含有VARCHAR, BLOB或TEXT列的表）進行了很多更改，則應(yīng)使用OPTIMIZE TABLE。被刪除的記錄被保持在鏈接清單中，后續(xù)的INSERT操作會重新使用舊的記錄位置。您可以使用OPTIMIZE TABLE來重新利用未使用的空間，并整理數(shù)據(jù)文件的碎片。
在多數(shù)的設(shè)置中，您根本不需要運行OPTIMIZE TABLE。即使您對可變長度的行進行了大量的更新，您也不需要經(jīng)常運行，每周一次或每月一次即可，只對特定的表運行。
OPTIMIZE TABLE只對MyISAM, BDB和InnoDB表起作用。
對于MyISAM表，OPTIMIZE TABLE按如下方式操作：
如果表已經(jīng)刪除或分解了行，則修復(fù)表。
如果未對索引頁進行分類，則進行分類。
如果表的統(tǒng)計數(shù)據(jù)沒有更新（并且通過對索引進行分類不能實現(xiàn)修復(fù)），則進行更新。

mysql> OPTIMIZE table a;
+--------+----------+----------+-----------------------------+
| Table  | Op       | Msg_type | Msg_text                    |
+--------+----------+----------+-----------------------------+
| test.a | optimize | status   | Table is already up to date |
+--------+----------+----------+-----------------------------+
1 row in set (0.00 sec)

****
以上某些的段落是直接摘自MySQL的中文手冊，詳細可以直接查看MySQL的幫助手冊，這里只是簡單指出幾種定期優(yōu)化的方式，需要注意的是無論是ANALYZE，CHECK還是OPTIMIZE在執(zhí)行期間將對表進行鎖定，因此請注意這些操作要在數(shù)據(jù)庫不繁忙的時候執(zhí)行

****
參考
《MySQL 5.1參考手冊》

by 陳于喆

show table status
mysql官方文檔在

http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

這里的rows行是表的行數(shù)，但是實際上是不準(zhǔn)的。myisam是準(zhǔn)的，其他的存儲引擎是不準(zhǔn)的。要準(zhǔn)確的行數(shù)就需要使用count(*) 來獲取了。

mysql執(zhí)行大批量刪除
執(zhí)行大批量刪除的時候注意要使用上limit

因為如果不用limit，刪除大量數(shù)據(jù)很有可能造成死鎖

如果delete的where語句不在索引上，可以先找主鍵，然后根據(jù)主鍵刪除數(shù)據(jù)庫

ps: 平時update和delete的時候最好也加上limit 1 來防止誤操作

optimize、Analyze、check、repair維護操作

optimize 數(shù)據(jù)在插入，更新，刪除的時候難免一些數(shù)據(jù)遷移，分頁，之后就出現(xiàn)一些碎片，久而久之碎片積累起來影響性能，這就需要DBA定期的優(yōu)化數(shù)據(jù)庫減少碎片，這就通過optimize命令。

如對MyisAM表操作：optimize table 表名

對于InnoDB表是不支持optimize操作，否則提示“Table does not support optimize, doing recreate + analyze instead”，當(dāng)然也可以通過命令：alter table one type=innodb; 來替代。

Analyze 用來分析和存儲表的關(guān)鍵字的分布，使得系統(tǒng)獲得準(zhǔn)確的統(tǒng)計信息，影響 SQL 的執(zhí)行計劃的生成。對于數(shù)據(jù)基本沒有發(fā)生變化的表，是不需要經(jīng)常進行表分析的。但是如果表的數(shù)據(jù)量變化很明顯，用戶感覺實際的執(zhí)行計劃和預(yù)期的執(zhí)行計劃不 同的時候，執(zhí)行一次表分析可能有助于產(chǎn)生預(yù)期的執(zhí)行計劃。

Analyze table 表名

Check檢查表或者視圖是否存在錯誤，對 MyISAM 和 InnoDB 存儲引擎的表有作用。對于 MyISAM 存儲引擎的表進行表檢查，也會同時更新關(guān)鍵字統(tǒng)計數(shù)據(jù)

Repair optimize需要有足夠的硬盤空間，否則可能會破壞表，導(dǎo)致不能操作，那就要用上repair，注意INNODB不支持repair操作
生成亂序的id
方法：

使用預(yù)設(shè)表

比如id和toid的映射

其中id是固定的，toid是隨機的。

然后在redis或memcache中記錄一個指針值，指向id

當(dāng)要獲取一個新toid的時候，取出指針值，加1，然后去預(yù)設(shè)表中獲取toid

查詢和索引
查詢的時候必須要考慮到如何命中索引

比如有幾個小招：

1 不要在索引列中使用表達式

where mycol *2 < 4

2 不要在like模式的開始位置使用通配符%

where col_name like ‘%string%'

不如

where col_name like ‘string%'

3 避免過多使用mysql自動轉(zhuǎn)換類型，有可能無法用到index

比如

select * from mytbl where str_col=4

但是str_col為字符串，這里其實就隱含了字符串變化

應(yīng)該使用

select * from mytbl where str_col='4'

索引比表還大就不需要建立索引了嗎
否

索引是按照順序排列的。所以即使索引比表大，也是可以加快查詢速度的。

當(dāng)然如果索引比表還大首要的任務(wù)必須是檢查下索引建立地是否有問題

Char和varchar如何選擇
char是定長，varchar變長
varchar除了設(shè)置了數(shù)據(jù)之外，還多使用1兩個字節(jié)定義了數(shù)據(jù)實際長度。

char會在后面空余的行填充上空字符串

myisam建議使用char。myisam中有個靜態(tài)表的概念。使用char比使用varchar的查詢效率高很多。

innodb建議使用varchar。主要是從節(jié)省空間的方面考慮

多個TimeStamp設(shè)置默認值
一個表中至多只能有一個字段設(shè)置CURRENT_TIMESTAMP

對于下面的需求：

一個表中，有兩個字段，createtime和updatetime。

1 當(dāng)insert的時候，sql兩個字段都不設(shè)置，會設(shè)置為當(dāng)前的時間
2 當(dāng)update的時候，sql中兩個字段都不設(shè)置，updatetime會變更為當(dāng)前的時間

這樣的需求是做不到的。因為你無法避免在兩個字段上設(shè)置CURRENT_TIMESTAMP 

解決辦法有幾個：

1 使用觸發(fā)器。

2 將第一個timestamp的default設(shè)置為0

3 老老實實在sql語句中使用時間戳。

http://www.dbjr.com.cn/article/31872.htm

查詢數(shù)據(jù)表有多少行，多少容量
不要使用select count(*)

使用show table status like ‘table_name'  但是innodb的話會有50%左右的浮動，是個預(yù)估值

AUTO_INCREMENT的設(shè)置

1 不要設(shè)置為int，請設(shè)置為unsinged int，auto_increment的范圍是根據(jù)類型來判定的
2 auto_increment數(shù)據(jù)列必須要有索引，并且保證唯一性。
3 auto_increment必須有NOT NULL屬性
4 auto_increment可以使用

UPDATE table SET seq = LAST_INSERT_ID(seq -1)

mysql的表示時間的字段用什么類型
表示時間可以使用timestamp和datetime來使用

datetime表示的時間可以從0000-00-00：00：00 到9999-12-31：00:00:00

timestamp表示的時間為1970-01-01 08：00：01到2038-01-19 11：14：07

timestamp占用的空間比datetime少，且可以設(shè)置時區(qū)等功能，所以能使用timestamp的地方盡量使用timestamp

使用timestamp還可以設(shè)置

[ON UPDATE CURRENT_TIMESTAMP]

[DEFAULT CURRENT_TIMESTAMP]

myisam和innodb支持外鍵
myisam不支持外鍵，innodb支持；

如果你使用創(chuàng)建外鍵的命令對myisam的表操作，操作不會返回失敗，但是是沒有外鍵關(guān)聯(lián)建立起來的。

對一個字段加減語句
經(jīng)常有需求對一個字段加減會使用

update table set a = a+1

這樣是對的

但是如果這樣設(shè)置：

select a from table

取出數(shù)據(jù)后a為1

update table set a =2

這樣會導(dǎo)致如果在select和update之間有其他事務(wù)操作修改這個字段的話，導(dǎo)致最后的設(shè)置可能出錯。

最新評論