Shulou(Shulou.com)06/01 Report--

Introduction to the architecture of 1 MySQL 1.1 sql_mode

Sql_mode is an easy to ignore variable, empty by default, can tolerate some illegal operations, in the production environment, it must be set to strict mode, in the development test environment with this variable is also necessary, because this can find problems before production.

Common values for sql_mode are as follows:

ONLY_FULL_GROUP_BY: for GROUP BY aggregation operations, if the column in SELECT does not appear in GROUP BY, then this sql is illegal NO_AUTO_VALUE_ON_ZERO: this value affects the insertion of self-incrementing columns. By default, inserting 0 or NULL means generating the next growth value. If the primary key ID you want to insert is 0, you can configure this value STRICT_TRANS_TABLES: if a value cannot be inserted into a transaction table The current operation is interrupted, and the non-transactional table does not restrict NO_ZERO_IN_DATE: in strict mode, date and month are not allowed to be zero no _ ZERO_DATE: zero date is not allowed to be inserted, otherwise an exception is thrown ERROR_FOR_DIVISION_BY_ZERO: during insertion or update, an error occurs if the data is divided by zero, if the parameter is not configured Then return NULLNO_AUTO_CREATE_USER when the data is divided by zero: disable the creation of user NO_ENGINE_SUBSTITUTION with empty password: if the required storage engine is not compiled or disabled, throw an error PIPES_AS_CONCAT: regard | as string concatenation operator ANSI_QUOTES: after configuring this parameter, you cannot use "" to refer to the string 1.2 MySQL logical schema.

Compared with other databases, MySQL is a little different. Its architecture can be applied in many different scenarios and play a good role, mainly reflected in the architecture of storage engine. Plug-in storage engine architecture separates query processing from other system tasks and data storage and extraction. This architecture can choose the appropriate storage engine according to business needs and actual needs.

Connection layer

The top layer is client and connection service, including local socket communication and tcp/ip communication, which mainly completes connection processing, authorization authentication and related security schemes. this layer introduces thread pool to provide threads for authorized users and implements ssl safe links.

Service layer

Management Serveices & Utilities: system management and control tool SQL Interface:SQL interface, receives SQL commands and returns query results Parser: parser, validates and parses SQL commands Optimizer: query optimizer, optimizes statements before query, Cache and Buffer: query cache, if the query cache has hit query results, the query statement can directly retrieve data from the query cache. This cache mechanism is composed of a series of small caches. Such as table cache, record cache, key cache, permission cache, etc.

Engine layer

The storage engine layer is responsible for the storage and extraction of data, and the server communicates with the storage engine through API.

Storage layer

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

1.2.1 execution cycle of SQL

Open diagnostic analysis tool

Set profiling=1

Show recent queries

Show profiles

View the execution steps of SQL

Show profile cpu,block io for query 1

1.2.2 the query process client establishes a connection with MySQL, sends the query statement, queries the cache first, and returns the result directly if the cache hits. Otherwise, the statement parsing is performed. That is to say, the parser accesses the cache before parsing, and the parser verifies the statement and parses the query. Then it generates a parsing tree, and then the optimizer converts the parser into the execution plan, and finally executes the plan. Result 1.2.3 SQL execution order FROM ON JOIN WHERE GROUP BYHAVING SELECTDISTINCT ORDER BY LIMIT 1.3 MySQL storage engine

View supported storage engines

Show engines

View the current default storage engine

Show variables like'% storage_engine%'

1.3.1 introduction to each engine

InnoDB

InnoDB is MySQL's default transactional engine, which handles a large number of short-term transactions, and InnoDB is preferred unless there is a special reason to use other storage engines.

MyISAM

MyISAM provides a large number of features, including full-text retrieval, compression, spatial functions, etc., but MyISAM does not support transactions and row-level locks, the disadvantage is that it can not be safely recovered after a crash.

Archive

The Archive archive storage engine only supports INSERT and SELECT operations, but does not support indexing before MySQL5.1

Archive table is suitable for log and data acquisition applications.

According to the English test results, the Archive table is about 75% smaller than the MyISAM table and 83% smaller than the InnoDB table that supports transaction processing.

Blackhole

The Blackhole engine does not implement any storage mechanism, and it discards all inserted data without any saving. However, the server logs the Blackhole table, so it can be used to copy data to the standby database, or simply log it. However, this application will encounter a lot of problems, so it is not recommended.

