数据库的优化相关问题

娃哈哈哈❚❚

已于 2024-06-10 18:35:52 修改

阅读量1.1k

点赞数 25

文章标签：数据库

于 2024-05-23 20:54:25 首次发布

本文链接：https://blog.csdn.net/m0_73756512/article/details/139153104

版权

MySQL的优化

优化手段（包括但不仅限）

SQL查询优化：这是最低成本的优化手段，通过优化查询语句，适当添加索引等方式进行。并且效果显著

库表结构优化：通过规范化设计、优化索引和数据类型等方式进行库表结构优化，需要对数据库结构进行调整和改进。

系统配置优化：根据硬件和操作系统的特点，调整最大连接数，内存管理，IO调度等参数

硬件优化：升级硬盘，增加内存容量，升级处理器等硬件方面的投入。

查看数据库的情况

通过以下命令可以查看当前数据库在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或uodate的次数很高，可能说明服务其运行的应用程序过于依赖写入操作和少量的读取操作

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

慢查询日志

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

使用以下命令

show variables like 'slow_query_log';

怎样开启慢查询日志：

修改my.ini文件，添加以下配置

[mysql]
slow_query_log=1
long_query_time=2
添加完一定要重启mysql服务
默认日志记录在：C:\dev\mysql\8.0.36-winx64\data目录下。
或者在

show profiles

通过show profiles可以查看一个sql语句在执行过程中具体的耗时情况

查看是否支持profile操作：select @@have_profiling

查看profiling是否开启：select @@profiling（navicat默认开启）

将profiling打开：set profiling = 1（dos窗口默认是关闭的。可以通过命令打开）

show profiling：查看执行过的所有的select语句耗时情况。

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

show profile for query id值;

查看整个执行过程中cpu的占用情况

show profile cpu for query id值;

explain：可以查看一个DQL语句的执行情况，根据执行计划可以做出相应的优化措施

explain select * from emp where empno =7369;

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

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

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：反应索引中在查询中使用的列所占字节数

row：查询扫描的预估计行数

extra：给出查询相关的额外信息和说明。

索引优化

最左前缀原则

最左前缀原则：

要想让索引生效，必须遵循最左前缀原则；

举例：如果给customer表的name，age，gender添加索引。并且在创建索引的时候顺序为（name，age，gender）那么在编写select语句的时候，where当中有很多条件。如果这些条件中没有name字段的参与，则复合索引失效。必须有name这个最左边的字段参与条件，这样复合索引会生效，生效后：可能是完全索引可能是不完全索引

创建一张表：
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);

验证1：
explain select * from t_customer where name='zhangsan' and age=20 and gender='M';
/*完全使用了索引，索引长度1033个字节*/

验证2：
explain select * from t_customer where name='zhangsan' and age=20;
/*部分使用了索引，索引长度1028 ，说明gender在索引当中占用5个字节的长度 */

验证3：
explain select * from t_customer where name='zhangsan';
/*部分使用了索引，索引长度1023，说明age在索引当中占用5个字节的长度 */

验证4：
explain select * from t_customer where age=20 and gender='M' and name='zhangsan';
/*完全使用了索引，索引长度1033个字节 */

验证5：
explain select * from t_customer where gender='M' and age=20;
/*因为条件不符合最左前缀原则，没有name条件出现，所以不会使用索引，进行全表扫描 */

验证6：
explain select * from t_customer where name='zhangsan' and gender='M';
/*部分使用了索引，只有name字段使用索引，因为中间断开了，导致gender没有使用索引 */

验证7：
explain select * from t_customer where name='zhangsan' and gender='M' and age=20;
/*完全使用了索引*/

范围查询时，在“范围条件”右侧的列索引会失效：
explain select * from t_customer where name='zhangsan' and age>20 and gender='M';

建议范围条件中添加等号，这样可以让索引完全生效
explain select * from t_customer where name='zhangsan' and age>=20 and gender='M';

指定索引

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

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

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

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

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

2、ignore index(索引名称)：忽略该索引

3、force index(索引名称)：强行使用该索引

单列索引和复合索引

优先选择单列索引，但是当查询语句的条件中由多个条件，建议将这几个列创建为复合索引，因为创建单列索引很容易引起回表查询。

复合索引，越靠左，越起到主导作用：对比以下两个代码

create index idx_emp_ename_job on emp(ename,job);

create index idx_emp_job_ename on emp(job,ename);

索引创建的原则：

1、表数据量庞大，通常超过百万条数据。

2、经常出现在where，order by，group by后面的字段建议添加索引。

3、创建索引的字段尽量具有很强的唯一性。

4、如果字段存储文本，内容较大，一定要创建前缀索引。

5、尽量使用复合索引，使用单列索引容易回表查询。

6、如果一个字段中的数据不会为NULL，建议建表时添加not null约束，这样优化器就知道使用哪个索引列更加有效。

7、不要创建太多索引，当对数据进行增删改的时候，索引需要重新重新排序。

8、如果很少的查询，经常的增删改不建议加索引。

索引失效的情况：

1、索引列参加了运算，索引会失效：

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

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

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

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

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

SQL优化

order by 优化

优化原则：

1、排序也要遵循最左前缀法则。

2、使用覆盖索引。

3、针对不同的排序索引，创建不同索引。（如果所有字段都是升序，或者所有字段都是降序，则不需要创建新的索引）

4、如果无法避免filesort，要注意排序缓存的大小，默认缓存大小256KB，可以修改系统变量sort_buffer_size

show variables like 'sort_buffer_size';

单列：

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

using index：表示使用索引，因为索引是提前排好序的，效率很高

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

复合：最好加上复合索引（都升/降）

如果通过两个字段进行排序，最好给两个字段添加索引

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

group by优化

添加索引进行优化，一个单列索引，多个用复合索引

limit优化

数据量大时，取数据时，往后效率越低。

解决办法：使用覆盖查询索引+子查询的形式来提高效率

主键优化：

主键设计原则：

1、主键值不要太长，二级索引叶子结点上存储的是主键值，主键值太长，容易导致索引占用空间较大。

2、尽量使用auto_increment生成主键。尽量不要使用uuid做主键，因为uuid不是顺序插入。

3、最好不要使用业务主键，因为业务的变化会导致主键值的频繁修改，主键值不建议修改，因为主键值修改，聚集索引一定会重新排序。

4、在插入数据时，主键值最好是顺序插入，不要乱序插入，因为乱序插入可能会导致B+树叶子结点频繁的进行页分裂与页合并操作，效率较低。

insert优化

insert优化原则

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

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

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

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

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

第一步：登录MySQL时指定参数

mysql --local-infile -u用户名 -p密码

第二步：开启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 '文件路径' into table t_temp fields terminated by ',' lines terminated by '\n';

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事务才可以继续。

娃哈哈哈❚❚

关注

25
点赞
踩
26

收藏

觉得还不错? 一键收藏
1
评论
数据库的优化相关问题

如果这些条件中没有name字段的参与，则复合索引失效。当存储引擎是InnoDB时，表的行级锁是针对索引添加的锁，如果索引失效了，或者不是索引列时，会提升为表级锁。A事务和B事务，开启A事务后，通过A事务修改表中某条记录，修改后，在A事务未提交的前提下，B事务去修改同一条记录时，无法继续，直到A事务提交，B事务才可以继续。如果你想真正提高效率，可以自己使用额外的其他程序来实现，例如每向表中插入一条记录时，在redis数据库中维护一个总行数，这样获取总行数的时候，直接从redis中获取即可，这样效率是最高的。
复制链接

扫一扫