Share Mysql optimization ideas

1. Overall optimization idea

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

Recommended: "mysql video tutorial"

2. Build script observation status

mysqladmin -uroot -p  ext \G

This command can obtain information such as the current number of queries, poll regularly and redirect the results to text, and then process them into charts.

3. Solutions

1. If queries appear regularly and slowly, consider the cache avalanche problem.

For this problem, we only need to deal with the cache expiration time so that it does not expire at similar times at the same time. The expiration time should be as discrete as possible, or concentrated until midnight.

2. If the non-regular query is slow, consider the design is not optimized

Processing method:

a: Enable profiling to record query operations and obtain statement execution details

show variables like '%profiling%';
set profiling=on;
select count(*) from user; 
show profiles;
show profile for query 1;
>>>
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.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 view statement execution, index usage, scan range, etc.

mysql> explain select count(*) from goods \G
*************************** 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

Table optimization and column type selection

Column selection principles:

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

Reason: Integer type, time operation is fast and saves space

char/varchar needs to consider the conversion of the character set and the proofreading set during sorting, and is slow

blob cannot use the memory temporary table

2: Just use enough, don’t be generous (such as smallint, varchar(N))

Reason: Large fields waste memory and affect speed

Use varchar(10), varchar( 300) stores the same content, but when querying tables, varchar(300) takes more memory

3: Try to avoid using NULL

Reason: NULL is not conducive to indexing, so use Special bytes to mark.

The space occupied on the disk is actually larger

Index optimization strategy

1. Index type

1.1 B-tree index (sorted fast search structure)

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

1.2 hash index

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

Question: Since hash index is so efficient, why not use it?

The result of a.hash function calculation is random. If the data is placed on the disk, taking the primary key as id as an example, then as the id grows, the row corresponding to the id is randomly placed on the disk. .

b. Unable to optimize range queries

c. Unable to use prefix index, for example, in b-tree, the value of the field column is "helloworld", and the index query xx=hello/xx =helloworld can use the index (left prefix index), but the hash index cannot do it, because hash(hello) and hash(helloworld) are not related.

d. Sorting cannot be optimized either

e. Rows must be returned, the data location must be obtained through the index, and the data must be returned to the table.

2.b-tree Common misunderstandings about indexes

2.1 Add indexes to columns commonly used in where conditions

Example: where cat_id=3 and price>100; //Query the third column, more than 100 yuan The product

is incorrect: both cat_id and price are indexed. In fact, only one index can be used, they are all independent indexes.

2.2 After creating an index on multiple columns, the index will work no matter which column is queried

2.2 Creating an index on multiple columns After indexing, no matter which column is queried, the index will play a role.

Correct answer: For a multi-column index to work, the index needs to meet the left prefix requirement (layered index)

With index(a, b, c) For example:

语句 索引是否发挥作用
where a=3 是
where a=3 and b=5 是
where a=3 and b=5 and c=4 是
where b=3 or where c=4 否
where a=3 and c=4 a列能发挥索引作用，c列不能
where a=3 and b>10 and c=7 a,b能发挥索引作用，c列不能

High performance index strategy

1. For innodb, because there are data files under the node, the split of the node will It becomes slower. For the primary key of innodb, try to use integer type, and it is an increasing integer type.

2. The length of the index directly affects the size of the index file, affects the speed of additions, deletions and modifications, and indirectly affects the query speed (taking up more memory).

3. For the values ​​in the column, intercept parts from left to right to build an index.

a. The shorter the truncation, the higher the degree of repetition, the smaller the distinction, and the worse the indexing effect

b. The longer the truncation, although the discrimination is improved, the index file becomes larger Affects speed

So try to find a balance point in length to maximize performance. Common method: intercept different lengths to test index distinction

Discrimination test:

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

After the test is completed, you can create an index based on the optimal length obtained from the test

alter table table_name add index word(word(4));

Ideal index

1. Frequent queries

2. Discrimination High

3.Small length

4.Try to cover common query fields

The above is the detailed content of Share Mysql optimization ideas. For more information, please follow other related articles on the PHP Chinese website!