MySQL Trace ToolWhether MySQL chooses to use an index in the end or a table involves multiple indexes, and how to choose an index in the end, you can use the trace tool to find out. Turning on the trace tool will affect MySQL performance, so it can only temporarily analyze SQL usage and close it immediately after use. Case AnalysisBefore talking about the trace tool, let’s take a look at a case:
1
2
3
4
# 示例表CREATE TABLE`employees`(`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`), KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
INSERT INTO employees(name,age,position,hire_time)VALUES('ZhangSan',23,'Manager',NOW());INSERT INTO employees(name,age,position,hire_time)VALUES('HanMeimei', 23,'dev',NOW());INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());复制代码
Copy after login
How to choose the appropriate index for MySQL
1
EXPLAIN select * from employees where name > 'a';复制代码
Copy after login
If you use the name index, you need to traverse the name field joint index tree, and then you need to go to the primary key index tree based on the traversed primary key value to find the final data. The cost is more than a full table scan. High, you can use covering index optimization, so that you only need to traverse the joint index tree of the name field to get all the results, as follows:
1
EXPLAIN select name,age,position from employees where name > 'a' ;复制代码
Copy after login
1
EXPLAIN select * from employees where name > 'zzz' ;复制代码
Copy after login
Regarding the execution results of the above two
name>'a' and name>'zzz', whether mysql will choose to use the index or a table involves multiple indexes, what will happen to mysql in the end? Select the index, we can use the trace tool to find out. Turning on the trace tool will affect the performance of mysql, so you can only temporarily analyze the sql usage and close it immediately after use.
{ "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
Conclusion: The cost of full table scan is lower than index scan, so MySQL finally chooses full table scan.
Case 2
1
2
select * from employees where name > 'zzz' order by position;SELECT * FROM information_schema.OPTIMIZER_TRACE;
复制代码
Copy after login
Conclusion: Looking at the trace field, we can see that the cost of index scan is lower than that of full table scan, so MySQL finally chooses index scan.
Common SQL in-depth optimization
Order by and Group by Optimization
Case 1
1
EXPLAIN select * from employees where name = 'ZhangSan' andposition = 'dev' order by age复制代码
Copy after login
Analysis:
Use the
leftmost prefix rule: The middle field cannot be broken, so the query uses name index, it can also be seen from key_len = 74 that the age index column is used in the sorting process, because there is no using filesort in the Extra field.
Case 2
1
EXPLAIN select * from employees where name = 'ZhangSan' order by position复制代码
Copy after login
Analysis:
From the execution results of explain Look: key_len = 74, the query uses the name index, and because position is used for sorting, age is skipped, and
Using filesort appears.
Case 3
1
EXPLAIN select * from employees where name = 'ZhangSan' order by age,position复制代码
Copy after login
Analysis:
The query only uses
Index name, age and position are used for sorting, noneUsing filesort.
Case 4
1
EXPLAIN select * from employees where name = 'ZhangSan' order by position,age复制代码
Copy after login
Analysis:
and explain in case 3 The execution result is the same, but
Using filesort appears, because the index creation order is name,age,position, but when sorting, age and position reverse the positions .
案例5
1
EXPLAIN select * from employees where name = 'ZhangSan' andage = 18 order by position,age复制代码
Copy after login
分析:
与案例4对比,在Extra中并未出现** Using filesort **,因为 age 为常量,在排序中被优化,所以索引未颠倒,不会出现 Using filesort 。
案例6
1
EXPLAIN select * from employees where name = 'ZhangSan' order by age asc, position desc;复制代码
Copy after login
分析:
虽然排序的字段列与索引顺序一样,且 order by 默认升序,这里 position desc 变成列降序,导致与索引的排序方式不同,从而产生 Using filesort 。MySQL8 以上版本有降序索引可以支持该种查询方式。
案例7
1
EXPLAIN select * from employees where name in ('ZhangSan', 'hjh') order by age, position;复制代码
Copy after login
分析:
对于排序来说,多个相等条件也是范围查询。
案例8
1
EXPLAIN select * from employees where name > 'a' order by name;复制代码
Copy after login
可以用覆盖索引优化
1
EXPLAIN select name,age,position from employees where name > 'a' order by name;复制代码
Copy after login
优化总结
MySQL支持两种方式的排序 filesort 和 index。Using index 是指MySQL 扫描索引本身完成排序。index 效率高,filesort 效率低。
order by 满足两种情况会使用 Using index.
order by 语句使用索引最左前例。
使用 where 子句与 order by 子句条件列组合满足索引最左前例。
尽量在索引列上完成排序,遵循索引建立(索引创建的顺序)时的最左前缀法则。
如果 order by 的条件不在索引列上,就会产生 Using filesort。
能用覆盖索引尽量用覆盖索引。
group by 和 order by 很类似,其实质是先排序后分组,遵循索引创建顺序的最左前缀法则。对于 group by 的优化如果不需要排序的可以加上 order by null 禁止排序。注意:where 高于 having,能写在 where 中的限定条件就不要去 having 限定了。
EXPLAIN select * from employees where name = 'ZhangSan' order by position;复制代码
Copy after login
查看下这条sql对应trace结果如下(只展示排序部分):
1
set session optimizer_trace="enabled=on",end_markers_in_json=on; #开启traceselect * from employees where name = 'ZhangSan' order by position;select * from information_schema.OPTIMIZER_TRACE;复制代码
Query the total number of rows maintained by MySQL itself
show table status
Maintain the total number in Redis
Increase count table
Query the total number of rows maintained by MySQL itself
For the table of myisam storage engine, the performance of count query without where condition is very good High, because the total number of rows in the table of the myisam storage engine will be stored on the disk by mysql, and the query does not need to be calculated.
For the innodb storage engine table mysql will not store the total number of record rows in the table, and the query count needs to be calculated in real time.
show table status
If you only need to know the estimated value of the total number of rows in the table, you can use the following sql query, the performance is very high
Maintain the total number in Redis
When inserting or deleting table data rows, maintain the count value of the total number of table rows in redis at the same time (use the incr or decr command), but this method may Not accurate, it is difficult to ensure transaction consistency between table operations and redis operations.
Increase the count table
When inserting or deleting table data rows, maintain the count table at the same time, so that they can operate in the same transaction.
If you want to know more about programming learning, please pay attention to the php training column!
The above is the detailed content of Practice (2)--MySQL performance optimization. For more information, please follow other related articles on the PHP Chinese website!
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn