Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

MySQL Design Specification (continued)

2025-02-23 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

1.1 Database field design specification

Give priority to the smallest data type that meets your storage needs:

Unsigned integer is used to store non-negative data.

N in varchar (N) represents the number of characters, not the number of bytes (Oracle is the number of bytes, for example, the lower 2 bytes of Chinese GBK stores a Chinese character), that is, varchar (2) in MySQL can store 2 Chinese characters and occupy 6 bytes (UTF8).

Avoid using text,blob data types:

The common text type can store 64k, and generally does not use so much space.

If not, it is recommended to separate blob or text columns into separate extension tables and avoid using select *

Text,blob can only use prefix indexes, and by default none of the text,blob columns can have default values.

Avoid using ENUM data types:

An alter statement is required to modify the ENUM value

ORDER BY of ENUM type is inefficient and requires additional operations

Prohibit the use of numeric values as enumerated values for ENUM.

Whenever possible, define all columns as NOTNULL:

The index NULL column takes up extra space to save (empty or not), so it takes up more space

Compare and calculate the NULL to do special treatment, the index may be invalid.

Use the TIMESTAMP or DATETIME type to store time:

TIMESTAMP 1970-01-01-01-19, occupying 4 bytes the same as INT storage

Other ranges are stored using the DATETIME type.

Financial and financial related data, using DECIMAL type:

The occupied space is determined by a defined width.

Can be used to store larger integer data than bigint.

1.2 Database SQL development specification

It is recommended that you use precompiled statements for database operations:

Pass only parameters, which is more efficient than passing SQL statements

The same statement can be parsed at once and used many times to improve processing efficiency.

Avoid implicit conversions of data types:

Implicit conversion may cause the index to fail.

Take full advantage of indexes that already exist on the table:

Avoid using double% query conditions, such as' 123% 'can use indexes

A SQL can only use one column of a federated index for range queries

PS:index (a, b, c), where a = xxand b > low and b

< high可使用到b列的索引, wherea >

Low and a

< high and b = xx,b这列使用不到索引; 这个处理和oracle的联合索引是一致的。 使用left join或not exists来优化not in操作。 禁止跨库查询: 程序连接不同数据库使用不同的账号; 为数据库迁移和分库分表留出余地; 降低业务的耦合度; 避免权限过大产生的安全风险,SQL注入也只能看到一个库。 禁止使用Select *: 消耗过多的CPU, IO, 网络资源; 无法使用覆盖索引; 可减少表结构变更带来的影响。 禁止使用不含字段列表的INSERT: INSERT INTO T(A, B) VALUES(…….); 可减少表结构变更带来的影响。 尽量避免使用子查询,可以把子查询优化为join操作: 当然并不是所有的子查询都能转换为join,如group by等得子查询; 通常是IN子查询这样的语句。 子查询结果集无法使用索引;子查询会产生临时表操作,如果子查询数据量大将严重影响效率;消耗过多的CPU、IO。 避免使用JOIN关联太多的表: 每Join一个表多占用一部分内存(join_buffer_size); 会产生临时表操作,影响查询效率; MySQL最多允许关联61个表,建议不超过5个。 减少同数据库的交互次数: 数据库更适合做批量操作; 合并多个相同的操作到一起; PS:如alter table t addcolumn c1 int, change column c2 c2 int… 使用in代替or: in的值不要超过500个; in操作可以有效的利用索引。 禁止使用order byrand()进行随机排序: 会把表中所有满足条件的数据装载到内存中进行排序; 消耗大量的CPU/IO/MEM; 处理方式:推荐在程序中获取一个随机值,然后在数据库中获取数据的方式(亲认为开发人员愿意去实现多余的算法吗?都希望一句SQL返回结果集)。 WHERE从句禁止对列进行函数转换和计算: where date(createtime) = '20171010' à where createtime >

= '20171010' and createtime < '20171011'

UNIONALL OR UNION:

These two sorting methods are exactly the same as Oracle, UNION ALL does not sort, UNION sorting.

Split a complex SQL into multiple small SQL:

MySQL A SQL can only be calculated using one CPU

After SQL splitting, the processing efficiency can be improved by parallel query.

1.3 Code of conduct for database operation

Batch DML operations with more than 100W rows of data need to be performed multiple times in batches:

Master-slave delay needs to be considered.

Binlog logs generate a large number of logs for row format

Avoid the operation of large transactions and block parallelism.

Modification of large table structure:

It is recommended to use pt-online-schema-change to modify the table structure

The active delay caused by large table modification can be avoided.

Avoid locking tables when modifying table fields.

It is forbidden to grant super permissions to program accounts:

When the maximum number of connections is reached, a user connection with super privileges is also allowed

Super permissions can only be left to DBA accounts that deal with the problem.

For program accounts, follow the principle of minimum permissions:

When using database accounts, programs can only be used under one DB, and cross-database usage is not allowed.

In principle, program accounts are not allowed to have drop permissions.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report