1. MySQL优化手段

MySQL数据库的优化手段通常包括但不限于：

  • SQL查询优化：这是最低成本的优化手段，通过优化查询语句、适当添加索引等方式进行。并且效果显著。
  • 库表结构优化：通过规范化设计、优化索引和数据类型等方式进行库表结构优化，需要对数据库结构进行调整和改进
  • 系统配置优化：根据硬件和操作系统的特点，调整最大连接数、内存管理、IO调度等参数
  • 硬件优化：升级硬盘、增加内存容量、升级处理器等硬件方面的投入，需要购买和替换硬件设备，成本较高

我们主要掌握：SQL查询优化

2. SQL性能分析工具

2.1 查看数据库整体情况

通过以下命令可以查看当前数据库在SQL语句执行方面的整体情况：

1
2
3
4
5
6
show global status like 'Com_select';
show global status like 'Com_insert';
show global status like 'Com_delete';
show global status like 'Com_update';

show global status like 'Com_______';

这些结果反映了从 MySQL 服务器启动到当前时刻，所有的 SELECT 查询总数。对于 MySQL 性能优化来说，通过查看 Com_select 的值可以了解 SELECT 查询在整个 MySQL 服务期间所占比例的情况：

  • 如果 Com_select 次数过高，可能说明查询表中的每条记录都会返回过多的字段。
  • 如果 Com_select 次数很少，同时insert或delete或update的次数很高，可能说明服务器运行的应用程序过于依赖写入操作和少量读取操作。

总之，通过查看 Com_select 的值，可以了解 MySQL 服务器的长期执行情况，并在优化查询性能时，帮助我们了解 MySQL 的性能瓶颈。

2.2 慢查询日志

慢查询日志文件可以将查询较慢的DQL语句记录下来，便于我们定位需要调优的select语句。

通过以下命令查看慢查询日志功能是否开启：

1
show variables like 'slow_query_log';
1
2
3
4
5
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+

慢查询日志功能默认是关闭的。请修改my.ini文件来开启慢查询日志功能，在my.ini的[mysqld]后面添加如下配置：

1
2
slow-query-log=1
long_query_time=3

PS：slow_query_log=1表示开启慢查询日志功能，long_query_time=3表示：只要SELECT语句的执行耗时超过3秒则将其记录到慢查询日志中。

重启mysql服务。再次查看是否开启慢查询日志功能：

1
2
3
4
5
# 停止 mysql 服务
net stop mysql

# 启动 mysql 服务
net start mysql
1
2
3
4
5
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+

尝试执行一条时长超过3秒的select语句：

1
select empno,ename,sleep(4) from emp where ename='smith';

慢查询日志文件默认存储在：你mysql安装目录下的 Data 目录中，默认的名字是：计算机名-slow.log

通过该文件可以清晰的看到哪些DQL语句属于慢查询：

查看哪些DQL语句属于慢查询

2.3 show profiles

通过show profiles可以查看一个SQL语句在执行过程中具体的耗时情况。帮助我们更好的定位问题所在。

查看当前数据库是否支持 profile 操作：

1
select @@have_profiling;
1
2
3
4
5
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+

查看 profiling 开关是否打开：

1
select @@profiling;
1
2
3
4
5
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+

将 profiling 开关打开：

1
set profiling = 1;
1
2
3
4
5
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+

可以执行多条DQL语句，然后使用 show profiles; 来查看当前数据库中执行过的每个SELECT语句的耗时情况。

1
2
3
4
select empno,ename from emp;
select empno,ename from emp where empno=7369;
select count(*) from emp;
show profiles;
每个SELECT语句的耗时情况

查看某个SQL语句语句在执行过程中，每个阶段的耗时情况：

1
show profile for query 4;
某个SQL语句语句在执行过程中每个阶段的耗时情况

想查看执行过程中cpu的情况，可以执行以下命令：

1
show profile cpu for query 4;
执行过程中cpu的情况

2.4 explain

explain命令可以查看一个DQL语句的执行计划，根据执行计划可以做出相应的优化措施。提高执行效率。

1
explain select * from emp where empno=7369;

DQL语句的执行计划

id

id反映出一条select语句执行顺序，id越大优先级越高。id相同则按照自上而下的顺序执行。

1
explain select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal;

explain-id(1)

