MySQL design specifications and performance optimization

Introduction

MySQL is currently one of the most widely used relational database, if used properly, can support enterprise-class high concurrency, high-reliability services, improper use and even a slightly higher level of personal sites are difficult to support;

Even with the use of cache, a large number of database access is still unavoidable, even if the long cache expiration date, and the cache hit ratio is ideal, but 缓存的创建 and 过期后的重建 all need to access the database;

This article mainly from the MySQL table structure design specification and the MySQL own performance optimization two aspects to discuss how to optimize the MySQL database;

MySQL table structure design specification 1. Database Design Naming conventions

(1) Database, data tables are used prefix, prefix name generally not more than 5 words;

# The official database name is made up of lowercase English and underscores to try to indicate which application or system is in use; mcs_webimops_portal # Backup database name is composed of the official library name plus the backup time; ops_portal_20150621mcs_webim_ 20150622 # The data table of the related application uses the same prefix, the prefix name generally does not exceed 5 words; Webim_userwebim_group # The Backup data table name is made up of the official table name plus the backup time; webim_user_20150620webim_group_ 20150620

(2) The field name is completed with the word combination, the first letter lowercase, the first letter of the following words (camel style), preferably with a table name prefix;

# If the fields of table Webim_user useridusernameuserpassword# the associated fields between tables and tables, use a uniform name; # Store data for a field with as little storage space as possible;

2. Standardized design of database (1) Normalization design

In the design of actual relationship pattern, the third normal form is generally followed--there can be no dependency between the non-primary key fields in a data table;

Specific reference: Normalization-Database design principles

(2) Anti-normalization design

Example: Show my friends list on the page;
1. Follow the third paradigm

(User ID, friend ID) (User ID, user nickname, user email, registration time, contact number)

2. Inverse Paradigm Design

(User ID, friend ID, friend nickname) (User ID, user nickname, user email, registration time, contact number)

The problem of inverse normalization:
1. Data redundancy;
2. Updates result in inconsistent data, which can be modified by means of periodic synchronization;

Advantages of anti-normalization:
It is very important to reduce the cost of reading data , and it is necessary to use inverse normalization design according to different scenarios.

MySQL's own performance optimization

MySQL self-optimization is mainly described in the following ways:

1. Query statement optimization;
2. Slow query optimization;
3. mysql lock mechanism analysis and optimization;
4. Parameter configuration optimization;

Query statement optimization 1. MySQL Status report

Show status;show engine InnoDB status;

Some of the more friendly third-party tools:mysqlreport(),mysqltunner,mytop , etc., can show a more friendly status report;

2. Correct use of the index

If the index is used improperly, any other optimizations will be meaningless;

Index purpose

The index is similar to the directory of the book, through the continuous narrowing want to get the range of data to filter out the final desired results, speed up the query;

Index type

Indexed single-column index and composite index, one index of the combined index contains multiple columns;
MySQL index types include:

(1) Normal index --No restrictions

# CREATE INDEX IndexName on TableName (columnName (length)); # If it is a Char,varchar type, length can be less than the actual length of the field; # If it is a blob and text type, you must specify length;# to modify the table structure alter TABLENAME add index IndexName on (columnName (length)); # Directly specify CREATE table when creating tables TableName (    ID int not NULL,    username varchar (+) NOT NULL,    index IndexName (columnName (length))); # DROP Index Drop Index indexname on tablename;# View Index show index from TableName;

(2) Unique index --the value of the indexed column must be unique, allow null values, and, if it is a composite index, the combination of the column values must be unique;

# Create an index directly creates the unique index indexname on tableName (columnName (length)); # Modify the table structure alter TABLENAME add unique indexname on (col Umnname (length)); # Directly specifying CREATE TABLE TABLENAME (    ID int not NULL,    username varchar (+) not NULL when creating tables,    unique IndexName columnName (length));

(3) primary key index --is a unique index, but does not allow null values, generally is to create a table directly when the primary key index;

CREATE TABLE tableName (    ID int not NULL,    username varchar (+) NOT NULL,    primary key (ID));

(4) combined index --Adding indexes for multiple columns;

# Assume that the data table name, age, address, zip and other fields, need to create a combined index for name, age, zip; create index indexname on TableName (name (ten), age, Zip); # or Al ter table TableName Add index IndexName (name (ten), age, zip);

For the varchar Type field, if the length is too long, it is better to limit the length of the index, can speed up the index query speed, reduce the size of the index file;

Leftmost prefix match

such as the combination of the above Name,age,zip index, the following combination will be used to the index, explain can be used for analysis:

