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('209.207.224.40'); 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>
数据库是有状态的服务,变更复杂而且速度慢,如果把业务逻辑放到数据库中,将会限制业务的快速发展。建议把业务逻辑提前,放到前端或中间逻辑层,而把数据库作为存储层,实现逻辑与存储的分离。

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

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

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

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

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

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

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

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


熱AI工具

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

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

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

AI Hentai Generator
免費產生 AI 無盡。

熱門文章

熱工具

記事本++7.3.1
好用且免費的程式碼編輯器

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

ZendStudio 13.5.1 Mac
強大的PHP整合開發環境

SublimeText3漢化版
中文版,非常好用

Atom編輯器mac版下載
最受歡迎的的開源編輯器