由于id相同，反映出三张表在执行顺序上属于平等关系，执行时采用，先d，再e，最后s。

1
explain select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno where e.sal=(select sal from emp where ename='ford');

explain-id(2)

反映出，先执行子查询，然后让e和d做表连接。

select_type

反映了mysql查询语句的类型。常用值包括：

  • SIMPLE：表示查询中不包含子查询或UNION操作。这种查询通常包括一个表或是最多一个联接（JOIN）
  • PRIMARY：表示当前查询是一个主查询。（主要的查询）
  • UNION：表示查询中包含UNION操作
  • SUBQUERY：子查询
  • DERIVED：派生表（表示查询语句出现在from后面）

table

反映了这个查询操作的是哪个表。

type

反映了查询表中数据时的访问类型，常见的值：

  • NULL：效率最高，一般不可能优化到这个级别，只有查询时没有查询表的时候，访问类型是NULL。例如：select 1;
  • system：通常访问系统表的时候，访问类型是system。一般也很难优化到这个程序。
  • const：根据主键或者唯一性索引查询，索引值是常量值时。explain select * from emp where empno=7369;
  • eq_ref：根据主键或者唯一性索引查询。索引值不是常量值。
  • ref：使用了非唯一的索引进行查询。
  • range：使用了索引，扫描了索引树的一部分。
  • index：表示用了索引，但是也需要遍历整个索引树。
  • all：全表扫描

效率最高的是NULL，效率最低的是all，从上到下，从高到低。

possible_keys

这个查询可能会用到的索引

key

实际用到的索引

key_len

反映索引中在查询中使用的列所占的总字节数。

rows

查询扫描的预估计行数。

Extra

给出了与查询相关的额外信息和说明。这些额外信息可以帮助我们更好地理解查询执行的过程。

3. 索引优化

3.1 加索引 vs 不加索引

将这个sql脚本初始化到数据库中（初始化100W条记录）：t_vip.sql

根据id查询（id是主键，有索引）：

1
select * from t_vip where id = 900000;

根据name查询（name上没有索引）：

1
select * from t_vip where name='4c6494cb';

给name字段添加索引：

1
create index idx_t_user_name on t_vip(name);

再次根据name查询（此时name上已经有索引了） ：

1
select * from t_vip where name='4c6494cb';

3.2 最左前缀原则

假设有这样一张表：

1
2
3
4
5
6
7
create table t_customer(
	id int primary key auto_increment,
	name varchar(255),
	age int,
	gender char(1),
	email varchar(255)
);

添加了这些数据：

1
2
3
4
5
insert into t_customer values(null, 'zhangsan', 20, 'M', 'zhangsan@123.com');
insert into t_customer values(null, 'lisi', 22, 'M', 'lisi@123.com');
insert into t_customer values(null, 'wangwu', 18, 'F', 'wangwu@123.com');
insert into t_customer values(null, 'zhaoliu', 22, 'F', 'zhaoliu@123.com');
insert into t_customer values(null, 'jack', 30, 'M', 'jack@123.com');

添加了这样的复合索引：

1
create index idx_name_age_gender on t_customer(name,age,gender);

最左前缀原则：当查询语句条件中包含了这个复合索引最左边的列 name 时，此时索引才会起作用

验证1：

1
explain select * from t_customer where name='zhangsan' and age=20 and gender='M';

验证结果：完全使用了索引

最左前缀原则-验证1

验证2：

1
explain select * from t_customer where name='zhangsan' and age=20;

验证结果：使用了部分索引

最左前缀原则-验证2

验证3：

1
explain select * from t_customer where name='zhangsan';

验证结果：使用了部分索引

最左前缀原则-验证3

验证4：

1
explain select * from t_customer where age=20 and gender='M' and name='zhangsan';

验证结果：完全使用了索引

最左前缀原则-验证4

验证5：

1
explain select * from t_customer where gender='M' and age=20;

验证结果：没有使用任何索引

最左前缀原则-验证5

验证6：

1
explain select * from t_customer where name='zhangsan' and gender='M';

验证结果：使用了部分索引

最左前缀原则-验证6

验证7：

1
explain select * from t_customer where name='zhangsan' and gender='M' and age=20;

验证结果：完全使用了索引

最左前缀原则-验证7

范围查询时，在“范围条件”右侧的列索引会失效

验证：

