Mysql table creation and index usage specifications _ MySQL

Detailed description of Mysql table creation and index usage specifications bitsCN.com

1. when creating a MySQL table, the field must be set to a non-empty value and the default value of the field must be set.
2. when creating a MySQL table, if the field needs to be NULL, you need to set the default value of the field. the default value is not NULL.
3. create a MySQL table. if a field is equivalent to a foreign key, add an index to the field.
4. when creating a MySQL table, fields with the same attribute values between different tables, column type, type length, whether it is non-null, whether it is the default value, must be consistent, otherwise, the index cannot be correctly used for association comparison.
5. when using MySQL, one SQL statement can only use one index of one table. All field types can be indexed. up to 15 attributes can be indexed for multiple columns.
6. if you can select among multiple indexes, MySQL usually uses the index with the lowest row and the index with the highest unique value.
7. create an index (part1, part2, part3), which is equivalent to three indexes: index (part1), index (part1, part2), and index (part1, part2, part3.
8. MySQL uses indexes only in the following format for the like syntax:
SELECT * FROM t1 WHERE key_col LIKE 'AB % ';
9. The select count (*) syntax is less efficient than select count (col_name) in statements without the where condition, but faster in statements with the where condition.
10. in the where condition, multiple and conditions must be the key_part attribute of a multi-column index and must contain key_part1. For each single index, only the index that traverses the least rows is used.
11. in the where condition, each condition must be a valid index.
12. The conditions after order by must be attributes of the same index, and the sorting ORDER must be consistent (for example, both are ascending or descending ).
13. all group by columns reference the attributes of the same index, and the indexes must store their keywords in order.
14. JOIN indexes. appropriate indexes should be created for all fields matching ON and where.
15. Use force index to intelligently scan the entire table to inform MySQL of the higher efficiency of using indexes.
Sixteen. regularly ANALYZE the TABLE tbl_name as the updated keyword distribution of the scanned TABLE.
17. regularly use slow log check statements and execute explain to analyze indexes that may be improved.
18. if conditions permit, set larger values of key_buffer_size and query_cache_size (global parameter), and sort_buffer_size (session variable, not more than 4 MB ).
Remarks
The primary key naming rules are as follows:
The primary key name starts with pk _, followed by the table name where the primary key is located. The primary key name cannot exceed 30 characters. If it is too long, you can abbreviated the table name. The abbreviation rule is the same as the abbreviation rule of the table name. The primary key name is represented by lowercase English words.

The naming rules for foreign keys are as follows:
The foreign key name starts with fk _, followed by the table name of the foreign key and the corresponding primary table name (excluding t _). The sub-table name and parent table name are separated by underscores. The length of the foreign key name cannot exceed 30 characters. If it is too long, you can abbreviated the table name. The abbreviation rule is the same as the abbreviation rule of the table name. The foreign key name is represented by lowercase English words.

The index naming rules are as follows:
1) the index name is represented by lowercase English letters and numbers. The index name cannot exceed 30 characters.
2) the index corresponding to the primary key has the same name as the primary key.
3) the unique index starts with uni _, followed by the table name. The general index starts with ind _, followed by the table name.
4) If the index length is too long, you can abbreviation the table name. Abbreviation rules are abbreviated rules with the same table name

Index syntax
Example:
Create index log_url ON logaudit_log (url );
Show index from logaudit_log
Drop index log_request_time on logaudit_log

