MySQL优化

时间：2024-5-3    作者：老大夫    分类： Mysql

MySQL优化手段

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

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

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

SQL性能分析工具

查看数据库整体情况

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

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 的性能瓶颈。

慢查询日志

慢查询日志文件可以将查询较慢的DQL语句记录下来，便于我们定位需要调优的select语句。
通过以下命令查看慢查询日志功能是否开启：

show variables like 'slow_query_log';

image.png
慢查询日志功能默认是关闭的。请修改my.ini文件来开启慢查询日志功能，在my.ini的[mysqld]后面添加如下配置：
image.png
image.png
注意：slow_query_log=1表示开启慢查询日志功能，long_query_time=3表示：只要SELECT语句的执行耗时超过3秒则将其记录到慢查询日志中。
重启mysql服务。再次查看是否开启慢查询日志功能：
image.png
image.png

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

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

慢查询日志文件默认存储在：C:\dev\mysql-8.0.36-winx64\data 目录下，默认的名字是：计算机名-slow.log
通过该文件可以清晰的看到哪些DQL语句属于慢查询：
image.png

show profiles

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

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

select @@have_profiling;

image.png

查看 profiling 开关是否打开：

select @@profiling;

image.png

将 profiling 开关打开：

set profiling = 1;

image.png

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

select empno,ename from emp;
select empno,ename from emp where empno=7369;
select count(*) from emp;
show profiles;

image.png

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

show profile for query 4;

image.png

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

show profile cpu for query 4;

image.png

explain

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

explain select * from emp where empno=7369;

image.png

id

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

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;

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

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');

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

select_type

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

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

table

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

type

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

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

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

possible_keys

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

key

实际用到的索引

key_len

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

rows

查询扫描的预估计行数。

Extra

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

索引优化

加索引 vs 不加索引

将这个sql脚本初始化到数据库中（初始化100W条记录）：t_vip.sql
根据id查询（id是主键，有索引）：

select * from t_vip where id = 900000;

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

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

给name字段添加索引：

create index idx_t_user_name on t_vip(name);

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

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

最左前缀原则

假设有这样一张表：

create table t_customer(
    id int primary key auto_increment,
    name varchar(255),
    age int,
    gender char(1),
    email varchar(255)
);

添加了这些数据：

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');

添加了这样的复合索引：

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

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

验证1：

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

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

验证2：

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

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

验证3：

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

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

验证4：

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

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

验证5：

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

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

验证6：

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

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

验证7：

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

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

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

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

验证结果：name和age列索引生效。gender列索引无效。
image.png
怎么解决？建议范围查找时带上“=”

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

image.png

索引失效情况

有这样一张表：

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

有这样一些数据：

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

有这样一些索引：

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);

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

explain select * from t_emp where sal > 5000;

验证结果：使用了索引
image.png

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

验证结果：索引失效
image.png

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

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

验证结果：索引有效
image.png

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

验证结果：索引失效
image.png

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

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

验证结果：索引有效
image.png

explain select * from t_emp where age=20;

验证结果：索引失效
image.png

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

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

验证结果：使用了索引
image.png

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

alter table t_emp drop index idx_t_emp_sal;

再次验证：

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

验证结果：索引失效
image.png

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

复制一张新表：emp2

create table emp2 as select * from emp;

给sal添加索引：

alter table emp2 add index idx_emp2_sal(sal);

验证1：

explain select * from emp2 where sal > 800;

不走索引：
image.png

验证2：

explain select * from emp2 where sal > 1000;

不走索引：
image.png

验证3：

explain select * from emp2 where sal > 2000;

走索引：
image.png

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

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

create index idx_emp2_comm on emp2(comm);

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

update emp2 set comm=null;

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

explain select * from emp2 where comm is null;

验证结果：不走索引。
image.png
验证此时条件使用is not null是否走索引：
image.png

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

update emp2 set comm=100;

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

explain select * from emp2 where comm is null;

验证结果：走索引
image.png
验证此时条件使用is not null是否走索引：

explain select * from emp2 where comm is not null;

验证结果：不走索引
image.png

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

指定索引

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

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

查看 t_customer 表上的索引：

show index from t_customer;

image.png
可以看到name age gender三列添加了一个复合索引。
现在给name字段添加一个单列索引：

create index idx_name on t_customer(name);

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

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

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

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

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

image.png

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

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

image.png

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

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

image.png

覆盖索引

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

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

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';

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

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

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

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

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

