Mysql > create table (s)

  1. NULL takes up extra storage space
  2. The comparison is more complicated
  3. Columns containing NULL values affect SQL optimization products, especially composite indexes

2. WHERE clause does not perform function conversions and expressions

SELECT date(goods_storage_move.record_time) AS date, sum(1) AS count 
FROM goods_storage_move 
WHERE
  goods_storage_move.shop_id = 104 
  AND goods_storage_move.STATUS = 1 
  AND date( goods_storage_move.create_time ) >= '2021-03-01' 
  AND date( goods_storage_move.create_time ) <= '2021-03-09' 
GROUP BY
  date(
  goods_storage_move.record_time)
Copy the code

Similarly, using date in where invalidates create_time

3. Consider the order when designing an index

class StatisticBasketDaily(Statistic_MapBase, TimeBaseMixin):

    __tablename__ = "statistic_basket"
    __table_args__ = (
        Index(
            "ux_shop_goods_date_customer",
            "shop_id",
            "goods_id",
            "customer_id",
            "is_period",
            "statistic_date",
            unique=True,
        ),
        Index("ix_shop_date", "shop_id", "is_period", "statistic_date",),
        Index(
            "ix_shop_goods_date", "shop_id", "goods_id", "is_period", "statistic_date",
        ),
    )
Copy the code

Between and > >= < <= if you put it in the middle, it’s not going to get to the index so it’s going to get to the end of the index

4. Implicit conversions cause index invalidation

select name,phone from shop_customer where id = '111';
Copy the code

Similar to id string conversion causes id primary key index invalidation

5. In operation or % operation

The value of in should not exceed 500 or you may not find the appropriate index or! Select * from ‘%name%’ where ‘%’ = ‘%name%’

6. union union_all

If you can use union_all, try not to use union queries because union queries need to sort and exclude the same data

7. Always use decimal when designing decimals in your database

You can store precision in decimal without having to use float you can store precision in Decimal which is an exact floating-point number without losing precision when evaluating

8. Index naming

Primary key index name pk_ field name Unique index name UK_ field name common index IDX_ field name development and maintenance at a glance

9. Split large SQL into smaller SQL

Large SQL will produce large transactions, database blocking data volume will be too large CPU full

10. Large fields access low-frequency fields and split them

You are advised to store large fields and low-frequency fields in separate tables to separate hot and cold data. Reducing the table width can reduce disk I/OS and reduce memory usage

11. Order of join table

Mysql > select * from ‘LEFT join’; select * from ‘LEFT join’; select * from ‘RIGHT’; select * from ‘LEFT join’ An INNER JOIN is usually driven by a table with fewer result sets (mysql does this for us)

12. You are advised to control the amount of data in a single table

Limit the size of a single table to 5 million. 5 million is not the limit of the MySQL database

13. Func.count and count() differences

Func. Count select count() from table where func. Select count() from (select * from tabel where… Count () is inefficient if you have a large amount of data

14. Avoid queries in loops

It’s going to be the map outside of the loop that does the processing and the for loop that needs to get the data

The above is my profound experience in work and I will continue to improve it here in the future, hoping to bring help to you