个人中心
文章发布
退出
作者热门文章
- Java锁的逻辑（结合对象头和ObjectMonitor）
- 还在用饼状图？来瞧瞧这些炫酷的百分比可视化新图形（附代码实现）⛵
- 自动注册实体类到EntityFrameworkCore上下文，并适配ABP及ABPVNext
- 基于Sklearn机器学习代码实战
25
4
如果有多条数据需要同时插入，不要每次插入一条，然后分多次插入，因为每执行一次插入的操作，都要进行数据库的连接，多个操作就会连接多次，而一次批量操作只需要连接1次 。
因为Mysql默认每执行一次操作，就会提交一次事务，这样就会涉及到频繁的事务的开启与关闭 。
start
transaction
;
insert
into
表名
values
(),(),();
insert
into
表名
values
(),(),();
insert
into
表名
values
(),(),();
commit
;
主键一般是默认自增的，但是也可以手动增加，这里不建议手动乱序增加，而是使用默认的顺序增加，原因会在后面解释.
如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用Mysql数据库提供的load指令进行插入， 。
首先在连接数据库的时候需要加上 --local-infile 参数 。
mysql
--
local-infile -u root -p
在使用本地文件加载功能的时候，需要先查看本地加载文件选项是否开启的 。
mysql
>
select
@@local_infile
;
+
--
--------------+
|
@@local_infile
|
+
--
--------------+
|
0
|
+
--
--------------+
1
row
in
set
(
0.00
sec)
说明1：0表示本地加载文件并未开启 。
开启本地加载文件的语句 。
mysql
>
set
global local_infile
=
1
;
Query OK,
0
rows affected (
0.01
sec)
mysql
>
select
@@local_infile
;
+
--
--------------+
|
@@local_infile
|
+
--
--------------+
|
1
|
+
--
--------------+
1
row
in
set
(
0.00
sec)
创建一个空表tb_user，其表结构如下 。
mysql
>
desc
tb_user;
+
--
--------+-------------+------+-----+---------+----------------+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+
--
--------+-------------+------+-----+---------+----------------+
|
id
|
int
|
NO
|
PRI
|
NULL
|
auto_increment
|
|
username
|
varchar
(
50
)
|
NO
|
UNI
|
NULL
|
|
|
password
|
varchar
(
50
)
|
NO
|
|
NULL
|
|
|
name
|
varchar
(
20
)
|
NO
|
|
NULL
|
|
|
birthday
|
date
|
YES
|
|
NULL
|
|
|
sex
|
char
(
1
)
|
YES
|
|
NULL
|
|
+
--
--------+-------------+------+-----+---------+----------------+
6
rows
in
set
(
0.01
sec)
使用load加载本地文件 'tb_user_data.sql' 内容到新创建的表中,其中tb_user_data.sql中的测试数据如下 。
houlei
@houleideMacBook
-
Pro Desktop
%
cat tb_user_data.sql
1
,a,aa,aaa,
2023
-
07
-
01
,
1
2
,b,bb,bbb,
2023
-
07
-
02
,
0
3
,c,cc,ccc,
2023
-
07
-
03
,
1
4
,d,dd,ddd,
2023
-
07
-
04
,
0
5
,e,ee,eee,
2023
-
07
-
05
,
1
6
,f,ff,fff,
2023
-
07
-
06
,
0
7
,g,gg,ggg,
2023
-
07
-
07
,
1
houlei
@houleideMacBook
-
Pro Desktop
%
使用load加载本地文件 'tb_user_data.sql' 内容到新创建的表中 。
mysql
>
load
data local infile
'
/Users/houlei/Desktop/tb_user_data.sql
'
into
table
tb_user fields terminated
by
'
,
'
lines terminated
by
'
\n
'
;
Query OK,
7
rows affected (
0.01
sec)
Records:
7
Deleted:
0
Skipped:
0
Warnings:
0
说明1： load data local infile 是加载本地文件的意思， 。
说明2：'/Users/houlei/Desktop/tb_user_data.sql'是文件路径 。
说明3：into table tb_user 是将文件中的数据，插入到tb_user表中 。
说明4：fields terminated by ',' 是说每个字段之间的数据是使用','分割的 。
说明5：lines terminated by '\n' 是说每一行之间的数据使用的是‘\n’分割的 。
说明6：本方法只是举例，在实际运用大数据量插入时100万条数据的插入至少要数分钟，如果使用load方法只需要十几秒 。
。
在InnoDB储存引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table）IOT 。
说明1：在索引的B+数中所有的数据保存在叶子节点上，非叶子节点只保存主键key的值 。
说明2：索引中的各个节点都是保存在逻辑结构页上面的，一页默认大小16K 。
。
页可以为空，也可以填充一半，也可以填充100%，每个页包含了2至N行数据，根据主键排列 。
情况1：主键顺序插入 。
。
说明1：row是行数据，每一页上可以存放多个行数据。 。
情况2：主键乱序插入 。
说明1：当我们想要在插入一个id=50的数据时，会发生页分裂 。
说明2：这时会将 1#page 页里面的数据超过 50% 的数据，移动到新开辟的 3#page 页中 。
说明3：然后将 id=50 的数据也拼接到 3#page 页中 。
说明4：这时就会出现一个问题， 3#page 中的索引比 2#page 页中的索引小，所以还需要将 3#page 页前置，这就叫页分裂 。
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用 。
当页中删除的记录达到 merge_threshold (默认为页的50%)，InnoDB 会开始寻找最靠近的页（前或者后）看看是否可以合并以优化空间使用 。
说明1：这时在 2#page 删除了13,14,15,16数据后，该页空余空间超过50%时就会寻找前一页或者后一页，是否同样有不满足50%,可以合并的 。
说明2：这时 1#page 页是满的，不能合并， 3#page 页不满可以合并，所以 3#page 页迁移到 2#page 页中 。
说明3：这时如果在有数据20插入就可以直接插入到3#page页上了，这就是页合并.
。
。
通过表的索引或者全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序 。
通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外的排序，操作效率高， 即排序的列表字段符合覆盖索引 .
emp表结构:
mysql
>
desc
emp;
+
--
---------+-------------+------+-----+---------+----------------+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+
--
---------+-------------+------+-----+---------+----------------+
|
id
|
int
|
NO
|
PRI
|
NULL
|
auto_increment
|
|
name
|
varchar
(
20
)
|
YES
|
|
NULL
|
|
|
age
|
int
|
YES
|
|
NULL
|
|
|
job
|
varchar
(
20
)
|
YES
|
|
NULL
|
|
|
salary
|
int
|
YES
|
|
NULL
|
|
|
entrydate
|
date
|
YES
|
|
NULL
|
|
|
managerid
|
int
|
YES
|
|
NULL
|
|
|
dept_id
|
int
|
YES
|
MUL
|
NULL
|
|
+
--
---------+-------------+------+-----+---------+----------------+
8
rows
in
set
(
0.01
sec)
emp表中索引情况 。
mysql
>
show
index
from
emp;
+
--
-----+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
|
Table
|
Non_unique
|
Key_name
|
Seq_in_index
|
Column_name
|
Collation
|
Cardinality
|
Sub_part
|
Packed
|
Null
|
Index_type
|
Comment
|
Index_comment
|
Visible
|
Expression
|
+
--
-----+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
|
emp
|
0
|
PRIMARY
|
1
|
id
|
A
|
7
|
NULL
|
NULL
|
|
BTREE
|
|
|
YES
|
NULL
|
|
emp
|
1
|
fk_dept
|
1
|
dept_id
|
A
|
3
|
NULL
|
NULL
|
YES
|
BTREE
|
|
|
YES
|
NULL
|
+
--
-----+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
案例1：对查询结果进行按 salary 和 age 都进行升序排序 。
mysql
>
explain
select
salary,age
from
emp
order
by
salary, age;
+
--
--+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
+
--
--+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|
1
|
SIMPLE
|
emp
|
NULL
|
ALL
|
NULL
|
NULL
|
NULL
|
NULL
|
7
|
100.00
|
Using filesort
|
+
--
--+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1
row
in
set
,
1
warning (
0.00
sec)
说明1：Extra 中值为 Using filesort 说明是先查出来需要的数据，然后再排序的,效率不高.
说明2：为什么会出现Using filesort呢？因为查询的这些字段在查询之前是无须的，索引需要先将数据查询出来，然后再做排序，这样才能得到想要的排序好的数据.
案例2：给 salary 和 age 添加一个联合排序 。
mysql
>
create
index
salary_age_idx
on
emp(salary,age);
Query OK,
0
rows affected (
0.07
sec)
Records:
0
Duplicates:
0
Warnings:
0
mysql
>
show
index
from
emp;
+
--
-----+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
|
Table
|
Non_unique
|
Key_name
|
Seq_in_index
|
Column_name
|
Collation
|
Cardinality
|
Sub_part
|
Packed
|
Null
|
Index_type
|
Comment
|
Index_comment
|
Visible
|
Expression
|
+
--
-----+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
|
emp
|
0
|
PRIMARY
|
1
|
id
|
A
|
7
|
NULL
|
NULL
|
|
BTREE
|
|
|
YES
|
NULL
|
|
emp
|
1
|
fk_dept
|
1
|
dept_id
|
A
|
3
|
NULL
|
NULL
|
YES
|
BTREE
|
|
|
YES
|
NULL
|
|
emp
|
1
|
salary_age_idx
|
1
|
salary
|
A
|
7
|
NULL
|
NULL
|
YES
|
BTREE
|
|
|
YES
|
NULL
|
|
emp
|
1
|
salary_age_idx
|
2
|
age
|
A
|
7
|
NULL
|
NULL
|
YES
|
BTREE
|
|
|
YES
|
NULL
|
+
--
-----+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4
rows
in
set
(
0.00
sec)
说明1：联合索引 salary_age_idx 中 salary 是第一索引字段，age 是第二索引字段 。
说明2：Collation 中A 代表升序，D 代表降序 。
。
案例3：再次使用 order by 对 salary 和 age 进行升序排序 。
mysql
>
explain
select
salary,age
from
emp
order
by
salary,age;
+
--
--+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
|
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
+
--
--+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
|
1
|
SIMPLE
|
emp
|
NULL
|
index
|
NULL
|
salary_age_idx
|
10
|
NULL
|
7
|
100.00
|
Using
index
|
+
--
--+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
1
row
in
set
,
1
warning (
0.00
sec)
mysql
>
explain
select
salary,age
from
emp
order
by
salary;
+
--
--+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
|
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
+
--
--+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
|
1
|
SIMPLE
|
emp
|
NULL
|
index
|
NULL
|
salary_age_idx
|
10
|
NULL
|
7
|
100.00
|
Using
index
|
+
--
--+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
1
row
in
set
,
1
warning (
0.00
sec)
说明1：在做升序排列时，无论 order by 后面是组合索引的全部字段，还是只有部分字段，这时 Extra 的值都是Usind index，所以其查询的结果直接就是排序好的结果 。
说明2：为什么呢？因为这个时候 salary和age是一个联合索引，索引在文件中是一个带顺序的b+数结构，所以将这个字段建立一个联合索引，就意味着使用索引查询的时候，就已经是带着顺序的数据了，所以这个时候就不需要在将数据从新在排序了，这样的查询效率就会更高.
案例4: order by 中的字段顺序和索引顺序不一致的情况 。
mysql
>
explain
select
salary,age
from
emp
order
by
age, salary;
+
--
--+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
|
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
+
--
--+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
|
1
|
SIMPLE
|
emp
|
NULL
|
index
|
NULL
|
salary_age_idx
|
10
|
NULL
|
7
|
100.00
|
Using
index
; Using filesort
|
+
--
--+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-----------------------------+
1
row
in
set
,
1
warning (
0.00
sec)
说明1：这个时候order by 是age在前，salary在后，和索引的顺序不一致，仍然会触发索引，使用Using index，但是也会使用Using filesort,所以推荐大家使用正确的索引顺序的字段来进行排序 。
。
案例5：对salary和age做降序查询 。
mysql
>
explain
select
salary,age
from
emp
order
by
salary
desc
, age
desc
;
+
--
--+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------+
|
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
+
--
--+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------+
|
1
|
SIMPLE
|
emp
|
NULL
|
index
|
NULL
|
salary_age_idx
|
10
|
NULL
|
7
|
100.00
|
Backward
index
scan; Using
index
|
+
--
--+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------+
1
row
in
set
,
1
warning (
0.00
sec)
mysql>
explain select id,salary,age from emp order by salary desc
;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------+
| 1 | SIMPLE | emp | NULL | index | NULL | salary_age_idx | 10 | NULL | 7 | 100.00 |
Backward index scan; Using index
|
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
说明1：无论是对salary和age同时做降序还是对其中一个字段做降序排列，都会出现 Backward index scan; Using index，其中 Backward index scan 是反向扫描索引 。
说明2：这是因为索引中默认的顺序是升序的，而做降序排列，就需要反向扫描索引了 。
案例7：创建一个 salary 和 age 都是降序的索引 。
create
index
salary_age_desc_idx
on
emp(salary
desc
, age
desc
);
Query OK,
0
rows affected (
0.02
sec)
Records:
0
Duplicates:
0
Warnings:
0
查询目前所有的索引 。
mysql
>
show
index
from
emp;
+
--
-----+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
|
Table
|
Non_unique
|
Key_name
|
Seq_in_index
|
Column_name
|
Collation
|
Cardinality
|
Sub_part
|
Packed
|
Null
|
Index_type
|
Comment
|
Index_comment
|
Visible
|
Expression
|
+
--
-----+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
|
emp
|
0
|
PRIMARY
|
1
|
id
|
A
|
7
|
NULL
|
NULL
|
|
BTREE
|
|
|
YES
|
NULL
|
|
emp
|
1
|
fk_dept
|
1
|
dept_id
|
A
|
3
|
NULL
|
NULL
|
YES
|
BTREE
|
|
|
YES
|
NULL
|
|
emp
|
1
|
salary_age_desc_idx
|
1
|
salary
|
D
|
7
|
NULL
|
NULL
|
YES
|
BTREE
|
|
|
YES
|
NULL
|
|
emp
|
1
|
salary_age_desc_idx
|
2
|
age
|
D
|
7
|
NULL
|
NULL
|
YES
|
BTREE
|
|
|
YES
|
NULL
|
|
emp
|
1
|
salary_age_idx
|
1
|
salary
|
A
|
7
|
NULL
|
NULL
|
YES
|
BTREE
|
|
|
YES
|
NULL
|
|
emp
|
1
|
salary_age_idx
|
2
|
age
|
A
|
7
|
NULL
|
NULL
|
YES
|
BTREE
|
|
|
YES
|
NULL
|
+
--
-----+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6
rows
in
set
(
0.01
sec)
说明1：这里 salary_age_desc_idx 就是根据 salary 和 age 做的降序索引，其Collation中的D即降序的意思 。
案例8：使用salary_age_desc_idx索引然后在使用order by降序查询 。
mysql
>
explain
select
salary,age
from
emp
use
index
(salary_age_desc_idx)
order
by
salary
desc
, age
desc
;
+
--
--+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
|
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
+
--
--+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
|
1
|
SIMPLE
|
emp
|
NULL
|
index
|
NULL
|
salary_age_desc_idx
|
10
|
NULL
|
7
|
100.00
|
Using
index
|
+
--
--+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
1
row
in
set
,
1
warning (
0.01
sec)
说明1：这个时候的Extra 中显示的 Using index，效率就会比较高了 。
说明2：这是因为salary_age_desc_idx索引的顺序就是降序排列的，所以使用该索引做降序排列的时候，就不需要在做反向扫描 。
说明3：在实际的业务中，我们可以根据自己的查询需要，创建升序或者降序的索引.
。
为了测试数据的准确性，这是我先把除了主键以外的索引都删除了，然后根据需要在重新创建 。
mysql
>
show
index
from
emp;
+
--
-----+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
|
Table
|
Non_unique
|
Key_name
|
Seq_in_index
|
Column_name
|
Collation
|
Cardinality
|
Sub_part
|
Packed
|
Null
|
Index_type
|
Comment
|
Index_comment
|
Visible
|
Expression
|
+
--
-----+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
|
emp
|
0
|
PRIMARY
|
1
|
id
|
A
|
7
|
NULL
|
NULL
|
|
BTREE
|
|
|
YES
|
NULL
|
+
--
-----+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2
rows
in
set
(
0.01
sec)
案例1：根据job做聚合查询 。
mysql
>
select
job,
count
(
*
)
from
emp
group
by
job;;
+
--
------------+----------+
|
job
|
count
(
*
)
|
+
--
------------+----------+
|
董事长
|
1
|
|
项目经理
|
1
|
|
开发
|
3
|
|
财务
|
1
|
|
出纳
|
1
|
|
人事
|
1
|
+
--
------------+----------+
6
rows
in
set
(
0.00
sec)
我们使用explain查看一下执行计划 。
mysql
>
explain
select
job,
count
(
*
)
from
emp
group
by
job;
+
--
--+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
+
--
--+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|
1
|
SIMPLE
|
emp
|
NULL
|
ALL
|
NULL
|
NULL
|
NULL
|
NULL
|
7
|
100.00
|
Using
temporary
|
+
--
--+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1
row
in
set
,
1
warning (
0.00
sec)
说明1：通过Extra字段：Using temporary,说明在这次的查询中创建了一张临时表，这是无论是空间上还是速度上都会影响到查询效率的.
这时我们给 job 创建一个索引，再次使用explain查看一下执行计划 。
mysql
>
create
index
job_idx
on
emp(job);
Query OK,
0
rows affected (
0.02
sec)
Records:
0
Duplicates:
0
Warnings:
0
mysql
>
show
index
from
emp;
+
--
-----+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
|
Table
|
Non_unique
|
Key_name
|
Seq_in_index
|
Column_name
|
Collation
|
Cardinality
|
Sub_part
|
Packed
|
Null
|
Index_type
|
Comment
|
Index_comment
|
Visible
|
Expression
|
+
--
-----+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
|
emp
|
0
|
PRIMARY
|
1
|
id
|
A
|
7
|
NULL
|
NULL
|
|
BTREE
|
|
|
YES
|
NULL
|
|
emp
|
1
|
job_idx
|
1
|
job
|
A
|
6
|
NULL
|
NULL
|
YES
|
BTREE
|
|
|
YES
|
NULL
|
+
--
-----+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
mysql
>
explain
select
job,
count
(
*
)
from
emp
group
by
job;
+
--
--+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
+
--
--+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|
1
|
SIMPLE
|
emp
|
NULL
|
index
|
job_idx
|
job_idx
|
83
|
NULL
|
7
|
100.00
|
Using
index
|
+
--
--+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1
row
in
set
,
1
warning (
0.00
sec)
说明2：这是查询中就使用到了索引查询，而没有建立临时表 。
这时我们在对 job 和 age 同时做分组查询 。
mysql
>
explain
select
job,age,
count
(
*
)
from
emp
group
by
job,age;
+
--
--+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
+
--
--+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|
1
|
SIMPLE
|
emp
|
NULL
|
ALL
|
NULL
|
NULL
|
NULL
|
NULL
|
7
|
100.00
|
Using
temporary
|
+
--
--+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1
row
in
set
,
1
warning (
0.00
sec)
说明3：这时Extra字段的值，仍然是Using temporary，那是因为没有一个与之对应的联合索引.
我们继续创建一个 job 和 age 的联合索引，然后再看一下 explain 的执行计划 。
mysql
>
create
index
job_age_idx
on
emp(job,age);
Query OK,
0
rows affected (
0.05
sec)
Records:
0
Duplicates:
0
Warnings:
0
mysql
>
show
index
from
emp;
+
--
-----+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
|
Table
|
Non_unique
|
Key_name
|
Seq_in_index
|
Column_name
|
Collation
|
Cardinality
|
Sub_part
|
Packed
|
Null
|
Index_type
|
Comment
|
Index_comment
|
Visible
|
Expression
|
+
--
-----+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
|
emp
|
0
|
PRIMARY
|
1
|
id
|
A
|
7
|
NULL
|
NULL
|
|
BTREE
|
|
|
YES
|
NULL
|
|
emp
|
1
|
job_idx
|
1
|
job
|
A
|
6
|
NULL
|
NULL
|
YES
|
BTREE
|
|
|
YES
|
NULL
|
|
emp
|
1
|
job_age_idx
|
1
|
job
|
A
|
6
|
NULL
|
NULL
|
YES
|
BTREE
|
|
|
YES
|
NULL
|
|
emp
|
1
|
job_age_idx
|
2
|
age
|
A
|
6
|
NULL
|
NULL
|
YES
|
BTREE
|
|
|
YES
|
NULL
|
+
--
-----+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5
rows
in
set
(
0.00
sec)
mysql
>
select
job,age,
count
(
*
)
from
emp
group
by
job,age;
+
--
------------+------+----------+
|
job
|
age
|
count
(
*
)
|
+
--
------------+------+----------+
|
人事
|
27
|
1
|
|
出纳
|
25
|
1
|
|
开发
|
22
|
2
|
|
开发
|
24
|
1
|
|
董事长
|
43
|
1
|
|
财务
|
25
|
1
|
|
项目经理
|
38
|
1
|
+
--
------------+------+----------+
7
rows
in
set
(
0.00
sec)
mysql
>
explain
select
job,age,
count
(
*
)
from
emp
group
by
job,age;
+
--
--+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
+
--
--+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|
1
|
SIMPLE
|
emp
|
NULL
|
index
|
job_age_idx
|
job_age_idx
|
88
|
NULL
|
7
|
100.00
|
Using
index
|
+
--
--+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1
row
in
set
,
1
warning (
0.00
sec)
说明4：当我们group by后面的分组字段，存在于某一个联合索引中的时候，group by会使用索引查询，而不会建立临时表 。
案例2：我们根据job做过滤然后再根据age排序 。
mysql
>
select
job,age
from
emp
where
job
=
"开发"
group
by
age;
+
--
------+------+
|
job
|
age
|
+
--
------+------+
|
开发
|
22
|
|
开发
|
24
|
+
--
------+------+
2
rows
in
set
(
0.01
sec)
mysql
>
explain
select
job,age
from
emp
where
job
=
"开发"
group
by
age;
+
--
--+-------------+-------+------------+------+---------------------+-------------+---------+-------+------+----------+-------------+
|
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
+
--
--+-------------+-------+------------+------+---------------------+-------------+---------+-------+------+----------+-------------+
|
1
|
SIMPLE
|
emp
|
NULL
|
ref
|
job_idx,job_age_idx
|
job_age_idx
|
83
|
const
|
3
|
100.00
|
Using
index
|
+
--
--+-------------+-------+------------+------+---------------------+-------------+---------+-------+------+----------+-------------+
1
row
in
set
,
1
warning (
0.00
sec)
说明1：当where后面的条件和group by 后面的条件一起组合成连锁索引，也不会建立临时表，也会直接走连个查询索引的。效率同样比较高 。
总结:
。
account_transaction表数据量展示 。
mysql
>
select
count
(
*
)
from
account_transaction;
+
--
--------+
|
count
(
*
)
|
+
--
--------+
|
2261942
|
+
--
--------+
1
row
in
set
(
8.40
sec)
说明1：account_transaction总数据量有226万+ 。
。
案例1：分别采用分页查询，第一页，第1万页，200万页的数据 。
mysql
>
select
*
from
account_transaction limit
1
,
2
;
+
--
--+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
|
id
|
trade_no
|
type
|
method
|
time
|
payment
|
out_trade_no
|
amount
|
balance
|
trader_staff_id
|
operator_staff_id
|
device_id
|
remark
|
+
--
--+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
|
2
|
156384294742000250
|
TOP_UP
|
CASH
|
2019
-
07
-
23
00
:
49
:
07.072256
|
LOCAL_ACCOUNT
|
|
10000
|
10000
|
250
|
12
|
6
|
|
|
3
|
156384301875000251
|
TOP_UP
|
CASH
|
2019
-
07
-
23
00
:
50
:
18.059192
|
LOCAL_ACCOUNT
|
|
10000
|
10000
|
251
|
12
|
6
|
|
+
--
--+--------------------+--------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
2
rows
in
set
(
0.00
sec)
mysql
>
select
*
from
account_transaction limit
10000
,
2
;
+
--
-----+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
|
id
|
trade_no
|
type
|
method
|
time
|
payment
|
out_trade_no
|
amount
|
balance
|
trader_staff_id
|
operator_staff_id
|
device_id
|
remark
|
+
--
-----+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
|
10054
|
156506391300003827
|
CONSUME_LUNCH
|
|
2019
-
08
-
06
03
:
58
:
33.000000
|
LOCAL_ACCOUNT
|
|
200
|
9800
|
3827
|
0
|
27
|
|
|
10055
|
156506391300002816
|
CONSUME_LUNCH
|
|
2019
-
08
-
06
03
:
58
:
33.000000
|
LOCAL_ACCOUNT
|
|
200
|
9800
|
2816
|
0
|
19
|
|
+
--
-----+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
2
rows
in
set
(
0.02
sec)
mysql
>
select
*
from
account_transaction limit
2000000
,
2
;
+
--
-------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
|
id
|
trade_no
|
type
|
method
|
time
|
payment
|
out_trade_no
|
amount
|
balance
|
trader_staff_id
|
operator_staff_id
|
device_id
|
remark
|
+
--
-------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
|
5524352
|
163539315991003043
|
CONSUME_LUNCH
|
|
2021
-
10
-
28
03
:
52
:
39.000000
|
LOCAL_ACCOUNT
|
|
200
|
3800
|
3043
|
0
|
34
|
|
|
5524354
|
163539342290003077
|
CONSUME_LUNCH
|
|
2021
-
10
-
28
03
:
57
:
02.000000
|
LOCAL_ACCOUNT
|
|
200
|
1500
|
3077
|
0
|
19
|
|
+
--
-------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
2
rows
in
set
(
2.51
sec)
说明1：我们对1页，1万页，200万页的数据分别查询，发现随着查询数据量的增加，查询的时间也在增加 。
说明2：当我们查询limit 2000000,2时，此时需要Mysql排序钱2000002条记录，但是仅仅需要返回200001-20002的记录，前2000000条记录丢弃，查询排序的代价非常大 。
。
查询优化 。
mysql
>
select
a.
*
from
account_transaction
as
a, (
select
id
from
account_transaction
order
by
id limit
2000000
,
2
)
as
at
where
a.id
=
at.id;
+
--
-------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
|
id
|
trade_no
|
type
|
method
|
time
|
payment
|
out_trade_no
|
amount
|
balance
|
trader_staff_id
|
operator_staff_id
|
device_id
|
remark
|
+
--
-------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
|
5524352
|
163539315991003043
|
CONSUME_LUNCH
|
|
2021
-
10
-
28
03
:
52
:
39.000000
|
LOCAL_ACCOUNT
|
|
200
|
3800
|
3043
|
0
|
34
|
|
|
5524354
|
163539342290003077
|
CONSUME_LUNCH
|
|
2021
-
10
-
28
03
:
57
:
02.000000
|
LOCAL_ACCOUNT
|
|
200
|
1500
|
3077
|
0
|
19
|
|
+
--
-------+--------------------+---------------+--------+----------------------------+---------------+--------------+--------+---------+-----------------+-------------------+-----------+--------+
2
rows
in
set
(0
.51
sec)
说明3：同样是分页查询2000000页以后的数据，该查询仅好事0.51秒，比直接使用limit分页查询快了几倍 。
说明4：Mysql官方针对大数据量的分页查询给出的方案是，建议使用覆盖查询加子查询形式进行优化 。
说明5：该插叙的子查询：select id from account_transaction order by id limit 2000000,2，首先这是根据id查询到需要数据的id，本身根据id查找就是比较快的.
mysql
>
select
id
from
account_transaction
order
by
id limit
2000000
,
2
;
+
--
-------+
|
id
|
+
--
-------+
|
5524352
|
|
5524354
|
+
--
-------+
2
rows
in
set
(
0.45
sec)
说明6：将该子查询的结果当做一张表，与account_trasaction做子查询，这样效率就会比直接使用limit速度快很多.
。
是一个聚合函数，对于返回的结果集，一行一行的判断，如果count函数的参数不为NULL,累计值就+1，否则不加1，最后返回累计值 。
count(*):
InnoDB引擎并不会把全部的字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加,mysql 对count(*)做了优化.
count(主键) 。
InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层，服务层那个主键后，直接按行进行累加（主键不可能为空） 。
count(普通字段):
没有not null 约束：InnoDB引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null,计数+1. 。
有not null 约束：InnofDB引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，直接按行累加 。
count(1） 。
InnoDB引擎遍历整张表，但不取值，服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加.
count(*) ≈count(1)>count(id)>count(普通字段) 。
。
说明1：因为左边和右边都是根据id修改的不同数据，这时id是主键索引，所以这里的修改都只会触发行锁，不会影响其他行的修改.
说明1：update的时候，如果条件是索引字段，则只会触发行索引 。
说明2：updae的时候，如果条件是非索引字段，则会触发表索引，即在update的时候，整张表处于锁住的状态.
说明3：主需要对update的字段创建一个索引值，就可以在update的时候将表锁降低为行锁.
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁.
。
最后此篇关于Mysql高级5-SQL优化的文章就讲到这里了,如果你想了解更多关于Mysql高级5-SQL优化的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。
25
4
0
目前我正在构建相当大的网络系统，我需要强大的 SQL 数据库解决方案。我选择 Mysql 而不是 Postgres，因为一些任务需要只读(MyISAM 引擎)而其他任务需要大量写入(InnoDB)。
我在 mysql 中使用如下命令。当它显示表格数据时，它被格式化为一个非常干净的表格，间距均匀且 |作为列分隔符。 SELECT * FROM TABLE_NAME; 当我从 CLI 运行命令时，如下
我知道这个问题之前已经被问过好几次了，我已经解决了很多问题，但到目前为止没有任何效果。 MySQL 试图将自身安装到的目录 (usr/local/mysql) 肯定有问题。关于我的错误的奇怪之处在于我
以下是我的 SQL 数据结构，我正在尝试如下两个查询: Select Wrk_ID, Wrk_LastName, Skill_Desc from Worker, Skill where
我们有一个本地 mysql 服务器(不在公共(public)域上)，并希望将该服务器复制到我们拥有的 google 云 sql 实例。我的问题是:1.这可能吗？2.我们的本地服务器只能在本地网络上访问
我有一个表(test_table)，其中一些字段值(例如字段 A、B 和 C)是从外部应用程序插入的，还有一个字段(字段 D)，我想从现有表(store_table)插入其值，但在插入前者(A、B 和
我想创建一个 AWS RDS 实例，然后使用 terraform 管理数据库用户。因此，首先，我创建了一个 RDS 实例，然后使用创建的 RDS 实例初始化 mysql 提供程序，以进一步将其用于用户
当用户在我的网站上注册时，他们会在我的一个数据库中创建自己的表格。该表存储用户发布的所有帖子。我还想做的是也为他们生成自己的 MySql 用户——该用户仅有权从他们的表中读取、写入和删除。 创建它应该
我有一个关于 ColdFusion 和 Mysql 的问题。我有两个表:PRODUCT 和 PRODUCT_CAT。我想列出包含一些标记为:IS_EXTRANET=1 的特殊产品的类别。所以我写了这个
我想获取 recipes_id 列的值，以获取包含 ingredient_id 的 2,17 和 26 条目的值。 假设 ingredient_id 2 丢失则不获取记录。 我已经尝试过 IN 运算符
在 Ubuntu 中，我通常安装两者，但 MySQL 的客户端和服务器之间有什么区别。 作为奖励，当一个新语句提到它需要 MySQL 5.x 时，它是指客户端、服务器还是两者兼而有之。例如这个链接ht
我重新访问了我的数据库并注意到我有一些 INT 类型的主键。 这还不够独特，所以我想我会有一个指导。 我来自微软 sql 背景，在 ssms 中你可以 选择类型为“uniqeidentifier”并自
我的系统上有 MySQL，我正在尝试确定它是 Oracle MySQL 还是 MySQL。 Oracle MySQL 有区别吗: http://www.oracle.com/us/products/m
我是在生产 MySQL 中运行的应用程序的新维护者。之前的维护者已经离开，留下的文档很少，而且联系不上了。 我面临的问题是执行以下请求大约需要 10 秒: SELECT COUNT(*) FROM `
我有两个位于不同机器上的 MySQL 数据库。我想自动将数据从一台服务器传输到另一台服务器。比方说，我希望每天早上 4:00 进行数据传输。 可以吗？是否有任何 MySQL 内置功能可以让我们做到这一
有什么方法可以使用 jdbc 查询位于 mysql 根目录之外的目录中的 mysql 表，还是必须将它们移动到 mysql 根目录内的数据库文件夹中？我在 Google 上搜索时没有找到任何东西。 最
我在 mysql 数据库中有两个表。成员和 ClassNumbers。两个表都有一个付费年份字段，都有一个代码字段。我想用代码数字表中的值更新成员表中的付费年份，其中成员中的代码与 ClassNumb
情况:我有 2 台服务器，其中一台当前托管一个实时 WordPress 站点，我希望能够将该站点转移到另一台服务器，以防第一台服务器出现故障。传输源文件很容易；传输数据库是我需要弄清楚如何做的。两台服
Phpmyadmin 有一个功能是“复制数据库到”..有没有mysql查询来写这个函数？类似于将 db A 复制到新的 db B。 最佳答案 首先创建复制数据库: CREATE DATABASE du
我有一个使用 mySQL 作为后端的库存软件。我已经在我的计算机上对其进行了测试，并且运行良好。 当我在计算机上安装我的软件时，我必须执行以下步骤: 安装 mySQL 服务器 将用户名指定为“root
我是一名优秀的程序员,十分优秀！