Forwarding Original address: http://www.cnblogs.com/hanzhao1987/p/6100096.html
MySQL Benefits:
Easy to use, low cost, easy to expand, replication leading
The life cycle of MySQL:
MySQL server listener 3306 port > Authenticate Access user > create MySQL thread > Check memory (qcache) > Parse sql> generate query plan > Open Table > Check memory (Buffer PooL) > To disk fetch data > Write memory > Return data to Client > Close table > Close thread > Close connection
MySQL Architecture:
MySQL connector: Client
MySQL server: connection pool; query optimizer (cache); Storage engine layer (index structure, cache); file system (File,log)
----------------------------------------------------------------------------------------------
MySQL Connection number:
A small number of connections, under large concurrency, can cause the connection to be released, causing the database connection to time out or the corresponding time is too long
mysql config file max_connections = 1000 maximum number of links, do not write default is 100
Wait_timeout = 10 time-out period
See how many connections are currently
Show status like '%threads_connected% '
Show Processlist
-----------------------------------------------------------------------------------------
MySQL Cache
1.mysql Layer: Query Cache queries caches: stores the full result of a select, and when there is the same query, skips the parsing, optimization, and execution stages, directly returning the result, and when the table related to this cache SELECT statement changes, this cache fails
Open can improve performance, but for read and write increased overhead, read the time to check the cache, for write, after writing to update the cache
But generally this is a small cost, but also to be based on business characteristics trade-offs
Win (My.ini) Linux (MY.CNF)
SHOW VARIABLES like '%query_cache% ':
Query_cache_type=on//on turn off off
query_cache_size=200m General recommendation not to exceed 256M
query_cache_limit=1m limit the size of the storage results, if the results are larger than this, it will not be cached
Query the current usage of Quache:
SHOW STATUS like '%qcache% '
2. Storage Engine Layer: Innodb_buffer_pool
He caches the data in the entire table, the bigger the better, the general setting is 70% of the server's physical memory.
Parameters:
View Innodb_buffer_pool:
SHOW VARIABLES like '%innodb_buffer_pool% ':
Innodb_buffer_pool_size: Size
Query the current usage of Innodb_buffer_pool:
SHOW STATUS like '%innodb_buffer_pool% '
The main concerns are two parameters:
Innodb_buffer_pool_read_requests total number of BP queries
Innodb_buffer_pool_reads the number of times the data was fetched from the physical hard disk
With these two parameters, we can know BP's hit rate.
--------------------------------------------------------------------------------------------------------------- ---
MySQL Slow query log
You can use Mysqlslowdump to analyze slow query logs
Configure MySQL Slow query
Linux:
Added in MySQL config file my.cnf: log-slow-queries=/opt/data/slowquery.log (Specify log file location, can be empty, the system will give a default file Host_name-slow.log) long_query_time=2 (record more than the time, default is 10s) log-queries-not-using-indexes (log down without using the index of the query, depending on the situation can decide whether to open)
Windows:
In My.ini [mysqld] Add the following statement: log-slow-queries = E:\web\mysql\log\mysqlslowquery.loglong_query_time = 2 (other parameters as above)
Set global slow_query_log=on;
Set global long_query_time=1; #设置记录查询超过多长时间的sql
Set global slow_query_log_file= '/opt/data/slow_query.log '; #设置mysql慢查询日志路径, this path requires write access
This is not a way to restart the MySQL service. -----query: SHOW VARIABLES like '%query% ';
Use this statement to see if the current MySQL slow query is turned on, and where the slow query log file for MySQL is.
Slow_query_log #是否开启慢查询
Slow_query_log_file #日志的存放位置
Long_query_time #超过多少秒的查询就写入日志
-----Parse the MySQL slow query log using the Mysqldumpslow command to parse the MySQL slow query log.
The mysqldumpslow command parameters are as follows:
-S, is the way to indicate the sort, C, T, L, R are in accordance with the number of records, time, query time, the number of records returned to sort, AC, at, AL, AR, indicating the corresponding flashbacks;
-T, which is the meaning of top N, which is to return the data of the previous number of bars;
-G, you can write a regular match pattern behind, the case is not sensitive; For example, to follow the top 20 SQL for the longest SQL execution time
Mysqldumpslow-s t-t 20-g ' select '/opt/data/slowquery_2016050921.log
Get the query that contains the left connection in the first 10 lines sorted by time.
Mysqldumpslow-s t-t 10-g ' left join '/opt/data/slowquery_2016050921.log mysqldumpslow command result
-------------------further analysis of the most resource-intensive SQL explain
This command is the main way to see how the query optimizer decides to execute the query, a feature that has limitations, is just an approximation, and sometimes it is a good approximation, and sometimes it may be far apart. But its output is the most accurate information that can be obtained and is worth studying carefully.
A simple execution plan
| 1 |
mysql> explain select* from students where id in(2,3); |
1.explain does not take into account the impact of triggers, stored procedures, or functions on queries.
2.explain does not consider the impact of caching on query results
3. The specific optimizations that MySQL performs queries are not considered
4. Is an estimate based on statistical information, not an exact value.
5.mysql5.6 only supports explain of select before
Select column, which has the following values
Simple it represents a straightforward select with no union and subquery.
Primary the outermost select, in the statement with the subquery, the outermost select query is primary
The DERIVED value indicates that Select,mysql, which is contained in a subquery in the FROM clause, executes recursively and places the result in a temporary table. The server internally calls its "derived table" because the temporary table is a table column derived from a subquery:
Displays the name of the table referenced by the output row, and if it is a subquery, the table name displays the derived type column: refers to the type of MySQL access, that is, how to find rows in the table, the following is the most important method of access, from the worst to the best:all<index<range< Ref<eq_ref<const,system<nullall
Is full table scan, usually means that MySQL must scan the entire table, from beginning to end, to find the desired line. The InnoDB table is the primary key order.
Index
The join type is the same as all except that only the index tree is scanned. This is usually faster than all, because the index file is usually smaller than the data file. (That is, although all and index are read-only, index is read from the index and all is read from the hard disk).
Range
A range scan is a restricted index scan that starts at a point within the index and returns rows that match the entire value domain. This is better than a full index scan because it does not need to traverse all indexes. It is obvious that between or in the where sentence with a ">" or "<" query or in.
Ref
A non-unique index scan that returns all rows that match a single value.
Eq_ref
With this index lookup, MySQL knows that only one record that matches the criteria is returned. This access can be seen when MySQL uses a primary key or a unique index. MySQL is very good for these types of access type optimizations.
Const,system
When MySQL can optimize a part of a query and convert it into a constant, it will use these types of access.
Null
This access means that MySQL can break down query statements during the optimization phase, or even need to access tables or indexes at the execution stage.
Key_len columns:
This column shows the number of bytes that MySQL uses in the index.
Ref column:
Shows which column of the index is being used
Rows column
The number of rows that MYSQL considers must be checked to return the requested data
Extra
This column contains additional information that is not suitable for display in other columns.
Some of the more important ones are as follows:
"Using index"
This value indicates that MySQL will use the overwrite index to avoid accessing the table.
"Using where"
Indicates that the query used a WHERE condition
"Using temporary"
This means that MySQL uses a temporary table when sorting the results of the query.
"Using Filesort"
This means that MySQL uses an external index to sort the results instead of reading rows from the table in indexed order.
--------------------------------------------------------------------------------------------------------
MySQL profile analyzing the overhead of SQL execution is an important means of optimizing SQL. In the MySQL database, you can enable SQL profiling by configuring the profiling parameter. When this parameter is turned on, subsequent SQL statements will record their resource costs, such as IO, context switches, cpu,memory, and so on.
Open profile:
SET @ @profiling = 1;
See if profile is turned on
SELECT @ @profiling;
1 is on, 0 is off
Only valid for the current session after opening.
When the profile is turned on, the SQL executed will be recorded by the profile
Show profiles; You can see the SQL that is currently logged
View the profile results show profile for query N; the Query_idshow profile in the #n为show profile can see the execution time of each step in the SQL execution plan, as well as CPU, memory, IO consumption
Show profile CPU for query 1; #查看cpu消耗
Show profile block IO for query 1; #查看io消耗
Show profile memory for query 1; #查看cpu
Can also be written together
Show profile CPU, block IO for query 1------------------------------------------------------------------------------- -------------------------------------------------SQL Optimization method: Changing the SQL execution plan
Once we have identified the optimization goals, we need to identify ways to achieve our goals. For SQL statements, there is only one way to achieve the above 2 goals, that is to change the SQL execution plan, let him try to "less detours", as far as possible through a variety of "shortcuts" to find the data we need to achieve "reduce the number of IO" and "reduce CPU computing" goal.
Join as little as possible
The advantage of MySQL is simplicity, but it's also a disadvantage in some ways. The MySQL optimizer is efficient, but because of its limited amount of statistical information, the optimizer is more likely to deviate from the work process. For a complex multi-table Join, on the one hand due to its optimizer constraints, and also in the Join this aspect of the effort is not enough, so performance from the Oracle and other relational database predecessors still have a certain distance. But if it is a simple single-table query, this gap will be very small even in some scenarios to better than these database predecessors.
Try to be less ordered
Sorting operations consume more CPU resources, so reducing the ordering can significantly affect SQL response time in scenarios where the cache hit ratio is high enough for the IO capability.
For MySQL, there are several ways to reduce sorting, such as:
Optimize by using index to sort the way
Reduce the number of record entries in the sort
No need to sort data
Try to avoid select *
SELECT * Generally causes a full table scan, showing all columns, and the fields required for select.
Try to use join instead of subquery
While Join performance is poor, there is a significant performance advantage over MySQL subqueries. MySQL's sub-query execution plan has been a big problem, although this problem has existed for many years, but has been released by all the stable version of the widespread, has not been much improved. While the authorities have recognized this issue early and pledged to resolve it as soon as possible, at least we have not yet seen which version of the issue has been better solved.
Try to use UNION ALL instead of union:
The difference between Union and union all is that the former needs to merge two (or more) result sets and then perform a unique filtering operation, which involves sorting, adding a lot of CPU operations, and increasing resource consumption and latency. So when we can confirm that it is not possible to duplicate a result set or do not care about repeating the result set, try to use union all instead of union.
Disable foreign keys
----------------------------------------------the basic principles of SQL optimization to avoid large SQL
One SQL can only run on one CPU
In high concurrency environments, large SQL can easily affect performance issues
Maybe a big SQL to kill the database
Split SQL
Keep things short and concise
Ready-to-use, out-of-the-box
Irrelevant operation kicks out the transaction and reduces resource consumption
To maintain consistency, split transactions
Avoid high-volume updates
Avoid peaks
Daytime speed limit
Plus sleep
Avoid type conversions
Avoid excessive data, it is recommended to use limit
Avoid operations such as mathematical operations, function calculations, and logical judgments in SQL statements
Avoid or
Same field, recommended in
Different fields, recommended union
Prioritize high-concurrency SQL rather than low-frequency execution some "big" sql
Optimize from a global perspective, rather than one-sided adjustment
Try to explain each SQL that runs in the database whenever possible.
Design principles for-------------------------------------------------------------Indexes 1. The index column of the search is not necessarily the column to be selected, in other words, the column that is most appropriate for the index appears in the Where The column in the clause, or the column specified in the JOIN clause, instead of the column that appears in the select list after the SELECT keyword.
2. Use a unique index to consider the distribution of values in a column. For columns with unique values, the index works best, and columns with multiple duplicate values have the worst indexes.
3. Use a short index. If you index a string, you should specify a prefix length, which you should do whenever possible. For example, if you have a char (200) column, and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Indexing the first 10 or 20 characters can save a lot of index space, and may make queries faster.
4. Use the leftmost prefix. When you create an index of n columns, you are actually creating n indexes that MySQL can use. Multiple-column indexes can serve several indexes because the leftmost Lie in the index can be used to match rows. Such a column set is called the leftmost prefix.
5. Do not over-index. Do not assume that the index "the more the better", everything is indexed is wrong. Each additional index consumes additional disk space and reduces the performance of write operations, as we have described earlier. When you modify the contents of a table, the index must be updated, and sometimes refactoring may be required, so the more indexes you have, the longer it takes. If an index is seldom exploited or never used, it will unnecessarily slow down the table's modification speed. In addition, MySQL takes time to consider each index when generating an execution plan. Creating redundant indexes has brought more work to query optimization. Too many indexes may also make MySQL choose the best index to use. Maintaining only the desired index is advantageous for query optimization. If you want to add an index to an indexed table, you should consider whether the index you want to increase is the leftmost index of an existing multi-column index. If so, don't bother to increase the index because it's already there.
-------------------------------------------------primary key index, a primary key index is automatically added to the primary key
Unique index, data is unique when combined with a unique index, which is a unique constraint, query efficiency with a unique index is faster than normal and multi-column indexes
Single-column index (normal index), can be added on any columns
Multi-column index (combined index), if there are multiple columns behind the where sentence, or if the data is duplicated, it is necessary to add multiple column indexes to avoid updating the columns of the composite index, because the indexes are re-maintained after the data is updated.
Note: Modifying the table structure will invalidate the index and need to refactor the index
MySQL monitoring and tuning---turn
