Skip to content

Instantly share code, notes, and snippets.

@7kfpun

7kfpun/gist:6cf98a02a4d2b9813a1b7536a9a2a028

Created November 22, 2017 04:48
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save 7kfpun/6cf98a02a4d2b9813a1b7536a9a2a028 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save 7kfpun/6cf98a02a4d2b9813a1b7536a9a2a028 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. 7kfpun created this gist Nov 22, 2017.
    53 changes: 53 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,53 @@
    ### Fundamental
    - Use auto_increment integer as primary key for **all** tables(make its name be `id`)
    - Comment is required for new columns& new tables
    - Use `utf8` as the default charset for character type
    - Single table size < 50 millions records
    - Do not store images, files as binary type in database
    - Do not connect **production** database through `ldev`, `dev`, `test` and `sandbox` environment
    - Do not do **stress test** on production database

    ### Naming convention
    - all db name, table name and column name, **must** be
    - combination of lower case letter and underscore(**NO** capital letters)
    - no longer than 32 characters
    - **do not** use mysql preserved words (especially column name). (e.g.: `block`, `key(s)`, `type(s)`, `name(s)`, `value(s)`)
    - for temperary tables and databases, must start with `tmp_` as prefix, and end with `_2017XXXX` as postfix
    - for backup tables and databases, must start with `bak_` as prefix, and end with `_2017XXXX` as postfix

    ### Table design
    - always use `not null` unless really need difference between empty and null.
    - always explicitly indicate column's default value
    - integer
    - make all integer columns be `UNSIGNED` unless it might have negative values
    - use smallest integer type(tinyint, smallint, mediumint, int, bigint) that cover the range we need.
    - use default display width for integer (e.g. `int` rather than `int(10)`)
    - datetime
    - use `int` to store time

    ### Index
    - all index columns **must** be **not null**
    - no more than 5 indexes for one single table
    - no more than 5 columns for one single index(multi-column index)
    - each table **must** have primary key, and it must be `integer`
    - **do not** use UUID, MD5 or HASH as primary key
    - **do not** add index on column with low cardinality(e.g. gender, only three possible values)
    - **do not** add unique index on primary key
    - naming:
    - non-unique index:`idx_column1_column2`
    - unique index: `uniq_column1_column2`
    - all must be lower case

    ### SQL
    - explain your query
    - **do not** use force index
    - use unique key as much as possible
    - make query as simple as possible
    - split big query into small queries
    - do not use `store procedure`
    - do not use `case when then else end`. Please, do this logic in application level
    - do not do calcuation on index column
    - use `union all` instead of `union` if possible
    - when using `like`, do not use `%` as prefix (e.g. "%somthing")
    - do not do reverse lookup like `not in`, `not like`
    - avoid using sub-query