preface

Today, I will give you a list of some common questions about the database. If you are interested in these questions, you can expand your research.

A UNION ALL b UNION ALL C UNION ALL D UNION

  • The UNION and UNION ALL keywords combine two result sets into one.

  • UNION will filter out duplicate records after table linking, so it will sort the result set generated after table linking, delete duplicate records and then return the result.

  • UNION ALL simply merges the two results and returns.

  • The efficiency of a UNION ALL is much better than that of a UNION because the UNION needs to be sorted and de-duplicated.

2. TRUNCATE is different from DELETE

  • TRUNCATE is a DDL statement, while DELETE is a DML statement.
  • TRUNCATE is to drop the entire table and then rebuild the table. Therefore, the speed of TRUNCATE must be faster than that of DELETE.
  • TRUNCATE cannot be rolled back, but DELETE can.
  • TRUNCATE execution results only return0 rows affected, can be interpreted as no result returned.
  • TRUNCATE resets the horizontal line (since the start of the increment column), DELETE does not.
  • TRUNCATE clears only the entire table, and DELETE can be deleted based on conditions.
  • In general, TRUNCATE performance is better than DELETE.

3. Difference between TIMESTAMP and DATETIME

The same

  • The TIMESTAMP column is displayed in the same format as the DATETIME column. The display column width is fixed at 19 characters and is formatted asYYYY-MM-DD HH:MM:SS.

The difference between

  • TIMESTAMP
    • 4 bytes of storage, time range:The 1970-01-01 08:00:01 ~ 1970-01-01 11:14:07.
    • The value is saved in UTC format and involves time zone conversion. The current time zone is converted for storage and then returned to the current time zone for retrieval.
  • DATETIME
    • 8-byte storage, time range:The 1000-10-01 00:00:00 ~ 1000-10-01 23:59:59.
    • Actual format storage, time zone independent.

4. What is a federated index

An index on two or more columns is called a combined index, and a combined index is also called a composite index.

5. Why use federated indexes

  • To reduce overheadCreate index (col1,col2,col3) create index (col1,col2,col3) Reduce disk space overhead.
  • Cover indexSelect col1,col2,col3 from test where col1=1 and col2=2. So MySQL can get the data directly by traversing the index, andDon’t need to back to the tableThis reduces a lot of random IO operations. Overwriting indexes is one of the main optimization tools to improve performance.
  • High efficiency: The more index columns, the less data is filtered through the index. A table with 1000W data has the following SQLselect from table where col1=1 and col2=2 and col3=3, assuming that each condition can filter out 10% of the data, if there is only a single value index, then the index can filter out1000W*10%=100wCol2 =2 and COL3 = 3, then sort, then paginate; If it is a joint index, filter it out by index1000w*10%*10%*10%=1w, the efficiency has been significantly improved.

MySQL > select * from left; MySQL > select * from left

  • MySQL will follow the leftmost prefix matching principle when creating the joint index, that is, the leftmost first, when retrieving data from the leftmost of the joint index matching.
  • MySQL will keep matching to the right until it encounters a range query (>, <, between, like) and then stops matching, for examplea = 1 and b = 2 and c > 3 and d = 4If set up(a,b,c,d)Sequential index, d does not use index, if set up(a,b,d,c)The order of a, B, and D can be adjusted arbitrarily.
  • Theta and in can be out of order, for examplea = 1 and b = 2 and c = 3To establish(a,b,c)The indexes can be in any order, and mysql’s query optimizer will help you optimize them into a form that the indexes recognize.

7. What are clustered and non-clustered indexes

  • A clustered index is an index created with a primary key.
  • A non-clustered index is an index created with a non-primary key.

8. What is an override index

  • Cover index (covering index) refers to an execution of a query statement only can obtain from the index page (if not clustered index, stored in a leaf node is the primary key + column value, finally back to the table, which is to through the primary key to find it again), to avoid the check to the index, do back to the table operation, reduce the I/O efficiency.
  • It is easier to understand in conjunction with question 10.

9. What is a prefix index

A prefix index creates an index on the first few characters of the text, which creates a smaller index. But MySQL cannot use prefix indexes in ORDER BY or GROUP BY, nor can they be used as overwriting indexes.

Syntax for creating prefix indexes:

ALTER TABLE table_name ADD
KEY(column_name(prefix_length))
Copy the code

10. Differences between InnoDB and MyISAM index storage structure

  • The MyISAM index file and the data file are separate, the index file only holds the address of the data record.
  • In InnoDB, the table data file itself is an index structure organized by B+Tree, and the data field of the leaf node of this Tree holds complete data records. The key of this index is the primary key of the table, so the InnoDB table data file itself is the primary index, so it must have a primary key. If there is no display definition, an implied field is automatically generated as the primary key. This field is 6 bytes long integer type.
  • InnoDB’s Secondary Index (non-primary key Index) stores only primary key columns and Index columns. If the primary key is defined large, the other indexes will also be large.
  • MyISAM engine uses B+Tree as the index structure. The data field of the leaf node of the index file stores the address of the data record and points to the corresponding value in the data file. Each node only has the value of the index column.
  • There is no difference in structure between MyISAM primary index and Secondary key, except that the primary index requires the key to be unique and the Secondary index can be repeated. (Since the MyISAM Secondary index stores the address of the data record on the leaf node, like the primary key index, there is no need to traverse the primary key index again.)

