MySQL高级部分学习

最新推荐文章于 2022-09-03 17:19:29 发布

泡椒酱的java学习记录

最新推荐文章于 2022-09-03 17:19:29 发布

阅读量356

点赞数 1

分类专栏： MySQL基础系列文章标签： mysql

本文链接：https://blog.csdn.net/jja_wangfei/article/details/105546751

版权

MySQL基础系列专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL高级部分学习

1、docker 安装MySQL

# 拉取MySQL镜像
[root@spark ~]# docker pull mysql
# 得到如下：  我本地原来已经拉取下来了
Using default tag: latest
latest: Pulling from library/mysql
Digest: sha256:4a30434ce03d2fa396d0414f075ad9ca9b0b578f14ea5685e24dcbf789450a2c
Status: Image is up to date for mysql:latest
# 或者指定版本号：
[root@spark ~]# docker pull mysql:5.5
# 得到如下： 我本地原来已经拉取下来了
5.5: Pulling from library/mysql
Digest: sha256:12da85ab88aedfdf39455872fb044f607c32fdc233cd59f1d26769fbf439b045
Status: Image is up to date for mysql:5.5

2、 docker运行MySQL：

[root@spark ~]# docker run -p 3306:3306 --name mysql -v $PWD/conf:/etc/mysql.d -v $PWD/logs -v$PWD/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql
# 得到如下的镜像id：
4c3a8d9df601f2dc98c4b31b9162fda0ab6ce045eb0d8c4e7d1855b62111eda9
# 或者我本地已经运行了一个MySQL了 最新版的，所以执行5.5版本的时候，可用如下的命令：
[root@spark ~]# docker create --name mysql_jia -v /data/mysql-data:/var/lib/mysql -p 3308:3308 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.5

# 然后：
[root@spark ~]# docker start mysql_jia
mysql_jia
# 再然后：
[root@spark ~]# docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
a3416a755dea        mysql:5.5           "docker-entrypoint.s…"   22 minutes ago      Up 21 minutes       3306/tcp, 0.0.0.0:3308->3308/tcp    mysql_jia
9450eac823ea        mysql               "docker-entrypoint.s…"   10 days ago         Up 13 hours         0.0.0.0:3306->3306/tcp, 33060/tcp   mysql


# 查看：
[root@spark ~]# docker ps -la
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
4c3a8d9df601        mysql               "docker-entrypoint.s…"   8 seconds ago       Up 5 seconds        0.0.0.0:3306->3306/tcp, 33060/tcp   mysqls

# 进入容器：
[root@spark ~]# docke exec -it 4c3a8d9df601 /bin/bash
root@4c3a8d9df601:/# mysql -uroot -p
# 然后输入你的密码即可

3、7种join图

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-12q95ffc-1586962154616)(C:\Users\Administrator\Desktop\join图.png)]
在这里插入图片描述

4、sql解释

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

mysql> create database jia;
Query OK, 1 row affected (0.01 sec)

mysql> use jia;

mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
|  1 | RD       | 11     |
|  2 | HR       | 12     |
|  3 | MK       | 13     |
|  4 | MIS      | 14     |
|  5 | FD       | 15     |name
+----+----------+--------+
5 rows in set (0.00 sec)

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)


#1、 内联
mysql> select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;
+----+------+--------+----+----------+--------+
| id | name | deptId | id | deptName | locAdd |
+----+------+--------+----+----------+--------+
|  1 | z3   |      1 |  1 | RD       | 11     |
|  2 | z4   |      1 |  1 | RD       | 11     |
|  3 | z5   |      1 |  1 | RD       | 11     |
|  4 | w5   |      2 |  2 | HR       | 12     |
|  5 | w6   |      2 |  2 | HR       | 12     |
|  6 | s7   |      3 |  3 | MK       | 13     |
|  7 | s8   |      4 |  4 | MIS      | 14     |
+----+------+--------+----+----------+--------+
7 rows in set (0.00 sec)

