单表优化
最佳左前缀原则为,保持索引的定义和使用顺序的一致性
将含In的范围查询,放到where条件语句的最后。
索引需要逐步优化
两表优化
小表驱动大表
避免索引失效的一些原则:
复合索引,不要跨列或无序使用(最佳左前缀)
复合索引,尽量使用全索引匹配。就是尽量避免复合索引有三列,而只用到两列。
复合索引不能使用范围查询(!= <>)或is null (is not null),否则自身及右侧索引全部失效。
不要对索引进行操作(计算,函数,类型转换),否则索引失效。
select .. where A.x*3 = ...
-- A.x列存在索引,索引失效
==注意:SQL优化,是一种概率层次的优化,原因是服务层中有SQL优化器,可能会影响我们的优化,出现与我们预期不符的情况。==
尽量不要使用or,会使左右两边索引全部失效。
其他的优化方法
exist 和 in :如果主查询的数据极大,则使用in,如果子查询的数据大,则使用exist。
order by :选择使用单路还是双路;调整buffer容量的大小;避免使用select *...;复合索引不要跨列使用。
SQL慢查询日志:
MySQL提供的一种日志记录,用于记录MySQL中响应超过阈值的SQL语句(默认是10秒),慢查询日志默认是关闭的,建议:开发时打开,最终部署时关闭。
- 查看慢查询日志是否开启
sql show variables like '%slow_query_log%';
- 临时开启慢查询日志(在内存中开启),将服务重启后关闭。
set global slow_query_log = 1;
- 永久开启慢查询日志,修改MySQL配置文件
ini [mysqld] ... + slow_query_log = 1 + slow_query_log_file = 日志存放地址
- 查看慢查询阈值并修改
```sql -- 查看慢查询阈值时间 show variables like '%long_query_log%'
-- 修改阈值(临时修改),重新登录MySQL后生效(不需要重启服务) set global long_query_time = 5
--修改阈值(永久修改),修改MySQL配置文件,重启服务生效 [mysqld] ... + long_query_time = 5 ```
- 查询超过阈值的SQL
sql show global status like '%slow_queries%' -- 当然这种方法只能查看超过阈值的SQL条数,具体的SQL语句还是要通过查看上面设置的慢查询日志来确定
- 使用mysqldumpslow工具来查询慢SQL
通过查看日志的方式虽然可行,但是当我们的SQL语句多了之后,慢SQL语句也随之变多,这样一来查看慢sql日志就成了一件费时费力的事,且日志内容较多,容易造成混乱。使用mysqldumpslow工具可以主动的过滤出我们希望查看的慢SQL语句,比如可以对吗慢SQL语句进行排序,进行正则匹配,只取10条数据等等。
PROFILES分析海量数据
-- 查看profiles是否开启
show variables like '%profiling%';
查看SQL语句执行的Query_id以及持续时间
show profiles;
注意: show profiles可以看到profiling打开之后的SQL语句,会显示ID,持续时间,以及SQL语句,但是我们无法精准的看到各个硬件消费的时间。
查看SQL语句执行的具体硬件消费情况
-- 查看全部信息
show profile all for query Query_id;
-- 查看部分信息
show profile cpu, block io for query Query_id;
全局查询日志
全局查看日志会记录开启之后的全部SQL语句,因此比较耗费性能。所以我们仅在调优、开发时使用。而且全局查询日志信息较少,所以使用较少。
使用方法
-- 查看是否开启
show variables like '%general_log%';
-- 开启全局查询日志
SET GLOBAL general_log = 1;
SET GLOBAL log_output='table'; -- 表明我们将SQL存在表里。mysql.general_log表。
SET GLOBAL log_output='file';
SET GLOBAL general_log_file='目录/general.log'; -- 表明将SQL存储在文件里
SET GLOBAL general_log = 1; -- 如果无效就需要再开启一下
锁机制
MyISAM只支持表锁,InnoDB支持表锁和行锁
-- 加锁
lock table 表1 read/write, 表1 read/write...;
-- 释放锁
unlock tables;
-- 查看表加锁情况
show open tables;
读锁(共享锁)
会话1 对 A 表加 读 锁
- 会话1:可以读取A表数据,但不能修改。不可以读写其它表数据。
- 其它会话:可以读取A表数据,如果修改的话必须要等会话1将读锁释放。可以读写其它表数据。
| | 会话1 | 其他会话 | | ------ | ---------------- | --------------------------------------- | | A表 | 可以读,不可以写 | 可以读取,想要更新必须等待会话1释放表锁 | | 其他表 | 不可以读写 | 可以读写 |
写锁(互斥锁)
会话1 对 A 表加 写 锁
- 会话1:可以对A表进行增删改查,但是不能操作其他表。
- 其他会话:想要读写A表,必须等会话1释放写锁,可以正常操作其他表。
关闭自动提交
-- 以下三种方式都可以
set autocommit=0;
start transaction;
begin;
主从复制
集群和分布式:用板砖来比喻,一开始一个人负责搬砖和运砖,后来工头嫌他慢,又找了几个人帮他一起搬砖运砖,这叫集群(多个服务器支持一个项目)。后来工头还不满足,就让一群人专门负责搬砖,另一群人专门负责运砖,这叫分布式(一个项目划分为几个部分)。
主从复制大概过程:
![1aaea97814367432f52ad375ec1e2a03.png](https://i-blog.csdnimg.cn/blog_migrate/0e0ae557ced71ddd9bc5512501a6bf48.jpeg)