掌握excel线性回归技巧助力数据分析与决策优化
1029
2022-05-28
本篇博客再次去复盘 Mysql 中的索引,从该角度学习 MySQL 的优化。
索引
索引不是越多越好,因为数据库在插入,删除,更新数据时,都会更新索引,导致效率降低。
新建索引的时候优先选择离散度高的索引(列中不同值多的,适合做索引,例如 id 适合,status 状态不适合,因为一般只有2个值),如果一个列中 NULL 值特别多,也不适合做索引列。
如果仅依赖一列无法做到唯一,可以选择几列同时作为索引,即复合索引,复合索引要优先把离散度高的列放在前面,形式上是左侧。
优化细节
尽量避免 select * 的出现,在仅有主键索引的情况下,下述命令时间差别(假设查询的是一个超过百万数据的表格)
select * from 百万表名 # 耗时 1.9s select id,name,age from 百万表名 # 耗时 1.5s
如果确定一张表中主键没有间隔,即完整连续,可以使用下述分页办法提高查询效率。
select id,name from 百万表名 limit 1000000,10000; # 0.25秒 select id,name from 百万表名 where id > 1000000 limit 10000; # 0.024秒
在仅有主键索引的情况下,速度提高了 10 倍。
where 查询注意事项
优先使用带索引的列,例如下述两个查询,得到的结果一样,但耗时却相差较大
select id,name from 百万表名 where id =5 # 0.02秒 select id,name from 百万表名 where tid = 88888 # 0.3秒
先使用索引,然后在进行正向过滤(=,in,<,>),最后执行复杂条件(子查询,like 模糊查询)
优先过滤数字型字段,然后在选择字符串;
条件里面字段类型要明确,例如数字型字段=数字,字符型字段=字符
不用OR,而用 union 或 union all 进行组合
union 操作符用于合并两个或多个 select 语句的结果集,前提是结果集的列数相同,每列的数据类型也相同。
union:合并两个查询结果中相同的数据;
union all:不合并两个查询结果中的相同数据
可以对比下述语句的执行计划
select * from `百万表名` where id = 5 or id = 1 order by id desc (select * from `百万表名` where id = 5 order by id desc) union all (select * from `百万表名` where id = 1 order by id desc) order by null
尽量使用 = 替换 in,尽量避免使用非正向过滤,例如 !,!=,not in,not exists,not like
尽量不使用 %匹配内容。
如果在 in 中查询的数据是连续的,可以使用 between...and... 代替;
实战中也可以使用 exists 代替 in
select id from `百万表名` where exists(select 1 from 另一张表 where id=`百万表名`.id);
影响数据库性能的参数
公共参数及其默认值
同时最大连接数
max_connections = 151
查询排序时缓冲区大小,仅限制 order by 和 group by
sort_buffer_size = 2M
打开文件数限制
open_files_limit = 1024
InnoDB参数及其默认值
查看所有配置使用如下命令
show variables like 'innodb_%';
索引和数据缓冲区大小
innodb_buffer_pool_size = 128M # 建议设置内存大小的60%~70%
缓冲池实例个数
innodb_buffer_pool_instances = 1 # 建议设置为 4 或者 8
控制MySQL的磁盘写入策略
innodb_flush_log_at_trx_commit = 1
这部分找到一篇讲解的不错的 博客,可以参考学习
是否共享表空间
innodb_file_per_table = OFF
关闭独立表空间将导致共享表空间 idbdata 持续增大,从而影响 I/O 性能,可以修改为开启独立表空间模式。
innodb_file_per_table = 1 # 为使用独占表空间 innodb_file_per_table = 0 # 为使用共享表空间
与之对应的几个配置如下所示:
修改独占空表空间的数据存储位置
innodb_data_home_dir = "C:\mysql\data\" # 数据库文件所存放的目录 innodb_log_group_home_dir = "C:\mysql\data\" # 日志存放目录 innodb_data_file_path=ibdata1:10M:autoextend innodb_file_per_table=1
日志缓冲区大小
innodb_log_buffer_size = 8M
MySQL
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。