搜尋
首頁資料庫mysql教程MySQL开发规范与使用技巧总结_MySQL

1.命名规范

1.库名、表名、字段名必须使用小写字母,并采用下划线分割
a)MySQL有配置参数lower_case_table_names,不可动态更改,linux系统默认为 0,即库表名以实际情况存储,大小写敏感。如果是1,以小写存储,大小写不敏感。如果是2,以实际情况存储,但以小写比较。
b)如果大小写混合使用,可能存在abc,Abc,ABC等多个表共存,容易导致混乱。
c)字段名显示区分大小写,但实际使?用不区分,即不可以建立两个名字一样但大小写不一样的字段。
d)为了统一规范, 库名、表名、字段名使用小写字母。

2.库名、表名、字段名禁止超过32个字符。
库名、表名、字段名支持最多64个字符,但为了统一规范、易于辨识以及减少传输量,禁止超过32个字符。

3.使用INNODB存储引擎。
INNODB引擎是MySQL5.5版本以后的默认引擘,支持事务、行级锁,有更好的数据恢复能力、更好的并发性能,同时对多核、大内存、SSD等硬件支持更好,支持数据热备份等,因此INNODB相比MyISAM有明显优势。

4.库名、表名、字段名禁止使用MySQL保留字。
当库名、表名、字段名等属性含有保留字时,SQL语句必须用反引号引用属性名称,这将使得SQL语句书写、SHELL脚本中变量的转义等变得?非常复杂。

5.禁止使用分区表。
分区表对分区键有严格要求;分区表在表变大后,执?行DDL、SHARDING、单表恢复等都变得更加困难。因此禁止使用分区表,并建议业务端手动SHARDING。

6.建议使用UNSIGNED存储非负数值。
同样的字节数,非负存储的数值范围更大。如TINYINT有符号为 -128-127,无符号为0-255。

7.建议使用INT UNSIGNED存储IPV4。
用UNSINGED INT存储IP地址占用4字节,CHAR(15)则占用15字节。另外,计算机处理整数类型比字符串类型快。使用INT UNSIGNED而不是CHAR(15)来存储IPV4地址,通过MySQL函数inet_ntoa和inet_aton来进行转化。IPv6地址目前没有转化函数,需要使用DECIMAL或两个BIGINT来存储。
例如:

<code class="hljs sql">SELECT INET_ATON(&#39;209.207.224.40&#39;); 3520061480
SELECT INET_NTOA(3520061480); 209.207.224.40</code>

<strong>8.强烈建议使用TINYINT来代替ENUM类型。</strong><br> ENUM类型在需要修改或增加枚举值时,需要在线DDL,成本较高;ENUM列值如果含有数字类型,可能会引起默认值混淆。

<strong>9.使用VARBINARY存储大小写敏感的变长字符串或二进制内容。</strong><br> VARBINARY默认区分大小写,没有字符集概念,速度快。

<strong>10.INT类型固定占用4字节存储</strong><br> 例如INT(4)仅代表显示字符宽度为4位,不代表存储长度。数值类型括号后面的数字只是表示宽度而跟存储范围没有关系,比如INT(3)默认显示3位,空格补齐,超出时正常显示,python、java客户端等不具备这个功能。

<strong>11.区分使用DATETIME和TIMESTAMP。</strong><br> 存储年使用YEAR类型。存储日期使用DATE类型。 存储时间(精确到秒)建议使用TIMESTAMP类型。<br> DATETIME和TIMESTAMP都是精确到秒,优先选择TIMESTAMP,因为TIMESTAMP只有4个字节,而DATETIME8个字节。同时TIMESTAMP具有自动赋值以及?自动更新的特性。注意:在5.5和之前的版本中,如果一个表中有多个timestamp列,那么最多只能有一列能具有自动更新功能。

如何使用TIMESTAMP的自动赋值属性?<br> a)自动初始化,而且自动更新:

<code class="hljs sql"><code class="hljs vbnet">column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP</code></code>

<code class="hljs vbnet">b)只是自动初始化:

<code class="hljs sql"><code class="hljs vbnet"><code class="hljs php"> column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP</code></code></code>