1
explain select * from t_customer where name='zhangsan' and age>20 and gender='M';

验证结果：name和age列索引生效。gender列索引无效。

范围查询时在范围条件右侧的列索引会失效

怎么解决？建议范围查找时带上=

1
explain select * from t_customer where name='zhangsan' and age>=20 and gender='M';

解决范围查询时在范围条件右侧的列索引失效

3.3 索引失效情况

有这样一张表：

1
2
3
4
5
6
create table t_emp(
  id int primary key auto_increment,
  name varchar(255),
  sal int,
  age char(2)
);

有这样一些数据：

1
2
3
insert into t_emp values(null, '张三', 5000,'20');
insert into t_emp values(null, '张飞', 4000,'30');
insert into t_emp values(null, '李飞', 6000,'40');

有这样一些索引：

1
2
3
create index idx_t_emp_name on t_emp(name);
create index idx_t_emp_sal on t_emp(sal);
create index idx_t_emp_age on t_emp(age);

3.3.1 索引列参加了运算，索引失效

1
explain select * from t_emp where sal > 5000;

验证结果：使用了索引

索引列未参加运算，索引生效

1
explain select * from t_emp where sal*10 > 50000;

验证结果：索引失效

索引列参加了运算，索引失效

3.3.2 索引列进行模糊查询时以 % 开始的，索引失效

1
explain select * from t_emp where name like '张%';

验证结果：索引有效

索引列进行模糊查询时未以%开始，索引生效

1
explain select * from t_emp where name like '%飞';

验证结果：索引失效

索引列进行模糊查询时以%开始的，索引失效

3.3.3 索引列是字符串类型，但查询时省略了单引号，索引失效

1
explain select * from t_emp where age='20';

验证结果：索引有效

索引列是字符串类型查询时使用了单引号，索引生效

1
explain select * from t_emp where age=20;

验证结果：索引失效

索引列是字符串类型但查询时省略了单引号，索引失效

3.3.4 查询条件中有or，只要有未添加索引的字段，索引失效

1
explain select * from t_emp where name='张三' or sal=5000;

验证结果：使用了索引

查询条件中有or，没有未添加索引的字段，索引生效

将t_emp表sal字段上的索引删除：

1
alter table t_emp drop index idx_t_emp_sal;

再次验证：

1
explain select * from t_emp where name='张三' or sal=5000;

验证结果：索引失效

查询条件中有or，有未添加索引的字段，索引失效

3.3.5 当查询的符合条件的记录在表中占比较大，索引失效

复制一张新表：emp2

1
create table emp2 as select * from emp;

给sal添加索引：

1
alter table emp2 add index idx_emp2_sal(sal);

验证1：

1
explain select * from emp2 where sal > 800;

不走索引：

查询的符合条件的记录在表中占比较大，索引失效验证1

验证2：

1
explain select * from emp2 where sal > 1000;

不走索引：

查询的符合条件的记录在表中占比较大，索引失效验证2

验证3：

1
explain select * from emp2 where sal > 2000;

走索引：

查询的符合条件的记录在表中占比较大，索引失效验证3

3.5.6 关于is null和is not null的索引失效问题

给emp2的comm字段添加一个索引：

1
create index idx_emp2_comm on emp2(comm);

将emp2表的comm字段值全部更新为NULL：

1
update emp2 set comm=null;

验证此时条件使用is null是否走索引：

1
explain select * from emp2 where comm is null;

验证结果：不走索引。

使用is null不走索引

验证此时条件使用is not null是否走索引：

验证此时条件使用is not null是否走索引1

将emp2表的comm字段全部更新为非NULL：

1
update emp2 set comm=100;

验证此时条件使用is null是否走索引：

1
explain select * from emp2 where comm is null;

验证结果：走索引

走索引

验证此时条件使用is not null是否走索引：

1
explain select * from emp2 where comm is not null;

验证结果：不走索引

不走索引

结论：走索引还是不走索引，根数据分布有很大关系，如果符合条件的记录占比较大，会考虑使用全表扫描，而放弃走索引。

3.4 指定索引

当一个字段上既有单列索引，又有复合索引时，我们可以通过以下的SQL提示来要求该SQL语句执行时采用哪个索引：

  • use index(索引名称)：建议使用该索引，只是建议，底层mysql会根据实际效率来考虑是否使用你推荐的索引
  • ignore index(索引名称)：忽略该索引
  • force index(索引名称)：强行使用该索引

