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

System Database in MySQL

Mysql database: it is the core database of mysql, similar to the master library in sql server. It is mainly responsible for storing the control and management information that mysql needs to use, such as database users, permissions settings, keywords and so on.

PERFORMANCE_SCHEMA:MySQL 5.5.The new database is mainly used to collect database server performance parameters. The storage engine of the tables in the database is PERFORMANCE_SCHEMA, and users cannot create tables with the storage engine of PERFORMANCE_SCHEMA.

Information_schema database: a virtual database created after MySQL 5.0.It doesn't physically exist. Information_schema database is similar to "data dictionary" and provides a way to access database metadata, that is, the data of the data. Such as database name or table name, column type, access rights (more detailed access method)

Server configuration

Mysqld options, server system variables, and server state variables

Https://dev.mysql.com/doc/refman/5.7/en/mysqld-option-tables.html

Https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-status-variables/

Note: some of these parameters support runtime modification and will take effect immediately; some parameters are not supported and can only take effect by modifying the configuration file and restarting the server program; some parameters are global and immutable; some can provide separate (session) settings for each user

Get a list of available options for mysqld:

Mysqld-- help-verbose

Mysqld-- print-defaults gets the default settings

Server system variables: global and session

Server state variables: global and session

Gets the server parameters and their values used by the running mysql process

Mysql > SHOW GLOBAL VARIABLES

Mysql > SHOW [SESSION] VARIABLES

There are three ways to set the server system variable:

Set up on the command line:

Shell >. / mysqld_safe-- skip-name-resolve=1; # skips DNS reverse parsing to speed up the connection

In the configuration file my.cnf, set:

Skip_name_resolve=1

Use the SET command on the mysql client:

Mysql > SET GLOBAL sql_log_bin=0

Server-side settin

Modify the value of the server variable:

Mysql > help SET

Modify global variables: valid only for newly created sessions after modification; invalid for established sessions

Mysql > SET GLOBAL system_var_name=value

Mysql > SET @ @ global.system_var_name=value

Modify the session variable:

Mysql > SET [SESSION] system_var_name=value

Mysql > SET @ @ [session.] system_var_name=value

State variable (read-only): a variable used to hold statistics while mysqld is running, which cannot be changed

Mysql > SHOW GLOBAL STATUS

Mysql > SHOW [SESSION] STATUS

Server variable SQL_MODE

SQL_MODE: some constraint checking can be done by setting it, which can be set globally or for the current session respectively. See: https://mariadb.com/kb/en/library/sql-mode/

Common MODE:

NO_AUTO_CREATE_USER

Prohibit GRANT from creating users with empty passwords

NO_AUTO_VALUE_ON_ZERO

Inserting 0 or NULL into a self-growing column will not be the next self-growing value

NO_BACKSLASH_ESCAPES

Backslash "\" as a normal character rather than an escape character

PAD_CHAR_TO_FULL_LENGTH

When enabled, hollow data will not be truncated for CHAR types

PIPES_AS_CONCAT

Treat "| |" as a join operator rather than a "or operator"

Example:

Set sql_mode='traditional'

Show variables like 'sql_mode' can see that traditional consists of many items.

Options that need to be added to the MariaDB profile

Innodb_file_per_table

Each table is saved as a separate file

Log_bin=/data/mysqllog/mysql-bin

One of two options to turn on binary logging

Do not put the binary log on the same disk as the data

Note that the group permission of the owner of the folder must be mysql

Leave enough space, usually the binary log is larger than the database itself.

Binlog_format=row

Binary logs are based on line records

Innodb_log_group_home_dir=/data/mysqllog/transaction_log/

Specify the transaction log storage directory, which is recommended to be stored on a different disk from the data.

Skip_name_resolve=on

Forbid name parsing

Query_cache_size=10M

Query cache size is set to not 0, otherwise query cache is not enabled

Frequently used fields can be indexed

MySQL architecture

The block size of the database is an integral multiple of 4K, and the MySQL is 16K

The execution path of the query

Query cache

Query caching (Query Cache) principle:

