What are storage engine categories and their application scenarios?

(1) : storage engine: 1. MyIsam, 2. InnoDB, 3. Default :InnoDB engine. The underlying storage structure of InnoDB is B+ tree. Each node of the B tree corresponds to a page of InnoDB. The page size is fixed and is generally set to 16K (2). 1) Frequently updated tables, suitable for handling multiple concurrent update requests. 2) Support transactions. 3) Recovery from disasters (through bin-log, etc.). 4) Foreign key constraints. Only he supports foreign keys. 5) Support automatic increment of column attribute AUTO_INCREMENT.Copy the code

Principles for creating indexes?

(1) : Select unique index: unique index value is unique. This index can be used to determine a record more quickly. (2) : Create indexes for columns that often need to be sorted, grouped, and syndicated. (3) : Create indexes for columns that are often used as query criteria. (4) : limit the number of indexes: the more indexes, the lower the query efficiency of the table, because the index table will re-create the index of the table when updating the table data. The more data the table has, the more index columns, the more time it takes to create the index. (5) : If the index value is very long, the speed of the query will be affected. (6) : If the value of the index field is long, it is best to use the worth prefix for the index. (7) : Delete indexes that are no longer used or rarely used. (8) : the most left prefix matching principle, a very important principle. (10) : Index columns cannot participate in the calculation, keep the column "clean" : the query with function does not participate in the index. (11) : Expand indexes as far as possible, do not create new indexes.Copy the code

Index failure condition? == Verify whether the SQL statement uses indexes. Use the explain keyword == before the SQL statement

(1) : index invalid if like begins with %, index valid if like ends with &. (2) : No colleague before or after the OR statement uses the index. The index takes effect only when both the columns before and after the query condition of the OR statement are indexes. (3) : combined index, when the index is not used in the first column, the index will be invalid, that is, the left-most matching rule. (4) : The data type is implicitly converted. For example, vARCHar without single quotation marks may be automatically converted to int. In this case, the index fails. (5) : NULL IS NOT NULL IS NOT NULL; (6) : use on index fields, NOT, <>,! =, when the index is not used, only a full table scan is performed for such processing. (7) : the index field is calculated. The index will not be used in the function operation. (8) : The index will not be used when the full table scan speed is faster than the index speed.Copy the code

Index classification?

1) Plain index: a basic index type in MySQL. It allows you to insert duplicate and null values in columns where the index is defined. 2) unique index: the value in the index column must be unique, but null values are allowed. 3) Primary key index: a special unique index that does not allow null values. (2) : Composite index: an index created on a combination of multiple fields. The index will be used only when the left field of the field is used in the query condition. The composite index follows the leftmost prefix set. (3) : Full-text index: SQL > select * from CHAR,VARCHAR,TEXT; select * from CHAR,VARCHAR,TEXT; select * from CHAR,VARCHAR,TEXT; Spatial index: A spatial index is an index of spatial data types. There are four spatial data types in MySQL, namely, GEOMETRY, POINT, LINESTRING, and POLYGON. Use the SPATIAL keyword when creating SPATIAL indexes. If the engine is MyISAM, the column that creates the spatial index must be declared NOT NULL.Copy the code

Add index to Linux

ALTER TABLE 'table_name' ADD PRIMARY KEY (' column ') ALTER TABLE 'table_name' ADD UNIQUE (' column ') ALTER TABLE 'table_name' ADD INDEX index_name (' column ') ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )Copy the code

What is an index?

An index is a data structure that helps us quickly retrieve data in a database.Copy the code

What data structure does the index use?

Common mysql has two main structures: hash index and B+Tree index. We use innoDB engine, the default is B+Tree.Copy the code

Since InnoDB uses B+ tree index model, do you know why use B+ tree? Does this have any disadvantages compared to Hash indexing?

The underlying hash index is a hash table, which is a key-value data storage structure. Therefore, the storage relationship of multiple data has no sequential relationship at all. Therefore, the interval query cannot be directly queried through the index, so full table scan is required. Therefore, hash indexes are only suitable for equivalent query scenarios. B+ tree is a multi-way balanced query tree, so its nodes are naturally ordered (the left child node is smaller than the parent node, and the parent node is smaller than the right child node), so there is no need to do full table scan for range query.Copy the code

What is the difference between a B+ Tree index and a Hash index?

1: hash index is suitable for equivalent query, but cannot be used for range query. 2: Hash indexes cannot be sorted using indexes. 3: The hash index does not support the left-most matching rule of the multi-column joint index. 4: If there are a large number of duplicate keys, the efficiency of hash index will be very low because of hash collision.Copy the code

What can a page child node of a B+Tree store?

1: innoDB's B+Tree may store entire rows of data, or may store primary key values.Copy the code

InnoDB B+Tree stores whole rows of data and primary keys.

1: Whole row data: innoDB's B+Tree is a primary key index that stores whole rows of data, also known as a clustered index. 2: Stores the primary key value: becomes a non-primary key index, also known as a non-clustered indexCopy the code

Is there a difference between a clustered index and a non-clustered index when querying data? Why is that?

Clustered index queries are faster. The page children of the primary key index tree store entire rows of data. That's the data we need to get. The page child node of the non-primary key index is the value of the primary key. After querying the primary key, we need to query the data again by the value of the primary key. (This process is called table-back.)Copy the code

Must non-primary key indexes be queried multiple times?

Not necessarily? Because overwriting an index also allows you to query only once.Copy the code

What is an override index?

(1) : overridden index means that the execution of a query statement can be obtained only from the index. You don't have to read from a data table. It can also be called index coverage. When a query meets the overwrite index condition, mysql only needs to return the data required by the query through the index. In this way, table back operations can be avoided and I/O can be reduced to improve efficiency. (2) : The table covering_index_SAMPLE has a common index idx_KEY1_KEY2 (key1,key2). Select key2 from covering_index_SAMPLE where key1 = 'keytest'; Can be queried by overwriting the index without returning to the table.Copy the code

Leftmost matching principle?

When creating a federated index, you generally follow the leftmost matching principle. That is, the attribute with the highest identification degree in the joint index is placed at the front of the query statement.Copy the code

What indexes are optimized for mysql5.6 and mysql5.7?

Mysql5.6 introduces index push-down optimization, which is enabled by default. Example: The user table (a,b,c) forms an index. Select * from user where a='23' and b like '%eqw%' and c like 'dasd' If there is no index push-down rule, mysql will query the corresponding data with a='23'. It then returns to the mysql server. The mysql server then verifies that the data queried by and meets the criteria based on two like fuzzy queries. This process is designed to go back to the table operation. If the index push-down technique is used, mysql will first return the index with condition A ='23', and then verify whether the index meets the condition according to the fuzzy query condition. If the condition is met, mysql will directly locate the corresponding index according to the index. If the condition is not met, it will reject the index. Therefore, with index push-down optimization, you can reduce the number of times back to the table with like conditions.Copy the code

How to check whether the SQL statement uses index query?

Use Explain to query the execution plan of SQL statements to analyze index usage.Copy the code

How does the optimizer execute?

1: Find out possible indexes based on the search criteria. 2: Calculates the cost of a full table scan. 3: Calculates the cost of executing queries using different indexes. 4. Compare the cost of various execution schemes and find out the one with the lowest cost.Copy the code