深入理解MYSQL索引优化：多列索引

最新推荐文章于 2023-11-06 12:01:38 发布
最新推荐文章于 2023-11-06 12:01:38 发布
阅读量352 收藏
点赞数
文章标签： mysql 数据库 database
原文链接：https://www.fgba.net/forum-75-1.html
版权

　　索引是什么

　　是存储引擎用于找到数据的一种数据结构。

　　MYSQL索引优化视频详解：「链接」

　　索引的性能

　　在数据量小的时候，一个坏的索引往往作用没有那么明显，但是在数据量比较大的时候一个坏的索引和好的索引有巨大的区别。

　　在查询优化的时候应该首先考虑索引优化。这个是最简单的，也是效果最好。

　　索引的执行流程

　　索引=> 索引值=> 数据行

　　mysql> explain select first_name from actor where actor_id=5;

　　+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

　　| 1 | SIMPLE | actor | NULL | const | PRIMARY | PRIMARY | 2 | const | 1 | 100.00 | NULL |

　　+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

　　1 row in set, 1 warning (0.00 sec)

　　通过explian可以看到where条件后面是使用了主键索引。

　　多列索引

　　如果是多列组成的索引，那么在使用索引的时候要考虑索引的顺序。

　　多列索引有如下原则

　　最左匹配原则，如果缺失了索引的最左侧列，那么索引不生效中间不可中断原则，如果中间定义的索引没有在where条件里面体现，那么后面的字段过滤将不会用到索引范围中断原则，只要使用了范围查询，那么其右侧的索引都无法正常使用多列索引的验证建表语句

　　create table person

　　(

　　A INT(10) not null,

　　B INT(10) not null,

　　C INT(10) not null,

　　version VARCHAR(20) not null

　　);

　　create index A

　　on person (A, B, C);填充数据的存储过程

　　delimiter //

　　//

　　CREATE DEFINER=`dev`@`%` PROCEDURE `insert_person`(IN item int)

　　BEGIN

　　DECLARE counter INT;

　　SET counter=item;

　　WHILE counter >=1000 DO

　　INSERT INTO person VALUES(left(counter, 2),left(counter,3), counter,counter );

　　SET counter=counter - 1;

　　END WHILE;

　　END;

　　//

　　delimiter ;

　　call insert_person(100000);数据详情

　　mysql> select count(*) from person;

　　+----------+

　　| count(*) |

　　+----------+

　　| 100000 |

　　+----------+

　　1 row in set (0.04 sec)场景分析一、索引生效1. 符合多列索引生效的三个原则，全部用上了索引

　　(a) 只对A做等值查询

　　mysql> explain select * from person where A=12;

　　+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------+

　　| 1 | SIMPLE | person | NULL | ref | A | A | 4 | const | 1100 | 100.00 | NULL |

　　+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------+

　　1 row in set, 1 warning (0.00 sec)

　　可以看到是用到了A的索引的。

　　(b)只对A做范围查询,并且结果集覆盖索引的时候。

　　mysql> explain select A,B,C from person where A > 13;

　　+----+-------------+--------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+--------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+

　　| 1 | SIMPLE | person | NULL | range | A | A | 4 | NULL | 49555 | 100.00 | Using where; Using index |

　　+----+-------------+--------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+

　　1 row in set, 1 warning (0.00 sec)

　　（c）对A,B做等值查询

　　mysql> explain select A,B,C from person where A=13 and b=129;

　　+----+-------------+--------+------------+------+---------------+------+---------+-------------+------+----------+-------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+--------+------------+------+---------------+------+---------+-------------+------+----------+-------------+

　　| 1 | SIMPLE | person | NULL | ref | A | A | 8 | const,const | 1 | 100.00 | Using index |

　　+----+-------------+--------+------------+------+---------------+------+---------+-------------+------+----------+-------------+

　　1 row in set, 1 warning (0.00 sec)

　　(d) 对A做等值，B做范围

