Mysql development specification documentation

Mysql development specification document 1. mysql database naming convention 2. table Structure Design and Data Type Selection 3. development considerations www.2cto.com 1. mysql database naming rules 1.1 naming rules: Table/view/procedure/function/package: lowercase letters of the object type abbreviation + function module abbreviation + "_" + lowercase words of the business meaning, for example: primary Key/foreign key/index: object Type abbreviation + "_" + Table Name abbreviation + field name object type Abbreviation: Table: tView: vProcedure: pFunction: fnPackage: pk primary key: pk foreign key: fk index: idx www.2cto.com Eg: Trader background function: tbk_operate_log search menu: ts_operate_log Index Command: idx_tablename_column 2. table Structure Design and Data Type Selection 2.1 common data types: INTEGER: tinyint, small Int, mediumint, int, bigint. Generally, int is enough for the floating point type: DECIMAL (M, D) precision. DECIMAL is not recommended. We recommend that you multiply the number by a fixed multiple to convert it to an integer to save storage space, there is no additional maintenance cost. TINYINT> SMALLINT> MEDIUMINT> INT> BIGINT> DECIMAL (the storage space increases, but the performance decreases ). Only int or bigint can be used for fields of the auto-incrementing sequence type, and the unsigned type is clearly identified. When the field exceeds 4.2 billion, the bigint struct type: varchar, char, when selecting the type of ENUM, SET, and text character columns, do not use the TEXT data type. The lob type must be resolutely put to an end. The text type can be used only when the number of characters exceeds 20000, all fields that use the text type must be split from the original table and stored in another table separately with the original table primary key. The processing method determines that its performance is lower than that of char or varchar. For a fixed length field, we recommend that you use the CHAR type. The variable length field should use VARCHAR as much as possible, and only set the appropriate maximum length, rather than randomly giving a large maximum length limit, because of the different length ranges, MySQL also has different storage processes. For the Status field, you can use char or ENUM to store it, because it can greatly reduce the storage space, and even if you need to add a new type, as long as it is added to the end, you do not need to recreate table data to modify the structure. What if it is to store pre-defined attribute data? You can try to use the SET type. Even if there are multiple attributes, it can be easily used, while saving a lot of storage space. Date and Time: Commonly Used TIMESTAMP, date must be accurate (year, month, day, hour, minute, second) time field, you can use datetime, timestamp; if the time field only needs to be accurate to the day, the basic design of the date 2.2 table structure is as follows: 2.2.1. the field uses not null: the MySQL NULL type is different from the Oracle NULL type and will enter the index. If it is a composite index, this NULL field will greatly affect the efficiency of the entire index. In addition, the processing of NULL in the index is special and will also occupy additional storage space 2.2.2. proper splitting/redundancy. when a large field similar to TEXT or a large VARCHAR type exists in our table, this field is not required if most of us access this table, instead, we can split it into another independent table to reduce the storage space occupied by common data. One obvious advantage of this is that the number of data records that can be stored in each data block can be greatly increased, which reduces the number of physical IO operations and greatly improves the cache hit rate in the memory. B. independent small fields that are frequently referenced and can only be obtained by joining two (or more) large tables. In this scenario, each Join operation is only used to obtain the value of a small field, A large number of Join records can cause a large number of unnecessary IO operations, which can be optimized by exchanging space for time. However, while redundancy is required, ensure that data consistency is not damaged, and ensure that redundant fields are updated at the same time. 2.2.3. the size of the control table mysql significantly reduces the performance when processing large tables (char tables> 1000 rows, or int tables> W, therefore, we need to use different methods to control the capacity of A single table. A: Hierarchical Storage of data based on hot and cold data, and historical archiving B: database/table/partition tables are used, and horizontal splitting is used to control the capacity of A single table. C: for the OLTP system, the resource consumption of a single transaction can be controlled. In case of a large transaction, the OLTP system can be disassembled and the mode is changed to zero to avoid special cases affecting the public. D: The number of tables in a single database should not exceed 500. E: the number of fields in a single table cannot be too large. A maximum of 50 2.2.4 fields are allowed. table definition parameters: mysql> show create table utf8 \ G; * *************************** 1. row *************************** Table: utf8Create Table: create table 'utf8' ('id' int (11) not null AUTO_INCREMENT, 'Name' varchar (10) default null, 'createtime' timestamp null default null, 'string' tinyint (4) DEFAULT '1', 'A' decimal (10, 0) default null, 'B' decimal (10, 4) default null, 'C' datetime default null, 'D' date default null, 'E' enum ('A', 'B ', 'C') default null, 'F' set ('A', 'B', 'C') DEFAULT NULL, PRIMARY KEY ('id '), unique key 'name _ UNIQUE '('name'), unique key 'createtime _ unique' ('createtime') ENGI NE = InnoDB AUTO_INCREMENT = 7 default charset = utf8 ROW_FORMAT = dynamic engine: select an appropriate storage engine based on your business needs. Select innodb as the general transaction table, and myisamAUTO_INCREMENT as the read-only table: the initialization value of the auto-increment column CHARSET: defines the character set of the table based on your business needs. For multi-language Environments, select utf8ROW_FORMAT: Row Storage Format description: mysql file storage format antelope Barracuda mysql Row Storage Format antelope: provides two row formats: compact and redundant. redundant is compatible with earlier versions. By default, the saved file format is "compact". Barracuda: Innodb plugin introduces a new file format. The Barracuda file format contains two new row record formats: Dynamic and Compressed. Compressed has a good compression function for character type data, which can improve efficiency 2.2.5. creating an appropriate index requires additional maintenance, access, and space costs. Therefore, you must be cautious when creating an index so that a single index can cover as many SQL statements as possible, for tables with high update frequency, you must control the number of indexes. A. for a large and newer volume of data, index maintenance costs will be very high. If there is very little demand for retrieval and there is no high requirement on retrieval efficiency, we do not recommend creating indexes, or minimize the number of indexes. B. It is better to directly traverse faster data from a small amount of data to index retrieval, and it is not suitable for indexing. C. Try to make the search conditions as many as possible in the index, and filter all the data through the index as possible. The back table only retrieves additional data fields. D. the order of fields plays a vital role in the efficiency of the composite index. Fields with better filtering results must be more advanced. when the amount of data to be read accounts for a large proportion of the total data volume or the index filtering effect is not very good, the use of the index is not necessarily better than the full table scan. F. in actual use, only one index can be used for one data access at a time, which must be noted during index creation, it does not mean that each condition in the Where clause in an SQL statement can be indexed. g. do not use foreign keys in High-concurrency environments. It is too easy to generate deadlocks. The program should guarantee the constraints. character fields must use prefix indexes. 3. development considerations 3.1 do not perform mathematical or function operations on index columns 3.2 avoid large SQL statements, disassemble multiple small SQL statements 3.3 avoid using select * 3.4 replace or with in ()/union, note that the number of in statements is less than 300. Avoid % prefix. Fuzzy prefix. 3.5. Avoid subquery. ---- end ----