The connector

Connectors establish connections to clients, obtain permissions, and maintain and manage connections

mysql -h$ip -P$port -u$user -p
Copy the code

After typing the command, you need to enter the password in the interactive dialogue. The password can also be written directly after -p (without Spaces) on the command line

mysql> show processlist;
Copy the code

The “Sleep” line in the Command column indicates that there is now a free connection in the system.

If the client is inactive for too long, the connector automatically disconnects it. This time is controlled by the wait_timeout parameter, which defaults to 8 (28800 seconds) hours

show variables like 'wait_%'
Copy the code

analyzer

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

Analyzers include lexical analysis, syntax analysis:

So-called lexical analysis identifies keywords as tokens,

Lexical analysis is to identify one word after another, and grammatical analysis is to identify the syntax structure of the program on the basis of lexical analysis, namely AST abstract syntax tree

For example, MySQL knows from the keyword “select” that you typed, which is a query statement. It also recognizes the string “T” as “table name T” and the string “ID” as “column ID”

You can feel syntax tree links

The optimizer

The optimizer determines which index to use when there are multiple indexes in the table. Or determine the order in which tables are joined when a statement has multiple joins

actuator

Call the engine interface to read and write data and process the acquired data

InnoDB index model

InnoDB uses a B+ tree index model, so data is stored in a B+ tree

