Summary of Mysql database optimization methods (must read)-Mysql Tutorial-php.cn

Summary of Mysql database optimization methods (must read)

不言

Release： 2018-08-18 17:52:06

Original

3190 people have browsed it

This article brings you a summary of Mysql database optimization methods. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

There is no end to learning, and database optimization is divided into various aspects. Here, I have made a relatively complete summary and shared it with colleagues who are working or studying.

Database optimization is divided into the following seven major aspects:

1 , The design of the table must conform to Three Normal Forms (Appropriate inverse three normal forms can also be);

2. Add appropriate indexes, indexes have a great impact on query speed , indexes must be added (primary key index, unique index, ordinary index, full-text index);

3. Add appropriate stored procedures, triggers, transactions, etc.;

4 , read and write separation (master-slave database);

5. Some optimizations of sql statements (sql statements with relatively slow query execution speed);

6. Table partitioning (Table splitting: Divide a large table into multiple tables. Partition: Allocate the contents of a table to different areas for storage );

7. Upgrade the mysql server hardware.

Next I will explain the optimization method in detail.

First and third normal form

First normal form:

Atomicity: The fields in the table cannot be divided any more. As long as it is a relational database, it will automatically meet the first normal form

Relational database (with the concept of rows and columns): mysql , sql server, oracle, db2, infomix, sybase, postgresql, when designing, first have the library->table->field->specific record (content): when storing data, the fields must be designed.

Non-relational database (generally referred to as nosql database): memcache, redis, momgodb, etc.

Second Normal Form:

There are no identical records in a table, which can be solved by using a primary key

Third Normal Form:

Redundant data cannot be stored in the table

Reverse three paradigm design:

##Album table1Life1002Work Photo100


ID	Album name	Album views
	Photo

Photo List##ID123

If we want to calculate the views of an album, we can add the album views field to the album table, and update the album views at the same time when browsing photos.

2. Turn on slow query

Mysql slow query is turned off by default, and SQL statements that exceed 10 seconds are recorded by default.

1. View the slow query record time:

show variables like ‘long_query_time’;

Copy after login

2. Modify the slow query time:

set long_query_time=2;

Copy after login

3. Test through the following function:

benchmark(count,expr)   函数可以测试执行count次expr操作需要的时间

Copy after login

3. Create index

1. Primary key index Features:

(1) There is at most one primary key index in a table

(2) One primary key index can point to multiple columns

( 3) The columns of the primary key index cannot have duplicate values, nor can they have null

(4) The primary key index is highly efficient.

2. Characteristics of unique indexes:

(1) There can be multiple unique indexes in a table

(2) A unique index can point to multiple columns,

(3) If not null is not specified on the unique index, the column can be empty, and there can be multiple nulls at the same time,

(4) The unique index is more efficient.

3. Ordinary index:

Using ordinary index is mainly to improve query efficiency

4 , Full-text index

The full-text index mysql5.5 that comes with mysql does not support Chinese, but supports English. It also requires that the storage engine of the table is myisam. If you want to support Chinese, there are two options,

(1) Use aphinx Chinese version coreseek (to replace the full-text index)

(2) Plug-in mysqlcft.

Main problems with adding indexes:

(1) Indexes should be created for fields that are frequently used as query conditions,

the uniqueness is too high Poor fields are not suitable for creating an index alone, even if they are frequently used as query conditions, Fields that are updated very frequently are not suitable for creating an index

(2) will not appear in the WHERE clause Fields should not be indexed. The index comes at a cost. Although the query speed is improved, it

will affect the efficiency of additions and deletions. And the index file will take up space.

4. Table partitioning and partitioning

##Vertical partitioning Table (content main table additional table):
Content main table: stores some public information of various data, such as the name of the data, addition time, etc.,

You can use multiple additional tables, which store unique information about some data.

The main reason is that the data in the main content table is accessed more frequently.

Features: different table structures

Horizontal table splitting:

The data exists in different tables

Features: The same table structure

##Partition:

is to store a table in different areas of the disk, but it is still one table.

Basic concepts:

(1)Range (range) – This mode allows the data to be divided into different scope. For example, a table can be divided into several partitions by year.

(2)List (predefined list) – This mode allows the system to split data by the value of a predefined list.

(3)Hash (Hash) – This mode allows calculation of the Hash Key of one or more columns of the table, and finally the data corresponding to different values of this Hash code Areas are divided. For example, you can create a table that partitions the table's primary key.

(4)Key（键值）-上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

分区表的限制：

(1)只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列。

(2)最大分区数目不能超过1024。

(3)如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键在内。

(4)按日期进行分区很非常适合，因为很多日期函数可以用。但是对于字符串来说合适的分区函数不太多。

五、并发处理的锁机制

锁机制：在执行时，只有一个用户获得锁，其他用户处于阻塞状态，需要等待解锁。

mysql 的锁有以下几种形式：

表级锁：开销小，加锁快，发生锁冲突的概率最高，并发度最低。myisam引擎属于这种类型。

行级锁：开销大，加锁慢，发生锁冲突的概率最低，并发度也最高。innodb属于这种类型。

表锁的演示：