"Interview" "MySQL FAQ summary" "04"

"Common interview questions Summary catalogue >>>"091 Database Deadlock Concept

In most cases, it can be assumed that if a resource is locked, it will always be released at a later time. Deadlocks occur when multiple processes access the same database, and each of these processes has locks that are required by other processes, resulting in the inability of each process to continue. Simply put, process a waits for process B to release his resources, and B waits for a to release his resources so that they wait for each other to form a deadlock.
Although the process may occur during the operation of the deadlock, but the occurrence of deadlocks must also have certain conditions, the occurrence of deadlocks must have the following four necessary conditions.
　　1) Mutually exclusive condition : Refers to the process of allocating resources to the exclusive use, that is, for a period of time a resource is occupied by only one process. If there are other processes requesting resources at this time, the requestor can wait until the resource-occupying process is freed.
　　2) Request and hold condition : means that the process has maintained at least one resource, but a new resource request has been made, and the resource has been occupied by another process, at which time the request process is blocked, but the other resources that you have obtained remain.
　　3) Non-deprivation conditions : Refers to the process has been obtained resources, before the end of use, can not be deprived, can only be released by themselves when the use is complete.
　　4) Loop wait condition : Refers to the deadlock, there must be a process-the resource of the ring chain, that is, the process set {P0,P1,P2,???, Pn} P0 is waiting for a P1 occupied resources; P1 is waiting for P2 resources, ... PN is waiting for resources that have been consumed by P0.
The following methods help to minimize deadlocks:
　　(1) Access the object in the same order.
　　(2) Avoid user interaction in the transaction.
　　(3) Keep the transaction short and in a batch process.
　　(4) Use a low isolation level.
　　(5) Use a bound connection.

092 database has several data protection methods (AAA)

The common methods and techniques to implement database security control are: user identification and identification, access control, view mechanism; audit; data encryption;

The difference between 093 Union and union all and the use

Union is inefficient because it is being scanned for duplicate values. If the merge does not intentionally delete duplicate rows, then the union all two must have the same number of SQL statement fields to union, and the field type is "compatible" (consistent);
　　The difference between Union and Union All is that union automatically compresses duplicate results in multiple result sets, and union ALL displays all results, whether duplicates or not.
　　Union: Set operation on two result sets, excluding duplicate rows, and sorting the default rules;
　　UnionAll: set operation on two result sets, including repeating rows, without sorting;
　　Intersect: The intersection of two result sets, excluding duplicate rows, and sorting the default rules;
　　minus: Perform a differential operation on two result sets, excluding duplicate rows, and sorting the default rules.
The ORDER BY clause can be specified in the last result set to change the ordering method.

What is the backup command for 094 MySQL?

Mysqldump-hhostname-uusername-ppassword databasename > Backupfile.sql
Backing up the MySQL database to a format with a delete table
Backing up the MySQL database is a format with a delete table that allows the backup to overwrite the existing database without having to manually delete the existing database.
Mysqldump-–add-drop-table-uusername-ppassword databasename > Backupfile.sql
Compress the MySQL database directly backup
Mysqldump-hhostname-uusername-ppassword DatabaseName | gzip > backupfile.sql.gz
Back up a MySQL database (some) table
Mysqldump-hhostname-uusername-ppassword databasename specific_table1 specific_table2 > Backupfile.sql
Back up multiple MySQL databases at the same time
Mysqldump-hhostname-uusername-ppassword–databases databasename1 databasename2 databasename3 > Multibackupfile.sql
Backing up the database structure only
Mysqldump–no-data–databases databasename1 databasename2 databasename3 > Structurebackupfile.sql
Back up all databases on the server
Mysqldump–all-databases > Allbackupfile.sql
Commands to restore MySQL database
Mysql-hhostname-uusername-ppassword DatabaseName < Backupfile.sql
Restore a compacted MySQL database
Gunzip < backupfile.sql.gz | Mysql-uusername-ppassword DatabaseName
To transfer a database to a new server
Mysqldump-uusername-ppassword DatabaseName | mysql–host=...-C DatabaseName

095 when the MySQL server is slow to enter what command can alleviate the server pressure

　　The first step is to check the status of the system
Some of the operating system's tools to check the status of the system, such as CPU, memory, switching, disk utilization, based on experience or compared to the normal state of the system, sometimes the system appears to be idle on the surface, which may not be a normal state, because the CPU may be waiting for the completion of IO. In addition, you should also view the processes that occupy the system resources (CPU, memory).
1.1 Using SAR to check the operating system for IO problems
1.2 Using VMSTAT to monitor memory CPU resources
1.3 Disk IO problem, processing mode: do RAID10 improve performance
1.4 Network problems, Telnet to the open port of MySQL, if not, see if the firewall is set correctly. Also, see if MySQL turns on the skip-networking option, turn it off if you turn it on.
　　second step to check MySQL parameters
2.1 Max_connect_errors
2.2 Connect_timeout
2.3 Skip-name-resolve
2.4 Slave-net-timeout=seconds
2.5 master-connect-retry
　　step three check MySQL related status values
3.1 Attention to the number of connections
3.2 Attention to the system lock situation
3.3 Follow slow query (slow) log