CSV

CSV engine can deal with ordinary CSV files as MySQL tables, but it does not support indexing. CSV can be used as a mechanism for data exchange, and the data stored by CSV engine can be read by text editor and execl.

Memory

If you need to access the data quickly, and the data will not be modified, it doesn't matter if you lose it after a restart, then using the Memory table is very useful, and the Memory table is at least one order of magnitude faster than the MyISAM table.

Federated

The Federated engine is an agent that accesses other MySQL servers, and although it seems to provide a good cross-server flexibility, it often causes problems, so it is disabled by default.

1.3.2 whether the InnoDB and MyISAM comparison item InnoDBMyISAM foreign key supports whether the transaction supports row table locking. During the operation, only the row of the operation is locked, which will not affect other rows. It is suitable for high and published locks, and even if only one row is operated, the whole table will be locked. It is not suitable for high concurrency caching, which not only caches the index but also caches real data, and has high memory requirements. And memory size has a decisive impact on performance only cache indexes, do not cache real data concerns concurrent writing, transactions, greater resource savings, less consumption, simple business default installation YY default use YN built-in system tables using NY2 index optimization analysis 2.1 optimization steps

Sub-database sub-table

SQL optimization

Build an index

Adjust my.cnf to optimize server and configuration parameters

2.2 introduction to Index 2.2.1 what is an index?

In addition to the data itself, the database maintains a data structure that satisfies a specific search algorithm, which points to the data in some way, so that advanced search algorithms can be implemented on the basis of these data structures. this data structure is the index.

Generally speaking, the index itself is so large that it is impossible to store it all in memory, so the index is often stored on disk in the form of an index file.

Although the index improves the efficiency of the query, it also reduces the efficiency of updating, because when updating the table, we not only insert the data, but also save the fields of the index column added by each update of the index file. will adjust the index information after the key value changes caused by the update

In fact, an index is also a table, which holds the primary key and index fields and points to the records of the entity table, so the index column also takes up space.

2.2.2 MySQL Index structure 2.2.2.1 BTree Index

As shown in the figure, disk block 1 contains data items 17 and 35, including pointers P1, P2, and P3

P1 represents blocks less than 17, P2 represents blocks between 17 and 35, and 35 represents blocks greater than 35.

Search process

If you want to find data item 29, first load disk block 1 into memory, then an IO occurs, use binary search to determine that 29 is between 17 and 35, lock the P2 pointer of disk block 1, load disk block 3 into memory through the disk address of the P2 pointer of disk block 1, then an IO occurs, use binary search to determine 29 between 26 and 30, lock the P2 pointer of disk block 3 Disk block 8 is loaded into memory through the disk address of the P2 pointer of disk block 3, and an IO occurs. At the same time, a binary check is used to find 29, and the query ends.

2.2.2.2 B+Tree Index

The non-leaf nodes of the B+ tree only store key and take up very little space, so the nodes in each layer can index a wider range of data. In other words, each IO operation can view more data.

The leaf nodes are connected to each other, which accords with the pre-read characteristics of the disk. For example, the figure stores the leaf nodes of 5, 8 and 9, which has a pointer to the leaf nodes of 10, 15 and 18, so when we read the data corresponding to 5, 8 and 9 from the disk, because of the pre-reading characteristics of the disk, the corresponding data of 10, 15 and 18 will be read out by the way, which is a sequential read rather than disk seek, which speeds up the speed.

Range query is supported, and part of the scope query is very efficient, because the data is stored in the leaf node layer, and there is a pointer to other leaf nodes, so the range query only needs to traverse the leaf node layer, without the need to traverse the whole tree.

2.2.2.3 clustered index and non-clustered index

A clustered index is not a separate index type, but a way of data storage, in which data rows and adjacent key values are stored together.

According to the order of clustering index, when the query displays a certain range of data, because the data are closely linked, the database does not have to extract data from multiple data blocks, so a lot of IO operations are saved.

Currently, only InnoDB data engine supports clustered indexes for MySQL databases, while MyISAM does not support clustered indexes.

Since there can be only one sort of physical storage of data, each MySQL table can only have one clustered index, which is generally the primary key of the table.

In order to make full use of the clustering characteristics of clustered indexes, the primary key columns of InnoDB tables try to choose ordered ID instead of unordered ID, such as UUID.

