Loading

MySQL基础

存储引擎

体系结构

image-20240720180752622

存储引擎

  • 增删改查,索引的实现方式
  • 基于表的,不是基于库的

image-20240721015007503

存储引擎 文件
innodb xxx.idb
myisam xxx.sdi(表结构),xxx.MYD(数据),xxx.MYI(索引)
memory xxx.sdi

image-20240721015326780

索引

分类

  • 按字段特性分

    image-20240721021731310

  • 按物理存储结构分

    image-20240721021817727

    回表查询

    image-20240721021851435

SQL性能分析

查询操作频次

SHOW GLOBAL STATUS LIKE "Com_______"; (7个下划线)

image-20240721022755594

慢查询日志

-- 查看是否开启
SHOW VARIABLES LIKE "slow_query_log";

-- 配置文件中
slow_query_log = 1 --开启慢查询日志
slow_query_log_file = /var/lib/mysql --慢查询日志的存放路径
long_query_time = 2 --超过两秒的雨具会被记录

profile详情

-- 是否支持
SELECT @@profiling
-- 是否开启
SELECT @@have_profiling;
-- 开启profile
SET profile = 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执行计划

image-20240721230120829

字段名 含义
id id相同,执行顺序从上到下;id不同,值越大,越先执行
type NULL、system、const、eq_ref、ref、range、index、all
key 使用的索引

使用

  1. 最左前缀法则:如果跳跃某一列,索引将部分失效(后面的字段索引失效)。【联合索引】
  2. 出现范围查询(>,<),范围查询右侧的列索引失效。【联合索引】
explain select * from tb_user where profession='软件工程' and age >30 and status ='0';

explain select * from tb_user where profession='软件工程'and age >=30 and status='O';
  1. 索引列上运算,索引失效

  2. 字符串不加引号,索引失效

  3. 后模糊走索引,前模糊不走索引

  4. or条件两侧都有索引才会走索引,否则索引失效

  5. 数据分布影响,如果mysql评估走索引更慢,就不走索引

  6. SQL提示

-- use index() 建议使用索引
explain select * from tb_user use index(idx_user_pro) where profession ='软件工程';

-- ignore index() 忽略某个索引
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

-- force index() 强制使用索引
explain select * from tb_user force index(idx_user_pro) where profession = "软件工程';
  1. 索引覆盖,避免回表查询 (select xxx)

image-20240722004752099

  1. 前缀索引