096 How do I export a table structure?

1. Export the entire database
Mysqldump-u User name-p password database name > exported file name
c:\users\jack> Mysqldump-uroot-pmysql sva_rec > E:\sva_rec.sql
2. Export a table, including table structure and data
Mysqldump-u User name-p password database name table name > exported file name
c:\users\jack> mysqldump-uroot-pmysql Sva_rec date_rec_drv> e:\date_rec_drv.sql
3. Export a database structure
c:\users\jack> mysqldump-uroot-pmysql-d sva_rec > E:\sva_rec.sql
4. Export a table with only a table structure
Mysqldump-u User name-p password-d database name Table name > exported file name
c:\users\jack> mysqldump-uroot-pmysql-d Sva_rec date_rec_drv> e:\date_rec_drv.sql
5. Import the database
Common source Commands
Go to MySQL Database console,
such as Mysql-u root-p
Mysql>use Database
Then use the source command, followed by the script file (for example, the. SQL used here)
Mysql>source D:wcnc_db.sql

097 what commands to use to see if the process is working properly after logging in to MySQL

Enter show Processlist;
If you have super privileges, you can see all the threads, otherwise you will only see the thread that you initiated (this is the thread that the current MySQL account is running on).

098 MySQL Remote connection command

First, MySQL connection to the local database, the user named "root", the password "123" (Note: "-P" and "123" can not have space between)
C:>mysql-h Localhost-u root-p123
Second, MySQL connection remote database (192.168.0.201), port "3306", User name "root", password "123"
C:>mysql-h 192.168.0.201-p 3306-u root-p123

099 MySQL master and slave how to transfer logs

MySQL replication tracks all changes to the database (updates, deletions, and so on) based on the primary server in the binary log. Each slave server receives a saved update from the primary server that the primary server has logged to the binary log so that the server can perform the same updates to its copy of the data.

100 how databases are backed up

　　1, full backup , this is the most common way for most people, it can back up the entire database, including user tables, system tables, indexes, views and stored procedures and other database objects. But it takes more time and space, so it's generally recommended to do a full backup once a week.
　　2, transaction log backup , the transaction log is a separate file, it records the database changes, backup only need to replicate since the last backup changes to the database, so only a small amount of time. To make the database robust, it is recommended to back up the transaction log hourly or even more frequently.
　　3. Differential backups are also called incremental backups . It is another way to back up only part of the database, it does not use the transaction log, instead it uses a new image of the entire database. It is smaller than the initial full backup because it contains only the databases that have changed since the last full backup. It has the advantage of faster storage and recovery. It is recommended to make a differential backup every day.
　　4, file backup , database can be composed of many files on the hard disk. If the database is very large and cannot be backed up in one night, you can use a file backup to back up a portion of the database nightly. This kind of backup is not very common because the database is generally not too large to have to use multiple file stores.

101 See if MySQL database supports InnoDB

View MySQL's storage engine:show plugins;
How to randomly extract 10 records from a table in MySQL
1. Use the MySQL built-in functions to operate, the specific SQL code is as follows:
SELECT * FROM TableName ORDER by RAND () LIMIT 10
2. Do not do a lot of work to the database, which will cause the database to lock up and block in a certain time period. It is recommended to randomly generate a number of 1-x (total number of rows) from PHP, and then use these 10 random numbers as query criteria, specific statements such as:
SELECT * FROM TableName where ID in (2,8,4,11,12,9,3,1,33)
You may also want to repeat the exclusion, and you need to concatenate 10 values in the program and connect to the SQL statement.

102 How to view the current user connected to MySQL.

Show full processlist, see which users are in the user field

103 write MySQL How to change the password?

