background

Imagine you are a lumberjack with the best axe in the forest, so you are the most efficient. One day a salesman came along and praised a new tree-cutting tool, the chain saw. You bought one too, but you don’t know how to use it. Do you suppose you could do what you used to do, swing the chainsaw hard at the trunk…

MySQL is the same tool, design specification is a good tool to illustrate. Namely unified naming style, and can let the new hand quickly.

The main content of this article can be found in a similar version on the Web, but it is slightly different in some details. Based on years of experience in using MySQL and overall consideration of the application and MySQL (consider MySQL as the low-profile version: Bigtable + KV), these details are adjusted.

named

Avoid using MySQL keywords as db/table/field/index names

  • DB
    • Use project name as prefix and “_db” as suffix; Add suffix 8 – bit – wide digits starting at 0
    • Style: Lowercase letters separated by underscores
    • The DB name contains a maximum of 42 characters
  • Table
    • “_db” as a suffix; Add the suffix 8 – digit width to the sub-table, starting with 0
    • Style: Lowercase letters separated by underscores
    • The table name contains a maximum of 48 characters
  • Field
    • Primary keys are defined as:id BIGINT UNSIGNED NOT NULL
    • Fields that point to the primary key of another table end with the suffix “_id”
    • Style: Lowercase letters separated by underscores
  • Index
    • Use idx_ as prefix. The index field name and sequence combination are names
    • Style: Lowercase letters separated by underscores
  • Comment
    • Comment all fields with pure English words

DB

  • Use Innodb storage engine

    Innodb supports transactions, supports row-level locking, better recovery, and better performance under high concurrency

  • Use utF8MB4_unicoDE_CI encoding

    Better compatibility, unified character set can avoid garbled characters caused by character set conversion, the need for conversion before comparing different character sets will cause index failure

Table

  • Use utF8MB4_unicoDE_CI encoding

  • Each table must explicitly define a primary key

    1. The data is stored in the same order as the primary keys
    2. Do not use columns that update frequently as primary keys, and do not use fields such as UUID, MD5, HASH, and string that cannot ensure sequential growth of the data

  • Limit the amount of data in a single table to less than 10 million

    1. The query performance of this level of data is better
    2. You can use historical data archiving, sub-database sub-table and other means to control the amount of single table data

  • Wide tables should be split into index tables and content tables to improve query performance

    1. MySQL limits each table to store a maximum of 4096 columns, and the data size of each row cannot exceed 65535 bytes to reduce disk I/O and ensure the memory cache hit ratio of hot data
    2. The wider the table, the more memory it takes to load into the memory buffer pool, the more IO it consumes, and the more efficient the cache is used to avoid reading useless cold data

  • Cautious use of Joins

    1. The application layer cache is more efficient and can be reused in multiple query scenarios
    2. Association at the application layer makes it easier to split the database and achieve high performance and scalability
    3. Query efficiency is improved. MySQL > select * from primary key; select * from primary key

  • Use MySQL partitioned tables with caution

    A partitioned table consists of multiple files physically and a single table logically. Select partition keys carefully. Cross-partition query efficiency may be lower

  • Do not use foreign keys

    1. MySQL foreign key implementation is relatively simple and rough, poor performance
    2. MySQL is the back-end storage and does not place any computational logic on MySQL
    3. Database/table sharding can be very difficult if you rely on computational logic running on the MySQL server

Field

  • The smallest data type that meets storage requirements is preferred

    The larger the number of fields in a column is, the more space is required for indexing, the less number of inodes stored on a disk page, the more I/O times, and the worse the index performance

    Methods: 1) Convert string to numeric type for storage, for example, convert IP address to integer data (inet_aton/inet_ntoa). 2) For non-negative data (such as self-increasing ID and integer IP), unsigned integer type is preferred for storage

  • Column names and column types that store the same data must be the same

    If the associated column types are inconsistent during query, implicit data type conversion is automatically performed, which invalidates indexes on columns and reduces query efficiency

  • Define all columns as NOT NULL if possible

    • NULL takes up extra space to save
    • NULL requires special handling and may cause an application exception
    • NULL MySQL index statistics and value comparisons are more complex

  • Avoid using ENUM types

    • To change the ENUM value, use the ALTER statement
    • The ORDER BY operation of ENUM type is inefficient and requires additional operations
    • Do not use numeric values as enumeration values of enUms

  • Do not store large data such as long texts, images, and files in the database

    MySQL in-memory temporary tables do not support TEXT and BLOB large data types. If such data is contained in a query, the in-memory temporary table cannot be used for sorting operations, but the disk temporary table must be used

    And for this kind of data, MySQL still has to perform a second query, which will make SQL performance become poor, but it is not to say that such data type must not be used

  • The reservation field is disabled

    • The naming of reserved fields is difficult to recognize by name
    • The reserved field cannot confirm the data type to be stored. Therefore, an appropriate data type cannot be selected
    • Changes to the reserved field type lock the table

Index

  • Limit the number of indexes in a table. You are advised to limit the number of indexes in a table to five

    When the MySQL optimizer optimizes a query, it evaluates the candidate indexes based on statistics to generate the best execution plan. If there are many indexes that can be used for the query at the same time, it will increase the time for the MySQL optimizer to generate the execution plan and also reduce the query performance

Stored Programs

  • Disallow mysql views, stored procedures, triggers, and custom functions

Queries

  • Do not directly connect to the production environment. Manually delete or modify production data
  • Disable SELECT * You must use SELECT < field list >

    Reduces the impact of table structure changes on your application

  • Disallow INSERT statements without a list of fields

    INSERT INTO TBL (c1,c2, C3) VALUES (a,b,c); Error: INSERT INTO VALUES (a,b,c);

  • The WHERE clause disallows function conversions and calculations of columns

    Functional conversions or calculations on columns result in unusable indexes.

    WHERE create_time >= 20190101 AND create_time < 20190102 error: WHERE DATE(create_time)=20190101

  • Use UNION ALL instead of UNION when there are no duplicate values

    The UNION puts ALL the data in the result set into a temporary table and then deduplicates it. The UNION ALL does not deduplicates the result set

Refer to the link: www.cnblogs.com/huchong/p/1…

Author: Cyningsun Author: www.cyningsun.com/06-06-2021/… Copyright notice: All articles on this blog are licensed under CC BY-NC-ND 3.0CN unless otherwise stated. Reprint please indicate the source!