DDL (data definition language) ALTER create drop
DML (data manipulation language) inset Delete update
DTL (Data transaction language) Conmmit rollback savepoint
DCL (Data Control statement) GRANT permission revoke reclaim
MySQL Database optimization:
1, the database table to design reasonable (3NF, sometimes also need appropriate inverse paradigm)
2, SQL statement Optimization (index, common tips)
3, the configuration of the database
4, the appropriate hardware configuration and operating system
5, read and write separation
Q: What is the database 3 paradigm?
1NF: Is atomic, indivisible (as long as the use of a relational database, will automatically meet)
2NF: On the basis of 1NF, we consider whether to meet 2NF, as long as the record of the table to meet the uniqueness, that is, your table can not appear exactly the same record, generally speaking, we design a primary key in the table.
3NF: On the basis of satisfying 2NF: we consider whether to satisfy 3NF, both our field information can be derived from the associated relationship (usually we handle by foreign key) the storage engine using the foreign key database must be InnoDB
Question 2: Database parameter configuration
The most important thing for the InnoDB storage engine is memory, so the following two parameters are very large
Innodb_additional_mem_pool_size = 64M
Innodb_buffer_pool_size = 1G buffer pool Size
For MyISAM, you need to adjust the key_buffer_size
You can see the current state with the show status statement to determine which parameters to adjust
First, show how many times you have used insert, UPDATE, DELETE, etc.
Sql:show status like "Com";
The query will be accurate when it is not closed in the command window, and will start counting again if it closes
Show sessionstatus like "com_update";
Even closing the window will count all the times you've done it.
Show globalstatus like "Com_insert";
If you have already used 6 times update
1, with the session statistics will be 6 times
If the command window is closed after the execution show session statuslike "Com_update"; It's 0.
2, but if you use show global status like "Com_insert"; it's 6 times.
Second, show attempts to connect to the MySQL server number of times
Show status like "connections";
How long has the database been started?
Show status like "uptime";
Show how many slow queries (default is 10 seconds)
Show status like "Slow_queries";
How to find a slow query in a project Select,mysql database support to the slow query of the statement recorded in the log, for programmers to analyze
1. Start MySQL (special startup mode)
A) in the MySQL installation directory under the Bin directory to start mysqld.exe–slow-query
b) Netstat–an See if Port 3306 is starting
C Query the number of slow queries show the status like "Slow_queries";
D Setting the time set long_query_time=1 for slow queries;
For example, to increase the primary key index
Alter table User Add primary key (ID);
Alter table user Drop primary key
Alter table User Drop INDEX index name
Show index (ES) from table name
Show keys from table name
Adding an index causes the query to get a lot faster, and it works like a book. If you're looking for a knowledge point, it's hard to find, only 1.1 points of the search, if there is a directory will soon be positioned to this knowledge point in that chapter about what position such inquiries will naturally be faster ah, But the advantage has to have the disadvantage, the index can bring the benefit to the query, but it's a hassle for add update delete, for example, if you're adding a knowledge point, you're not allowed to add to the table of contents the knowledge that he belongs to in the chapter, and also changes as you change and delete. To keep the information accurate.
A command that automatically analyzes whether an index needs to be used: explain
Example:explain SELECT * from emp where id = 9;
Classification of indexes:
Primary key index (primary key)
Unique key index (unique)
Full-text Indexing (Fulltext)
Composite index (Dole together)
It is more appropriate to add indexes to those columns:
1, the more frequent as the query criteria should be indexed fields
2, the uniqueness of the poor field is not suitable for the creation of a separate index, in a timely manner as a query condition
3, updated very frequently fields are not suitable for creating indexes
4, fields that do not appear in the WHERE clause should not create an index
Query all indexes in a table: Show indexes from table (table name)
The most important condition for a query to use an index is the need to use an index in the query criteria
The indexes may be used in the following situations
1, for the creation of multiple-column index, as long as the query conditions using the leftmost column, the index will generally be used
2, for use like query, query if it is '%aaa ' will not use to index ' aaa% ' will use to index
Indexes are not used in the following tables
1, if the condition has or, even if there is a conditional index will not use
2, for a multiple-column index, not the first part of the use, you will not use the index
3, like query is to start with%
4. If the column type is a string, be sure to enclose the data in quotes in the condition, otherwise the index will not be used.
5, if the MySQL estimate using full table scan than using the index faster, then do not use the index.
Viewing the use of indexes
Show status like ' handler_read% ';
Only the bigger the Handler_read_key, the better.
Handler_read_rnd_next as small as possible.
MyISAM does not support transactions and foreign keys, and a table consists of three files,. frm. myi. myd
InnoDB supports transactions and foreign keys
The query is fast for MyISAM, but the space is not freed when you delete the field, and you must manually release the Optimize table table_name
1, the level of the table
The vertical table is for the associated type of table, for example, there is a student's personal information (have Head) table, a test information (test questions and answers) table, then I want to check a student's test scores and personal information, then MySQL will be the student's personal information and test Information Table Association, speed back a lot, So to the test information in the answer and the topic separate in the creation of a table, if the increase can also be separate head portrait for a separate table (such as Baidu separate picture server)
Read and write separation:
The Web program is slow, the first test object is the database, wrote a few years of program are aware. Now you want to track down the SQL statements in MySQL that drag server performance? Need to open a slow query output of an organ: log_slow_queries. Can be set in the MySQL configuration file (my.ini/my.cnf) or through the MySQL client temporary settings. The advantage of the second approach is that you can take the settings into effect without restarting the MySQL service. Let's try this one:
First connect to the MySQL server through the client, and enter the following statement:
SET GLOBAL log_slow_queries = on;
SET GLOBAL long_query_time = 3;
This allows MySQL to record the SQL statement that takes >=3 seconds and output it to a slow query log file. Here's the question, where is this slow query log file? As follows, execute the following statement in the MySQL client:
Show variables like ' slow_query_log_file ';
You'll see the MySQL slow query log file location. Mine is:/usr/local/mysql/data/host-slow.log.
