View table related commands

  • View table structure desc table name
  • Run the SQL show create table command to query the table name
  • Check the index show index from the table name

With and without indexes Because indexes are designed to speed up searches, query efficiency can go through the roof with indexes.

There is an index

mysql> select * from tb1 where name = 'zhangqiye'; +-----+-------------+---------------------+----------------------------------+---------------------+ | nid | name | email | radom | ctime | +-----+-------------+---------------------+----------------------------------+---------------------+ | 889 | zhangqiye |  [email protected] | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 | +-----+-------------+---------------------+----------------------------------+---------------------+ 1 rowin set (0.00 sec)
Copy the code

There is no index

mysql> select * from tb1 where email = '[email protected]'; +-----+-------------+---------------------+----------------------------------+---------------------+ | nid | name | email | radom | ctime | +-----+-------------+---------------------+----------------------------------+---------------------+ | 889 | zhangqiye |  [email protected] | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 | +-----+-------------+---------------------+----------------------------------+---------------------+ 1 rowin set (1.23 sec)
Copy the code

Adding an index to a database table can indeed speed up queries, but only if the index is used correctly; if it is used incorrectly, even indexing will not work. An index that does not take effect even if it is created:

  • like ‘%xx’ select * from tb1 where name like ‘%cn’;

  • Select * from tb1 where name = ‘zhangqiye’;

  • or select * from tb1 where nid = 1 or email = ‘[email protected]’; Select * from tb1 where nid = 1 or name = ‘seven’; select * from tb1 where name = ‘seven’; select * from tb1 where nid = 1 or email = ‘[email protected]’ and name = ‘zhangqiye’

  • Inconsistent type If the column is a string, the passing condition must be enclosed in quotes, otherwise… select * from tb1 where name = 999;

  • ! = select * from tb1 where name ! = ‘zhangqiye’ select * from tb1 where nid! = 123

  • Select * from tb1 where name > ‘zhangqiye’ Select * from tb1 where nID > 123 select * from tb1 where num > 123 select * from tb1 where num > 123

  • order by select email from tb1 order by name desc; Select * from tb1 order by nid desc; select * from tb1 order by nid desc;

  • Combination index leftmost prefix if the combination index is :(name,email) name and email — use index name — use index email — do not use index

Other Matters needing attention

  • Avoid using select *
  • Count (1) or count(column) instead of count(*)
  • Char instead of vARCHAR whenever possible when creating a table
  • Order of fields in a table Fields of fixed length take precedence
  • Composite indexes replace multiple single-column indexes (often when multiple conditional queries are used)
  • Use short indexes whenever possible
  • Use joins instead of sub-queries
  • When connecting tables, ensure that the condition types are consistent
  • Index hash values (with few duplicates) are not suitable for index construction, for example: gender is not suitable

Limit pagination whether there is an index or not, limit pagination is a concern. When mysql uses limit pagination, the query efficiency decreases as the page number increases.

Use limit start, count page statements directly

select * from product limitSelect * from product (select * from product); select * from product (select * from product)limitSelect * from product where product = 'productlimit100, 20 0.016 secs select * from productlimitSelect * from product (select * from productlimit10000, 20 of 0.094 secondsCopy the code

We’ve already seen that as the start log increases, the time increases, which means that the page limit has a lot to do with the start page number, Select * from product limit 400000, Select * from product limit 866613, 20 37.44 secs select * from product limit 866613, 20 37.44 secs We can also draw two things from it:

1)limitThe query time of the statement is proportional to the position of the start recordlimitStatement is convenient, but not suitable for direct use on tables with many records.Copy the code

Performance optimization for the limit paging problem uses a table’s overwrite index to speed up paging queries. As we all know, queries that use an index query that contains only that index column (overwrite index) are queries that are fast. Because there are optimization algorithms using index lookup, and the data is on the query index, there is no need to find the relevant data address, which saves a lot of time. Mysql also has an index cache, which works best when concurrency is high. In our example, we know that the ID field is the primary key and naturally contains the default primary key index. Now let’s see how the query works with an overridden index:

Select * from product; select * from product; select * from productlimit866613, 20 0.2 seconds compared to 37.44 seconds for all columns, which is about 100 times faster. So if we also want to query all columns, there are two methods, one is the form of id>=, the other is the use of JOIN, let's look at the actual situation:  SELECT * FROM product WHERE ID > =(select id from productlimit 866613, 1) limitSELECT * FROM product a JOIN (SELECT id FROM productlimit866613, 20) b ON a.id = b.id query time is also very short, like!Copy the code

