导图社区 MySQL数据库
- 41
- 3
- 1
- 举报
MySQL数据库
java后端面试必备的mysql知识点梳理、InnoDB存储引擎是一种兼顾高可靠性和高性能的通用存储引擎，也是MySQL5.5之后默认的存储引擎。
编辑于2023-02-11 21:30:44 河南- mysql
- Java后端
- MySQL数据库
java后端面试必备的mysql知识点梳理、InnoDB存储引擎是一种兼顾高可靠性和高性能的通用存储引擎，也是MySQL5.5之后默认的存储引擎。
- java虚拟机
java虚拟机相关知识点梳理、1、通过类的全限定名获取定义此类的二进制字节流；2、将二进制字节流所代表的静、态存储结构转化为方法区的运行时数据结构；3、在内存中生成该类的Class对象放入元空间中，作为方法区这些数据的访问入口。
- 并发编程
系统的梳理并发编程相关概念，助力面试！一种温柔的关闭线程池的方式，不会接收新的任务，但在关闭前将之前提交的任务处理完毕、一种粗暴的方式关闭线程池，既不会接收新的任务，也不会处理已经提交的任务，但会返回队列中未处理的任务。
MySQL数据库
社区模板帮助中心，点此进入>>
- MySQL数据库
java后端面试必备的mysql知识点梳理、InnoDB存储引擎是一种兼顾高可靠性和高性能的通用存储引擎，也是MySQL5.5之后默认的存储引擎。
- java虚拟机
java虚拟机相关知识点梳理、1、通过类的全限定名获取定义此类的二进制字节流；2、将二进制字节流所代表的静、态存储结构转化为方法区的运行时数据结构；3、在内存中生成该类的Class对象放入元空间中，作为方法区这些数据的访问入口。
- 并发编程
系统的梳理并发编程相关概念，助力面试！一种温柔的关闭线程池的方式，不会接收新的任务，但在关闭前将之前提交的任务处理完毕、一种粗暴的方式关闭线程池，既不会接收新的任务，也不会处理已经提交的任务，但会返回队列中未处理的任务。
- 相似推荐
- 大纲
数据库
三大范式
(原子性)第一范式：每一列都不可以再分
(完全依赖)第二范式：非主键列完全依赖于主键，而不能依赖于主键的一部分
(直接依赖)第三范式：非主键列必须直接依赖于主键，而不能依赖于其他非主键列
SQL基础
SQL的分类
DDL：数据定义语言，用来定义数据库、表和字段
数据库操作
show databases#查询所有数据库
select database()#查询当前选中的数据库
create database [if not exist] myschool [default charset utf8mb4]#创建数据库
drop database [if exists] myschool#删除数据库
use database myschool#使用数据库
表操作
show tables#查询当前数据库的所有表
desc student#查看当前表的详细信息
show create table student#查询指定表的建表语句
删除表
drop table student
删除所有的数据行和表结构，速度最快，无法找回数据
truncate table student
只删除表的数据，不会删除表的结构，速度第二，但无法与where一起使用
delete from student
只删除数据，不删除表结构，可以与where一起使用来删除指定的行，速度最慢
三者比较
语句类型：drop和truncate是数据定义语言，delete是数据操作语言
安全问题：drop和truncate删除时不记录日志，无法回滚，delete可以回滚
删除程度：delete只删数据，不删表结构和索引，truncate删除数据，不删表结构，但会会重建索引，drop会把数据，表结构，索引都删除
效率比较：drop>truncate>delete
(修改表名)alter table student rename teacher
字段操作
(添加字段)alter table student add nickname varchar(50) [comment '昵称'] [约束];
(仅修改字段类型)alter table student modify nickname char(50)
(改变整个字段)alter table student change nickname username char(50) [约束][注释]
(删除字段)alter table student drop nickname
DML：数据操纵语言，用来对表中的数据进行增删改
DQL：数据查询语言，用来查询表中的记录
DQL编写顺序
SELECT 字段列表 5、分组后获取字段列表 FROM 表明列表 1、先从表中查询 WHERE 条件列表 2、再从表中筛选 GROUP BY 分组字段列表 3、指定分组 HAVING 分组后的条件列表 4、指定分组后的条件 ORDER BY 排序字段列表 6、进行排序 LIMIT 分页参数 7、进行分页
DLQ执行顺序
FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后的条件列表 SELECT 字段列表 ORDER BY 排序字段列表 LIMIT 分页参数
DCL：数据控制语言，用来创建数据库用户、以及管理用户访问数据库的权限
用户管理
查询用户
select * from user
创建用户
create user `wsh`@`localhost` identified by '123456'
修改用户密码
alter user `wsh`@`localhost` identified [with mysql_native_password] by 'newmima'
删除用户
drop user `wsh`@`localhost`
权限管理
查询权限
show grants for `wsh`@`localhost`
授予权限
grant select,update,insert,delete on myschool.student to `wsh`@`localhost`; grant all on *.* to `%`@`%`;
撤回权限
revoke select on myschool.student from `wsh`@`localhost`
mysql常用的数据类型
数值类型(默认有符号数)
整数类型
TINYINT(1 byte)
SMALLINT(2 byte)
MEDIUMINT(3 byte)
INT(4 byte)
BIGINT(8 byte)
浮点型
FLOAT(4 byte)
DOUBLE(8 byte)
定点型
DECIMAL(取决于精度和标度)
字符串类型
CHAR（性能好，存储定长字段，例如手机号，不足指定长度用空格填充）
VARCHAR(性能差，存储变长字段，例如邮箱，不足指定长度不会用空格填充)
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
枚举类型
ENUM（只可以给字段插入一个值）
INSERT set_db() VALUES('1')
SET(可以给字段插入多个值)
INSERT set_db() VALUES('1,2,3')
日期时间类型
TIME
DATE
DATETIME
TIMESTAMP
YEAR
函数
字符串函数
CONCAT
LOWER
UPPER
LPAD(str,n,'c')左填充
RPAD(str,n,'c')右填充
TRIM
SUBSTRING(str,1,5)
数值函数
CEIL（向上取整）
FLOOR（向下取整）
MOD(x,y)返回x/y的模
RAND()返回0-1之间的随机数
ROUND(x,y)求参数x的四舍五入的值，保留y位小数
日期函数
CURDATE()当前日期
CURTIME()当前时间
NOW()当前日期和时间
YEAR（date）
MONTH（date）
DAY（date）
DATA_ADD(date,INTERVAL 70 DAY)往后推70天
DATEDIFF（date1，date2）返回date1-date2的值
流程控制函数
if(value,str1,str2)如果value为true，返回str1，否则返回str2
select if(true,'aaa','bbb') #返回aaa select if(false,'aaa','bbb') #返回bbb
ifnull(str1,str2)如果str1不为空，返回str1，否则，返回str2
select ifnull('aaa','bbb') #返回'aaa' select ifnull('','bbb') #返回'' select ifnull(null,'bbb') #返回bbb
case when then else end
eg1： select name, (case city when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as address from user eg2： select id, name, (case when score>=90 then '优秀' when score>=80 then '及格' else '不及格' end) as level from result
约束
主键约束
唯一约束
非空约束
默认约束
检查约束
外键约束
#添加外键 alter table course add constraint fk_course_student_id foreign key （student_id）references student(id) on update cascade on delete cascade; #删除外键 alter table course drop foreign key fk_course_student_id;
NO ACTION如果子表有记录，父表不允许更新
RESTRICT如果子表有记录，父表不允许更新
CASCADE如果子表有记录，则父表更新则更新，父表删除则删除
SET NULL子表记录设置为空
SET DEFAULT子表记录设置默认值
概要
数据库连接查询
内连接
内连接只返回两个表中连接字段相等的行
左连接
左连接返回左表的全部记录以及右边连接字段相等的记录，其余部分用空值填充
右连接
右连接返回右表全部记录，以及左边连接字段相同的记录，其余部分空值填充
外连接
全外连接返回两张表的全部记录，以及左右表连接字段相等的记录
事务
开启事务的方式
自动提交
select @@autocommit 查看是否自动提交
set @@autocommit=0设置不自动提交
start transaction 或 begin
四大特性
原子性：事务是最小分割的基本单元，要么都执行，要么都不执行(通过undo log 来实现原子性)
一致性：事务在提交前后，其状态保持一致(保证了原子性、持久性、隔离性之后，一致性才有保障)
隔离性：并发访问数据库时，一个事务的执行不受其他事务的影响，事务之间相互独立(通过锁机制和MVCC多版本并发控制实现隔离性)
持久性：事务一旦提交或回滚，它对数据库中数据的改变是永久的(使用redo log来实现持久性)
事务并发带来的问题
脏读
一个事务读取另一个事务还没有提交的数据
不可重复读
一个事务先后读取同一条记录，但两次读取的数据不同
幻读
一个事务按照条件查询数据时不存在，但是当插入数据时，发现当前数据已存在，好像出现了幻觉一样（一个事务先后读取的结果集不一样，变多或变少）
丢失更新
一个事务访问了某一种数据时，另一种事务也访问了该数据，第一个事务修改了该数据并提交事务之后，另一个事务也对数据进行了修改并提交，那么第一个事务的修改就会丢失。
事务的隔离级别
读取未提交(读数据不加锁)
导致
脏读
不可重复读
幻读
读取已提交(读数据加锁，语句执行完释放锁)（Oracle默认）
导致
不可重复读
幻读
可重复读(读数据加锁，必须等事务提交之后才释放锁)（MySQL默认）
导致
幻读
可串行化(整个事务过程一直持有锁，事务提交之后才释放锁)
查看事务的隔离级别
查看
select @@transaction_isosaction
设置
set [session|global] transaction isolation level {read uncommited | read commited | repeatable read | serializable}
存储引擎
MySQL体系结构
连接层
主要负责连接管理、授权认证、以及相关的安全方案。用户登录成功后，服务器也会验证该客户所具有的操作权限。
服务层
主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。 所有跨存储引擎的功能也在这一层实现，如过程、函数等
引擎层
负责MySQL中数据的存储和提取，服务层通过API和引擎层进行通信。不同存储引擎具有不同的功能，其中索引就是根据引擎层来实现的。
存储层
将数据存储在文件系统之上，并完成与存储引擎的交互
概要
存储引擎简介
存储引擎就是数据的存储、查询、更新以及建立索引的实现方式。 存储引擎是基于表的，同一个数据库中不同的表可以有不同的存储引擎，所以存储引擎也称为表类型 通过show engine来查看当前数据库支持的存储引擎，MySQL 5.5之后默认存储引擎为InnoDB
InnoDB引擎
InnoDB存储引擎是一种兼顾高可靠性和高性能的通用存储引擎，也是MySQL5.5之后默认的存储引擎。
支持事务（DML操作遵循ACID模型）、外键（保证数据的完整性和正确性）和行级锁（提高并发访问性能）
.ibd文件用于存储表结构、数据和索引，每一张表都对应一个表空间文件，参数innodb_file_per_table用于指定是否多张表公用一个表空间
逻辑存储结构：
表空间(TableSpace)
段(Segment)
区(Extent)，大小是固定的，为1M，可以存储64页
页(Page)，大小固定为16K
行(Row)
MyIsam引擎
是MySQL数据库早期默认的存储引擎
不支持事务、外键和行级锁，只支持表锁，但是它访问速度快，适合频繁的查询操作
.sdi用于存储表结构信息，.MYD存储数据，.MYI存储索引
Memory引擎
所有数据都在内存中，只有.sdi文件用于存储表结构信息，数据的处理速度块，支持哈希索引，但是对表有内存限制，太大的表无法缓存在内存中，且安全性不高
概要
存储引擎的选择
如果应用经常进行更新删除操作，对事务的完整性和并发性要求比较高，使用InnoDB
如果应用以读和写为主，很少进行更新和删除，对事物的并发性要求不高，建议使用MyIsam，例如日志相关的数据或电商系统中足迹还有评论相关的数据
被MongoDB所替代
memory数据保存在内存中，访问速度快，常用于做临时表或缓存，缺点就是对表有大小限制，太大的表无法缓存在内存中，而且无法保证数据的安全性
被redis所替代
InnoDB与MyIsam对比
InnoDB支持事务、外键、行级锁以及MVCC多版本并发控制，而MyIsam不支持
InnoDB必须要有主键，而MyIsam可以没有主键
InnoDB适合频繁的更新操作，MyIsam适合频繁的查询操作
在InnoDB中，.ibd文件用于存储表结构、数据、索引，在MyIsam中，表结构、数据、索引是分开存储的，分别是.frm、.myd、.myi文件
索引
什么是索引
索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。 索引是一种数据结构，用于协助查询和更新数据库表中的数据，索引的实现通常有B树和B+树。
优缺点
优点： 加快数据的检索效率，降低数据库的IO成本。 通过索引列对数据进行排序，降低数据排序的成本。 缺点： 时间方面，创建索引和维护索引需要耗费时间，当对数据库中的表进行更新的时候，索引也需要动态的维护，会降低表更新的效率。 从空间上来说，索引需要占据一定的磁盘空间 因此，如果表数据量比较小的话，不适合建立索引
索引的数据结构
B+树索引
最常见的索引类型，大部分存储引擎都支持B+Tree索引
Hash索引
1、查询效率高，在不出现hash冲突的情况下，通常只需要一次检索，效率高于B+树 2、只支持等值匹配，不支持范围查询 3、无法完成排序的操作 4、只有memory存储引擎支持hash索引，而InnoDB中具有自适应hash的功能，hash索引是InnoDB根据B+树索引在指定条件下自动构建的
R-tree索引（空间索引，不常用）
MyIsam引擎的一个特殊的索引类型，主要用于地理空间数据类型
Full-text索引（全文索引，不常用）
一种通过建立倒排索引，快速匹配文档的索引类型
存储引擎支持情况
为什么InnoDB选择B+树索引？
与二叉树或者红黑树相比： 如果顺序插入，会导致二插树的深度过大，影响查询效率 与B树相比： 第一点：B树非叶子节点也会存放数据，这样导致一页中键值和指针减少，在存储同样数据量的情况下，B树的深度更大，导致性能的降低。 第二点：B+树无论查询那个数据，都要到叶子结点才能找到相应的数据，效率更加稳定，而B树可能在非叶子节点就查询结束 第三点：B+树的所有叶子节点之间形成一个有序的双向链表，便于范围查询和排序 与hash索引相比： hash只支持等值匹配，不支持范围查询，有时候还会发生hash冲突 与红黑树相比： 索引的数据结构会放在磁盘中，每次查询都要在磁盘中进行访问。红黑树节点的出度为2，而B+树的出度一般都非常大，所以在相同数据量的情况下，B+树的高度会更低，减少了磁盘IO操作次数。 因此InnoDB选择B+树索引结构
索引的分类
主键索引
数据不能有重复值和空值，一个表只能有一个主键索引
唯一索引
数据不允许重复，允许有空值，但只能有一个，一个表可以有多个唯一索引
普通索引
数据可以重复，也可以有空值，一个表可以有多个普通索引
全文索引
全文索引查找的是文本中的关键字，而不是索引中的值，一个表可以有多个全文索引
根据索引的存储形式划分
聚集索引
索引与数据放在一块，索引结构的叶子节点保存了行数据，一个表必须有聚集索引，而且只能有一个 聚集索引的创建规则： 1、如果一个表存在主键，那么主键索引就是聚集索引 2、如果没有主键，那么选择第一个唯一索引作为聚集索引 3、如果没有主键和唯一索引，则InnoDB会自动生成一个rowID作为隐藏的聚集索引
二级索引（非聚集索引、辅助索引）
数据与索引分开存储，索引结构的叶子节点保存了对应的主键。一个表可以有多个非聚集索引 回表查询是指先用二级索引找到相应的主键值，再根据主键值到聚集索引中查找相应的数据
思考题
id为主键索引，name为普通索引 select * from user where id = 118 select * from user where name = '小明' 前者效率高，聚集索引，只需要到达叶子节点，就能找到对应的行数据 后者效率低，先根据name对应的二级索引中去查，找到对应的id，然后根据id再进行一次查询
聚集索引与非聚集索引的区别？
聚集索引是指索引与数据放在一块，叶子结点存储了所有的行数据，索引中键值的逻辑顺序决定了磁盘行的物理顺序；非聚集索引中索引与数据时分开的，叶子节点存储的是行数据对应的主键，索引中键值的逻辑顺序与磁盘行的物理顺序不同
一个表必须有聚集索引，而且只能有一个，但可以有多个非聚集索引
聚集索引插入数据较慢,因为需要移动数据行对应的物理位置，查询数据较快，因为叶子结点就对应行数据，非聚集索引如果查询时使用覆盖索引，其查询速度也是很快的
索引的使用
or分割的左右字段都要有索引
模糊匹配中%不能放在最左边
最前面的几个字符是模糊不清的，无法根据索引的有序性定位到一个具体的索引，只能进行全表扫描，找出符合条件的数据 类似于联合索引中，必须有最左边的索引
联合索引必须要最左前缀原则
最左前缀原则指的是，查询从索引的最左列开始，不能跳过中间列，如果跳过中间列，则后面列的索引将失效 使用索引的时候，必须使用最左列，但是位置可以不再最左边： 联合索引：(age,name,school) 使用方式： where age=1,name=3,school=5 生效 where name=3,school=5,age=1 生效 where name=3,school=5 失效
索引字段中不能出现：<,>,!=,not in
在联合索引中，如果出现了范围查询(>,<)，则范围查询右侧的列索引将失效 解决方式：使用>= 或 <=来解决
不能在索引列上进行函数操作，索引也不能是表达式的一部分
隐式类型转换会出现索引失效，例如int字段加上引号
如果mysql评估使用索引比全表扫描更慢，则不使用索引
根数据分布有关系，假如该查询能够查询整张表的大部分数据，则不会走索引 如果只能查询一小部分数据，则会走索引
索引的失效情况
尽量使用覆盖索引，不要使用select*
覆盖索引指的是：需要返回的列，在所使用的索引中都能够全部找到 eg.表user里面id为聚集索引，name为二级索引 select * from user where id=118;#执行了一级索引，叶子节点包含了所有数据，不需要回表查询。 select id,name from user where name='root';#执行了二级索引，因为要返回的name是索引，同时叶子节点存储的就是id值，因此也不需要回表查询。 select * from user where name='root';#执行了二级索引，由于要返回全部字段，因此要回表查询。
面试题
假如有一张表user(id,name,age,status),由于数据量过大，需要对以下SQL语句进行优化，该如何优化？ select id,name,age from user where name='itcast'; 优化：建立name，age为联合索引 create index idx_user_name_age on user(name,age);
前缀索引的使用
当字段为varchar或text类型时，在此字段上建立索引会让索引变得很大，浪费大量的IO操作，影响查询效率。可以使用前缀索引，只将字符串的一部分前缀建立索引，这样可以大大降低索引空间，提升检索效率。 语法： create index idx_xxx on student(title(5))#对字段title的前5个字符建立索引 根据索引的选择性来确定前缀长度（唯一索引的选择性为1） 公式： select count(distinct email)/count(*) from student;#查询email的选择性 select count(distinct substring(email,1,5))/count(*) from student;#查询email前5个字符的选择性
索引的设计原则
（哪些表）针对于数据量大、且频繁进行查询的表建立索引
（哪些字段）针对于常作为查询条件where、order by、group by操作的字段建立索引
（区分度高，唯一索引）尽量选择区分度高的字段建立索引，并且尽量建立唯一索引，区分度越高，使用索引的效率也就越高
（字段较长，前缀索引）字符串类型的字段，如果字段的长度过长，可以根据字段的特点建立前缀索引
（不能为空，not null）在创建表时，如果索引字段不能存储空值，那么用not null来约束这个字段，这样在查询表的时候，优化器可以更容易选择最合适的索引
（覆盖索引，联合索引）尽量选择联合索引，避免使用单列索引，因为联合索引在查询的时候很多情况下都可以覆盖索引，避免回表查询，提升查询效率
（索引数量）控制索引的数量，索引并不是多多益善，索引本身占据一定的磁盘空间，需要维护，同时对于修改数据的代价也比较大。
（索引列顺序）让选择性最强的索引列放在联合索引的前面
索引不适合的场景
数据量比较小的表
频繁进行更新的表
离散性比较低的字段
SQL的优化
加索引
避免返回不必要的数据
适当分批量进行
。。。
分库分表
SQL性能分析
SQL执行频率
show global status like 'Com_______' 可以查看增删改查的频率
慢查询日志
慢查询日志记录了所有执行时间超过指定参数long_query_time的所有SQL语句的记录 默认没有开启此功能，需要到my.cnf中配置如下信息 slow_query_log=1 #开启慢查询日志 long_query_time = 2#超时时间2秒 修改完重启mysql服务： systemctl restart mysqld 查看是否开启该日志： show variables like 'slow_query_log';
profile详情
可以查询每一条SQL语句的更详细的执行情况，耗时多少，都耗费在了哪一部分 操作： 1、通过select @@have_profiling来查看当前是否支持该功能 2、通过select @@profiling来判断该功能是否开启 3、set profiling=1 来开启此功能 使用： 1、查看每一条SQL的基本耗时情况 show profiles; 2、查看指定query_id的SQL语句各阶段的耗时情况 show profile for query 118; 3、查看指定query_id的SQL语句的CPU使用情况 show profile cpu for query 118;
explain执行计划
#两种使用方式： explain select * from user; desc select * from user; explain执行计划各字段的含义： id：select查询的序列号，表示查询中执行select子句或操作表的顺序 （id值相同，从上往下执行，id值不同，值越大越先执行） select_type：表示查询的类型，例如simple、primary、subquery、union type（重要）：表示连接类型，性能由好到差的连接类型为：null、system、const、eq_ref、ref、range、index、all （不查询任何表一般是null，查询系统表一般是system，根据主键或唯一索引查询表一般是const，使用非唯一索引一般是ref，如果对索引也进行扫描一般是index，全表扫描一般是all） possible_key：表示可能用到的索引 key：表示实际用到的索引 key_len：表示索引中使用的字节数，为索引字段最大可能长度，并非实际使用长度。
SQL提示
如果在一个字段上建立了多个索引，可以通过SQL提示来实现优化数据库的目的 use index 建议使用 ignore index 忽略使用 force index 强制使用 #建议使用 explain select * from user use index(idx_user_name) where name = 'zhangshan' #忽略使用 explain select * from user ignore index(idx_user_name) where name='zhangshan' #强制使用 explain select * from user force index(idx_user_name) where name='zhangshan'
SQL优化的一般步骤
show status 查看各种命令的执行频率
通过慢查询日志定位那些执行效率低的SQL语句
通过explain或desc来分析低效的SQL语句
SQL语句的具体优化
insert插入优化
批量插入
insert into user values (...),(...),(...)
手动提交事务
start transaction; insert into user (...); insert into user (...); insert into user (...); insert into user (...); commit;
主键顺序插入
尽量之间顺序插入 如果主键乱序插入，可能会导致页分裂
页分裂
页合并
当删除一行记录时，实际上并没有物理删除，只是记录被标记为删除并且它的空间变得允许被其他记录声明使用。 当页中删除的记录到的MERGE_THRESHOLD(默认50%)，InnoDB会开始寻找最靠近的页，看看是否可以将两个页合并，从而优化空间
主键优化原则
在满足业务需求的条件下，尽量降低主键的长度
插入数据时，尽量选择顺序插入，使用AUTO_INCREMENT自增主键，避免使用UUID这种字段作为主键
大批量数据的插入，使用insert性能较低，此时应该使用load指令
#客户端连接数据库时，加上参数--local-infile mysql --local-infile -uroot -p #设置全局参数local_infile为1，开启从本地加载文件到数据库的开关 set global local_infile=1; #执行load指令 load data local infile '/root/sql.log' into table 'tb_user' fileds terminated by ',' lines terminated by '\n'；
order by排序优化(尽量使用index)
using filesort
先通过表的索引或全表扫描，获取符合条件的数据行，然后在排序缓冲区完成排序操作 也就是，所有不是通过索引直接返回排序结果的排序都是file sort排序
using index
通过索引顺序扫描直接返回有序数据，不需要额外排序，效率较高
具体优化方法
根据排序字段建立合适的索引，多字段排序，也遵循最左前缀原则
尽量使用覆盖索引，尽量只返回需要的字段，不要返回全部字段
多字段排序时，一个升序，一个降序，此时要注意创建联合索引的规则
create index idx_user_age_phone on user(age asc,phone desc);
如果不可避免出现filesort排序，大数据量排序时，可以适当增加排序缓冲区sort_buffer_size的大小
group by优化
可以通过索引提高效率
也需要遵循最左前缀法则
limit优化
问题：limit 200000000,10非常耗时
解决方案：覆盖索引+子查询
首先根据id进行排序，通过limit找到符合条件的id结果集，然后根据这个结果集返回所需的记录 加入要查询user表中2000000以后的10条记录 select * from user as u,(select id from user order by id limit 2000000,10) as r where u.id=r.id
count优化
count的几种用法
count(主键)
InnoDB会遍历整张表，把每一行的id取出来，返回给服务层，服务层不需要判断为空，直接进行累加操作
count(字段)
没有not null约束：InnoDB先遍历每一张表，把字段取出来，服务层判断如果不为空才进行累加操作
有not null约束：与主键性能一样，服务层直接进行累加
count(1)
InnoDB遍历整张表，但不取值，直接按行进行累加
count(*)
InnoDB引擎专门做了优化，不会取值，直接进行累加
性能比较count(*)效率最高
count(*) 约等于 count(1) 大于 count(id) 大于 count(字段)
update优化
假如name不是索引 update set name='aaa' where name='bbb';#此时会将行级锁升级为表级锁 需要为name添加索引
更新的条件一定要有索引
否者行级锁会升级为表级锁
数据库锁
高并发环境下，如何安全修改行？
使用乐观锁与悲观锁
乐观锁与悲观锁
乐观锁
一旦一个事务拥有悲观锁，其他事务都不能对数据进行修改，必须等待锁释放
悲观锁
允许多个事务同时对数据进行访问，在更新的时候通过版本号机制或CAS算法来判断是否可以提交事务
select for update的含义
锁的分类
全局锁
对整个数据库实例进行加锁，加锁后整个实例不能进行DML、DDL，只能进行DQL
典型的使用场景就是对数据库进行逻辑备份
备份数据库的方式
添加全局锁：flush tables with read lock;
备份数据：mysqldump -uroot -p123456 myschool > myschool.sql;
解锁：unlock tables;
表级锁
特点：对整张表进行加锁，加锁快，开销小，锁粒度最大，锁冲突概率也最大，并发度低，但不会出现死锁
分类
表锁
共享读锁
语法
加锁lock tables student read
解锁unlock tables student/客户端断开连接
独占写锁
语法
加锁lock tables student write
解锁unlock tables student/客户端断开连接
元数据锁(meta data lock)
MDL加锁过程是系统自动控制的，在访问一张表时会自动加上，主要为了避免DML与DDL冲突，保证读写的正确性
在MySQL5.5中引入了MDL，当对一张表进行增删改查时加共享锁，当修改表结构的时候加排它锁。(一个进程对数据进行增删改查，其他进程也可以进行增删改查，但不可以修改表结构)
意向锁
作用
（主要解决在InnoDB引擎中，行锁与表锁的冲突问题）在对一张表进行加表锁之前，需要先判断这张表中是否存在行级锁，那么就需要一行一行的检测，效率低下，如果引入了意向锁，那么只需要判断一下意向锁与表锁的兼容性就可以
意向共享锁IS
与表锁共享锁兼容，与表锁排它锁互斥
(select...lock in share mode)
begin; select * from user lock in share mode; commit;
意向排它锁IX
与表锁共享锁、排它锁都互斥。意向锁之间不会互斥
(insert、delete、update、select for update)
begin; #执行增删改操作，默认加锁意向排它锁，或者使用select ... for update commit;
行级锁
特点：对当前行进行加锁，加锁慢，开销大，锁粒度最小，锁冲突概率也最小，并发度高，但容易发生死锁
分类
行锁(Record Lock)
锁定单个行记录，防止其他事务对此进行update和delete。在RC、RR隔离级别下都支持（若不通过索引字段更新数据，则会将行级锁升级为表级锁）
分类
共享锁
排它锁
上锁方式
insert、delete、update自动加上排它锁
select不加锁
select ... Lock in share mode共享锁
select ... for update排它锁
间隙锁(Gap Lock)
锁定索引记录的间隙，不包括记录本身，防止其他事务对这个间隙进行insert，产生幻读，在RR隔离级别下支持。（唯一索引上的等值查询，给不存在的记录加锁，优化为间隙锁）
间隙锁唯一目的是防止其他事务插入间隙，出现幻读，间隙锁可以共存，一个事务采用的间隙锁不会影响另一个事务对同一间隙采用间隙锁
临键锁(Next-key Lock)
行锁和间隙锁的组合，同时锁住数据和数据前面的间隙，在RR隔离级别下支持
视图、触发器、存储过程
视图
创建的视图，进行增删改，其实是对基表进行增删改
1、如果创建视图有条件，但没有使用检查选项： create view v1 as select * from user where age>9; 那么在增加一条年龄小于9的记录时，视图里面没有数据，但是基表里面有 2、如果使用了检查选项 create view v1 as select * from user where age>9 with check option; 那么在增加一条年龄小于9的记录会报错！ 3、with cascade check option 有级联（不加cascade，默认也有） 会对当前视图，以及当前视图所依赖的视图进行检查，符合条件才允许插入或更新 4、with local check option 仅仅对当前视图检查，如果依赖视图没有检查条件，不检查，如果有才会检查
视图的更新
必须要保证视图中的行与基表中的行一一对应
下列情况下不可更新
聚合函数：sum()、max()、min()等
distinct
group by
having
union 或 union all
视图的作用
使用简单
使用安全
通过视图，用户只能查询和修改视图里面包含的字段
数据独立
视图可以帮助用户屏蔽基表结构变化带来的影响（例如基表字段name改为了studentName，创建视图时可以通过select studentName as name来进行屏蔽）
存储过程
特点
封装(对SQL与具有的封装)、复用(可以重复使用)
可以接收参数，可以返回数据
减少网络交互，效率提升
语法
创建
create procedure 名称([参数列表]) begin select * from user; end;
调用
call 存储过程名称([参数列表])
查看
1、select * from information_schema.ROUTINES where ROUTINES_SCHEMA='itcase'; 从 information_schema.ROUTINES表 中查询 数据库为'itcase' 的存储过程 2、查询创建存储过程的语句 show create procedure 存储过程名称
删除
drop procedure [if exists] 存储过程名称
注意(delimiter的使用)
在命令行创建存储过程时，需要通过关键字delimiter指定SQL语句的结束符 例如delimiter $$ 让'$$'为SQL语句的结束符，默认为';'
变量
系统变量
查看系统变量
show [global|session] variables;查询全部
show [global|session] variables like 'auto%'模糊查询
select @@[global|session]系统变量名;#具体的一个变量
设置系统变量
set [global|session] 系统变量名=值
用户自定义变量
赋值
set @var_name = value
set @var_name := value
使用
select @var_name;
如果没有该变量，返回空，不会报错
局部变量
触发器
进阶InnoDB存储引擎
逻辑存储结构
表空间
段
区
页
行
架构
内存结构
缓冲池
事务原理
MVCC
基本概念
当前读
读取到的是记录的最新版本，会对读取的记录加锁，防止其他事务进行修改
这些都是当前读
select ... lock in share mode
select ... for update
update
insert
delete
快照读
简单的select(不加锁)就是快照读，可能是历史记录
隔离级别
读取已提交：每次select，都生成一个快照读
可重复读：开启事务后第一个select语句才是快照读的地方
可串行化：快照读会退化为当前读
实现原理
记录中的隐藏字段
DB_TRX_ID(最近更新事务id，记录插入这条记录或最近修改该记录的id)
DB_ROLL_PTR(回滚指针，指向这一条记录的上一个版本，用于配合undo log，指向上一个版本)
DB_ROW_ID(隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段)
undo log
回滚日志，在增删改的时候产生便于数据回滚的日志
当insert的时候，产生的日志只在回滚时需要，commit后可以立即删除
当delete、update的时候，产生的日志不仅在回滚时需要，在快照读时也需要，不会被立即删除
readview