# 2、左联
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;
+----+------+--------+------+----------+--------+
| id | name | deptId | id   | deptName | locAdd |
+----+------+--------+------+----------+--------+
|  1 | z3   |      1 |    1 | RD       | 11     |
|  2 | z4   |      1 |    1 | RD       | 11     |
|  3 | z5   |      1 |    1 | RD       | 11     |
|  4 | w5   |      2 |    2 | HR       | 12     |
|  5 | w6   |      2 |    2 | HR       | 12     |
|  6 | s7   |      3 |    3 | MK       | 13     |
|  7 | s8   |      4 |    4 | MIS      | 14     |
|  8 | s9   |     51 | NULL | NULL     | NULL   |
+----+------+--------+------+----------+--------+
8 rows in set (0.00 sec)

# 3、右联
mysql> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
+------+------+--------+----+----------+--------+
| id   | name | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
|    1 | z3   |      1 |  1 | RD       | 11     |
|    2 | z4   |      1 |  1 | RD       | 11     |
|    3 | z5   |      1 |  1 | RD       | 11     |
|    4 | w5   |      2 |  2 | HR       | 12     |
|    5 | w6   |      2 |  2 | HR       | 12     |
|    6 | s7   |      3 |  3 | MK       | 13     |
|    7 | s8   |      4 |  4 | MIS      | 14     |
| NULL | NULL |   NULL |  5 | FD       | 15     |
+------+------+--------+----+----------+--------+
8 rows in set (0.00 sec)

# 4、待定
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;
+----+------+--------+------+----------+--------+
| id | name | deptId | id   | deptName | locAdd |
+----+------+--------+------+----------+--------+
|  8 | s9   |     51 | NULL | NULL     | NULL   |
+----+------+--------+------+----------+--------+
1 row in set (0.00 sec)

# 5、待定
mysql> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
+------+------+--------+----+----------+--------+
| id   | name | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
| NULL | NULL |   NULL |  5 | FD       | 15     |
+------+------+--------+----+----------+--------+
1 row in set (0.01 sec)

# 6、全集
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id
    -> union
    -> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
+------+------+--------+------+----------+--------+
| id   | name | deptId | id   | deptName | locAdd |
+------+------+--------+------+----------+--------+
|    1 | z3   |      1 |    1 | RD       | 11     |
|    2 | z4   |      1 |    1 | RD       | 11     |
|    3 | z5   |      1 |    1 | RD       | 11     |
|    4 | w5   |      2 |    2 | HR       | 12     |
|    5 | w6   |      2 |    2 | HR       | 12     |
|    6 | s7   |      3 |    3 | MK       | 13     |
|    7 | s8   |      4 |    4 | MIS      | 14     |
|    8 | s9   |     51 | NULL | NULL     | NULL   |
| NULL | NULL |   NULL |    5 | FD       | 15     |
+------+------+--------+------+----------+--------+
9 rows in set (0.00 sec)

# 7、a 独有 + b 的独有
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null
    -> union
    -> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
+------+------+--------+------+----------+--------+
| id   | name | deptId | id   | deptName | locAdd |
+------+------+--------+------+----------+--------+
|    8 | s9   |     51 | NULL | NULL     | NULL   |
| NULL | NULL |   NULL |    5 | FD       | 15     |
+------+------+--------+------+----------+--------+
2 rows in set (0.00 sec)

2、索引

在数据库系统还维护着一些特定查找算法的数据结构，用于某种方式引用数据。索引的本质是一种数据结构，用于快速查找和排序。索引的目的是在于提高查询效率。

1、索引的分类：

单值索引：只包含了单个列，一个表可以有多个单列索引；

唯一索引：索引列的值必须唯一，但允许有空值；

mysql> mysql> show index from tbl_emp;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tbl_emp |          0 | PRIMARY    |            1 | id          | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tbl_emp |          1 | fk_dept_id |            1 | deptId      | A         |           5 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

