Shulou(Shulou.com)06/02 Report--

In this issue, the editor will bring you an example analysis of Mysql knowledge points and pilot areas. the article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

The specific data in mysql is stored in rows, while rows are stored in pages. In other words, the page is above the line.

Mysq a page size of 16K, of course, this size can be changed by modifying the configuration file.

When we create a new table, the user records section of the table is empty and will be recorded in it after we insert a record. When the insert is full, the record information will be brushed into the next page.

1.delete_mask: indicates whether the line-changing record is deleted or not. It actually deletes the identification bit of the record and occupies a binary bit, in which 0muri-not deleted 1Mel-has been deleted.

2.heap_no: indicates the location of the current record on this page. In addition, there are two virtual record rows in heap_no that record the primary key values of the maximum and minimum records on the current page.

3.next_record: indicates the address offset from the real data of the current record to the real data of the next record. In fact, this is a single linked list, and you can find the next record through one record. It is worth noting, however, that the next record is not the next record in the order in which we insert it, but the next record in the order in which the primary key values go from small to large. And the next record that specifies the minimum record is the record with the lowest primary key value stored on this page, and the next record of the record with the largest primary key value on this page is the largest record on this page.

The order of records shown in the figure above is the order of records inserted sequentially (1.2.3.4), but we often have non-sequential insertions in actual development. As shown in the following figure:

When the case of non-sequential insertion occurs, although the insertion order has changed from (1.2.3.4) to (1.2.4.3), its linked list order will remain unchanged (1.2.3.4) it should be noted that the sequential insertion of heap_no in the figure is different from the non-sequential insertion of heap_no.

When we have the situation of table record deletion in the actual development process, the corresponding record information in the above figure will also change, for example: the change of the maximum record row and the minimum record row (may change), the change of next_record (refer to the change of single linked list in the data structure, the subsequent pointer null operation), the change of delete_mask (0Lue-not deleted-deleted), the change of heap_no, the change of nowned value.

Note: no matter how we add, delete or modify, InnoDB always maintains a single linked list of the data in the page, and the nodes in the linked list are stored in the order of primary key values from small to large.

Take the following table structure as an example

An index is a sorted data structure, a clustered index, and what I understand is that the generated index is a sequential nonclustered index, which is unordered, similar to abcd... in a dictionary. This is the clustered index, the nonclustered index is similar to the partial radical in the dictionary. The following figure is the index structure generated by the federated index.

To return to the table is to find out the primary key index through the federated index and then to check all the data through the primary key index. This behavior becomes huibiao.

1. When using the like keyword fuzzy query, the% in front of the index does not work, only if "%" is not in the first position, the index will take effect (like'% text'- the index does not work)

2. When using a federated index, the index will take effect only if the first of these fields is used in the query condition.

3. For a query that uses the OR keyword, the index will only take effect if there is only the OR keyword in the query condition of the query statement, and the columns in both conditions before and after OR are indexes, otherwise the index will not take effect.

4. Try to avoid using the! = or operator in the where clause, otherwise the engine will give up using the index and do a full table scan.

5. In order to optimize the query, we should avoid full table scanning as far as possible, and we should first consider establishing indexes on the columns involved in where and order by.

6. Try to avoid expression operations on fields in the where clause, which will cause the engine to give up using the index and do a full table scan. Such as:

Select id from t where num/2=100

It should be changed to:

Select id from t where num=100*2

7. Avoid performing functional operations on fields in the where clause as far as possible, which will cause the engine to abandon the use of indexes and conduct full table scans.

8. Do not perform functions, arithmetic operations, or other expression operations on the left side of the "=" in the where clause, or the system may not be able to use the index correctly.

9. Not all indexes are valid for the query. Sql optimizes the query based on the data in the table. When the index column has a large number of duplicate data, the sql query will not use the index, such as fields in a table.

Almost half of the sex,male,female, then even if the index is established on the sex, it will have no effect on the query efficiency.

10. The more indexes, the better. The index can improve the efficiency of the corresponding select, but it also reduces the efficiency of insert and update.

Because the index may be rebuilt during insert or update, how to build the index needs to be carefully considered, depending on the circumstances. It is best to have no more than 6 indexes in a table

If there are too many, consider whether it is necessary to build indexes on columns that are not often used.

11. try to use numeric fields, and try not to design character fields that contain only numeric information, which will reduce the performance of queries and connections, and increase storage overhead.

This is because the engine compares each character in the string one by one when processing queries and connections, while for numeric types, it only needs to be compared once.

12. Mysql queries use only one index, so columns in order by will not use indexes if they are already used in the where clause.

Therefore, if the default sorting of the database can meet the requirements, do not use sorting operations, try not to include the sorting of multiple columns, and it is best to build a compound index for these columns if necessary.

13. Order by index, problems that don't work (except primary key index):

1. If select only queries the index field, the order by index field will use the index, or the whole table will be arranged.

2. If there are where conditions, such as where vtype=1 order by vtype asc. This way order by will also use the index!

Two and four indexes

PRIMARY, INDEX and UNIQUE belong to one category.

PRIMARY primary key. Is unique and cannot be empty.

INDEX index, normal

UNIQUE unique index. Repetition is not allowed.

FULLTEXT is a full-text index used to retrieve text information in an article.

Third, commonly used SQL optimization:

1. Optimize group by statement

By default, MySQL sorts all group by col1,col2. This is similar to specifying order by col1, col2 in a query. If group by is included in the query but the user wants to avoid the consumption of sorting results, you can use order by null to disable sorting

two。 In some cases, you can use joins instead of subqueries. Because you don't need to create temporary tables in memory to use join,MySQL.

3. If you want to use an index in a query that contains or, you must use an index for every conditional column between or. If there is no index, you should consider increasing the index.

Select * from table name where condition 1 contains'or condition 2 contains tttt'

The above is the analysis of the Mysql knowledge points and face pilot examples shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.