免费制作,太原seo网站排名,营销型网站设计流程,wordpress 输出子分类目录
SQL性能分析
SQL执行频率
SQL慢查询日志
Profile
Explain
SQL优化
插入数据的优化
主键优化
Order By优化
Group By优化
Limit 优化
Count 优化
Update 优化
多表连接查询优化 SQL性能分析
通过SQL性能分析来做SQL的优化#xff0c;主要是优化SQL的查询语…目录
SQL性能分析
SQL执行频率
SQL慢查询日志
Profile
Explain
SQL优化
插入数据的优化
主键优化
Order By优化
Group By优化
Limit 优化
Count 优化
Update 优化
多表连接查询优化 SQL性能分析
通过SQL性能分析来做SQL的优化主要是优化SQL的查询语句其中索引的优化占主要部分
SQL执行频率 通过以下命令来查看INSERT、UPDATE、DELETE、SELECT的访问频次查看不同语句所占的比例 根据不同语句的执行频率来进行不同的优化 SHOW SESSION/GLOBAL STATUS; 查看当前会话/全局服务器的状态信息 SHOW SESSION/GLOBAL STATUS LIKE ‘Com_______’ #(7个下划线)查看当前会话/全局的SQL增删改查的执行频率就是从服务器的状态信息中提取关于增删改查的信息 show global status like Com_______; #查看全局的SQL语句插入、更新、删除、查询的执行频率 SQL慢查询日志 查看MySQL的慢查询日志的开关是否打开 SHOW VARIABLES LIKE slow_query_log; 慢查询日志的作用 通过慢查询日志可以定位到哪些查询语句需要进行优化 慢查询日志是记录了所有执行时间超过指定参数long-query-time默认10s的SQL语句的日志即当SQL语句的执行时间超过10s我们就会认为此语句是慢查询 慢查询日志的路径 Linux系统关于MySQL慢查询日志 Linux关于MySQL的配置文件在/etc/my.cnf中 Linux中默认MySQL的慢日志查询没有开启需要配置MySQL的配置文件中配置如下信息: #开启MySQL慢日志查询开关 slow_query_log 1 #设置慢日志的时间为2sSQL执行时间超过2s就会被视为慢查询 log_query_time 2 #保存之后然后重启mysql服务 Linxu会自动生成相关的日志文件存放慢日志查询中记录的信息存放的位置在/var/lib/mysql/localhost-slow.log中 Windows系统关于MySQL慢查询日志 Windows默认开启了慢查询日志的开关可以在MySQL的配置问价查看慢查询日志信息 Windows关于MySQL的配置文件在C:\ProgramData\MySQL\MySQL Server 8.0\my.ini中以下是关于慢查询日志的配置信息 slow-query-log1 #开启MySQL慢日志查询开关 slow_query_log_file慢查询日志的存放位置 #指定慢查询日志文件的存放位置 long_query_time10 #设置慢日志的时间为10s 如果还是找不到对应的慢查询日志的存储位置 可以通过show variables like slow_%来查看 慢查询日志的格式 Time执行SQL的时间 UserHost通过哪个用户在哪个主机上登录到此SQL的 Query_time当前命令执行耗时的时间 Lock_time 表锁的时间InnoDB的行锁等待不会反应在这里 Rows_sent 返回多少行 Rows_examined检查了多少行 USE使用的哪个数据库 SET timestamp执行SQL的时间戳 ApplicationName此字段会注释掉用于告知此用户是通过什么软件连接到数据库的此处为DataGrip 以及查询语句 如果Rows_examined很大而Rows_sent很小则说明此查询需要优化 Profile 通过Profile可以帮助我们了解SQL语句执行的时间都耗费到哪里去了也会记录错误的语句 查看当前MySQL是否支持profile操作以及是否启用了此操作 show variables like %profil%; 在MySQL中开启Profile功能 set session/global profiling True; Window和Linux默认Profile是关闭的不同的版本可能有区别 Profile相关语句 SHOW PROFILES; #查看会话中每一条SQL的耗时基本情况每条SQL语句都有唯一的query_id SHOW PROFILE FOR QUERY query_id; #查看此query_id对应的SQL语句各个阶段的耗时情况 SHOW PROFILE CPU FOR QUERY query_id; #查看此query_id对应的SQL语句的CPU使用情况 Explain
前面的分析语句都是通过时间粗略分析explan可以看到Sql语句的执行计划如何执行select语句信息select语句执行过程中如何连接以及连接的顺序经常通过此判断SQL语句的性能
MySQL索引3——Explain关键字和索引优化SQL提示、索引失效、索引使用规则_静下心来敲木鱼的博客-CSDN博客 SQL优化
插入数据的优化 Inster 优化 1、当插入多条数据时使用批量插入 INSTER INTO 表名 VALUES(值1.1 , 值2.1),(值1.2 , 值2.2)……; 2、当要插入的数据超过百条时建议使用事务来进行优化手动提交事务通过事务来优化 START TRANSCANTION; INSERT INTO 表名 VALUES(值1.1 , 值2.1),(值1.2 , 值2.2)……; INSERT INTO 表名 VALUES(值1.100, 值2.100),(值1. 101, 值2.101)……; COMMIT; 3、当要大批量的插入数据时使用INSTER语句插入性能低可以使用MySQL提供的LOAD进行进行插入将符合一定规则的文件直接导入到Mysql生成相应的表 客户端连接MySQL时加上参数--local-infile表示当前客户端连接服务端时需要加载客户端本地文件 mysql --local-infile -u root -p 2、设置全局参数local_infile为1表示开启从本地加载文件导入数据的开关 show variables like %infile%; #查看是否开启load开关 set global local_infile1; #开启load开关 3、执行load指令将准备好的本地文件中的数据加载到表结构中 load data local infile ‘本地文件的路径’ into table 表名 fields terminated by ‘每个字段对应值的分隔符’ lines terminated by ‘每一行数据的分隔符’ 建议分隔符都使用英文符号 模拟load插入 员工信息.txt文件路径为C:/Users/123/Desktop/员工信息.txt 创建user123表 create table User123( id int auto_increment primary key, name varchar(10), age int, origo varchar(10) ); 将.txt文件导入表中 load data local infile C:/Users/Tdemo/Desktop/员工信息.txt into table user123 fields terminated by , lines terminated by ;; 此时查看表的数据 select * from user123; 主键优化 在对主键进行插入数据时顺序插入性能高于乱序插入 InnoDB关于主键的数据组织方式 表数据都是根据主键顺序组织存放的这种存储方式的表称为索引组织表(index organized table IOT)我们再进行主键存放时就是根据顺序存放的 页分裂——乱序插入可能出现 页时InnoDB磁盘管理的最小单元一个页的大小默认为16k叶子节是有序的 每个页包含了2-N行数据如果一行数据过大就会行溢出 页可以为空、也可以填充一半、也可以填充100% 主键顺序插入 主键乱序插入——可能会出现页分裂 50找到要插入的位置在第1个页的末尾 发现此时第1页没有空间进行插入此时会新建1个页 然后找到第1个页的中间位置将其右边的数据都迁移到新建的页中然后再将50添加到新建的页中 此时再对三个页重新排序需要消耗较多的性能 页合并 当删除一行记录时实际上记录并没有被物理删除只是记录被标记(flaged)为删除并且它的空间变得允许被其它记录声明使用 当页中删除的记录达到MERGE_THRESHOLD默认为页的50%,InnoDB会开始寻找最靠近的页前或后看看是否可以将两个页合并以优化空间 Merge_threshold 合并页的阈值可以自己设置在创建表或索引时指定 主键设计原则 1、满足业务需求的情况下尽量降低主键的长度此操作可以降低二级索引占用的空间 2、插入数据时建议主键进行顺序插入可以使用AUTO_INCREMENT自增主键 3、尽量不要使用无序的值来作为主键导致插入主键时可能乱序在检索时也会耗费大量的IO 4、在进行业务操作时尽量避免对主键的修改 Order By优化 进行排序时通过Explain显示的Extra字段的内容类型如果为Using Filsort则需要进行优化尽量将其优化为Using Index通过满足覆盖索引实现 Using Filesort和Index讲解 1、Using Filesort通过表的索引或者全表扫描读取满足条件的数据行然后在排序缓冲区sort buffer中完成排序操作所有不是通过索引直接返回排序结果的排序都叫FileSort排序 2、Using Index查找使用了索引并且通过索引可以接返回有序的数据不需要额外排序操作效率高 3、Backward index scan反向扫描索引进行降序排序时会出现也是单独子啊排序缓冲区完成排序操作需要进行优化 如何优化Backward index scan 在创建索引时MySQL8之前的版本默认只是针对升序创建了索引我们可以额外对字段创建关于降序的索引 格式如下在字段后面跟上排序方式 create index 索引名 on 表名(字段1 asc , 字段2 desc); 为字段1创建升序索引为字段2创建倒序索引 总结 1、当创建联合索引后在进行排序时如果一部分按照升序排序一部分按照降序排序则会产生using filesort此时可以为此单独再创建索引 2、在使用索引时尽量实现覆盖索引 3、在使用是要避免索引失效 4、如果不可避免的出现filesort在大数据排序时可以适当增大排序缓冲区的大小sort_buffer_size默认256k 5、Show variables like ‘sort_buffer_size’; #查看排序缓冲区的大小当排序缓冲区占满之后会占用磁盘进行排序 Group By优化 主要研究索引对分组查询的影响思路和排序优化是一致的 进行分组时通过Explain显示的Extra字段的内容类型如果为Using Temporary则需要进行优化尽量将其优化为Using Index通过满足覆盖索引实现 Using Temporary讲解 Using temporary查找使用到了临时表性能比较低 总结 1、在分组操作时可以通过索引来提高效率 2、在分组操作时索引的使用也是满足最左前缀法则的 3、在分组操作时尽量实现覆盖索引避免回表查询 4、在分组操作时避免索引失效 Limit 优化 在大数据量情况下越往后分页耗时越长可能也会造成资源浪费 当我们查询limit 10000,10时我们需要排序MySQL前10010条记录但是只范围10000-10010之间的10条记录其它记录丢弃此时查询的代价就很大排序是为了保证每次通过limit查到的记录都一样 可以通过覆盖索引子查询的方式优化 通过子查询找到10000-10010对应数据的主键值然后再通过主键值来查找对应的数据 需要注意有些版本Mysql不支持在in之后使用子查询的语法所以以下的语句无法实现 Select * from 表名 where 主键值 in (select 主键值 from 表名 order by limit 10000,10); 方式二 我们可以将子查询的结果看作一张表通过多表查询来实现 Select a.* from 表1 a, (select 主键值 from 表1 order by limit 10000,10) b where a.主键值b.主键值; Count 优化 不同存储引擎对于Count(*)的不同处理方式 MyISAM引擎中会把一个表的总行数存在磁盘上因此执行count(*)的时候才会直接返回这个数效率很高前提是查询时没有where条件 InnoDB引擎在执行count()*时需要把遍历整张表把数据一行一行地从引擎里面读出来然后积累计数如果为Null则不加1如果为非Null值则计数加1最后将积累的计数输出无论是否有where条件InnoDB都是这样操作的 Count的不同用法以及性能差异 Count的主要用法有count(*)、count(主键)、count(字段)、count(X) Count(主键) ——主键的总记录行数 会遍历整张表把每一行的主键值提取出来返回给服务层服务层拿到主键后直接按照行数进行累加不进行判断是否为Null Count(字段) ——统计此字段有多少值不为Null不一定是总记录数 分为加了Not Null约束和没有加Null约束两种情况 没有加Not Null约束需要判断值是否为Null 把每一行的字段对应的值提取出来返回给服务层服务层判断是否为Null如果为Null则不加1如果为非Null值则计数加1 加了Not Null约束不需要判断是否为Null 把每一行的字段对应的值提取出来返回给服务层服务层进行累加 Count(x) ——x为数字查询表的总记录行数 InnoDB会遍历整张表不过不会进行取值服务层对于返回的每一行放一个数字x进去直接按照行进行累加 Count(*) ——表当中的总记录行数 Mysql对InnoDB引擎做了优化InnoDB引擎在执行count()*时需要把遍历整张表不过不会进行取值直接按照行数进行累加最后将积累的计数输出底层count(*)count(0) 性能比较 Count(*) ≈ Count(x) Count(主键) Count(字段) 尽量使用Count(*) 如何优化InnoDB引擎对Count关键字的处理 没有较好的优化策略想要优化的话可以在插入数据或删除数据时自行计数只不过比较繁琐 Update 优化 对于InnoDB引擎来说默认事务隔离级别是通过行锁实现的并且该行锁是针对索引加的锁不是针对记录加的锁如果该索引失效了则会从行锁升级为表锁 即对于建立了索引的字段使用的是行锁没有建立索引的字段使用的是表锁一旦锁表就会降低并发操作 因此优化建议为 1、使用Update时使用索引列作为更新条件避免表锁 2、避免出现索引失效将行锁升级为表锁 多表连接查询优化 内连接时Mysql会自动把输出结果小的表选为驱动表所以大表输出结果多的表的字段最好加上索引 左外连接时左表会全表查询不可避免地因为要显示左表的全部数据建议右边表的字段最好加上索引 右外连接时右表会全表查询建议左边表的字段最好加上索引 优化手段 1、在外连接时尽量将小表作为驱动表并保证被驱动表上的字段建立了索引 3、内连接时尽量将大表的字段加上索引
