The index

What is the index

An Index is a data structure that helps MySQL obtain data efficiently. You get the essence of an index: an index is a data structure. It can be simply understood as a fast, ordered lookup data structure. In addition to the data, the database system maintains data structures that satisfy specific lookup algorithms, and that reference (point to) the data in a way that makes it possible to implement advanced lookup algorithms on these data structures. This data structure is called an index. Indexes themselves are generally too large to be stored in memory, so indexes are often stored on disk in the form of index files.

The advantages and disadvantages

advantages

  • Improve the efficiency of data retrieval and reduce the IO cost of database.
  • Sorting data through indexed columns reduces the cost of sorting data and CPU consumption.

disadvantages

  • While indexes greatly speed up queries, they slow down the speed of updating tables, such as INSERTS, UPDATES, and DELETES. MySQL will update the index file every time it updates a column that has been added to the index.
  • The index is actually a table that holds primary keys and index fields and points to records in the entity table, so index columns also take up space.

B-tree and B + Tree

The difference between

  1. Keywords and records of b-tree are put together. Leaf nodes can be regarded as external nodes and contain no information. The non-leaf node of B+Tree contains only the keyword and the index pointing to the next node, and the record is only placed in the leaf node.
  2. In b-tree, the search time of records closer to the root node is faster. The existence of records can be determined as long as the keyword is found. However, in B+Tree, the search time of each record is basically the same, which needs to go from the root node to the leaf node, and the keywords need to be compared in the leaf node. From this point of view, the performance of b-tree seems to be better than that of B+Tree, but in practice, the performance of B+Tree is better. The non-leaf nodes of A B+Tree do not store actual data. Therefore, each node can contain more elements and the Tree height is smaller than that of a B-tree. In this way, disk access times are reduced. Although B+Tree takes more comparisons to find a record than B+Tree, a single disk access takes hundreds of memory comparisons, so in practice B+Tree performance may be better, and the leaf nodes of B+Tree are linked together using Pointers. B+Tree is convenient for sequential traversal (for example, viewing all files in a directory, all records in a table, etc.), which is why many databases and file systems use B+Tree.

Why B+Tree is more suitable for file index and database index of operating system than B-tree?

  1. The disk read and write costs of B+Tree are lower

The internal node of B+Tree does not have a pointer to the specific information of the keyword. Therefore, its internal nodes are smaller than b-tree. If all the keywords of the same internal node are stored in the same disk block, then the disk block can contain more keywords. Read into memory at a time to find more keywords. The number of IO reads and writes is relatively low.

  1. The B+Tree query efficiency is more stable

Because non-endpoints are not the nodes that ultimately point to the contents of the file, they are only the indexes of the keywords in the leaf nodes. So any keyword lookup must take a path from root to leaf. The length of all keyword query paths is the same, resulting in the same query efficiency of each data.

The index classification

  1. Single-value index: that is, an index contains only a single column. A table can have multiple single-column indexes
    • When you build a table, addThe key (column name)The specified
    • Created separately,Create index Index name on Table name (column name)
    • Created separately,Alter table alter table add index
  2. Unique index: The value of the indexed column must be unique, but null is allowed and null can occur more than once
    • When you build a table, addUnique (column name)The specified
    • Created separately,Create unique index idx_idx_id on
    • Created separately,Alter table alter table add unique alter table add unique
  3. Primary key index: InnoDB is a clustered index. The value must be unique and cannot be null
    • When you build a table, addPrimary key(column name)The specified
  4. Compound index: That is, an index contains multiple columns
    • When you build a table, addKey (list of column names)The specified
    • Created separately,Create index Index name on Table name (column name list)
    • Created separately,Alter table alter table add index

The index of grammar

  1. Create, syntax as above
  2. delete
    • Primary key index:Alter table TABLE name drop primary key
    • Non-primary key index:Drop Index Index name ON Indicates the name of the table
  3. To view
    • Show index from table name

This is suitable for creating indexes

  • The primary key automatically creates a unique index;
  • Fields that are frequently queried should be indexed
  • Query fields associated with other tables are indexed by foreign key relationships
  • Single key/composite index choice problem, composite index more cost-effective
  • A sorted field in a query that can be accessed through an index to speed sorting
  • Statistics or group fields in the query

