mysql

事务的四大特性

ACID

原子性(atomicty) 事务包含的所有操作要么全部成功，要么全部失败回滚.

原子性不能保证一致性, 因为即使每个事务都符合原子性, 但是整体结果不能满足一致性(比如,事务2可能覆盖事务1的结果, 比如给A转账100元的事物,B也转入100元, 理论上是需要转入200元, 转入100元就满足原子性, 但是不满足一致性.)

一致性（consistency)

一致性是最基本的属性，其它的三个属性都为了保证一致性而存在的. 更趋向于业务相关.(比如转账, 帐户A转一笔钱到帐户B上，如果帐户A上的钱减少了，而帐户B上的钱却没有增加，那么我们认为此时数据处于不一致的状态.)

隔离性（isolation）保证并发情况下的一致性，引入了隔离性.

实现就是两种锁, 悲观-每个操作的前后都加锁(保证并发情况下的一致性，引入了隔离性)
乐观锁, 不同的事务可以同时看到同一对象（一般是数据行）的不同历史版本(通过日志UNDO的方式来获取数据行的历史版本;内存中保存同一数据行的多个历史版本，通过时间戳来区分)

持久性（durability）数据库中的数据的改变就是永久性的

三大范示

1NF 数据库表字段的原子性, 不可拆分
2NF 1NF的基础上, 非主键必须依赖完整主键, 而不是主键的一部分
3NF 2NF的基础上, 非主键必须直接依赖主键, 不能存在传递依赖

事务隔离级别

不考虑隔离性的时候，可能导致脏读、幻读和不可重复读的问题

脏读: 读到还没提交的
幻读: 一个事务内的读取操作的结论不能支撑之后业务的执行
不可重复读: 一个事务期间读的两次结果不同

MySQL数据库为我们提供的四种隔离级别：

Serializable (串行化)
Repeatable read (可重复读), 解决事务并发不可重复读问题, Mysql默认
Read committed (读已提交), 只能读取已经提交的, 解决了脏读
Read uncommitted (读未提交),

mysql 查看和设置隔离级别

select @@transaction_isolation;
set session transaction isolation level read uncommitted;

生产环境的隔离级别
** RC(Read Commited)**

在RR隔离级别下，存在间隙锁，导致出现死锁的几率比RC大的多; 在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行.
结论是RC的并发性高于RR

utf8和utf8mb4

utf8mb4_general_ci ,挨个字符进行比较（general），并且不区分大小写（_ci，case insensitice）
utf8mb4_bin, 比较二进制

索引

索引是存储引擎用于提高数据库表的访问速度的一种数据结构。

优缺点:

优点:加快数据查找的速度; 加快分组和排序的速度,加快表与表之间的连接
缺点:占用物理空间; 动态维护索引,增删改时间变长.

不需要索引的场景:

where用不到
表记录较少
经常增删改查需要评估
参与列计算
区分度不高的字段不适合建立索引，如性别，只有男/女/未知三个值。加了索引，查询效率也不会提高。

索引的数据结构:

InnoDB引擎的索引类型有B+树索引和哈希索引，默认的索引类型为B+树索引

B+数
二分法
B+树的节点只存储索引key值，具体信息的地址存在于叶子节点的地址中,相邻的叶子节点之间是通过链表指针连起来的;而B+树则需要通过索引找到叶子结点中的数据才结束, 而B+树关键字可以出现多次。

Hash索引
基本用于精确查找, 由于hash的特点, 不支持排序,不支持范围查找, 不支持模糊查询, 会存在hash冲突.

索引的分类

主键索引;唯一索引(只有null可以多值);组合索引;全文索引(类char可用);普通索引

索引的最左匹配

如果用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。当遇到范围查询(>、<、between、like)就会停止匹配

聚集索引

InnoDB使用表的主键构造主键索引树，同时叶子节点中存放的即为整张表的记录数据。聚集索引叶子节点的存储是逻辑上连续的，使用双向链表连接，叶子节点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快。

覆盖索引

select的数据列只用从索引中就能够取得，不需要回表进行二次查询，也就是说查询列要被所使用的索引覆盖

# 在查询前面使用explain，输出的extra列会显示为using index。
explain select blog_id from user_like where user_id = 13;
# explain结果的Extra列为Using where; Using index

设计原则

区分度越高越值得用索引
尽量使用短索引
索引不是越多越好
利用最左前缀原则

索引实效