2.2.3 MySQL Index Classification 2.2.2.3.1 single-valued Index

That is, an index contains only a single column, and a table can have multiple single-column indexes.

Index together with the table: CREATE TABLE customer (id INT (10) UNSIGNED AUTO_INCREMENT, customer_no VARCHAR, customer_name VARCHAR, PRIMARY KEY (id), KEY (customer_name)); separate index: CREATE INDEX idx_customer_name ON customer (customer_name); deleted index: DROP INDEX idx_customer_name ON customer;2.2.3.2 unique index

The value of the index column must be unique, but can be empty

Index with the table: CREATE TABLE customer (id INT (10) UNSIGNED AUTO_INCREMENT, customer_no VARCHAR, customer_name VARCHAR, PRIMARY KEY (id), KEY (customer_name), UNIQUE (customer_no)); unique index separately: CREATE UNIQUE INDEX idx_customer_no ON customer (customer_no); deleted index: DROP INDEX idx_customer_no ON customer;2.2.3.3 primary key index

Automatically create a primary key index when it is set as the primary key

Index with the table: CREATE TABLE customer (id INT (10) UNSIGNED AUTO_INCREMENT, customer_no VARCHAR (200), customer_name VARCHAR (200), PRIMARY KEY (id)); separate primary key index: ALTER TABLE customer ADD PRIMARY KEY customer (customer_no); delete primary key index: ALTER TABLE customer DROP PRIMARY KEY Modify and build primary key index: you must first delete (DROP) the original index, and then create a new (ADD) index 2.2.3.4 composite index.

An index contains a single column

Index together with the table: CREATE TABLE customer (id INT (10) UNSIGNED AUTO_INCREMENT, customer_no VARCHAR, customer_name VARCHAR, PRIMARY KEY (id), KEY (customer_name), UNIQUE (customer_name), KEY (customer_no, customer_name); separate index: CREATE INDEX idx_no_name ON customer (customer_no, customer_name); delete index: DROP INDEX idx_no_name ON customer 2.2.4 timing of index creation

What are the situations that require index creation?

Fields in which primary keys automatically create unique indexes frequently as query conditions should create fields associated with other tables in index queries. Foreign key relations establish fields that are more cost-effective than single-valued indexes in combinatorial indexes. sorting fields if accessed through the index will greatly improve the sorting speed of statistical or grouping fields in the query.

What are the situations that do not require index creation?

Table records too few frequently updated tables where conditions unused fields do not need to create indexes poorly filtered fields do not need to create index 2.3 performance analysis 2.3.1 EXPLAN

Use the EXPLAIN keyword to simulate the optimizer's execution of SQL query statements to know how MySQL handles SQL statements and to analyze performance bottlenecks in query statements or table structures.

The role of EXPLAN:

View the reading order of the table

See which indexes can be used

Operation type of data read operation

Which indexes are actually used

References between tables

Mode of use:

Explain + SQL

Explain SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id`IS NULL-> UNION-> SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;2.3.2 explain each field

Id:

The sequence number of a SELECT query that contains a set of numbers indicating the order in which the SELECT clause or action table is executed in the query

Same id: execution order from top to bottom

Id is different: if it is a subquery, the sequence number of id will be incremented. The higher the id value, the higher the priority, the first to be executed.

The same id is different, but it exists at the same time: if the id is the same, it can be considered as a group, which is executed sequentially from top to bottom. In all groups, the higher the id value, the higher the priority and the first execution.

Each id represents a separate query. The fewer queries per SQL, the better.

Select_type

The type of query is mainly used to distinguish between ordinary queries, federated queries, subqueries, etc.

SIMPLE: the simplest query, excluding UNION and subqueries

PRIMARY: if the query contains complex subsections, the outermost query is marked as PRIMARY

DERIVED: subqueries included in the FROM list are marked as DERIVED,MySQL. These subqueries are executed recursively, putting the results in a temporary table.

SUBQUERY: include subqueries in SELECT or WHERE lists

DEPENDENT SUB: include subqueries in SELECT or WHERE lists, which are based on outer layers

UNCACHEABLE SUBQUREY: subqueries whose result sets cannot be cached must be re-evaluated for each row of the outer query

UNION: if the second SELECT appears after UNION, it is marked as UNION

UNION RESULT: the SELECT that gets the result from the UNION table

Table

Show which table the data in this row is about

Type

Displays the types used by connections, sorted by the best to the worst

System: the table has only one row of records

Const: indicates that it is found once through the index. Const is used to compare primary key or unique indexes, because only one row of data is matched, so it is very fast.

If the primary key is placed in the where list, MySQL can convert the query to a constant

Eq_ref: unique index scan, for each index key, only one record in the table matches it, which is common in primary key or unique index scan

Ref: a non-unique index scan that returns all rows that match a single value, which is essentially an index access that returns all rows that match a single value. However, it may find multiple rows that match the criteria, so it should be a mixture of lookups and scans.

Range: only retrieve a given range of rows, use an index to select rows, and the key column shows which index is used. Generally, queries such as between, in, and so on appear in the where statement. This range scan index scan is better than a full table scan, because it only needs to start at a certain point of the index, while the conclusion point is not to scan all indexes.

Index: when it comes to index, SQL uses the index but does not filter through the index. Generally, the overlay index is used or the index is used to sort groups.

All:Full Table Scan, which will traverse the entire table to find matching rows

Index_merge: multiple indexes are required to be combined during the query process, usually in SQL with the keyword or

Ref_or_null: for a field that requires both an association condition and a null value, the query optimizer will choose to join the query with ref_or_null

Index_subquery: use indexes to correlate subqueries instead of full table scans

Unique_subquery: this join type is similar to index_subquery, a unique index in a subquery

Generally speaking, you have to ensure that the query reaches at least range level, preferably ref

Possible_keys

Displays one or more indexes that may be applied to this table. If an index exists on the fields involved in the query, the index will be listed, but not necessarily used by the query.

Key

If the actual index is NULL, no index is used. If an overlay index is used in the query, the index overlaps with the select field of the query.

Key_len

Indicates the number of bytes used in the index, which can be used to calculate the length of the index used in the query, and the key_len field can check whether the index is fully utilized.

Ref

Shows which column of the index is used and, if possible, a constant, and which columns or constants are used to find values on the index column

Rows

The rows column shows the number of rows that MySQL thinks it must check when executing the query

Extra

Contains additional information that is not suitable for display in other columns but is very important

Using filesort: indicates that mysql will sort the data using an external index instead of reading according to the index order within the table. The sorting operation in MySQL that cannot be done by index is called file sorting Using temporary: the intermediate results are saved with temporary tables, and MySQL uses temporary tables when sorting query results, which is common in sorting order by and grouping query group byUSING index: indicates that the overlay index (Covering Index) is used in the corresponding select operation. It is efficient to avoid accessing the data rows of the table. If using where appears at the same time, it indicates that the index is used to perform the lookup of index key values. If there is no using where at the same time, it indicates that the index is only used to read data and not to use the index to perform lookup Using where: indicates that where filtering using join buffer is used: join cache is used: the value of the impossible where:where clause is always false Cannot be used to get any tuple select tables optimized away: in the case of no GROUP BY clause, optimizing MIN/MAX operations based on indexes or optimizing COUNT (*) operations for MyISAM storage engines does not have to wait until the execution phase to calculate, and the optimization is completed at the stage of query execution plan generation. 2.4 query Optimization 2.4.1 failure of using Index in single Table

Index (a _

Whether the Where sentence index is used WHERE a = 3y, aWHERE a = 3 AND b = 5y, a, bWHERE a = 3 AND b = 5 AND c = 4y, a, b, cWHERE b = 3 AND c = 4, WHERE c = 4nWHERE a = 3 AND c = 5y, WHERE a = 3 AND b > 4 AND c = 5y, WHERE a = 3 AND b > 4 AND c = 5y B interrupts WHERE an IS NULL AND b IS NOT NULLis null supports indexes but is not null does not, so a can use indexes, b cannot use indexes WHERE a 3 cannot use indexes WHERE abs (a) = 3abs cannot use indexes WHERE a = 3 AND b LIKE 'kk%' AND c = 4y, use a, b, cWHERE a = 3 AND b LIKE'% kk' AND c = 4y, use aWHERE a = 3 AND b LIKE'% kk%' AND c = 4y Use aWHERE a = 3 AND b LIKE'k% k% 'AND c = 4y, use a, b, c

Recommendations for creating an index:

For single-valued indexes, try to choose fields that are more filtered for the current query.

Select a combined index. The most filtered fields of the current query are located as far ahead of the index as possible.

Select a combined index, and try to select an index that can contain more fields in the where clause in the current query.

When selecting a combined index, if a field may have a range query, try to put the field at the end of the index order.

2.4.2 Relational query optimization

Ensure that the join field of the driven table has been indexed

When left join, select the small table as the driven table and the large table as the driven table

When inner join, MySQL will select the table of the small result set as the driver table.

As far as possible, subqueries should not be placed on driven tables, and indexes may not be used.

It can be directly associated with multiple tables as directly as possible, without subquery.

2.4.3 subquery optimization

Try not to use not in or not exists, use left join on xxx is null instead

2.4.4 sorting and grouping optimization

ORDER BY clause, try to use Index sorting, avoid using FileSort sorting

If it is not on an index column, filesort has two algorithms:

Two-way sorting

MySQL 4.1 uses two-way sorting, which literally means to scan the disk twice, finally get the data, read the row pointer and order by column, sort them, then scan the sorted list, re-read the corresponding data output from the list according to the values in the list, take the sort field from the disk, sort in buffer, and then fetch a batch of data from the disk, and scan the disk twice. As we all know, IO is very time-consuming, so after MySQL 4.1, there is a second improved algorithm, that is, single-path sorting.

One-way sorting:

Read all the columns needed by the query from disk, sort them in buffer according to the order by column, and then scan the sorted list for output. It is more efficient, avoids the second reading of data, and turns random IO into sequential IO, but it uses more space because it saves each row in memory. One-way sorting takes out all the fields. Therefore, it is possible that the total size of the data taken out exceeds the capacity of sort_buffer, so that only the data of sort_buffer capacity can be sorted each time, and then take a part after sorting. On the contrary, it increases the IO optimization strategy, increases the setting of sort_buffer_size parameters, increases the setting of max_length_for_sort_data parameters, reduces the use of select in the fields of the query behind select. *

The principle of using indexes in group by is almost the same as that in order by, except that group by can use indexes directly even if there are no filtering conditions.

3 query interception analysis

What is a slow query log?

Slow log is a kind of log record provided by MySQL. It is used to record statements whose response time exceeds the threshold in MySQL. Specifically, SQL whose running time exceeds the long_query_time value will be recorded in the slow log.

The default value for long_query_time is 10, which means to run a statement for more than 10 seconds.

The default slow query log is turned off and needs to be opened manually

Check whether the slow log enables SHOW VARIABLES LIKE'% slow_query_log%'; to enable slow log set global slow_query_log=1

View and configure long_query_time

View long_query_timeSHOW VARIABLES LIKE 'long_query_time%';set long_query_time=1

Log analysis tool mysqldumpslow

Common references:

Hadoop100 gets the 10 SQLmysqldumpslow-s r-t 10 / var/lib/mysql/hadoop100-slow.log that returns the most recordsets, gets the 10 SQLmysqldumpslow-s c-t 10 / var/lib/mysql/hadoop100-slow.log with the most visits, and gets the query statement mysqldumpslow-s t-t 10-g "left join" / var/lib/mysql/hadoop100-slow.log that contains the left join in the first 10 items sorted by time. Some commands are used in combination with more Otherwise, the screen burst may occur mysqldumpslow-s r-t 10 / var/lib/mysql/hadoop100-slow.log | more4 master-slave copy

The basic principles of replication

Master records changes to binary logs (binary log), which are called binary log events, binary log events

Slave copies the binary log events of master to its relay log (relay log)

Slave redoes the events in the relay log and applies the changes to its own database. MySQL replication is asynchronous and serialized.

The basic principles of replication

There is only one master per slave

There can be only one unique server ID per slave

Each master can have multiple salve

4.1 configure master-slave replication

1. Configure the master database

Vim / etc/my.cnf

Server-id=1log-bin=mysql-binbinlog_format=mixed

Assign accounts to slave services

View the information of the master server BIN log

Show master status

Restart the master database

Systemctl restart mariadb

2. Configure the slave database

Connect to the master database

CHANGE MASTER TO-> MASTER_HOST= "192.168.10.100",-> MASTER_USER= "slave",-> MASTER_PASSWORD= "123456",-> MASTER_LOG_FILE= "mysql-bin.000001",-> MASTER_LOG_POS=388

Start the slave database

Start slave

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.