MySQL基础
存储引擎
体系结构
存储引擎
- 增删改查,索引的实现方式
- 基于表的,不是基于库的
存储引擎 | 文件 |
---|---|
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 | 使用的索引 |
使用
- 最左前缀法则:如果跳跃某一列,索引将部分失效(后面的字段索引失效)。【联合索引】
- 出现范围查询(>,<),范围查询右侧的列索引失效。【联合索引】
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';
-
索引列上运算,索引失效
-
字符串不加引号,索引失效
-
后模糊走索引,前模糊不走索引
-
or条件两侧都有索引才会走索引,否则索引失效
-
数据分布影响,如果mysql评估走索引更慢,就不走索引
-
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 = "软件工程';
- 索引覆盖,避免回表查询 (select xxx)
- 前缀索引
-- 截取多长合适:计算选择性来权衡
select count(distinct substring(email,1,5) / count(*) from tb_user ;
- 尽量使用联合索引(而非单列索引),进行索引覆盖,减少回表查询
索引设计原则
-
针对于数据量较大,且查询比较频繁的表建立索引。
-
针对于常作为查询条件(where)、排序(orderby)、分组(groupby)操作的字段建立索引。
-
尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。
-
如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。
-
尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。
-
要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
-
如果索引列不能存储NULL值,请在创建表时使用NOTNULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。
SQL优化【加索引】
插入数据
- 批量插入
- 手动提交事务
- 主键数据插入
- 大批量查数据: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';
主键优化
- 满足业务需求的情况下,尽量降低主键的长度。【减少占用】
- 插入数据时,尽量选择顺序插入,选择使用
AUTO_INCREMENT
自增主键。【减少数据页的分裂与合并】
order by优化
Using filesort
:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sortbuffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫FileSort排序。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)
避免在操作数据时对表结构(元数据)进行修改。
意向锁
提高行锁表锁冲突时的判断速度。
- 意向共享锁(IS):与表锁共享锁(read)兼容,与表锁写锁(write)互斥。
- 意向排他锁(Ix):与表锁共享锁(read)及写锁(write)都互斥。意向锁之间不会互斥。
行级锁
InnoDB的数据是基于索引组织的,行锁是通过对索引上的索引项加锁来实现的,而不是对记录加的锁
- 行锁(RecordLock):锁定单个行记录的锁,防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
- 间隙锁(GapLock):锁定索引记录间隙(不含该记录),确保索引记录间隙不变,防止其他事务在这个间隙进行insert,产生幻读。在RR隔离级别下都支持。
- 临键锁(Next-KeyLock):行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙Gap。在RR隔离级别下支持。
行锁
默认情况下,InnoDB在 REPEATABLE READ 事务隔离级别运行,InnoDB使用临键锁进行搜索和索引扫描,以防止幻读。
-
索引上的等值查询(唯一索引)
- 对已存在的记录,优化为行锁。
- 不存在的记录,优化为间隙锁。
-
索引上的等值查询(普通索引),向右遍历时最后一个值不满足查询需求时,优化为间隙锁
-
索引上的范围查询(唯一索引),会锁到不满足条件的第一个值为止。
-
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