查看 t_customer 表上的索引：

1
show index from t_customer;

t_customer表上的索引

可以看到name、age、gender三列添加了一个复合索引。

现在给name字段添加一个单列索引：

1
create index idx_name on t_customer(name);

看看以下的语句默认使用了哪个索引：

1
explain select * from t_customer where name='zhangsan';

默认使用了联合索引

通过测试得知，默认使用了联合索引。

如何建议使用单列索引 idx_name：

1
explain select * from t_customer use index(idx_name) where name='zhangsan';

建议使用单列索引idx_name

如何忽略使用符合索引 idx_name_age_gender：

1
explain select * from t_customer ignore index(idx_name_age_gender) where name='zhangsan';

忽略使用符合索引idx_name_age_gender

如何强行使用单列索引 idx_name：

1
explain select * from t_customer force index(idx_name) where name='zhangsan';

强行使用单列索引idx_name

3.5 覆盖索引

覆盖索引我们在讲解索引的时候已经提到过了，覆盖索引强调的是：在select后面写字段的时候，这些字段尽可能是索引所覆盖的字段，这样可以避免回表查询。尽可能避免使用 select *，因为 select * 很容易导致回表查询。（本质就是：能在索引上检索的，就不要再次回表查询了。）

例如：有一张表 emp3，其中 ename，job添加了联合索引：idx_emp3_ename_job，以下这个select语句就不会回表：

1
2
3
4
5
drop table if exists emp3;
create table emp3 as select * from emp;
alter table emp3 add constraint emp3_pk primary key(empno);
create index idx_emp3_ename_job on emp3(ename,job);
explain select empno,ename,job from emp3 where ename='KING';

select语句不回表

如果查询语句要查找的列没有在索引中，则会回表查询，例如：

1
explain select empno,ename,job,sal from emp3 where ename='KING';

select语句回表

面试题：t_user表字段如下：id,name,password,realname,birth,email。表中数据量500万条，请针对以下SQL语句给出优化方案：

1
select id,name,realname from t_user where name='鲁智深';

如果只给name添加索引，底层会进行大量的回表查询，效率较低，建议给name和realname两个字段添加联合索引，这样大大减少回表操作，提高查询效率。

3.6 前缀索引

如果一个字段类型是varchar或text字段，字段中存储的是文本或者大文本，直接对这种长文本创建索引，会让索引体积很大，怎么优化呢？可以将字符串的前几个字符截取下来当做索引来创建。这种索引被称为前缀索引，例如：

1
2
3
drop table if exists emp4;
create table emp4 as select * from emp;
create index idx_emp4_ename_2 on emp4(ename(2));

以上SQL表示将emp4表中ename字段的前2个字符创建到索引当中。

使用前缀索引时，需要通过以下公式来确定使用前几个字符作为索引：

1
select count(distinct substring(ename,1,前几个字符)) / count(*) from emp4;

以上查询结果越接近1，表示索引的效果越好。（原理：做索引值的话，索引值越具有唯一性效率越高）

假设我们使用前1个字符作为索引值：

1
select count(distinct substring(ename,1,1)) / count(*) from emp4;

使用前1个字符作为索引值

假设我们使用前2个字符作为索引值：

1
select count(distinct substring(ename,1,2)) / count(*) from emp4;

使用前2个字符作为索引值

可见使用前2个字符作为索引值，能够让索引值更具有唯一性，效率越好，因此我们选择前2个字符作为前缀索引。

1
create index idx_emp4_ename_2 on emp4(ename(2));

执行以下的查询语句则会走这个前缀索引：

1
explain select * from emp4 where ename='KING';

查询语句走前缀索引

3.7 单列索引和复合索引怎么选择

当查询语句的条件中有多个条件，建议将这几个列创建为复合索引，因为创建单列索引很容易回表查询。

例如分别给emp5表ename，job添加两个单列索引：

1
2
3
4
5
create table emp5 as select * from emp;
alter table emp5 add constraint emp5_pk primary key(empno);

create index idx_emp5_ename on emp5(ename);
create index idx_emp5_job on emp5(job);

执行以下查询语句：

1
explain select empno,ename,job from emp5 where ename='SMITH' and job='CLERK';

