Mysql database optimization statement

Mysql database optimization statement database statement:
Ddl (Data Definition Language) alter create drop Dml (data operation language) inset delete update www.2cto.com Dtl (Data transaction language) conmmit rollback savepoint Select Dcl (Data Control Statement) grant permissions to revoke reclaim Mysql database optimization:
1. database tables should be properly designed (in line with 3NF, and sometimes appropriate inverse paradigm) 2. SQL statement optimization (indexing, commonly used Tips) 3. Database Configuration 4. Appropriate hardware configuration and operating system 5. read/write splitting Q: What is the database 3 paradigm? 1NF: It is atomic and inseparable (as long as a relational database is used, it will automatically comply with) 2NF: On the basis of 1NF, we consider whether 2NF is satisfied, as long as the table record satisfies uniqueness, that is to say, you cannot have identical records in the same table. Generally, we can design a primary key in the table. 3NF: Based on 2NF:, we consider whether 3NF is satisfied. Our field information can be derived from the association relationship (usually we use foreign keys for processing) the storage engine used for foreign key databases must be innoDB Question 2: the most important thing for innodb Storage engine to configure database parameters is memory, therefore, the following two parameters call Innodb_additional_mem_pool_size = 64 M www.2cto.com Innodb_buffer_pool_size = 1G buffer pool size. For myisam, you need to adjust key_buffer_size and use the show status statement to see, to determine how many insert, update, delete, and other SQL statements you have used: show status like "Com "; // The query will be accurate when it is not closed in the Command window. If it is closed, the Show sessionstatus will be counted again. Like "Com_update"; // even if the window is closed, all the times you have executed will be counted. Show globalstatus like "Com_insert"; Example: if update1 has been used for six times and session statistics are used for six times, if the command window is closed, execute Show session statuslike "Com_update "; the value is 0. 2. If you use Show global status like "Com_insert", it is 6 times. 2. display the number of times that you attempt to connect to the Mysql server. Show status like "Connections "; how long does the database start? Show status like "uptime"; how many slow queries (10 seconds by default) Show status like "Slow_queries"; 4. How to Create a project, select Mysql database supports recording slow query statements into logs for programmers to analyze www.2cto.com. Step 1. Start mysql (Special startup method) a. Start mysqld.exe-slow-queryb in the bindirectory of mysqlinstallation directory) netstat-an check whether port 3306 is enabled c) query the number of slow Queries show status like "Slow_queries"; d) set the slow query time set long_query_time = 1; index optimization: for example, add the primary key index Alter table user add primary key (id); Delete the primary key index Alter table user drop primary key Delete the index Alter table user drop index name Show index (es) from table name Show keys from table name Desc Adding an index to a table name will make the query much faster. The principle is that if a book does not have a directory, it will be hard to find a knowledge point. You can only search for it, if there is a directory, it will quickly find the location of this knowledge point in that chapter. In this way, the query will naturally be faster, but there will be some disadvantages. indexes will bring benefits to the query, however, it is naturally troublesome to add update delete. For example, if you add a knowledge point, you are not allowed to add it to the directory that belongs to that chapter, it also changes during modification and deletion to ensure information accuracy.
Command to automatically analyze whether indexes are required: explainExample: explain select * from emp where id = 9;

Classification of indexes: the unique key Index (unique) Index (General Index) full-text Index (fulltext) composite Index (multiple columns are joined together) it is more appropriate for www.2cto.com to add indexes to those columns: 1. For fields frequently used as query conditions, an index should be added. 2. For fields with poor uniqueness, it is not suitable to create an index separately, timely and frequent query conditions 3. Fields with frequent updates are not suitable for creating indexes 4. fields that do not appear in the where clause should not create indexes to query all indexes in a table: use show indexes from table (table name) indexes: the most important condition for an index to be used in a query is that the following indexes must be used in the query conditions, as long as the query condition uses the leftmost column, the index is generally used 2. For queries that use like, if the query is '% aaa', the index 'aaa %' will not be used. Index 1 will not be used in the tables below the index. If the condition has or, even if Conditional indexes are not used. 2. If multiple-column indexes are not used in the first part, indexes are not used. 3. If the column type is a string, the Like Query starts with %. 4. If the column type is a string, the data must be enclosed by quotation marks in the condition. Otherwise, the index is not used. 5. If mysql estimates that full table scan is faster than indexing, no index is used. View the index usage. Show status like 'handler _ read % ';

Only the larger the value of handler_read_key, the better the value. The smaller the value of Handler_read_rnd_next, the better the database type. MyISAM does not support transactions and Foreign keys. A table consists of three files ,. frm. myi. myd innoDB supports fast query of transactions and Foreign keys for MyISAM, but the time and space of deleting fields will not be released. You must manually release optimize table table_name www.2cto.com database sub-tables: 1. Horizontal sub-tables
2. Vertical table sharding: Stu table: id Name Pass PhotoMark table Id Sid Question answer vertical table sharding for associated tables. For example, there is a student's personal information (with an avatar) table, an exam information (exam questions and answers) table. If I want to query the exam scores and personal information of a student, mysql will associate the student's personal information with the exam information table, the speed is much lower, so we need to separate the answers and questions in the exam information to create a table, if you want to improve the performance, you can separate the profile picture into a separate table (such as Baidu's separate image server) for read/write Splitting:

Author web8