In fact, both use the same principle, so the effect is similar

Execution plan Explain + Query SQL – Used to display SQL execution information parameters. You can optimize SQL based on the reference information

Mysql > explain select * from (select nid,name from tb1where nid < 10) as B;
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table      | type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 9 | NULL | | 2 | DERIVED | tb1 | range | PRIMARY | PRIMARY | 8 | NULL |  9 | Usingwhere |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
Copy the code

Result Field Description

Id: query sequence identifier select_type query type SIMPLE SIMPLE query PRIMARY outermost query SUBQUERY mapping to SUBQUERY DERIVED SUBQUERY UNION UNION RESULT RESULT table using UNION: The name of the table being accessedtypeQuery access mode, performance: All < index < range < index_merge < ref_or_null < ref < eq_ref < system/const all < index < range < ref_or_null < ref < eq_ref < system/const all Select * from tb1; select * from tb1; Special: if there arelimitSelect * from tb1 select * from tb1where email = '[email protected]'
select * from tb1 where email = '[email protected]' limit1; Although both statements perform a full table scan, the second statement doeslimit, the scan is stopped after one is found. INDEX select nid from tb1; Select * from tb1 select * from tb1where name < 'zhagsan';
between and
in> >= < <= Operation note:! The = and > symbols INDEX_MERGE merge indexes, using multiple single-column indexes to search for SELECT * from Tb1where name = zhangsan'or nid in (11,22,33); Select * from tb1 where name = 'seven'; Nid,tb1. Name from tB2 left join tb1 on tb2.nid = tb1.nid; CONST constant tables have at most one matching row, because there is only one row, where column values are considered constant by the rest of the optimizer. CONST tables are fast because they only read once. select nid from tb1 where nid = 2 ; SYSTEM The SYSTEM table has only one row (= SYSTEM table). This is a special case of the const join type. select * from (select nid from tb1 where nid = 1) as A; Possible_keys: possible index key: true key_len: index byte length in MySQL Rows MySQL estimate number of rows to read in order to find desired rows ------ just an estimateCopy the code

Slow query The slow query log of MySQL is used to record statements whose response time exceeds the threshold. In particular, THE SQL statements whose response time exceeds the value of long_query_time are recorded in the slow query log. The default value of long_query_time is 10, which means that the statement is run for more than 10 seconds. By default, the Mysql database does not enable slow query logging. You need to manually set this parameter. However, it is not recommended to enable this parameter unless it is necessary for tuning, because slow query logging may affect performance. Slow query logging supports writing log records to files as well as to database tables.

This section describes the parameters of slow query in MySQL

Slow_query_log: indicates whether slow query logs are enabled. 1 indicates that slow query logs are enabled, and 0 indicates that slow query logs are disabled.log-slow-queries: indicates the path for storing slow query logs of the MySQL database of earlier versions (version 5.6 or later). If you do not need to set this parameter, the system provides a default file hostname-slow. log slow-query-log-file: new (5.6 or later) path for storing slow query logs of the MySQL database. Log long_query_time: specifies the slow query threshold. If the query time exceeds the specified threshold, logs are generated. Log_queries_not_using_indexes: Queries that do not use indexes are also recorded in the slow query log (optional). Log_output: indicates the log storage mode. log_output='FILE'Saves logs to a file. The default value is'FILE'. log_output='TABLE'Indicates that logs are saved to the database,Copy the code

The log information is then written to the mysql.slow_log table. The MySQL database supports two log storage modes, which are separated by commas (,), for example, log_output=’FILE,TABLE’. Recording logs in the dedicated log table consumes more system resources than recording logs in files. Therefore, to enable slow query logs and achieve higher system performance, you are advised to record logs in files first.

Mysqld = mysqld; mysqld = my.cnf; mysqld = my.ini;

slow_query_log = 1                          
long_query_time = 2                           
slow_query_log_file = /usr/slow.log       
log_queries_not_using_indexes = 1
Copy the code

Restart the MySQL service

Location of the MySQL configuration file Windows: The Windows configuration file is my.ini. It is usually stored in the MySQL installation directory or C :\Windows. Linux: The Linux configuration file is my.cnf, usually in /etc.