使用了单列索引

ename和job都出现在查询条件中，可以给emp6表的ename和job创建一个复合索引：

1
2
3
4
5
create table emp6 as select * from emp;
alter table emp6 add constraint emp6_pk primary key(empno);

create index idx_emp6_ename_job on emp6(ename,job);
explain select empno,ename,job from emp6 where ename='SMITH' and job='CLERK';

使用了复合索引

对于以上查询语句，使用复合索引避免了回表，因此这种情况下还是建议使用复合索引。

PS：创建索引时应考虑最左前缀原则，主字段并且具有很强唯一性的字段建议排在第一位。

例如：

1
create index idx_emp_ename_job on emp(ename,job);

和以下方式对比：

1
create index idx_emp_job_ename on emp(job,ename);

由于ename是主字段，并且ename具有很好的唯一性，建议将ename列放在最左边。因此这两种创建复合索引的方式，建议采用第一种。

复合索引底层原理：

复合索引底层原理

3.8 索引创建原则

  • 表数据量庞大，通常超过百万条数据。
  • 经常出现在where，order by，group by后面的字段建议添加索引。
  • 创建索引的字段尽量具有很强的唯一性。
  • 如果字段存储文本，内容较大，一定要创建前缀索引。
  • 尽量使用复合索引，使用单列索引容易回表查询。
  • 如果一个字段中的数据不会为NULL，建议建表时添加not null约束，这样优化器就知道使用哪个索引列更加有效。
  • 不要创建太多索引，当对数据进行增删改的时候，索引需要重新重新排序。
  • 如果很少的查询，经常的增删改不建议加索引。

4. SQL优化

4.1 order by优化

准备数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
drop table if exists workers;

create table workers(
  id int primary key auto_increment,
  name varchar(255),
  age int,
  sal int
);

insert into workers values(null, '孙悟空', 500, 50000);
insert into workers values(null, '猪八戒', 300, 40000);
insert into workers values(null, '沙和尚', 600, 40000);
insert into workers values(null, '白骨精', 600, 10000);

explain查看一个带有order by的语句时，Extra列会显示：using index 或者 using filesort，区别是什么？

  • using index: 表示使用索引，因为索引是提前排好序的。效率很高。
  • using filesort：表示使用文件排序，这就表示没有走索引，对表中数据进行排序，排序时将硬盘的数据读取到内存当中，在内存当中排好序。这个效率是低的，应避免。

此时name没有添加索引，如果根据name进行排序的话：

1
explain select id,name from workers order by name;

name没有添加索引根据name进行排序

显然这种方式效率较低。

给name添加索引：

1
create index idx_workers_name on workers(name);

再根据name排序：

1
explain select id,name from workers order by name;

给name添加索引根据name进行排序的话

这样效率则提升了。

如果要通过age和sal两个字段进行排序，最好给age和sal两个字段添加复合索引，不添加复合索引时：

按照age升序排，如果age相同则按照sal升序

1
explain select id,age,sal from workers order by age,sal;

不添加复合索引时按照age升序sal升序

这样效率是低的。

给age和sal添加复合索引：

1
create index idx_workers_age_sal on workers(age, sal);

再按照age升序排，如果age相同则按照sal升序：

1
explain select id,age,sal from workers order by age,sal;

添加复合索引时按照age升序sal升序

这样效率提升了。

在B+树上叶子结点上的所有数据默认是按照升序排列的，如果按照age降序，如果age相同则按照sal降序，会走索引吗？

1
explain select id,age,sal from workers order by age desc,sal desc;

反向索引扫描使用索引

可以看到备注信息是：反向索引扫描，使用了索引。

这样效率也是很高的，因为B+树叶子结点之间采用的是双向指针。可以从左向右（升序），也可以从右向左（降序）。

如果一个升序，一个降序会怎样呢？

1
explain select id,age,sal from workers order by age asc, sal desc;

age升序sal降序

可见age使用了索引，但是sal没有使用索引。怎么办呢？可以针对这种排序情况创建对应的索引来解决：

1
create index idx_workers_ageasc_saldesc on workers(age asc, sal desc);

创建的索引如下：A表示升序，D表示降序。

创建idx_workers_ageasc_saldesc索引

再次执行：

1
explain select id,age,sal from workers order by age asc, sal desc;