Not suitable for creating indexes

  • Too few table records
  • A table or field that is frequently added or deleted
  • Indexes are not created for fields that are not needed in the WHERE condition
  • Those with bad filters are not suitable for indexing

Explain Performance analysis

Introduction to the

What is the

View the execution plan: Use the EXPLAIN keyword to simulate the optimizer’s execution of SQL queries to see how MySQL processes SQL statements. Analyze performance bottlenecks in query statements or table structures.

Can do

  • The read order of the table
  • Operation type of data read operation
  • Which indexes are available
  • Which indexes are actually used
  • References between tables
  • How many rows per table are queried by the optimizer

How to play

Explain + SQL statements.

Explain Information returned after execution:

Description of each field

  1. Id: Serial number of the SELECT query, containing a set of numbers indicating the order in which the SELECT clause or operation table is executed in the query.
    • The ids are the same and the execution sequence is from top to bottom
    • The ID is different. For subqueries, the ID sequence increases. A larger ID has a higher priority and is executed earlier
    • If the ids are the same, they can be considered as a group and executed from top to bottom. In all groups, the greater the ID value, the higher the priority and the earlier the execution

    Id Number Each number indicates an independent query. The fewer times an SQL query runs, the better.

  2. Select_type: indicates the query type. It is used to distinguish the common query, joint query, and subquery complex queries. The values are as follows:
    • Simple: A simple select query that does not contain subqueries or unions
    • Primary: The outermost query is marked as primary if it contains any complex subparts
    • Derived: Subqueries contained in the FROM list are labeled as derived, and MySQL executes these subqueries recursively, putting the results in temporary tables.
    • Subquery: Contains subqueries in a SELECT or WHERE list
    • Depedent SubQuery: Contains subqueries in a SELECT or WHERE list, based on the outer layer
    • Uncacheable subQuery: a subquery that cannot be cached
    • Union: If the second SELECT appears after the union, it is marked as union; If UNION is included in the subquery of the FROM clause, the outer SELECT will be marked: DERIVED
    • Union result: SELECT the result from the union table
  3. Table: Which table this data is based on.
  4. Type: indicates the access type of the query. Is a relatively important indicator, and the result value from the best to the worst is: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index < span style = “max-width: 100%; clear: both; min-height: 1px;

    Just remember: system > const > eq_ref > ref > range > index > ALL. Others are not common.

    • System: The table has only one row (equal to the system table). This is a const column that does not normally occur and can be ignored.
    • Const: indicates that the index was found once, and const is used to compare primary key or unique indexes. Because it only matches one row, it’s fast. By placing the primary key 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. Common for primary key or unique index scans.
    • Ref: a non-unique index scan that returns all rows matching a single value. It 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 lookup and scan.
    • Range: Retrieves only rows in a given range, using an index to select rows. An index scan is better than a full table scan because it only needs to start at one point in the index and end at another point without scanning all the indexes.
    • Index: Indicates that the SQL uses an index but does not filter it. It usually overwrites an index or sorts groups by an index.
    • All: Traverses the table to find matching rows.

    Other types are as follows:

    • Index_merge: A combination of indexes is required during a query, usually in SQL with the OR keyword.
    • Ref_or_null: For a field that requires both filtering conditions and null values. The query optimizer will choose to query with ref_OR_NULL join.
    • Index_subquery: Associates subqueries with indexes instead of full table scans.
    • Unique_subquery: This join type is similar to index_subquery. A unique index in a subquery.
  5. Possible_keys: Displays one or more possible indexes that can be applied to the table. If an index exists on a field involved in the query, the index is listed but not necessarily used by the query.
  6. Key: indicates the actual index. If NULL, no index is used.
  7. Key_len: Indicates the number of bytes used in the index. This column is used to calculate the length of the index used in the query. Key_len displays a value for the maximum possible length of the index field, not the actual length used. How do I calculate key_len?
    • Int =4; int=4; varchar(20)=20; char(20)=20
    • For a string field such as vARCHar or char, the character set is multiplied by different values, such as utF-8 by 3 and GBK by 2
    • The dynamic string vARCHar is added by 2 bytes
    • Fields that are allowed to be empty are added by 1 byte
  8. Ref: Shows which column of the index is used, if possible, as a constant. Which columns or constants are used to find values on index columns.
  9. Rows: Displays the number of rows that MySQL thinks it must check when executing a query. Less is better!
  10. Extra important information.
    • Using filesort: indicates that mysql uses an external index sort for data, instead of reading data in the order of the indexes in the table. A sort operation in MySQL that cannot be done with an index is called “file sort”. Sorting fields can be accessed through indexes to improve sorting speed.
    • Using temporary: Use temporary tables to hold intermediate results. MySQL uses temporary tables when sorting query results. Common in sort order by and group by queries.
    • Using index: the corresponding select operation Using index (Covering index), avoid access to the table row, efficiency is good! Using WHERE indicates that the index is used to perform a key lookup. If using WHERE is not present at the same time, it indicates that the index is used to read data rather than perform a lookup using the index.
    • Using WHERE: Indicates that where filtering is used.
    • Using join buffer: The join buffer is used.
    • Impossible WHERE: The value of the where clause is always false and cannot be used to retrieve any data.
    • Select Tables Optimized Away: In the absence of the group by clause, optimization of MIN/MAX operations based on indexes or COUNT(*) operations for MyISAM storage engines does not have to wait until the execution stage to perform the calculation. Optimization is completed at the generation stage of the query execution plan.
    • Distinct: Optimizes the DISTINCT operation to stop searching for the same value after finding the first matched ancestor.

