MySQL Often asked in interviews , This article summarizes the classic questions in the interview .

****1.**** What are the three paradigms of database ?

First paradigm : Each column cannot be split again .

Second paradigm : On the basis of the first paradigm , Non primary key columns are completely dependent on primary keys , It cannot be part of the primary key .

The third paradigm : On the basis of the second paradigm , Non primary key columns only depend on primary keys , Independent of other non primary keys .

When designing the database structure , Try to follow the three paradigms , If not , There must be enough reasons .

Such as performance . In fact, we often compromise database design for performance .

****2.****mysql What are the tables about permissions

MySQL The server controls users' access to the database through the permission table , Permission table is stored in mysql In the database , from mysql_install_db Script initialization .

These permission tables are user,db,table_priv,columns_priv and host.

user Permission table : Record the user account information allowed to connect to the server , The permissions inside are global level .

db Permission table : Record the operation permissions of each account on each database .

table_priv Permission table : Record operation permissions at the data table level .

columns_priv Permission table : Record operation permissions at the data column level .

host Permission table : coordination db The permission table provides more detailed control over the database level operation permissions on a given host . This permission table is not supported by GRANT and REVOKE Effect of statement .

3.SQL What are the main types of statements

· Data definition language DDL(Data Ddefinition Language)CREATE,DROP,ALTER

Mainly for the above operations , That is, those who have operations on logical structures, etc , Including table structure , Views and indexes .

· Data query language DQL(Data Query Language)SELECT

This is easy to understand , Query operation , with select keyword .

Various simple queries , Connection query belongs to DQL.

· Data manipulation language DML(Data Manipulation Language)INSERT,UPDATE,DELETE

Mainly for the above operations , That is, to operate the data , Corresponding to the above query operation DQL And DML Jointly build the common operations of adding, deleting, modifying and checking for most junior programmers . Query is a special kind , Be divided into DQL in .

· Data control function DCL(Data Control Language)GRANT,REVOKE,COMMIT,ROLLBACK

Mainly for the above operations , That is, those who have operations on database security and integrity , It can be simply understood as permission control, etc .

4****. What is deadlock ? How to solve it ?****

Deadlock refers to two or more transactions occupying each other on the same resource , And request to lock the other party's resources , Which leads to a vicious circle .

Common methods to solve deadlock :

If different programs access multiple tables concurrently , Try to agree to access the tables in the same order , It can greatly reduce the chance of deadlock ;

In the same transaction , Try to lock all resources required at one time , Reduce the probability of deadlock generation ;

For business parts that are very prone to deadlock , You can try to use upgrade to lock the granularity , Reduce the probability of deadlock through table level locking ;

If the business is not handled well, you can use distributed transaction locks or optimistic locks .

****5.**** What is dirty reading ? Unreal reading ? Non repeatable ?

Dirty reading (Drity
Read): A transaction has updated a copy of data , Another transaction read the same data at this time , For some reason , previous RollBack Operation , Then the data read by the latter transaction will be incorrect .

Non repeatable (Non-repeatable read): Data inconsistency in two queries of a transaction , This may be because a transaction is inserted between the two query processes to update the original data .

Unreal reading (Phantom
Read): In two queries of a transaction , Inconsistent number of data transactions , For example, a transaction queries several columns (Row) data , Another transaction inserts several new columns of data at this time , The previous transaction is in the next query , You'll find several columns of data that it didn't have before .

6****.SQL Life cycle of ?****

① Establish a connection between the application server and the database server

② Database process get request sql

③ Parse and generate execution plan , implement

④ Read data into memory and perform logical processing

⑤ Connect through step 1 , Send results to client

⑥ Turn off the connection , Release resources

****7.****MySQL database cpu Soar to 100% How to deal with it ?

When cpu Soar to 100% Hour , First use the operating system command top Command observation, isn't it mysqld Caused by occupation .

If not , Identify processes with high occupancy , And carry out relevant treatment .

If it is mysqld Caused by ,show
processlist, Look at what's running inside session situation , Does it consume resources sql Running . Find the ones with high consumption sql, Check whether the implementation plan is accurate ,index Is it missing , Or it is caused by too much data .

generally speaking , Definitely kill Drop these threads ( Simultaneous observation cpu Whether the utilization rate decreases ), And so on ( For example, add indexes , change sql, Change memory parameters ) after , Run these again SQL.

It could be every sql Not much resource consumption , But suddenly , There are a lot of session Connected to cause cpu soar , In this case, we need to work with the application to analyze why the number of connections increases , And make corresponding adjustments , For example, limit the number of connections, etc .

****8.****MySQL What problems have master-slave replication solved ?

The role of master-slave replication is :

There is a problem with the primary database , Can switch to the slave database . It can perform read-write separation at the database level . Daily backup can be performed on the slave database .

data distribution : Start or stop replication at will , And distribute data backup in different geographical locations

load balancing : Reduce pressure on a single server

High availability and failover : Help applications avoid a single point of failure

Upgrade test : You can use a higher version of MySQL As slave Library

****9.****MySQL What are the common backup tools ?

Common backup tools mysql copy :

Logical backup (mysqldump,mydumper)

Physical backup (copy,xtrabackup)

Comparison of backup tool differences :

·mysql Replication compared with other backups , The backup data obtained is relatively real-time .

· Logical backup : It's easy to divide the tables .mysqldump When backing up data, all sql Statements are integrated in the same file ;mydumper When backing up data SQL Statement is split into a single according to the table sql file ,
each sql The file corresponds to a complete table .

· Physical backup : Copy is available , Fast .

copy: Directly copy files to the data directory , It may cause table damage or inconsistent data .

xtrabackup about innodb Tables do not need to be locked , about myisam Table still needs to be locked .

10.MySQL How to make a backup plan ?

Depending on the size of the library . generally speaking 100G Library in , May consider using mysqldump Do it , because mysqldump More lightweight and flexible , The backup time is selected in the low peak period of business , It can be backed up in full every day (mysqldump The files backed up are relatively small , Smaller after compression ).

100G Above Libraries , You can consider using xtranbackup Do it , Backup speed is significantly faster than mysqldump Be quick .

Generally, one is available every week , The rest will be backed up incrementally every day , The backup time is the low peak period of business .

Introduce yourself first , Xiaobian 13 Graduated from Jiaotong University in , Once worked in a small company , Have been to Huawei OPPO Such a big factory ,18 Entered Alibaba in , up to now . Know most junior and intermediate level java engineer , Want to upgrade skills , They often need to grope for growth or sign up for classes , But for training institutions, the tuition fee is nearly 10000 yuan , The pressure is really not small . Self study, which is not systematic, is inefficient and long , And it's easy to touch the ceiling, and the technology doesn't stop . So I collected one 《java Develop a complete set of learning materials 》 To everyone , The original intention is also very simple , I just hope to help friends who want to learn by themselves and don't know where to start , At the same time, reduce everyone's burden . Add business card below , You can get a full set of learning materials

©2019-2020 Toolsou All rights reserved,
Solve in servlet The Chinese output in is a question mark C String function and character function in language MySQL management 35 A small coup optimization Java performance —— Concise article Seven sorting algorithms (java code ) use Ansible Batch deployment SSH Password free login to remote host according to excel generate create Build table SQL sentence Spring Source code series ( sixteen )Spring merge BeanDefinition Principle of Virtual machine installation Linux course What are the common exception classes ?