Summary of Mysql database optimization methods (must read)

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’;

2. Modify the slow query time:

set long_query_time=2;

3. Test through the following function:

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

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属于这种类型。

表锁的演示：

1.对myisam表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的操作。

2.表添加读锁后，其他进程对该表只能查询操作，修改时会被阻塞。

3.当前进程，能够执行查询操作，不能执行修改操作。不能对没有锁定的表进行操作。

4.锁表的语法：

lock table 表名 read|write

5.也可以锁定多个表

6.对myisam表的写操作（加写锁），会阻塞其他进程对锁定表的任何操作，不能读写，

7.表加写锁后，则只有当前进程对锁定的表，可以执行任何操作。其他进程的操作会被阻塞。

 行锁的演示：

1.innodb存储引擎是通过给索引上的索引项加锁来实现的，这就意味着：只有通过索引条件检索数据，innodb才会使用行级锁，否则，innodb使用表锁。

2.开启行锁后，当前进程在针对某条记录执行操作时，其他进程不能操作和当前进程相同id的记录。

php里面有文件锁，在实际的项目中多数使用文件锁，因为表锁，会阻塞，当对一些表添加写锁后，其他进程就不能操作了。这样会阻塞整个网站，会拖慢网站的速度。

相关推荐：

Photo Name	Album ID	Views
My puppy	1	49
My kitten	1	51
My colleagues	2	100

The above is the detailed content of Summary of Mysql database optimization methods (must read). For more information, please follow other related articles on the PHP Chinese website!