<code class="hljs vbnet"><code class="hljs php">c)自动更新,初始化的值为0:

<code class="hljs sql"><code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"> column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP</code></code></code></code>

<code class="hljs vbnet"><code class="hljs php"><code class="hljs sql">d)初始化的值为0:

<code class="hljs sql"><code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php">column1 TIMESTAMP DEFAULT 0</code></code></code></code></code>

<code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php"><strong>12.所有字段均定义为NOT NULL。</strong><br> a)对表的每一行,每个为NULL的列都需要额外的空间来标识。<br> b)B树索引时不会存储NULL值,所以如果索引字段可以为NULL,索引效率会下降。<br> c)建议用0、特殊值或空串代替NULL值。

<code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php">MySQL使用技巧

<code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php"><strong>1.将大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据。</strong><br> 有利于有效利用缓存,防?止读入无用的冷数据,较少磁盘IO,同时保证热数据常驻内存提?高缓存命中率。

<code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php"><strong>2.禁止在数据库中存储明文密码。</strong><br> 采用加密字符串存储密码,并保证密码不可解密,同时采用随机字符串加盐保证密码安全。

<code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php"><strong>3.表必须有主键,推荐使用UNSIGNED自增列作为主键。</strong><br> 表没有主键,INNODB会默认设置隐藏的主键列;没有主键的表在定位数据行的时候非常困难,也会降低基于行复制的效率。

<code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php"><strong>4.禁止冗余索引。</strong><br> 索引是双刃剑,会增加维护负担,增?大IO压力。(a,b,c)、(a,b),后者为冗余索引。可以利用前缀索引来达到加速目的,减轻维护负担。

<code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php"><strong>5.禁止重复索引。</strong><br> primary key a;uniq index a;重复索引增加维护负担、占用磁盘空间,同时没有任何益处。

<code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php"><strong>6.不在低基数列上建立索引,例如“性别”。</strong><br> 大部分场景下,低基数列上建立索引的精确查找,相对于不建立索引的全表扫描没有任何优势,而且增大了IO负担。

<code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php"><strong>7.合理使用覆盖索引减少IO,避免排序。</strong><br> 覆盖索引能从索引中获取需要的所有字段,从?而避免回表进行二次查找,节省IO。<br> INNODB存储引擎中,secondary index(非主键索引,又称为辅助索引、二级索引)没有直接存储行地址,而是存储主键值。<br> 如果用户需要查询secondary index中所不包含的数据列,则需要先通过secondary index查找到主键值,然后再通过主键查询到其他数据列,因此需要查询两次。覆盖索引则可以在?一个索引中获取所有需要的数据,因此效率较高。<br> 例如SELECT email,uid FROM user_email WHERE uid=xx,如果uid不是主键,适当时候可以将索引添加为index(uid,email),以获得性能提升。

<code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php"><strong>8.用IN代替OR。SQL语句中IN包含的值不应过多,应少于1000个。</strong><br> IN是范围查找,MySQL内部会对IN的列表值进行排序后查找,比OR效率更高。

<code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php"><strong>9.表字符集使用UTF8,必要时可申请使用UTF8MB4字符集。</strong><br> a)UTF8字符集存储汉字占用3个字节,存储英文字符占用一个字节。<br> b)UTF8统一而且通用,不会出现转码出现乱码风险。<br> c)如果遇到EMOJ等表情符号的存储需求,可申请使用UTF8MB4字符集。

<code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php"><strong>10.用UNION ALL代替UNION。</strong><br> UNION ALL不需要对结果集再进行排序。

<code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php"><strong>11.禁止使用order by rand()。</strong><br> order by rand()会为表增加一个伪列,然后用rand()函数为每一行数据计算出rand()值,然后基于该行排序,这通常都会生成磁盘上的临时表,因此效率非常低。建议先使用rand()函数获得随机的主键值,然后通过主键<br> 获取数据。

<code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php"><strong>12.建议使用合理的分页方式以提高分页效率。</strong><br> 假如有类似下面分页语句:

<code class="hljs sql"><code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php"><code class="hljs sql">SELECT * FROM table ORDER BY TIME DESC LIMIT 10000,10;</code></code></code></code></code></code>