前缀索引

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

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个字符创建到索引当中。

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

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

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

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

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

image.png

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

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

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

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

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

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

image.png

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

当查询语句的条件中有多个条件，建议将这几个列创建为复合索引，因为创建单列索引很容易回表查询。
例如分别给emp5表ename，job添加两个单列索引：

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);

执行以下查询语句：

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

image.png

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

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';

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

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

create index idx_emp_ename_job on emp(ename,job);

和以下方式对比：

create index idx_emp_job_ename on emp(job,ename);

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

复合索引底层原理：
无标题.png

索引创建原则

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

SQL优化

order by的优化

准备数据：

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进行排序的话：

explain select id,name from workers order by name;

image.png
显然这种方式效率较低。
给name添加索引：

create index idx_workers_name on workers(name);

再根据name排序：

explain select id,name from workers order by name;

image.png
这样效率则提升了。

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

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

image.png
这样效率是低的。
给age和sal添加复合索引：

create index idx_workers_age_sal on workers(age, sal);

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

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

image.png
这样效率提升了。

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

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

image.png
可以看到备注信息是：反向索引扫描，使用了索引。
这样效率也是很高的，因为B+树叶子结点之间采用的是双向指针。可以从左向右（升序），也可以从右向左（降序）。

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

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

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

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

创建的索引如下：A表示升序，D表示降序。
image.png
再次执行：

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

image.png

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

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

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

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

explain select * from workers order by age,sal;

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

order by 优化原则总结：

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

image.png

group by优化

创建empx表：

create table empx as select * from emp;

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

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

image.png
看看是否走索引了：

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

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

给job添加索引：

create index idx_empx_job on empx(job);

再次执行：

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

image.png
效率提升了。

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

create index idx_empx_deptno_sal on empx(deptno, sal);

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

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

image.png
效率很高，因为deptno是复合索引中最左边的字段。
根据sal分组，查看每个工资有多少人：

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

image.png
使用了临时表，效率较低。
通过测试得知，group by也同样遵循最左前缀法则。

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

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

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

limit优化

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

image.png

怎么解决？使用覆盖索引，加子查询
使用覆盖索引：速度有所提升
image.png
使用子查询形式取其他列的数据：
image.png
通过测试，这种方式整体效率有所提升。

主键优化

主键设计原则：

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

1.jpg

insert优化

insert优化原则：

  • 批量插入：数据量较大时，不要一条一条插入，可以批量插入，当然，建议一次插入数据不超过1000条
insert into t_user(id,name,age) values (1,'jack',20),(2,'lucy',30),(3,'timi',22);
  • mysql默认是自动提交事务，只要执行一条DML语句就自动提交一次，因此，当插入大量数据时，建议手动开启事务和手动提交事务。不建议使用数据库事务自动提交机制。
  • 主键值建议采用顺序插入，顺序插入比乱序插入效率高。
  • 超大数据量插入可以考虑使用mysql提供的load指令，load指令可以将csv文件中的数据批量导入到数据库表当中，并且效率很高，过程如下：
    • 第一步：登录mysql时指定参数
mysql --local-infile -uroot -p1234
  • 第二步：开启local_infile功能
set global local_infile = 1;
  • 第三步：执行load指令
use powernode;

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

load data local infile 'E:\\powernode\\05-MySQL高级\\resources\\t_temp-100W.csv' into table t_temp fields terminated by ',' lines terminated by '\n';

文件中的数据如下：
image.png

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

count(*)优化

分组函数count的使用方式：

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

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

注意：

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

update优化

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

有一张表：t_fruit

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卡住：
image.png
事务A结束之后，事务B继续执行：
image.png
当然，如果更新的不是同一行数据，事务A和事务B可以并发：
image.png

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

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

版权所有：伸手党盘
文章标题：MySQL优化
文章链接：https://ssdpan.cn/?post=428
本站文章来源于网络搜集和原创，仅供学习使用，未经授权请勿用于任何商业用途，如有侵权及时联系删除

推荐阅读：

扫描二维码，在手机上阅读


搜索

电视test

    hls.js player example

天气

辽宁交通广播

        菜鸟学堂

沈阳经济广播

        菜鸟学堂

个人资料

    blogger

    老大夫

    兽人永不为奴，除非包吃包住。

主页链接

分类

最新评论

链接

倒计时

    本日剩余 59.6%
    本周剩余 37.1%
    本月剩余 22.0%
    本年剩余 52.2%

日历

存档

标签

最新文章

热门文章