標簽：union all 分組 字段名 空間 編號 from 表達 最大 time
MySQL數據庫与 Oracle、 SQL Server 等數據庫相比，有其内核上的优势与劣势。我们在使用MySQL數據庫的时候需要遵循一定规范，扬长避短。本规范旨在帮助或指导RD、QA、OP等技术人员做出适合线上业务的數據庫设计。在數據庫变更和处理流程、數據庫表设计、
SQL編寫等方面予以規範，從而爲公司業務系統穩定、健康地運行提供保障。
以下所有規範會按照【高危】、【強制】、【建議】三個級別進行標注，遵守優先級從高到低。
對于不滿足【高危】和【強制】兩個級別的設計，DBA會強制打回要求修改。
库通配名_編號，編號从0开始递增，比如wenda_001以時間進行分庫的名稱格式是“庫通配名_時間”create database db1 default character set utf8;。auto_increment(2)標識表裏每一行主體的字段不要設爲主鍵，建議設爲其他字段如user_id，order_id等，并建立unique key索引（可参考cdb.teacher表設計）。因爲如果設爲create_time和最後更新時間字段update_time，便于查問題。NOT NULL屬性，業務可以根據需要定義DEFAULT值。因爲使用NULL值会存在每一行都会占用额外存储空間、数据迁移容易出错、聚合函数计算结果偏差等问题。blob、text等大字段，垂直拆分到其他表裏，僅在需要讀這些對象的時候才去select。user_name屬性在user_account，user_login_log等表裏冗余一份，減少join查詢。tmp_開頭。備份表用于備份或抓取源表快照，名稱必須以bak_開頭。中間表和備份表定期清理。alter table，必須經過DBA審核，並在業務低峰期執行。因爲alter table會産生表鎖，期間阻塞對于該表的所有寫入，對于業務可能會産生極大影響。auto_increment屬性），推薦使用bigint類型。因爲無符號int存儲範圍爲-2147483648~2147483647（大約21億左右），溢出後會導致報錯。status、類型type等字段推薦使用tinytint或者smallint类型节省存储空間。int類型，不推薦用char(15)。因爲int只占4字節，可以用如下函數相互轉換，而char(15)占用至少15字节。一旦表数据行数到了1亿，那么要多用1.1G存储空間。 SQL：select inet_aton(‘192.168.2.12‘); select in et_ntoa(3232236044); PHP: ip2long(‘192.168.2.12’); long2ip(3530427185);enum，set。 因为它们浪费空間，且枚举值写死了，变更不方便。推荐使用tinyint或smallint。blob，text等类型。它们都比较浪费硬盘和内存空間。在加载表数据时，会读取大字段到内存里从而浪费内存空間，影响系统性能。建议和PM、RD沟通，是否真的需要这么大字段。Innodb中当一行记录超过8098字节时，会将该记录中选取最overflow-page裏。不幸的是在compact行格式下，原始page和overflow-page都會加載。int，程序端乘以100和除以100进行存取。因爲int占用4字節，而double占用8字節，空間浪费。varchar存储。因爲varchar是變長存儲，比char更省空間。MySQL server层规定一行所有文本最多存65535字节，因此在utf8字符集下最多存21844個字符，超过会自动转换为mediumtext字段。而text在utf8字符集下最多存21844mediumtext最多存2^24/3個字符，longtext最多存2^32個字符。一般建議用varchar類型，字符數不要超過2700。timestamp。因爲datetime占用8字節，timestamp僅占用4字節，但是範圍爲1970-01-01 00:00:01到2038-01-01 00:00:00。更爲高階的方法，選用int來存儲時間，使用SQL函數unix_timestamp()和from_unixtime()來進id int/bigint auto_increment,且主鍵值禁止被更新。pk_”開頭，唯一鍵以“uk_”或“uq_”開頭，普通索引以“idx_”開頭，一律使用小寫格式，以表名/字段的名稱或縮寫作爲後綴。BTREE；MEMORY表可以根據需要選擇HASH或者BTREE類型索引。userid的區分度可由select count(distinct userid)計算出來。key(a,b)，則key(a)爲冗余索引，需要刪除。partition-key）必須有索引，或者是組合索引的首列。alter table操作，必須在業務低峰期執行。utf8或utf8mb4。utf8。一個較爲規範的建表語句爲：
CREATE TABLE user (
`id` bigint(11) NOT NULL AUTO_INCREMENT,
`user_id` bigint(11) NOT NULL COMMENT ‘用户id’
`username` varchar(45) NOT NULL COMMENT ‘真实姓名‘,
`email` varchar(30) NOT NULL COMMENT ‘用户邮箱’,
`nickname` varchar(45) NOT NULL COMMENT ‘昵称‘,
`avatar` int(11) NOT NULL COMMENT ‘头像‘,
`birthday` date NOT NULL COMMENT ‘生日‘,
`sex` tinyint(4) DEFAULT ‘0‘ COMMENT ‘性别‘,
`short_introduce` varchar(150) DEFAULT NULL COMMENT ‘一句话介绍自己，最多50个汉字‘,
`user_resume` varchar(300) NOT NULL COMMENT ‘用户提交的简历存放地址‘,
`user_register_ip` int NOT NULL COMMENT ‘用户注册时的源ip’,
`create_time` timestamp NOT NULL COMMENT ‘用户记录创建的时间’,
`update_time` timestamp NOT NULL COMMENT ‘用户资料修改的时间’,
`user_review_status` tinyint NOT NULL COMMENT ‘用户资料审核状态，1为通过，2为审核中，3为未通过，4为还未提交审核’,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_user_id` (`user_id`),
KEY `idx_username`(`username`),
KEY `idx_create_time`(`create_time`,`user_review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘网站用户基本信息‘;*。因爲select *會將不該讀的數據也從MySQL裏讀出來，造成網卡壓力。且表字段一旦更新，但model層沒有來得及更新的話，系統會報錯。insert into t1 values(…)，道理同上。insert into…values(XX),(XX),(XX)…。這裏XX的值不要超過5000個。值過多雖然上線很很快，但會引起主從同步延遲。UNION，推薦使用UNION ALL，並且UNION子句个数限制在5个以内。因爲union all不需要去重，节省數據庫资源，提高性能。select… where userid in(….500个以内…)，这么做是为了减少底层扫描，减轻數據庫压力从而加速查询。hint，如sql_no_cache，force index，ignore key，straight join等。因爲hint是用來強制SQL按照某個執行計劃來執行，但隨著數據量變化我們無法保證自己當初的預判是正確的，因此我們要相信MySQL優化器！SELECT|UPDATE|DELETE|REPLACE要有WHERE子句，且WHERE子句的條件必需使用索引查找。where length(name)=‘Admin‘或where user_id+2=10023。where a=1 or b=2優化爲where a=1… union …where b=2, key(a),key(b)。select a,b,c from t1 limit 10000,20;優化爲: select a,b,c from t1 where id>10000 limit 20;。update t1 join t2…。select a from db1.table1 alias1 where …。INSERT|UPDATE|DELETE|REPLACE語句操作的行數控制在2000以內，以及WHERE子句中IN列表的傳參個數控制在500以內。auto_increment屬性字段的表的插入操作，並發需要控制在200以內。repeatable-read。unique key，如update … where id=XX; 否则会产生间隙锁，内部扩大锁定范围，导致系统性能下降，产生死锁。order by，和業務溝通能不排序就不排序，或將排序放到程序端去做。order by、group by、distinct这些语句较为耗费CPU，數據庫的CPU资源是极其宝贵的。order by、group by、distinct這些SQL盡量利用索引直接檢索出排序好的數據。如where a=1 order by b可以利用key(a,b)。order by、group by、distinct這些查詢的語句，where條件過濾出來的結果集請保持在1000行以內，否則SQL會很慢。update|delete t1 … where a=XX limit XX; 这种带limit的更新语句。因爲会导致主从不一致，导致数据错乱。建议加上order by PK。update t1 set … where name in(select name from user where…);效率極其低下。insert into …on duplicate key update…在高並發環境下，會造成主從不一致。update t1,t2 where t1.id=t2.id…。標簽：union all 分組 字段名 空間 編號 from 表達 最大 time
原文地址：https://www.cnblogs.com/zeenzhou/p/15054759.html
