How system optimization is done-database optimization

Hello everyone, here is "chat system optimization" and synchronize updates at the following address

• Blog Park: http://www.cnblogs.com/changsong/
• Knowledge Column: Https://zhuanlan.zhihu.com/youhua

Here I will be based on the Java EE System and the Internet architecture, to talk about the various aspects of system optimization!

Objective

Most of the company's databases are MySQL, although today's NoSQL database such as MONGO, HBase is more and more popular, but the traditional MySQL is still the most used in the industry. This article is taking MySQL as an example.

Database

The database is the only single-point resource in the application system and is especially careful about the use of the database's resources. Here are some points to note

1. Where the database is stored as a data store, no valuable resources should be used for data conversion or statistical operations, and some character conversions are not used in SQL.
2. Database connection resources are valuable, peripheral systems continue to be allocated on demand
3. Database is not afraid of high QPS small query, but afraid of slow query, so please eliminate slow query.
4. Index is not as much as possible, maintaining index resources also consumes database computing resources.
5. Database computing power is more valuable than storage
6. If it is a master-slave architecture, the network bandwidth and stability of the main machine and slave machine should be ensured
7. Do not store big data such as pictures, files, etc. in the database
8. Prohibit online database stress testing
9. Prohibit direct connection to database from test, development environment
10. Do not bulk update, query database during peak business hours
11. Do not store business logic in MySQL database, write stored procedures and triggers, etc.
12. Disables the ability to perform background management and statistical report classes on the main library, all from the library

Hardware

1. Disk

MySQL every second in a large number of complex query operations, the amount of read and write disk can be imagined. Therefore, disk I/O is generally considered to be one of the biggest constraints on MySQL performance, and it is recommended to use a raid-0+1 disk array.

2.CPU

It is recommended to use at least 4U server to do database server, basically the more the better

3. Memory

Server memory is not recommended to be less than 4GB. Basically, the bigger the better.

System Configuration

MySQL configuration in my.conf, affecting several key configuration properties of the new energy

• Using InnoDB storage Engine 5.5 after the default breaking, support transactions, row-level locks, better recoverability, high concurrency under better performance, multi-core, large memory, SSD and other hardware support better.
• Table Character set uses UTF8MB4 to use the UTF8MB4 character set, if it is a Chinese character, accounting for 3 bytes, but the ASCII code character is 1 bytes; uniform, there will be no conversion garbled risk, and can solve the problem of symbolic expression garbled;
• Max_connections Maximum number of connections (users)
• Innodb_log_file_size is important in the case of high write loads, especially large data sets. The larger the value, the higher the performance, but note that the recovery time may be increased. Set to 64-512MB, varies by server size
• Innodb_buffer_pool_pages_data allocated, number of pages being used
• Innodb_buffer_pool_pages_total buffer Total number of pages
• Innodb_page_size compiled Innodb page size (default 16KB)

Tuning Reference calculation Method:

val = innodb_buffer_pool_pages_data/innodb_buffer_pool_pages_total * 100%

Val > 95% Consider increasing the innodb_buffer_pool_size, 75% of the recommended use of physical memory

Val < 95% is considered to reduce innodb_buffer_pool_size, the recommended setting is: Innodb_buffer_pool_pages_data * innodb_page_size * 1.05/(1024*1024*10 24)

database table Structure

The design goal of table structure, in addition to satisfying the business, minimizes the query operation on the code implementation, so the design of some redundant fields can be designed to reduce the number of database IO.

The concept of big data storage wide tables, such as the popular Elasticsearch, is also the embodiment of this idea.

1. Try to avoid using partitioned table MySQL partition table actual performance is not very good.
2. Split large and low-frequency fields, separating hot and cold data
3. Using reasonable sub-database sub-table strategy, it is recommended to use hash table, table name suffix using decimal number, subscript starting from 0 for the first time to divide the table as many points as possible, to avoid two sub-tables, two times the difficulty and cost of the table is higher
4. The number of fields in a single table is controlled within 20
5. A complete build statement should contain the necessary fields, primary keys, reasonable indexes (all conditional statements in the composite code create a reasonable index, and the primary key must have

Index Design

An index is a double-edged sword that can improve query efficiency but also reduces the speed of insertions and updates and consumes disk space.

1. The number of indexes in a single table does not exceed 5
2. The number of fields in a single index does not exceed 5
3. Use a prefix index on a string, the prefix index is no longer than 10 characters, and if you have a char (200) column, if the majority value is unique within the first 10 characters, do not index the entire column. Indexing the first 10 characters can save a lot of index space, and may make queries faster
4. Table must have primary key, do not use UUID, MD5, hash as primary key, try not to select String column as primary key;
5. High-sensitivity fields are placed first when creating composite indexes, and indexes are not created on low-sensitivity fields, such as "gender"
6. Avoid redundant or duplicate indexes
7. Reasonable creation of federated indexes (avoid redundancy), index (a, B, c) equals index (a), index (A, B), index (A,, B, c)
8. The index is not as much as possible, and is created as needed.
9. Each additional index consumes additional disk space and reduces the performance of write operations
10. Do not perform mathematical operations and function operations on indexed columns;
11. Try not to use foreign key foreign keys to protect referential integrity, can be implemented on the business side, the parent table and the child table operation will affect each other, reduce the availability;
12. Queries that do not use% leading, such as like "%xxx", do not use a reverse query, such as the not in/not like cannot use the index, resulting in a full table scan full table scan resulting in a reduced buffer pool utilization

Field design

1. Do not use text, blob types as much as possible. Deleting this value leaves a large "void" in the data table, and you can consider separating the BLOB or text column into a separate table
2. Stores exact floating-point numbers in decimal instead of float and double. The advantage of floating-point numbers relative to the fixed-point number is that floating-point numbers can represent a larger range of data in the case of certain lengths, and the disadvantage of floating-point numbers is that it can cause accuracy problems.
3. Convert a character to a number
4. Use tinyint instead of enum type
5. Field length as far as practical needs to allocate, do not arbitrarily allocate a large capacity VARCHAR (n), n means that the number of characters is not the number of bytes, such as VARCHAR (255), can store up to 255 characters, you need to choose the actual width of N. varchar (n), n as small as possible, because the maximum length of all varchar fields in a table is 65,535 bytes, when sorting and creating a temporary table class of memory operations, the length of n is used to request memory;
6. If possible, all fields are defined as NOT NULL
7. Stores a larger range of values by storing the same number of bytes as non-negative integers using unsigned. If the tinyint has a symbol of-128-127, unsigned 0-255
8. Use timestamp to store time. Because timestamp uses 4 bytes, DateTime uses 8 bytes, and timestamp has automatic assignment and auto-update features.
9. Using the int unsigned storage IPV4
10. Using varbinary to store case-sensitive variable-length strings
11. Prohibit storing plaintext passwords in the database

How system optimization is done-database optimization