Cache SELECT operations or preprocess query result sets and SQL statements. When new SELECT statements or preprocessing query statements are requested, query the cache first to determine whether there is an available recordset. The criteria: whether it is exactly the same as the cached SQL statement, case-sensitive (because the hash value is used)

Advantages and disadvantages

There is no need for any parsing and execution of SQL statements. Of course, syntax parsing must first obtain query results directly from Query Cache to improve query performance.

The judgment rules of query cache are not intelligent enough, that is, it increases the threshold for the use of query cache and reduces its efficiency.

The use of query cache increases the overhead of checking and cleaning recordsets in Query Cache

Which queries may not be cached

The SQL_NO_CACHE parameter is added to the query statement (for example, when querying millions of large tables, it cannot actually be cached, but adding SQL_NO_CACHE can improve performance)

The query statement contains functions that get values, including custom functions, such as: NOW ()

CURDATE (), GET_LOCK (), RAND (), CONVERT_TZ (), etc.

Queries to system databases: SESSION-level variables or local variables in stored procedures are used in mysql and information_schema query statements

LOCK IN SHARE MODE and FOR UPDATE are used in the query statement, which is similar to SELECT in the query statement. Statements for INTO to export data

Query operations on temporary tables; queries with warnings; queries that do not involve any tables or views; queries that a user only has column-level permissions

When the transaction isolation level is Serializable, all query statements cannot be cached

Query cache-related server variables

Query_cache_min_res_unit: the minimum allocation unit of memory blocks in the query cache. The default is 4k. Smaller values will reduce waste, but will lead to more frequent memory allocation operations. Higher values will lead to waste, excessive fragmentation and insufficient memory.

Query_cache_limit: the maximum value that a single query result can be cached. Default is 1m.

SQL_NO_CACHE is recommended for statements whose query results are too large to be cached

Query_cache_size: the total memory space available to the query cache; in bytes, it must be an integer multiple of 1024, with a minimum value of 40KB. Below this value, there is an alert (default is 0, cache is not started)

Query_cache_wlock_invalidate: if a table is locked by another session, whether the result can still be returned from the query cache. The default value is OFF, which means you can continue to return data from the cache in a scenario where the table is locked by another session. ON means it is not allowed.

Query_cache_type: whether to enable caching. Values include ON, OFF, and DEMAND.

Cache control of SELECT statements

SQL_CACHE: explicitly specifies that query results are stored in the cache

SQL_NO_CACHE: explicit query results are not cached

Query_cache_type parameter variable:

Query caching is turned off when the value of query_cache_type is OFF or 0

If the value of query_cache_type is ON or 1, query caching is enabled, and the result of SELECT will be cached if it meets the caching condition. Otherwise, no caching will be performed. If SQL_NO_CACHE is explicitly specified, no caching will be allowed. This is the default value.

When the value of query_cache_type is DEMAND or 2, the query caching function is performed as needed, and only the SELECT statements of explicitly specified SQL_CACHE are cached; others are not cached.

See:

Https://mariadb.com/kb/en/library/server-system-variables/#query_cache_type

Https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html

Optimize query cache

Query cache-related state variables

SHOW GLOBAL STATUS LIKE 'Qcache%'

Qcache_free_blocks: number of memory Block in idle Query Cache

The total Block in the Qcache_total_blocks:Query Cache. When the Qcache_free_blocks is relatively large, it is possible to use memory fragments to perform FLUSH QUERY CACHE to clean up the fragments.

Qcache_free_memory: total amount of Query Cache memory that is idle

Number of Qcache_hits:Query Cache hits

Qcache_inserts: the number of times a new Query Cache is inserted into the Query Cache, that is, the number of times it missed

Qcache_lowmem_prunes: the number of times the old Query Cache needs to be deleted for the new Cache object when the Query Cache memory capacity is insufficient

Qcache_not_cached: the number of SQL that has not been Cache, including the SQL that cannot be Cache and the SQL statement that will not be Cache due to query_cache_type

Qcache_queries_in_cache: number of SQL in Query Cache

Estimation of hit rate and memory usage

The minimum allocation unit of memory blocks in the query cache query_cache_min_res_unit: (query_cache_size-Qcache_free_memory) / Qcache_queries_in_cache

