Design principles

3. Control the number of columns: control the number of fields within 20 4. Balance the paradigm and redundancy: sacrifice the paradigm design to improve efficiency 5. Table character set use UTF8MB4. Use INNODB storage engine

Data table design

1. Use the most efficient (and smallest) data type possible

tinyint(1Byte)
smallint(2Byte)
mediumint(3Byte)
int(4Byte)
bigint(8Byte)
bad case: int (1)/int (11)Copy the code

Sex enum (‘ F ‘, ‘M’); find_in_set($search, column name); 4. Avoid NULL fields

NULL fields are difficult to query optimize NULL field indexes require extra space NULL field compound indexes are invalid badcase: 'name' char(32) default null 'age' int not null goodcase: 'age' int not null default 0Copy the code

5, use less text/blob, vARCHAR performance will be much higher than text; If bloB is unavoidable, please remove the watch

6. Not storing images in a database

7. For MyISAM tables, if there are no variable length columns (VARCHAR, TEXT, or BLOB columns), use a fixed-size record format. This is faster but unfortunately may waste some space. Even if you have set the VARCHAR column ROW_FORMAT=fixed with the CREATE option, you can prompt for fixed-length rows

8. Use the sample character set, such as latin1. Use UTF-8 as little as possible, because UTF-8 takes up three times as much space as latin1. You can use latin1 on fields that do not require UTF-8, such as mail, URL, and so on

9. Accuracy and space conversion. Floating-point types typically use less space than DECIMAL types when storing data of the same numeric range. The FLOAT field uses 4 bytes to store data. DOUBLE takes 8 bytes and has a higher precision and a larger range of values. Data from DECIMAL will be converted to DOUBLE

The database name table name field name must have a fixed naming length of 12 characters or less. The database name, table name, and field name cannot contain more than 32 characters. Must see the meaning of the name; MySQL > select * from database; MySQL > select * from database; Temporary library and table names must be prefixed with TMP and suffixed with date and day. Backup libraries and tables must start with bak and end with date

11. The physical length of InnoDB table row records should not exceed 8KB. The default data page of InnoDB is 16KB. Therefore, when large columns are larger than 8KB (especially TEXT/BLOB columns), page-overflow storage can result, similar to ORACLE’s “row migration”. Therefore, if large columns (especially TEXT/BLOB columns) must be used and read and written frequently, If the innodbPagesize option is changed to 8KB, the recommended physical row length should not exceed 4KB. If the innodbPagesize option is changed to 8KB, the recommended physical row length should not exceed 4KB

The index class

1. Use indexes carefully and rationally

Improve the query, slow down the update index must not be more the better (can not add, must add) overwrite record number is not suitable to build index, such as “gender” 2, character fields must be built prefix index

Bad case: select id where age +1 = 10;

4. It is recommended to use autoincrement columns for innoDB primary keys

Primary key creates cluster index Primary key should not be modified String should not be primary key If primary key is not specified innoDB uses a unique and non-null index insteadCopy the code

5, do not use foreign keys, please ensure constraints by the program

6. Avoid creating indexes on prefixes of existing indexes. For example, if index (a, b) exists, drop index (a).

Control the length of a single index. Use key (name (8)) to index the first few characters of the data

8. Use indexes selectively. It is not good to use indexes on columns that change very little, such as gender columns

Optimize table can compress and sort indexes, not run frequently

The Analyze table is used to update data

Index selectivity = count(distinct(username) /count(*)); count(distinct(username) /count(*)); The advantage of high index selectivity is that mysql can filter more rows when looking for matches. Unique index selectivity is the best, with a value of 1

12. Do not use duplicate or redundant indexes. For the index of INNODB engine, every time the data is modified, the primary key index and the corresponding index value in the secondary index will be changed, which may cause a lot of data migration, paging, and fragmentation

ALTER TABLE T1 ADD INDEX(user(20)); ALTER TABLE T1 ADD INDEX(user(20)); ALTER TABLE T1 ADD INDEX(user(20)); The length of the prefix index can be calculated based on the statistics of the field, usually a little more than the average length

14. Use pt-duplicate-key-checker periodically to check and delete duplicate indexes. Delete idx2(a) from idx1(a, b)

SQL statement design class

1. SQL statements should be as simple as possible. One SQL statement can only be used in one CPU, large statements can be separated from small statements to reduce lock time.

2, simple transaction, transaction time as short as possible,bad case: upload picture transaction

3. Avoid using trig/func instead of triggers and functions

4, do not select *, consumption of CPU, IO, memory, bandwidth, this program is not scalable

5, OR rewrite to IN()

The efficiency of OR is level NinWhen the news of thelog(n) levelinSelect id from t select id from twherePhone = '159' or phone= '136' =>select ID from twhere phone in(' 159 ', '136');Copy the code

6. OR/UNION

Select id from twhere phone = '159' or name = 'john';
=>
select id from t where phone='159' union  select id from t where name='jonh';

Copy the code

7, avoid negative %, such as not in/like

Count (*)

Limit efficient paging

limitSelect id from tlimit 10000, 10;
=>
select id from t where id > 10000 limit 10;

Copy the code

10. Use union all instead of union

11. Use less joins

12, use group by, automatic sorting

13. Please use the same type for comparison

Load data is about 20 times faster than INSERT

Update the data to batch update after breaking, do not update too much data at a time

16. Use performance analysis tools

Sql explain/showprofile/mysqlSLA 17, run –log-slow-queries — long-query-time=2 to query statements that are slow to be queried. Then use Explain to analyze the query and optimize it

show profile;
mysqlsla;
mysqldumpslow;
explain;
show slow log; show processlist; Show query_response_time(Percona) optimize data insert, update, delete This is done through the optimize command. For example, MyISAM table: optimize table nameCopy the code

18. Do not run large queries in the database

19, the use of precompiled statements, only pass parameters, more efficient than passing SQL statements; Parse once, use many times; Reduce the SQL injection probability

20, Disable the use of order by rand()

21, forbid a single SQL statement to update multiple tables at the same time

MySQL is not good at math and logical judgment.

23, SQL statement requires all research and development, SQL keywords are all uppercase, each word is only allowed to have a space

24,, can NOT NOTIN NOT NOTIN, pit too much. SQL > select * from tablea where id (select id from tableB) select * from tableA where id (select id from tableB

Select * from table AwhereExists (select * from table BwhereB.i d = table Anderson, d)Copy the code

The distinction between in and EXISTS mainly results in a change in driver order (which is key to performance changes). In the case of exists, the outer layer table is the driver table and is accessed first. In, the subquery is executed first. So IN is suitable for the situation where the outside is large and the inside is small; EXISTS applies to a situation where the outer surface is small and the inner surface is large. For not in and NOT EXISTS, it is recommended to use not EXISTS, not in May have logic problems, not in is not just a matter of efficiency. How to efficiently write a SQL statement that replaces not EXISTS?

The original SQL statement

The select colname... From A tablewhere a.id not in(select b.id from B)Copy the code

Efficient SQL statements

The select colname... From table A Left join table B onwhere a.id = b.id where b.id is null

Copy the code

The extracted result set is shown in the figure below, with data in table A not in Table B

One question is, how do you calculate the performance status key metrics?

QPS, Queries Per Second: indicates the number of Queries processed by a database Per Second. TPS, Transactions Per Second: indicates the number of Transactions processed Per Second

Original: www.kancloud.cn/miant/yii2/…