MySQL开发规范与使用技巧总结_MySQL-mysql教程-PHP中文网

首页

数据库

mysql教程

MySQL开发规范与使用技巧总结_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

May 30, 2016 pm 05:11 PM

使用技巧

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>

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

ENUM类型在需要修改或增加枚举值时，需要在线DDL，成本较高；ENUM列值如果含有数字类型，可能会引起默认值混淆。9.使用VARBINARY存储大小写敏感的变长字符串或二进制内容。 VARBINARY默认区分大小写，没有字符集概念，速度快。

<code class="hljs sql">10.INT类型固定占用4字节存储 例如INT(4)仅代表显示字符宽度为4位，不代表存储长度。数值类型括号后面的数字只是表示宽度而跟存储范围没有关系，比如INT(3)默认显示3位，空格补齐，超出时正常显示，python、java客户端等不具备这个功能。9.使用VARBINARY存储大小写敏感的变长字符串或二进制内容。

VARBINARY默认区分大小写，没有字符集概念，速度快。11.区分使用DATETIME和TIMESTAMP。 存储年使用YEAR类型。存储日期使用DATE类型。存储时间(精确到秒)建议使用TIMESTAMP类型。 DATETIME和TIMESTAMP都是精确到秒，优先选择TIMESTAMP，因为TIMESTAMP只有4个字节，而DATETIME8个字节。同时TIMESTAMP具有自动赋值以及?自动更新的特性。注意：在5.5和之前的版本中，如果一个表中有多个timestamp列，那么最多只能有一列能具有自动更新功能。

<code class="hljs sql">如何使用TIMESTAMP的自动赋值属性? a)自动初始化，而且自动更新：10.INT类型固定占用4字节存储

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

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

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

11.区分使用DATETIME和TIMESTAMP。

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

存储年使用YEAR类型。存储日期使用DATE类型。存储时间(精确到秒)建议使用TIMESTAMP类型。<code class="hljs vbnet"><code class="hljs php">c)自动更新，初始化的值为0： DATETIME和TIMESTAMP都是精确到秒，优先选择TIMESTAMP，因为TIMESTAMP只有4个字节，而DATETIME8个字节。同时TIMESTAMP具有自动赋值以及?自动更新的特性。注意：在5.5和之前的版本中，如果一个表中有多个timestamp列，那么最多只能有一列能具有自动更新功能。

如何使用TIMESTAMP的自动赋值属性?<pre class='brush:php;toolbar:false;'>
&lt;code class=&quot;hljs sql&quot;&gt;&lt;code class=&quot;hljs vbnet&quot;&gt;&lt;code class=&quot;hljs php&quot;&gt;&lt;code class=&quot;hljs sql&quot;&gt; column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP&lt;/code&gt;&lt;/code&gt;&lt;/code&gt;&lt;/code&gt;</pre>
a)自动初始化，而且自动更新： <p><code class="hljs sql"><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">12.所有字段均定义为NOT NULL。 a)对表的每一行，每个为NULL的列都需要额外的空间来标识。 b)B树索引时不会存储NULL值，所以如果索引字段可以为NULL，索引效率会下降。 c)建议用0、特殊值或空串代替NULL值。<code class="hljs vbnet"><code class="hljs php">c)自动更新，初始化的值为0：

<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">12.所有字段均定义为NOT NULL。
a)对表的每一行，每个为NULL的列都需要额外的空间来标识。
b)B树索引时不会存储NULL值，所以如果索引字段可以为NULL，索引效率会下降。
c)建议用0、特殊值或空串代替NULL值。<h2 id="code-class-hljs-sql-code-class-hljs-vbnet-code-class-hljs-php-code-class-hljs-sql-code-class-hljs-php-MySQL使用技巧-code-code-code-code-code"><code class="hljs sql"><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">1.将大字段、访问频率低的字段拆分到单独的表中存储，分离冷热数据。 有利于有效利用缓存，防?止读入无用的冷数据，较少磁盘IO，同时保证热数据常驻内存提?高缓存命中率。<code class="hljs sql"><code class="hljs vbnet"><code class="hljs php"><code class="hljs sql"><code class="hljs php">1.将大字段、访问频率低的字段拆分到单独的表中存储，分离冷热数据。 有利于有效利用缓存，防?止读入无用的冷数据，较少磁盘IO，同时保证热数据常驻内存提?高缓存命中率。

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

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

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

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

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

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

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

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

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

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

