Performance metrics

  • QPS
  • TPS
  • RT

Source of problem

  1. Slow logging, configuration parameters (MySQL5.7)
Slow_query_log = 1 long_query_time = 0.5 slow_query_log = missile. The logCopy the code
  1. tcpdump & wireshark
tcpdump -nvvv -i eth0 -c 20 'port 3306 and host x'
Copy the code
  1. Monitoring alarm

For example, SLA alarm TP99 on an interface

Where is the analysis slow

Performance profiling

SQL: Select name from TB1 where ID = 888 Server ->> Application (JDBC) : FetchSize Result set application (JDBC) ->> Server: Disconnect

Locate the cost of each step

set profiling=1;
show profiles;
show profile for query 1;
Copy the code

Profile has been deprecated in versions 5.7 and 8. The official recommendation is to use Performance Schema for Performance analysis, which will be added later

Execution plan Explain

  • Type: const > eq_ref > ref > ref_or_null > range > index > all
select * from tb1 where id = 1;
select a.name from tb1 a left join tb2 b where a.id = b.id;
select * from tb1 where name='san';
select * from tb1 where name='san' or name is null;
select * from tb1 where age > 20 and age < 30;
select name from tb1;
select * from tb1;
Copy the code
  • Rows: The number of rows to retrieve
  • Extra:
    • using where
    • using index
    • using index condition
    • using filesort
    • using temporary
    • using index for group by
    • using join buffer

Best practices:

  • select
    • Query as far as possible to go index, go overwrite index, go combined index of the left prefix
    • Avoid select *
    • Avoid large amount of data in the result set
    • Avoid refiltering and sorting fields for functional calculations
    • Avoiding type conversions
  • join
    • Small tables drive large tables
    • The join field is indexed and the type matches.
    • Preferentially optimize nested-loop inner loop
    • Avoid cross joins (Cartesian product)
  • limit

Notice the offset problem

Select * from tb1 limit 100000,10; Select * from tb1 t1,(select id from tb1 order by limit 100000,10) t2 where t1.id = t2.id limit 10; select * from tb1 where id > $max_id order by id limit 10;Copy the code
  • order by
  • group by
    • Loose index scanning, which uses indexes to query data for regrouping
    • Compact index scan. The difference is that in index scan, all index keys that meet the conditions are read, and then the data is grouped according to the read data
    • Create to avoid temporary table sorting
  • Count, count(*)/count(1) is recommended. Count (field) does not count null values and has low accuracy requirements. Use statistics of information_schama.table
  • DML
    • Large operations -> batch small operations to avoid lock contention and complex delays
    • Pay attention to the operation sequence to prevent Deal Lock
    • Truncate is recommended for all table data
  • DDL (merge operations on the same table, online DDL, third party online change table)

Table structure

  • Engine choices
  • Field design
    • The smaller the better, for example, an IP is converted to an unsigned int using the inet_aton function, which takes 4 bytes, and the phone number bigint > varchar
    • The simpler the better,
    • not null > null
    • tinyint > enum
    • Char,varchar > blob, text
  • Down the table
    • Vertical split: hot and cold separate, text\blob separate table
    • Horizontal expansion, the introduction of middleware sub – library sub – table
  • The index
    • You are advised to add the ID as the primary key
    • You are not advised to use MD5 or unordered string as the primary key because data insertion causes frequent paging, occupies large space, and poor query performance
    • Fields that appear in the where\join association field \ ORDER by\group BY are suitable for indexing
    • Do not create indexes for fields that are frequently updated, which affects performance
    • Indexes are not recommended for columns with low cardinality
    • Composite index, left to right by distinction
    • The number of combined index fields is not recommended to exceed 5
    • It is recommended to use a short index and a prefix index to intercept long strings, which can optimize the query and avoid large indexes
    • You are not advised to create more than five indexes for a single table
    • Do not build redundant indexes

code

  • Use precompiled statements to prevent SQL injection and improve performance

Database Configuration (primary/secondary, parameters)

  • Reading and writing separation
  • synchronously

Host configuration

Improve configuration, disk capacity expansion, double library expansion, etc

The network status

Analyzing network Time

Gal cache

Query the situation where the class occupies a large proportion, introduce Ehcache + Redis, etc., and do their own good transaction operations