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

1. MySQL data modeling recommendations: 1.1 rational use of bigint (20) numerical type: type: BIGINT size: 8-byte range (signed): (- 9 233 372 036 854 775 808 remark 9 223 372 036 854 775 807) range (unsigned): (0min 18 446 744 073 709 551 615) usage: maximum integer value applicable scenario: record IP value, record mobile phone number Record × × piece number, record other document numbers and large data within 20 digits. Mobile phone number: 11 digits, save bingint 8 bytes, store varchar 12 bytes × × × number: 18 bits, store bingint 8 bytes, store varchar 19 bytes IP string: 11-15 bits, store bingint 8 bytes, store varchar 12-16 bytes IP operation skills '58.56.161.255' corresponds to 976790015 using function conversion: string conversion value: select INET_ATON ('58.56.161.255') The general requirements of field design are: a. Select INET_NTOA (976790015). The writing is concise, the meaning is readable, do not use the keyword which belongs to the system code, must take the accurate annotation. b. Use lowercase letters and underscores. c. Id (identifier) is recommended for the primary key, and the unique identification of business data can also be used for the unique identification of internal data and index organization structure of the system. The external key name xxx_id can be recommended in the primary key comment COMMENT. d. The external keys of other tables are named in the way of xxx_id, and there is a recommended name, and there is no recommended foreign key for xxx. e. Field type selection requirements, business reasonable, saving storage space. f. The field is no longer required to be non-empty, and the program should pay attention to the corresponding processing when using NULL. NULL does not take up any space except for the NULL flag bit. 1.3 the general requirements for table design are: a. The writing is concise, the meaning is readable and must be accurately annotated. b. The table name is prefixed, and the prefix definition is abbreviated by the name of the system or module. c. Use lowercase letters and underscores. d. The engine is selected according to the application scenario, and OLTP uses innodb,OLAP to recommend myisam. e. The character set is recommended to use utf8 or utf8mb4 f. To estimate the capacity, a large amount of data should consider the sub-table strategy, which should be completed in consultation with DBA. 1.4 the general requirements for index design are: a. Index name, general index plus prefix idx_, it is recommended that unique index plus prefix uni_ can also be unified with idx_. b. Joint index name, it is recommended that the middle column name can be abbreviated to reduce the length of the name, or the full name can be used. For example: KEY `status`, `areacode`, `sid2`, `ifaudit`, `asktime`, `activityroom`) c. D. 2. MySQL development environment process specification: 2.1 slow query slow query records, which can be checked when the development completes the test. Long_query_time = 1 slow_query_log_file =. / slowquery.log2.2 not using indexes queries for cases where the index is not used, and checks when the development finishes testing. The log_queries_not_using_indexes = 1 information goes into the slow_query_log_file file.

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.