2、索引结构

在这里插入图片描述
BTree索引：多路复用结构

那些情况下需要建立索引:

1、主键自动建立唯一索引；

2、频繁作为查询条件的字段应该建立索引；

3、查询中与其他表关联的字段，外键管子建立索引；

4、频繁更新的字段不适合建立索引，因为每次更新记录，还要更新索引；

5、where条件里用不到的字段不需要创建索引；

6、单键/组合索引的组合问题，在高频发情况下倾向于创建组合索引；

7、查询中排序的字段，排序字段若通过索引去访问将大大提高排序效率；

8、查询中统计的或者分组的字段；

3、性能分析

1、查询优化器：

2、MySQL的常见瓶颈：

1. CPU：CPU在饱和的时候一般发生在数据装入内存或者从磁盘上读取数据的时候；
2. IO：装入数据远大于内存容量的时候；
3. 服务器的硬件瓶颈：top、free等

3、explain：

mysql> explain select * from tbl_emp;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tbl_emp | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

explain 作用：

获取表的读取顺序；
1. 数据读取操作的操作类型；
2. 哪些索引可以被使用；
3. 哪些索引被实际使用；
4. 表之间的关系；
5. 每张表有多少行被优化器查询

explain表头信息：

id   select_type   table   partitions    type   possibale_keys    key key_len    ref    rows    filtered     Extra

在这里插入图片描述

mysql> explain
    ->   select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null
    ->   union
    ->   select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                          |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------+
|  1 | PRIMARY      | a          | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL                                                           |
|  1 | PRIMARY      | b          | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    4 |    25.00 | Using where; Not exists; Using join buffer (Block Nested Loop) |
|  2 | UNION        | b          | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL                                                           |
|  2 | UNION        | a          | NULL       | ALL  | fk_dept_id    | NULL | NULL    | NULL |    8 |    12.50 | Using where; Using join buffer (Block Nested Loop)             |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary                                                |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------+
5 rows in set, 1 warning (0.01 sec)

解释：也就是说，上面的查询，会先执行表b ,再执行表a, 然后再执行表a，再执行表b。（因为属于第三种，id相同又不同，id越大的先执行，id相同的情况下，顺序执行）。

select_type:
在这里插入图片描述

mysql> explain
    ->   select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null
    ->   union
    ->   select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                          |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------+
|  1 | PRIMARY      | a          | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL                                                           |
|  1 | PRIMARY      | b          | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    4 |    25.00 | Using where; Not exists; Using join buffer (Block Nested Loop) |
|  2 | UNION        | b          | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL                                                           |
|  2 | UNION        | a          | NULL       | ALL  | fk_dept_id    | NULL | NULL    | NULL |    8 |    12.50 | Using where; Using join buffer (Block Nested Loop)             |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary                                                |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------------+
5 rows in set, 1 warning (0.01 sec)

type:

在这里插入图片描述

mysql> explain select * from tbl_emp, tbl_dept where tbl_emp.deptId = tbl_dept.id;
+----+-------------+----------+------------+------+---------------+------------+---------+-----------------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key        | key_len | ref             | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------------+---------+-----------------+------+----------+-------+
|  1 | SIMPLE      | tbl_dept | NULL       | ALL  | PRIMARY       | NULL       | NULL    | NULL            |    4 |   100.00 | NULL  |
|  1 | SIMPLE      | tbl_emp  | NULL       | ref  | fk_dept_id    | fk_dept_id | 5       | jia.tbl_dept.id |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------------+---------+-----------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

在这里插入图片描述

mysql> explain select * from tbl_emp where deptId = '3';
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tbl_emp | NULL       | ref  | fk_dept_id    | fk_dept_id | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

在这里插入图片描述

mysql> explain select * from tbl_emp where id between 2 and 4;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_emp | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

在这里插入图片描述

