Write it at the front ： I am a 「 Sail to the sea 」, The nickname comes from my name and my girlfriend's name . I love technology 、 Love open source 、 Love programming .
Technology is open source 、 Knowledge is shared.
This blog is a little summary and record of my study , If you are right about Java、 Algorithm Interested in , Can pay attention to my dynamic , Let's study together .
Use knowledge to change fate , Let our family have a better life.
Related articles ：
List of articles
- One 、 Business
- Two 、 Indexes
- 3、 ... and 、 Three paradigms of database design
- Four 、MySQL Master-slave replication of
- 5、 ... and 、MySQL In the lock
- 6、 ... and 、 How to do MySQL Performance optimization
MySQL A business is a group of sql Statement or a unit of work that runs independently . The unit of work is either fully executed , Or none at all .
Atomicity： A transaction is indivisible , All operations in a transaction , Or it's all done , Or not at all .
Uniformity： A transaction will cause data to switch from one consistent state to another . in other words , Before and after transaction start , The integrity of the database is not compromised .
Isolation,： Changes made by a firm before final submission , It's invisible to other things . In other words, the execution of one transaction is not interfered by other transactions .
persistence： Once a transaction is committed , Changes to the data will be permanently saved to the database , Even if the system fails, it will not be lost .
When multiple transactions operate on the same data of the same database at the same time , There will be concurrency problems .
Dirty reading： For two things T1、T2, T1 Read has been T2 Updated but not yet submitted data . If the transaction T2 Rolled back , T1 The data read is temporary and invalid .
It can't be read repeatedly： For two things T1、T2, T1 Read a data , then T2 Updated the data . If T1 Read the same data again , The values are different .
Fantasy reading： For two things T1、T2, T1 Read a data from a table , then T2 Some new rows are inserted into the table . If T1 Read the same table again , There will be more lines .
By setting the isolation level of the transaction, the concurrency of the transaction can be avoided . Transactions have the following four isolation levels ：
Read submitted（read-committed） Avoid dirty reading
Repeatable（repeatable-read） Avoid dirty reading 、 Non repeatable reading and part of unreal reading
Serialization（serializable） Avoid dirty reading 、 No repeated reading or phantom reading
|Transaction isolation level||Dirty reading||It can't be read repeatedly||Fantasy reading|
|Read uncommitted （read-uncommitted）||yes||yes||yes|
|Read submitted （read-committed）||no||yes||yes|
MySQL database (InnoDB engine ) The default isolation level used is ： Repeatable （ Repeatable read );
Higher isolation level , The more data integrity and consistency can be guaranteed , But the greater the impact on concurrent performance .
IndexesIt is a structure that sorts the values of one or more columns in a database table , Using index can improve the query speed of specific data in the database
① Using indexes can greatly speed up the query of data
② By creating a unique index , Ensure the uniqueness of each row of data in the database table .
③ When using grouping and sorting clauses for data queries , Using indexes can reduce the time it takes to group and sort queries .
① Indexing takes time to create and maintain , As the amount of data increases, so does the time required .
② When adding data in the table 、 When deleting and modifying , Index should also be maintained dynamically , This reduces the speed of data maintenance .
③ Index needs disk space , In addition to the fact that data tables occupy data space , Each index takes up a certain amount of physical space , If there are a lot of indexes , Index files can reach the maximum file size faster than data files
Unreasonable index design or lack of index will cause obstacles to database and application performance . Efficient indexing is very important for good performance . When designing an index , The following criteria should be considered ：
① The more indexes, the better , If there are a large number of indexes in a table , Not just disk space , And it will affect INSERT、 DELETE、UPDATES And so on , Because when the data in the table changes, the index will also be adjusted and updated .
② Avoid excessive indexing of frequently updated tables , And there are as few columns in the index as possible . The fields that are often used for queries should be indexed , But avoid adding unnecessary fields .
③ It is better not to use index for tables with small data volume , Because there is less data , Queries can take less time than index traversal , Indexes may not be optimized .
④ Index a column with many different values that are often used in conditional expressions , Don't index columns with very few different values . For example, in the student table “ Gender ” Only... In the field “ male ” And “ Woman ” Two different values , So there's no need to index . If the index is built, it will not improve the query efficiency , On the contrary, it will seriously slow down the data update speed .
⑤ When uniqueness is a characteristic of some kind of data itself , Specify a unique index . The use of unique index pairs ensures the data integrity of the defined columns , To improve query speed .
⑥ To sort or group frequently ( That is to say group by or order by operation ) Index on the column of , If there are more than one column to sort , You can build composite indexes on these columns .
Make sure that each column remains atomic
The first paradigm is the most basic paradigm . Each column of the database table is required to be an indivisible atomic data item .
|Student number||full name||Course no.||Course name||credits||achievement|
|001||Sail to the sea||101||Java||4||80|
|002||Setting sail for sunrise||102||data structure||6||90|
Make sure that each column in the table is related to the primary key
The second paradigm is based on the first one , The second paradigm needs to ensure that every column in the database table is related to the primary key , You can't just relate to a part of the primary key （ Mainly for the union primary key ）. That is to say, in a database table , Only one kind of data can be saved in a table , It is not allowed to save multiple data in the same database table .
|Student number||full name||Course no.||achievement|
|001||Sail to the sea||101||80|
|002||Setting sail for sunrise||102||90|
|Course no.||Course name||credits|
Make sure that each column is directly related to the primary key column , Not indirectly
The third paradigm is based on the second paradigm , You need to make sure that every column in the data table is directly related to the primary key , Not indirectly .
Student list ：
|Student number||full name|
|001||Sail to the sea|
|002||Setting sail for sunrise|
The curriculum ：
|Course no.||Course name||credits|
League tables ：
|Student number||Course no.||achievement|
MySQL Master slave copyData can be taken from a MySQL The database server master node is replicated to one or more slave nodes .
Generate two threads from the library , One I/O Threads , One SQL Threads .
I/O Threads Go to the master database Of binlog, And will get binlog The log says relay log（ relay logs ） In file ;
The main library will generate a log dump Threads , Used to give to the slave I/O Thread transfer binlog.
SQL Threads Will read relay log Log in file , And parse it into concrete operation , To achieve master-slave operation consistency , And the final data is consistent .
Read / write separation, In the development work , Sometimes I meet someone sql
Statement needs lock table , Resulting in temporary inability to use the read service , This will affect the existing business , Use master-slave replication , Let the main database be responsible for writing , Read from the library , In this way, even if the main database appears to lock the table , The normal operation of the business can also be ensured by reading from the database .
Real time data backup, When a node in the system fails , It's easy to fail over .
Database lock is to support concurrent access to shared resources , Provide data integrity and consistency . Locking in the database is to ensure that in the case of high concurrency , When accessing the database , There's no problem with the data .
According to the operation points :
① Read the lock ( Shared lock )： For the same data , Multiple read operations can be performed simultaneously without affecting each other .
② Write lock ( Exclusive lock )： Before the current write operation is completed , Will block other write and read locks .
By granularity :
① Table locks ： Low overhead , Locked fast ; A deadlock will not occur ; Large locking size , The highest probability of lock collisions , Lowest degree of concurrency .
② Row lock ： Spending big , Lock the slow ; A deadlock occurs ; Locking granularity minimum , The lowest probability of lock collisions , The highest degree of concurrency .
③ Page locks ： Cost and lock time are between table lock and row lock ; A deadlock occurs ; Lock granularity is between table lock and row lock , The concurrency is average .
① When only one piece of data is needed , Use limit 1
② Avoid using select * , List the fields to be queried when querying .
③ Use join Instead of subquery .
④ Reduce use or, Use in perhaps union(union all) Instead of .
⑤ Index search fields . But don't over index , More indexes , The more space it takes , Instead, performance slows down .
⑥ Avoid type conversion , Otherwise, the index will be invalid .
⑦ Use explain . Use explain , It can help us understand MySQL How to deal with sql Of the statement , You can see that sql Implementation plan of , So that we can better understand sql Sentence deficiency , And then optimize the statement .
⑧ Vertical segmentation , Put common and related fields in the same table , Divide the data of a table into several tables This can reduce the complexity of the table and the number of fields , So as to achieve the purpose of optimization
Due to limited level , This blog is inevitably deficient , I would like to ask you guys to give me some advice ！
本文为[Sail to the sea]所创，转载请带上原文链接，感谢