%开头的like查询如%abc; 查询条件中列类型是字符串，没有使用引号(类型转化导致); 索引列!=; 索引列计算;查询条件使用or连接

前缀索引

// email列创建前缀索引
ALTER TABLE table_name ADD KEY(column_name(prefix_length));

索引下推

索引条件下推，也叫索引下推，英文全称Index Condition Pushdown，简称ICP。
存储部分做部分筛选工作.

MyISAM和InnoDB的区别

是否支持行级锁 : MyISAM 只有表级锁，而InnoDB 支持行级锁和表级锁，默认为行级锁。
是否支持事务和崩溃后的安全恢复： MyISAM 不提供事务支持。而InnoDB提供事务支持，具有事务、回滚和崩溃修复能力。
是否支持外键：MyISAM不支持，而InnoDB支持。
是否支持MVCC ：MyISAM不支持，InnoDB支持。应对高并发事务，MVCC比单纯的加锁更高效。
MyISAM不支持聚集索引，InnoDB支持聚集索引。

MVCC

MVCC(Multiversion concurrency control) 就是同一份数据保留多版本的一种方式，进而实现并发控制。在查询的时候，通过read view和版本链找到对应版本的数据。

快照读和当前读

表记录有两种读取方式。

快照读：读取的是快照版本。普通的SELECT就是快照读。通过mvcc来进行并发控制的，不用加锁。
当前读：读取的是最新版本。UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。

在快照读情况下，MySQL通过mvcc来避免幻读。
在当前读情况下，MySQL通过next-key来避免幻读（加行锁和间隙锁来实现的）。

InnoDB通过mvcc机制避免了幻读现象。而mvcc机制无法避免当前读情况下出现的幻读现象。

共享锁和排他锁

select * from table where id<6 lock in share mode;--共享锁
select * from table where id<6 for update;--排他锁

# bin /redo /undo log
bin 数据库级别. 
redo log是innodb引擎级别,记录innodb存储引擎的事务日志,innodb_flush_log_at_tx_commit设置为1,执行commit时会将redo log同步写到磁盘.
undo log
undo log用于数据的撤回操作，它保留了记录修改前的内容。

Mysql架构

Server 层: 连接器, 查询缓存, 分析器,优化器, 执行器
存储引擎

分库分表

垂直划分:
比如, 10列分成4列和6列
业务进行划分
水平划分

缺点: 分片事务一致性难以解决; 跨节点join性能差，逻辑复杂;数据分片在扩容时需要迁移

分区表

1张表的数据分成N多个区块,分区一般都是放在单机里的，用的比较多的是时间范围分区，方便归档。只不过分库分表需要代码实现，分区则是mysql内部实现。
range分区
list分区
hash分区

processlist

show processlist 或 show full processlist 可以查看当前 MySQL 是否有压力，正在运行的SQL，有没有慢SQL正在执行。返回参数如下：

id：线程ID，可以用kill id杀死某个线程
db：数据库名称
user：数据库用户
host：数据库实例的IP
command：当前执行的命令，比如Sleep，Query，Connect等
time：消耗时间，单位秒
state：执行状态，主要有以下状态：
Sleep，线程正在等待客户端发送新的请求
Locked，线程正在等待锁
Sending data，正在处理SELECT查询的记录，同时把结果发送给客户端
Kill，正在执行kill语句，杀死指定线程
Connect，一个从节点连上了主节点
Quit，线程正在退出
Sorting for group，正在为GROUP BY做排序
Sorting for order，正在为ORDER BY做排序


info：正在执行的SQL语句

其他细节

查询语句执行流程: 权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎
更新语句执行过程: 分析器、权限校验、执行器、引擎、redo log（prepare状态）、binlog、redo log（commit状态）
exist和in的区别, exists用于对外表记录做筛选, 循环外表记录。in是循环内表.
int(10)和char(10) 显示数据的长度，而char(10)表示的是存储数据的长度.
深分页怎么优化
limit 500000, 10 会取出500010数据丢弃500000条

select * from xxx order by id limit 500000, 10;
使用下面的
select * from xxx  where id >=(select id from xxx order by id limit 500000, 1) order by id limit 10;
select * from xxx  where id >=500000 order by id limit 10;

高度3的b+数, 可以存2千万条数据, 所以在 InnoDB 中B+树高度一般为3层时，就能满足千万级的数据存储。

posted on 2022-08-01 14:45 WillingCPP 阅读(24) 评论(0) 收藏举报

刷新页面返回顶部