深圳设计网站费用,建筑工程信息查询,嘉陵 建设 摩托车官方网站,王也的八奇技是什么文章目录 0、order by优化原则1、Using filesort2、Using index3、连接数据库4、查看索引5、删除索引6、按照年龄进行排序7、执行计划 order by age8、执行计划 order by age,phone9、创建联合索引 (age,phone)10、再次执行计划 order by age11、再次执行计划 order by age,pho… 文章目录 0、order by优化原则1、Using filesort2、Using index3、连接数据库4、查看索引5、删除索引6、按照年龄进行排序7、执行计划 order by age8、执行计划 order by age,phone9、创建联合索引 (age,phone)10、再次执行计划 order by age11、再次执行计划 order by age,phone12、执行计划 order by age desc,phone desc13、执行计划 order by phone,age14、执行计划 order by age asc,phone desc15、Collation校对是用于定义字符如何比较和排序的16、创建索引联合索引 (age asc,phone desc)17、再次执行计划 order by age asc,phone desc18、再次执行计划 order by age asc,phone asc19、升序/降序联合索引结构图示: 0、order by优化原则 根据排序字段建立合适的索引多字段排序时也遵循最左前缀法则。尽量使用覆盖索引多字段排序一个升序一个降序此时需要注意联合索引在创建时的规则ASC/DESC如果不可避免的出现filesort大数据量排序时可以适当增大排序缓冲区大小sort_buffer_size默认256K mysql show variables like sort_buffer_size;
--------------------------
| Variable_name | Value |
--------------------------
| sort_buffer_size | 262144 |
--------------------------
1 row in set, 1 warning (0.01 sec)mysql1、Using filesort 通过表的索引或全表扫描读取满足条件的数据行然后在排序缓冲区sort buffer中完成排序操作所有不是通过索引直接返回排序结果的排序都叫FileSort排序 2、Using index 通过有序索引顺序扫描直接返回有序数据这种情况即为using index不需要额外排序操作效率高。 3、连接数据库
C:\Users\dgqmysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 MySQL Community Server - GPLCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type help; or \h for help. Type \c to clear the current input statement.mysql4、查看索引
mysql use mybatis-example;
Database changed
mysql show index from tb_user;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| tb_user | 0 | PRIMARY | 1 | id | A | 24 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 0 | idx_user_phone | 1 | phone | A | 24 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 0 | idx_user_phone_name | 1 | phone | A | 24 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 0 | idx_user_phone_name | 2 | name | A | 24 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 1 | profession | A | 16 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 2 | age | A | 22 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 3 | status | A | 24 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_email_5 | 1 | email | A | 23 | 5 | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_name | 1 | name | A | 24 | NULL | NULL | | BTREE | | | YES | NULL |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9 rows in set (0.02 sec)mysql5、删除索引
mysql drop index idx_user_phone_name on tb_user;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql drop index idx_user_phone on tb_user;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql drop index idx_user_name on tb_user;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql show index from tb_user;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| tb_user | 0 | PRIMARY | 1 | id | A | 24 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 1 | profession | A | 16 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 2 | age | A | 22 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 3 | status | A | 24 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_email_5 | 1 | email | A | 23 | 5 | NULL | YES | BTREE | | | YES | NULL |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 rows in set (0.00 sec)mysql6、按照年龄进行排序
mysql select id,age,phone from tb_user order by age;
-----------------------
| id | age | phone |
-----------------------
| 22 | 19 | 17799990021 |
| 23 | 20 | 17799990022 |
| 6 | 22 | 17799990005 |
| 1 | 23 | 17799990000 |
| 5 | 23 | 17799990004 |
| 7 | 24 | 17799990006 |
| 10 | 27 | 17799990009 |
| 11 | 27 | 17799990010 |
| 12 | 29 | 17799990011 |
| 24 | 29 | 17799990023 |
| 19 | 30 | 17799990018 |
| 16 | 31 | 17799990015 |
| 2 | 33 | 17799990001 |
| 3 | 34 | 17799990002 |
| 17 | 35 | 17799990016 |
| 8 | 38 | 17799990007 |
| 18 | 38 | 17799990017 |
| 15 | 40 | 17799990014 |
| 9 | 43 | 17799990008 |
| 14 | 43 | 17799990013 |
| 13 | 44 | 17799990012 |
| 20 | 51 | 17799990019 |
| 21 | 52 | 17799990020 |
| 4 | 54 | 17799990003 |
-----------------------
24 rows in set (0.00 sec)mysql7、执行计划 order by age
mysql explain select id,age,phone from tb_user order by age;
----------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 24 | 100.00 | Using filesort |
----------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysql8、执行计划 order by age,phone
mysql explain select id,age,phone from tb_user order by age,phone;
----------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 24 | 100.00 | Using filesort |
----------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysql9、创建联合索引 (age,phone)
mysql create index idx_user_age_phone on tb_user(age,phone);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql show index from tb_user;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| tb_user | 0 | PRIMARY | 1 | id | A | 24 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 1 | profession | A | 16 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 2 | age | A | 22 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 3 | status | A | 24 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_email_5 | 1 | email | A | 23 | 5 | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_age_phone | 1 | age | A | 19 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_age_phone | 2 | phone | A | 24 | NULL | NULL | | BTREE | | | YES | NULL |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7 rows in set (0.01 sec)mysql10、再次执行计划 order by age
mysql explain select id,age,phone from tb_user order by age;
----------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tb_user | NULL | index | NULL | idx_user_age_phone | 48 | NULL | 24 | 100.00 | Using index |
----------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysql11、再次执行计划 order by age,phone
mysql explain select id,age,phone from tb_user order by age,phone;
----------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tb_user | NULL | index | NULL | idx_user_age_phone | 48 | NULL | 24 | 100.00 | Using index |
----------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysql12、执行计划 order by age desc,phone desc
mysql explain select id,age,phone from tb_user order by age desc,phone desc;
-------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tb_user | NULL | index | NULL | idx_user_age_phone | 48 | NULL | 24 | 100.00 | Backward index scan; Using index |
-------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysqlExtra: 提供了关于MySQL如何解决查询的额外信息。Backward index scan表示MySQL正在向后扫描索引因为您使用了DESC。Using index表示MySQL只使用了索引来满足查询而不需要回表到数据行。 13、执行计划 order by phone,age
mysql explain select id,age,phone from tb_user order by phone,age;
--------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
--------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tb_user | NULL | index | NULL | idx_user_age_phone | 48 | NULL | 24 | 100.00 | Using index; Using filesort |
--------------------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysqlMySQL执行查询时使用了idx_user_age_phone索引但它仍然需要对结果进行额外的文件排序操作Using filesort。这是因为虽然查询使用了索引但索引的顺序先age后phone与查询中ORDER BY子句指定的顺序先phone后age不匹配。 Extra 中的 Using index; Using filesort 表示使用了索引但由于 排序顺序 与 索引顺序 不完全一致还需要额外的文件排序操作。Extra: 额外的信息。Using index表示MySQL只使用了索引来满足查询但Using filesort表示MySQL需要对结果进行额外的排序操作因为索引的顺序与ORDER BY子句指定的顺序不匹配。 14、执行计划 order by age asc,phone desc
mysql explain select id,age,phone from tb_user order by age asc,phone desc;
--------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
--------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tb_user | NULL | index | NULL | idx_user_age_phone | 48 | NULL | 24 | 100.00 | Using index; Using filesort |
--------------------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysql索引是按照(age, phone)的顺序构建的即首先按age排序然后在age相同的情况下按phone排序。但是由于您希望按phone降序排序而索引中的phone部分是升序的因此MySQL不能仅使用索引来满足ORDER BY子句的要求而必须进行额外的排序步骤。 15、Collation校对是用于定义字符如何比较和排序的
mysql show index from tb_user;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| tb_user | 0 | PRIMARY | 1 | id | A | 24 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 1 | profession | A | 16 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 2 | age | A | 22 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 3 | status | A | 24 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_email_5 | 1 | email | A | 23 | 5 | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_age_phone | 1 | age | A | 19 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_age_phone | 2 | phone | A | 24 | NULL | NULL | | BTREE | | | YES | NULL |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7 rows in set (0.00 sec)mysql在MySQL中Collation校对是用于定义字符如何比较和排序的。当你看到SHOW INDEX结果中的Collation列为A时这通常意味着使用了默认的字符集和校对规则。在MySQL中A通常代表latin1_swedish_ci对于latin1字符集或类似的默认校对规则但具体的含义可能会根据MySQL的版本和配置有所不同。
在latin1_swedish_ci中
latin1 是字符集character set它支持西欧语言中的字符。swedish_ci 是校对规则collation它定义了字符如何比较和排序。ci表示大小写不敏感case-insensitive。 在 MySQL 中“Collation”排序规则用于定义字符数据的排序和比较方式。 “A” 通常表示升序排序Ascending意味着在比较和排序字符数据时按照字母表顺序从小到大进行排列。 16、创建索引联合索引 (age asc,phone desc)
mysql create index idx_user_age_pho_ad on tb_user(age asc,phone desc);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql show index from tb_user;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| tb_user | 0 | PRIMARY | 1 | id | A | 24 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 1 | profession | A | 16 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 2 | age | A | 22 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 3 | status | A | 24 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_email_5 | 1 | email | A | 23 | 5 | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_age_phone | 1 | age | A | 19 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_age_phone | 2 | phone | A | 24 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_age_pho_ad | 1 | age | A | 19 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_age_pho_ad | 2 | phone | D | 24 | NULL | NULL | | BTREE | | | YES | NULL |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9 rows in set (0.01 sec)mysql17、再次执行计划 order by age asc,phone desc
mysql explain select id,age,phone from tb_user order by age asc,phone desc;
-----------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tb_user | NULL | index | NULL | idx_user_age_pho_ad | 48 | NULL | 24 | 100.00 | Using index |
-----------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysql18、再次执行计划 order by age asc,phone asc
mysql explain select id,age,phone from tb_user order by age asc,phone asc;
----------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | tb_user | NULL | index | NULL | idx_user_age_phone | 48 | NULL | 24 | 100.00 | Using index |
----------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysql19、升序/降序联合索引结构图示:
