Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the optimization schemes of mysql". In the daily operation, I believe that many people have doubts about the optimization scheme of mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "what are the optimization schemes of mysql?" Next, please follow the editor to study!

1. Field selection optimization

Table field not null, because the null value is difficult to query and optimize and takes up additional index space, the default number 0 is recommended.

Fields of data status types, such as status, type, and so on, try not to define negative numbers, such as-1. Because this can be added with UNSIGNED, the numerical capacity will be doubled.

If possible, replace INT with TINYINT, SMALLINT, etc., and try not to use BIGINT, because it takes up less space.

Fields of string types take up more space than numeric types, so try to use integers instead of strings, and many scenarios can be replaced by integers through coding logic.

Do not set the string type length arbitrarily, keep it as small as possible on the premise of satisfying the business, and use integers to store IP.

Do not have too many fields in a single table, and it is recommended that it be less than 20.

Reserve in advance for predictable fields, because the larger the amount of data, the more time it takes to modify the data structure.

two。 Index design optimization

Index, space for time optimization strategy, basically according to business needs to design the index, enough to cope with millions of data, get into the habit of using explain, about explain can also access: explain makes your sql write more reliable and know more.

A common sense: the more indexes, the better. Indexes can degrade data writing performance.

The length of the index field is as short as possible, which can save a lot of index space.

Cancel foreign keys, can be handed over to the program to constrain, better performance.

The compound index matches the leftmost column rule, the order of the index is consistent with the query condition, and the unnecessary single-column index is removed as far as possible.

Fields with less value distribution (less repetition) are not suitable for indexing, such as gender, where there are only two or three values.

It is recommended to add an index to the fields that need to be sorted, because the index will be sorted and can improve query performance.

String fields are indexed with prefixes instead of full-field indexes, which can significantly reduce index space.

3. Query sentence optimization

Try to use short queries instead of complex inline queries.

Query does not use select *. Try to query indexed fields to avoid returning to the table.

Try to use limit to limit the number of queries.

The query field falls on the index as far as possible, especially the composite index, so we need to pay more attention to the leftmost prefix matching.

Splitting large delete / insert operations will, on the one hand, lock tables and affect other business operations, and on the other hand, MySQL also has restrictions on the length of sql.

It is not recommended to use MySQL functions, calculations, etc., can be handled by the program first, from some of the points mentioned above, you will find that those that can be handled by the program should not transfer the pressure to the database. Because most of the server performance bottlenecks are on the database.

Query count, performance: count (1) = count (*) > count (primary key) > count (other fields).

Query operators can use between without in, and can use in without or.

Avoid using operators such as! = or < >, IS NULL or IS NOT NULL, IN, NOT IN, and so on, because these queries cannot use indexes.

Sql should be as simple as possible, use less join, and more than two join are not recommended.

Tens of millions of data

At this stage of the amount of data, the data itself has a great value, data in addition to meet the conventional business needs, there will be some data analysis needs. At this time, the variability of the data is not high, and basically we will not consider modifying the original structure. We will generally consider optimizing it from three aspects: partition, sub-table and sub-database:

1. Zoning

Partitioning is a horizontal partition in which a database divides a table into smaller, more manageable parts according to certain rules. It is completely transparent to the application and does not affect the business logic of the application, that is, there is no need to modify the code. Therefore, it can save more data, query, delete and support operation by partition, so as to achieve the purpose of optimization. If you are considering partitioning, you can prepare in advance to avoid some of the following restrictions:

A table can only have a maximum of 1024 partitions (8192 partitions are supported after mysql5.6). But when you are in practice, it is best not to open more than 100 partitions at a time, because opening partitions is also a waste of time.

If there is a primary key or unique index column in the partition field, then all primary key columns and unique index columns must be included, and if there is a primary key or unique index in the table, the partition key must be a primary key or unique index. For this point, the author adds that the author divides the zone according to the day where the created_at is located, so the table is as follows

CREATE TABLE `sms_record_ hash` (

. Omit field definition

PRIMARY KEY (`id`, `created_ at`)

.

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

/ * 50100 PARTITION BY HASH (DAYOFYEAR (created_at))

PARTITIONS 366 * /

Foreign key constraints cannot be used in partitioned tables.

A null value will invalidate partition filtering, which will be placed in the default partition. Please do not let the partition field appear NULL.

All partitions must use the same storage engine.

two。 Sub-table

The table is divided into horizontal table and vertical table.

A horizontal split table is split into small tables with the same data structure, such as table1, table2. To relieve the pressure of reading and writing to the database

The vertical table separates some fields to form a new table, and the data structure of each table is different, which can optimize the situation of locking tables with high concurrency.

It is conceivable that if the sub-table, the logic of the program needs to be modified, so it is generally at the beginning of the project, foresee a large amount of data, will consider the sub-table. Sub-tables are not recommended in the later stage, and the cost is very high.

3. Sub-library

The sub-database is generally the master-slave mode, in which the master node of a database server is copied to multiple databases of one or more slave nodes, the master database is responsible for write operations, and the slave database is responsible for read operations, so as to achieve master-slave separation, high availability, data backup and other optimization purposes.

Of course, the master-slave mode will also have some defects, such as the master-slave synchronization delay, the problems caused by the large binlog file, and so on.

At this point, the study of "what are the optimization schemes of mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.