<code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php"><code class="hljs sql">这种分页方式会导致大量的io,因为MySQL使用的是提前读取策略。<br> 推荐分页方式:

<code class="hljs sql"><code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php"><code class="hljs sql"><code class="hljs sql">SELECT * FROM table WHERE TIME<last_time 10.="" as="" by="" desc="" from="" id="" inner="" join="" limit="" order="" pre="" select="" t="" table="" time=""><p><strong>13.SELECT只获取必要的字段,禁?止使用SELECT *。</strong> 

减少网络带宽消耗; 

能有效利用覆盖索引; 

表结构变更对程序基本无影响。</p><p><strong>14.SQL中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数。</strong> 

语句级复制场景下,引起主从数据不一致;不确定值的函数,产?生的SQL语句无法利用QUERY CACHE。</p><p><strong>15.采用合适的分库分表策略。例如千库十表、十库百表等。</strong> 

采用合适的分库分表策略,有利于业务发展后期快速对数据库进行水平拆分,同时分库可以有效利?用MySQL 

的多线程复制特性。</p><p><strong>16.减少与数据库交互次数,尽量采用批量SQL语句。</strong> 

使用下面的语句来减少和db的交互次数:</p><pre class="prebrush"><code class="hljs sql">a)INSERT ... ON DUPLICATE KEY UPDATE
b)REPLACE INTO
c)INSERT IGNORE
d)INSERT INTO VALUES() </code>

<strong>17.拆分复杂SQL为多个小SQL,避免大事务。</strong><br> 简单的SQL容易使?用到MySQL的QUERY CACHE;减少锁表时间特别是MyISAM;可以使用多核 CPU。

<strong>18.对同一个表的多次alter操作必须合并为一次操作。</strong><br> mysql对表的修改绝大部分操作都需要锁表并重建表,而锁表则会对线上业务造成影响。为减少这种影响,必须把对表的多次alter操作合并为一次操作。例如,要给表t增加一个字段b,同时给已有的字段aa建立索引,<br> 通常的做法分为两步:

<code class="hljs sql"><code class="hljs sql">alter table t add column b varchar(10);</code></code>

<code class="hljs sql">然后增加索引:

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql">alter table t add index idx_aa(aa);</code></code></code>

<code class="hljs sql"><code class="hljs sql">正确的做法是:

<code class="hljs sql"><code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">alter table t add column b varchar(10),add index idx_aa(aa);</code></code></code></code></code></code></code></code></code>

<code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><strong>19.避免使用存储过程、触发器、视图、自定义函数等。</strong><br> 这些高级特性有性能问题,以及未知BUG较多。业务逻辑放到数据库会造成数据库的DDL、SCALE OUT、<br> SHARDING等变得更加困难。

<code class="hljs vbnet"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><strong>20.禁止有super权限的应用程序账号存在。</strong><br> 安全第一。super权限会导致read only失效,导致较多诡异问题而且很难追踪。

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><strong>21.不要在MySQL数据库中存放业务逻辑。</strong><br> 数据库是有状态的服务,变更复杂而且速度慢,如果把业务逻辑放到数据库中,将会限制业务的快速发展。建议把业务逻辑提前,放到前端或中间逻辑层,而把数据库作为存储层,实现逻辑与存储的分离。

陳述
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
您什麼時候應該使用複合索引與多個單列索引?您什麼時候應該使用複合索引與多個單列索引?Apr 11, 2025 am 12:06 AM

在數據庫優化中,應根據查詢需求選擇索引策略:1.當查詢涉及多個列且條件順序固定時,使用複合索引;2.當查詢涉及多個列但條件順序不固定時,使用多個單列索引。複合索引適用於優化多列查詢,單列索引則適合單列查詢。

如何識別和優化MySQL中的慢速查詢? (慢查詢日誌,performance_schema)如何識別和優化MySQL中的慢速查詢? (慢查詢日誌,performance_schema)Apr 10, 2025 am 09:36 AM

要優化MySQL慢查詢,需使用slowquerylog和performance_schema:1.啟用slowquerylog並設置閾值,記錄慢查詢;2.利用performance_schema分析查詢執行細節,找出性能瓶頸並優化。

