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

This article mainly introduces what mysql optimization methods have, which has a certain reference value, friends who need can refer to it. Let's take a look at the mysql optimization method with me.

I. overall optimization ideas

First of all, build a script to observe the number of queries, connections and other data, determine the environmental reasons and internal SQL implementation reasons, and then do specific processing according to the specific reasons.

Second, observe the status of the build script

Mysqladmin-uroot-p ext\ G

This command can get information such as the number of current queries, regularly poll and redirect the results into text, and then process them into a chart.

III. Countermeasures

1. If the query is slow in regularity, consider the cache avalanche problem.

For this problem, it is only necessary to deal with the cache failure time as not close to the same time at the same time, the failure time as discretized as possible, or concentrated until midnight.

two。 If the irregular query is slow, consider the lack of optimization in the design.

Treatment method:

A: start the profiling record query operation and get the execution details of the statement

Show variables like'% profiling%';set profiling=on;select count (*) from user; show profiles;show profile for query 1 > 000060 | | Executing hook on transaction | 0.000004 | | starting | 0.000049 | | checking permissions | 0.000007 | Opening tables | 0.000192 | init | 0.000006 | System lock | 0.000009 | | optimizing | 0.000005 | statistics | 0. 000014 | | preparing | 0.000017 | | executing | 0.001111 | | end | 0.000006 | query end | 0.000003 | waiting for handler commit | 0.000015 | closing tables | 0.000011 | | freeing items | 0.000085 | | cleaning up | | 0.000008 | +-+-+ |

B: use explain to check statement execution, index usage, scan range, etc.

Mysql > explain select count (*) from goods\ gateway * 1. Row * * id: 1 select_type: SIMPLE table: goods partitions: NULL type: index possible_keys: NULL key: gid key_len: 5 Ref: NULL rows: 3 filtered: 100.00 Extra: Using index

C: related optimization techniques

Optimization of tables and selection of column types

Column selection principles:

1: field type priority integer > date,time > char,varchar > blob

Reason: integer, time operation is fast, save space

Char/varchar needs to consider the conversion of the character set and the proofreading set when sorting, which is slow.

Blob cannot use memory temporary tables

2: enough is enough, don't be generous (e.g. smallint,varchar (N))

Reason: large fields waste memory and affect speed

The contents stored in varchar (10) and varchar (300) are the same, but varchar (300) costs more memory when looking up tables.

3: try to avoid using NULL

Reason: NULL is not good for indexing and should be marked with special bytes.

Actually takes up more space on the disk.

Index optimization strategy

1. Index type

1.1 B-tree index (sorted quick lookup structure)

Note: in Myisam,innodb, B-tree index is used by default

1.2 hash Index

In memory table, the default is hash index, and the theoretical query time review degree of hash is O (1).

Question: since hash indexes are so efficient, why not use them all?

The result calculated by the a.hash function is random. If the data is placed on the disk, taking the primary key as id, then as the id grows, the corresponding rows of id are randomly placed on the disk.

b. Unable to optimize range query

c. Prefix indexes cannot be utilized, for example, in b-tree, where the value of the field column is "helloworld", index queries xx=hello/xx=helloworld can use indexes (left prefix indexes), but hash indexes cannot, because hash (hello) is not associated with hash (helloworld).

d. Sorting cannot be optimized.

e. You must go back to the row, get the data location through the index, and go back to the table to get the data.

Common misunderstandings of 2.b-tree Index

2.1 add indexes on columns commonly used in where conditions

Example: where cat_id=3 and price > 100; / / query the third column, goods over 100 yuan

Error: indexes are added to both cat_id and price. In fact, only one index can be used, they are independent indexes.

2.2 after indexing on multiple columns, the index will play a role in which column is queried.

2.2 after indexing on multiple columns, the index will play a role in which column is queried.

Correct solution: on multi-column indexes, the index plays a role and needs to meet the requirements of the left prefix (hierarchical index).

Take the example of index (a _

Whether the sentence index plays a role where axiom 3 is where axiom 3 and baggage 5 is where axiom 3 and baggage 5 and cantilever 4 is where bread3 or where cantilever 4 no where axiom 3 and cantilever 4 a can play an indexing role, c column can not where axiom 3 and b > 10 and cantilever 7 a can play an indexing role, column c cannot

High performance indexing strategy

1. For innodb, because there are data files under the node, the splitting of nodes will become slower, for the primary key of innodb, try to use integers, and it is an incremental integer.

two。 The length of the index directly affects the size of the index file, the speed of addition, deletion and modification, and indirectly affects the query speed (taking up a lot of memory).

3. Index the values in the column by intercepting them from left to right.

a. The shorter the cut, the higher the repetition, the smaller the distinction, the worse the indexing effect.

b. The longer the cut, the higher the differentiation, but the larger the index file affects the speed.

So try to find a balance in length to maximize performance, and the usual trick is to intercept different lengths to test index differentiation.

Discrimination test:

Select count (distinct left (word, 1) / count (*) from table

After the test is completed, the index can be established according to the optimal length obtained by the test.

Alter table table_name add index word (word (4))

Ideal index

1. Query frequently

two。 High degree of differentiation

3. Small length

4. Try to cover common query fields

About what mysql optimization methods are shared here, I hope that the above content can have a certain reference value for everyone, you can learn to apply. If you like this article, you might as well share it for more people to see.

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.