使用了idx_workers_ageasc_saldesc索引

我们再来看看，对于排序来说是否支持最左前缀法则：

1
explain select id,age,sal from workers order by sal;

排序遵循最左前缀法则

通过测试得知，order by也遵循最左前缀法则。

我们再来看一下未使用覆盖索引会怎样？

1
explain select * from workers order by age,sal;

排序未使用覆盖索引

通过测试得知，排序也要尽量使用覆盖索引。

order by 优化原则总结：

  • 排序也要遵循最左前缀法则。
  • 使用覆盖索引。
  • 针对不同的排序规则，创建不同索引。（如果所有字段都是升序，或者所有字段都是降序，则不需要创建新的索引）
  • 如果无法避免filesort，要注意排序缓存的大小，默认缓存大小256KB，可以修改系统变量 sort_buffer_size ：
    1
    show variables like 'sort_buffer_size';
    系统变量sort_buffer_size

4.2 group by优化

创建empx表：

1
create table empx as select * from emp;

job字段上没有索引，根据job进行分组，查看每个工作岗位有多少人：

1
select job,count(*) from empx group by job;

job字段上没有索引根据job进行分组

看看是否走索引了：

1
explain select job,count(*) from empx group by job;

使用了临时表

使用了临时表，效率较低。

给job添加索引：

1
create index idx_empx_job on empx(job);

再次执行：

1
explain select job,count(*) from empx group by job;

使用idx_empx_job索引

效率提升了。

我们再来看看group by是否需要遵守最左前缀法则：给deptno和sal添加复合索引

1
create index idx_empx_deptno_sal on empx(deptno, sal);

根据部门编号分组，查看每个部门人数：

1
explain select deptno,count(*) from empx group by deptno;

使用了idx_empx_deptno_sal索引

效率很高，因为deptno是复合索引中最左边的字段。

根据sal分组，查看每个工资有多少人：

1
explain select sal, count(*) from empx group by sal;

根据sal分组

使用了临时表，效率较低。

通过测试得知，group by也同样遵循最左前缀法则。

我们再来测试一下，如果将部门编号deptno（复合索引的最左列）添加到where条件中，效率会不会提升：

1
explain select sal, count(*) from empx where deptno=10 group by sal;

将部门编号deptno（复合索引的最左列）添加到where条件中

效率有提升的，这说明了，group by确实也遵循最左前缀法则。（where中使用了最左列)

4.3 limit优化

数据量特别庞大时，取数据时，越往后效率越低，怎么提升？mysql官方给出的解决方案是：使用覆盖索引+子查询的形式来提升效率。

limit优化-查询效率(1)

怎么解决？使用覆盖索引，加子查询

使用覆盖索引：速度有所提升

limit优化-查询效率(2)

使用子查询形式取其他列的数据：

limit优化-查询效率(3)

通过测试，这种方式整体效率有所提升。

4.4 主键优化

主键设计原则：

  • 主键值不要太长，二级索引叶子结点上存储的是主键值，主键值太长，容易导致索引占用空间较大。
  • 尽量使用auto_increment生成主键。尽量不要使用uuid做主键，因为uuid不是顺序插入。
  • 最好不要使用业务主键，因为业务的变化会导致主键值的频繁修改，主键值不建议修改，因为主键值修改，聚集索引一定会重新排序。
  • 在插入数据时，主键值最好是顺序插入，不要乱序插入，因为乱序插入可能会导致B+树叶子结点频繁的进行页分裂与页合并操作，效率较低。
    1. 主键值对应聚集索引，插入主键值如果是乱序的，B+树叶子结点需要不断的重新排序，重排过程中还会频繁涉及到页分裂和页合并的操作，效率较低。
    2. B+树上的每个节点都存储在页（page）中。一个页面中存储一个节点。
    3. MySQL的InnoDB存储引擎一个页可以存储16KB的数据。
    4. 如果主键值不是顺序插入的话，会导致频繁的页分裂和页合并。在一个B+树中，页分裂和页合并是树的自动调整机制的一部分。当一个页已经满了，再插入一个新的关键字时就会触发页分裂操作，将页中的关键字分配到两个新的页中，同时调整树的结构。相反，当一个页中的关键字数量下降到一个阈值以下时，就会触发页合并操作，将两个相邻的页合并成一个新的页。如果主键值是随机的、不是顺序插入的，那么页的利用率会降低，页分裂和页合并的次数就会增加。由于页的分裂和合并是比较耗时的操作，频繁的分裂和合并会降低数据库系统的性能。因此，为了优化B+树的性能，可以将主键值设计成顺序插入的，这样可以减少页的分裂和合并的次数，提高B+树的性能。在实际应用中，如果对主键值的顺序性能要求不是特别高，也可以采用一些技术手段来减少页分裂和合并，例如B+树分裂时采用“延迟分裂”技术，或者通过调整页的大小和节点的大小等方式来优化B+树的性能。
