The top layer is some client and connection services, including local socket communication and most TCP/IP-like communication based on client/server tools.

It mainly completes some connection processing, authorization authentication, and related security solutions. The concept of thread pool is introduced on this layer to provide threads for clients that securely access through authentication.

The second-layer architecture mainly completes most of the core service functions, such as SQL interfaces, and completes cached queries, SQL analysis and optimization, and the execution of some built-in functions.

At this layer, the server will parse the query and create the corresponding internal parse tree, and complete the corresponding optimization: such as determining the order of the query table, whether to use indexes, etc., and finally generate the corresponding execution operation

core services

Storage engine layer, the storage engine is really responsible for the storage and retrieval of data in MySQL, and the server communicates with the storage engine through API. Different storage engines have different functions, so we can choose according to our actual needs.

The data storage layer mainly stores data on the file system running on the raw device and completes the interaction with the storage engine.

Method 1: Modify the configuration file and add configuration information

Method 2: Use mysql command

show variables like '%profiling%';

time complexity

Non-leaf nodes store data

The query needs to be matched layer by layer, and the range query needs to be traversed

Deleting nodes will cause the tree to rearrange

time complexity

Non-leaf nodes do not store data

When querying data, you only need to query leaf nodes and maintain bidirectional pointers, organized in a linked list, and support range queries.

Deleting node B will not rearrange the tree.

Cluster index (must exist, and there is only one)

non-clustered index

Improve data retrieval efficiency and reduce IO costs

Reduce data sorting costs and reduce CPU consumption

The index is actually a table that stores the primary key and index fields and points to the records of the entity table, so the index columns also take up space.

Although indexes improve query speed, they reduce table update speed.

poly index

Non-clustered index (secondary index)

Create index

Create prefix index

Create a union index

Delete index

Query table index

Single value index

unique index

primary key index

Both can be used as cluster indexes, and the primary key index can be used as a cluster index.

Composite index (joint index)

Need to create index

No need to create index

Create indexes for fields with large amounts of data and frequent queries

For fields that are often used as query conditions, such as Group By, where, order by

If it is a string type field and the length of the field is long, you can create a prefix index based on the characteristics of the string.

Try to use joint indexes and reduce the use of singleton indexes. When querying, joint indexes can cover indexes, save disk space, avoid table backs, and improve efficiency.

Control the number of indexes. The more indexes, the better. Indexes will also take up a lot of disk space, and it takes a long time to maintain the index, which will affect the efficiency of additions, deletions and modifications.

If the index field cannot store NULL values, use NOT NULL to constrain it when creating the table. This facilitates optimization by the optimizer.

Do not use * when querying. Query the corresponding fields according to the required fields. Use covering indexes.

Function operations are used on index fields, including ordinary operations, and the index is invalid.

String type fields are not enclosed in single quotes, resulting in implicit type conversion and index failure.

When performing fuzzy queries, if % is added in front, the index will also become invalid.

There must be indexes before and after the or connection. If there is no index on either side, the index will be invalid.

Impact of data distribution: If MySQL evaluates that a full table scan is faster than a full index, the index will also become invalid.

The joint index after the range query will be invalid < , > , !=

is not null will cause the index to fail, is null can use the index

grammar

Efficiency comparison of grouping and sorting

Result analysis

page splitting

Page merge phenomenon

The length of the primary key should not be too long, which will occupy space, generate more pages, and cause multiple IOs.

When inserting the primary key, keep it in order and avoid page splits.

Do not use uuid or other natural primary keys such as ID cards

one-way sort

two-way sort

When not using where, you can try adding limit

The fields of the joint index are in the wrong order, so the whole table must be sorted.

The field sorting rules of joint index sorting must be consistent, either all ascending or all descending order, otherwise the whole table sorting must be used

method one

Method 2

count(primary key)

count(field)

count(number)

count(*) The InnoDB engine will traverse the entire page without taking a value. The server has made special optimizations and accumulates data directly by row.

The two are the fastest because there is no need to take a value

The updated condition field name must be indexed, otherwise the row lock will be upgraded to a table lock.

All configuration items of master and slave are configured under the [mysqld] node, and they are all in lowercase letters.

binlog_format

grant all privileges on *.* to root@'%' identified by 'root'; # Create a root user and authorize remote access!

After performing this step, do not operate the main server MYSQL again to prevent the status value of the main server from changing.

[Required] Unique ID of slave server: server-id=2

[Optional] Enable relay log: relay-log=mysql-relay

CHANGE MASTER TO MASTER_HOST='mall_mysql_master',MASTER_PORT=3306,MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=0;

start slave;

show slave status\G;

mysql> stop slave;

mysql> reset slave;

mysql> reset master;