MySQL和SQL:開發人員的基本技能MySQL和SQL:開發人員的基本技能Apr 10, 2025 am 09:30 AM

MySQL和SQL是開發者必備技能。 1.MySQL是開源的關係型數據庫管理系統,SQL是用於管理和操作數據庫的標準語言。 2.MySQL通過高效的數據存儲和檢索功能支持多種存儲引擎,SQL通過簡單語句完成複雜數據操作。 3.使用示例包括基本查詢和高級查詢,如按條件過濾和排序。 4.常見錯誤包括語法錯誤和性能問題,可通過檢查SQL語句和使用EXPLAIN命令優化。 5.性能優化技巧包括使用索引、避免全表掃描、優化JOIN操作和提升代碼可讀性。

描述MySQL異步主奴隸複製過程。描述MySQL異步主奴隸複製過程。Apr 10, 2025 am 09:30 AM

MySQL異步主從復制通過binlog實現數據同步,提升讀性能和高可用性。 1)主服務器記錄變更到binlog;2)從服務器通過I/O線程讀取binlog;3)從服務器的SQL線程應用binlog同步數據。

mysql:簡單的概念,用於輕鬆學習mysql:簡單的概念,用於輕鬆學習Apr 10, 2025 am 09:29 AM

MySQL是一個開源的關係型數據庫管理系統。 1)創建數據庫和表:使用CREATEDATABASE和CREATETABLE命令。 2)基本操作:INSERT、UPDATE、DELETE和SELECT。 3)高級操作:JOIN、子查詢和事務處理。 4)調試技巧:檢查語法、數據類型和權限。 5)優化建議:使用索引、避免SELECT*和使用事務。

MySQL:數據庫的用戶友好介紹MySQL:數據庫的用戶友好介紹Apr 10, 2025 am 09:27 AM

MySQL的安裝和基本操作包括:1.下載並安裝MySQL,設置根用戶密碼;2.使用SQL命令創建數據庫和表,如CREATEDATABASE和CREATETABLE;3.執行CRUD操作,使用INSERT,SELECT,UPDATE,DELETE命令;4.創建索引和存儲過程以優化性能和實現複雜邏輯。通過這些步驟,你可以從零開始構建和管理MySQL數據庫。

InnoDB緩衝池如何工作,為什麼對性能至關重要?InnoDB緩衝池如何工作,為什麼對性能至關重要?Apr 09, 2025 am 12:12 AM

InnoDBBufferPool通過將數據和索引頁加載到內存中來提升MySQL數據庫的性能。 1)數據頁加載到BufferPool中,減少磁盤I/O。 2)臟頁被標記並定期刷新到磁盤。 3)LRU算法管理數據頁淘汰。 4)預讀機制提前加載可能需要的數據頁。

MySQL:初學者的數據管理易用性MySQL:初學者的數據管理易用性Apr 09, 2025 am 12:07 AM

MySQL適合初學者使用,因為它安裝簡單、功能強大且易於管理數據。 1.安裝和配置簡單,適用於多種操作系統。 2.支持基本操作如創建數據庫和表、插入、查詢、更新和刪除數據。 3.提供高級功能如JOIN操作和子查詢。 4.可以通過索引、查詢優化和分錶分區來提升性能。 5.支持備份、恢復和安全措施,確保數據的安全和一致性。

See all articles

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

AI Hentai Generator

AI Hentai Generator

免費產生 AI 無盡。

熱門文章

R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
3 週前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳圖形設置
3 週前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.如果您聽不到任何人,如何修復音頻
3 週前By尊渡假赌尊渡假赌尊渡假赌
WWE 2K25:如何解鎖Myrise中的所有內容
3 週前By尊渡假赌尊渡假赌尊渡假赌

熱工具

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

MantisBT

MantisBT

Mantis是一個易於部署的基於Web的缺陷追蹤工具,用於幫助產品缺陷追蹤。它需要PHP、MySQL和一個Web伺服器。請查看我們的演示和託管服務。

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

強大的PHP整合開發環境

SublimeText3漢化版

SublimeText3漢化版

中文版,非常好用

Atom編輯器mac版下載

Atom編輯器mac版下載

最受歡迎的的開源編輯器