延迟分裂技术

4.5 insert优化

insert优化原则：

  • 批量插入：数据量较大时，不要一条一条插入，可以批量插入，当然，建议一次插入数据不超过1000条。

    1
    insert into t_user(id,name,age) values (1,'jack',20),(2,'lucy',30),(3,'timi',22);

  • mysql默认是自动提交事务，只要执行一条DML语句就自动提交一次，因此，当插入大量数据时，建议手动开启事务和手动提交事务。不建议使用数据库事务自动提交机制。

  • 主键值建议采用顺序插入，顺序插入比乱序插入效率高。

  • 超大数据量插入可以考虑使用mysql提供的load指令，load指令可以将csv文件中的数据批量导入到数据库表当中，并且效率很高，过程如下：

    1、登录mysql时指定参数

    1
    mysql --local-infile -uroot -proot

    2、开启local_infile功能

    1
    set global local_infile = 1;

    3、执行load指令

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    use muyoukule;

    create table t_temp(
      id int primary key,
      name varchar(255),
      password varchar(255),
      birth char(10),
      email varchar(255)
    );

    load data local infile 'D:\\muyoukule.csv' into table t_temp fields terminated by ',' lines terminated by '\n';

    文件中的数据如下：

    muyoukule.csv文件中的数据

    导入表中之后，数据如下：

    muyoukule.csv文件中的数据导入表中

4.6 count(*)优化

分组函数count的使用方式：

  • count(主键)
    • 原理：将每个主键值取出，累加
  • count(常量值)
    • 原理：获取到每个常量值，累加
  • count(字段)
    • 原理：取出字段的每个值，判断是否为NULL，不为NULL则累加。
  • count(*)
    • 原理：不用取值，底层mysql做了优化，直接统计总行数，效率最高。

结论：如果你要统计一张表中数据的总行数，建议使用 count(*)

注意：

  • 对于InnoDB存储引擎来说，count计数的实现原理就是将表中每一条记录取出，然后累加。如果你想真正提高效率，可以自己使用额外的其他程序来实现，例如每向表中插入一条记录时，在redis数据库中维护一个总行数，这样获取总行数的时候，直接从redis中获取即可，这样效率是最高的。
  • 对于MyISAM存储引擎来说，当一个select语句没有where条件时，获取总行数效率是极高的，不需要统计，因为MyISAM存储引擎维护了一个单独的总行数。

4.7 update优化

当存储引擎是InnoDB时，表的行级锁是针对索引添加的锁，如果索引失效了，或者不是索引列时，会提升为表级锁。
什么是行级锁？A事务和B事务，开启A事务后，通过A事务修改表中某条记录，修改后，在A事务未提交的前提下，B事务去修改同一条记录时，无法继续，直到A事务提交，B事务才可以继续。

有一张表：t_fruit

1
2
3
4
5
6
7
8
create table t_fruit(
  id int primary key auto_increment,
  name varchar(255)
);

insert into t_fruit values(null, '苹果');
insert into t_fruit values(null, '香蕉');
insert into t_fruit values(null, '橘子');

开启A事务和B事务，演示行级锁：

事务A没有结束之前，事务B卡住：

事务A没有结束之前，事务B卡住

事务A结束之后，事务B继续执行：

事务A结束之后，事务B继续执行

当然，如果更新的不是同一行数据，事务A和事务B可以并发：

更新的不是同一行数据，事务A和事务B可以并发

行级锁是对索引列加锁，以上更新语句的where条件是id，id是主键，当然有索引，所以使用了行级锁，如果索引失效，或者字段上没有索引，则会升级为表级锁：

表级锁

因此，为了更新的效率，建议update语句中where条件中的字段是添加索引的。