Query cache hit rate: Qcache_hits / (Qcache_hits + Qcache_inserts) * 100%

Query cache memory usage: (query_cache_size-qcache_free_memory) / query_cache_size * 100%

InnoDB storage engine

Buffer pool for the InnoDB storage engine:

In general, the hit of the InnoDB storage engine buffer pool should not be less than 99%

View the relevant status variables:

Show global status like 'innodb%read%'\ G

Innodb_buffer_pool_reads: indicates the number of times the page was read from the physical disk

Innodb_buffer_pool_read_ahead: the number of times pre-read

Innodb_buffer_pool_read_ahead_evicted: the number of pages that are pre-read but replaced from the buffer pool without reading, generally used to determine the efficiency of pre-reading

Innodb_buffer_pool_read_requests: the number of times pages are read from the buffer pool

Innodb_data_read: total number of bytes read

Innodb_data_reads: the number of read requests initiated. Multiple pages may need to be read per read.

Innodb buffer pool hit ratio calculation:

Average number of bytes per read:

Indexes

An index is a special data structure: defines a field that is used as a lookup condition during a lookup

The index is implemented in the storage engine

Advantages:

Indexing can reduce the amount of data that the service needs to scan and reduce the number of IO

Indexes can help servers avoid sorting and use temporary tables

The index can help to convert random Ithumb O to sequential IGO.

Disadvantages:

Take up extra space and affect the insertion speed (because you want to change the index at the same time)

Index type:

B + TREE, HASH, R TREE

Clustered (clustered) index, non-clustered index: whether the data and index are stored in the same order

Primary key index, secondary (secondary) index

Dense index, sparse index: whether each data item is indexed

Simple index, combined index (refers to the establishment of indexes on 1 and multiple fields, respectively)

Left prefix index: index the preceding characters

Override index: the data to be queried can be extracted from the index with high performance

Clustered and non-clustered indexes, primary and secondary indexes

The clustered index is equivalent to the catalogue of the book, and the non-clustered index is equivalent to the appendix of the book.

In general, the primary key index is with the data, and the secondary index stores the pointer to the primary key, which is not with the data, and is generally not the index of the primary key.

MyISAM does not support clustered indexes

B+TREE index

B+tree index

B+tree index

B + Tree index: sequential storage, the distance from each leaf node to the root node is the same; left prefix index, suitable for querying data of range class

The types of queries that can be used with B-Tree indexes:

Full match: exact all index columns, such as: last name qin, first name jianyuan, age 27

Matches the leftmost prefix: that is, only the first column of the index is used, such as qin

Match column prefix: matches only the beginning of a column of values, such as those whose last name begins with Q

Match range values: for example, between qin and wang

Exactly matches one column and the range matches another: for example, the last name is qin, and the first name starts with j

Query that accesses only the index

Restrictions on B-Tree indexes:

If you do not start with the leftmost column, you cannot use the index: for example, to find the first name xiaochun or the last name to end with g

You cannot skip the columns in the index: for example, if you look up a person with the surname wang, age 30, you can only use the first column of the index.

If a column in the query is a range query, then the column on the right can no longer use the index: for example, wang, first name x%, age 30, and can only use the index above the last name and first name.

Special hint:

The order of the index columns should match the writing of the query statement in order to make better use of the index.

To optimize performance, it may be necessary to create different indexes for the same column but in different order to meet different types of query requirements

Hash index

Hash index: implemented based on hash table, only queries that accurately match all columns in the index are valid. The index itself only stores the corresponding hash values and data pointers of the index columns. The index structure is compact and the query performance is good.

Explicit hash indexes are supported only by the Memory storage engine

Applicable scenarios:

Only equivalent comparison queries are supported, including =, IN ()

Scenarios that are not suitable for using hash indexes:

Does not apply to sequential queries: the order in which indexes are stored is not the order of values

Fuzzy matching is not supported

Range query is not supported

Partial index column matching search is not supported: for example, the A column index is invalid if only A column index is queried.

Other indexes

Spatial index (R-Tree):

