Shulou(Shulou.com)05/31 Report--

This article focuses on "mysql database optimization statement", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn the "mysql database optimization statement"!

Mysql optimized statement

Database statement:

Ddl (data definition language) alter create drop

Dml (data manipulation language) inset delete update

Dtl (data transaction language) conmmit rollback savepoint

Select

Dcl (data control statement) grant authorized revoke recovery

Mysql database optimization:

1. Database tables should be properly designed (in accordance with 3NF, sometimes an appropriate inverse normal form is required)

2. Optimization of Sql statements (indexing, commonly used tips)

3. Configuration of database

4. Appropriate hardware configuration and operating system

5. Separation of reading and writing

Q: what is the database 3 paradigm?

1NF: it is atomic and indivisible (as long as you use a relational database, it will automatically match)

2NF: on the basis of satisfying 1NF, we consider whether to satisfy 2NF, as long as the records of the table are unique, that is, it is impossible for you to have exactly the same records in the same table. Generally speaking, we can design a primary key in the table.

3NF: on the basis of satisfying 2NF:, we consider whether to satisfy 3NF, that is, our field information can be derived through the associated relationship (usually we deal with it through foreign keys) the storage engine of the foreign key database must be innoDB.

Q2: database parameter configuration

The most important thing for the innodb storage engine is memory, so the following two parameters are adjusted very large

Innodb_additional_mem_pool_size = 64m www.2cto.com

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 decide 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 be counted again if it is closed.

Show sessionstatus like "Com_update"

/ / even if you close the window, it will count all the times you have executed.

Show globalstatus like "Com_insert"

Example: session

If you have used update 6 times

1. It will be 6 times by using session.

If Show session statuslike "Com_update" is executed after closing the command window; it will be 0

2. But if you use Show global status like "Com_insert"; it's 6 times.

Display the number of attempts to connect to the Mysql server

Show status like "Connections"

How long has the database been started?

Show status like "uptime"

Displays the number of slow queries (default is 10 seconds)

Show status like "Slow_queries"

4. How to find the slow query select in a project. The database supports recording the slow query statements in the log for programmers to analyze www.2cto.com.

Steps:

1. Start mysql (special startup mode)

A) start mysqld.exe-slow-query under the bin directory under the installation directory of mysql

B) Netstat-an to see if port 3306 is started

C) 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 a primary key index

Alter table user add primary key (id)

Delete primary key index

Alter table user drop primary key

Delete index

Alter table user drop index index name

Show index

Show index (es) from table name

Show keys from table name

Desc table name

Increasing the index makes the query much faster, the principle is like a book without a catalog, then if you want to find a knowledge point, it will be very difficult to find, you can only flip through it bit by bit. If there is a directory, it will quickly locate the location of this knowledge point in that chapter, so the query will naturally be fast, but there will be advantages and disadvantages, and the index will bring benefits to the query. But it is naturally troublesome for add update delete, for example, if you add a knowledge point, you are not allowed to add that knowledge point in the catalog that it belongs to that chapter, and it will also be changed when you modify and delete it, so as to maintain the accuracy of the information.

A command that automatically analyzes whether an index is required: explain

Example: explain select * from emp where id = 9

Classification of the index:

Primary key Index (primary key)

Unique key Index (unique)

Index (General Index)

Full text Index (fulltext)

Composite index (multiple columns and together)

It is appropriate to add indexes on those columns:

1. Fields that are frequently used as query criteria should be indexed.

2. The fields with poor uniqueness are not suitable to create indexes separately, and they can be used as query conditions timely and frequently.

3. Fields that are updated very frequently are not suitable for creating indexes

4. Fields that do not appear in the where clause should not be indexed

Query all indexes in a table: show indexes from table (table name)

Use of the index:

The most important condition for a query to use an index is to use an index in the query condition.

Indexes may be used in the following situations

1. For a multi-column index created, the index is generally used as long as the leftmost column is used in the query condition.

2. For queries that use like, if the query is'% aaa' will not use the index 'aaa%' will use the index

Indexes will not be used in the following tables

1. If there is an or in the condition, it will not be used even if there is a conditional index.

2. For a multi-column index, if it is not the first part of the use, the index will not be used

3. The Like query starts with%

4. if the column type is a string, be sure to enclose the data in quotation marks in the condition, otherwise the index is not used.

5. If mysql estimates that using a full table scan is faster than using an index, no index is used.

View the usage of the index

Show status like 'handler_read%'

Only the bigger the handler_read_key, the better.

The smaller the Handler_read_rnd_next, the better

Database type:

MyISAM does not support transactions and foreign keys. A table consists of three files, .frm .myi .myd

InnoDB supports transactions and foreign keys

Query is fast for MyISAM, but space will not be freed when fields are deleted, so you must release optimize table table_name manually.

At this point, I believe that everyone on the "mysql database optimization statement" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.