MySQL基础

目录

• 存储引擎
  • 体系结构
  • 存储引擎
• 索引
  • 分类
  • SQL性能分析
    • 查询操作频次
    • 慢查询日志
    • profile详情
    • explain执行计划
  • 使用
  • 索引设计原则
• SQL优化【加索引】
  • 插入数据
  • 主键优化
  • order by优化
  • group by优化
  • limit 优化
  • count优化
  • updata优化
• 锁
  • 全局锁
  • 表级锁
    • 表锁
    • 元数据锁（meta data lock, MDL）
    • 意向锁
  • 行级锁
    • 行锁
• 日志
  • 错误日志
  • 二进制日志
  • 查询日志
  • 慢查询日志

存储引擎

体系结构

存储引擎

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

存储引擎	文件
innodb	xxx.idb
myisam	xxx.sdi（表结构），xxx.MYD（数据），xxx.MYI（索引）
memory	xxx.sdi

索引

分类

• 按字段特性分

  

• 按物理存储结构分

  

  回表查询

  

SQL性能分析

查询操作频次

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

慢查询日志

-- 查看是否开启
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执行计划

字段名	含义
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';

3. 索引列上运算，索引失效

4. 字符串不加引号，索引失效

5. 后模糊走索引，前模糊不走索引

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

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

8. 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 = "软件工程';

9. 索引覆盖，避免回表查询 （select xxx）

10. 前缀索引

-- 截取多长合适：计算选择性来权衡
select count(distinct substring(email,1,5) / count(*) from tb_user ;

11. 尽量使用联合索引（而非单列索引），进行索引覆盖，减少回表查询

索引设计原则

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';

主键优化

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

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优化

updata优化

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

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

锁

全局锁

全库的数据备份

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

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

-- 释放锁
unlock tables；

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

表级锁

表锁

共享读锁，独占写锁

元数据锁（meta data lock, MDL）

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

意向锁

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

1. 意向共享锁（IS）：与表锁共享锁（read）兼容，与表锁写锁（write）互斥。
2. 意向排他锁（Ix）：与表锁共享锁（read）及写锁（write）都互斥。意向锁之间不会互斥。

行级锁

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

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

行锁

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

1. 索引上的等值查询（唯一索引）

   1. 对已存在的记录，优化为行锁。
   2. 不存在的记录，优化为间隙锁。

2. 索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，优化为间隙锁

   

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

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

日志

错误日志

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

二进制日志

记录DDL和DML语句

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

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

日志格式	含义
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