Performance optimization database
Performance optimization - database Part 1
First, what is performance?
Generally speaking, the consumption of db / SQL operations accounts for the largest proportion in one process. Db / SQL optimization is the core of business system performance
relational database
What is a relational database?
Codd put forward the relational model in 1970, which is based on the theory of relational algebra. The database based on relational database model is called relational database.
Database design paradigm
Explanation of terms:
For example:
Does the following table conform to the second paradigm?
(student number, class name) can be used as code.
There are (student number, class name) - > name, but student number - > name. There are some functional dependencies of the non primary attribute "name" on the code.
There are (student number, class name) - > department name, but student number - > department name. There are some functional dependencies of the non primary attribute "department name" on the code.
Therefore, it does not conform to the second paradigm.
Does the following table conform to the third paradigm?
The main attribute is student number, including student number - > department name, department name - > department head. Therefore, the Dean transfer function depends on the student number, so it does not accord with the third paradigm.
Common database
Structured query language consists of six parts:
Mysql database
Version of MySQL database
MySQL simplified execution process:
MySQL detailed execution process
MySQL execution engine:
2.1 MySQL index principle
Data is segmented by page. When a data is used, the data nearby will usually be used immediately
MySQL index structure:
The index structure of MySQL InnoDB is a B + tree.
B + tree is simply a balanced lookup tree. All record nodes are stored on leaf nodes of the same layer in size order, and connected by the pointers of each leaf node. Pages are connected by a two-way linked list, and the data in the page is a one-way linked list.
B + tree index is divided into clustered index and auxiliary index
Clustered index:
A B + tree is constructed according to the primary key of each table. At the same time, the leaf node stores the row record data of the whole table, which is also called the leaf node of the clustered index, which is called the data page. Since the actual data pages can only be sorted by one B + tree, each table can only have one clustered index.
Many documents say that the clustered index stores data physically in order. In fact, its storage is not physically continuous, but logically continuous.
Nonclustered index
It is also called auxiliary index. The leaf node contains not only the key value, but also the pointer to find the row data corresponding to the index.
If you need to find data in a secondary index with a height of 3, you need to traverse three times to find the specified primary key. If the height of the clustered index tree is also 3, you need to search the clustered index tree three times to finally find the page where the complete row data is located.
Why can't the data of a single table exceed 20 million?
A: first, a page 16K.
It can be calculated that 1170x16 = 18724 data can be placed in the B + tree of layer 2. If the B + tree of layer 3 is 1170x1170x16 = 21902400 pieces of data.
Therefore, if there is more than so much data, the B + tree will become four layers, and the query will be slow.
2.2 parameter configuration optimization
Connection request variable
Buffer variable 4 key_ buffer_ size 5. query_ cache_ Size (QC for short) 6 max_ connect_ errors： 7. sort_ buffer_ size 8. max_ allowed_ packet=32M 9. join_ buffer_ size=2M 10. thread_ cache_ size=300
Configure several InnoDB variables 11 innodb_ buffer_ pool_ size 12. innodb_ flush_ log_ at_ trx_ commit 13. innodb_ thread_ concurrency=0 14. innodb_ log_ buffer_ size 15. innodb_ log_ file_ size=50M 16. innodb_ log_ files_ in_ group=3 17. read_ buffer_ size=1M 18. read_ rnd_ buffer_ size=16M 19. bulk_ insert_ buffer_ size=64M 20. binary log