1. Create table specifications
1.1 A field representing a concept, named is_xxx, of unsigned tinyint type;
1.2 Use lowercase letters or digits for table names and field names. Disallow the beginning of a number, disallow a number between two underscores. The reserved word is disabled. Reserved words are strings with specific semantics, such as SELECT, DISTINCT, FROM, range, and desc.
1.3 You are advised to name the table as project Name_Table Name, and base modules as module name_table Name.
1.4 Index Creation Name The unique index name is named uk_name. The normal index name is idx_name. The joint index is named idx_COL1_COL2;
Create index SQL
Create primary key unique constraint index
ALTER TABLE action_all ADD PRIMARY KEY uk_id (`id`) USING BTREE;
Create and drop normal indexes; The left-most prefix principle actually creates two sets of composite indexes
-- Combination 1: type; Combination 2: Type, sellerNick
ALTER TABLE action_all ADD INDEX `idx_type_sellerNick` (`type`, `sellerNick`);
ALTER TABLE action_all DROP INDEX `idx_type_sellerNick`;
Copy the code1.5 Decimals are of type decimal and float or double are prohibited
1.6 If the stored strings are nearly the same length, char is used as a fixed-length string
1.7 Appropriate character storage length not only saves database table space, saves index storage, but also improves retrieval speed
1.8VARCHAR is a string of variable length. Storage space is not allocated in advance. The length should not exceed 5000.
1.9 Add primary key IDS to the table, record state, create time create_time, update time update_time.
1.10 When changing the meaning of a field or appending a field indicating the status, update the field comment.
1.11 fields allow for some redundancy to improve performance.
1.12 if the capacity of a single table exceeds 2GB or the number of rows in a single table exceeds 5 million, you are advised to divide databases and tables.
Key and index can be used to create a database index. Index categories include primary key index, unique index, common index; Key creates constraint indexes for primary key indexes and unique indexes. Index Creates a common index. Key has dual meaning, constraint and index, which emphasizes constraint standard database integrity and index auxiliary query function at the same time. These indexes include primary key, unique key, and foreign key. Key contains constraint and index. Index is only used as an auxiliary query, there is no constraint, the constraint is the key, index is created in another InnoDB tablespace as a directory structure stored.
[Question 2] Why index is used instead of reserving the word key? Index serves only as a common index and supports query. Key creates constraint and index. Keys are usually created to constrain table behavior, not for queries, such as unique constraints or foreign key constraints. For ancillary queries only, create index indexes to assist queries.
A composite index is relative to a single field index. It supports the creation of a composite index for multiple fields in the same way as a single field index. The combination index follows the left-most prefix principle. The combination order is from left to right. The combination index column is unique. Combined index advantages, index overwrite and index push-down. Index coverage reduces many back-table operations and improves query efficiency. Index push-down, the more index columns there are, the less data will be filtered through the index.
If the table has a primary key, the primary key is the clustered index. If the table has no primary key, the first NOT NULL column with a unique constraint is used as the cluster index by default. If there is no primary key and no uniquely constrained column, a hidden ROW_ID is created as the cluster index by default;
The leaf node of InnoDB’s clustered index stores a page structure. A page contains multiple rows of data and can be directly located to row records. InnoDB must have at least one clustered index. The value of the leaf node of the clustered index is all the column values, and the value of the leaf node of the non-clustered index is the index column + primary key.
Common index is also called secondary index, in addition to the clustered index index, that is, non-clustered index. InnoDB’s plain index leaf node stores primary key (clustered index) values, while MyISAM’s plain index stores record Pointers.
Index files are independent storage files of InnoDB, which occupy a certain amount of disk space. Do not create too many indexes for a table. Otherwise, index files in a large table quickly expand, and you need to create optimal indexes. When updating insert/update/delete tables, you need to update index files at the same time, which has a certain speed impact.
【 Question 6 】 Index failure left fuzzy or full fuzzy query, according to the B+ tree structure, can not be left fuzzy query, index failure. Indexes fail when range queries >, <, or between are used on index columns. The index is invalid if there is an OR reserved word in the conditional query. Index invalidation occurs when the result set of the query exceeds 20% of the total table. The index is invalid when a query condition uses a function on an index column. String conditional query should be enclosed in single quotation marks; otherwise, the index will be invalidated due to implicit conversion. Perform logical operations on index columns, including +, -, *, /, <>,! And so the index is invalidated, so! = invalidates indexes in conditional queries; In this case a functional index can be created to handle this. For composite indexes, the left field is not used according to the left-most prefix principle, and the index is invalid. In, not in, EXISTS, not EXISTS The use of reserved words will invalidate the index. B-tree index is null, is not NULL Does not run the index, bitmap index is null, is not NULL does run the index. Index invalidation occurs when the variable is times and the table field is date, or vice versa.
Statements that normally use indexes:
Some scenarios of index failure:
2. Index protocol
2.1 Unique indexes must be created for fields with unique features
2.2 Do not use join when more than three tables are used for joint query. Fields that need to be joined need to be of exactly the same data type; When multiple tables are used for associated query, ensure that indexes are created for associated fields.
2.3 When creating indexes, the storage engine has a strict limit on the length of index keys. The maximum length of all index keys cannot exceed 1000 and is not the total of the actual data length, but the total of the defined key field length.
Calculation mode of the main character set: latin1 = 1 byte = 1 character UFT8 = 3 Byte = 1 character GBK = 2 byte = 1 character
2.4 page search rigorous left fuzzy or full fuzzy, must be through the search engine to solve.
The index file has the left-most prefix matching feature of b-tree. The left value cannot be determined and the index cannot be used. That is, the index is invalid in the case of left-fuzzy or full-fuzzy query.
2.5 For the use of order by, pay attention to the orderliness of the index. The last field of order BY is part of the composite index and placed at the end of the index combination order to avoid file_sort, which affects query performance.
For the composite index IDx_A_b_C; Where a= where? and b=? order by c; WHERE a>10 ORDER BY b; WHERE a>10 ORDER BY b; Index A_b cannot be sorted. That is, indexes fail when range queries are used on index columns;
2.6 Paging Query Optimization Delay query or sub-query optimization is used to quickly locate id segments to be obtained and then associate them
SELECT a.* FROM 表 1 a, (select id from 表 1 whereConditions LIMIT100000.20 ) b where <u>a.id</u> =<u>b.id</u>
Copy the codeLimit 1 is used to query a single record. Select a specific field is more efficient than select *; Count the number of records using Select count(*) complex function nesting can be converted into code logic to handle; The efficiency of instr() is higher than that of LIKE. The index failure problem is not exists better than that of NOT in. The index failure problem is considered
2.7 Target of SQL performance optimization: at least range level, ref level is required, conSTS is the best.
Consts has at most one matching row (primary key or unique index) in a single table that can be read in the optimization phase. Ref refers to using a normal index. Range Performs a range search on an index.
2.8 When creating a composite index, the most distinguishable index is at the far left
2.9 When the SQL statement WHERE conditions include equivalent query and range query, the equivalent query comes first.
select id from custom where code = 'code' and createTime > '2021-07-01 00:00:00';
Copy the code2.7 Explain statement execution plan analysis
The execution stages of a statement are divided into
- The FROM stage
- WHERE phase
- GROUP BY phase
- HAVING phase
- The SELECT phase
- The ORDER BY phase
- LIMIT stage
- Select_type item
SIMPLE: Simple SELECT, do not use UNION or subquery, etcPRIMARY: The outermost query in a subquery. If the query contains any complex subparts, the outermost select is marked as PRIMARYUNIONThe second or subsequent SELECT statement in: UNIONDEPENDENT UNIONThe second or subsequent SELECT statement in: UNION, depending on the query outsideUNION RESULT: the result of the UNION, the second select in the UNION statement starts all subsequent selectSUBQUERY: The first SELECT in the subquery whose results are independent of the external queryDEPENDENT SUBQUERY: The first SELECT in the subquery, which depends on the external queryDERIVED: A subquery of the SELECT FROM clause of a derived tableUNCACHEABLE SUBQUERY: The result of a subquery cannot be cached, and the first line of the external link must be reevaluated
- Type: The performance of ALL table queries is poor. The performance of NULL table queries is the best
ALL: Full Table ScanMySQL will traverse the table to find a matching rowindex: Full Index Scan, index differs from ALL. Index traverses only the index treerange: Retrieves only rows of a given range, using an index to select rowsref: represents the join matching criteria for the above table, that is, which columns or constants are used to find values on index columnseq_refFor each index key value, only one record in the table matches. In simple terms, a primary key or unique key is used as the association condition in a multi-table joinConst, system: These types of access are used when MySQL optimizes part of a query and converts it to a constant. MySQL can convert the query to a constant if the primary key is placed in the WHERE list. System is a special case of const type, and is used when the table being queried has only one rowNULLMySQL breaks down statements during optimization without even accessing a table or index. For example, selecting a minimum value from an index column can be done by a separate index lookup.
- Possible_keys with key
Possible_keys contains key and is a superset of key. Key indicates the index that may be used in the query
- key_len
Represents the number of bytes used in the index, from which the length of the index used in the query can be calculated; The shorter the length is, the better, without sacrificing accuracy (key_len displays the maximum possible length of the index field, not the actual length used, i.e. key_len is calculated from the table definition, not retrieved from the table)
- rows
A comparison of columns to indexes that indicates the join match criteria for the above table, that is, which columns or constants are used to find values on indexed columns
- extra
Using temporary: the mysql server will filter rows that are retrieved by the storage engine after they have been retrieved by the storage engine. MySQL needs to use temporary tables to store result sets. Order by Using filesort: when a Query contains an order by operation that cannot be completed Using an index, the operation is called “filesort”. The value change highlights the lack of use of indexes when retrieving connection conditions and the need for a connection buffer to store intermediate results. If this value is present, it should be noted that indexes may be added to improve performance depending on the query. Impossible WHERE: This value emphasizes that the WHERE statement will result in no eligible rows (the result cannot exist by collecting statistics). Select Tables Optimized Away: This value means that by using the index alone, the optimizer may return only one line from the aggregate function result with No tables Used: Query statement using FROM Dual or without any FROM clause
【 question 1 】 What is file_sort
If we need to continue sorting by a field without adding an index, we will see filesort in Extra if we query the SQL using Explain. If you have an index, you can just fetch it, you don’t need to sort it; If no index is created, the sorted fields will be sorted in the query. You need to load all the data into the memory for sorting. If the memory is insufficient, the database will crash.
Select * from sellerNick; select * from sellerNick;
Buffer size view for filesort
If the query is clustered index, as a conditional query, only one scan of B+ tree can locate the row record through the clustered index; For a normal index, the B+ tree is scanned twice to locate the row record, first to the value of the clustered index through the normal index, and then to the row record through the value of the clustered index through a second scan of the B+ tree. This is called a table-back query.
3. The SQL code
3.1 Use select count(*) instead of select count(col). Count (*) is the standard row count syntax defined in SQL92, independent of database, NULL and non-NULL. Count (*) counts rows with a value of NULL; Count (col) does not count;
3.2 count(distinctCOL) Counts the number of non-duplicates of that column other than NULL. Note count(distinctCOL1, COL2) If all of one column is NULL, then zero is returned even if the other column has a different value.
3.3 If all values in a column are NULL, count(col) returns 0, but sum(col) returns NULL
3.4 use ISNULL() to check whether the value ISNULL. Note: A direct comparison of NULL to any value is NULL.
NULL<>NULL returns NULL, not false. NULL=NULL returns NULL, not true. NULL<>1 returns NULL, not true.
3.5 Pagination logic in code, if count is 0, it directly returns to avoid the execution of subsequent pagination statements.
3.6 Foreign Keys are not recommended for Cascading. All foreign key concepts can be solved by business logic at the application layer. Foreign keys and cascading update are suitable for low-concurrency single machine, but not for distributed and high-concurrency cluster. Cascading update is strongly blocked, and there is the risk of database update storm. Foreign keys affect database insert speed.
3.7 The use of stored procedures is prohibited. It is difficult to debug extensions and transplant.
3.8 IN, not in Avoid index invalidation.
3.9 If globalization is required, all characters are stored and represented using UTF-8 encoding. If you want to use emoticons, use UTFMB4 for storage.
3.10 TRUNCATETABLE is faster than DELETE and uses fewer system and transaction log resources. However, TRUNCATE has no transaction and does not trigger, which may cause accidents. Therefore, it is not recommended to use this statement in development code
Why should we try to avoid FileSort? Powered By niaonao