SELECT * from TableName where name= "Lee" and Age=20 and zip= "050000", select * from TableName where name= "Lee" and Age=20;s Elect * from TableName where name= "Lee"; # Composite indexes can also work for queries that contain order by and group by, as well as the leftmost prefix principle (invalid for order by hash index); Select * FR Om tableName ORDER by name, age, Zip;select * from TableName where name= ' Lee ' ORDER by age, zip;# for group by, it is generally necessary to group records before placing them in New temporary tables, and then perform function operations such as COUNT (), sum (), Max (), and so on; # if you have an appropriate index, you can use an index instead of creating a temporary table; select COUNT (id) from tableName where sex= ' m ' GROUP by Age, zip;

The query optimizer automatically adjusts the order of conditional expressions to match the combined index;
Be sure to pay attention to the sequence when indexing, (Key1, Key2) and (Key2, key1) are completely different;

The time to build the index

In general, the columns that appear in the where and join need to be indexed, MySQL only,,,,, and < <= = > >= between in sometime (not with wildcards% and _ Start of the query) like will use the index;

Select A.name from table1 as a left join Table2 B on A.name=b.username where b.age=20 and b.zip= ' 053000 '; # at this point, you need to username, Age and zip indexing;

The shortcomings of the index

Index has a great advantage, but can not be abused, according to the actual situation to decide whether to use the index, which words Jianjian index, generally in the query volume of more than the table will be indexed;

