基础
Mysql存储特点
SQL 执行流程
查询
SQL 语句执行顺序
(8) SELECT
(9) DISTINCT<Select_list>
(1) FROM <LEFT_TABLE>
(3) <join type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <grou_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
(11) LIMIT <limit_number>
Group By
左连接和右连接
比如 A 表为左表,B 表为右表
-
左连接
-
右连接
drop、delete、truncate的区别
-
delete
-
truncate
-
TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存,删除行是不能恢复的。
-
并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
-
当表被 TRUNCATE 后,这个表和索引所占用的空间会恢复到初始大小,
-
drop
即使配置了 binlog_format = row, truncate 和 drop 删除操作在日志中保存还是 statement 格式的,所以 binlog 中只有一句 drop/truncate 语句,没法恢复数据。除非数据库有全量备份。
不同的count用法
首先你要弄清楚count()的语义。count()是一个聚合函数,对于返回的结果集,一行行地判断,如果count函数的参数不是NULL,累计值就加1,否则不加。最后返回累计值。
count(*)
-
MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回这个数,效率很高,但是不支持事务;
-
而 InnoDB 执行count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数,虽然结果准确,但会导致性能问题。
-
show table status 命令虽然返回很快,但是不准确;
count(1)
count(主键id)
count(字段)
为什么InnoDB不跟MyISAM一样,也把数字存起来呢?
MySQL 数据类型
-
tinyint:8位,1字节
-
smallint:16位,2字节
-
mediumint:24位,3字节
-
int:32位,4字节
-
bigint:64位,8字节
-
float:32位,4字节
-
double:64位,8字节
-
char:1~255字节
-
varchar:L+1 字节, 在此 L <= M 和 1 <= M <= 255
-
text:L+2 字节, 在此 L < 2^16
普通读和快照读
存储引擎
1.MyISAM
2.InnoDB
(1) MyISAM 和 InnoDB 的区别
存储引擎:是表级别的,形容数据表
a. 存储文件的区别
b. 索引的区别
c. 是否支持行级锁
d. 是否支持事务
e. 是否支持 MVCC
f. 是否支持外键
-
MyISAM 不支持外键。
-
InnoDB 支持外键。
g. 是否支持安全恢复
3.Memory
索引
索引是帮助 MySQL 高效获取数据的排好序的数据结构。
1.为什么要索引?
索引是帮助 MySQL 高效获取数据的排好序的数据结构。
2.什么是B+树?
-
B+ 树的非叶子节点,只保存索引,而不保存数据,因此 B+ 树比 B 树更加矮壮。这就意味着,B+ 树检索速度会更快
-
B+ 树叶子节点是一个有序双向链表,遍历查询更方便。
-
B+ 树的一个节点占有一页,一页大概是16k。每次查询,把一页加载到内存中去查询(比如二分查询)。如下图,就是一个节点(一页):
h=3 的 B+ 树差不多能放2千万的数据。
B+ 树查询过程
为什么不用二叉搜索树?
为什么不用红黑树?
红黑树就是二叉平衡树。左右子树高度差不超过1。
b 树比红黑树强的地方
-
红黑树是一种"二叉搜索树",每个node节点只能保存一对key,value
-
B 或 B+ 树是一种”多路搜索树“,每个node节点可以保存多个数据
-
因此,相比较而言,B 或者 B+ 树比红黑树高度更低,高度更低就意味着检索速度更快。
为什么不用 Hash 表
-
只能满足 =, IN 的查询,不支持范围查询
比如查询 col > 10
-
hash 有冲突问题
为什么不用B树?
B 树
3.为什么建议 innoDB 表必须建主键,并且是auto_increment
什么是回表
回表会基于非主键索引的查询后回到主键索引树搜索的过程。即当通过非主键索引找到索引列值以外的字段时,就会回表。
比如:
create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k)) engine=InnoDB;
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
此时执行 select * from T where k between 3 and 5
:
-
先在 k 索引树找到 k = 3 的数据记录(一个数据页,保存若干id数据),找到 id = 300
-
然后去主键索引树,找到 id = 300 的数据记录
-
这个过程就是在回表
原因:
-
因为非主键索引建立的B+树叶子节点的数据表保存的是索引列值和主键。(聚簇索引保存的是主键和其他所有列值)
-
所以,如果要查询索引值以外的值时,先要通过非主键索引找到相应的主键值,再通过主键值去聚簇索引B+树找到相应的数据行,再读取出要查询的数据。
-
比如,MySQL采用非主键索引name来作为索引,那么底层B+树存放的是name列值和主键id。如果此时用一下sql查询
select * from student where name = "James"
那么MySQL只能进行查询到name列值和相应的id,而其他的列值就必须通过这个id,再去聚簇索引保存的B+树,找到相应的数据并读取。这就是回表。
怎样避免回表
通过覆盖索引的方式。
覆盖索引
将被查询的字段,建立到联合索引里去
场景1:全表count查询优化
原表为: user(id, name, sex);
直接: select count(name) from user; 不能利用索引覆盖。
添加索引: alter table user add key(name); 就能够利用索引覆盖提效。
什么是联合索引?
最左前缀原则/最左匹配原则
总结:对于(a, b, c) 这样的联合索引, (a, c), (a, b), (a) 这些查询方式,根据最左匹配原则,都会从(a, b, c) 的联合索引树去查询数据。因为叶子节点是先根据 a 来进行排序的,然后根据 b,再根据c。而遇到范围查询,如 between,in,>,<,则会停止匹配。比如,where a = 1 and b > 2 and c = 3,在联合索引树中,先找到 a = 1,b > 2 的所有叶子节点,根据 id 去主键索引树查询完整的记录。
B+树这种索引结构,可以利用索引的“最左前缀”,来定位记录。
(I) 比如上图索引为 index(name, age)
-
比如
-
查询条件为 where name = '张三',则找到 ("张三", 10), id=4 的记录,然后往后遍历所有满足条件的值
-
查询条件为 where name like '张%',则找到 ("张六", 30), id=3 的记录,然后往后遍历所有满足条件的值
-
所以,只要满足最左前缀,就可以利用索引来加速查找。
因此,建立索引时,要考虑索引顺序。比如,居民身份信息索引就只需建立 (card_id, name)
和 (name)
就行了,不能再单独建立一个 (card_id)
(II) 在最左匹配原则中,有如下说明:
-
最左前缀匹配原则,非常重要的原则,mysql会根据联合索引一直向右匹配直到遇到范围查询 (>、<、between、like) 就停止匹配
-
比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
-
因为B+树的叶子节点中,数据页和数据页是按照联合索引第一个值来排序的,然后才是第二个,然后才是第三个。
-
= 和 in 可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成查询效率最高的形式,也就是匹配联合索引的形式。
-
匹配最左边的列
-
比如联合索引是(a1, a2, a3)
-
那么(a1), (a1, a2),(a1, a3) 都会触启用联合索引(a1, a2, a3)的查询,到联合索引(a1, a2, a3)树中去查询。(a2, a1)等也行
-
而(a2),(a2,a3)等都不会触发联合索引(a1, a2, a3)的查询。
-
.匹配列前缀
select * from staffs where id like 'A%';//前缀都是排好序的,使用的都是联合索引
select * from staffs where id like '%A%';//全表查询
select * from staffs where id like '%A';//全表查询
-
遇到范围
by the way, 联合索引最多只能包含16列
索引下推
当有联合索引 (name, age) 时,如果我们执行以下语句:
select * from user_info where name="王%" and age=20 and ismale=1;
聚簇/非聚簇索引
特点:
-
使用主键值的大小进行记录和页的排序。
-
页内的记录按照主键大小排成单向链表
-
各个数据页之间按照主键大小顺序排成双向链表
-
MySQL一个表只有一个聚簇索引。
-
如果没有定义主键。
缺点:
-
插入速度严重依赖于插入顺序。所以最好自增ID为主键, 否则插入会带来B+树的分裂。
-
更新主键代价很高,所以最好主键为不可更新。
MySQL 怎么创建索引?
三种方式
-
CREATE INDEX <index_name> ON TABLE <table_name> (<column_name>)
-
ALTER TABLE <table_name> ADD INDEX <index_name>(<column_name>);
-
CREATE TABLE tableName(
id INT NOT NULL,
columnName columnType,
INDEX [indexName] (columnName(length))
);
索引原则
适合创建索引
-
频繁被查询的数据。
-
不为 NULL 的数据。
-
频繁作为 WHERE 条件的字段。
-
频繁需要 ORDER BY 的字段(因为索引叶子节点已经有序)。
-
频繁用于表连接的字段。
尚硅谷版本:
-
字段的数值有唯一性的限制。业务上具有唯一特性的字段,即使是组合字段,页必须建成唯一索引。
-
频繁被 WHERE 查询条件的字段。
-
针对GROUP BY,ORDER BY的2个字段,需要建立联合索引且GROUP BY字段在前, ORDER BY字段在后。
-
UPDATE、DELETE 的 WHERE 条件列
-
DISTINCT 字段需要创建索引。
-
多表 JOIN 连接操作时,创建索引注意事项
-
连接表的数量不要超过3张
-
对 WHERE 条件创建索引
-
对用于连接的字段创建索引
-
使用字符串前缀对varchar创建索引
#对于varchar上创建索引,要指定索引长度,即查询前一部分,但是导致的问题就是排序不准确
create table shop(addrass varchar(120) not null);
#指定字符串索引前缀部门
alter table shop add index(addrass(12));
#查看区分度,越高越好
select count(distinct addrass) / count(*) from shop;
-
索引尽量不要超过6个。索引太多会占用磁盘空间,CUD需要维护索引。优化器也要进行多次选择。
不适合创建索引
-
频繁更新的字段,因为维护索引的代价很高。
-
不建议对无序字段创建索引。(主键是 id 的话,尽量自增)。
-
不要创建冗余索引。比如已经创建了 (name, age) 的索引,那么不需要再创建 (name) 索引了,因为根据最左匹配原则,(name) 索引也可以走 (name, age) 索引。
InnoDB事务
1. 什么是事务?
事务是一组操作,这一组操作要么同时成功,要么同时失败。
2. 事务的特性
ACID
-
Atomicity:原子性
-
Consistency:一致性
-
Isolation:隔离性
-
Duration:持久性
原子性
事务的操作要么成功,要么失败。
一致性
一致性是事务的目的。我们对数据库操作,就是要保证数据的一致性。一旦事务操作失败,就应该回滚到原先的数据。
隔离性
事务与事务之间是隔离的,互不影响的。如果多个事务同时操作一个数据,可能会出现脏读、不可重复读和幻读的问题。
持久性
事务一旦 commit,那么对数据库的改变应该是永久的。就是说,数据应该被持久化在硬盘上。
3. 事务的使用
START TRANSACTION;
...
SQL
...
COMMIT;
MySQL的锁
从锁的粒度来分,锁分为表锁和行锁。表锁锁的是整张表,或者说,锁的是索引树。行锁锁的是索引。行锁又分为读锁(共享锁、S 锁)、写锁(排他锁、X 锁)。读锁是共享的,它允许多个事务同时读取一个资源。写锁是排他的,写锁会阻塞其他的写锁和读锁。
1.全局锁
2.表级锁
有哪些表级锁?
(1) 表锁
(2) 元数据锁 MDL
MDL不需要显式使用
元数据锁的问题:
解决:
表锁锁的是什么?
表锁的是整张表
3.行锁
(1) 行锁是什么?
(2) 什么是两阶段锁?
a. 两阶段锁
两阶段锁——将事务的获取锁和释放锁分为增长和缩减两个阶段
-
增长阶段:事务可以获得锁,但不能释放锁
-
缩减阶段:事务只可以释放锁,不可以获得锁
b. 严格两阶段锁
c. 好处
在 InnoDB 中,行锁是在需要的时候才加上的,但是只会在事务提交以后才会释放。
如果事务中需要锁多行,那要把最可能造成锁冲突和最可能影响并发度的锁放后面。
(3) 行锁锁的是什么?
-
行锁锁的是索引。
(4) 有哪几类行锁?
-
记录锁:单个行记录的锁
-
间隙锁:锁住一个范围,不包含记录本身
-
临键锁:Next-key lock,锁住一个范围 + 记录本身(用于解决 MySQL REPEATABLE READ 隔离级别的幻读问题)
(5) 怎么减少行锁对性能的影响?
4.死锁
发生死锁后的策略:
-
超时等待:设置超时时间,超时后自动退出
-
主动死锁检测:判断是否有死锁链产生。从事务A到申请的资源持有者事务B,再一路判断下去,如果最终形成了一个环,说明有死锁。
产生死锁的四个必要条件:
-
不可剥夺条件:线程占有的资源不会被其他线程抢占。
-
请求和等待条件:线程占有一部分资源后,又去申请其他的资源。而申请的资源被其他的线程占有,因此等待其他线程释放资源。
-
互斥条件:线程占有的资源不可被其他的线程访问,其他线程想要获得该资源,必须等待线程释放该资源。
-
循环等待条件:产生死锁循环链。
5.共享锁/读锁 Slock
6.排他锁/写锁 Xlock
-
事务在进行写操作时,不允许其他事务进行读或者修改
-
加锁:
-
释放锁:commit
、rollback
7.意向锁
日志
总结:
-
binlog,归档日志,为了数据库的数据一致性。用于数据备份和主从数据同步。(事务开始 —> 写 binlog cache —> 事务结束 —> binlog cache 到磁盘)
-
redo log,重做日志,为了数据库崩溃后的数据恢复。(事务开始 —> 写 redo log buffer —> redo log buffer 到磁盘 —> 事务结束 )
-
undo log,回滚日志,用于事务回滚,保证事务的原子性。
1.两阶段提交
2.binlog(归档日志)
Binary log,二进制逻辑日志。binlog 是二进制逻辑日志,server 层持有,所有存储引擎都能用,它采用追加写的形式,记录语句的逻辑操作,主要用于数据备份和主从复制中的主从同步。
-
binlog 是 server 层定义的,所有引擎都可以使用。
-
binlog 是一种逻辑日志,记录的是语句的原始逻辑,类似于“给 ID=2 这一行的 c 字段加 1”。
-
binlog会记录所有的逻辑操作,只要发生了数据的变更,就会记录 binlog;并且是采用"追加写"的形式。
-
binlog 用于
(1) 三种记录格式
STATEMENT
记录的内容是 SQL
语句原文,每一条修改语句都会被记录到 binlog 中。
比如执行一条update T set update_time=now() where id=1
,记录的内容如下:
-
DML,DDL 语句都会明文显示
-
不支持 RU,RC 的隔离级别
-
可能会导致主从复制数据不一致
比如:
ROW
基于行的复制:记录的内容不再是简单的 SQL
语句了,还包含操作的具体数据:
-
DDL语句明文显示,DML语句加密显示;
-
导致 binlog 文件较大
MIXED
混合模式:MySQL
会判断这条SQL
语句是否可能引起数据不一致,如果是,就用row
格式,否则就用statement
格式。
(2) 写入机制
整体流程:事务开启的时候,将 binlog 写入 binlog cache,然后在事务结束的时候,把 binlog 从 binlog cache 里写入到磁盘中。
具体:
-
系统会为每个线程,分配一个块空间 binlog cache
-
事务开启后,binlog 会被写入到 binlog cache中。
-
事务结束后,binlog 会被写入到文件系统的 page cache 里。
-
最后,再从 page cache 持久化到磁盘上。
3.redo log (重做日志)
redo log,是 InnoDB 引擎独有的物理日志,主要用于记录数据页上的修改,它由四个文件组成,从头开始记录,当写到第四个文件末尾,则回到第一个文件开头继续写,redo log 主要用于 MySQL 崩溃后恢复。
(1) 什么时候写
MySQL 以数据页为单位(16kb),每次查询时,将一个数据页从磁盘加载到缓冲池(Buffer Pool),后续查找都会先从缓冲池里找,找不到再去磁盘加载。
-
更新时,先去缓冲池(Buffer Pool)尝试更新,如果找到要更新的数据,则直接更新,并记录 redo log。
-
否则,从磁盘中加载对应的数据页到缓冲池,然后进行更新,并且记录 redo log。
(2) 写入机制
(3) 日志文件组
4.undo log (回滚日志)
5.Undo log 和 Redo log 有什么区别?
(1) undo log(回滚日志)
(2) redo log(重做日志)
6.binlog 和 Redo log 有什么区别?
-
binlog 是 server 层定义的;
redo log 是 InnoDB 层定义的;
-
binlog 先于 redo log 被记录(两阶段提交)
-
binlog 是逻辑日志;
redo log 是物理日志;
-
binlog 是追加写;
redo log 是循环写,日志大小固定;
数据库并发问题
1.并发问题
-
脏写:事务A修改了事务B未提交的数据。
-
脏读:事务A读取了事务B更新,但未提交的数据。之后事务B对数据库进行 了回滚,那么事务A读到的就是无效的数据。
-
不可重复读:事务A读取的数据,该数据之后被事务B修改并提交。事务A再次读取这个数据时,发生这个数据和之前读到的不一样。
-
幻读:事务 A 读取一张表的一些数据,事务 B 在这个表中插入了几行新的数据并提交。当事务A再次读取的时候,发现多读了一些数据。
2.四种隔离级别
-
READ UNCOMMITTED: 允许事务读取更新了但未提交的数据。脏读、不可重复读、幻读的问题均存在。
-
READ COMMITTED: 允许事务读取已经被其他事务提交了的数据。可以避免脏读,但是不可重复读、幻读问题不可避免。
-
REPETABLE READ: 事务读取一个字段时,不允许其他事务对该字段进行修改。可以避免脏读,不可重复读,但幻读问题不可避免。
-
SERILIZATION: 事务读取一个表时,不允许其他事务对这个表进行操作。可以避免脏读,不可重复读,幻读问题,但是性能低。
(1) READ UNCOMMITTED原理
-
读不加锁,写加锁。会带来脏读问题。脏读问题不可接受。
-
但是如果给读加锁,那么数据库在进行更新时,就不可读了,会带来性能上的问题
-
解决办法:MVCC(多版本并发控制 Multi-Version Concurrency Control)
(2) MySQL 怎么实现的可重复读
锁 + MVCC 实现事务级数据快照,事务只能读取当前事务版本的数据。其他事务修改的数据版本更高,当前事务无法读到。
怎么避免幻读呢?
当前读:比如:select * from table where id > 5 for update
就是一条当前读语句
快照读:执行 select 的时候,生成一个快照。所以看不到对其他事务的操作不感知。
-
当前读的时候,可以加 Next-Key Lock 来避免幻读。next-key lock 是前开后闭区间,而间隙锁是前开后开区间。
-
使用快照读
-
加表锁
(3) SERILIZATION
间隙锁实现的
3.悲观并发控制
加锁。—— 数据库的各种锁
4.乐观并发控制
乐观锁。
(1) 基于时间戳的协议
postgresql 就使用了该协议
(2) 基于验证的协议
-
乐观并发控制其实本质上就是基于验证的协议
-
将事务执行分为三个阶段:
-
读阶段:执行事务所有的读操作和写操作。
-
验证阶段:验证更新是否合法
-
写阶段:
5.多版本并发控制 MVCC
MVCC 如何实现各种隔离级别
MVCC 会生成数据快照(snapshot)
-
READ COMMIT 可以解决脏读的问题,MVCC 通过在数据记录后面加上版本号来实现 READ COMMIT。事务 A 读取记录时,会生成版本号,事务 B 更新数据,会加写锁,然后更新版本号。事务 A 再次读会读最新版本号的数据。
-
REPEATABLE READ 隔离级别是事务级别的数据快照,事务每次只能读当前事务版本的数据。可以解决不可重复读的问题。
-
MVCC 可以使用Next-Key Lock (行锁 + 间隙锁)来解决幻读的问题。
SERILIZABALE READ 是只允许事务串行执行,而不允许并行执行。这样是最安全的,但是也是效率最低的。
前提:大多事务是只读的,而少数事务是写操作。
-
每一个写操作都会创建一个新版本的数据
-
读操作从多个版本的数据中选一个最合适的返回。
当事务提交以后,该类型的 undo log 占用的 undo log segment 就会被系统回收(要么被重用,要么被释放)
MVCC 的原理
① Read View + ② 隐藏字段 + ③ undo log 实现
(1) undo log
(2) 隐藏字段
-
每一行数据有两个隐藏字段:
-
trx_id:最后插入或更新该行的事务 id (就是最新的事务 id)
-
roll_ptr:在 undo log 中的指针。
-
row_id:如果没有设置主键,innoDB 会用该列来生成聚簇索引。
(3) Read View
事务读的时候,用 Read view 来做可见性判断。
Read View 规则:
-
如果 trd_id == Read View 的 creator_trx_id,那么就代表当前版本的行就是当前事务最后修改并提交的,可以直接读。
-
如果 trd_id < Read View 的 up_limit_id,即当前活跃事务中最小的事务 id 都大于行最大的 id,当前事务可以直接读。
-
如果 trd_id >= Read View 的 low_limit_id,即当前版本的行的事务 id 大于等于 Read View 中活跃事务下一次要生成的事务 id,那么当前版本的行不可以被当前事务访问。
-
如果 trd_id 在 Read View 的 up_limit_id 和 low_limit_id 之间,那需要判断 trx_id 是否在活跃列表 trx_ids 中,
流程
-
首先获得事务 id。
-
获取 Read View。
-
查询数据,与 Read View 进行比较。
-
如果不符合 Read View 规则,则从 undo log 获取历史数据。
-
最后返回符合规则的数据。
(1) MySQL 和 MVCC
总结:
-
insert: 创建版本号 ——> 当前系统版本号
-
update: 找到比事务当前版本号小的最大版本号的数据行,创建一行新的数据行,并且更新数据,然后把创建版本号写为当前系统版本号
-
delete: 把删除版本号置为当前系统版本号
-
select: 选出数据行创建版本号小于等于事务创建版本号的,并且删除版本号未定义或者删除版本号大于事务删除版本号的。
InnoDB 的 MVCC
InnoDB 的 MVCC,主要依赖于隐藏字段、Read View、undo log
具体实现
-
插入:InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
-
更新:获取最大版本号的数据,然后计算该数据更新后的结果,并创建一个当前系统版本号的数据。
-
用写锁锁定行
-
记录 redo log
-
把改行修改前的数据复制到 undo log
-
修改当前行的值,填写事务编号,使回滚指针指向 undo log 中的修改前的行。
-
删除:InnoDB 将数据行的删除标志设置为当前系统版本号
-
读:满足两个条件:
-
数据行的 create_version <= 事务的 create_version
-
数据行的 delete_version 未定义,或者 > 事务的 delete_version
(2) PostgreSQL 与 MVCC
多版本时间戳排序协议。
-
PostgreSQL 中都是使用乐观并发控制的
-
PG 的读请求,数据库直接返回最新的消息。
范式
第一范式
-
列都是不可再分的。
第二范式
例子2:
id, stu_name, course_id, course_name 拆分学生表和课程表
第三范式
-
非主键列之间不存在传递依赖。
比如:Student(id, 姓名, 年龄, 所在学院, 学院地点, 学院电话)
-
因为存在如下决定关系:
(学号) → (姓名, 年龄, 所在学院, 学院地点, 学院电话),这个数据库是符合2NF的,
-
但是不符合3NF,因为存在如下决定关系:
(学号) → (所在学院) → (学院地点, 学院电话)
分库分表
1.分库
a.垂直分库
b.水平分库
2.分表
a.垂直分表
b.水平分表
MySQL优化方案
参考:MySQL高性能优化规范建议 | JavaGuide【对线面试官】MySQL调优 (qq.com)
-
数据库表存在一定数据量,就需要有对应的索引
-
发现慢查询时,检查是否走对索引,是否能用更好的索引进行优化查询速度,查看使用索引的姿势有没有问题
-
explain 看是否走索引。
-
查询时,使用函数
、查询条件计算
、<>
、IS NOT NULL
或者类似 like '%xxx'
或导致索引失效。
-
使用覆盖索引避免回表操作。
-
尽量指定 select 的列,避免 select *。
-
考虑建立联合索引,把区分度高的列放左边,匹配最左匹配原则。
-
当索引解决不了慢查询时,一般由于业务表的数据量太大导致,利用空间换时间的思想(NOSQL、聚合、冗余...)
-
insert、delete 要拆分。
-
用子查询来优化,比如 limit offset, n
,从 offset 获取到 offset + n 的数据,提高查询效率。
-
数据量太大,可以考虑能否把旧数据备份后删除。
-
如果对于数据一致性要求没有那么高的话,可以用缓存(如 redis)来优化查询。
-
如果涉及到字符串查询导致查询速度慢的话,可以用 ElastaticSearch 搜索引擎来存储数据,查询走 ES。
-
可以根据查询条件的维度,考虑做聚合表,比如用户下单后的订单明细,可以按天聚合成一条数据,查询就快了。
-
当读写性能均遇到瓶颈时,先考虑能否升级数据库架构即可解决问题,若不能则需要考虑分库分表
-
升级主从架构,实现读写分离。
-
分库分表。比如按照 userId 进行分表。
1.数据库服务器内核优化
2.my.cnf 配置,搭配压力测试进行调试
3.sql语句调优
-
使用缓存优化查询(不推荐)
-
用 explain 检测 SQL 查询,看看我们的 sql 是否走了索引
-
给常 select 的列建立索引
-
使用覆盖索引避免回表操作
-
指定 select 中查询的列,尽量不要 select *
-
考虑建立联合索引,把区分度高的列放在左边,充分考虑最左匹配原则。
-
limit 1(明确只有一行数据时)
-
选择正确的数据库引擎(mySIAM, innoDB)
-
大量的 delete、insert 进行拆分,分几次进行。大查询也可以分几次进行,每次返回一部分数据。
-
数据表特别大的时候,把不用的旧数据备份后删除。
-
数据类型尽量使用小的
-
固定字段长度
-
明确的固定的字段上使用 enum (性别、国家、市) varchar
-
id 主键每张表都要建立集群分区
-
rand() 计算是在 cpu 上进行的
-
连接两表的时候, join 尽量保持两个字段的类型一致
-
垂直分割
四个层面
-
查询语句优化(逻辑层面)
-
索引优化(物理层面)
-
数据库参数设置优化——调整my.cnf
-
分库分表
查询语句优化
-
切分查询
-
分解关联查询
count
-
统计某个列值的数量(不包括NULL)
-
统计结果集的行数(包括NULL)
索引查询优化
最左前缀原则
在最左匹配原则中,有如下说明:
-
最左前缀匹配原则,非常重要的原则,mysql会根据联合索引一直向右匹配直到遇到范围查询 (>、<、between、like) 就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
-
= 和 in 可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成查询效率最高的形式,也就是匹配联合索引的形式。
CREATE TABLE `user` (
`userid` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL DEFAULT '',
`password` varchar(20) NOT NULL DEFAULT '',
`usertype` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`userid`),
KEY `a_b_c_index` (`username`,`password`,`usertype`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
现在有一个user表,联合索引为(username, password, usertype)
explain select * from user2 where username = '1' and password = '1';
explain select * from user2 where password = '1' and username = '1';
-
索引失效情况
-
计算导致索引失效
#用了索引
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;
#计算导致索引失效
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
-
函数导致索引失效
#创建索引
CREATE INDEX idx_name ON student(NAME);
# 索引起作用
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
# 函数导致索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
-
类型转换导致索引失败
# name是varchar型
# 索引有用
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = '123';
# 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123;
-
范围条件右边的列索引失效
# 创建索引
CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);
# 索引idx_age_classId_name不能正常使用
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
# 创建索引
CREATE INDEX idx_age_name_classid ON student(age,NAME,classid);
# 索引idx_age_name_classid可以正常使用
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
应用开发中范围查询,例如:金额查询,日期查询往往都是范围查询。应将查询条件放置where语句最后。(创建的联合索引中,务必把范围涉及到的字段写在最后)
-
!= 或者 <> 导致索引失效
# 创建索引
CREATE INDEX idx_name ON student(NAME);
# 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ;
# 但这个用了主键索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.id != 55 ;
-
is null可以用索引,is not null 无法使用索引
# 用了索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
# 没用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;
-
like以通配符%开头索引失效
# 使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%';
# 未使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';
拓展:Alibaba《Java开发手册》 【强制】页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。
-
or 前后存在非索引的列,索引失效
#未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
#在age字段上创建索引,发现还是没有用到索引
CREATE INDEX idx_age ON student(age);
#在classid上创建索引,这时候用上了索引,因为OR的前后两个字段都加上了索引。
CREATE INDEX idx_cid ON student(classid);
-
字符集不同导致索引失效
索引查询优化
关联查询优化
-
确保 ON 或者 USING 子句上的列上有索引。
-
内连接:有索引的数据量大的表作为被驱动表供没有索引的数据量小的驱动表查询。(给关联字段添加索引)
-
Join 查询时,使用查询结果集(行 * 单行容量)小的驱动表嵌套大的驱动表
子查询优化
-
禁止使用 not in,not exists 子查询,改用 left join … where b.x is null;
或者 left join … where b.x is null = ''
;
排序索引
-
对应索引顺序不能错,否则不会使用索引
-
对于排序数据优化器会综合考虑全加载到内存进行 fileSort 更快还是使用索引排序更好。尽量使用上索引排序
-
当使用 where ... order by 时,也能用上索引
Group by 优化
-
使用 group by,order by,distinct时,尽量保证where过滤结果集在1000 以内
分页查询优化
-
select … from … limit 20000, 10改为select …from where id > 20000 limit 10。保证往聚簇索引上靠
上面两种写法都表示取第2,3,4三条数据。
LIMIT用法:
①如果后接一个参数,如select* from article LIMIT 10。表示取前10个数据。
②如果后接两个参数,如LIMIT 1,3 。第一个数表示要跳过的数量,后一位表示要取的数量,例如:select* from article LIMIT 1,3 就是跳过1条数据,从第2条数据开始取,取3条数据,也就是取2,3,4三条数据。
OFFSET用法:
③当limit和offset组合使用的时候,limit后面只能有一个参数,表示要取的数量,offset表示要跳过的数量。例如:select * from article LIMIT 3 OFFSET 1 表示跳过1条数据,从第2条数据开始取,取3条数据,也就是取2,3,4三条数据。
覆盖索引
索引下推(ICP)
主键设计方案
-
淘宝:订单id可能是时间 +去重字段 + 用户id尾号6位
-
mysql8.0有改进的主键id
MySQL主从复制
实现
分为
-
同步复制:用户写到主数据库的数据必须同步到从数据库后,才会告诉用户写成功。
-
异步复制:用户写到主数据库的数据,就告诉用户成功。
-
半同步复制:用户写到主数据库的数据,只要同步到一个从数据库就告诉用户成功。
分布式系统 CAP 理论
-
Consistency 一致性:任何用户读到的数据要么是最新的,要么就响应错误。
-
Available 可用性:任何用户的请求一定能返回到数据,而不会得到响应错误。
-
Partition tolerance 分区容忍性:分布式系统依靠网络传输数据,而网络是不可信的。但是消息丢失或者延迟,不影响系统继续提供服务。
主从模型
形式
-
一主一从
-
一主多从
-
多主一从
-
双主复制
-
级联复制
主从复制原理
主库记录 binlog,然后从库将 binlog 在自己的服务器上重放,从而保证了主、从的数据一致性。
-
基于行
-
物理复制
-
binlog 中记录实际更新数据的每一行。
-
基于语句
主从复制步骤
-
从库启动 I/O 线程,与主库建立连接。
-
主库启动 binlog dump 线程,读取主库上的 binlog event,并且发送给从库 I/O 线程。
-
从库 I/O 线程将读取到的 binlog event 写到 relay log 中。
-
从库启动 SQL 线程,把 Relay log 中的数据进行重放,完成数据同步。
Relay log
复制方式
(1) 异步复制
(2) 同步复制
(3) 半同步复制
数据一致性
从库维护一个 relay-log.info 文件中,记录了当前从库正在复制的 binlog 和写入的 relay log 的 POS 位置。
MySQL 怎么开启主从复制?
SQL 语句
Group by
用法
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
Datetime 和 Timestamp
-
Datetime:没有时区信息,8 bytes
-
Timestamp:有时区信息,4 bytes