<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使用的是提前读取策略。 推荐分页方式:

<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>

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

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

<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">19.避免使用存储过程、触发器、视图、自定义函数等。 这些高级特性有性能问题，以及未知BUG较多。业务逻辑放到数据库会造成数据库的DDL、SCALE OUT、 SHARDING等变得更加困难。

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

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

声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

您可以使用哪些工具来监视MySQL性能？Apr 23, 2025 am 12:21 AM

如何有效监控MySQL性能？使用mysqladmin、SHOWGLOBALSTATUS、PerconaMonitoringandManagement(PMM)和MySQLEnterpriseMonitor等工具。1.使用mysqladmin查看连接数。2.用SHOWGLOBALSTATUS查看查询数。3.PMM提供详细性能数据和图形化界面。4.MySQLEnterpriseMonitor提供丰富的监控功能和报警机制。

MySQL与SQL Server有何不同？Apr 23, 2025 am 12:20 AM

MySQL和SQLServer的区别在于：1)MySQL是开源的，适用于Web和嵌入式系统，2)SQLServer是微软的商业产品，适用于企业级应用。两者在存储引擎、性能优化和应用场景上有显着差异，选择时需考虑项目规模和未来扩展性。

在哪些情况下，您可以选择SQL Server而不是MySQL？Apr 23, 2025 am 12:20 AM

在需要高可用性、高级安全性和良好集成性的企业级应用场景下，应选择SQLServer而不是MySQL。1)SQLServer提供企业级功能，如高可用性和高级安全性。2)它与微软生态系统如VisualStudio和PowerBI紧密集成。3)SQLServer在性能优化方面表现出色，支持内存优化表和列存储索引。

MySQL如何处理角色集和碰撞？Apr 23, 2025 am 12:19 AM

mySqlManagesCharacterSetsetSandCollationsyutusututf-8asthEdeFault，允许ConfigurationAtdataBase，table和columnlevels，AndrequiringCarefullageLignmentToavoidMismatches.1）setDefeaultCharactersetTercharactersetEtCollacterSeteTandColletationForAdataBase.2）conformentcollecharactersettersetertersetcollatertersetcollationcollation

MySQL中有什么触发器？Apr 23, 2025 am 12:11 AM

MySQL触发器是与表相关联的自动执行的存储过程，用于在特定数据操作时执行一系列操作。1）触发器定义与作用：用于数据校验、日志记录等。2）工作原理：分为BEFORE和AFTER，支持行级触发。3）使用示例：可用于记录薪资变更或更新库存。4）调试技巧：使用SHOWTRIGGERS和SHOWCREATETRIGGER命令。5）性能优化：避免复杂操作，使用索引，管理事务。

您如何在MySQL中创建和管理用户帐户？Apr 22, 2025 pm 06:05 PM

在MySQL中创建和管理用户账户的步骤如下：1.创建用户：使用CREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password';2.分配权限：使用GRANTSELECT,INSERT,UPDATEONmydatabase.TO'newuser'@'localhost';3.修正权限错误：使用REVOKEALLPRIVILEGESONmydatabase.FROM'newuser'@'localhost';然后重新分配权限；4.优化权限：使用SHOWGRA

MySQL与Oracle有何不同？Apr 22, 2025 pm 05:57 PM

MySQL适合快速开发和中小型应用，Oracle适合大型企业和高可用性需求。1）MySQL开源、易用，适用于Web应用和中小型企业。2）Oracle功能强大，适合大型企业和政府机构。3）MySQL支持多种存储引擎，Oracle提供丰富的企业级功能。

与其他关系数据库相比，使用MySQL的缺点是什么？Apr 22, 2025 pm 05:49 PM

MySQL相比其他关系型数据库的劣势包括：1.性能问题：在处理大规模数据时可能遇到瓶颈，PostgreSQL在复杂查询和大数据处理上表现更优。2.扩展性：水平扩展能力不如GoogleSpanner和AmazonAurora。3.功能限制：在高级功能上不如PostgreSQL和Oracle，某些功能需要更多自定义代码和维护。

See all articles