Index failures are common when indexes are used in a single table

The Mysql version in the following is 5.6.

SQL > create table user;

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT 'name',
  `age` int(3) NOT NULL COMMENT 'age',
  `address` varchar(255) NOT NULL COMMENT 'address',
  `email` varchar(255) NOT NULL COMMENT 'mail',
  `mobile` varchar(255) NOT NULL COMMENT 'Mobile number'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
Copy the code

SQL > create index;

create index idx_user_nameAgeEmail on user(name,age,email);
Copy the code

Full value matches my favorite

I like to refer to: query fields in order in the index can be matched!



Can you see that all the indexes are used, if I change the order, does it still hold?



Order of query fields in SQL,It has nothing to do with the order in which the fields in the index are used. The optimizer automatically optimizes without affecting the results of SQL execution.

Optimal left prefix rule



A difference in the order of query fields and index fields can cause the index to be underused or even invalidated!

Reason: With composite indexes, you need to follow the best left prefix rule, that is, if you index multiple columns, follow the left-most prefix rule. It refers to a queryStart at the left-most front of the index and do not skip columns in the index.

Conclusion: The filtering conditions must be met in the order in which the index is created. Once a field is skipped, the fields behind the index cannot be used.

Do not do any calculations on index columns



Not doing anything on the index column (calculation, function, (automatic or manual) type conversion) will cause the index to fail and move to a full table scan.

There cannot be range queries on index columns



Suggestion: Place the index order of possible range queries last

Use overwrite indexes whenever possible



Do not write select *!

Use does not equal (! = or <>)



In use does not equal (! = or <>), the index cannot be used and a full table scan will result.

Is not NULL and is NULL sometimes indexes fail

Add a nullable field and create a single-valued index:

alter table user add column remark varchar(255) comment 'note';
create index idx_user_remark on user(remark);
Copy the code



Before filling the remark field, the result is as follows:



The remark field fills the data, leaving only one row null, and the result is as follows:



After the remark field is filled in, the result is as follows:



Is NULL and is not NULL!

Before and after fuzzy matching of like



Wildcards are placed on the right as far as possible! Otherwise, the index is invalid.

Reduce the use of OR



Using OR will invalidate the index. Use union all or union instead.

practice

Let’s say index(a, b, c).

Where clause Whether the index is in use
where a=3 Y, apply to a
where a=3 and b=5 Y applies to a and B
where a=3 and b=5 and c=4 Y applies to a, B, and C
Where b=3 or where b=3 and c=4 or where c=4 N, no A
where a=5 and c=3 Y applies to A, but c does not, and B breaks in the middle
where a=3 and b>4 and c=5 Y applies to a and B, but c doesn’t. B is the range
where a=3 and b like ‘kk%’ and c=4 Y applies to a, B, and C
where a=3 and b like ‘%kk’ and c=4 Y, apply to a
where a=3 and b like ‘%kk%’ and c=4 Y, apply to a
where a=3 and b like ‘k%kk%’ and c=4 Y applies to a, B, and C