　　mysql> explain select A,B,C from person where A=13 and b > 129;

　　+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

　　| 1 | SIMPLE | person | NULL | range | A | A | 8 | NULL | 1100 | 100.00 | Using where; Using index |

　　+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

　　(e) ABC等值查询

　　mysql> explain select A,B,C from person where A=12 and b=129 and c=1294;

　　+----+-------------+--------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+--------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+

　　| 1 | SIMPLE | person | NULL | ref | A | A | 12 | const,const,const | 1 | 100.00 | Using index |

　　+----+-------------+--------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+

　　1 row in set, 1 warning (0.00 sec)

　　(f) 对AB做等值，C范围查询

　　mysql> explain select A,B,C from person where A=12 and b=129 and c > 1294;

　　+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

　　| 1 | SIMPLE | person | NULL | range | A | A | 12 | NULL | 105 | 100.00 | Using where; Using index |

　　+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

　　1 row in set, 1 warning (0.00 sec)

　　关于C/C++ Linux后端开发网络底层原理知识 点击 正在跳转 获取，内容知识点包括Linux，Nginx，ZeroMQ，MySQL，Redis，线程池，MongoDB，ZK，Linux内核，CDN，P2P，epoll，Docker，TCP/IP，协程，DPDK等等。

　　

深入理解MYSQL索引优化：多列索引

　　二、索引部分生效

　　满足最左原则，但是不满足中间不中断或者范围中断。

　　(a) 对A做等值，C做等值或者是范围

　　mysql> explain select * from person where A=12 and c=1230;

　　+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+

　　| 1 | SIMPLE | person | NULL | ref | A | A | 4 | const | 1100 | 10.00 | Using index condition |

　　+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+

　　1 row in set, 1 warning (0.00 sec)

　　mysql> explain select * from person where A=12 and c >1230;

　　+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+

　　| 1 | SIMPLE | person | NULL | ref | A | A | 4 | const | 1100 | 33.33 | Using index condition |

　　+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+

　　1 row in set, 1 warning (0.00 sec)

　　(b)对A做等值，B做范围，C做等值或者范围，B做了范围查询之后，无法对C正常使用索引

　　mysql> explain select * from person where A=12 and b > 123 and c >1230;

　　+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

　　| 1 | SIMPLE | person | NULL | range | A | A | 8 | NULL | 660 | 33.33 | Using index condition |

　　+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

　　1 row in set, 1 warning (0.00 sec)

　　mysql> explain select * from person where A=12 and b=123 and c >1230;

　　+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

　　| 1 | SIMPLE | person | NULL | range | A | A | 12 | NULL | 109 | 100.00 | Using index condition |

　　+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

　　1 row in set, 1 warning (0.00 sec)

　　(c) 对A做范围查询，BC做等值或者范围，那么BC将不会使用索引

　　mysql> explain select A,B,C from person where A > 12 and b > 123 and c >1230;

　　+----+-------------+--------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+--------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+

　　| 1 | SIMPLE | person | NULL | range | A | A | 4 | NULL | 49555 | 11.11 | Using where; Using index |

　　+----+-------------+--------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+

　　1 row in set, 1 warning (0.00 sec)

　　需要注意的是这个地方ABC只是普通的索引如果是select * from,那么将不会使用索引。因为还有一个字段不再索引持有的数据上。

　　三、多列索引失效：

　　（a）查询条件中不包含最左列

　　mysql> explain select * from person where b=123 and c >1230;

　　+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

　　| 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 99110 | 3.33 | Using where |

　　+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

　　1 row in set, 1 warning (0.00 sec)

　　mysql> explain select * from person where b=123

　　-> ;

　　+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

　　| 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 99110 | 10.00 | Using where |

　　+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

　　1 row in set, 1 warning (0.00 sec)

　　mysql> explain select * from person where b >123;

　　+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

　　| 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 99110 | 33.33 | Using where |

　　+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