create table ltq(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;
Copy the code

Insert a few values and look at the index structure:

Primary key index leaves store entire rows of data,

B+tree data is stored in leaf nodes,

Index non-leaf nodes

By default, each data page is 16KB. By placing data on leaf nodes, more index values can be stored, reducing levels and speeding up queries

Leaf node contents that are not primary key indexes are primary key values

Each new index creates a B+ tree

See the B+tree add structure here

Select * from table_name where table_name = 1;

If you understand the data structure, it is easy to know what is called back to the table, query the common index, the common **** index stored knowledge primary key ID, so other data must be obtained through the primary key ID, to the primary index to obtain data

Index coverage:

Overwriting an index means that there is enough information on the index to satisfy the query request without going back to the primary key index to fetch data.

Select id,k from LTQ where k in (3,5);Copy the code

Through the ordinary index can already meet the requirements, ordinary index already contains ID, K index does not need to return to the table, especially suitable for the joint index

An index pushdown

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB
Copy the code

Federated index (name, age)

Mysql > select * from tuser where name like 'zhang %' and age=10 and ismale=1;Copy the code

When you query ID3, you do not need to query the corresponding data in the table. Instead, you can use the AGE field to filter out the rows that do not meet the conditions, reducing the number of times of retrieving the table

MySQL common lock

Global lock

MySQL provides a method for adding a global read lock by running Flush tables with read lock

The whole library is in the read-only state, data update statements (data add, delete, modify), data definition statements (including table building, modify table structure, etc.) and update class transaction submission statements, will be blocked;

Using the connection status show processList, you can see that the connection is blocked

Show processList;

show processlist;
Copy the code

Waiting fo global read lock; waiting fo global read lock

Unlock: unlock tables;

Table level lock

Table lock and Meta Data Lock (MDL).

The syntax for table locking is lock tables… read/write;

Lock tables friend read, is_like write;

Any other thread that writes friend, reads or writes is_like will block.

Thread A can only read friend and write is_like

Another class of table-level locks is MDL** (Metadata lock)**

Add MDL read lock when adding, deleting, modifying or checking a table; Add an MDL write lock to a table when making structural changes to the table

  • Read locks are not mutually exclusive, multiple threads can add, delete, change and check a table at the same time

  • Read/write locks and write locks are mutually exclusive

Session A starts first and locks table T with an MDL read

Session B also requires an MDL read lock (read locks are not mutually exclusive)

Session C adds the age column, while session A has not committed the read lock, so the lock is not released

Session C is blocked(read and write are mutually exclusive),

If the read lock is not released, subsequent sessionD queries will be blocked

Here is the result

Row locks

MySQL row locking is implemented by each engine at the engine level. MyISAM does not support row locking

A row lock is a lock on a row in a table

begin;
Copy the code

Start a transaction;

Execute update statement to lock corresponding row (hold row lock)

When other threads connect to update the same row, they block, waiting for the row lock

Row locks must wait for the transaction to commit before being released

Row locks are added when they are needed, but are not released immediately when they are no longer needed, but at the end of a transaction

If you need to lock more than one row in your transaction, put the locks that are most likely to cause lock conflicts and affect concurrency as far back as possible

This maximizes concurrency

When you are in a large transaction, try to put the update statement back as much as possible so that the row lock can be opened later, the transaction can be committed, and the row lock can be released as soon as possible

order by

CREATE TABLE `t` ( `id` int(11) NOT NULL, `city` varchar(16) NOT NULL, `name` varchar(16) NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `city` (`city`) ) ENGINE=InnoDB; Select city,name,age from t where city=' hangzhou 'order by name limit 1000;Copy the code

Full field sort

MySQL allocates a block of memory to each thread for sorting, called sort_buffer. Sort_buffer is in the server layer

  1. Sort_buffer is a table at the server layer. The executor checks the table definition and finds that the total length of the name, city, and age fields is less than max_LENGTH_FOR_sort_data

  2. Initialize sort_buffer to ensure that name, city and age are inserted into the buffer.

  3. Select the first primary key id (ID_X) that meets the condition city=’ hangzhou ‘;

  4. Select name, city, age from sort_buffer;

  5. Fetch the primary key ID of a record from index city; Repeat steps 3 and 4 until the value of city does not meet the query conditions, and the corresponding primary key ID is ID_Y in the figure.

  6. Quicksort the data in sort_buffer by field name; Take the first 1000 rows and return them to the client.

Schematic diagram:

The rowid sorting

If the length of sorted row data exceeds max_LENGTH_FOR_sort_DATA;

show variables like '%max_length_for_sort_data%'
Copy the code

Modify the

SET max_length_for_sort_data = 16;
Copy the code

  1. The executor looks at the table definition and finds that the total length of the name, city, and age fields exceeds max_LENGTH_FOR_sort_data. Therefore, only id and NAME fields are added to sort_buffer initialization.

  2. The executor calls the read data interface of the storage engine and finds the first primary key ID from index city that meets the condition city=’ Hangzhou ‘, that is, ID_X in the figure.

  3. Select * from sort_buffer; select * from sort_buffer;

  4. Fetch the primary key ID of a record from index city;

  5. Repeat steps 3 and 4 until the condition city=’ Hangzhou ‘is not met, i.e. ID_Y in the figure;

  6. Sort the data in sort_buffer by field name;

  7. Iterate through the sorting result, take the first 1000 rows, and return to the original table according to the value of ID to retrieve the city, name and age fields and return them to the client.

Sorting process:

  1. Result set is a logical concept,

    The MySQL server actually fetched the ids from the sorted sort_buffer,

    Then check the results of city, name and age fields in the original table. The server saves the results in memory and returns them directly to the client

The reason why MySQL needs to create temporary tables and do sorting operations on temporary tables is that the original data is unordered

alter table t add index city_user(city, name);
Copy the code

  1. When a composite index is added, the data is ordered, sorted by the first field and then by the second

    1. Select first primary key from index (city,name) where city=’ hangzhou ‘;

    2. Select name, city, age as part of the result set;

    3. Drop a record primary key ID from index (city,name); Repeat steps 2 and 3 until the 1000th record is found or the condition city=’ Hangzhou ‘is not met.

Unindexed, sort is used

Add the (city,name) composite index and get it directly from the index without using sort:

Random query

Mysql > CREATE TABLE 'words' (' id' int(11) NOT NULL AUTO_INCREMENT, 'word' varchar(64) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; mysql> select word from words order by rand() limit 3;Copy the code

The Extra column displays Using temporary, indicating that a temporary table is required. Using filesort to indicate that a sort operation is required.

  1. You need to use the Memory engine to create temporary tables. Select * from vARCHar (64); select * from varchar(64)
  2. From the words table, extract all word values in primary key order. For each word value, the rand() function is called to generate a random decimal number greater than 0 and less than 1, and the random decimal and Word are stored separately in a temporary table
  3. Initialize sort_buffer. Sort_buffer has two fields, one of type double and the other of type integer. Random values and primary keys are fetched line by line from an in-memory temporary table
  4. To two fields in sort_buffer. Sort by random values in sort_buffer. After sorting, the location information of the first three results is extracted, and word values are successively extracted from the temporary table in memory and returned to the client

Random query good scheme:

  • Here’s a better solution

    Random query good scheme:

    • Count (*) = count;

    • Randomly generate three values A, B, and C;

    • Execute three more limit Y, 1 to get three rows of data.

    mysql> select count() into C from t; Integer a = Math.floor(count rand()); Integer b = Math.floor(count* rand()); Integer c = Math.floor(count* rand()); select * from t limit a; 1; Select * from t limit b, 1; Select * from t limit c, 1;

MySQL has two kill commands:

  • Kill query + thread ID;
  • Kill connection + thread ID;

When you find a connection deadlock, or a large transaction taking up memory

​show processlist;
Copy the code

Check the connection status and close the connection by id

Geek time address

These are my notes on geek time