MySQL学习笔记

MySQL

---

基础篇

---

数据库DataBase（DB）、SQL（Structure Query Language）

MySQL客户端连接：①MySQL自带的客户端命令行 ②mysql [-h 127.0.01] [-P 端口号] -u root -p

score double(4,1) (精度,标度)

BLOB (binary large object)二进制大对象，是一个可以存储二进制文件的容器。在计算机中，BLOB常常是数据库中用来存储二进制文件的字段类型。BLOB是一个大文件，典型的BLOB是一张图片或一个声音文件，由于它们的尺寸，必须使用特殊的方式来处理。

定长字符串CHAR 变长字符串VARCHAR

truncate table 删除指定表，并重新创建该表

DQL

select * from 表名；模糊查找like（占位符_ 匹配单个字符，%匹配任意个字符）

ascend、descend 升序、降序

多表查询

select distinct d.id , d.name from emp e , dept d where e.dept_id = d.id; distinct extinct

事务

start transaction 或 begin；

回滚事务 rollback；

并发事务问题：

1）脏读：一个事务读到另外一个事务还没有提交的数据。

2）不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同。

3）幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 "幻影"。

“ACID”

进阶篇

---

面试题：InnoDB和MyISAM区别？

事务、外键、行级锁

索引

UNIQUE FULLTEXT ??

• 最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始（最左列存在即可，与位置无关），并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

联合索引中，出现范围查询（>,<），范围查询右侧的列索引失效 >=可以

• 索引失效的情况

1、不要在索引列上进行运算操作， 索引将失效。

2、字符串类型字段使用时，不加引号，索引将失效。

3、如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

4、用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

5、如果MySQL评估使用索引比全表更慢，则不使用索引。

尽量使用覆盖索引，减少select *。覆盖索引是指 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。（尽量不回表查询）

问题：这里不满足最左前缀为什么还会用到索引呢？

bilibili弹幕：

对于条件子句按最左法则判断；对于select字段，按照索引覆盖判断。两个方法满足一个就能走索引

​

只要是满足覆盖法则的都要用到索引，只要是排序的都要用到最左前缀法则自己多测试就明白了

​

首先看type，all是扫描全表，index是扫描整个索引树，这里用到索引覆盖，所以只需扫描索引树；其次分组条件没满足最左前缀，所以用到了临时表

SQL优化

• limit优化

一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

explain select * from tb_sku t , (select id from tb_sku order by id

limit 2000000,10) a wheret.id = a.id;

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。

@@global/session变量 @用户在当前会话自定义变量

触发器

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。

现在触发器还只支持行级触发，不支持语句级触发。

锁

全局锁

表级锁

表锁

元数据锁

meta data lock，MDL，MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。

alter table ... EXCLUSIVE 与其他的MDL都互斥

意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

• 意向共享锁(IS): 由语句select ... lock in share mode添加 。 与 表锁共享锁(read)兼容，与表锁排他锁(write)互斥。

• 意向排他锁(IX): 由insert、update、delete、select...for update添加 。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。

• 一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。

行级锁

• 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC（Read Commited）、RR（Repeatable Read）隔离级别下都支持。

• 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

• 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

---

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。

• 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

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

InnoDB引擎

WAL（Write-Ahead Logging）redo log日志文件是追加 顺序磁盘IO “以redo log buffer刷新到磁盘redo log file”为事务成功的标志而不是实时刷新(随机IO性能低，可以过一段时间刷)

MVCC

1、隐藏字段（DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID） 2、Undo log版本链 3、ReadView

为什么select ... lock in share mode(共享锁)，select ... for update、update、insert、delete(排他锁)都是一种当前读？？个人思考“因为加了锁就不会出现数据再被改变的可能，可以放心展示当前记录的最新版本”

selectobject_type,object_schema,object_name,lock_type,lock_durationfrom

performance_schema.metadata_locks;//来查看数据库中的元数据锁的情况

selectobject_schema,object_name,index_name,lock_type,lock_mode,lock_datafrom

performance_schema.data_locks;//查看意向锁及行锁的加锁情况

Multi-Version Concurrency Control，多版本并发控制

readview

MySQL管理

mysql

mysqladmin

mysqlbinlog

mysqlshow

mysqldump：备份内容包含创建表，及插入表的SQL语句。

-T, --tab=name //自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件，数据文件

mysqlimport/source：import导入txt文件，source导入sql文件

运维篇

---

日志

主从复制

分库分表

读写分离