目錄

選擇優化的數據類型

選擇數據類型的原則

數據類型

整數

實數

字符串

日期和時間

選擇標識符

schema設計中的陷阱

范式和反范式

緩存表和匯總表

物化視圖

計算器表

加快ALTER TABLE 操作的速度

只修改.frm文件

CHANGE,?MODIFY, 和ALTER?區別

---

良好的邏輯設計和物理設計是高性能的基石，應該根據系統將要執行的查詢來設計schema。

選擇優化的數據類型

選擇數據類型的原則

• 更小的通常更好

盡量使用可以**正確存儲**的**最小**數據類型。

• 簡單就好

簡單數據類型需要更少CPU周期。

• 盡量避免NULL

可為NULL的列使索引，存儲等更復雜。

數據類型

分類	類型	存儲大小（字節）	范圍	范圍（無符號）
整數	tintyint	1	(-128，127)	(0，255)
	smallint	2	(-32 768，32 767)	(0，65 535)
	mediumint	3	(-8 388 608，8 388 607)	(0，16 777 215)
	int	4	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)
	bigint	8	...	...
實數	float	4	...	...
	double	8	...	...
	decimal(m,n)	不定	...	...
日期和時間	date	3	1000-01-01/9999-12-31
	time	3	838:59:59'/'838:59:59'
	year	1	1901-2155
	datetime	8	1000-01-01 00:00:00/9999-12-31 23:59:59
	timestamp	4	1970-01-01 -- 2037
字符串	char(m)	m	0-255字節
	varchar(m)	/	0-65535 字節
	tinyblob,tinytext		0-255字節
	blob,text		0-65 535字節
	mediumblob		2^24 - 1 字節
	mediumtext
	longblob,longtext		2^32 -1 字節
位	bit	n	?	不同引擎不同。 innodb，[n/8]
?	binary(n)	n	?	n個字節
?	varbinary(n)	?	?	?

整數

整數類型有可選的UNSIGNED 屬性，表示不允許負值。

整數類型的選擇只決定了MYSQL怎么在內存和磁盤中保存數據，整數計算一般使用64位的bigint，即使32位環境也如此（一些聚合函數例外，使用decimal或double）。

實數

decimal支持精確計算，float和double 由于CPU支持，比decimal更快。decimal在MYSQL 5.0以上被保存在一個二進制中（每4個字節存9個數字）。decimal（18,9）使用9個字節，小數點一個字節，整數部分4個字節，小數部分4個字節。

浮點類型存儲同樣范圍的值時，比decimal節省空間，MYSQL使用double作為浮點計算的類型。

字符串

存儲引擎存儲char或者varchar值的方式，在內存中和磁盤上可能是不一樣。

VARCHAR，可變長，需要1或2個字節存儲長度信息。

char存儲時，會把末尾空格截斷，內部存儲時，添加空格，讀取出來時，默認空格會發現不存在了。

binary填充時默認填充的是\0，而不是空格。

日期和時間

MYSQL能存儲的最小時間粒度為秒。但是MYSQL也可以使用微秒級的粒度進行臨時運算。

如果需要微秒計算，則可以用bigint存儲時間戳，或者double存儲秒之后的小數部分。

除了特殊要求，通常盡量使用timestamp，它比datetime空間效率更高。

選擇標識符

為標識列選擇合適的數據類型非常重要。不僅考慮存儲，還要考慮怎么執行計算和比較。

一旦選擇了類型，要確保所有的關聯表中關聯字段都使用相同類型。

整數類型是個合適的選擇。

如果可能避免使用字符串類型作為標識列。

如果是UUID，應該移除‘-’，更好的做法是UNHEX轉成16字節數字，存儲在binary（16）中。

?

ORM等產生的SQL會導致很嚴重的性能問題。

schema設計中的陷阱

• 太多的列
• 太多關聯
• 全能枚舉
• 變相枚舉
• 確實需要NULL，用其他不可用到的值代替，增加復雜度

范式和反范式

根據經驗吧

緩存表和匯總表

數據冗余表，提高查詢

物化視圖

物化視圖指事先計算存儲在磁盤上的表，MYSQL并不原生支持，可以使用Flexviews。

計算器表

增加更多的slot，以減少并發的機會。

加快ALTER TABLE 操作的速度

Mysql的alter table操作的性能對大表示個問題，Mysql執行大部分修改表的結構操作的方法是用新的結構創建一個空表，從舊表查出數據插入到新表，然后刪除舊表。

mysql5.1之后，支持“在線”操作，這些功能不需要在整個操作過程中鎖表。最近innodb也支持通過排序來創建索引，這使得索引更快且有個緊湊的索引布局。

不是所有的alter table操作都會引起重建表結構。有2種方法可以改變或者刪除一個列的默認值

ALTER TABLE MODIFY COLUMN 將導致表重建。

ALTER TABLE ALTER COLUMN? 會直接修改.frm文件不涉及表數據，所以很快。

只修改.frm文件

不受官方支持

CHANGE,?MODIFY, 和ALTER?區別

ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} CHANGE [COLUMN] old_col_name new_col_name column_definition MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

• CHANGE:

  • 可重命名列和修改列的定義

  • Has more capability than?MODIFY, but at the expense of convenience for some operations.?CHANGE?requires naming the column twice if not renaming it.

  • With?FIRST?or?AFTER, can reorder columns.

• MODIFY:

  • Can change a column definition but not its name.

  • More convenient than?CHANGE?to change a column definition without renaming it.

  • With?FIRST?or?AFTER, can reorder columns.

• ALTER: Used only to change a column default value.

更多關于alter table 性能問題參考：?https://www.cnblogs.com/hllnj2008/p/5045752.html

?

?

?

?

?

?

?

總結

以上是生活随笔為你收集整理的高性能Mysql--Schema与数据类型优化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。