mysql> explain select * from tbl_emp where id = '2';
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tbl_emp | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

在这里插入图片描述

mysql> explain select id from tbl_emp;
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_emp | NULL       | index | NULL          | fk_dept_id | 5       | NULL |    8 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

possible_keys和key:

理论上实际上所用的索引。

如上面的例子。查询中若使用了覆盖索引，则只能出现在key中。

覆盖索引：简单的说就是，select后面查询的字段和创建的索引的顺序和个数是一致的。

如下：在这里插入图片描述

mysql> explain select id from tbl_emp;
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_emp | NULL       | index | NULL          | fk_dept_id | 5       | NULL |    8 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

说明：因为我创建了主键索引，所以相当于是覆盖索引的特例。因为查询的字段和创建索引的字段顺序和个数是一致的。

为了更好的说明情况，我们创建如下的索引：

mysql> create index idx_name_deptId on tbl_emp  (name,deptId);
Query OK, 0 rows affected (0.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

在这里插入图片描述

mysql> explain select name,deptId from tbl_emp;
+----+-------------+---------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key             | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_emp | NULL       | index | NULL          | idx_name_deptId | 68      | NULL |    8 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

ref：
在这里插入图片描述

mysql> explain select * from tbl_emp, tbl_dept where tbl_emp.deptId = tbl_dept.id and tbl_emp.name = 'z3';
+----+-------------+----------+------------+--------+----------------------------+-----------------+---------+--------------------+------+----------+--------------------------+
| id | select_type | table    | partitions | type   | possible_keys              | key             | key_len | ref                | rows | filtered | Extra                    |
+----+-------------+----------+------------+--------+----------------------------+-----------------+---------+--------------------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_emp  | NULL       | ref    | fk_dept_id,idx_name_deptId | idx_name_deptId | 63      | const              |    1 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | tbl_dept | NULL       | eq_ref | PRIMARY                    | PRIMARY         | 4       | jia.tbl_emp.deptId |    1 |   100.00 | NULL                     |
+----+-------------+----------+------------+--------+----------------------------+-----------------+---------+--------------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.01 sec)

注意： 范围查询的话，创建的对应字段的索引会失效。

两个表进行连接，left join 条件用于确定如何从右表搜索行，左表一定都有。所以在创建索引的时候，应该将索引创建在右表上。

同理，right join 肯定是右表都有，左表视情况，要么全有，要么部分有，因此创建索引的时候，应该建在左表上。

总结：相反建索引。

三个表创建索引情况：一样

小结果集驱动大结果集

防止索引失效：

序号	内容
1	全值匹配最佳（创建的索引列和查询列一致，顺序也一致）
2	最佳左前缀法则（如果索引了多列，遵守最佳左前缀法则，指：查询从索引的最左前列开始且不跳过索引的列）
3	不在索引列上做操作（计算，函数、类型转换），这样可能导致索引失效而转向全表扫描
4	存储引擎不能使用索引中范围条件右表的列
5	尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select * 的操作
6	MySQL在使用不等于的时候，无法使用索引，这样导致全表扫描
7	is null，is not null 也无法使用索引
8	like 以通配符开头，MySQL索引失效会变成全表扫描的操作
9	字符串不加单引号导致索引失效
10	少用or操作，这样会导致索引失效

最佳左前缀法则：

带头大哥不能死。。。。带头大哥死了，剩下的所有小弟就没法用了，不起作用了。

中间兄弟不能断。。。。中间一个兄弟断了，这个小弟后面的小弟就不起作用了。

索引列上无计算。。。。

百分like加右边。。。。

范围之后全失效。。。。

字符串里有引号。。。。

在实际生产中，like的时候，必要情况下使用"%xxx%"这样的查询，为了使索引不失效，我们可以创建覆盖索引，即查询的字段的顺序和个数与创建的索引的字段和顺序是一致的。

在mybatis中为了避免使用"%xxx"这种查询，因为这种查询会导致索引失效的，因此在mybatis中使用concat进行拼接。