Method One: (for Administrators or users with global permissions to reset other user's password)
Enter command line mode
Mysql-u root-p
Mysql>use MySQL;
mysql> UPDATE user SET Password=password ("New password") WHERE user= ' username ';
mysql> FLUSH privileges;
Mysql> quit;
Method Two:
Mysql-u root-p
Mysql>use MySQL;
Mysql> SET PASSWORD for Username=password (' New PASSWORD ');
Mysql> QUIT
Method Three:
mysqladmin-u root "Old password" "New password"
Note: New password Please enter the password you want to set.

104 How does MySQL repair a corrupted table?

There are two ways to use MySQL's check table and Repair table SQL statement, and the other is to use the multiple myisamchk provided by MySQL to isamchk the data detection recovery tool.

105 brief description of MySQL optimization (emphasis)

　　 1. Database design: As far as possible the database design of the smaller account for disk space.
1) Use a smaller integer type whenever possible. (Mediumint is more appropriate than int).
2) As far as possible, define the field as NOT NULL, unless it requires null.
3) If the variable length field is not used, such as varchar, then a fixed-size record format such as Char is used.
4) The primary index of the table should be as short as possible. In this way, each record is marked with a name and is more efficient.
5) Create only the indexes that are really needed. Indexes are useful for retrieving records, but are not conducive to saving records quickly. If you are always searching on a combined field of a table, create an index on those fields. The first part of the index must be the most commonly used field. If you always need to use a lot of fields, you should first copy these fields more, so that the index is better compressed.
6) All data must be processed before being saved to the database.
7) All fields must have a default value.
8) In some cases, dividing a frequently scanned table into two speeds can be much faster. This is especially true when a dynamic format table is scanned for related records, and it may use a smaller static format table.
　　 2. Use of the system
1) Use long connections as much as possible.
2) explain complex SQL statements.
3) If the two correlation tables are to be compared, the fields to be compared must be of the same type and length.
4) Limit statement as much as possible with order by or distinct. This avoids having to do a full table scan.
5) If you want to clear all records of the table, it is recommended to use TRUNCATE TABLE tablename instead of DELETE from TableName.
6) When you can use the store PROCEDURE or user function.
7) Use multiple record insertion format in an INSERT statement. and using the load data infile to import a lot of it, that's a lot faster than a simple insert.
8) often optimize TABLE to defragment.
9) There is a date type of data if you want to do more frequent comparisons, try to save in the unsigned int type is faster.
　　 3. Bottlenecks in the system
1) disk search. Parallel search allows data to be stored separately on multiple disks, which speeds up search time.
2) disk read/write (IO). Data can be read in parallel from multiple media.
3) CPU cycles. The data is stored in main memory. This will increase the number of CPUs to process the data.
4) Memory bandwidth. When the CPU is going to store more data in the CPU cache, the bandwidth of the memory becomes a bottleneck.

106 how can I determine which storage engines are available?

Mysql> show engines; Shows the full list of available database engines and whether the engines are supported in the current database server.

107 MySQL Database design data type selection need to be aware of where? (emphasis)

varchar and char types, varchar is variable length, requires additional 1-2 bytes of storage, can save space, may be useful for performance. However, due to the change of length, fragmentation may occur, such as updating data;
Using an enum instead of a string type, the data is actually stored as an integral type.
String type
Avoid using strings to make identifiers as much as possible, because they occupy a lot of space and are usually slower than integer types. Pay particular attention to not using the string identifier on the MyISAM table. MyISAM uses a compressed index (Packed index) for strings by default, which makes lookups slower. It is tested that the performance of the MyISAM table using a compressed index is 6 times times slower.
Also pay special attention to the completely ' random ' strings, such as those produced by MD5 (), SHA1 (), and UUID (). Each new value they produce is stored arbitrarily in a large space, slowing down inserts and some select queries.
1) They slow down the insert query because the inserted value is randomly placed in the index. This results in paging, random disk access, and clustered index fragmentation on the clustered storage engine.
2) They slow down the select query because logically adjacent rows are distributed across the disk and in memory.
3) random values cause the cache to have poor performance for all types of queries because they invalidate the access locality that the cache relies on for work. If the entire data set becomes equally "hot," there is no advantage in caching specific portions of the data into memory. And if the working set cannot be loaded into memory, the cache will do a lot of brush-writing work, and it will cause many cache misses.
If you save the UUID value, you should remove the dash, and a better way is to use Uhex () to convert the UUID value to a 16-byte number and save it in the binary (16) column.

108 MySQL, Oracle default port number

3306, 1521

109 InnoDB the way the transaction and the log are implemented.

　　(1) How many kinds of logs are there?
　　　　error log : Log error messages, also record some warning messages or correct information
　　　　Slow Query Log : Sets a threshold that records all SQL statements that run longer than this value to the log file for slow queries.
　　　　Binary log : Records all actions that make changes to the database
　　　　query log : Records all information about database requests, whether or not they are executed correctly.
　　(2) The form of log storage
　　(3) How the transaction is achieved through the log , the more deeply the better.
In the Innodb storage engine, the transaction log is implemented through the storage engine log buffers (Innodb log buffer) of redo and Innodb, and when a transaction is started, the LSN (log sequence number) of the transaction is logged; When the transaction executes, the transaction log is inserted into the log cache of the log of the InnoDB storage engine, and when the transaction commits, the log buffers of the storage engine must be written to disk (controlled by Innodb_flush_log_at_trx_commit), that is, before the data is written, You need to write the log first. This approach is called "pre-write Log mode", InnoDB this way to ensure the integrity of the transaction. This means that the pages stored on the disk are not synchronized with the page above the memory buffer pool, which is an asynchronous way of writing the redo log first and then writing to the data file.
　　isolation : implemented by lock
　　atomicity , consistency , and persistence are accomplished through redo and undo.

"Interview" "MySQL FAQ summary" "04"