MySql——Sql优化

君山李小狼

已于 2024-06-23 23:30:32 修改

阅读量368

点赞数

分类专栏： MySql 文章标签：学习

于 2022-03-28 14:42:56 首次发布

本文链接：https://blog.csdn.net/qq_35003730/article/details/123790514

版权

MySql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

SQL性能分析

sql执行频率

MySQL客户端连接成功后，通过show [] status命令可以提供服务器状态指令。通过如下指令，可以查看当前数据库的操作的访问频次

#七个下划线
SHOW GLOBAL STATUS LIKE 'Com_______'

在这里插入图片描述

慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位：秒，默认10秒）的所有SQL语句的日志
MySQL的慢查询日志默认没有开启，需要配置（/etc/my.cnf)，配置完成后需重启，查看慢日志文件中信息
/var/lib/mysql/localhost-slow.log

show variables like 'slow_query_log'

#开启MySQL慢日志查询开关
slow_query_log=1
#设置慢日志时间为2s，SQL语句执行时间超过2s，就会被视为慢查询
long_query_time=2

SQL性能分析

profile：show prifile能够在做sql优化时帮助我们了解时间都耗费去哪了，通过have_profiling参数，能够看到当前mysql是否支持profile操作

SELECT @@have_profiling;
SET profiling = 1;
#查看每一条sql的耗时基本情况
show profiles;
#查看指定query_id的sql语句各个阶段的耗时情况
show profile for query query_id;
#查看指定query_id的sql语句cpu的使用情况
show profile cpu for query query_id;

explain: 执行计划
explain或者desc命令获取mysql如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序
其中结果各字段的含义

key：实际使用的缩影，如果为null则没有使用
key_len：表示缩影中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
rows：mysql认为必须要执行查询的行数，在innodb中是一个谷梁志，可能并不总是准确的
filter：表示返回结果的行数占需要读取行数的百分比，越大越好

SQL优化

最左前缀法则

如果索引了多列（联合索引），要遵循最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引的列，如果跳跃某一列，索引将部分失效（后面的字段索引失效）

范围查询
联合索引中，出现范围查询（<,>),范围查询右侧的列索引失效，
注：加上等号则不会失效
模糊查询
如果仅仅是尾部模糊匹配，索引不会失效，如果头部模糊匹配，索引失效
or连接的条件
用or分割开的条件，如果or前的条件中列有索引，后面的列没有，那么设计的索引都不会被用到，需前后都为索引才能用到
数据分布影响
如果mysql评估使用索引币全表更慢，则不适用索引
null
使用is null和is not null时，若表中数据为null的数据很多则使用is not null会使用索引，大部分不为null，使用is null会走索引，需要看数据的具体分布情况
索引sql提示

select * from tab_user use index(ids_user_pro)where ...
select * from tab_user ignore index(idx_user_pro)

select * from tab_user force index(idx_user_pro) where

覆盖索引
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该缩影中已经全部能够找到），减少使用select*，避免回表查询

前缀索引
当字段类型为字符串时，有时候需要索引很长的字符串，这回让索引变得很大，浪费磁盘io，所以此时可以将字符串的一部分前缀

create index idx_xxx on tab_name(column(n));

在这里插入图片描述

索引设计原则

针对数据量较大，且查询比较频繁的表建立索引
针对常作为条件查询，排序，分组操作的字段建立索引
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
如果时字符串类型的字段，长度较长时，建立前缀索引
尽量使用联合索引，减少单列索引
要控制索引数量，不要太多
如果索引列不能存储null值，请在创建表时使用not null约束，当优化器知道每列是否包含null值时，可以更好的确定哪个索引可以最有效用于查询

order by优化

order by分为两种方式

Using filesort:通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所以不是通过索引直接返回排序结果的排序都叫FileSort排序
Using index:通过有序索引顺序扫描直接返回有序数据，这种情况为using index，不需要额外排序，操作效率高

#没有创建索引时，根据age，phone进行排序 using filesort
explain select id, age, phone from tab_user order by age, phone
#创建索引
create index idx_user_age_phone_aa on tb_user(age, phone)
#创建索引后，根据age,phone进行升序排序 using index
explain select id, age, phone from tb_user order by age, phone
#创建索引后,根age,phone进行降序排序 backward index using index
explain select id, age, phone from tb_user order by age desc, phone desc
#不符合最左前缀 using index, usingfileSort
explain select id, age, phone from tab_user order phone, age
#根据age ohone进行一个升序一个降序 using index using filesort
explain select id, age, phone from tb_user order by age asc, phone desc

总结

根据
根据排序字段建立合适的索引，多字段排序时，也遵行最左前缀法则
尽量使用覆盖索引
多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC)
如果不可避免出现fileSort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)
show varibles like ‘sort_buffer_size’

Group by优化

在分组操作时，可以通过索引来提高效率
分组操作时，索引的使用也是满足最左前缀法则的（即使前面的在where中）

limit优化

一个常见又非常头疼的问题就是limit 2000000，10,此时mysql需要排序钱2000010记录，仅仅返回2000000-20000010的记录，其他记录丢弃，查询排序的代价非常大

优化思路：一般分页查询时，通过覆盖索引能够比较好的提高性能，可以通过覆盖索引加子查询形式进行优化

explain select * from tb_sku t, (select id from tb_sku order by id limit 200000, 10) a where t.id = a.id;

Count优化

myISAM引擎把一个表的总行数存在了磁盘上，count(*)可以直接获取，效率很高
InnoDB引擎则需要把数据一行一行地从引擎里面读取出来然后累计计数
优化思路自己使用map等计数
效率排序 count(字段) < count(主键id) < count(1) 约等于count() 所以尽量使用count()

update优化

InnoDB的行锁时针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁

插入数据优化

批量插入：插入数据量多的情况下使用批量插入，批量插入最好为500-1000条，再多可分批次
手动提交事务
主键顺序插入：不然容易出现页分裂情况，效率下降

大批量插入数据

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行操作。操作如下：
在这里插入图片描述

#客户端连接服务段时，加上参数  --local-infile
mysql --local-infile -u -root -p
#设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
#执行load指令酱准备号的数据，加载到表结构中
load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n'