SQL execution efficiency detection mysql explain
Explain shows how mysql uses indexes to process select statements and connect tables. It can help you select better indexes and write more optimized query statements.
You can add the explain statement before the select statement:
Example: explain select surname, first_name form a, B where a. id = B. id
The analysis result is as follows:
Table | type | possible_keys | key | key_len | ref | rows | Extra
Description of the EXPLAIN column:
Table
Which table is the data of this row?
Type
This is an important column that shows the type used by the connection. The connection types from the best to the worst are const, eq_reg, ref, range, indexhe, and ALL.
Possible_keys
Displays indexes that may be applied to this table. If it is null, there is no possible index. You can select an appropriate statement from the WHERE statement for the relevant domain.
Key
Actually used index. If it is NULL, no index is used. In rare cases, MYSQL selects an optimized index. In this case, USE
INDEX (indexname) to force an INDEX or ignore index (indexname) to force MYSQL to IGNORE the INDEX
Key_len
The length of the index used. The shorter the length, the better.
Ref
It indicates which column of the index is used. if possible, it is a constant.
Rows
MYSQL considers that the number of rows that must be checked to return the requested data
Extra
Additional information about how MYSQL parses the query. We will discuss it in Table 4.3, but here we can see that the bad examples are Using temporary and Using filesort, which means MYSQL cannot use indexes at all, and the result is that the retrieval will be slow.
Meaning of the description returned by the extra column
Distinct
Once MYSQL finds the row that matches the row, it does not search any more.
Not exists
MYSQL optimizes left join. once it finds a row that matches the left join standard,
No more search
Range checked for each
Record (index map :#)
No ideal index is found. Therefore, for each row combination in the preceding table, MYSQL checks which index is used and uses it to return rows from the table. This is one of the slowest connections using indexes.
Using filesort
When you see this, the query needs to be optimized. MYSQL requires additional steps to find out how to sort the returned rows. It sorts all rows according to the connection type and the row pointer that stores the sort key value and all rows matching the condition.
Using index
The column data is returned from a table that only uses the information in the index but does not read the actual action. This occurs when all the request columns in the table are part of the same index.
Using temporary
When you see this, the query needs to be optimized. Here, MYSQL needs to create a temporary table to store the results. this usually happens when order by is applied to different column sets, rather than group.
Where used
The WHERE clause is used to limit which rows match the next table or are returned to the user. If you do not want to return ALL rows in the table and the connection type is ALL or index, this may occur or the query is faulty.
Explanations of different connection types (sorted by efficiency order)
System
The table has only one row: system table. This is a special case of the const connection type.
Const
The maximum value of a record in a table can match this query (the index can be a primary key or a unique index ). Because there is only one row, this value is actually a constant, because MYSQL first reads this value and treats it as a constant.
Eq_ref
During connection, MYSQL reads a record from the union of each record in the previous table during query, it is used when you query all data that uses the index as the primary key or unique key.
Ref
This connection type only occurs when the query uses keys that are not the only or primary key, or some of these types (for example, using the leftmost prefix. For each row union in the previous table, all records are read from the table. This type is heavily dependent on the number of records matched by the index-the less the better
Range
This connection type uses indexes to return rows in a range, such as> or
FAQ
1
The table contains 0.1 million records with a datetime field.
Data retrieval statement:
SELECT * FROM my_table WHERE created_at <'2017-01-20 ';
Using the EXPLAIN check, we found that the type is ALL, the key is NULL, and no index is used.
It can be determined that the created_at field sets the index.
Why?
Use select count (*) to check the total number of records that meet the WHERE condition. The total number is more !!
No wonder you don't need to use an index. at this time, it makes no sense to use an index. it is like a user table with 0.1 million records, with a unique character Field. it is wrong to set an index in this field.
Slightly modify the above statement:
SELECT * FROM my_table WHERE created_at BETWEEN '2017-12-06 'AND '2017-01-20 ';
Solve this problem!
There are only a few hundred matching records. the type of the EXPLAIN statement is range, the key is created_at, and the Extra is Using where.
Summarize the criteria by yourself. the purpose of indexing is to narrow down the result set as much as possible so as to achieve quick query.

60 thousand records meet the condition and have exceeded half of the total number of records. at this time, the index is meaningless, so MySQL does not use the index.
This is similar to setting the gender field and adding indexes. when you select all male records, the number of matching records accounts for about half of the total, mySQL does not use this index.
The more fields with unique values, the better the index effect.
When you set the Union index, the more unique values, the more should be placed on the "left ".

BitsCN.com