Mysql database optimization and mysql web Performance Optimization

This article introduces mysql database optimization and mysql web performance optimization. If you need to know about it, let's take a look.

This article introduces mysql database optimization and mysql web performance optimization. If you need to know about it, let's take a look.

Statement:

Ddl (Data Definition Language) alter create drop

Dml (data operation language) inset update

Dtl (Data transaction language) conmmit rollback savepoint

Select

Dcl (Data Control statement) grant permissions revoke reclaim

Mysql database optimization:

1. database tables should be properly designed (in line with 3NF, and sometimes appropriate inverse paradigm is required)

2. SQL statement optimization (indexing, common Tips)

3. Database Configuration

4. Proper 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)

2NF: Based on 1NF, we consider whether 2NF is satisfied. As long as the table records satisfy uniqueness, that is to say, it is impossible for you to 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 for foreign key databases must be innoDB

Question 2: database parameter configuration

The most important thing for the innodb Storage engine is the memory, so the following two parameters are greatly tuned.

Innodb_additional_mem_pool_size = 64 M

Innodb_buffer_pool_size = 1G buffer pool size

For myisam, you need to adjust the key_buffer_size

You can use the show status statement to view the current status to determine which parameters to adjust.

1. How many insert, update, and delete operations have you used?

SQL: show status like "Com ";

// The query will be accurate when it is not closed in the Command window. If it is closed, the statistics will start from the beginning.

Show status like "Com_update ";

// Even if the window is closed, all the times you have executed will be counted.

Show globalstatus like "Com_insert ";

Example: session

Assume that six update operations have been used.

1. The session statistics will be 6 times.

If the Show session statuslike "Com_update" is executed after the command window is closed, the value is 0.

2. But if you use Show global status like "Com_insert", it will be 6 times.

2. 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 ";

Display the number of slow queries (10 seconds by default)

Show status like "Slow_queries ";

4. How to Find the select statement for slow query in a project, mysql database supports recording slow query statements into logs for analysis by programmers

Steps:

1. Start mysql (Special Startup Mode)

A) Start mysqld.exe-slow-query in the binlog of the mysqlinstallation directory.

B) Run Netstat-an to check whether port 3306 is enabled.

C) The number of slow queries. show status like "Slow_queries ";

D) set the slow query time set long_query_time = 1;

:

For example, adding 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 name

Show Index

Show index (es) from Table Name

Show keys from Table Name

Desc table name

Adding an index will make the query much faster. The principle is like if a book has no 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.

Whether an index is required for automatic analysis: explain

Example: explain select * from emp where id = 9;

Index category:

Primary key Index)

Unique key index (unique)

Index (Common Index)

Full text index (fulltext)

Composite Index (multiple columns are joined together)

It is more appropriate to add indexes to those columns:
1. The fields frequently used as query conditions should be indexed.

2. A field with poor uniqueness is not suitable for creating an index separately, and is frequently used as a query condition in a timely manner.

3. Frequent Updates to fields are not suitable for index creation.

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 indexes:

The most important condition for an index to be used for a query is that the index must be used in the query conditions.

Indexes may be used in the following situations:

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

2. For queries using like, if '% aaa' does not use the index 'aaa %', it will use the index.

No index is used in the following tables.

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

2. If multiple-column indexes are not the first part, they are not used.

3. The Like Query starts with %.

4. If the column type is a string, you must use quotation marks to enclose the data in the condition. Otherwise, no index is used.

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

View index usage

Show status like 'handler _ read % ';

Only the greater the value of handler_read_key, the better.

The smaller the value of Handler_read_rnd_next, the better.

Type:

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

InnoDB supports transactions and Foreign keys

For MyISAM, queries are fast, but deleting fields in time and space is not released. You must manually release optimize table table_name.

Database sub-tables:

1. Horizontal table sharding

2. Vertical table sharding:

Stu table:
Id

Name

Pass

Photo

Mark table

Id

Sid

Question

Answer

Vertical table sharding: for example, there is a student's personal information (with an avatar) table and an exam information (exam questions and answers) table, at this time, I want to check a student's exam score and personal information. mysql will associate the student's personal information with the exam information table, and the speed will be much lower, therefore, you need to separate the answers and questions in the exam information to create a table. If you want to improve, you can also separate the profile picture into a separate table (such as Baidu's independent image server)

Read/write Splitting:

The Web program is slow. The first check object is the database, which is known to all programs that have been written for several years. What should I do if I want to track SQL statements that drag down the server performance in MySQL? Log_slow_queries must be enabled for a slow query output. You can set it in the MySQL configuration file (my. ini/my. cnf) or through the MySQL client. The advantage of the second method is that the setting takes effect without restarting the MySQL service. Let's try this:
First, connect to the Mysql server through the client, and then enter the following statement:
Set global log_slow_queries = ON;
Set global long_query_time = 3;

In this way, MySQL records SQL statements that take more than 3 seconds and outputs them to a slow query log file. The question is, where is the slow query log file? Run the following statement on the MYSQL client:
Show variables like 'slow _ query_log_file ';
The location of the MySQL slow query log file is displayed. My is:/usr/local/mysql/data/host-slow.log