• Indexes reduce the speed of updating tables, such as the insert,update,delete operation, when updating a table requires not only saving the data, but also saving the index file;
• Too many combinations of indexes can greatly increase the speed of index file expansion, causing disk space storage problems, a composite index containing multiple fields may have exceeded the data itself, and too many indexes, can also make MySQL select the best index to use (using the use index (KEY_ list) to specify the index to use when querying;
• For columns with unique values, the index works best, and for columns with multiple duplicate values, such as age or gender, indexing is not a good idea;

Use index considerations

1. The index does not contain a column with null values, so try not to make the default value of the field NULL when designing the database, otherwise the index of the relevant field cannot be established;
2. Using a short index, it is best to specify the length of the varchar type Word Jianjian index, as long as the first n characters than characters value is unique, improve the query speed, save disk space, reduce I/O operations;
3. MySQL queries only use an index, so if there are multiple fields in a query statement that need to be indexed, it is best to set up the combined index according to the leftmost prefix matching principle;
4. Like statements are generally discouraged, in the case of large amounts of data, it is very easy to cause performance problems, if not used, the wildcard% must be put to the back, such as "abc%";
5. Do not operate on the column, for example select * from users where YEAR(datetime)<2015; , will cause the index to fail, the full table scan;
6. Do not use not in and in;

How the Index Works

Main reference: MySQL indexing principle and slow query optimization

3. Join Statement optimization

Join statements are divided into inner and outer joins;

Internal connection:

SELECT * from a INNER join B on a.id = b.id;# equivalent to select * from A, where a.id = b.ID;

The inner connection is to retrieve the data rows that exactly match the conditions of the connection;

External connection:

Select ID, name, action from user as U left join user_action A on u.id = a.user_id;

The outer join retains all the data of the driver table, and the matching table cannot match the data with null output;

How external connections work

Reads a record from the left table, selects all the (n) data of the right table record (s) that match the condition in on, makes a connection, forms N records (including duplicate rows), and if there are no records matching the on condition on the right, the connected fields are null and continue to read the next one;

Find all the records in the left table but not in the right table:

Note: a.user_id must be declared as not NULL if the two columns in the A,u two-table join condition have the same name, use using (COL), select ID, name, action from user as U left    join us Er_action A on u.id = a.user_id    where a.user_id is NULL; # query when manually specifying index SELECT * FROM table1 use index (key1, Key2) where Key1=1 and key2=2 and Key3=3;select * FROM table1 Ignore index (KEY3) where key1=1 and key2=2 and key3=3;

Slow query optimization

To turn on the slow query log:

# Add the following configuration in my.cnf: long_query_time = 1log-slow-queries =/var/log/mysql/mysql_slow.log

# 将所有没有使用索引的查询记录也记录下来（根据需要决定是否开启）：log-queries-not-using-indexes

Slow query tool mysqlsla , you can use this tool to analyze the slow query log;

# mysqlsla-lt Slow/var/log/mysql/mysql_slow.log

Most slow queries are caused by Improper index use , be careful when using indexes, and other reasons are too complex (multi-table union query), the number of data table records too many , etc.

Lock mechanism analysis and optimization

Lock mechanism is another important factor that affects query performance;

The time overhead of a query consists mainly of two parts:

1. Calculation time of the query itself;(mainly affected by indexes)
2. Wait time before the query starts;(affected by lock mechanism)

Reduce table lock waits

The MyISAM Type table provides table-level locking, and you can use Mysqlreport to view the proportion of waiting table lock queries;

MyISAM table lock allows multithreading to read data at the same time, such as select query, no lock waiting;

For update operations, such as update, insert, and delete operations, all queries against the current table are rejected , and the update operation has a default higher priority, that is, when the table lock is released, the update operation will first get locked, and after all is done, the read operation is not completed. Should try to avoid when there are a large number of query requests, batch change data table, otherwise very easy to cause slow query;

You can use the following command to monitor the status of all threads:

Show Processlist\g;

Conclusion:

For applications that are primarily query-driven and have a low time-consuming update operation, there will be no too many lock waits, MyISAM storage engines, and a InnoDB storage engine that provides row locking for sites with frequent data updates and a low query request volume;

Row Locking

The InnoDB storage engine provides row locking support;

Row Locking Advantage : In the case of hybrid Select and update, Row locking can solve the problem of read and write mutex, because the update operation and select operation from different threads, and for the non-peer records, can be carried out concurrently;

Row locking is not always good:

1. The cost of row locking is not smaller than the table lock;

2. In a scenario where all are update operations, row locking can take longer, although the table locks up only one thread at a time in the updating state, while row locking all threads are updating state, but locking is only a logical level of constraint, even though all threads are updating state. But the physical write operation of the disk is executed serially;

3. For all queries of the scene, Row locking also requires additional overhead, the speed relative to the table lock slightly slower;

Storage Engine View

Show table status from DataBaseName where name= ' TableName '; ALTER TABLE TableName Type=myisam;

Parameter Configuration optimizes transactional table performance optimization

In addition to supporting row locking, foreign keys, and its easy-to-fix features, the InnoDB storage engine has the additional advantage of supporting transactions (ACID), and, of course, the fact that most sites do not require transaction-level assurance;
InnoDB is implemented by a pre-write log (WAL), that is, when a transaction commits, it is written to the in-memory transaction log buffer, and then when the transaction log is written to disk, InnoDB updates the actual data and indexes;
If you choose to use a transaction, when the transaction log is written to disk, it is an optimization point;

# The transaction log is immediately written to disk when the transaction commits, and the data and indexes are immediately updated to conform to the persistence principle; innodb_flush_log_at_trx_commit = # Transaction commits are not immediately written to disk, every 1S write disk files once, and flush to disk, Update data and indexes at the same time; # If MySQL crashes, the data in the transaction log buffer is permanently lost in the last 1 seconds, innodb_flush_log_at_trx_commit = 0# writes the disk file immediately when the transaction commits, but the disk is flushed at interval 1S. Update data and indexes at the same time; # operating system crashes will cause data loss; Innodb_flush_log_at_trx_commit = 2

Attention:

"Write to disk File" simply writes data to the kernel buffer in physical memory, "flush to disk" is to actually write the data in the kernel buffer to disk;

will be innodb_flush_log_at_trx_commit set to 0, the best performance can be achieved, while the possibility of data loss is also the largest, if you want to avoid data loss, can be set to 2;

# Set the memory buffer pool size for INNODB data and indexes, which can generally be set to 80%;innodb_buffer_pool_size of the server physical memory = 12G

Using Query caching

Objective: to cache the query results of select in memory for the next direct fetch;

Query_cache_type = 1query_cache_size = 64mquery_cache_limit = 1M

For the cache expiration policy, the mechanism used by MySQL is: When a table has an update operation, all query caches involving the table will be invalidated;

This scenario, for dense select operations and rarely updated tables, is more appropriate to use query caching, and for select and update hybrid applications, it is not appropriate to use query caching;

Temp table

Purpose: creating temporary tables on disk is time consuming, expensive, and requires reducing the number of temporary tables created on disk;

# try to set a large memory space for the temp table, MySQL will enable disk to save the temporary table when the memory space is not enough; tmp_table_size = 512M

Thread pool

MySQL uses multi-threading to handle concurrent connections, if you create a new connection each time, you will be creating a thread, when the system is busy, also increase the cost of MySQL;

# Use persistent connections as much as possible to reduce the repetition of thread creation; thread_cache_size = 100# allows MySQL to cache 100 threads;

Reference

• Http://c.biancheng.net/cpp/html/1468.html
• Http://database.51cto.com/art/200910/156685.htm
• Http://tech.meituan.com/mysql-index.html
• Building a high-performance web site

MySQL design specifications and performance optimization