　　1 row in set, 1 warning (0.00 sec)

　　mysql> explain select * from person where c=123;

　　+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

　　| 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 99110 | 10.00 | Using where |

　　+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

　　1 row in set, 1 warning (0.00 sec)

　　mysql> explain select * from person where c >123;

　　+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

　　| 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 99110 | 33.33 | Using where |

　　+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

　　1 row in set, 1 warning (0.01 sec)

　　(b) 含有最左列，但是索引的列带条件

　　mysql> explain select * from person where a + 1=12;

　　+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

　　| 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 99110 | 100.00 | Using where |

　　+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

　　1 row in set, 1 warning (0.00 sec)

　　## 从哪里带条件哪里就不开始使用索引

　　mysql> explain select * from person where a=12 and b + 1=123;

　　+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+

　　| 1 | SIMPLE | person | NULL | ref | A | A | 4 | const | 1100 | 100.00 | Using index condition |

　　+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+

　　1 row in set, 1 warning (0.00 sec)四、order by使用索引

　　(a) order by 字段的顺序和table中多列索引定义的顺序一样。

　　mysql> explain select * from person1 order by a,b, c;

　　+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+----------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+----------------+

　　| 1 | SIMPLE | person1 | NULL | ALL | NULL | NULL | NULL | NULL | 98999 | 100.00 | Using filesort |

　　+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+----------------+

　　1 row in set, 1 warning (0.00 sec)

　　mysql> explain select a,b,c from person1 order by a,b, c;

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+

　　| 1 | SIMPLE | person1 | NULL | index | NULL | index_A_B_C | 21 | NULL | 98999 | 100.00 | Using index |

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+

　　1 row in set, 1 warning (0.00 sec)

　　order by 不使用索引

　　(a) order by多个字段，每个字段都是独立的索引

　　mysql> create table person2 select * from person1;

　　Query OK, 99001 rows affected (0.68 sec)

　　Records: 99001 Duplicates: 0 Warnings: 0

　　mysql> alter table add key(a);

　　ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'add key(a)' at line 1

　　mysql> alter table add index_a key(a);

　　ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'add index_a key(a)' at line 1

　　mysql> alter table add index key(a);

　　ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'add index key(a)' at line 1

　　mysql> alter table add index index_a key(a);

　　ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'add index index_a key(a)' at line 1

　　mysql> alter table add index index_a(a);

　　ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'add index index_a(a)' at line 1

　　mysql> alter table person2 add index index_a(a);

　　Query OK, 0 rows affected (0.15 sec)

　　Records: 0 Duplicates: 0 Warnings: 0

　　mysql> alter table person2 add index index_b(b);

　　Query OK, 0 rows affected (0.15 sec)

　　Records: 0 Duplicates: 0 Warnings: 0

　　mysql> explain select * from person2 order by a,b;

　　+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+----------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+----------------+

　　| 1 | SIMPLE | person2 | NULL | ALL | NULL | NULL | NULL | NULL | 99110 | 100.00 | Using filesort |

　　+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+----------------+

　　1 row in set, 1 warning (0.00 sec)

　　(b) order by语句中同时含有desc和asc

　　mysql> explain select a,b,c from person1 order by a desc ;

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+

　　| 1 | SIMPLE | person1 | NULL | index | NULL | index_A_B_C | 21 | NULL | 98999 | 100.00 | Using index |

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+

　　1 row in set, 1 warning (0.01 sec)

　　mysql> explain select a,b,c from person1 order by a desc ,b asc;

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+

　　| 1 | SIMPLE | person1 | NULL | index | NULL | index_A_B_C | 21 | NULL | 98999 | 100.00 | Using index; Using filesort |

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+

　　1 row in set, 1 warning (0.00 sec)

　　(c) order by 里面有计算字段

　　mysql> explain select a,b,c from person1 order by a desc ,b + 1

　　-> ;

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+