MyISAM supports spatial indexing, can use any dimension combination query, and uses unique function access. It is often used for geographic data storage, but it is not used much.

Full text Index (FULLTEXT):

Look for keywords in the text instead of directly comparing the values in the index, similar to search engines

Clustered and non-clustered index

Clustered and non-clustered indexes, primary and secondary indexes

Redundant and duplicate indexes:

Redundant index: (a), (Arecoy B)

This is a bad index usage strategy. It is recommended to extend the index rather than redundancy.

Duplicate index: there is already an index, create the index again

Index optimization strategy:

Use columns independently: try to avoid their participation in the operation. Independent column index columns cannot be part of an expression or an argument to a function. In where conditions, index columns are always placed separately on one side of the comparison symbol.

Left prefix index: builds the number of characters on the left side of the specified index field, which is evaluated by index selectivity

Index selectivity: the ratio of non-duplicate index values to the total number of records in the data table

Multi-column indexes: AND operations are more appropriate to use multi-column indexes than to create separate indexes for each column

Select the appropriate index column order: when there is no sorting and grouping, the selectivity will be the highest on the left.

Index optimization recommendations

As long as the column contains a null value, it is best not to set the index in this example. If the composite index has a null value, the column will not use the index.

Try to use a short index and, if possible, set a prefix length

For columns that are often used in the where clause, it is best to set the index

For multiple column where or order by clauses, a composite index should be established

For like statements, indexes are not used if they start with% or'- 'and end with%

Try not to operate on columns (function operations and expression operations)

Try not to use not in and operations

Performance optimization of SQL statements

When querying, can you write the full field name without *?

In most cases, the join efficiency is much higher than that of the subquery

When multiple tables are connected, small tables drive large tables as much as possible, that is, small tables join large tables

Use limit in tens of millions of paging

For frequently used queries, you can turn on caching

Use more explain and profile to analyze query statements

Check the slow query log to find out the optimization of sql statements with long execution time.

Manage index

Create an index:

CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name,...)

Help CREATE INDEX

Delete the index:

DROP INDEX index_name ON tbl_name

View the index:

SHOW INDEXES FROM [db_name.] tbl_name

Optimize tablespaces:

OPTIMIZE TABLE tb_name (suggest tidying up when there is a big change in the table)

View the use of the index

SET GLOBAL userstat=1

SHOW INDEX_STATISTICS

EXPLAIN

Analyze the validity of the index through EXPLAIN:

EXPLAIN SELECT clause

Gets the query execution plan information to see how the query optimizer executes the query

Output information description:

Reference https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

Id: the number of each SELECT statement in the current query statement

There are three complex types of queries:

Simple subquery

For subqueries in FROM

Federated query: UNION

Note: an additional anonymous temporary table will appear in the analysis results of the UNION query

Select_type:

Simple query is SIMPLE

Complex query:

SUBQUERY: simple subquery

PRIMARY: outermost SELECT

DERIVED: for subqueries in FROM (if this item is used in newer versions)

The SELECT statement after the first UNION:UNION statement

UNION RESULT: anonymous temporary table

The table to which the table:SELECT statement is associated

Type: Association type or access type, that is, the way MySQL determines how to query the rows in the table, in the following order, performance from low to high

ALL: full table scan

Index: perform a full table scan according to the order of the indexes; if "Using index" appears in the extra column, it means to use an override index instead of a full table scan

Range: scope-limited scan based on the index; the scan location starts at one point in the index and ends at another point

Ref: returns all rows in the table that match a single value based on the index

Eq_ref: returns only one row, but compared to the need for an additional reference value

Const, system: returns a single row directly

Possible_keys: the index that may be used in the query

Key: the index used in the query

Key_len: number of bytes used in the index

Ref: the column or constant value used to complete a query using the index represented by the key field

Rows:MySQL estimates the number of rows that need to be read to find all the target rows

Extra: additional information

Using index:MySQL will use an override index to avoid accessing the table

The Using where:MySQL server will filter again after being retrieved by the storage engine

Using temporary:MySQL uses temporary tables when sorting results

Using filesort: sort the results using an external index

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.