公司注册网站查询,dedecms的网站放在哪个文件夹里,网站怎么注册啊,jsp做网站都可以做什么系列专栏#xff1a;MySQL数据库进阶 前言
在看此篇前#xff0c;建议先阅读MySQL索引#xff0c;对索引有个基本了解#xff1a;MySQL数据库进阶-索引-CSDN博客
在进行SQL优化前#xff0c;我们必须先了解SQL查询的性能分析#xff0c;为什么这条SQL慢#xff0c;慢在…系列专栏MySQL数据库进阶 前言
在看此篇前建议先阅读MySQL索引对索引有个基本了解MySQL数据库进阶-索引-CSDN博客
在进行SQL优化前我们必须先了解SQL查询的性能分析为什么这条SQL慢慢在哪里这样我们才能更好的进行SQL优化。 性能分析
查看执行频次
查看当前数据库的 INSERT, UPDATE, DELETE, SELECT 访问频次
SHOW GLOBAL STATUS LIKE Com_______;
或者 SHOW SESSION STATUS LIKE Com_______;
例show global status like Com_______
慢查询日志
慢查询日志记录了所有执行时间超过指定参数long_query_time单位秒默认10秒的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启需要在MySQL的配置文件/etc/my.cnf中配置如下信息 # 开启慢查询日志开关 slow_query_log1
# 设置慢查询日志的时间为2秒SQL语句执行时间超过2秒就会视为慢查询记录慢查询日志 long_query_time2 更改后记得重启MySQL服务日志文件位置/var/lib/mysql/localhost-slow.log
查看慢查询日志开关状态
show variables like slow_query_log;
profile
show profile 能在做SQL优化时帮我们了解时间都耗费在哪里通过 have_profiling 参数能看到当前 MySQL 是否支持 profile 操作
SELECT have_profiling;
profiling 默认关闭可以通过set语句在session/global级别开启 profiling
SET profiling 1;
查看所有语句的耗时
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 语句执行过程中表如何连接和连接的顺序。 语法 # 直接在select语句之前加上关键字 explain / desc EXPLAIN SELECT 字段列表 FROM 表名 HWERE 条件;
EXPLAIN 各字段含义 idselect 查询的序列号表示查询中执行 select 子句或者操作表的顺序id相同执行顺序从上到下id不同值越大越先执行 select_type表示 SELECT 的类型常见取值有 SIMPLE简单表即不适用表连接或者子查询、PRIMARY主查询即外层的查询、UNIONUNION中的第二个或者后面的查询语句、SUBQUERYSELECT/WHERE之后包含了子查询等 type表示连接类型性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、all possible_key可能应用在这张表上的索引一个或多个 Key实际使用的索引如果为 NULL则没有使用索引 Key_len表示索引中使用的字节数该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下长度越短越好 rowsMySQL认为必须要执行的行数在InnoDB引擎的表中是一个估计值可能并不总是准确的 filtered表示返回结果的行数占需读取行数的百分比filtered的值越大越好 SQL 优化
插入数据
普通插入 采用批量插入一次插入的数据不建议超过1000条 手动提交事务 主键顺序插入
大批量插入 如果一次性需要插入大批量数据使用insert语句插入性能较低此时可以使用MySQL数据库提供的load指令插入。
# 客户端连接服务端时加上参数 --local-infile这一行在bash/cmd界面输入
mysql --local-infile -u root -p
# 设置全局参数local_infile为1开启从本地加载文件导入数据的开关
set global local_infile 1;
select local_infile;
# 执行load指令将准备好的数据加载到表结构中
load data local infile /root/sql1.log into table tb_user fields terminated by , lines terminated by \n;
主键优化
数据组织方式在InnoDB存储引擎中表数据都是根据主键顺序组织存放的这种存储方式的表称为索引组织表Index organized table, IOT
页分裂页可以为空也可以填充一般也可以填充100%每个页包含了2-N行数据如果一行数据过大会行溢出根据主键排列。
页合并当删除一行记录时实际上记录并没有被物理删除只是记录被标记flaged为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录到达 MERGE_THRESHOLD默认为页的50%InnoDB会开始寻找最靠近的页前后看看是否可以将这两个页合并以优化空间使用。
MERGE_THRESHOLD合并页的阈值可以自己设置在创建表或创建索引时指定
主键设计原则 满足业务需求的情况下尽量降低主键的长度 插入数据时尽量选择顺序插入选择使用 AUTO_INCREMENT 自增主键 尽量不要使用 UUID 做主键或者是其他的自然主键如身份证号 业务操作时避免对主键的修改
order by优化 Using filesort通过表的索引或全表扫描读取满足条件的数据行然后在排序缓冲区 sort buffer 中完成排序操作所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序 Using index通过有序索引顺序扫描直接返回有序数据这种情况即为 using index不需要额外排序操作效率高
如果order by字段全部使用升序排序或者降序排序则都会走索引但是如果一个字段升序排序另一个字段降序排序则不会走索引explain的extra信息显示的是Using index, Using filesort如果要优化掉Using filesort则需要另外再创建一个索引如
此时使用如下sql会全部走索引
select id, age, phone from tb_user order by age asc, phone desc;
总结 根据排序字段建立合适的索引多字段排序时也遵循最左前缀法则 尽量使用覆盖索引 多字段排序一个升序一个降序此时需要注意联合索引在创建时的规则ASC/DESC 如果不可避免出现filesort大数据量排序时可以适当增大排序缓冲区大小 sort_buffer_size默认256k
group by优化 在分组操作时可以通过索引来提高效率 分组操作时索引的使用也是满足最左前缀法则的
如索引为idx_user_pro_age_stat则句式可以是select ... where profession order by age这样也符合最左前缀法则
limit优化
常见的问题如limit 2000000, 10此时需要 MySQL 排序前2000000条记录但仅仅返回2000000 - 2000010的记录其他记录丢弃查询排序的代价非常大。 优化方案一般分页查询时通过创建覆盖索引能够比较好地提高性能可以通过覆盖索引加子查询形式进行优化
例如
-- 此语句耗时很长
select * from tb_sku limit 9000000, 10;
-- 通过覆盖索引加快速度直接通过主键索引进行排序及查询
select id from tb_sku order by id limit 9000000, 10;
-- 下面的语句是错误的因为 MySQL 不支持 in 里面使用 limit
-- select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10);
-- 通过连表查询即可实现第一句的效果并且能达到第二句的速度
select * from tb_sku as s, (select id from tb_sku order by id limit 9000000, 10) as a where s.id a.id;
count优化
MyISAM 引擎把一个表的总行数存在了磁盘上因此执行 count(*) 的时候会直接返回这个数效率很高前提是不适用where InnoDB 在执行 count(*) 时需要把数据一行一行地从引擎里面读出来然后累计计数。 优化方案自己计数如创建key-value表存储在内存或硬盘或者是用redis
count的几种用法 如果count函数的参数count里面写的那个字段不是NULL字段值不为NULL累计值就加一最后返回累计值 用法count(*)、count(主键)、count(字段)、count(1) count(主键)跟count(*)一样因为主键不能为空 count(字段)只计算字段值不为NULL的行count(1)引擎会为每行添加一个1然后就count这个1返回结果也跟count(*)一样count(null)返回0
各种用法的性能 count(主键)InnoDB引擎会遍历整张表把每行的主键id值都取出来返回给服务层服务层拿到主键后直接按行进行累加主键不可能为空 count(字段)没有not null约束的话InnoDB引擎会遍历整张表把每一行的字段值都取出来返回给服务层服务层判断是否为null不为null计数累加有not null约束的话InnoDB引擎会遍历整张表把每一行的字段值都取出来返回给服务层直接按行进行累加 count(1)InnoDB 引擎遍历整张表但不取值。服务层对于返回的每一层放一个数字 1 进去直接按行进行累加 count(*)InnoDB 引擎并不会把全部字段取出来而是专门做了优化不取值服务层直接按行进行累加
按效率排序count(字段) count(主键) count(1) count(*)所以尽量使用 count(*)
update优化避免行锁升级为表锁
前面讲的都是基于查询的基于update最常见的问题就是锁升级也是update最重要的点。
InnoDB 的行锁是针对索引加的锁不是针对记录加的锁并且该索引不能失效否则会从行锁升级为表锁。
如以下两条语句 update student set no 123 where id 1;这句由于id有主键索引所
以只会锁这一行
update student set no 123 where name test;这句由于name没有索引所以会把整张表都锁住进行数据更新解决方法是给name字段添加索引。 如果本文对你有帮助记得点赞关注你的支持是我最大的动力
如果您有任何疑问或建议欢迎在评论区留言一起探讨交流