To put it simply:

  1. Main index difference: InnoDB data files themselves are index files. MyISAM’s index and data are separate.

  2. Secondary index differences: InnoDB’s secondary index data field stores the value of the primary key of the corresponding record rather than the address. MyISAM’s secondary index is not much different from the primary index.

11. Why choose monotonically increasing numeric primary keys

  • The data records themselves in InnoDB are stored on the leaves of the main index (B+ tree). This request within the same leaf node (the size of a memory or disk pages) of the individual data records in the primary key order, so every time when we have a new record into the MySQL will according to the primary key and insert it into the appropriate nodes position, if the page to load factor (InnoDB default for 15/16), then create a new page.

  • With auto-increment primary keys, each time a new record is inserted, the records are sequentially added to the current index node, and when a page is full, a new page is automatically opened, resulting in a compact index structure that is approximately sequentially filled. Since there is no need to move the existing data with each insert, this is efficient and does not add much overhead to maintaining the index.

  • If the use of the primary key, because every time into the approximation of random key values, so every time a new record to be inserted into the existing index at a certain position in the middle of the page, the MySQL to new records in order to find suitable location and mobile elements, even the target page may have been back to disk and clear it from the cache, at this time again want to read from the disk back, This added a lot of overhead, while frequent movement, paging caused a lot of fragmentation, resulting in a less compact index structure that had to be rebuilt with the OPTIMIZE TABLE and optimized fill pages.

To put it simply:

The index tree can only be located to one page, and inserts within each page still need to be compared and moved. So ordered primary keys can improve insertion efficiency.

12. The meaning of the length after int when creating a table

The number of bytes int is fixed, and the length represents the maximum width of the display. If not, it will be filled with 0 on the left, but must be used with zerofill. In other words, the length of an int does not affect the accuracy of the data stored, only the length of the display.

13. What does the SHOW INDEX result field mean

  • The Table:

    • The name of the table.

  • Non_unique:

    • 0: This index does not contain duplicate values.
    • 1: This index can contain duplicate values.

  • Key_name:

    • The index name, if an annotated index, is alwaysPRIMARY.

  • Seq_in_index:

    • The sequence number of the column in the index, starting with 1. For example, a federated index existsidx_a_b_c (a.b.c), aSeq_in_index=1, b=2, c=3.

  • Column_name:

    • The column name.

  • Collation:

    • Ascending: A (descending), D (descending), NULL (not sorted).

  • Cardinality:

    • A value that measures the uniqueness of the index and can be usedANALYZE TABLE(INNODB) ormyisamchk -a(MyISAM) updates the value.
    • This field doesn’t mean much if the table has too few records. In general, the larger the value is, the higher the index efficiency is.

  • Sub_part:

    • For a prefix index, the number of characters used for the index. Is displayed if the entire field is indexedNULL.

  • Null:

    • YES: This column allows NULL values.
    • ' ': This column does not allow NULL values.

  • Index_type:

    • Index types, including BTREE, FULLTEXT, HASH, and RTREE.

14. How to resolve index invalidation when like’% string %’?

  • LIKE the problem: like begins with a wildcard (‘% ABC… Mysql index failure will become a full table scan operation.
    • The culprit is %, noLIKE.LIKEOn the condition thattype = rangelevel
    • %xxx%: Full table scan
    • %xxx: Full table scan
    • xxx%: range
  • The solution:
    • Using an overwrite index, you can use theALLintoINDEXWhy? After overwriting the index, you can use the use index to perform a full table scan. Note that when using a match index, you can hit only one field, not all of them.

MySQL efficient pagination

  • There are SQL:SELECT * FROM ttl_product_info ORDER BY id LIMIT N,M. Among themLIMIT N,MThe biggest problem: fetch N+M rows, discard the first N rows, and returnN ~ N+MIf N value is very large, the efficiency is very poor (table record 1500W, N=10000000,M=30 takes 9 seconds).
  • SQL:SELECT id FROM ttl_product_info WHERE id > N LIMIT M, the ID column is the index column,id > NBelong torangeLevel, the efficiency is naturally high, then from the position of 30 records, high efficiency (table records 1500W, N=10000000,M=30, need 0.9ms).
  • Of course, to achieve the above effect, the premise is:
    1. Id is a unique index and monotonically increasing.
    2. The value of N is the last id of the last query record, (need front-end save, can not be obtained directly by traditional methods).
    3. Cross-page query is not supported, only page by page as shown on page 1, 2, 3 and 4.

conclusion

In order to maintain the integrity of the structure of the article, a summary paragraph is imposed here…

Reference article:

  • Segmentfault.com/a/119000001…
  • Blog.csdn.net/bigtree_372…