-- 截取多长合适:计算选择性来权衡
select count(distinct substring(email,1,5) / count(*) from tb_user ;
  1. 尽量使用联合索引(而非单列索引),进行索引覆盖,减少回表查询

索引设计原则

  1. 针对于数据量较大,且查询比较频繁的建立索引。

  2. 针对于常作为查询条件(where)、排序(orderby)、分组(groupby)操作的字段建立索引。

  3. 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。

  4. 如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。

  5. 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。

  6. 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。

  7. 如果索引列不能存储NULL值,请在创建表时使用NOTNULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。

SQL优化【加索引】

插入数据

  1. 批量插入
  2. 手动提交事务
  3. 主键数据插入
  4. 大批量查数据:load指令
-- 客户端连接服务端时,加上参数 --Local-infile
-- 设置全局参数localinfile为1,开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 执行load指令将准备好的数据,加载到表结构中
load data local infile '/root/sqll.log' into table 'tb_user fields terminated by', lines terminated by "\n';

image-20240722094543809

主键优化

  1. 满足业务需求的情况下,尽量降低主键的长度。【减少占用】
  2. 插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键。【减少数据页的分裂与合并】

image-20240722095344977

image-20240722095406437

order by优化

  1. Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sortbuffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
  2. Using index:通过有序索引l顺序扫描直接返回有序数据【覆盖索引】,这种情况即为using index,不需要额外排序,操作效率高。

group by优化

满足最左前缀法则即可

limit 优化

覆盖索引+子查询

-- 全表扫描
select * from tb_sku limit 5000000,10;

-- 子查询走索引
select s.* from tb_sku s,(select id from tb sku order by id limit 9000000,10) a where s.id = a.id;

count优化

image-20240722103654142

updata优化

根据索引字段进行更新,否则行锁升级为表锁

InnoDB的行锁是针对索引加的锁,不是针对记录加的锁。

全局锁

全库的数据备份

-- 加全局锁
flush tables with read lock;

-- 导出数据库
mysqldump -uroot -p1234 itcast > itcast.sql;

-- 释放锁
unlock tables;

-- innodb快照读实现备份,不加锁
mysqldump --single-transaction -uroot -p123456 itcast > itcast.sql

表级锁

表锁

共享读锁,独占写锁

image-20240722130105039

元数据锁(meta data lock, MDL)

避免在操作数据时对表结构(元数据)进行修改。

image-20240722132020119

意向锁

提高行锁表锁冲突时的判断速度。

  1. 意向共享锁(IS):与表锁共享锁(read)兼容,与表锁写锁(write)互斥。
  2. 意向排他锁(Ix):与表锁共享锁(read)及写锁(write)都互斥。意向锁之间不会互斥。

image-20240722135218825

行级锁

InnoDB的数据是基于索引组织的,行锁是通过对索引上的索引项加锁来实现的,而不是对记录加的锁

  1. 行锁(RecordLock):锁定单个行记录的锁,防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
  2. 间隙锁(GapLock):锁定索引记录间隙(不含该记录),确保索引记录间隙不变,防止其他事务在这个间隙进行insert,产生幻读。在RR隔离级别下都支持。
  3. 临键锁(Next-KeyLock):行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙Gap。在RR隔离级别下支持。

image-20240722135817994

行锁

image-20240722140836539

默认情况下,InnoDB在 REPEATABLE READ 事务隔离级别运行,InnoDB使用临键锁进行搜索和索引扫描,以防止幻读

  1. 索引上的等值查询(唯一索引)

    1. 对已存在的记录,优化为行锁
    2. 不存在的记录,优化为间隙锁
  2. 索引上的等值查询(普通索引),向右遍历时最后一个值不满足查询需求时,优化为间隙锁

    image-20240722144859527

  3. 索引上的范围查询(唯一索引),会锁到不满足条件的第一个值为止。

  4. InnoDB的行锁是针对于 索引 加的锁,不通过索引条件检索数据,那么InnoDB将对表中的所有记录加锁,此时就会升级为表锁

日志

错误日志

-- 查看相关配置
SHOW VARIABLES LIKE "%log_error%";

image-20240722173202132

二进制日志

记录DDLDML语句

-- 查看相关配置
SHOW VARIABLES LIKE "%log_bin%";

-- 查看日志格式
SHOW VARIABLES LIKE "%binlog_format";

image-20240722174922438

日志格式 含义
STATEMENT 基于SQL语句的日志记录,记录的是SQL语句,对数据进行修改的SQL都会记录在日志文件中。
ROW 基于行的日志记录,记录的是每一行的数据变更。(默认)
MIXED 混合了STATEMENT和ROW两种格式,默认采用STATEMENT,在某些特殊情况下会自动切换为ROW进行记录。
mysqlbinlog [参数选项] logfilename

-d : 指定数据库名称,只列出指定的数据库相关操作。
-o : 忽略掉日志中的前n行命令
-v : 将行事件(数据变更)重构为SQL语句
-vv : 将行事件(数据变更)重构为SQL语句,并输出注释信息

清理二进制日志

-- 删除全部binlog日志,删除之后,日志编号,将从 binlog.000001重新开始
reset master;

-- 删除******编号之前的所有日志
purge master logs to 'binlog.***';

-- 删除日志为"y-mm-dd hh24:mi:ss"之前产生的所有日志
purge master logs before 'yyyy-mm-dd hh24:mi:ss';

-- 配置过期时间
SHOW VARIABLES LIKE '%binlog_expire_logs_seconds%';

查询日志

-- 查看相关配置
SHOW VARIABLES LIKE "%general%";

慢查询日志

-- 慢查询日志
slow_query_log=1

-- 执行时间参数
long_query_time=2

-- 记录执行较慢的管理语句
log_slow_admin_statements =1

-- 记录执行较慢的未使用索引的语句
log_queries_not_using_indexes = 1
posted @ 2024-09-08 19:43  咪啪魔女  阅读(4)  评论(0编辑  收藏  举报