编程知识 cdmana.com

Interview do not know how to answer these six knowledge points, you dare say familiar with MySQL?

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

sql The order in which statements are written and executed

20 You have to memorize the micro service interview questions , The interview is bound to be asked

 Insert picture description here

One 、 Business


1. What is business

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 .

2. Four characteristics of transactions

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 .

3. Concurrency of transactions

① When does the concurrency problem occur ?

When multiple transactions operate on the same data of the same database at the same time , There will be concurrency problems .

② What are the concurrency problems of transactions ?

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 .

③ How to avoid the concurrency of transactions ?

By setting the isolation level of the transaction, the concurrency of the transaction can be avoided . Transactions have the following four isolation levels :

Read uncommitted (read-uncommitted)
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
Repeatable (repeatable-read) no no yes
Serialization (serializable) no no no

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 .

Two 、 Indexes


1. What is index

Indexes It 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

2. Advantages and disadvantages of index

advantage

① 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 .

shortcoming

① 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

2. The design principle of index

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 .

3、 ... and 、 Three paradigms of database design


1. First normal form

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
Table 1

2. Second normal form

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
Table two

Course no. Course name credits
101 Java 4
102 data structure 6
Table 3

3. Third normal form

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
Table 4


The curriculum :

Course no. Course name credits
101 Java 4
102 data structure 6
Table 5


League tables :

Student number Course no. achievement
001 101 80
002 102 90
Table 6

Four 、MySQL Master-slave replication of


1. Concept

MySQL Master slave copy Data can be taken from a MySQL The database server master node is replicated to one or more slave nodes .

2. principle

 Insert picture description here

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 .

3. Master slave replication uses

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 .

5、 ... and 、MySQL In the lock


1. Why lock up

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 .

2. Classification of locks

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 .

6、 ... and 、 How to do MySQL Performance optimization


① 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]所创,转载请带上原文链接,感谢
https://cdmana.com/2021/06/20210629125103380t.html

Scroll to Top