This series of articles refer to Ding Qi’s “MySQL Actual Combat 45 lecture”, the role is to improve themselves, deepen understanding and thinking, but also hope to interact with you to learn!

Technology is the crystallization of the wisdom of skillful craftsmen, at the beginning of the in-depth details are easy to be complicated and sophisticated skills confused, so in order to learn a technology, I will first of all have a general understanding of the outline, follow the outline, even if not touch a three to five to eight million also know that this is a mahjong.

Start with the simplest query to see how much water it waded in and whose shoes it wet.

mysql> select * from T where ID=10;Copy the code

Attached flow chart

The above query SQL, according to the flow chart, I do a simple comb, the overall outline from top to bottom is roughly divided into two layers, Server layer and storage engine layer, along the process, the first step is the client through the connector to connect to the MySQL Server; After the connection, the analyzer is used to determine whether the SQL syntax is correct. Then determine whether there is data in the query cache, if there is directly return; After the optimizer to optimize SQL, like a nanny also help you sort out; Finally by the executor to execute SQL, through the storage engine to query data, the whole process even if the end.

A brief introduction to components

  • MySQL is divided into Sever layer and storage engine layer.

  • Connector: Authenticates the identity and grants permissions to the client after establishing a TCP connection with the client

  • Query cache: If a hit is returned directly, the table is updated. Then the cache is invalid. It is not suitable to enable query cache for frequently changed tables.

  • Analyzer: Analyzes SQL keywords and whether they are spelled correctly

  • Optimizer: Optimize SQL to decide which index to go

  • Executor: Permission judgment is made here. Then open the table and execute the SQL as follows (without indexes)

    • A. Call InnoDB engine interface to get the first row of the table and judge the value; Traverse take; Returns the result wrapped as a recordset
    • B. In the slow query log, you can see a ROws_EXAMINED field that shows how many rows were scanned during the execution of this statement.

After class extension

Ask questions

  1. Where is the query cache located and how do I control the size of the query cache interval?
  2. How do I view slow query logs?
  3. How do I grant permissions to a user?
  4. How to optimize SQL to decide which index to go?

answer

1. Where is the query cache placed and how to control the size of the query cache interval?

MySQL’s query cache is stored entirely in memory.

Show variables like ‘%query_cache%’;

(1) Query_cache_type represents the cache type,OFF: query cache is disabled. ON: query cache is enabled.DEMAND indicates user-defined query cache

(2) query_cache_limit Indicates the maximum amount of SQL data for a single query

(3) query_cache_MIN_res_unit indicates the minimum unit of query cache

(4) query_cache_size specifies the size of the query cache

(5) query_cache_wLOCK_invalidate indicates whether the query cache supports write locks. OFF indicates that write locks are not considered when data is read. ON indicates that data is supported, that is, data is blocked by write locks

Query cache variables can only be set in the my.ini file

2. How do I view slow query logs?

Slow query logs: show variables like ‘%slow_query_log%’;

usingshow variables like '%long_query_time%';Online query Parameter Option Configuration value

Set global slow_query_log=1; Setting Parameter Configuration

The important parameters are as follows:

  • Slow_query_log =1 ## indicates whether slow query is enabled, and 1 indicates that slow query is enabled
  • Long_query_time = 0.5
  • Min_examined_row_limit =100 SQL examinations whose scan rows are smaller than this parameter are not recorded in the slow query log.
  • slow_query_log_file=/mysql/data/mysql_slow.log

3. How do I assign permissions to users?

Create user: create user identified by ‘password ‘;

Grant permission to a user on the database. Table to ‘user’ @ ‘host name ‘;

a. grant all on *.* to ‘xiaogang’@’%’; ## Grant all permissions to xiaogang

b. grant select on temp.temp1 to ‘xiaogang’@’%’; Mysql > grant temp. Temp1 to xiaogang;

4. How to optimize SQL to decide which index to go?

Query optimization is very similar to the concept of map navigation in that we usually just enter the desired result (destination) and the optimizer is responsible for finding the most efficient way to do it (best route).

The optimizer’s working process can be semantically divided into four stages:

A. Logical transformation: including negation elimination, equivalent transfer and constant transfer, constant expression evaluation, outer join conversion to inner join, sub-query conversion, view combination, etc.; B. Optimization preparation, such as index ref and range access method analysis, query condition fan out (filtered record number) analysis, constant scale detection; C. Cost-based optimization, including access method and connection order selection, etc.; D. Execution plan improvements, such as push down for table conditions, access method adjustments, sort avoidance, and push down for index conditions.

We practice

To enable optimizer tracing: SET Optimizer_trace =”enabled=on”;

TODOTODO is too deep!! First, empty.

Aqi use summary

-- Log in to the client
mysql -h$ip -P$port -u$user -p
-- Check the connection
show processlist;
With query_cache_type set to DEMAND, SQL_CACHE can be used to explicitly specify statements that use the query cache
mysql> select SQL_CACHE * from T where ID=10;-- Check whether slow query logs are enabled
show variables like '%slow_query_log%';
Copy the code

Rivers and lakes are worldly wisdom, leave a small praise, so that I can also pay attention to you.