1.SQL Constraints

SQL There are five basic constraints

constraint
NOT NULL	The default data is not NULL
UNIQUE	The data in the column is unique
PRIMARY KEY	The primary key is unique
FOREIGN KEY	Point to another table PRIMARY KEY
CHECK	Data range

Example:

Create a file called EXAM Table of , among AUTO_INCREMENT It has to be for PRIMARY KEY.

CREATE TABLE EXAM(
	ID INT NOT NULL AUTO_INCREMENT,
	NAME VARCHAR(20) NOT NULL,
	COMPUTER INT NOT NULL,
	ENGLISH INT NOT NULL,
	PRIMARY KEY (ID),
	CHECK (ENGLISH > 0)
);

2. Column empty

UPDATE tablename SET column_name='' where ...  ;

3.AUTO_INCREMENT Field

AUTO_INCREMENT Field （ When adding or deleting rows to a table , auto number ）

 CREATE TABLE tablename( segment type NOT NULL AUTO_INCREMENT, PRIMARY KEY (segment) );

When the middle line is deleted , The automatic number will still not change , But if you use UPDATE Insert the new row into the segment When set to the number of the deleted line ,
The system will insert the newly inserted row into the position of the previously deleted row .

4. Get table structure

DESCRIBLE And EXPLAIN The method of use is similar

SHOW CREATE TABLE table_name;

DESCRIBLE table_name;

EXPLAIN SELECT column_name1 ... FROM table_name WHERE condition;

Copy data from one table to another table （ The structure of the two tables is the same ）

	INSERT INTO BACK_UP SELECT * FROM TEMPLATE;

5. Get server metadata

	(1)  SELECT VERSION();   obtain MySQL  Version information 
	(2)  SELECT DATABASE();   Get all databases under this user 
	(3)  SELECT USER();   View the current user name 
	(4)  SHOW STATUS;   see VARIABLES 
	(5)  SHOW VARIABLES();   Get system global variables 

6. System database mysql

Every database system has a database ——mysql, There are several tables as follows ：

among user The user... Is stored in the table , Password and related permissions .

7.mysql Authority

The specific structure is in user In the table

Administrative authority	FILE,GRANT,PROCESS,RELOAD,SHUTDOWN
Authorization Form	user,db,host,tables_priv,columns_priv etc.
Database and table permissions	user,db,host,tables_priv,columns_priv etc. ALTER,CREATE,DELETE,DROP,INDEX,DEFERENCE,SELECT,UPDATE

8.MySQL Sign in （ Two stages ）

1. Server slave user Find matches in the table , If matching, proceed to the second stage ;
2. The server matches the user's permissions ;

Login query verification

When a query request is made , The server checks whether it has sufficient permissions to perform relevant operations , With user,db,tables_priv,columns_priv Check the sequence of

host The watch is not affected by GRANT,REVOKE influence , Therefore, it is generally possible to omit host Check , but host The inspection of is in user After the examination ;

9. rename

ALTER TABLE OLD_NAME RENAME TO NEW_NAME;  //  Table rename 

ALTER DATABASE OLD_NAME RENAME TO NEW_NAME;  //  Database rename 

10. Database backup

1. Backup in the database , adopt BACKUP command
2. Backup through terminal command

mysqldump -u root -p db_name > db_file_name.sql ;

3. Database recovery

mysqladmin -u root -p create db_name ;		// Create a new database 
mysql -u root -p db_name < db_file_name.sql	// Import the backup into 

11. Add a new local user

There are two ways to add new mysql user ( root jurisdiction )

GRANT	GRANT ALL ON *.* TO user_name IDENTIFIED BY “pass_word” ;
REVOKE	REVOKE ALL ON *.* TO user_name;
INSERT	INSERT INTO user VALUES (“host_”,“user_name”,PASSWORD(“password”), …)
DELETE	DELETE FROM user WHERE USER = “user_name”

Be careful ： INSERT Insert a password that will not be encrypted , and GRANT It will be encrypted automatically , Therefore use INSERT When creating users, you need to use... Manually PASSWORD() encryption .

12.myisamchk Check and repair the database

Use myissamchk when , Must be unique access , Otherwise, it is easy to cause the server to crash .

Secondly, when trying to repair the damaged file, back up the file in advance , Make sure that before backup MySQL The server is down , Otherwise, the data may be inconsistent ;

myisamchk --recover --quick path_to_tbname	Quick fix
myisamchk --recover path_to_tbname	Ordinary repair
myisamchk --safe-recover path_to_tbname	Specific repair

The execution speed decreases in turn , The execution efficiency is improved in turn ;

13.MySQL file type

*.cnf	The configuration file
*.MYI	Tabular file
*.frm	Table structure file
*.MYD	Table data file

14.MySQL performance optimization （ Default parameter optimization ）

Main common performance parameters

back_log	back_log The value is in MySQL How many requests can be stored in the stack in a short period of time before you temporarily stop answering new requests .
interactive_timeout	The number of seconds the server waits for action on an interactive connection before shutting it down
key_buffer_size	key_buffer_size Is the buffer size for the index block , Add it to get better indexes
max_connections	The number of customers allowed at the same time
record_buffer	Each thread that performs a sequential scan allocates a buffer of this size for each table it scans , Modify the buffer size
sort_buffer	Each thread that needs to be sorted allocates a buffer of that size . Increase this value to accelerate ORDER BY or GROUP BY operation .
table_cache	Number of open tables for all threads . Increasing this value increases mysqld Number of file descriptors required
thread_cache_size	The number of threads that can be reused in , If there are many new threads , In order to improve performance, you can use this variable value .
wait_timeout	The number of seconds the server waits for action on a connection before shutting it down .

