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

What is the optimization scheme of MySql database? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

I. Overview of the programme

Optimize existing mysql databases.

Second, the advantages and disadvantages of the scheme

Advantages: the existing business is not affected, the source program does not need to modify the code, and the cost is the lowest.

Disadvantages: with an upper limit, it will not work if the amount of data exceeds 100 million.

III. Detailed rules for the implementation of the programme

1. Performance should be considered when designing databases and creating tables

When designing tables, you should pay attention to:

(1) the table field avoids null values, which are difficult to query and optimize and take up additional index space. It is recommended that the default number 0 replaces null.

(2) try to use INT instead of BIGINT. If it is non-negative, add UNSIGNED (so the numerical capacity will be doubled). Of course, it is better to use TINYINT, SMALLINT and MEDIUM_INT.

(3) replace string types with enumerations or integers

(4) try to use TIMESTAMP instead of DATETIME

(5) do not have too many fields in a single table. It is recommended that it be less than 20.

(6) use integers to save IP

two。 Create an index

(1) the more indexes, the better. To create targeted indexes according to the query, consider indexing the columns involved in the WHERE and ORDER BY commands. You can check whether indexes or full table scans are used according to EXPLAIN.

(2) the null judgment of the field in the WHERE clause should be avoided as far as possible, otherwise it will cause the engine to give up using the index and do a full table scan.

(3) fields with sparse value distribution are not suitable for indexing, such as "gender", which has only two or three values.

(4) character fields are indexed with prefixes only.

(5) character fields are best not to be primary keys.

(6) without foreign keys, constraints are guaranteed by the program.

(7) avoid using UNIQUE as much as possible, and be constrained by the program.

(8) keep the order of ideas consistent with the query conditions when using multi-column indexes, and delete unnecessary single-column indexes.

In short, use the appropriate data type and choose the appropriate index

Select the appropriate data type

(1) use the smallest data type that can be saved, integer < date,time < char,varchar < blob

(2) using simple data types, integers are less expensive than character processing because string comparisons are more complex. For example, the int type stores the time type, and the bigint type converts the ip function

(3) using a reasonable field attribute length, a fixed-length table will be faster. Use enum, char instead of varchar

(4) use not null to define fields whenever possible

(5) use text as little as possible, and it is best to divide the table if you have to use it.

Select the appropriate index column

(1) frequently queried columns, columns that appear in the where,group by,order by,on clause

(2) columns that appear in where condition =, between,in, and like string + wildcard (%)

(3) for columns with small length, the smaller the index field, the better, because the storage unit of the database is page, and the more data can be stored in a page, the better.

(4) columns with large dispersion (more different values) are placed in front of the federated index. Look at the discreteness by counting different column values. The larger the count, the higher the discretization.

The compilation of 3.sql needs to be optimized.

(1) use limit to limit the records of query results

(2) avoid select * and list the fields you need to find

(3) use join to replace subqueries

(4) split large delete or insert statements

(5) you can find the slow SQL by opening the slow query log.

(6) No column operation: SELECT id WHERE age + 1 = 10. Any column operation will result in a table scan, including database tutorial functions, evaluation expressions, and so on. When querying, move the operation to the right of the equal sign as much as possible.

(7) sql statements are as simple as possible: a sql can only operate on one cpu; large statements break down small statements to reduce lock time; a large sql can block the whole library

(8) the efficiency of rewriting OR to IN:OR is n, the efficiency of IN is log (n), and the number of in is recommended to be less than 200. it is recommended that no functions or triggers are used in the application program.

(10) avoid% xxx queries

(11) use less JOIN

(12) compare the same type, such as the ratio of '123' to '123', 123 to 123

(13) avoid using the! = or operator in the WHERE clause as much as possible, otherwise the engine will abandon the use of the index and perform a full table scan

(14) for continuous values, use BETWEEN instead of IN:SELECT id FROM t WHERE num BETWEEN 1 AND 5

(15) Don't take the whole table for list data, use LIMIT to page, and don't have too many pages.

4. Zoning

The partition introduced by MySQL in version 5.1 is a simple horizontal split. Users need to add partition parameters when creating the table, which is transparent to the application without modifying the code.

For users, the partition table is an independent logical table, but the underlying layer is composed of multiple physical child tables. The code for partitioning is actually encapsulated by the objects of a group of underlying tables, but for the SQL layer, it is a black box that completely encapsulates the underlying layer. The way MySQL implements partitioning also means that indexes are also defined according to partitioned child tables, and there is no global index.

The user's SQL statement needs to be optimized for the partition table, and the columns with partition conditions should be included in the SQL condition, so that the query can be located on a small number of partitions, otherwise all partitions will be scanned. You can use EXPLAIN PARTITIONS to see that a certain SQL statement will fall on those partitions for SQL optimization.

The benefits of zoning are:

(1) A single table can store more data.

(2) the data of the partition table is easier to maintain. You can delete a large number of data in batches through the whole partition, or you can add new partitions to support newly inserted data. In addition, a separate partition can be optimized, checked, repaired, etc.

(3) some queries can only fall on a few partitions from the query conditions, and the speed will be very fast.

(4) the data of partition table can also be distributed on different physical devices, so it is funny to make use of multiple hardware devices.

(5) Partition table dependency can be used to avoid some special bottlenecks, such as mutually exclusive access of InnoDB single index and inode lock competition of ext3 file system.

(6) individual partitions can be backed up and restored

Limitations and disadvantages of partitions:

(1) A table can only have a maximum of 1024 partitions

(2) 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.

(3) Partition tables cannot be constrained by foreign keys.

(4) the NULL value invalidates partition filtering.

(5) all partitions must use the same storage engine

Type of partition:

(1) RANGE partition: assigns multiple rows to a partition based on column values belonging to a given contiguous interval

(2) LIST partitioning: similar to partitioning by RANGE, except that LIST partitions are selected based on column values matching a value in a set of discrete values

(3) HASH partition: a partition selected based on the return value of a user-defined expression that uses the column values of the rows to be inserted into the table. This function can contain any valid expression in MySQL that produces a non-negative integer value

(4) KEY partitioning: similar to partitioning by HASH, except that KEY partitions only support calculating one or more columns, and the MySQL server provides its own hash function. One or more columns must contain integer values

(5) for more information about the concept of mysql partition, please consult the official documentation.

5. Sub-table

Sub-table is a large table, optimized according to the above process, or the query is stuck, then divide the table into multiple tables, divide a query into multiple queries, and then return the results to the user.

The split table is divided into vertical split and horizontal split, usually with a field as the split item. For example, split the id field into 100 tables: the table name is tableName_id0

But: the sub-table needs to modify the source code, which will bring a lot of work to the development and greatly increase the development cost, so: it is only suitable to consider the existence of a large amount of data at the initial stage of development, and do a good job of sub-table processing. It is not suitable for the application to go online and then make changes, the cost is too high! And choose this plan, not as low as the cost of choosing the second and third option I provide! Therefore, it is not recommended.

6. Sub-library

Divide a database into multiple, it is recommended to do a read-write separation on the line, the real sub-library will also bring a lot of development costs, the loss outweighs the gain! It is not recommended.

This scheme is a personal comprehensive summary, for reference only!

The answer to the question about how to optimize the MySql database is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.