　　| 1 | SIMPLE | person1 | NULL | index | NULL | index_A_B_C | 21 | NULL | 98999 | 100.00 | Using index; Using filesort |

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+

　　1 row in set, 1 warning (0.00 sec)

　　(d) orderby 里面顺序和索引的顺序不同

　　mysql> explain select a,b,c from person1 order by b,a;

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+

　　| 1 | SIMPLE | person1 | NULL | index | NULL | index_A_B_C | 21 | NULL | 98999 | 100.00 | Using index; Using filesort |

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+

　　1 row in set, 1 warning (0.00 sec)

　　(e) group by 和orderby 的顺序不同

　　mysql> explain select a,b,c from person1 group by a,b,c order by a,b;

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+

　　| 1 | SIMPLE | person1 | NULL | index | index_A_B_C | index_A_B_C | 21 | NULL | 98999 | 100.00 | Using index |

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+

　　1 row in set, 1 warning (0.00 sec)

　　mysql> explain select a,b,c from person1 group by a,b,c order by b,a;

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+----------------------------------------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+----------------------------------------------+

　　| 1 | SIMPLE | person1 | NULL | index | index_A_B_C | index_A_B_C | 21 | NULL | 98999 | 100.00 | Using index; Using temporary; Using filesort |

　　+----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+----------------------------------------------+

　　1 row in set, 1 warning (0.00 sec)

　　上面的查询顺序一样，没有filesort，下面的不一样，就用了

　　(f) join查询的时候order只能按照主键的来排序，否则就会失效

　　mysql> explain select * from person3 t1 left join person4 t2 on t1.version=t2.version where t1.version=1200 order by t1.version;

　　+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------------+

　　| 1 | SIMPLE | t1 | NULL | index | PRIMARY | PRIMARY | 22 | NULL | 101 | 10.00 | Using where |

　　| 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 22 | dev.t1.version | 1 | 100.00 | NULL |

　　+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------------+

　　2 rows in set, 3 warnings (0.00 sec)

　　mysql> explain select * from person3 t1 left join person4 t2 on t1.version=t2.version where t1.version=1200 order by t2.version;

　　+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+----------------------------------------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+----------------------------------------------+

　　| 1 | SIMPLE | t1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 101 | 10.00 | Using where; Using temporary; Using filesort |

　　| 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 22 | dev.t1.version | 1 | 100.00 | NULL |

　　+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+----------------------------------------------+

　　2 rows in set, 3 warnings (0.00 sec)

　　mysql> show create table person3;

　　+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

　　| Table | Create Table |

　　+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

　　| person3 | CREATE TABLE `person3` (

　　`A` varchar(10) DEFAULT NULL,

　　`B` int(50) NOT NULL,

　　`C` int(50) NOT NULL,

　　`version` varchar(20) NOT NULL,

　　PRIMARY KEY (`version`)

　　) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

　　+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

　　1 row in set (0.00 sec)

　　mysql> show create table person4;

　　+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

　　| Table | Create Table |

　　+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

　　| person4 | CREATE TABLE `person4` (

　　`A` varchar(10) DEFAULT NULL,

　　`B` int(50) NOT NULL,

　　`C` int(50) NOT NULL,

　　`version` varchar(20) NOT NULL,

　　PRIMARY KEY (`version`)

　　) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

　　+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

　　1 row in set (0.00 sec)

　　mysql> explain select * from person3 t1 left join person4 t2 on t1.version=t2.version order by t1.version;

　　+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+

　　| 1 | SIMPLE | t1 | NULL | index | NULL | PRIMARY | 22 | NULL | 101 | 100.00 | NULL |

　　| 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 22 | dev.t1.version | 1 | 100.00 | NULL |

　　+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+

　　2 rows in set, 1 warning (0.00 sec)

　　mysql> explain select * from person3 t1 left join person4 t2 on t1.version=t2.version order by t2.version;

　　+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+---------------------------------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+---------------------------------+

　　| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 101 | 100.00 | Using temporary; Using filesort |

