The original title: what I did on MySQL to optimize the original links: zhoupq.com/ on MySQL – I do… Please indicate the source of reprint

This article describes how I optimized MySQL for my project over the course of a year. With certain subjectivity and limitations, please support at the same time, don’t hesitate to give advice.

　　

The installation


  • Developer Machine allocates minimum subsystem resources for MySQL
  • The Server Machine allocates a proportion of system resources to MySQL
  • Dedicated MySQL Server Machine Allocates all system resources for MySQL

If you have the same misfortune as me, not only choose Win OS as the server system, but also choose Developer Machine(development Machine), brother hug, don’t cry, reinstall. It doesn’t matter why this happened. You have to switch to a Dedicated MySQL Server Machine.

After reinstalling and switching, you will find that the MySQL installed before must be fake.

A primary key














Since the increase








UUID



























Database auto-growth primary key and -UUID



MySQL > select * from user where ID > UUID;





The data type

The length of the


NULL


From a development AND maintenance perspective, if you are not sure whether the column is NULL, then in SQL, you must add “AND tab_NAME! = NULL AND tab_NAME ! = “”, easy to ignore, the more code, the greater the probability of error.

The index


I made three optimizations for the index:

  • Don’t abuse indexes
  • Left-most prefix index
  • The prefix index

Don’t abuse indexes


Left-most prefix index


For example, abcd consists of fields A, B, C, and D. The SQL conditions are as follows:

  1. a=1 and b =2 and c=3 and d=4
  2. a=1 and b =2 and d=4 and c=3
  3. a=1 and b =2 and c>=3 and d=4

1 uses index ABcd, 2 uses index ABd, and 3 uses index ABC. The order of conditions is important. Stand in the back with a big face like a selfie. Sorry, the second point above uses the same index “abcd” as the first point. Use EXPLAIN tools to analyze:

Mysql > EXPLAIN SELECT * FROM mysql> EXPLAIN SELECT * FROMtest t WHERE t.a = 'q' AND t.b = 'w' AND t.c = 'e' AND t.d = 'f'; +----+-------------+-------+------+---------------+------+---------+-------------------------+------+------------------- -------+ | id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------------------+------+------------------- -------+ | 1 | SIMPLE | t | ref | name | name | 360 | const,const,const,const | 1 | Usingwhere; Using index | +----+-------------+-------+------+---------------+------+---------+-------------------------+------+------------------- -------+ 1 rowin set

mysql> EXPLAIN SELECT * FROM test t WHERE t.a = 'q' AND t.b = 'w' AND t.d = 'f' AND t.c = 'e'; +----+-------------+-------+------+---------------+------+---------+-------------------------+------+------------------- -------+ | id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------------------+------+------------------- -------+ | 1 | SIMPLE | t | ref | name | name | 360 | const,const,const,const | 1 | Usingwhere; Using index | +----+-------------+-------+------+---------------+------+---------+-------------------------+------+------------------- -------+ 1 rowin setCopy the code

The prefix index


Check out my other blog post: MySQL High Performance Index prefix Index

Multi-table union query


Multiple LEFT Joins will definitely not work. Even if there is an index, it is easy to cause a full table scan. In order to reduce the probability of this situation, I generally adopt two methods:

  • The paradigm
  • A temporary table

The paradigm

The measure of how good a DBA is is how anti-paradigm he is. – zhihu

Let’s say I want to correlate the number of attributes under each date with the dates in table A and table B. I can add A column to table A to store “quantities”, which violates the paradigm, but improves performance. I think it’s a good deal.

Standardization is to serve technology, and technology is to serve business. Normalization is the routine, which can guarantee no mistakes, but can not solve special problems, special problems also need special treatment.

A temporary table


  • Benefits Speed up query
  • disadvantages
    • At some point (early morning) the DATABASE I/O is too high
    • There may be exceptions, do a good job of transaction management, let it roll back, re-execute, if there is a problem, you need human intervention
    • Data accuracy is delayed by one day, suitable for non-sensitive business

The above optimizations are for databases only. Caching (level 1 cache, level 2 cache) is the responsibility of the persistence layer framework and is outside the scope of this document.