Tips: If you haven't studied the previous part, please study it first.
SQL > alter table table (); SQL > alter table ();Juejin. Cn/post / 705222…
6. Index failure
Index failure (should be avoided)
Create a table
- Build table SQL
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT NULL DEFAULT' ' COMMENT'name',
`age` INT NOT NULL DEFAULT 0 COMMENT'age',
`pos` VARCHAR(20) NOT NULL DEFAULT' ' COMMENT'job',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'Entry Time'
)CHARSET utf8 COMMENT'Staff Record';
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3'.22.'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July'.23.'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000'.23.'dev',NOW());
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
Copy the code- The TEST data in the STAFFS table
mysql> select * from staffs;
+----+------+-----+---------+---------------------+
| id | name | age | pos | add_time |
+----+------+-----+---------+---------------------+
| 1 | z3 | 22 | manager | 2020- 08- 04 14:42:33 |
| 2 | July | 23 | dev | 2020- 08- 04 14:42:33 |
| 3 | 2000 | 23 | dev | 2020- 08- 04 14:42:33 |
+----+------+-----+---------+---------------------+
3 rows in set (0.00 sec)
Copy the code- The compound indexes in the STAFFS table are name, age, pos
mysql> SHOW INDEX FROM staffs;
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+------ --+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+------ --+------+------------+---------+---------------+
| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+------ --+------+------------+---------+---------------+
4 rows in set (0.00 sec)
Copy the code- The compound indexes in the STAFFS table are name, age, pos
mysql> SHOW INDEX FROM staffs;
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+------ --+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+------ --+------+------------+---------+---------------+
| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+------ --+------+------------+---------+---------------+
4 rows in set (0.00 sec)
Copy the code6.2 Index failure criteria
- Full value matches my favorite
- The best left prefix rule: If you index more than one example, follow the left-most prefix rule. Indicates that the query starts at the left-most front of the index and does not skip columns in the index.
- 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
- The storage engine cannot use the column to the right of the range condition in the index
- Try to use overridden indexes (queries that only access the index (the index column is the same as the query column)) and reduce ** SELECT ***
- Mysql does not equal (**! The index cannot be used when =** or <>) causes a full table scan
- Is null, is not null also cannot use index (earlier version cannot run index, later version should be optimized to run index)
- Like begins with a wildcard (‘ % ABC… Mysql index failure is a full table scan operation
- The index of a string without single quotation marks is invalid
- Use or sparingly, as it will break the index when joining
6.2.1. Best left matching rule: The leading brother can’t die, and the middle brother can’t break
- Only lead eldest brother name
- Key = index_staffs_nameAgePos Indicates that the index takes effect
- Ref = const: This constant is the string constant of ‘July’ at query time
mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-------- ---------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-------- ---------------+
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-------- ---------------+
1 row in set (0.00 sec)
Copy the code- Take the lead brother Name and take the little brother Age
- Key = index_staffs_nameAgePos Indicates that the index takes effect
- Ref = const,const: The constants are ‘July’ and 23
mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 23;
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------+------+-- ---------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------+------+-- ---------------------+
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | const,const | 1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------+------+-- ---------------------+
1 row in set (0.00 sec)
Copy the code- Take the lead brother name take the little brother age, little brother age take the little brother pos
- Key = index_staffs_nameAgePos Indicates that the index takes effect
- Ref = const,const,const: July, 23, dev
mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 23 AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+--- ---+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+--- ---+-----------------------+
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+--- ---+-----------------------+
1 row in set (0.00 sec)
Copy the code- Lead brother Name hung up
- Key = NULL indicates that the index is invalid
- Ref = null indicates that the ref is invalid
mysql> EXPLAIN SELECT * FROM staffs WHERE age = 23 AND pos = 'dev';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
Copy the code- The eldest brother name did not hang, the younger brother age ran away
- Key = index_staffs_nameAgePos Indicates that the index is not invalid
- Ref = const indicates that only one constant is used, that is, the second constant (pos = ‘dev’) is not in effect
mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-------- ---------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-------- ---------------+
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-------- ---------------+
1 row in set (0.00 sec)
Copy the code6.2.2 Calculation on index column will cause index invalidation and turn to full table scan
- No operation is performed on the first brother name: key = index_staffs_nameAgePos Indicates that the index takes effect
mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-------- ---------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-------- ---------------+
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-------- ---------------+
1 row in set (0.00 sec)
Copy the code- Use the LEFT function to intercept the substring
- Key = NULL indicates that the index takes effect
- Type = ALL Indicates that a full table scan is performed
mysql> EXPLAIN SELECT * FROM staffs WHERE LEFT(name,4) = 'July';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
Copy the code6.2.3 All shall be invalid after the scope
- An exact match
- Type = ref indicates a non-unique index scan, and the SQL statement will return all rows matching a single value.
- Key_len = 140 indicates the number of bytes used in the index
mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 23 AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+--- ---+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+--- ---+-----------------------+
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+--- ---+-----------------------+
1 row in set (0.00 sec)
Copy the code- Change age to range matching
- Type = range Indicates range scanning
- Key = index_staffs_nameAgePos indicates that the index is not invalid
- Key_len = 78 and ref = NULL indicate that the range search invalidates the following indexes
mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age > 23 AND pos = 'dev';
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-------- ---------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-------- ---------------+
| 1 | SIMPLE | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | NULL | 1 | Using index condition |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-------- ---------------+
1 row in set (0.00 sec)
Copy the code6.2.4 Try to use overwrite indexes (queries that access only the index (the index column is the same as the query column)), reduceselect *
SELECT *The writing of
mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age > 23 AND pos = 'dev';
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-------- ---------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-------- ---------------+
| 1 | SIMPLE | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | NULL | 1 | Using index condition |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-------- ---------------+
1 row in set (0.00 sec)
Copy the code- Extra = Using where; Using index: Using index improves query efficiency
mysql> EXPLAIN SELECT name, age, pos FROM staffs WHERE name = 'July'AND age = 23 AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+--- ---+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+--- ---+--------------------------+
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | Using where; Using index |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+--- ---+--------------------------+
1 row in set (0.00 sec)
Copy the code- Type = ref and Extra = Using where; Using index, although a range search is used in the query criteria, there is no need for a full table scan because we only need to look up the index column
mysql> EXPLAIN SELECT name, age, pos FROM staffs WHERE name = 'July'AND age > 23 AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-------- ------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-------- ------------------+
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using where; Using index |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-------- ------------------+
1 row in set (0.00 sec)
Copy the code6.2.5 mysql in use does not equal (! = or <>) will cause a full table scan
- In the use of! = will <> cause index invalidation:
- Key = null indicates that the index is invalid
- Rows = 3 indicates that a full table scan has taken place
mysql> EXPLAIN SELECT * FROM staffs WHERE name ! = 'July';
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE name <> 'July';
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
Copy the code6.2.6, is null, is not null
- Is null, is not NULL causes index invalidation: key = null indicates index invalidation
mysql> EXPLAIN SELECT * FROM staffs WHERE name is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE name is not null;
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
Copy the code6.2.7, like %
- The index relation of the STAFFS table
mysql> SHOW INDEX from staffs;
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+------ --+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+------ --+------+------------+---------+---------------+
| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+------ --+------+------------+---------+---------------+
4 rows in set (0.00 sec)
Copy the code- Like % on the left-hand side
- Type = All and rows = 3 indicate that a full table scan is performed
- Key = null indicates that the index is invalid
mysql> EXPLAIN SELECT * FROM staffs WHERE name like '%July';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE name like '%July%';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
Copy the code- Key = index_staffs_nameAgePos indicates that the index is not invalid
mysql> EXPLAIN SELECT * FROM staffs WHERE name like 'July%';
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-------- ---------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-------- ---------------+
| 1 | SIMPLE | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | NULL | 1 | Using index condition |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-------- ---------------+
1 row in set (0.00 sec)
Copy the code6.2.8, overwrite index (like ‘% STR %’)
6.2.8.1. Create a table
- Build table SQL
CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL.PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1'.21.'[email protected]');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2'.23.'[email protected]');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3'.24.'[email protected]');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4'.26.'[email protected]');
Copy the code- Test data in the TBL_USER table
mysql> select * from tbl_user;
+----+------+------+-----------+
| id | name | age | email |
+----+------+------+-----------+
| 1 | 1aa1 | 21 | a@ 163.com |
| 2 | 2bb2 | 23 | b@ 163.com |
| 3 | 3cc3 | 24 | c@ 163.com |
| 4 | 4dd4 | 26 | d@ 163.com |
+----+------+------+-----------+
4 rows in set (0.00 sec)
Copy the code6.2.8.2. Create an index
- SQL instructions for creating indexes
CREATE INDEX idx_user_nameAge ON tbl_user(name, age);
Copy the code- Create a federated index in the NAME and AGE fields of the TBL_USER table
mysql> CREATE INDEX idx_user_nameAge ON tbl_user(name, age);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INDEX FROM tbl_user;
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+-- ----+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+-- ----+------------+---------+---------------+
| tbl_user | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| tbl_user | 1 | idx_user_nameAge | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |
| tbl_user | 1 | idx_user_nameAge | 2 | age | A | 4 | NULL | NULL | YES | BTREE | | |
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+-- ----+------------+---------+---------------+
3 rows in set (0.00 sec)
Copy the code6.2.9,Test coverage index
- None of the following SQL indexes is invalid:
- The overwrite index is not invalidated as long as the query fields are related to the overwrite index and there are no extra fields
- But I can’t figure out what id has to do with it…
EXPLAIN SELECT name, age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT name FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, name FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, name, age FROM tbl_user WHERE NAME LIKE '%aa%';
Copy the codemysql> EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+-------+---------------+------------------+---------+------+------+----------------------- ---+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+------------------+---------+------+------+----------------------- ---+
| 1 | SIMPLE | tbl_user | index | NULL | idx_user_nameAge | 68 | NULL | 4 | Using where; Using index |
+----+-------------+----------+-------+---------------+------------------+---------+------+------+----------------------- ---+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT name, age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+-------+---------------+------------------+---------+------+------+----------------------- ---+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+------------------+---------+------+------+----------------------- ---+
| 1 | SIMPLE | tbl_user | index | NULL | idx_user_nameAge | 68 | NULL | 4 | Using where; Using index |
+----+-------------+----------+-------+---------------+------------------+---------+------+------+----------------------- ---+
1 row in set (0.00 sec)
Copy the code- The index of the following SQL will be invalidated: overwrite index will be invalidated if there are extra fields
EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, name, age, email FROM tbl_user WHERE NAME LIKE '%aa%';
Copy the codemysql> EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id, name, age, email FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
Copy the code6.2.10,The index of a string without single quotation marks is invalid
- Normal operation, index is not invalid
mysql> SHOW INDEX FROM staffs;
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+------ --+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+------ --+------+------------+---------+---------------+
| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+------ --+------+------------+---------+---------------+
4 rows in set (0.00 sec)
mysql> explain select * from staffs where name='2000';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-------- ---------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-------- ---------------+
| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-------- ---------------+
1 row in set (0.00 sec)
Copy the code- If you forget to write ‘ ‘in the string, then mysql will perform an implicit cast for you. Any cast will invalidate the index
mysql> explain select * from staffs where name=2000;
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
Copy the code6.2.11. Use or sparsely as it will cause index failure when connecting
- An OR connection will invalidate the index
mysql> SHOW INDEX FROM staffs;
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+------ --+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+------ --+------+------------+---------+---------------+
| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+------ --+------+------------+---------+---------------+
4 rows in set (0.00 sec)
mysql> explain select * from staffs where name='z3' or name = 'July';
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
Copy the code6.3. Index optimization of interview questions
Create a table
- Build table SQL
create table test03(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10));insert into test03(c1,c2,c3,c4,c5) values ('a1'.'a2'.'a3'.'a4'.'a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1'.'b2'.'b3'.'b4'.'b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1'.'c2'.'c3'.'c4'.'c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1'.'d2'.'d3'.'d4'.'d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1'.'e2'.'e3'.'e4'.'e5');
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
Copy the code- Test data in the test03 table
mysql> select * from test03;
+----+------+------+------+------+------+
| id | c1 | c2 | c3 | c4 | c5 |
+----+------+------+------+------+------+
| 1 | a1 | a2 | a3 | a4 | a5 |
| 2 | b1 | b2 | b3 | b4 | b5 |
| 3 | c1 | c2 | c3 | c4 | c5 |
| 4 | d1 | d2 | d3 | d4 | d5 |
| 5 | e1 | e2 | e3 | e4 | e5 |
+----+------+------+------+------+------+
5 rows in set (0.00 sec)
Copy the code- Test03 The index in the table
mysql> SHOW INDEX FROM test03;
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+---- --+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+---- --+------------+---------+---------------+
| test03 | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 1 | c1 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 2 | c2 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 3 | c3 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 4 | c4 | A | 5 | NULL | NULL | YES | BTREE | | |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+---- --+------------+---------+---------------+
5 rows in set (0.00 sec)
Copy the codeSQL > create index idx_test03_c1234 (idx_test03_c1234);
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4';- Full value matching
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4';
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+---- -------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+---- -------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 124 | const,const,const,const | 1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+---- -------------------+
1 row in set (0.00 sec)
Copy the codeEXPLAIN SELECT * FROM test03 WHERE c4='a4' AND c3='a3' AND c2='a2' AND c1='a1';- The mysql optimizer is optimized so that our indexes work
mysql> EXPLAIN SELECT * FROM test03 WHERE c4='a4' AND c3='a3' AND c2='a2' AND c1='a1';
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+---- -------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+---- -------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 124 | const,const,const,const | 1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+---- -------------------+
1 row in set (0.00 sec)
Copy the codeEXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='a4';- The C3 column is sorted using the index and not searched, causing C4 to be unable to search with the index
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='a4';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+---------------------- -+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+---------------------- -+
| 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93 | NULL | 1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+---------------------- -+
1 row in set (0.00 sec)
Copy the codeEXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4>'a4' AND c3='a3';- The mysql optimizer was optimized so that our indexes were all in effect, and a range search was performed at C4
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4>'a4' AND c3='a3';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+---------------------- -+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+---------------------- -+
| 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 124 | NULL | 1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+---------------------- -+
1 row in set (0.00 sec)
Copy the codeEXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3;- Column C3 uses the index for sorting, not lookup, and column C4 does not use the index
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+---------------- --------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+---------------- --------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+---------------- --------------------+
1 row in set (0.00 sec)
Copy the codeEXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c3;- So just like above, there’s no index for the C4 column
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c3;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+---------------- --------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+---------------- --------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+---------------- --------------------+
1 row in set (0.00 sec)
Copy the codeEXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c4;- Mysql used file sorting because the index was created in a different order from the order in which it was used
- Using Filesort: this SQL must be optimized
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c4;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+---------------- ------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+---------------- ------------------------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where; Using filesort |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+---------------- ------------------------------------+
1 row in set (0.00 sec)
Copy the codeEXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c2, c3;- Only c1 is indexed, but C2 and C3 are used for sorting, without filesort
- Why not use filesort because c2 comes after C1 when sorting?
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c2, c3;
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------- --------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------- --------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------- --------------+
1 row in set (0.00 sec)
Copy the codeEXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c3, c2;- With filesort, we set up index 1234, it’s out of order, 32 is reversed
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c3, c2;
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------- ------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------- ------------------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where; Using filesort |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------- ------------------------------+
1 row in set (0.00 sec)
Copy the codeEXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c2, c3;- Index with c1 and c2, but c2 and c3 are used for sorting, without filesort
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c2, c3;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+---------------- --------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+---------------- --------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+---------------- --------------------+
1 row in set (0.00 sec)
Copy the codeEXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c2, c3;- It has nothing to do with c5
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c2, c3;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+---------------- --------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+---------------- --------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+---------------- --------------------+
1 row in set (0.00 sec)
Copy the codeEXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c3, c2;- C2 = ‘a2’; c2= ‘a2’; c2= ‘a2’
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c3, c2;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+---------------- --------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+---------------- --------------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+---------------- --------------------+
1 row in set (0.00 sec)
Copy the codeEXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c2, c3;- The order is 1, 2, 3. No file sorting is generated
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c2, c3;
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------- --------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------- --------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------- --------------+
1 row in set (0.00 sec)
Copy the codeEXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c3, c2;- Group by and order by have the same index problem
- Using temporary; Using filesort has both, I can only say extinction master
`EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BYc3, c2; `group byIt's called grouping, it's sort before grouping,group by 和 order byThe problem with indexes is basically the sameUsing temporary; UsingFilesort has both, I can only say extinction masterCopy the codeConclusion:
- Group by basically needs to be sorted. If used improperly, temporary tables will be generated
- Set value to constant, range after the invalid, finally see the sort order
6.4 index failure Summary
6.4.1. General recommendations
- For single-key indexes, try to select indexes with better filtering for the current Query
- When selecting a composite index, the most filtered field in the current query is as far to the left of the index field order as possible.
- When selecting a composite index, try to select an index that contains as many fields as possible from the WHERE clause in the current query
- Select the appropriate index by analyzing statistics and adjusting the way query is written whenever possible
6.4.2 Summary of index optimization
- Like starts with constants, such as’ kk% ‘and’ k%kk% ‘
6.4.3, Like SQL measured
- = ‘kk’ : key_len = 93, please remember the value of this parameter, useful later
----+-------------+--------+------+------------------+------------------+---------+-------------------+------+---------- -------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+---------- -------------+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 93 | const,const,const | 1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+---------- -------------+
1 row in set (0.00 sec)
Copy the code- Like ‘% kk:
- Key_len = 93, as above, means that c1, C2, and c3 are indexed
- Type = range indicates that this is a range search
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like 'kk%' AND c3='a3';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+---------------------- -+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+---------------------- -+
| 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93 | NULL | 1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+---------------------- -+
1 row in set (0.00 sec)
Copy the code- Like ‘%kk’ and like ‘%kk%’ : key_len = 31, indicating that only C1 uses the index
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like '%kk' AND c3='a3';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------- -+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------- -+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------- -+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like '%kk%' AND c3='a3';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------- -+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------- -+
| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------- -+
1 row in set (0.00 sec)
Copy the code- Like ‘k%kk%’ : key_len = 93
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like 'k%kk%' AND c3='a3';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+---------------------- -+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+---------------------- -+
| 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93 | NULL | 1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+---------------------- -+
1 row in set (0.00 sec)
Copy the code6.4.4,Summary of index optimization
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 influence should be paid attention to; VAR quotes cannot be lost. There are tricks to SQL optimization
7. Query interception analysis
7.1. Query optimization
MySQL Optimization Principles
Mysql tuning outline
- Slow query open and capture
- Explain + slow SQL analysis
- Run the show profile command to query the execution details and life cycle of SQL on the Mysql server
- Tuning parameters of the SQL database server
A permanent small table drives a larger table, similar to Nested Loop Nested Loop
- EXISTSGrammar:
- SELECT … FROM table WHERE EXISTS(subquery)
- This syntax can be interpreted as: put the query data into the sub-query to perform conditional verification. According to the verification result (TRUE or FALSE), the data result of the main query can be preserved.
- EXISTS(subquery) only returns TRUE or FALSE, so SELECT** * in a subquery can also be SELECT 1 or otherwise
- The actual execution process of EXIST S sub-query may be optimized rather than the comparison item by item in our understanding. If efficiency is concerned, practical test can be carried out to determine whether it is efficient or not.
- The EXISTS subquery can also be replaced by conditional expression, other subquery or JOIN. The optimal type needs to be analyzed on a case-by-case basis
Conclusion:
- Always remember that small tables drive large tables
- When the table B dataset is smaller than the table A dataset, use in
- When the data set of table A is smaller than that of table B, exist is used
Usage of in and exists
- Tbl_emp table and TBL_DEPT table
mysql> select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
| 8 | s9 | 51 |
+----+------+--------+
8 rows in set (0.00 sec)
mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
| 1 | RD | 11 |
| 2 | HR | 12 |
| 3 | MK | 13 |
| 4 | MIS | 14 |
| 5 | FD | 15 |
+----+----------+--------+
5 rows in set (0.00 sec)
Copy the code- In the writing of
mysql> select * from tbl_emp e where e.deptId in (select id from tbl_dept);
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
+----+------+--------+
7 rows in set (0.00 sec)
Copy the code- The exists of writing
mysql> select * from tbl_emp e where exists (select 1 from tbl_dept d where e.deptId = d.id);
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
+----+------+--------+
7 rows in set (0.00 sec)
Copy the code7.2, ORDER BY optimization
ORDER BY clause, try to use Index rather than FileSort
7.2.1. Create a table
- Build table SQL
create table tblA(
#id int primary key not null auto_increment,
age int,
birth timestamp not null
);
insert into tblA(age, birth) values(22, now());
insert into tblA(age, birth) values(23, now());
insert into tblA(age, birth) values(24, now());
create index idx_A_ageBirth on tblA(age, birth);
Copy the code- Test data in tblA tables
mysql> select * from tblA;
+------+---------------------+
| age | birth |
+------+---------------------+
| 22 | 2020- 08- 05 10:36:32 |
| 23 | 2020- 08- 05 10:36:32 |
| 24 | 2020- 08- 05 10:36:32 |
+------+---------------------+
3 rows in set (0.00 sec)
Copy the code- Indexes in TBL
mysql> SHOW INDEX FROM tblA;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+ ------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+ ------------+---------+---------------+
| tblA | 1 | idx_A_ageBirth | 1 | age | A | 3 | NULL | NULL | YES | BTREE | | |
| tblA | 1 | idx_A_ageBirth | 2 | birth | A | 3 | NULL | NULL | | BTREE | | |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+ ------------+---------+---------------+
2 rows in set (0.00 sec)
Copy the codeExample 1: Case where you can sort using indexes
- Only the lead brother age
mysql> EXPLAIN SELECT * FROM tblA where age>20 order by age;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| 1 | SIMPLE | tblA | index | idx_A_ageBirth | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)
mysql> EXPLAIN SELECT * FROM tblA where birth>'2016-01-28 00:00:00' order by age;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+
| 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Copy the code- Age + birth
mysql> EXPLAIN SELECT * FROM tblA where age>20 order by age,birth;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| 1 | SIMPLE | tblA | index | idx_A_ageBirth | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Copy the code- Mysql default ascending order, full ascending or full descending order, can withstand
mysql> EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth ASC;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM tblA ORDER BY age DESC, birth DESC;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
1 row in set (0.01 sec)
Copy the codeExample 2:A case in which sorting cannot be done using an index
- The head brother age is dead
mysql> EXPLAIN SELECT * FROM tblA where age>20 order by birth;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------- ---------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------- ---------------+
| 1 | SIMPLE | tblA | index | idx_A_ageBirth | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------- ---------------+
1 row in set (0.01 sec)
Copy the code
Copy the code- Mysql default ascending order, if full ascending or full descending order, ok, but a rise or fall mysql can not carry
mysql> EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth DESC;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+---------------------------- -+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+---------------------------- -+
| 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+---------------------------- -+
1 row in set (0.00 sec)
Copy the code- MySQL supports two kinds of sorting methods, FileSort and Index. Index is more efficient, which means that MySQL scans the Index itself to complete sorting. FileSort is less efficient.
- ORDER BY satisfies both cases(Best left prefix principle), will sort by Index
- The ORDER BY statement uses the left-most front row of the index
- Use the where clause in combination with the OrderBy clause condition column to satisfy the left-most front of the index
- Do the sorting on the index column as much as possible, following the best left prefix for the index
If the index column is not sorted, mysql will start filesort’s two algorithms: double-path sort and single-path sort
-
Dual sorting
- MySQL4.1 used double-path sorting, which literally means scanning the disk twice for data. Read the row pointer and the columns for the Order Derby operation, sort them, then scan the sorted list and re-read the corresponding data transfer from the list according to the values in the list
- Fetch the sort fields from disk, sort them in buffer, and fetch the other fields from disk.
-
Single way sorting
- Fetching a batch of data requires two scans of the disk, which is notoriously time consuming for I/O, so after mysql4.1, there was an improved algorithm called single-way sort.
- It reads all the columns needed for the query from disk, sorts them by the columns to be Derby, and then scans the sorted list for output. It is faster, avoids a second read, and turns random I/O into sequential I/O, but uses more space. Because it keeps every line in memory.
-
Conclusion and the problems raised
- Since single-path is an improved algorithm, it is generally better than dual-path
- In sort_buffer, method B takes up A lot more space than method A. Because method B takes out all fields, it is possible that the total size of the retrieved data exceeds the capacity of sort_buffer. As A result, only sort_buffer size can be retrieved each time for sorting (create TMP file, Sort_buffer = sort_buffer = sort_buffer = sort_buffer This results in multiple I/ OS.
- Conclusion: An attempt to save an I/O operation resulted in a large number of /O operations that were not worth the cost.
-
Deeper optimization strategies
- Increases the sort_buffer_size parameter setting
- Increases the setting of the max_LENGTH_FOR_sorT_DATA parameter
Follow these rules to increase the speed of Order By
- Order bywhenselectIs a big no-no. It is important to Query only the fields you need. The impact here is:
- When total field size less than max_length_for_sort_data, Query and sort field is not a TEXT | BLOB, will use the improved algorithm, single channel sorting, or with the old algorithm, multiple sort.
- Both algorithms may exceed the sort_buffer capacity. After that, TMP files will be created for merge sorting, resulting in multiple I/ OS. However, using single-path sorting algorithm is more risky, so sort_buffer_size should be increased.
- Try increasing sort_BUFFer_SIZE Regardless of which algorithm you use, increasing this parameter will improve efficiency, depending on the system’s capabilities, of course, since this parameter is per-process
- Trying to increase max_LENGTH_FOR_sort_DATA Increasing this parameter increases the probability of using the improved algorithm. However, if set too high, the probability of total data capacity exceeding sort_BUFFer_size increases, with high disk I/O activity and low processor utilization evident.
Order By sort index optimization summary
7.3. GROUP BY Optimization
Group by keyword optimization
- Group by is essentially sorted and then grouped, following the best left prefix for the index
- When index columns are unavailable, increase the max_LENGTH_FOR_sort_data parameter setting + increase the sort_BUFFer_SIZE parameter setting
- Where having is higher than where having can be qualified
- Other rules are consistent with Order by
8. Slowly query logs
8.1 introduction to Slow Query Logs
- 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.
- The default value of long_query_time is 10, which means that SQL statements running for more than 10 seconds are logged
- He will check which SQL exceeds our maximum endurance time value. For example, if an SQL is executed for more than 5 seconds, we will consider it as slow SQL. We hope to collect SQL of more than 5 seconds and conduct comprehensive analysis based on previous explain.
8.2 log Query is enabled
How to play?
Description:
- 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 necessary for tuning, because slow query logging can have some performance impact. Slow query logs can be written to a file
Check whether and how to enable it
- Checking whether slow query logs are enabled:
- By default
slow_query_logThe value of “OFF” indicates that slow query logs are disabled - You can do that by setting
slow_query_logTo enable - through
SHOW VARIABLES LIKE '%slow_query_log%';Check whether slow query logs of mysql are enabled
- By default
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/Heygo-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)
Copy the code- How to Enable slow Query logs:
-
set global slow_query_log = 1; Enable slow log query
-
Set global slow_query_log=1 slowquery logs are valid only for the current database and are invalid after MySQL restarts.
-
mysql> set global slow_query_log = 1;
Query OK, 0 rows affected (0.07 sec)
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/Heygo-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)
Copy the code- To be permanent, you must modify the configuration file my.cnf (as well as other system variables)
- Modify themy.cnfFile,
[mysqld]Add or modify parameters below:slow_query_logandslow_query_log_fileThen restart the MySQL server. - Also configure the following two lines into the my.cnf file
- Modify themy.cnfFile,
[mysqld]
slow_query_log =1
slow_query_log_file=/var/lib/mysql/Heygo-slow.log
Copy the code- Parameters for slow queries
slow_query_log_file, which specifies the path for storing slow query log files. The system provides a default file by defaulthost_name-slow.log(If no parameter is specifiedslow_query_log_file)
8.3,Record to slow query
After slow query logging is enabled, what KIND of SQL will be recorded in slow query?
- This is controlled by the long_query_time parameter, which by default is 10 seconds, command:
SHOW VARIABLES LIKE 'long_query_time%';Check the threshold of slow SQL
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)
Copy the code- You can change it using commands or in the my.cnf parameter.
- If the running time is exactly long_query_time, it will not be recorded. In other words, the mysql source code is greater than long_query_time, not greater than or equal to.
8.3.1 Example of Slow Query Logs
- Check the threshold time of slow SQL. The default threshold time is 10s
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
Copy the code- To set the threshold time for slow SQL, we set it to 3s
mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)
Copy the codeWhy does the threshold time not change after setting?
- You need to reconnect or open a new callback to see the changed value.
- View global
long_query_timeValue:show global variables like 'long_query_time'; Discovery has taken effect
mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
mysql> show global variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
Copy the code- Record slow SQL for subsequent analysis
-
A select Dui sleep (4); More than 3s will definitely be recorded in the log
-
mysql> select sleep(4);
+----------+
| sleep(4) |
+----------+
| 0 |
+----------+
1 row in set (4.00 sec)
Copy the code- The slow query log file is stored in /var/lib/mysql.and its suffix is -slow.log
[root@Heygo mysql]# cd /var/lib/mysql/
[root@Heygo mysql]# ls -L total amount176156
-rw-rw----. 1 mysql mysql 56 8月 3 19:08 auto.cnf
drwx------. 2 mysql mysql 4096 8月 5 10:36 db01
-rw-rw----. 1 mysql mysql 7289 8月 3 22:38 Heygo.err
-rw-rw----. 1 mysql mysql 371 8月 5 12:58 heygo-slow.log
-rw-rw----. 1 mysql mysql 79691776 8月 5 10:36 IBdata1
-rw-rw----. 1 mysql mysql 50331648 8月 5 10:36 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 8月 3 19:08 ib_logfile1
drwx------. 2 mysql mysql 4096 8月 3 19:08 mysql
srwxrwxrwx. 1 mysql mysql 0 8month3 22:38 mysql.sock
drwx------. 2 mysql mysql 4096 8月 3 19:08 Performance_schema
Copy the code- View the content in slow query logs
[root@Heygo mysql]# cat Heygo-slow.log
/usr/sbin/mysqld, Version: 5.649. (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 200805 12:58:01
# User@Host: root[root] @ localhost [] Id: 11
# Query_time: 4.000424 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1596603481;
select sleep(4);
Copy the code- Query the number of slow query records in the current system:
show global status like '%Slow_queries%';
mysql> show global status like '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 1 |
+---------------+-------+
1 row in set (0.00 sec)
Copy the codeSlow query logs of the configuration edition
In /etc/my.cnf file under the [mysqld] node
slow_query_log=1; slow_query_log_file=/var/lib/mysql/Heygo-slow.log
long_query_time=3; log_output=FILE
Copy the codeLog analysis command mysqldumpslow
What is mysqlDumpslow?
In a production environment, if you need to manually analyze logs, find and analyze SQL, it is obviously a manual task. MySQL provides the log analysis tool mysqlDumpslow.
See help for mysqlDumpslow
[root@Heygo mysql]# mysqldumpslow --helpUsage: mysqldumpslow [ OPTS... ] [ LOGS... ] Parseand summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S' -n NUM abstract numbers with at least n digits within names -g PATTERN grep: only consider stmts that include this string -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time
Copy the codeMysqldumpshow Parameter description
s: indicates the sorting modec: Number of visitsl: Lock timer: Returns a recordt: Query timeal: Average lock timear: Average number of returned recordsat: Average query timet: indicates how many previous columns of data are returnedg: followed by a regular matching pattern, case insensitive
Common Parameter Manual
- Get the 10 SQL that return the most recordsets
mysqldumpslow -s r -t 10 /var/lib/mysql/Heygo-slow.log
Copy the code- Get the 10 most frequently accessed SQL
mysqldumpslow -s c- t 10/var/lib/mysql/Heygo-slow.log
Copy the code- Get the top 10 queries in chronological order that contain the left join
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/Heygo-slow.log
Copy the code- Additional Suggestions when using these commands | and more use, otherwise likely explodes the screen
It is recommended to combine these commands when using them|And more. Otherwise, the screen may explodeCopy the code8.4. Batch Data Scripts
8.4.1 create a table
- Build table SQL
CREATE TABLE dept
(
deptno int unsigned primary key auto_increment,
dname varchar(20) not null default "",
loc varchar(8) not null default ""
)ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE emp
(
id int unsigned primary key auto_increment,
empno mediumint unsigned not null default 0,
ename varchar(20) not null default "",
job varchar(9) not null default "",
mgr mediumint unsigned not null default 0,
hiredate date not null,
sal decimal(7.2) not null,
comm decimal(7.2) not null,
deptno mediumint unsigned not null default 0
)ENGINE=INNODB DEFAULT CHARSET=utf8;
Copy the code8.4.2. Set Parameters
- This function has none of DETERMINISTIC…
- Since querying logs is too slow, since we have bin-log enabled, we must specify a parameter for our function.
- Log_bin_trust_function_creators = OFF, a default parameter must be passed for function
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
Copy the code- through
set global log_bin_trust_function_creators=1;We don’t need to pass parameters to function
mysql> set global log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON |
+---------------------------------+-------+
1 row in set (0.00 sec)
Copy the code- If mysqld is restarted, the above parameters will disappear again.
- On Windows: my.ini –> [mysqld] add log_bin_trust_function_Creators =1
- In Linux, add log_bin_trust_function_Creators =1 to the /etc/my.cnf –> [mysqld] node
8.4.3 Create functions to ensure that each piece of data is different
- A function that randomly generates strings
Delimiter $$# Two $$marks the endcreate function rand_string(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz';
declare return_str varchar(255) default ' ';
declare i int default 0;
while i < n do
set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i=i+1;
end while;
return return_str;
end $$
Copy the code- A function that randomly generates department numbers
delimiter $$
create function rand_num() returns int(5)
begin
declare i int default 0;
set i=floor(100+rand()*10);
return i;
end $$
Copy the code8.4.4 Creating a Stored Procedure
- Create a stored procedure to insert data into the EMP table
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i+1;
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'salesman'.0001,curdate(),2000.400,rand_num());
until i=max_num
end repeat;
commit;
end $$
Copy the code- Create a stored procedure to insert data into the DEPT table
delimiter $$
create procedure insert_dept(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i+1;
insert into dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8));
until i=max_num
end repeat;
commit;
end $$
Copy the code8.4.5. Calling a Stored Procedure
- Insert 10 records into the DEPT table
DELIMITER ;
CALL insert_dept(100.10);
Copy the codemysql> select * from dept;
+--------+---------+--------+
| deptno | dname | loc |
+--------+---------+--------+
| 101 | aowswej | syrlhb |
| 102 | uvneag | pup |
| 103 | lps | iudgy |
| 104 | jipvsk | ihytx |
| 105 | hrpzhiv | vjb |
| 106 | phngy | yf |
| 107 | uhgd | lgst |
| 108 | ynyl | iio |
| 109 | daqbgsh | mp |
| 110 | yfbrju | vuhsf |
+--------+---------+--------+
10 rows in set (0.00 sec)
Copy the code- Insert 50w records into the emp table
DELIMITER ;
CALL insert_emp(100001.500000);
Copy the codemysql> select * from emp limit 20;
+----+--------+-------+----------+-----+------------+---------+--------+--------+
| id | empno | ename | job | mgr | hiredate | sal | comm | deptno |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
| 1 | 100002 | ipbmd | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 101 |
| 2 | 100003 | bfvt | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 107 |
| 3 | 100004 | | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 109 |
| 4 | 100005 | cptas | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 101 |
| 5 | 100006 | ftn | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 108 |
| 6 | 100007 | gzh | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 102 |
| 7 | 100008 | rji | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 100 |
| 8 | 100009 | | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 106 |
| 9 | 100010 | tms | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 100 |
| 10 | 100011 | utxe | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 101 |
| 11 | 100012 | vbis | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 104 |
| 12 | 100013 | qgfv | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 104 |
| 13 | 100014 | wrvb | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 105 |
| 14 | 100015 | dyks | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 109 |
| 15 | 100016 | hpcs | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 101 |
| 16 | 100017 | fxb | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 108 |
| 17 | 100018 | vqxq | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 102 |
| 18 | 100019 | rq | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 102 |
| 19 | 100020 | l | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 106 |
| 20 | 100021 | lk | salesman | 1 | 2020- 08- 05 | 2000.00 | 400.00 | 100 |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
20 rows in set (0.00 sec)
Copy the code9, Show the Profile
9.1,What is Show Profile?
- Mysql provides resources that can be used to analyze the resource consumption of statement execution in the current session. Can be used for tuning measurements of SQL
- Liverpoolfc.tv: dev.mysql.com/doc/refman/…
- By default, the parameter is turned off and the results of the last 15 runs are saved
9.2,Analysis steps
1) Check whether the current SQL version supports Show Profile
Show variables like '% profiling;Check whether Show Profile is enabled
mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
2 rows in set (0.01 sec)
Copy the code2),Enable the Show Profile function. The function is disabled by default
set profiling=on;Open the Show Profile
mysql> set profiling=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
2 rows in set (0.00 sec)
Copy the code3),Run the SQL
- Normal SQL
select * from tbl_emp;
select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id;
select * from tbl_emp e left join tbl_dept d on e.deptId = d.id;
Copy the code- Slow SQL
select * from emp group by id%10 limit 150000;
select * from emp group by id%10 limit 150000;
select * from emp group by id%20 order by 5;
Copy the code4),View the results
- Through the show profiles; Command view result
mysql> show profiles;
+----------+------------+----------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------------------------------+
| 1 | 0.00052700 | show variables like 'profiling%' |
| 2 | 0.00030300 | select * from tbl_emp |
| 3 | 0.00010650 | select * from tbl_emp e inner join tbl_dept d on e.'deptId' = d.'id' |
| 4 | 0.00031625 | select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id |
| 5 | 0.00042100 | select * from tbl_emp e left join tbl_dept d on e.deptId = d.id |
| 6 | 0.38621875 | select * from emp group by id%20 limit 150000 |
| 7 | 0.00014900 | select * from emp group by id%20 order by 150000 |
| 8 | 0.38649000 | select * from emp group by id%20 order by 5 |
| 9 | 0.06782700 | select COUNT(*) from emp |
| 10 | 0.35434400 | select * from emp group by id%10 limit 150000 |
+----------+------------+----------------------------------------------------------------------+
10 rows in set.1 warning (0.00 sec)
Copy the code5),Diagnosis of SQL
Show profile CPU, block IO for query SQL number;View the specific flow of SQL statement execution and how long each step takes
mysql> show profile cpu, block io for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000055 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000024 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000046 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000018 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000019 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000089 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set.1 warning (0.00 sec)
Copy the codeParameter Remarks:
ALL: displays ALL costs. BLOCK IO: displays costs related to BLOCK I/O CONTEXT SWITCHES: displays costs related to the CONTEXT switch. CPU: displays costs related to the CPU. PAGE FAULTS: displays PAGE error costs SOURCE: displays cost information related to Source_function, Source_file, and Source_line: displays cost information related to the number of SWAPS
Daily development needs attention to conclusions
Converting HEAP to MyISAM: The result of the query is too large to have enough memory to move to disk. Copying to TMP tables on disk Creating TMP tables mysql is Copying data to TMP tables on disk Locked: lock table
10. Query logs globally
Never turn this on in production.
10.1. Enable global query logs
- Mysql my.cnf = my.cnf
# open
general_log=1
Record the path of the log file
general_log_file=/path/logfile
# output format
log_output=FILE
Copy the code10.2,Encoding enables global query logging
- Run the following command to enable global log query
set global general_log=1;
set global log_output='TABLE';
Copy the code- After that, your SQL statements will be logged to the general_log table in the mysql database, which can be viewed using the following command
select * from mysql.general_log;
Copy the codemysql> select * from mysql.general_log;
+---------------------+---------------------------+-----------+-----------+--------------+------------------------------- ----------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+---------------------+---------------------------+-----------+-----------+--------------+------------------------------- ----------------+
| 2020- 08- 05 14:41:07 | root[root] @ localhost [] | 14 | 0 | Query | select * from emp group by id%10 limit 150000 |
| 2020- 08- 05 14:41:12 | root[root] @ localhost [] | 14 | 0 | Query | select COUNT(*) from emp |
| 2020- 08- 05 14:41:30 | root[root] @ localhost [] | 14 | 0 | Query | select * from mysql.general_log |
+---------------------+---------------------------+-----------+-----------+--------------+------------------------------- ----------------+
3 rows in set (0.00 sec)
Copy the code