Shulou(Shulou.com)06/01 Report--

This article will give you a detailed explanation on how to optimize the Mysql database. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

There is no end to learning, database optimization is divided into all aspects, here, I made a more comprehensive summary, sharing with colleagues who are working or studying.

The optimization of the database is divided into the following seven aspects:

1. The design of the table should conform to the three paradigms (the appropriate anti-three paradigms can also be used).

2. add the appropriate index, the index has a great influence on the query speed, and the index must be added (primary key index, unique index, general index, full-text index).

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

4. Read-write separation (master-slave database)

5. Some optimizations for sql statements (sql statements with slow query execution)

6. Sub-table partitioning (sub-table: dividing a large table into multiple tables. Partition: assign the contents of a table to different areas of storage)

7. Upgrade the hardware of mysql server.

Next, I will explain the way of optimization in detail.

One and three paradigms

The first paradigm:

Atomicity: the fields in the table can no longer be divided, as long as it is a relational database, it naturally automatically satisfies the first paradigm.

Relational database (with the concept of rows and columns): mysql, sql server, oracle, db2, infomix, sybase, postgresql, when designing, there are libraries-> tables-> fields-> specific records (content): when storing data, you need to design fields.

Non-relational database (generally refers to nosql database): memcache, redis, momgodb and so on.

The second paradigm:

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

The third paradigm:

Redundant data cannot be stored in a table

Anti-three-paradigm design:

Photo album table ID photo album name photo album views 1 life photos 1002 work photos 1002 photo table ID photo name photo album ID views 1 my puppy 1492 my kitten 1513 my colleague 2100

If you want to count the views of an album, we can add the album views field to the album table and update the album views while browsing the photos.

Second, open slow query

Mysql slow queries are turned off by default, and sql statements are recorded by default for more than 10 seconds.

1. View slow query record time:

Show variables like 'long_query_time'

two。 Modify slow query time:

Set long_query_time=2

3. Test with one of the following functions:

The benchmark (count,expr) function can test the time required to perform a count expr operation

Third, establish an index

1. The characteristics of primary key index:

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

(2) A primary key index can point to multiple columns.

(3) columns of primary key indexes cannot have duplicate values or null.

(4) the efficiency of primary key index is high.

2. The characteristics of unique index:

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

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

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

(4) the efficiency of unique index is high.

3. General index:

The main purpose of using general index is to improve query efficiency.

4. Full-text index

The full-text index mysql5.5 provided by mysql does not support Chinese but English, and 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 of coreseek (instead of full-text indexing)

(2) plug-in mysqlcft.

The main problem with adding an index:

(1) Indexes should be created more frequently as query condition fields, and fields with poor uniqueness are not suitable for creating indexes separately, even if frequent fields are used as query conditions, fields that are updated very frequently are not suitable for creating indexes.

(2) the field should not be created in the WHERE clause, and the index is at a cost. Although the query speed is improved, it will affect the efficiency of adding and deleting. And index files take up space.

IV. Sub-tables and partitions

Vertical sub-table (content main table + additional table):

Content master table: stores some common information about all kinds of data, such as the name of the data, adding time, etc.

Multiple additional tables can be used to store unique information about some data.

The main reason is that the data access in the main table of the content is more frequent.

Features: different table structure

Horizontal subtable:

Store table data in different tables.

Features: the table structure is the same

Zoning:

Is to store a table in different areas of the disk, which is still a table.

Basic concepts:

(1) Range (range)-this mode allows data to be divided into different ranges. 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 predefined list values.

(3) Hash (hash)-this mode allows you to calculate the Hash Key of one or more columns of the table and finally partition the data regions corresponding to different values of the Hash code. For example, you can create a table that partitions the primary key of the table.

(4) Key (key value)-an extension of the above Hash mode, where Hash Key is generated by the MySQL system.

Restrictions on partitioned tables:

(1) only the integer columns of the data table can be partitioned, or the data columns can be converted into integer columns through the partition function.

(2) the maximum number of divisions must not exceed 1024.

(3) if there is a unique index or primary key, the partition column must be included in all unique indexes or primary keys.

(4) Partition by date is very appropriate because many date functions are available. But there are not too many suitable partitioning functions for strings.

Fifth, the locking mechanism of concurrent processing

Locking mechanism: during execution, only one user gets the lock, and the other users are in a blocking state and need to wait for the lock to be unlocked.

Mysql locks come in the following forms:

Table-level lock: low overhead, fast locking, the highest probability of lock conflict and the lowest degree of concurrency. Myisam engines fall into this type.

Row-level lock: high overhead, slow locking, the lowest probability of lock conflict and the highest degree of concurrency. Innodb belongs to this type.

Demonstration of table lock:

1. Read operations on myisam tables (with read locks) do not block read requests from other processes to the same table, but block write requests to the same table. The operations of other processes are performed only when the read lock is released.

two。 After a read lock is added to the table, other processes can only query the table and block when it is modified.

3. The current process can perform query operations, but cannot perform modification operations. Cannot operate on tables that are not locked.

4. Syntax for locking tables:

Lock table table name read | write

5. You can also lock multiple tables

6. The write operation to the myisam table (with a write lock) will block any operation of other processes on the locked table and cannot read or write.

7. After the table is locked, only the current process can perform any operation on the locked table. The operations of other processes will be blocked.

Demonstration of row locks:

The 1.innodb storage engine is implemented by locking the index items on the index, which means that innodb uses row-level locks only if the data is retrieved by the index condition, otherwise innodb uses table locks.

two。 After the row lock is opened, when the current process performs an operation on a record, other processes cannot manipulate records with the same id as the current process.

There are file locks in php, and file locks are mostly used in actual projects because table locks block. When write locks are added to some tables, other processes cannot operate. This will block the entire site and slow down the site.

On how to optimize the Mysql database to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.