　　| 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 22 | dev.t1.version | 1 | 100.00 | NULL |

　　+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+---------------------------------+

　　2 rows in set, 1 warning (0.00 sec)

　　## 不加where也是一样的。

　　优化此种情况

　　mysql> explain select * from person3 t1 left join person4 t2 on t1.version=t2.version join(select version from person3 order by a desc) a_order on t1.version=a_order.version;

　　+----+-------------+---------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+

　　| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

　　+----+-------------+---------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+

　　| 1 | SIMPLE | t1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 101 | 100.00 | NULL |

　　| 1 | SIMPLE | person3 | NULL | eq_ref | PRIMARY | PRIMARY | 22 | dev.t1.version | 1 | 100.00 | NULL |

　　| 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 22 | dev.t1.version | 1 | 100.00 | NULL |

　　+----+-------------+---------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+

　　3 rows in set, 1 warning (0.00 sec)

 

iqifenxia
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
正确理解Mysql中的列索引和多列索引
09-10
本篇文章是对Mysql中的列索引和多列索引进行了详细的分析介绍，需要的朋友参考下
oracle group by 很多列_【Oracle】序列、索引和同义词
weixin_39878991的博客
12-22 622
序列 ：提供有规律的数值。索引 ：提高查询的效率同义词 ：给对象起别名序列: 可供多个用户用来产生唯一数值的数据库对象自动提供唯一的数值共享对象主要用于提供主键值将序列值装入内存可以提高访问效率CREATE SEQUENCE 语句定义序列:CREATESEQUENCEsequence[INCREMENTBYn] --每次增长的数值[STARTWI...
一天一篇mysql之十：mysql中group by的执行原理
weixin_39666581的博客
08-30 3573
Group By实现原理以及作用 mysql中group by实现方式有三种，松散索引，紧凑索引，临时文件（文件排序）。 在网上看了相关的介绍，大部分介绍都比较晦涩难懂，这里说下我的理解。 在学习SQL优化时，我们都知道可以对group by进行优化的方式就是对group by引用的字段建立索引。当group by引用多个字段时，我们建立的相应的索引也应包含多个字段。 对group by操...
mysql多列索引groupby_报表开发之扩展GROUPBY
weixin_29677553的博客
02-02 315
在实际运用中，比如在数据仓库中，经常需要对数据进行多维分析，不仅需要标准分组的结果(相当于 GROUP BY)，还需要不同维度的小计(简单 GROUP BY 中取部分列分组)和合计(不分组)，从而 提供多角度的数据分析，对于这种复杂分组需求，简单 GROUP BY 很在实际运用中，比如在数据仓库中，经常需要对数据进行多维分析，不仅需要标准分组的结果(相当于GROUP BY)，还需要不同维度的小计(...
MySQL分组（group by）查询优化
zsh2050的专栏
08-11 2766
MySQL分组（group by）查询优化
mysql多列索引groupby_高性能MySQL-索引详解
weixin_39682944的博客
02-08 438
1.mysql的索引工作类似一本书的目录部分,想找某个特定主题,先查找书的目录部分,找到对应的页码2.ORM工具只能生成基本的合法的查询3.索引是在存储引擎层实现的,不是服务器层4.B-tree就是指的B树,多叉平衡查找树,很多存储引擎使用的b+树,降低磁盘I/O操作,将随机i/o变成顺序i/o5.b树意味着所有的值是按顺序存储的,每个叶子页到根的距离相同,叶子页存储了指向下一个叶子页的指针6.存...
MySQL索引使用说明(单列索引和多列索引)
12-16
在性能优化过程中，选择在哪些列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有两种类型的列：在Where子句中出现的列，在join子句中出现的列。请看下面这个查询： Select age ## 不使用索引 FROM people ...
MySQL 函数索引的优化方案
01-19
很多开发人员在使用MySQL时经常会在部分列上进行函数计算等，导致无法走索引，在数据量大的时候，查询效率低下。针对此种情况本文从MySQL5.7 及MySQL8.0中分别进行不同方式的优化。 1、 MySQL5.7 MySQL5.7版本中不...
MySQL查询优化之索引的应用详解
12-15
糟糕的SQL查询语句可对整个应用程序的运行产生严重的...每个表最多可以做到16个索引，此外MySQL还支持多列索引及全文检索。 给表添加一个索引非常简单，只需调用一个CREATE INDEX命令并为索引指定它的域即可。列表A给
mysql group by作用_group by实现原理及其作用
weixin_33186395的博客
02-01 1573
mysql中group by实现方式有三种，松散索引，紧凑索引，临时文件(文件排序)。在网上看了相关的介绍，大部分介绍都比较晦涩难懂，这里说下我的理解。在学习SQL优化时，我们都知道可以对group by进行优化的方式就是对group by引用的字段建立索引。当group by引用多个字段时，我们建立的相应的索引也应包含多个字段。对group by操作优化的原理就是让mysql利用索引，而避免进行...
ES多个字段group by操作
cainiao1412的博客
10-20 2万+
第一种方式 这种方式查询出来的数据不是扁平化的，而是一层套一层的，比如字段一套字段二。 GET 索引name/索引type/_search { "size": 0, "aggregations": { "字段一的结果命名": { "terms": { "field": "要group的字段" }, "aggregations": { "字段二的结果命名": { "terms": { "field": "要group的字段"
mysql多列索引（组合索引）特点和使用场景
CaptainCats的博客
07-04 1586
mysql多列索引的特点
为什么你创建的数据库索引没有生效，索引失效的条件！
热门推荐
徐刘根的博客
03-15 2万+
几乎所有的小伙伴都可以随口说几句关于创建索引的优缺点，也知道什么时候创建索引能够提高我们的查询性能，什么时候索引会更新，但是你有没有注意到，即使你设置了索引，有些时候索引他是不会生效的！这不仅考察了大家对索引的了解程度，还要让大家在使用的时候能够正确的使用。以下介绍了一些可能会造成索引失效的特殊情况，希望大家在平时开发和面试的时候能够注意到！ 一、如何判断数据库索引是否生效 首先在接着探讨...
MySQL学习(四)之索引篇
weixin_65743593的博客
01-19 2078
MySQL索引，B树，B+树，为什么需要注意联合索引中的顺序？为什么要建立联合索引？或者说联合索引的好处？什么是最左前缀法则？什么情况索引会失效？
MySQL组合索引（多列索引）使用与优化
pan_junbiao的博客
07-04 7056
我们经常听到一些人说"把WHERE条件里的列都加上索引"，其实这个建议非常错误。在多个列上建立单独的索引大部分情况下并不能提高MySQL的查询性能。MySQL 在5.0之后引入了一种叫“索引合并”（index merge）的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。但是当服务器对多个索引做联合操作时，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上，特别是当其中有些索引的选择性不高，需要合并扫描大量的数据的时候。这个时候，我们需要一个多列索引。 利用存储过程插入1000w
MySQL数据库多列索引详解
m0_60259116的博客
03-22 726
MySQL数据库多列索引详解
mysql（六）多列索引之索引顺序问题
weixin_43994761的博客
07-21 1536
使用索引常见的错误是。
mysql 多值索引详解
最新发布
jkzyx123的博客
11-06 451
mysql 8.0.17后的新功能多值索引详解
MYSQL json数组创建多值索引
ggggd233的博客
11-16 1740
MYSQL json数组创建多值索引有感 ##函数索引 ##多值索引
mysql多列索引解释
06-03
相较于单列索引，多列索引可以更好地优化查询，提高查询效率。当查询语句中使用了多个查询条件时，多列索引可以使MySQL更快地定位到匹配的记录。 在创建多列索引时，需要注意以下几点： 1. 索引顺序：索引的列顺序...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值