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

Index optimization of MYSQL performance failure optimization sharp weapon

From a performance point of view, 80% of the performance problems are designed. From the statistical probability of performance test problems, 80% of the performance problems come from problems caused by data processing, in which SQL syntax problems account for a high specific price, and most of these problems are caused by problems in the way data is retrieved, such as full table scanning, multi-table association design, large table usage and so on. The optimization methods in this kind of problems are: syntax disassembly, optimizing table join mode, table physical partition, logical partition, appropriate use of database parameters, rational use of different types of indexes, optimization of hardware resources, optimization of business requirements and so on to improve the retrieval data rate.

For testers, it is mainly through the design of reasonable test scenarios and reasonable monitoring weapons, so that problems can be quickly found and possible failures in future production can be solved in the cradle, while the use of some indexes is reasonable. Only through a variety of debugging attempts in the testing process can we know how to optimize the most reasonable, and at this time, we are testers. We can also learn how to build an appropriate index in order to achieve the best results, which we as testers can personally experience optimization, of course, we have to understand the principle of database index use in order to know how to optimize, after all, we find that SQL problems are easy, it is difficult to solve problems, the rare reason is that we do not understand how they work.

Well, as testers, since we have to do how to test in order to test the problem, and then to diagnose and analyze the problem and optimize the problem, from the point of view of testing, we need to understand the simplicity of test scheme design, test model analysis, test strategy customization, monitoring model design, test case design, test result analysis, test report compilation and so on. We need to learn architecture principle, operating system principle, database principle, JVM principle and so on in order to understand their underlying principles in order to find problems more effectively, provide optimization solutions, find problems is technical behavior, solve optimization problems is artistic behavior.

For specific performance testing implementation process, please refer to: https://blog.51cto.com/372550/2068876

Index design requirements:

1. The column suitable for the index is the column that appears in the where clause, or the column specified in the join clause

2. The cardinality of the table is small and the indexing effect is poor, so it is not necessary to establish an index in this column.

3. Indexes are not recommended for single table types, such as gender tables, men and women.

4. Don't over-index. The index requires extra disk space and degrades the performance of write operations. When you modify the contents of the table, the index will be updated or even refactored, and the more index columns, the longer it will take.

5 、. The amount of data filtered is relatively small, generally speaking, 40% basically does not take the index (full table scan)

6. When calculating the fields of the index, you must calculate them on the right side of the operator. That is, to_char (oc.create_date, 'yyyyMMdd') is useless-- you can use functional indexes such as oracle, etc.

Then this article mainly talks about the principle and efficiency of MYSQL index.

What is an index? Why build an index?

The index is used to quickly find the row with a specific value in a column, without using the index, MySQL must read the entire table from the first record until finding out the relevant row, the larger the table, the more time it takes to query the data. If the query column in the table has an index, MySQL can quickly reach a location to search the data file without having to view all the data, then it will save a lot of time.

Just like our Chinese dictionary, if you don't know how to pronounce and explain a word, you need to look it up page by page, line by line, until you find the word you want. You can imagine how slow this is.

For example, this user user table, there are 10 million items of data, need to find Zhang Sanfeng, live in Wudang Mountain, the phone number for 15900xxxxxx1 personal information, if not using the index, there need to start from the first record in the table a stroke of traversal query, straight to find the main piece of information, if the first stroke is that may be fast, if in the table last stroke? If there is an index at this time, then Zhang Zijie will be able to find out the corresponding data quickly without having to traverse according to his name and phone number.

Now that we know that query efficiency can be improved through indexes, we need to understand that there are several storage types of indexes: BTREE, HASH.

To view the index type method:

You can use SHOW INDEX FROM table_name; to view index details:

Table: the table that creates the index

Non_unique: indicates that the index is not unique, 1 represents a non-unique index, and 0 represents a unique index, which means whether the index is unique or not

Key_name: the index name Seq_in_index indicates the position of the field in the index. In the case of a single-column index, the value is 1, and the combined index is the order of each field in the index definition (this only needs to know that the value of the single-column index is 1, and the combined index is something else).

Column_name: indicates the column field that defines the index, and Sub_part: indicates the length of the index.

Null: indicates whether the field can be null

Index_type: indicates the type of index

Primary key index PRIMARY KEY

It is a special unique index and no null values are allowed. Generally, the primary key index is created at the same time when the table is created. Note: a table can have only one primary key.

Unique index UNIQUE

The value of a unique index column must be unique, but null values are allowed. If it is a combined index, the combination of column values must be unique

-create a unique index

CREATE UNIQUE INDEX indexName ON table (column (length))

-modify the table structure

ALTER TABLE table_name ADD UNIQUE indexName ON (column (length))

General index INDEX

This is the most basic index, and it has no restrictions.

-create an index directly

CREATE INDEX index_name ON table (column (length))

-add indexes by modifying the structure of the table

ALTER TABLE table_name ADD INDEX index_name ON (column (length))

-Delete the index

DROP INDEX index_name ON table

Full text Index (FULLTEXT)

Full-text indexing, which can only be used on the MyISAM engine, and can only be used on CHAR,VARCHAR,TEXT type fields

-add full-text index

CREATE FULLTEXT INDEX index_content ON article (content)

-modify the table structure to add a full-text index

ALTER TABLE article ADD FULLTEXT index_content (content)

Combinatorial index

An index created on a combination of multiple fields in a table is used only if the left field of these fields is used in the query criteria, following the leftmost prefix set when using the combined index.

Case description

Specific index usage, case description such as: https://blog.51cto.com/372550/2089965 Magi MySQL database service CPU high problem analysis and optimization

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.