Performance optimization can exceed the specified maximum value of the operating system , Otherwise... Will fail , Parameters can be passed through MySQL The configuration file to modify ;

15. Chinese sorting error solution

MySQL The default character set is generally ISO-8859 Define Chinese characters as binary form ,	for example ：name varchar(20)binary
compile MySQL When using --with-charset =gbk	–with-charset =gbk

16. Indexes

Improve the efficiency of table lookup , It can be optimized by setting relevant indexes

Disadvantages and implementation of index

MySQL The implementation of index in is B- Trees

Index takes up memory space , A large number of indexes consume a lot of space
The index will drop DELETE ,UPDATE,INSERT Speed of operation , When performing operations, you don't just write data to the data file , Also write index files

17.mysqld Daemon

/var/run/mysqld 	 see mysql  Daemons of  , Need to be root jurisdiction 

18.MySQL Optimize

General optimization

Adjust server settings （ Modify the configuration file my.cnf）
Compile time optimization MySQL 1 Use a specific compiler 2 take MySQL Compile into a static file and execute 3 Configuration samples
Change the hardware configuration , Install more memory , Use fast I/O , etc.
SELECT Query cache is used when operations are frequent
Use MyISAM Key cache

Administrator level optimization

Save data in memory as much as possible
Keep the information in the index
Increase the server cache size

Query optimization

Use the same type of query , The query efficiency of the same data type is higher than that of different data types
Minimum match principle （ stay WHERE … AND … Priority will be given to the one with the least matches in the table , Then take the matches that meet another limit ）	SELECT * FROM table_name WHERE condition_1 AND condition_2 The system will condition Select the best search scheme by minimizing the number of matches in the table
Regular expressions are not optimized
MySQL Automatic type conversion	SELECT * FROM table_name WHERE column_int = ‘1’ , At this point ’1’ Convert to INT Type if column_int It's indexed , In exchange for the reduction of query efficiency , And indexes cannot be used
Some subqueries can use joins for the same purpose , And more efficient	Rewrite the subquery to join Pattern , Improve query efficiency

Optimize MySQL Default scheduling policy （ Realization , Locking mechanism ）

Write operations take precedence over read operations
Only one write operation can be responded to at a time , The write execution order is determined by the request order
Support the execution of multiple read operations
The scheduling policy can be set according to the statement adjuster LOW_PRIORITY Wait for the system scheduling regulator

Change the priority of statement scheduling

LOW_PRIORITY and HIGH_PRIORITY Moderators affect storage engines that use data table locks （ for example MyISAM and MEMORY）.DELAYED The modifier is used for MyISAM and MEMORY Data sheet .

LOW_PRIORITY Keywords are applied to DELETE、INSERT、LOAD DATA、REPLACE and UPDATE
HIGH_PRIORITY Keywords are applied to SELECT and INSERT sentence
DELAYED Keywords are applied to INSERT and REPLACE sentence

Use deferred insert operation

DELAYED The modifier applies to INSERT and REPLACE sentence . When DELAYED When the insert operation arrives , The server puts data rows into a queue , And immediately return a status message to the client , In this way, the client can continue the operation before the data table is actually inserted into the record . If the reader reads data from the data table , The data in the queue will be kept , Until there is no reader . Then the server starts inserting deferred data rows （delayed-row） Data rows in the queue . While inserting , The server also checks whether new read requests arrive and wait . If there is , The delayed data row queue is suspended , Allow the reader to continue . When there is no reader , The server starts inserting delayed data rows again . This process has been going on , Until the queue is empty .

Optimize data types and efficiency

Use short data types whenever possible
about MyISAM Fixed data types process faster than variable types , CHAR Faster than VARCHAERMEMORY take VARCHAR and CHAR All as CHAR Type processing BDB Not much difference InnoDB There is no distinction CHAR and VARCHAR , But the main performance impact is the amount of storage , therefore VARCHAR Better performance
Use SELECT * FROM table_name PROCEDURE ANALYSE(), Check out the optimization suggestions
Encryption function MD5(),SHA1(),CRC32()
about BLOB and TEXT type , It takes a lot of time and resources to perform the operation , To do this, you can encrypt the content , Add index , Fast operation based on hash value
Batch operation is more efficient than single line loading ,INSERT INTO table_name VALUES (),(),… Than A single INSERT Efficient
about MyISAM , If you load data into a new table , In order to improve efficiency, the indexes existing in the table should be cancelled , After creating a new table, re create the relevant index

19. Locking mechanism

The finer the lock level , The better the concurrency

The levels of locks are MyISAM and MEMORY Table hierarchy , InnoDB Row hierarchy , BDB Page hierarchy
Table level locks do not deadlock ,InnoDB and BDB Deadlock prone ,InnoDB and BDB After the transaction starts , Assign the corresponding lock according to the request

summary

There may be mistakes in some places , Imperfect , Will continue to improve in the future .