formula

Full value match my favorite, most left prefix to obey; The first brother cannot die, the middle brother cannot be broken; Less calculation on index column, all invalid after range; * * * * * * * * * * Unequal null values and OR, index failure should be used less; VAR quotes can not be lost, SQL optimization tips!

Associated query optimization

7 kinds of the join

left join

explain select * from class left join book on class.card=book.card;
Copy the code

Mysql > select * from class where index (‘ book ‘, ‘class’);



Create index of card field in class and book respectively, and find:

  1. When optimizing associated queries, only indexes on the driven table are valid!
  2. When left JOIN, the left is the driver table, and the right is the driven table!

So you should index the book table.

right join

Create index on left JOIN table (left join table, left join table)

inner join

explain select * from a inner join b on a.card=b.card;

explain select * from b inner join a on b.card=a.card;
Copy the code

Inner join: mysql will select the small result set table as the driver table for you.

summary

  1. Minimize the total number of NestedLoop loops in join statements: “Always drive large result sets with small ones.”
  2. The inner loop of NestedLoop is optimized first.
  3. Ensure that join condition fields on the driven table in the JOIN statement are indexed.
  4. If you cannot guarantee that the join condition fields of the driven table are indexed and the inner layer resources are sufficient, do not skimp on the JoinBuffer Settings.

SQL optimization

Bulk data insertion

When using the load command to import data, proper Settings can make the import more efficient.

  1. Primary key order insertion: Since InnoDB-type tables are stored in primary key order, data is imported in primary key order, which can effectively improve the efficiency of data import.
  2. Disable uniqueness check: Perform this operation before importing dataSET UNIQUE_CHECKS=0Turn off the uniqueness check and execute it after the importSET UNIQUE_CHECKS=1 恢

Complex uniqueness check can improve the efficiency of import. 3. Manually commit transactions: If the application uses automatic commit, you are advised to run SET AUTOCOMMIT=0 to disable automatic commit before the import and then run SET AUTOCOMMIT=1 to enable automatic commit after the import to improve the import efficiency.

Optimizing insert statements

  1. Insert many rows at the same time: Try to use insert statements with more than one value table, which greatly reduces the cost of connecting, closing, and so on between the client and the database. Makes efficiency faster than a single INSERT statement executed separately.
# Primitive modeinsert into tb_test values(1.'Tom');
insert into tb_test values(2.'Cat');
insert into tb_test values(3.'Jerry'); # optimizationinsert into tb_test values(1.'Tom'), (2.'Cat'), (3.'Jerry');
Copy the code
  1. Insert data into a transaction
start transaction;
insert into tb_test values(1.'Tom');
insert into tb_test values(2.'Cat');
insert into tb_test values(3.'Jerry');
commit;
Copy the code
  1. Sequential insertion of data
# Primitive modeinsert into tb_test values(4.'Tim');
insert into tb_test values(1.'Tom');
insert into tb_test values(3.'Jerry');
insert into tb_test values(5.'Rose');
insert into tb_test values(2.'Cat'); # optimizationinsert into tb_test values(1.'Tom');
insert into tb_test values(2.'Cat');
insert into tb_test values(3.'Jerry');
insert into tb_test values(4.'Tim');
insert into tb_test values(5.'Rose');
Copy the code

Optimize the order by statement

Two kinds of sorting

  1. The first method is to sort the returned data by filesort sort. All sorts that do not return the sorted results directly by index are called filesort sort.
  2. The second method directly returns the ordered data through the ordered index sequential scan. In this case, using index does not need additional sorting and the operation efficiency is high.

The optimization goal

Now that you know how MySQL sorts, the optimization goal is clear: minimize additional sorts and return ordered data directly through the index.

  1. Where condition uses the same index as order BY;
  2. And the order by is the same as the index order;
  3. And the order by fields are either in ascending order or in descending order.

