Mysql specification for use of tables and indexes

1. For MySQL table, the field shall be set as non-empty, and the default value of the field shall be set.
2. When the MySQL table is built and the NULL field is required, the default value of the field shall be set, and the default value shall not be NULL.
3.MySQL table, if the field is equivalent to a foreign key, the field should be indexed.
4.MySQL is used to build the table. The fields, column types, type lengths, non-null values and default values of the same attribute values between different tables should be kept at 1, otherwise the index cannot be used for association comparison.
5. When MySQL is used, one SQL statement can only use one index of one table. All field types can be indexed, with a maximum of 15 properties for multi-column indexes.
6. If you can choose between multiple indexes, MySQL usually USES the index that finds the least rows and the index with the highest value of 1.
7. The establishment of indexes index (part1,part2,part3) is equivalent to the establishment of three indexes index(part1),index(part1,part2) and index (part1,part2,part3).
8.MySQL must use an index for the like syntax in the following format:
SELECT * FROM t1 WHERE key_col LIKE 'ab%';
9.SELECT COUNT(*) syntax is not as efficient in statements without where conditions as in statements with SELECT COUNT(col_name), but it is faster in statements with where conditions.
10. In the where condition, multiple and conditions must all be the key_part attribute of a multi-column index and must contain key_part1. For each single 1 index, use only the index that traverses the least number of rows.
101. In the where condition, each of the multiple or conditions must be a valid index.
102. The condition after ORDER BY must be an attribute of the same index and the sort order must be 1 (for example, both in ascending or descending order).
103. All GROUP BY columns reference properties of the same index, and the index must keep its keywords in order.
104.JOIN index, all fields matching ON and where shall be properly indexed.
105. Intelligent scanning of the full table using FORCE INDEX to inform MySQL is more efficient using the index.
106. Periodically ANALYZE TABLE tbl_name updates the keyword distribution for the scanned table.
107. Periodically use slow log check statements, execute explain, and analyze possible indexes for improvement.
108. If possible, set the values of key_buffer_size and query_cache_size (global parameters) and sort_buffer_size (session variable, not more than 4M is recommended).
note
The primary key is named according to the following rules:
The primary key name begins with pk_, followed by the table name where the primary key is located. The primary key name cannot be longer than 30 characters. If it is too long, you can abbreviate the table name. The abbreviation rule is the same as the abbreviation rule of the table name. The primary key name is represented as a lowercase English word.

Foreign keys are named according to the following rules:
The foreign key name begins with fk_, followed by the table name where the foreign key resides and the corresponding main table name (t_ excluded). Child and parent table names are themselves separated by an underscore (_). Foreign key names cannot be longer than 30 characters. If it is too long, you can abbreviate the table name. The abbreviation rule is the same as the abbreviation rule of the table name. Foreign key names are denoted by lowercase English words.

The index is named according to the following rules:
1) index names are in lowercase letters and Numbers. The length of the index name cannot exceed 30 characters.
2) the index corresponding to the primary key and the primary key have the same name.
3) the exclusive index begins with uni_, followed by the table name. 1 generic indexes begin with ind_, followed by the table name.
4) if the index is too long, you can abbreviate the table name. The abbreviation rule is the same as the abbreviation rule of the table name

index related syntax
Ex. :
CREATE INDEX log_url ON logaudit_log(url);
show index from logaudit_log
drop index log_request_time on logaudit_log

sql performs efficiency testing mysql explain
explain shows how mysql USES indexes to process select statements and join tables. You can help select better indexes and write more optimized queries.
To use, add explain before select statement:
explain select surname,first_name form a,b where a. id= b. id
The analysis results are as follows:
table | type | possible_keys | key | key_len | ref | rows | Extra
Explanation of EXPLAIN column:
table
Shows which table this row is about
type
This is the important column that shows what type of connection is used. The best to worst connection types are const, eq_reg, ref, range, indexhe, and ALL
possible_keys
Shows the indexes that might be applied to this table. If null, there is no possible index. You can select an appropriate statement from the WHERE statement for the relevant domain
key
The actual index used. If it is NULL, no index is used. Very rarely, MYSQL will select an index that is not sufficiently optimized. In this case, you can use USE in an SELECT statement
INDEX (indexname) to force the use of 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, without losing accuracy
ref
Shows which column of the index is used, if possible, as a constant
rows
The number of rows that MYSQL considers necessary to check to return the requested data
Extra
Additional information on how MYSQL parses queries. This is discussed in table 4.3, but the bad examples you can see here are Using temporary and Using filesort, meaning MYSQL can't use indexes at all, resulting in slow retrieval
The meaning of the description returned by the extra column
Distinct
Once MYSQL has found a row that matches the row associated with it, it no longer searches
Not exists
MYSQL optimizes LEFT JOIN, and once it finds a line that matches LEFT JOIN,
I'm not searching anymore
Range checked for each
Record (index map:#)
No ideal index was found, so for each combination of rows from the previous table, MYSQL checks which index was used and USES it to return rows from the table. This is 1 of the slowest joins using the index
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 that match the condition
Using index
Column data is returned from a table that only USES the information in the index and does not read the actual action, which occurs when all of the requested columns for 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, which usually happens on ORDER BY instead of GROUP BY for different column sets
Where used
The WHERE clause is used to limit which rows match the next table or are returned to the user. This can happen if you don't want to return all the rows in the table and the join type is ALL or index, or if the query has a problem
Interpretation of different connection types (in order of efficiency)
system
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 the table matches the query (the index can be either a primary key or a 1-only index). Because there's only one row, this value is actually a constant, because MYSQL reads this value and treats it as a constant
eq_ref
In joins, MYSQL reads one record from the table for each record in the query from the previous table, which is used when the query USES either the primary key of the index or the entire one-only key
ref
This join type occurs only if the query USES a key that is not a unique 1 or primary key, or a part of these types (for example, using the leftmost prefix). For each row union of the previous table, all records will be read from the table. This type depends heavily on how many records are matched against the index-the less the better
range
This connection type USES an index to return rows in a range, such as using & gt; or
FAQ
1
The table contains 100,000 records with one field of type datetime.
Statement to fetch data:
SELECT * FROM my_table WHERE created_at < '2010-01-20';
Check with EXPLAIN and find that type is ALL, key is NULL, there is no index at all.
To be sure, the created_at field is indexed.
Why?
Using SELECT COUNT(*), I looked at the total number of records that met the WHERE condition, which was 6W!!
No wonder you don't need an index, it makes no sense to use an index. It's like a user table with 100, 000 records, with a gender field, either male or female.
Slightly modify 1 of the above statement:
SELECT * FROM my_table WHERE created_at BETWEEN '2009-12-06' AND '2010-01-20';
Problem solved this time!
There are only a few hundred eligible records, type for EXPLAIN is range, key is created_at, Extra is Using where.
As a rule of thumb, the purpose of an index is to minimize the result set so that it can be queried quickly.

60,000 records are eligible, which is more than one and a half of the total number of records. At this time, the index is meaningless, so MySQL gives up using the index.
This is similar to setting the gender field and indexing it. When you want to select all male records, the number of eligible records is about one-half of the total. MySQL also does not use this index.
The more 1-only fields, the better the index.
When setting a joint index, the more 1's you have, the more you should put them on the left.