假设我们创建了索引： idx_a_b_c

在where之后的字段中，如where a = ‘1’ and b = ‘2’ and c = ‘3’ 等价于 where a = ‘1’ and c = ‘3’ and b = ‘2’ 等价于 where b = ‘2’ and a = ‘1’ and c = ‘3’；

where a = ‘1’ and b = ‘2’ and c > ‘3’ ：同样用到了三个索引，覆盖索引。 ref const const

where a = ‘1’ and b > 2 and c = ‘3’ : 同上，MySQL做了优化，会先检查索引顺序的。 ref const const

where a = ‘1’ and c = ‘3’ order by b：用了一个索引，即a这个是有效的。b的作用在于排序，而非查找，因此也算的上用到了这个索引。 ref const

where a = ‘1’ order by b：只用到了第一个； ref const

where a = ‘1’ order by c：只用到了一个； ref const

where a = ‘1’ order by b, c：一个 ref const

where a = ‘1’ orde by c,b：一个 ref const using filesort

where a = ‘1’ and b = ‘2’ order by b,c：两个 ref const const 因为排序字段 b = ‘2’ 已经是常量了

where a = ‘1’ and b = ‘2’ order by c,b：一个 ref const using filesort

where a = ‘1’ group by b, c：一个 ref const

where a = ‘1’ group by c,b：一个 ref const using temporary using filesort

group by 基本上都需要进行排序，因此会有临时表产生

下面看看MySQL5.5版本和最新版8.x版本的对索引的区别：

准备数据：
在这里插入图片描述

索引情况：
在这里插入图片描述

创建覆盖索引后的索引情况：

在这里插入图片描述

5.5版本的覆盖索引的查询情况：
在这里插入图片描述

说明：type为ref级别的，四个const，用到了覆盖索引

5.5版本范围查询的情况：
在这里插入图片描述

说明：只有到了第一个索引（ttitle），范围之后的索引没用到，ref级别，一个const

8版本范围查询的情况：
在这里插入图片描述

同上；

5.5版本范围查询没用到索引的情况：
在这里插入图片描述

在这里插入图片描述
截图\5.5版本第二个索引没用到的情况.png)]

在这里插入图片描述
截图\5.5版本排序字段有一个常量的情况.png)]

截图\5.5版本排序字段非常量的情况.png)]

截图\5.5版本两个排序字段都为非常量且顺序正常的情况.png)]
在这里插入图片描述截图\8版本排序字段有一个常量的情况.png)]

在这里插入图片描述
截图\8版本两个字段排序同时其中一个为常量的情况.png)]

截图\5.5版本两个排序字段都为非常量且顺序正常的情况.png)]
在这里插入图片描述
截图\5,5版本两个排序字段为非常量且逆序的情况.png)]

截图\8版本两个排序字段为非常量且逆序的情况.png)]
在这里插入图片描述
截图\5.5版本两个聚合字段为非常量且顺序的情况.png)]

\截图\8版本聚合字段两个字段为非常量且顺序的情况.png)]

截图\5.5版本两个聚合字段为非常量且逆序的情况.png)]

截图\8版本两个排序字段为非常量且逆序的情况.png)]
在这里插入图片描述
\截图\8版本聚合字段没显性出现在查询字段的异常情况.png)]

\截图\8版本聚合字段两个字段为非常量且顺序的情况.png)]

截图\8版本聚合字段两个字段为非常量且逆序的情况.png)]
在这里插入图片描述

泡椒酱的java学习记录

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL高级部分学习

MySQL高级部分学习1、docker 安装MySQL# 拉取MySQL镜像[root@spark ~]# docker pull mysql# 得到如下：我本地原来已经拉取下来了Using default tag: latestlatest: Pulling from library/mysqlDigest: sha256:4a30434ce03d2fa396d0414f075...
复制链接

扫一扫

专栏目录