Otherwise, you would definitely need to do something extra, which would result in FileSort. For example, in the following test, the user table contains a composite index in the order name,age,email:

  1. The remark field in order BY does not have an index, so filesort
  2. An index is used in where, and the age field in order BY follows name in the index, so it is not filesort
  3. Where the use of the index, and the order by the email field behind the index with age, but the age range queries, lead to the back of the index fails, so is filesort (more popular: The data whose name is Jack and age is greater than 10 were queried. At this time, there are many ages. The mailboxes in a single age are in order. A composite index is sorted by name, age, and email.
  4. In order by age, it is not filesort

  1. 3 again
  2. The age and email fields in order BY are followed by name and age respectively in the index, but the result of filesort is one ascending order and one descending order
  3. Where index is used, but the order by field is out of order, resulting in filesort

  1. SQL > create remark single-value index filesort

Optimize filesort

It is possible to reduce the presence of Filesort by creating appropriate indexes, but in some cases, it is necessary to speed up the sorting operation of Filesort. MySQL has two sorting algorithms for Filesort:

  1. Double scan algorithm: before MySQL 4.1, this method was used to sort. Sort (sort buffer, sort buffer); if sort buffer is not enough, store the sort result in temporary table. Once sorted, the records are read back into the table based on the row pointer, which can result in a large number of random I/O operations.
  2. Single-scan algorithm: take out all the fields that meet the conditions at one time, and then output the result set directly after sorting in sort buffer. The memory cost of sorting is high, but the sorting efficiency is higher than that of two-scan algorithm.

Select * from max_length_FOR_sort_data; select * from max_length_FOR_sort_data; select * from max_length_for_sort_data; Otherwise use the first. Sort_buffer_size and max_LENGTH_FOR_SORT_DATA system variables can be appropriately raised to increase the size of the sort area and improve sorting efficiency.

Optimize the group by statement

Since GROUP BY actually does the same sort operation, and compared with ORDER BY, GROUP BY mainly only has more grouping operations after sorting. Of course, if other aggregate functions are used in grouping, then some calculation of aggregate functions is required. Therefore, indexes can be used in the implementation of GROUP BY as well as ORDER BY.

Group by uses indexes in almost the same way as ORDER BY, except that group BY can use indexes without filtering conditions.



If the query contains group by but the user wants to avoid consumption of the sort result, then order by NULL can be used to disable the sort.

Optimize nested queries

Mysql 4.1 supports SQL subqueries. The technique uses a SELECT statement to create a single column of query results, which can then be used as a filter in another query. Using subqueries allows you to do many SQL operations at once that would logically require multiple steps, avoid transactions or table locks, and make it easy to write. However, in some cases, subqueries can be replaced by more efficient joins.



Join queries are more efficient because MySQL does not need to create temporary tables in memory to perform a logical two-step query.

When determining the scope, do not use not in and not exists. Use left join on XXX is null instead.

Optimized OR condition

For a query clause that contains OR, if you want to use an index, each condition column between OR must use the index, and no compound index can be used. If there is no index, you should consider adding an index. It is recommended to use union instead of OR.

Optimized paging query

In general paging queries, performance can be improved by creating overwrite indexes. MySQL > select * from ‘limit 200000010’ where ‘limit 200000010’ = ‘limit 2000001-2000010’;

select * from tbl_item limit 2000000.10;
Copy the code

Optimization idea 1

Sort paging is performed on the index, and then the primary key is associated back to the original table to query the other column contents.

select * from tbl_item a join (select id from tbl_item limit 2000000.10) b on a.id=b.id;
Copy the code

Optimization Idea 2

This scheme applies to tables with self-increasing primary keys and can convert Limit queries into queries at a certain location.

select * from tbl_item where id> =2000001 limit 10;
Copy the code

Using SQL prompts

SQL prompt is an important means to optimize the database. In simple terms, it is to add some artificial prompts in THE SQL statement to achieve the purpose of optimizing the operation.

use index

ignore index

force index

Other optimization

  1. Avoid select * and select as many columns as you need
  2. Avoid using in and not in
  3. Use numeric fields whenever possible
  4. Never use in if you can use between
  5. A group by is not a group by
  6. If you can use union all, don’t use union
  7. Field data type optimization: Avoid null; Use smaller fields whenever possible; Fixed length is preferred
  8. Avoid multiple table associations

Slow Query logs

If the production environment is faulty, how to tune it?

  1. Enable slow query logs and capture slow query SQL
  2. Use Explain + slow SQL analysis
  3. Show Profiles Queries the execution details and life cycle of SQL on the MySQL server
  4. MySQL database parameters were tuned

What is slow query logging?

  1. The slow query log of MySQL is used to record statements whose response time exceeds the threshold. In particular, SQL statements whose response time exceeds the value of long_query_time are recorded in the slow query log.
  2. The default value of long_query_time is 10, which means that statements that run for more than 10 seconds are logged to the slow query log.
  3. Through slow query log to check which SQL exceeded our maximum endurance time value, for example, an SQL execution more than 5 seconds is considered as slow SQL, hope to collect more than 5 seconds OF SQL, combined with previous explain for comprehensive analysis.

How does it work?

By default, slow query logging is disabled for the MySQL database. You need to set this parameter manually. Of course, it is generally not recommended to enable this parameter unless it is needed for tuning purposes. This is because slow log query may affect performance to some extent. Slow query logs can be written to a file. This mode takes effect temporarily and becomes invalid after the database is restarted. You are advised to use this mode and disable the slow log query function as follows:

# Check whether the slow query log function is enabledshow variables like '%slow_query_log%'; # enable slow query log1Open;0Shut downset global slow_query_log=1; Set the path for saving slow query logsset global slow_query_log_file='/var/lib/mysql/test-slow.log'; Set the threshold for viewing slow queries in secondsshow variables like '%long_query_time%'; Set the slow query threshold in secondsset global long_query_time=5; # long_query_time # long_query_time # long_query_time1. show global variables like '%long_query_time%';
# 2.Re-open a session and you'll seeCopy the code

[mysqld] in my.cnf:

[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/test-slow.log
long_query_time=5
log_output=FILE
Copy the code

Wait for the system to run for a while to view the slow SQL in the log file and tune it!

Show global status like '%Slow_queries%';Copy the code

Log analysis tool

While manually viewing slow query logs can be time-consuming and physically demanding, MySQL provides the log analysis tool mysqlDumpslow.

See help for mysqlDumpslow, executed on a Linux servermysqldumpslow --helpCommand, the following information is displayed:



Important parameters are listed below:

  1. -s ORDER: Indicates the ORDER mode. The ORDER mode is at by default. The options are as follows:
    • Al: Average lock time
    • Ar: average number of returned records
    • At: indicates the average query time
    • C: Access times
    • L: Lock time
    • R: Returns a record
    • T: Query time
  2. -t NUM: Returns the first NUM data
  3. -g PATTERN: matches regular expressions and is case-insensitive

Common log analysis statements:

Get the maximum 10 SQL statements that return recordsets
mysqldumpslow -s r -t 10 /var/lib/mysql/test-slow.log
Get the 10 most frequently accessed SQL statements
mysqldumpslow -s c -t 10 /var/lib/mysql/test-slow.log
Select * from left join (s) where left join (s) are in order by time
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/test-slow.log
# additional Suggestions when using these commands | and more use, otherwise likely explodes the screen
mysqldumpslow -s r -t 10 /var/lib/mysql/test-slow.log | more
Copy the code

show profile

What is?

Mysql provides resources that can be used to analyze resource consumption for statement execution in the current session. Can be used for SQL tuning measurement, the website explains. By default, the parameter is disabled and the results of the last 15 runs are saved by default.

How does it work?

  1. Check whether the current MySQL version supports:show variables like '%profiling%';
mysql> show variables like '%profiling%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | have_profiling | YES | # # support | profiling | OFF | function is OFF by default | profiling_history_size | | 15 # default record in recent 15 SQL + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 2 rows in set (0.02sec)Copy the code
  1. Enable the function, which is disabled by default. Enable the function before using:set profiling=1;
  2. Run a few SQL
  3. View the results:show profiles;
mysql> show profiles; +----------+------------+-----------------------------------+ | Query_ID | Duration | Query | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | | 0.00074700 show variables like '% profiling %' | | 2 0.00059550 | | select * from user | | 3 | 0.02182150 now () | | the select + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 3 rows in the set (0.04 SEC)Copy the code
  1. Diagnosis of SQL:show profile cpu,block io for query Query_ID, Query_ID is the result of the previous query
    1. All the information that can be displayed
      • ALL: indicates ALL cost information
      • BLOCK IO: costs related to BLOCK I/O operations
      • CONTEXT SWITCHES: Information about costs related to CONTEXT SWITCHES
      • CPU: indicates the CPU cost
      • IPC: Send and receive related cost information
      • MEMORY: indicates the MEMORY costs
      • PAGE FAULTS: PAGE fault-related overhead information
      • SOURCE: Source_function, Source_file, and Source_line Cost information
      • SWAPS: Cost information related to the number of SWAPS
mysql> show profile cpu,block io for query 2; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | starting 0.000066 | | 0.000036 0.000024 | | 0 0 | | | checking permissions | | | | | 0 0 0.000006 0.000011 0.000018 | | Opening tables | 0.000026 0.000016 0.000011 | | | | 0 0 | | init | | | | | 0 0 0.000039 0.000000 0.000027 | | System lock | | 0.000032 0.000000 0.000019 | | 0 0 | | | optimizing | | | | | 0 0 0.000016 0.000000 0.000016 | | statistics | | 0.000000 0.000028 | | | 0 0 0.000028 | | preparing | | | | | 0 0 0.000021 0.000000 0.000022 | | executing | | | 0.000000 0.000015 0.000015 | | 0 0 | | Sending data | | | | | 0 0 0.000176 0.000000 0.000175 | | end | | | | 0 0.000016 0.000000 0.000017 | 0 | | | | | | 0 0.000017 0.000000 0.000017 query end | 0 | | closing tables | | | | | | 0 0 0.000020 0.000000 0.000020 | freeing the items | | | | | 0 0 0.000049 0.000000 0.000049 | | cleaning up | | | | | | 0 0 0.000070 0.000000 0.000070 + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 15 rows in the set (0.06 SEC)Copy the code
  1. Status conclusions to note (bad) :
    • Converting HEAP to MyISAM: Query result is too large, not enough memory, moving to disk
    • Creating TMP table: Creating a temporary table may cause data to be copied to the temporary table and then deleted, which is inefficient
    • Copying to TMP tables on disk: Dangerous!!
    • Locked:

General query process

Mysql client establishes connection with mysql server through protocol, sends query statement, first checks query cache, if match, directly returns the result, otherwise carries on statement parsing, that is, before parsing query, The server first accesses the Query cache, which stores SELECT statements and the corresponding query result set. If a query result is already in the cache, the server will no longer parse, optimize, and execute the query. It simply returns the cached results to the user, which greatly improves the performance of the system. Syntax parsers and preprocessing: First mysql parses SQL statements by keywords and generates a corresponding “parse tree”. The mysql parser validates and parses queries using mysql syntax rules; The preprocessor further checks that the parsed number is valid based on some mysql rules. The query optimizer parses the tree when it is considered legitimate and converts it into an execution plan by the optimizer. A query can be executed in many ways, all returning the same result. The optimizer’s job is to find the best execution plan. However, mysql uses the B+TREE index by default, and a general direction is that no matter what you do with SQL, at least for now, mysql only uses one index in a table at most.

The execution order of SQL

Handwriting order:

select distinct <select_list>
from <left_table>
<join_type> join <right_table> on <join_condition>
where <where_condition>
group by <group_by_list>
having <having_condition>
order by <order_by_condition>
limit <limit_number>
Copy the code

The actual execution order, with the update of Mysql version, its optimizer is also constantly upgraded, the optimizer will analyze the performance consumption caused by different execution order and dynamically adjust the execution order. Here is the order of the most common queries:

from <left_table>
on <join_condition>
<join_type> join <right_table>
where <where_condition>
group by <group_by_list>
having <having_condition>
select
distinct <select_list>
order by <order_by_condition>
limit <limit_number>
Copy the code

Global Query Logs

Configuration to start

Mysql my.cnf mysql my.cnf

General_log_file =/path/logfile/log_output=FILECopy the code

Code to enable

set global general_log=1;
set global log_output='TABLE';
Copy the code

Select * from mysql.general_log; select * from mysql.general_log; Command to see

Never use it in a production environment!

Some of the above content is taken from Silicon Valley MySQL Advanced and dark horse programmer MySQL Advanced