大厂面试八股文——数据库mysql

手撕mysql命令

• 查找employees里最晚入职员工的所有信息

  select * from employees where hire_date = (
  select max(hire_date) from employees
  );
  

• 查找employees里入职员工时间排名倒数第三的员工所有信息

  select * from employees 
  order by hire_date desc 
  limit 2,1
  
  

• 建表语句如下:

  #有一个全部员工的薪水表salaries简况如下:
  CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`));
  
  #有一个各个部门的领导表dept_manager简况如下:
  CREATE TABLE `dept_manager` (
  `dept_no` char(4) NOT NULL,
  `emp_no` int(11) NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`));
  

  请你查找各个部门领导薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列，以上例子输入如下:

  select salaries.*,dept_manager.dept_no
  from salaries,dept_manager
  where dept_manager.emp_no=salaries.emp_no
        and
        salaries.to_date='9999-01-01'
        and 
        dept_manager.to_date='9999-01-01';
  

• 建表语句如下:

  CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` char(1) NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`));
  
  CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`));
  

  请你查找所有已经分配部门的员工的last_name和first_name以及dept_no，未分配的部门的员工不显示

  select employees.last_name,employees.first_name,dept_emp.dept_no 
  from employees,dept_emp 
  where employees.emp_no=dept_emp.emp_no
  

  请你查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工

  select employees.last_name, employees.first_name, dept_emp.dept_no
  from employees
  left join dept_emp
  on dept_emp.emp_no = employees.emp_no
  #左外连接：左边表数据行全部保留，右边表保留符合连接条件的行
  

​ 请你找出所有非部门领导的员工emp_n

select emp_no from employees where emp_no
not in (select emp_no from dept_manager)

获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示

select dept_emp.emp_no,dept_manager.emp_no 
from dept_emp join dept_manager on dept_emp.dept_no=dept_manager.dept_no 
where dept_emp.emp_no != dept_manager.emp_no;

获取所有部门中员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列

select dept_emp.dept_no as dept_no_a, dept_emp.emp_no, max(salaries.salary) as salary
from dept_emp,salaries
where salaries.emp_no=dept_emp.emp_no
group by dept_emp.emp_no,dept_emp.dept_no
having max(salaries.salary) = 
(
    select max(salaries.salary) 
    from dept_emp inner join salaries
    on salaries.emp_no=dept_emp.emp_no 
    where dept_emp.dept_no = dept_no_a
)
order by dept_no_a

• 有一个薪水表，salaries简况如下:

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gB2GP9mW-1649155292008)(J:\面试\2021.2.16\leetcode\pic\8307279490CB9F89069769B3CDABC925)]

  请你查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t

  select emp_no,count(to_date) as t from salaries group by emp_no having t>15
  #GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集
  #HAVING过滤分组
  #where过滤行
  #order by：排序产生的输出
  

  请你找出所有员工具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

  select distinct salary 
  from salaries
  order by salary desc
  

• 查询每个班级的平均分

  SELECT AVG (CHINESE ),AVG (ENGLISH ) FROM grade
  ORDER BY class;
  

• 从id, course, score中select出学生成绩最好的科目及成绩

  SELECT name, MAX(score) FROM id
  ORDER BY id;
  

• 查询班级平均分排名前三的班。

  SELECT AVG (CHINESE ),AVG (ENGLISH ) FROM grade
  ORDER BY class limit 3;
  

• 查询成绩第二的学生

  一张表student：
  name    mark
  ---------------
  Alice   98
  Bob     67
  Eric    75
  Kato    84
  

  #首先找到排名第一的学生
  SELECT name, mark 
  FROM student
  order by desc limit 1,1; 
  

• 实现一个 按时间降序的分页查询

  select * from answersheet_config
  where createUserId = '2244000014000000908'
  order by createDatetime desc
  limit 0,5;
  

• 给一张表，有id，name两个列，查第二大的id的列

• mysql联合索引a,b,c使用

  abc 能用到索引
  bca 能用到索引
  cab 能用到索引
  ab 能用到索引
  ac 能用到索引
  ba 能用到索引
  ca 能用到索引
  bc 用不到索引
  b 用不到索引
  c 用不到索引
  cb 用不到索引

  用到索引的有a,ab,abc,ac 因为优化器会自动调整and前后的顺序，所以ba,cba,bca,ca都会用到索引，其他的都不会用到该索引。ac这一组仅仅是a用到索引。

  组合索引 有“最左前缀”原则，遇到范围查询(>、<、between、like)就会停止匹配

查询中用到的关键词主要包含六个，并且他们的顺序依次为 select–from–where–group by–having–order by

其中 select 和 from 是必须的，其他关键词是可选的，这六个关键词的执行顺序 与 sql 语句的书写顺序并不是一样的，而是按照下面的顺序来执行

• from:需要从哪个数据表检索数据
• where:过滤表中数据的条件
• group by:如何将上面过滤出的数据分组
• having:对上面已经分组的数据进行过滤的条件
• select:查看结果集中的哪个列，或列的计算结果
• order by :按照什么样的顺序来查看返回的数据

select->from->where->group by->having->order by->limit

select：返回的列或表达式

from：从中检索数据的表

where：行级过滤

group by：行分组说明

having：组级过滤

order by：输出排序数组

limit：要检索的行数

from 后面的表关联，是自右向左解析 而 where 条件的解析顺序是自下而上的。

也就是说，在写 SQL 文的时候，尽量把数据量小的表放在最右边来进行关联（用小表去匹配大表），而把能筛选出小量数据的条件放在 where 语句的最左边 （用小表去匹配大表）

非关系型数据库和关系型数据库区别，优势比较？

非关系型数据库的优势

1. 性能

NOSQL 是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过 SQL 层的解析，所以性能非常高。

2. 可扩展性

同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。

关系型数据库的优势

1. 复杂查询

可以用 SQL 语句方便的在一个表以及多个表之间做非常复杂的数据查询。

2. 事务支持

使得对于安全性能很高的数据访问要求得以实现。

总结

对于这两类数据库，对方的优势就是自己的弱势，反之亦然。

NOSQL 数据库慢慢开始具备 SQL 数据库的一些复杂查询功能，比如 MongoDB。

对于事务的支持也可以用一些系统级的原子操作来实现例如乐观锁之类的方法来曲线救国，比如 Redis set nx。

什么是临时表，临时表什么时候删除？

临时表手动删除：

DROP TEMPORARY TABLE IF EXISTS temp_tb;

临时表只在当前连接可见，当关闭连接时，MySQL 会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表，并且操作属于本连接的临时表。

创建临时表的语法与创建表语法类似，不同之处是增加关键字 TEMPORARY，如：

CREATE TEMPORARY TABLE tmp_table ( NAME VARCHAR (10) NOT NULL, time date NOT NULL ); select * from tmp_table;

什么是sql注入，怎么防止SQL注入

什么是SQL注入?

• 用户提交带有恶意的数据与SQL语句进行字符串方式的拼接，从而影响了SQL语句的语义，最终产 生数据泄露的现象

如何防止SQL注入?
SQL语句参数化

• SQL语言中的参数使用%s来占位，此处不是python中的字符串格式化操作

• 将SQL语句中%s占位所需要的参数存在一个列表中，把参数列表传递给execute方法中第二个参数

  execute方法中的 %s 占位不需要带引号

mysql集群，集群出现延迟如何解决

MYSQL从节点延迟问题原因及处理方法

mysql 因为异步同步，只能达到最终一致性，而无法达到实时一致性，所以理论是有延迟在所难免。
在mysql 5.7 版本实现了多线程同步，缓解了延迟问题，但不可能完全实现实时同步。

一、延迟原因大概有以下几点：

1.硬件

问题主要体现在服务器性能问题上，服务器性能包括主节点和从节点。
MYSQL 同步如果配置成 binlog_format=row,从节点一般会从节点性能优于主节点。
如果是多源复制，那么从节点的性能高于主节点就尤为重要。
主要是以下几个方面：CPU、网络和磁盘

2.参数配置

影响主从同步参数如下：

#关闭binlog 日志，从节点不写日志， 减少IO。
#log-bin = /data/mysql/mysql-bin
#binlog_format=row
#如果不需要紧急备份还原从节点的话，可以关掉slave的二进制文件记录功能



----------------------
sync_binlog = 0
innodb_flush_log_at_trx_commit = 0

参数解析如下： 
sync_binlog = N
N=0    不主动刷新二进制日志文件的数据到磁盘上，而是由操作系统决定；


innodb_flush_log_at_trx_commit = N： 
N=0  每隔一秒，把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上；
log buffer 会每秒写入到日志文件并刷写（flush）到磁盘。但每次事务提交不会有任何影响，
也就是 log buffer 的刷写操作和事务提交操作没有关系。
在这种情况下，MySQL性能最好，但如果 mysqld 进程崩溃，通常会导致最后 1s 的日志丢失

N=0,0  磁盘IO写能力有限，无复制或允许复制延迟稍微长点能接受，例如：日志性登记业务；

------------------------
sync_master_info = 1
sync_relay_log=0
sync_relay_log_info = 0

sync_master_info：每间隔多少事务刷新master.info，如果是table（innodb）设置无效，每个事务都会更新
sync_relay_log：默认为10000，即每10000次sync_relay_log事件会刷新到磁盘。为0则表示不刷新，交由OS的cache控制
sync_relay_log_info：每间隔多少事务刷新relay-log.info，如果是table（innodb）设置无效，每个事务都会更新

----------------------
log_slave_updates=0 	#关闭log_slave_updates，从库【binlog】不再记录主库同步的操作日志到binlog


---------------------------------
#同步线程设置
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=18	#配置自己需要的线程数


-------------------------
long_query_time = 2
slow_query_log = 1
log_slow_slave_statements = 1
slow_query_log_file = /data/mysql/slow.log
#打开慢查询，慢查询时间限制是2秒。

3.主节点中运行的大事务

大批量的插入，修改，删除数据。因为日志格式为binlog_format=row，会产生大量的binlog 日志。
大事务处理，也是批量提交更新，也是有时间延迟的。

4.主节点、从节点的慢查询影响

慢查询会影响MYSQL性能，有锁等待。以至于数据的更新存在时间差。在从节点体现为：io等待，锁等待。

5.主节点数据锁问题

比如表修改：  alter table,create index 这类表级锁，直接产生等待。
就是一般的 update 如果修改数据量大，也会影响到从节点的同步。

6.主节点中表无主键

如果一个表没有主键，在同步到从节点后，所有的修改，每次的查询都是全表搜索，性能差问题被广大。
（比如更新100条记录，每条记录更新都是一个binlog事务,每次更新都是全表搜索）

二.定位同步延迟，怎样解决

在主从节点中，使用以下命令，可以看到以下一些信息：

主节点：show master status\G

 Master file:mysql-bin.000010 Master Position:144033539


从节点： show slave status\G


Slave_IO_State: Waiting for master to send event
		  Master_Host: 10.10.1.161
		  Master_User: bak
		  Master_Port: 3301
		Connect_Retry: 60
	      Master_Log_File: mysql-bin.000010 #主从节点同步的文件是一致的
	  Read_Master_Log_Pos: 144033539  #读到的日志位置点也是一致的，说明主从IO同步日志没有问题，也就是说问题不在IO_thread
	       Relay_Log_File: test-mysql02-relay-bin-master1.000042
		Relay_Log_Pos: 77469816
	Relay_Master_Log_File: mysql-bin.000010
	     Slave_IO_Running: Yes
	    Slave_SQL_Running: Yes
	      Replicate_Do_DB: 
	  Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys,batch
	   Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
		   Last_Errno: 0
		   Last_Error: 
		 Skip_Counter: 0
	  Exec_Master_Log_Pos: 144032167		#重新解析日志的位置点严重滞后，说明问题在 sql_thread	
	Seconds_Behind_Master: 1			#延迟时间（最小单位1秒，但这个并不是很准确的）	

1.从上面的分析可以定位到问题在 sql_thread，也就是说问题在从节点上。
有可能是性能问题，比如：MEM 不足，IO性能差。
计划先修改参数值，进行调整。

2.如果 Read_Master_Log_Pos 189460063 值与主节点 Position 1030784024 相关太大。问题应该是出在网络上，主从同步日志文件太慢。
3.如果 Relay_Log_Pos: 171735369 值  Exec_Master_Log_Pos: 171735164 相关太大，问题应该是在磁盘IO，或者服务器本身性能问题，重解析日志时，读取日志到 执行SQL时间太长 。







2.如果前面的定位发现是IO_thread 问题（日志不同步，读到的日志位置不一致）。我们可以从网络方面解决。
2.1.查看网络带宽，
2.2.使用压缩传输：
	主从日志传输进行压缩传输：slave_compressed_protocol=1

	mysql> show variables like 'slave_compressed_protocol';
	+---------------------------+-------+
	| Variable_name             | Value |
	+---------------------------+-------+
	| slave_compressed_protocol | OFF   |
	+---------------------------+-------+
	1 row in set (0.00 sec)



我得到上面的预警信息是使用了以下脚本:

	#!/bin/sh
	cmd=/usr/local/mysql/bin/mysql
	mysqluser=test
	mysqlpwd=testpsd
	log=/opt/shell/slave_monitor.log
	hosts='10.10.1.101'
	master_host='10.10.1.10'
	m_port='3306'

	behind=`$cmd -u$mysqluser -p$mysqlpwd  -e "show slave status\G"|grep -iE "Seconds_Behind_Master"|awk '{print $2}'`

	m_file=`$cmd -h${master_host} -utest -ptest --port ${m_port}  -e "show master status\G"|grep -iE "File"|awk '{print $2}'`
	m_position=`$cmd  -h${master_host} -utest -ptest --port ${m_port}  -e "show master status\G"|grep -iE "Position"|awk '{print $2}'`
	s_status=`$cmd -u$mysqluser -p$mysqlpwd  -e "show slave status for channel 'master1'\G"|sed -n 1,23p `

	if [ "$behind1" -gt 0 ] 
	 then 
	   DingTalk.py  "$master_host:Seconds_Behind_Master：$behind.  Master file:$m_file Master Position:$m_position  $s_status"
	fi


3.打开慢查询跟踪，查看具体是什么SQL 很慢,逐个SQL 进行优化。
	mysql> show variables like 'log_slow_slave_statements';
	+---------------------------+-------+
	| Variable_name             | Value |
	+---------------------------+-------+
	| log_slow_slave_statements | OFF   |
	+---------------------------+-------+
	1 row in set (0.00 sec)

	mysql> 



4.某些表缺少主键或者唯一键则所有的SQL_THREAD会扫描全表并造成同步延迟。
所以需要确保表有主键或者唯一键。以下SQL 可查询是否有表没有主键索引：

mysql> select TABLE_SCHEMA,TABLE_NAME 
	from `information_schema`.`columns` c where TABLE_SCHEMA='hyjf_config' 
	GROUP BY TABLE_SCHEMA,TABLE_NAME 
	HAVING sum(if(column_key in ('PRI','UNI'), 1,0)) =0;

mysql几种日志

https://www.cnblogs.com/benbenhan/articles/13528551.html

• 重做日志（redo log）

  确保事务的持久性。redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

  • 内容：物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的。
  • **什么时候产生：**事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。
  • **什么时候释放：**当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。
  • **对应的物理文件：**默认情况下，对应的物理文件位于数据库的data目录下的ib_logfile1&ib_logfile2

• 回滚日志（undo log）

  作用：

  保证数据的原子性，保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

  内容：

  逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。

  什么时候产生：

  事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性

  什么时候释放：

  当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

  对应的物理文件：

  MySQL5.6之前，undo表空间位于共享表空间的回滚段中，共享表空间的默认的名称是ibdata，位于数据文件目录中。

  MySQL5.6之后，undo表空间可以配置成独立的文件，但是提前需要在配置文件中配置，完成数据库初始化后生效且不可改变undo log文件的个数

  如果初始化数据库之前没有进行相关配置，那么就无法配置成独立的表空间了。

• 二进制日志（binlog）

  mysql-binlog是MySQL数据库的二进制日志，用于记录用户对数据库操作的SQL语句（(除了数据查询语句）信息。可以使用mysqlbin命令查看二进制日志的内容

  作用：

  • 用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。
  • 用于数据库的基于时间点的还原。

  什么时候产生：

  • 事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中。

  • 这里与redo log很明显的差异就是redo log并不一定是在事务提交的时候刷新到磁盘，redo log是在事务开始之后就开始逐步写入磁盘。

  • 因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了bin_log的情况下，对于较大事务的提交，可能会变得比较慢一些。

  • 这是因为binlog是在事务提交的时候一次性写入的造成的，这些可以通过测试验证。

  什么时候释放：

  binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

• 慢查询日志
  慢日志记录执行时间过长和没有使用索引的查询语句，报错select、update、delete以及insert语句，慢日志只会记录执行成功的语句。

  1. 查看慢查询时间：
  　　show variables like “long_query_time”;默认10s

• 错误日志（errorlog）

  错误日志记录着mysqld启动和停止,以及服务器在运行过程中发生的错误的相关信息。在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出。
  　　指定日志路径两种方法:
  　　　　编辑my.cnf 写入 log-error=[path]
  　　　　通过命令参数错误日志 mysqld_safe –user=mysql –log-error=[path] &

• 普通查询日志 general query log

  记录了服务器接收到的每一个查询或是命令，无论这些查询或是命令是否正确甚至是否包含语法错误，general log 都会将其记录下来 ，记录的格式为 {Time ，Id ，Command，Argument }。也正因为mysql服务器需要不断地记录日志，开启General log会产生不小的系统开销。 因此，Mysql默认是把General log关闭的。

  查看日志的存放方式：show variables like ‘log_output’;

数据库高并发的解决方案

1. 在web服务框架中加入缓存。在服务器与数据库层之间加入缓存层，将高频访问的数据存入缓存中，减少数据库的读取负担。
2. 增加数据库索引。提高查询速度。（不过索引太多会导致速度变慢，并且数据库的写入会导致索引的更新，也会导致速度变慢）
3. 主从读写分离，让主服务器负责写，从服务器负责读。
4. 将数据库进行拆分，使得数据库的表尽可能小，提高查询的速度。
5. 使用分布式架构，分散计算压力。

MySQL 高并发环境解决方案 分库 分表 分布式 增加二级缓存。。。。。

需求分析：互联网单位 每天大量数据读取，写入，并发性高。

• 现有解决方式：水平分库分表，由单点分布到多点数据库中，从而降低单点数据库压力。
• 集群方案：解决 DB 宕机带来的单点 DB 不能访问问题。
• 读写分离策略：极大限度提高了应用中 Read 数据的速度和并发量。无法解决高写入压力。

优化 sql 语句的一般步骤

https://juejin.cn/post/6844903493635932167

定义执行效率较低的 sql 语句

1. 通过慢查询日志定位那些执行效率较低的 sql 语句，用 --log-slow-queries[=file_name] 选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 sql 语句的日志文件。

2. 慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候慢查询日志并不能定位问题，可以使用 show processlist 命令查看当前 mysql 在进行的线程，包括线程的状态、是否锁表等，可以实时的查看 sql 的执行情况，同时对一些锁表操作进行优化。

通过 explain 分析低效 sql 的执行计划

执行explain 后再执行 show warnings，可以看到sql 真正被执行之前优化器做了哪些 sql 改写

索引的底层实现

数据库的索引是使用B+树来实现的。

• 磁盘读取依靠的是机械运动，分为寻道时间、旋转延迟、传输时间三个部分，这三个部分耗时相加就是一次磁盘IO的时间，大概9ms左右。这个成本是访问内存的十万倍左右；
• 正是由于磁盘IO是非常昂贵的操作，所以计算机操作系统对此做了优化：预读；每一次IO时，不仅仅把当前磁盘地址的数据加载到内存，同时也把相邻数据也加载到内存缓冲区中。因为局部预读原理说明：当访问一个地址数据的时候，与其相邻的数据很快也会被访问到。每次磁盘IO读取的数据我们称之为一页（page）。一页的大小与操作系统有关，一般为4k或者8k。这也就意味着读取一页内数据的时候，实际上发生了一次磁盘IO。

sql 查询语句确定创建哪种类型的索引，如何优化查询

• 性能优化过程中，选择在哪个列上创建索引是最重要的步骤之一，可以考虑使用索引的主要有两种类型的列：在 where 子句中出现的列，在 join 子句中出现的列。
• 考虑列中值的分布，索引的列的基数越大，索引的效果越好。
• 使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度。
• 利用最左前缀,顾名思义，就是最左优先，在多列索引，有体现:(ALTER TABLE people ADD INDEX lnamefnameage (lame,fname,age)😉，所谓最左前缀原则就是先要看第一列，在第一列满足的条件下再看左边第二列，以此类推
• 不要过度建索引，只保持所需的索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。
• 在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。
• MySQL 只对一下操作符才使用索引：<,<=,=,>,>=,between,in
• 以及某些时候的 like(不以通配符%或_开头的情形)。

B树与二叉树的对比

数据库索引是存储在磁盘上，当表中的数据量比较大时，索引的大小也跟着增长，达到几个G甚至更多。当我们利用索引进行查询的时候，不可能把索引全部加载到内存中，只能逐一加载每个磁盘页，这里的磁盘页就对应索引树的节点。每个结点加载一次磁盘io，减少磁盘IO的次数就必须要压缩树的高度，让瘦高的树尽量变成矮胖的树。

• 平衡二叉树由于树深度过大而造成磁盘IO读写过于频繁，进而导致效率低下。为了减少磁盘ＩＯ的次数，就你必须降低树的深度
• B树这种数据结构常常用于实现数据库索引，查找效率比较高

MySQL B+Tree 索引和 Hash 索引的区别？

• Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位;
• B+树索引需要从根节点到枝节点，最后才能访问到页节点这样多次的 IO 访问;

B+树索引和哈希索引的明显区别是：

• 如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；

• 如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；

• 同理，哈希索引没办法利用索引完成排序，以及 like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；

• 哈希索引也不支持多列联合索引的最左匹配规则；

B+树索引的关键字检索效率比较平均，不像 B 树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

在大多数场景下，都会有范围查询、排序、分组等查询特征，用 B+树索引就可以了。

Hash 索引

1. Hash 索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询,因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和 Hash 运算前完全一样；
2. Hash 索引无法被用来避免数据的排序操作，因为 Hash 值的大小关系并不一定和 Hash 运算前的键值完全一样；
3. Hash 索引不能利用部分索引键查询，对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用；
4. Hash 索引在任何时候都不能避免表扫描，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要回表查询数据；
5. Hash 索引遇到大量 Hash 值相等的情况后性能并不一定就会比 B+树索引高。

B+Tree 索引

MySQL 中，只有 HEAP/MEMORY 引擎才显示支持 Hash 索引。

常用的 InnoDB 引擎中默认使用的是 B+树索引，它会实时监控表上索引的使用情况，如果认为建立哈希索引可以提高查询效率，则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引（在 InnoDB 中默认开启自适应哈希索引），通过观察搜索模式，MySQL 会利用 index key 的前缀建立哈希索引，如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询。

为什么用B+树，为什么不用红黑树和B树

B+树是一种特殊的平衡多路树，是B树的优化改进版本，它把所有的数据都存放在叶节点上，中间节点保存的是索引。这样一来相对于B树来说，减少了数据对中间节点的空间占用，使得中间节点可以存放更多的指针，使得树变得更矮，深度更小，从而减少查询的磁盘IO次数，提高查询效率。另一个是由于叶节点之间有指针连接，所以可以进行范围查询，方便区间访问。

而红黑树是二叉的，它的深度相对B+树来说更大，更大的深度意味着查找次数更多，更频繁的磁盘IO，所以红黑树更适合在内存中进行查找。

B树和B+树的区别

这都是由于B+树和B具有不同的存储结构所造成的区别，以一个m阶树为例。

1. 关键字的数量不同；B+树中分支结点有m个关键字，其叶子结点也有m个，其关键字只是起到了一个索引的作用，但是B树虽然也有m个子结点，但是其只拥有m-1个关键字。
2. 存储的位置不同；B+树中的数据都存储在叶子结点上，也就是其所有叶子结点的数据组合起来就是完整的数据，但是B树的数据存储在每一个结点中，并不仅仅存储在叶子结点上。
3. 分支结点的构造不同；B+树的分支结点仅仅存储着关键字信息和子树数据的指针（这里的指针指的是磁盘块的偏移量），也就是说内部结点仅仅包含着索引信息。
4. 查询不同；B树在找到具体的数值以后，则结束，而B+树则需要通过索引找到叶子结点中的数据才结束，也就是说B+树的搜索过程中走了一条从根结点到叶子结点的路径。

B+树相比B树优点

• B+树的分支结点仅仅存储着关键字信息和子树数据的指针，本身的占用空间较小，因此可以容纳更多节点元素，也就是说同样数据情况下，B+ 树会 B 树更加“矮胖”，因此查询效率更快。

• 查找某个范围的数据，只需在B+树的叶子节点链表中遍历即可，不需要像B 树那样挨个中序遍历比较大小。

总结来说，B+树的优点就是：

1. 层级更低，IO 次数更少；
2. 每次都需要查询到叶子节点；
3. 查询性能稳定叶子节点形成有序链表，范围查询方便

B树主要应用于文件系统以及部分数据库索引，如MongoDB，所以目前大部分关系型数据库索引是使用B+树实现。

• B树：有序数组+平衡多叉树
• B+树：有序数组链表+平衡多叉树

Mysql的innodb是以页为存储单位的，每个B+Tree的叶子节点都是一个页的大小的倍数，默认一页的大小是16K

也就是每一个页都包含两个页指针，一个是previous page指针，指向上一个页，一个是next page指针，指向下一个页。
头部还有Page的类型信息和用来唯一标识Page的编号。根据这个指针分布可以想象到Page链接起来就是一个双向链表。

由于一个B+Tree的叶子节点是一个页，所以每个叶子节点之间是一个双向链表的结构。

B+树一个节点有多大？一千万条数据，B+树多高？

• B+树一个节点的大小设为一页或页的倍数最为合适。因为如果一个节点的大小 < 1页，那么读取这个节点的时候其实读取的还是一页，这样就造成了资源的浪费。
• 在 MySQL 中 B+ 树的一个节点大小为“1页”，也就是16k。之所以设置为一页，是因为对于大部分业务，一页就足够了：
  • 首先InnoDB的B+树中，非叶子节点存的是key + 指针；叶子节点存的是数据行。
  • 对于叶子节点，如果一行数据大小为1k，那么一页就能存16条数据；对于非叶子节点，如果key使用的是bigint，则为8字节，指针在mysql中为6字节，一共是14字节，则16k能存放 16 * 1024 / 14 = 1170 个索引指针。于是可以算出，对于一颗高度为2的B+树，根节点存储索引指针节点，那么它有1170个叶子节点存储数据，每个叶子节点可以存储16条数据，一共 1170 x 16 = 18720 条数据。而对于高度为3的B+树，就可以存放 1170 x 1170 x 16 = 21902400 条数据（两千多万条数据），也就是对于两千多万条的数据，我们只需要高度为3的B+树就可以完成，通过主键查询只需要3次IO操作就能查到对应数据。所以在 InnoDB 中B+树高度一般为3层时，就能满足千万级的数据存储，所以一个节点为1页，也就是16k是比较合理的。

INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN

SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）、SQL LEFT JOIN、SQL RIGHT JOIN、SQL FULL JOIN，其中前一种是内连接，后三种是外链接。

**内连接：**指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件

select column_name(s)
from table 1
INNER JOIN table 2
ON
table 1.column_name=table 2.column_name

INNER JOIN产生的结果集中，是1和2的交集。

**外连接：**连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接。

左外连接：左边表数据行全部保留，右边表保留符合连接条件的行。

LEFT JOIN返回左表的全部行和右表满足ON条件的行，如果左表的行在右表中没有匹配，那么这一行右表中对应数据用NULL代替。

LEFT JOIN产生表1的完全集，而2表中匹配的则有值，没有匹配的则以null值取代。

右外连接：右边表数据行全部保留，左边表保留符合连接条件的行。

RIGHT JOIN返回右表的全部行和左表满足ON条件的行，如果右表的行在左表中没有匹配，那么这一行左表中对应数据用NULL代替。

RIGHT JOIN产生表2的完全集，而1表中匹配的则有值，没有匹配的则以null值取代。

全外连接：左外连接 union 右外连接。

UNION操作法来组合数条SQL查询，UNION从查询集中自动去除重复的行

• FULL JOIN 会从左表 和右表 那里返回所有的行。如果其中一个表的数据行在另一个表中没有匹配的行，那么对面的数据用NULL代替
• FULL OUTER JOIN产生1和2的并集。但是需要注意的是，对于没有匹配的记录，则会以null做为值。

主键 外键 唯一键 自增主键

主键 PRIMARY KEY

• 数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（NULL）。

外键 FOREIGN KEY

• 在一个表中存在的另一个表的KEY(可以是另外一个表的主键或唯一键)称此表的外键。

唯一键 UNIQE

• 保证字段的唯一性，可以为空

自增主键 AUTO_INCREMENT

• 自增列每增加一行自动增量，每个表只能有一个自增列，
• 一般将主键设置为自增列，又叫自增主键
• 只能是数值型，delete删除自增列后，若再次插入，从断开处插入
• truncate删除后，再次插入从1开始

为什么要使用自增ID作为主键

• 什么是自增ID（MySQL数据库）

  • 自增ID是在设计表时如果将id字段的值设置为自增的形式也就是AUTO_INCREMENT， 那么当插入一行数据时就无需指定id，数据表会根据前一个id值+1进行填充。
  • 指定了AUTO_INCREMENT的列必须要建索引，一般把ID作为主键，这样系统会自动为ID建立索引

• 自增ID有什么好处？

  • 增加记录时，可以不用指定id字段，不用担心主键重复问题
  • 数据库自动编号，速度快，而且是增量增长，按顺序存放，对于检索非常有利
  • 数字型，占用空间小，易排序，在程序中传递也方便

数据库范式

范式之间的关系，第一范式包含第二范式，第二范式包含第三范式。这里只举反例，因为实际的数据在设计数据库表的时候，范式都是相对的。

• 第一范式 1NF
  • 确保每列保持原子性，所有字段值都是不可分解的原子值
  • 比如，学生包括学号、姓名；地址包括省份，城市。若在使用中经常访问学号，城市等字段，则建表时，不能把学生、地址作为字段，若将其作为字段，则不满足第一范式。
  • 将学号、城市等作为字段，符合第一范式（相对来讲，因为城市还是可以可分的，只是平时访问的多，也不会访问城市以下的字段）
• 第二范式 2NF
  • **需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）；**或者说每一个非主属性都完全函数依赖与任何一个主键
  • 比如，学号，课程号，学院，成绩建一个数据库表。此时（学号，课程号）为主键，也称联合主键，此时只能当学号，课程号两者都确定的情况下，才能确定成绩。但学院只与学号有关，因此部分依赖与联合主键，这种情况不符合第二范式。
• 第三范式 3NF
  • **数据表中的每一列数据都和主键直接相关，而不能间接相关；**或每一个非主属性既不传递依赖主键，也不部分依赖主键。
  • 订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID），CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合3NF。

索引失效场景

创建了索引并不意味着高枕无忧，在很多场景下，索引会失效。下面列举了一些导致索引失效的情形，是我们写SQL语句时应尽量避免的。

1、条件字段原因

单字段有索引，WHERE条件使用多字段(含带索引的字段)，例如 SELECT * FROM student WHERE name ='张三' AND addr = '北京市'语句，如果name有索引而addr没索引，那么SQL语句不会使用索引。

多字段索引，违反最佳左前缀原则。例如，student表如果建立了(name,addr,age)这样的索引，WHERE后的第一个查询条件一定要是name，索引才会生效。

2、<>、NOT、in、not exists

当查询条件为等值或范围查询时，索引可以根据查询条件去找对应的条目。否则，索引定位困难(结合我们查字典的例子去理解)，执行计划此时可能更倾向于全表扫描，这类的查询条件有：<>、NOT、in、not exists

3、查询条件中使用OR

如果条件中有or，即使其中有条件带索引也不会使用(因此SQL语句中要尽量避免使用OR)。要想使用OR，又想让索引生效，只能将OR条件中的每个列都加上索引。

4、查询条件使用LIKE通配符

SQL语句中，使用后置通配符会走索引，例如查询姓张的学生(SELECT * FROM student WHERE name LIKE ‘张%’)，而前置通配符(SELECT * FROM student WHERE name LIKE ‘%东’)会导致索引失效而进行全表扫描。

5、索引列上做操作(计算，函数，(自动或者手动)类型装换)

有以下几种例子：

在索引列上使用函数：例如select * from student where upper(name)='ZHANGFEI';会导致索引失效，而select * from student where name=upper('ZHANGFEI');是会使用索引的。

在索引列上计算：例如select * from student where age-1=17;

6、在索引列上使用mysql的内置函数，索引失效

例如，SELECT * FROM student WHERE create_time

7、索引列数据类型不匹配

例如，如果age字段有索引且类型为字符串(一般不会这么定义，此处只是举例)但条件值为非字符串，索引失效，例如SELECT * FROM student WHERE age=18会导致索引失效。

8、索引列使用IS NOT NULL或者IS NULL可能会导致无法使用索引

B-tree索引IS NULL不会使用索引，IS NOT NULL会使用，位图索引IS NULL、IS NOT NULL都会使用索引。

最后，对索引的使用做一个总结吧：

索引有利于查询，但不能随意加索引，因为索引不仅会占空间，而且需要在写库时进行维护。

如果多个字段常常需要一起查询，那么在这几个字段上建立联合索引是个好办法，同时注意最左匹配原则。

不要在重复度很高的字段上加索引，例如性别。

避免查询语句导致索引失效，哪些情况会导致索引失效请见前文。

聚集索引和非聚集索引

聚集（clustered）索引：数据行的物理顺序与列值（一般是主键的那一列）的逻辑(索引）顺序相同，一个表中只能拥有一个聚集索引。因为一个表的物理顺序只有一种情况

聚集索引的好处了，索引的叶子节点就是对应的数据节点（MySQL的MyISAM除外，此存储引擎的聚集索引和非聚集索引只多了个唯一约束，其他没什么区别），可以直接获取到对应的全部列的数据，而非聚集索引在索引没有覆盖到对应的列的时候需要进行二次查询

聚簇索引默认是主键，如果表中没有定义主键，InnoDB会选择一个唯一的非空索引代替

创建聚集索引

如果不创建索引，系统会自动创建一个隐含列作为表的聚集索引。

1.创建表的时候指定主键（注意：SQL Sever默认主键为聚集索引，也可以指定为非聚集索引，而MySQL里主键就是聚集索引）

create table t1(
	id int primary key,
	name nvarchar(255)
)

2.创建表后添加聚集索引

SQL Server

create clustered index clustered_index on table_name(colum_name)

MySQL

alter table table_name add primary key(colum_name)

值得注意的是，最好还是在创建表的时候添加聚集索引，由于聚集索引的物理顺序上的特殊性，因此如果再在上面创建索引的时候会根据索引列的排序移动全部数据行上面的顺序，会非常地耗费时间以及性能。

非聚集（unclustered）索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引

非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。

MySQL中InnoDB的一级索引、二级索引

每个InnoDB表具有一个特殊的索引称为聚簇索引（也叫聚集索引，聚类索引，簇集索引）。如果表上定义有主键，该主键索引就是聚簇索引。如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

表中的聚簇索引（clustered index ）就是一级索引，除此之外，表上的其他非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。

索引最左前缀/最左匹配

最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

最左前缀原则，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

联合索引

所谓联合索引，也称多列所谓，就是建立在多个字段上的索引，这个概念是跟单列索引相对的。联合索引依然是B+树，但联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。

注意点：

• 联合索引要遵从最左前缀原则，否则不会用到索引
• Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。
• 如索引是 index (a,b,c)，可以支持 a 或 a,b 或 a,b,c 3种组合进行查找，但不支持 b,c 进行查找

为什么会形成最左匹配原则

最左匹配原则都是针对联合索引来说的

索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树

假如创建一个（a,b)的联合索引

• 可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。
• 发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的

假如我们对a b c三个字段建立了联合索引，在联合索引中，从最左边的字段开始，任何连续的索引都能匹配上，当遇到范围查询的时候停止。比如对于联合索引index(a,b,c),能匹配a,ab,abc三组索引。并且对查询时字段的顺序没有限制，也就是a,b,c; b,a,c; c,a,b; c,b,a都可以匹配。

1.匹配范围值

select * from table_name where a > 1 and a < 3
可以对最左边的列进行范围查询

select * from table_name where a > 1 and a < 3 and b > 1;
多个列同时进行范围查找时，只有对索引最左边的那个列进行范围查找才用到B+树索引，也就是只有a用到索引，在1<a<3的范围内b是无序的，不能用索引，找到1<a<3的记录后，只能根据条件 b > 1继续逐条过滤

2. 精确匹配某一列并范围匹配另外一列

如果左边的列是精确查找的，右边的列可以进行范围查找

select * from table_name where a = 1 and b > 3;
a=1的情况下b是有序的，进行范围查找走的是联合索引

3.最左匹配
建立索引（A,B,C）

select * from table_name where b = ‘2’
select * from table_name where c = ‘3’
select * from table_name where b = ‘1’ and c = ‘3’
这些没有从最左边开始，最后查询没有用到索引，用的是全表扫描

select * from table_name where a = ‘1’ and c = ‘3’
如果不连续时，只用到了a列的索引，b列和c列都没有用到

MySQL逻辑架构

mysql的逻辑架构分为3层，

• 连接/线程处理：大多数基于网络的客户端/服务器的工具或服务都有类似的架构。比如连接处理、授权认证、安全等等。
• 服务器的核心功能，查询解析、分析、优化、缓存以及所有的内置函数。所有跨引擎的功能都在这一层实现。
• 存储引擎，负责MySQL中数据的存储和提取，每个存储引擎都有它的优势和劣势，服务器通过API与存储引擎进行通信，这些接口屏蔽了不同存储引擎之间的差异。

MySQL索引

三大优点

• 索引大大减少了服务器需要扫描的数据量；

• 索引可以帮助服务器避免排序和创建临时表(B+索有序)；

• 索引可以将随机I/O变为顺序I/O(B+有序的,会将相邻的数据存储在一起)

索引分类

MySQL索引分为普通索引、唯一索引、主键索引、组合索引、全文索引。索引不会包含有null值的列，索引项可以为null（唯一索引、组合索引等），但是只要列中有null值就不会被包含在索引中。

• 普通索引

  create index index_name on table(column)；
  create table(..., index index_name column);#创建表时指定
  

• 唯一索引：类似普通索引，索引列的值必须唯一（可以为空，这点和主键索引不同）

• 主键索引：特殊的唯一索引，不允许为空，只能有一个，一般是在建表时指定primary key(column)

• 组合索引：在多个字段上创建索引，遵循最左前缀原则。alter table t add index index_name(a,b,c);

• 全文索引：主要用来查找文本中的关键字，不是直接与索引中的值相比较，像是一个搜索引擎，配合match against使用，现在只有char，varchar，text上可以创建全文索引。在数据量较大时，先将数据放在一张没有全文索引的表里，然后再利用create index创建全文索引，比先生成全文索引再插入数据快很多。

什么样的字段适合创建索引

• 主键自动建立唯一索引
• 经常需要范围查找的列
• 经常作表连接的字段，比如外键
• 经常出现在order by, group by, distinct 后面排序或分组的字段

哪些不适合建立索引

• 数据记录太少的表 <300w
• 经常增删改的表
• 数据重复且分布平均的表字段，如性别和国籍
• text，image等类型不应该建立索引，这些列的数据量大（假如text前10个字符唯一，也可以对text前10个字符建立索引）
• MySQL能估计出全表扫描比使用索引更快时，不使用索引

MySQL建立索引/主键索引，删除索引？

MySQL建立索引有两种方式：用alter table或者create index。

alter table table_name add primary key(column_list) #添加一个主键索引
alter table table_name add index (column_list)      #添加一个普通索引
alter table table_name add unique (column_list)     #添加一个唯一索引

create index index_name on table_name (column_list)   #创建一个普通索引
create unique index_name on table_name (column_list)  #创建一个唯一索引

Mysql删除索引同样也有两种方式：alter table 和 drop index

alter table table_name drop index index_name    #删除一个普通索引
alter table table_name drop primary key         #删除一个主键索引

drop index index_name on table table_name

Mysql的优化（索引优化，性能优化）

高频访问：

• 分表分库：将数据库表进行水平拆分，减少表的长度
• 增加缓存： 在web和DB之间加上一层缓存层
• 增加数据库的索引：在合适的字段加上索引，解决高频访问的问题

并发优化：

• 主从读写分离：只在主服务器上写，从服务器上读
• 负载均衡集群：通过集群或者分布式的方式解决并发压力

索引优化

独立的列

索引列不能是表达式的一部分，也不能是函数的参数。例如：SELECT actor_id FROM actor WHERE actor_id + 1 = 5;或者SELECT actor_id FROM actor WHERE f(actor_id) = 5;

前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得很大且很慢。此时可以有两个策略，一个是自定义哈希索引，另一个就是前缀索引；

• 前缀索引能大大节约索引空间，从而提高索引效率，但这样也会降低索引的选择性（索引选择性——不重复的索引值和数据表记录总数的比值）

• 选择足够长的前缀以保证较高的选择性,同时又不能太长

  缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描；

多列索引

• 在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。

  SELECT film_id, actor_ id FROM sakila.film_actor
  WHERE actor_id = 1 AND film_id = 1;
  

选择合适的索引顺序

正确的索引顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要；索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY 、GROUP BY和DISTINCT等子句的查询需求；

• 索引列顺序的选择——在不考虑分组和排序的情况下，将选择性最高的列放到索引最前面（经验法则）,这时候索引的作用只是用于优化WHERE 条件的查找

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式

聚簇索引总是把数据行存储在叶子页中，因此一个表中只能有一个聚簇索引。并不是所有的存储引擎都支持聚簇索引，这里我们主要讨论InnoDB，在InnoDB中，聚簇索引其实就是主键索引。如果表中没有定义主键，InnoDB会选择一个唯一的非空索引作为主键。如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引。聚簇索引的优点如下：

• 可以把相关数据保存在一起。
• 数据访问更快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

如果表在设计和查询的时候能充分利用以上特点，将会极大提高性能。当然，聚簇索引也有它的缺点：

• 聚簇索引最大限度提高了I/O密集型应用的性能，但如果所有的数据都存放在内存中，聚簇索引就没有优势了。
• 插入速度严重依赖插入顺序。这也是为什么InnoDB一般都会设置一个自增的int列作为主键。
• 更新聚簇索引的代价很高，因为会强制InnoDB将每个被更新的行移到新的位置。
• 如果不安顺序插入新数据时，可能会导致"页分裂"。
• 二级索引可能会比想象的更大。因为在二级索引的页子节点中包含了引用行的主键列。
• 二级索引访问可能会需要进行回表查询。

覆盖索引

如果一个索引包含或覆盖所有需要查询的字段值，我们就称之为“覆盖索引”

判断标准

使用explain，可以通过输出的extra列来判断，对于一个索引覆盖查询，显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询

锁的叶子节点包含需要查询的数据，不需要回表查询

什么是回表查询呢？

• 二级索引在页子节点中保存的并不是指向行的物理位置的指针，而是行的主键值。那么如果此次查询不是覆盖查询，就会利用二级索引页子节点中保存的行主键值再去表里进行二次查询

InnoDB

1、覆盖索引查询时除了除了索引本身的包含的列，还可以使用其默认的聚集索引列

2、这跟INNOB的索引结构有关系，主索引是B+树索引存储，也即我们所说的数据行即索引，索引即数据

3、对于INNODB的辅助索引，它的叶子节点存储的是索引值和指向主键索引的位置，然后需要通过主键在查询表的字段值，所以辅助索引存储了主键的值

4、覆盖索引也可以用上INNODB 默认的聚集索引

5、innodb引擎的所有储存了主键ID，事务ID，回滚指针,非主键ID，他的查询就会是非主键ID也可覆盖来取得主键ID

覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不用读取数据有以下一些优点

• 索引项通常比记录要小，所以MySQL访问更少的数据
• 索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O
• 大多数据引擎能更好的缓存索引，比如MyISAM只缓存索引
• 覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了

MySQL 执行一条查询语句的内部执行过程？

• 连接器：客户端先通过连接器连接到 MySQL 服务器。
• 缓存：连接器权限验证通过之后，先查询是否有查询缓存，如果有缓存（之前执行过此语句）则直接返回缓存数据，如果没有缓存则进入分析器。
• 分析器：分析器会对查询语句进行语法分析和词法分析，判断 SQL 语法是否正确，如果查询语法错误会直接返回给客户端错误信息，如果语法正确则进入优化器。
• 优化器：优化器是对查询语句进行优化处理，例如一个表里面有多个索引，优化器会判别哪个索引性能更好。
• 执行器：优化器执行完就进入执行器，执行器就开始执行语句进行查询比对了，直到查询到满足条件的所有数据，然后进行返回。

Sql语句比较慢，可能有那些原因

一个 SQL 执行的很慢，我们要分两种情况讨论：

1、大多数情况下很正常，偶尔很慢，则有如下原因

(1)、数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。

(2)、执行的时候，遇到锁，如表锁、行锁。

2、这条 SQL 语句一直执行的很慢，则有如下原因。

(1)、没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。

(2)、数据库选错了索引。

一条 SQL 大多数情况正常，偶尔才能出现很慢的情况，针对这种情况，我觉得这条SQL语句的书写本身是没什么问题的，而是其他原因导致的，那会是什么原因呢？

1.数据库在刷新脏页（flush）

当我们要往数据库插入一条数据、或者要更新一条数据的时候，我们知道数据库会在内存中把对应字段的数据更新了，但是更新之后，这些更新的字段并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到 redo log 日记中去，等到空闲的时候，在通过 redo log 里的日记把最新的数据同步到磁盘中去。

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

刷脏页有下面4种场景（后两种不用太关注“性能”问题）：

• **redolog写满了：**redo log 里的容量是有限的，如果数据库一直很忙，更新又很频繁，这个时候 redo log 很快就会被写满了，这个时候就没办法等到空闲的时候再把数据同步到磁盘的，只能暂停其他操作，全身心来把数据同步到磁盘中去的，而这个时候，就会导致我们平时正常的SQL语句突然执行的很慢，所以说，数据库在在同步数据到磁盘的时候，就有可能导致我们的SQL语句执行的很慢了。
• **内存不够用了：**如果一次查询较多的数据，恰好碰到所查数据页不在内存中时，需要申请内存，而此时恰好内存不足的时候就需要淘汰一部分内存数据页，如果是干净页，就直接释放，如果恰好是脏页就需要刷脏页。
• **MySQL 认为系统“空闲”的时候：**这时系统没什么压力。
• **MySQL 正常关闭的时候：**这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

拿不到锁

要执行的这条语句，刚好这条语句涉及到的表，别人在用，并且加锁了，我们拿不到锁，只能慢慢等待别人释放锁了。或者，表没有加锁，但要使用到的某个一行被加锁了，这个时候，我也没办法啊。

如果要判断是否真的在等待锁，我们可以用 show processlist这个命令来查看当前的状态

如果在数据量一样大的情况下，这条 SQL 语句每次都执行的这么慢，那就就要好好考虑下你的 SQL 书写了，下面我们来分析下哪些原因会导致我们的 SQL 语句执行的很不理想。

没有用上索引，我觉得这个原因是很多人都能想到的，例如你要查询这条语句

select * from t where 100 <c and c < 100000;

刚好你的 c 字段上没有索引，那么抱歉，只能走全表扫描了，你就体验不会索引带来的乐趣了，所以，这回导致这条查询语句很慢

正确的查询应该如下

select * from t where c = 1000 + 1;

如果我们在查询的时候，对字段进行了函数操作，也是会导致没有用上索引的，例如

select * from t where pow(c,2) = 1000;

主键索引和非主键索引是有区别的，主键索引存放的值是整行字段的数据，而非主键索引上存放的值不是整行字段的数据，而且存放主键字段的值

也就是说，我们如果走 c 这个字段的索引的话，最后会查询到对应主键的值，然后，再根据主键的值走主键索引，查询到整行数据返回。

好吧扯了这么多，其实我就是想告诉你，就算你在 c 字段上有索引，系统也并不一定会走 c 这个字段上的索引，而是有可能会直接扫描扫描全表，找出所有符合 100 < c and c < 100000 的数据。

Sql语句比较慢，如何定位哪一句慢

MySQL通过慢查询日志定位那些执行效率较低的SQL 语句，用–log-slow-queries[=file_name]选项启动时，mysqld 会写一个包含所有执行时间超过long_query_time 秒的SQL语句的日志文件，通过查看这个日志文件定位效率较低的SQL

MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句

• slow_query_log

这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。

• long_query_time

当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短。

• slow_query_log_file

记录日志的文件名。

• log_queries_not_using_indexes

这个参数设置为ON，可以捕获到所有未使用索引的SQL语句，尽管这个SQL语句有可能执行得挺快。

检测mysql中sql语句的效率的方法

1. Linux下启用MySQL慢查询

MySQL在Windows系统中的配置文件一般是是my.cnf找到[mysqld]下面加上

log-slow-queries=/data/mysqldata/slowquery。log
long_query_time=2

说明

log-slow-queries = F:/MySQL/log/mysqlslowquery。

为慢查询日志存放的位置，一般这个目录要有MySQL的运行帐号的可写权限，一般都将这个目录设置为MySQL的数据存放目录；
long_query_time=2中的2表示查询超过两秒才记录；

2.show processlist 命令

WSHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。

各列的含义和用途：

ID列

一个标识，你要kill一个语句的时候很有用，用命令杀掉此查询 /*/mysqladmin kill 进程号。

user列

• 显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。

host列

• 显示这个语句是从哪个ip的哪个端口上发出的。用于追踪出问题语句的用户。

db列

• 显示这个进程目前连接的是哪个数据库。

command列

• 显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。

time列

• 此这个状态持续的时间，单位是秒。

state列

• 显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个 sql语句，以查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成

info列

• 显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

3.explain来了解SQL执行的状态

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

使用方法，在select语句前加上explain就可以了：

explain select surname,first_name form a,b where a.id=b.id

EXPLAIN列的解释

table

• 显示这一行的数据是关于哪张表的

type

• 这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

possible_keys

• 显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key

• 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句 中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len

• 使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref

• 显示索引的哪一列被使用了，如果可能的话，是一个常数

rows

• MYSQL认为必须检查的用来返回请求数据的行数

Extra

• 关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

MySQL - 查看慢SQL

查看MySQL是否启用了查看慢SQL的日志文件

mysql> show variables like 'log_slow_queries'; 

查看执行慢于多少秒的SQL会记录到日志文件中

mysql> show variables like 'long_query_time';

配置my.ini文件（inux下文件名为my.cnf）， 查找到[mysqld]区段，增加日志的配置，如下示例：

[mysqld]
log="C:/temp/mysql.log"
log_slow_queries="C:/temp/mysql_slow.log"
long_query_time=1

• log指示日志文件存放目录；
• log_slow_queries指示记录执行时间长的sql日志目录；
• long_query_time指示多长时间算是执行时间长，单位s。

Linux下这些配置项应该已经存在，只是被注释掉了，可以去掉注释。但直接添加配置项也OK啦。
查询到效率低的 SQL 语句 后，可以通过 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序

查询优化

为什么查询速度会慢

通常来说，查询的生命周期大致可以分为以下顺序：从客户端，到服务器，然后在服务器上进行解析，优化后生成执行计划，执行，并返回结果给客户端。其中执行可以认为是整个生命周期最重要的阶段，这其中包含了大量为了检索数据到存储引擎的调用，以及调用后的数据处理，包括排序和分组。

在完成这些任务的时候，查询需要在不同的地方花费时间,包括网络、CPU计算，生成统计信息和执行计划、锁等待等操作，尤其是向底层存储引擎检索数据的调用操作。优化和查询的目的就是减少和消除这些操作所花费的时间。

慢查询的基础：优化数据访问

查询性能低下的最基本原因是访问的数据太多，大部分性能低下的查询可以通过减少访问的数据量的方式进行优化：

1. 确认程序是否在检索大量超过需要的数据，这通常意味着访问太多行，但有时也可能是访问太多列。
2. 确认MySQL服务器层是否在分析大量超过需求的数据。

重构查询方式

1. 切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);

rows_affected = 0
do {
    rows_affected = do_query(
    "DELETE FROM messages WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0

2. 分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

• 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
• 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
• 查询分解后,执行单个查询可减少锁竞争
• 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
• 查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。

SELECT * FROM tab
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';

SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

使用 Explain 进行分析

Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。

expalin 作用

通过explain可以知道mysql是如何处理语句，分析出查询或是表结构的性能瓶颈。通过

expalin可以得到：

• 表的读取顺序

• 表的读取操作的操作类型

• 哪些索引可以使用

• 哪些索引被实际使用

• 表之间的引用

• 每张表有多少行被优化器查询

Explain语法：explain select … from … [where …]

explain select * from news;

+----+-------------+-------+-------+-------------------+---------+---------+-------+------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------

id：这是SELECT的查询序列号

select_type：select_type就是select的类型，可以有以下几种：

SIMPLE：简单SELECT(不使用UNION或子查询等)
 
PRIMARY：最外面的SELECT
 
UNION：UNION中的第二个或后面的SELECT语句
 
DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
 
UNION RESULT：UNION的结果。
 
SUBQUERY：子查询中的第一个SELECT
 
DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
 
DERIVED：导出表的SELECT(FROM子句的子查询)

table：显示这一行的数据是关于哪张表的

type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一

结果值从好到坏依次是：
 
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
 
一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。
 
 
•system: const 的一种特例，表中只有一行数据
•const: 当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。使用主键查询往往就是 const 级别的，非常高效
•eq_ref: 最多只返回一条符合条件的记录，通过使用在两个表有关联字段的时候
•ref: 通过普通索引查询匹配的很多行时的类型
•fulltext: 全文索引
•ref_or_null: 跟 ref 类似的效果，不过多一个列不能 null 的条件
•index_merge: 此连接类型表示使用了索引合并优化。在这种情况下，输出行中的 key 列包含使用的索引列表，key_len包含所用索引的最长 key 部分列表
•unique_subquery: 在使用 in 查询的情况下会取代 eq_ref
•range: 范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符，用常量比较关键字列时,可以使用 range
•index: 类似全表扫描，只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多
•ALL: 全表扫描

possible_keys：列指出MySQL能使用哪个索引在该表中找到行

key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL

key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示使用哪个列或常数与key一起从表中选择行。

rows：显示MySQL认为它执行查询时必须检查的行数。

Extra：包含MySQL解决查询的详细信息，也是关键参考项之一

MYSQL数据库引擎，innodb和myisam的特点与区别

在MySQL 5.5之前，MyISAM是mysql的默认数据库引擎，之后的版本是InnoDB。

• InnoDB ： InnoDB是mysql的默认引擎，支持事务和外键，支持容灾恢复。适合更新频繁和多并发的表 、行级锁
• MyISAM ： 插入和查询速度比较高，支持大文件，但是不支持事务，适合在web和数据仓库场景下使用 表级锁
• MEMORY ： memory将表中的数据保存在内存里，适合数据比较小而且频繁访问的场景
• CSV
• blackhole

InnoDB

• 支持事务
• 有行级锁定和外键约束
• 不支持FULLTEXT类型的索引
• 没有保存表的行数
• 叶子结点是聚集索引

MyISAM

• 不支持事务
• 不支持行锁和外键，因此当INSERT或UPDATE数据时即写操作需要锁定整个表，效率便会低一些
• 叶子结点是非聚集索引

区别

• 事务
  • InnoDB支持，MyISAM不支持
• 行数
  • InnoDB没有保存表的行数，MyISAM保存了表的行数，可以直接读取
• 索引存储
  • InnoDB是聚集索引，MyISAM是非聚集索引
• 外键
  • InnoDB支持，MyISAM不支持
• 锁
  • InnoDB支持行锁，表锁。行锁可以提高多用户并发操作，但InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的
  • MyISAM支持表锁

如何选择：

1. 是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；
2. 如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB。
3. 系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB；
4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的。如果你不知道用什么存储引擎，那就用InnoDB，至少不会差。

MEMORY

MEMORY 是 MySQL 中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。这些特性与前面的两个很不同。

每个基于 MEMORY 存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为 frm 类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。值得注意的是，服务器需要有足够的内存来维持 MEMORY 存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。

MEMORY 默认使用哈希索引。速度比使用 B 型树索引快。当然如果你想用 B 型树索引，可以在创建索引时指定。

注意，MEMORY 用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY 的表的生命周期很短，一般是一次性的。

Mysql的表空间方式，各自特点

• 共享表空间：指的是数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在 data 目录下。

• 独立表空间：每一个表都将会生成以独立的文件方式来进行存储。

  • 优点：当表被删除时这部分空间可以被回收；可以更快的恢复和备份单个表；将单个表复制到另一个实例会很方便；

  • 缺点：mysqld会维持很多文件句柄，表太多会影响性能。如果很多表都增长会导致碎片问题

事务

事务是怎么实现的

https://blog.csdn.net/u013256816/article/details/103966510

https://www.cnblogs.com/takumicx/p/9998844.html

事务就是一组逻辑操作的集合。实现事务就是要保证可靠性和并发隔离，或者说，能够满足ACID特性的机制。而这些主要是靠日志恢复和并发控制实现的。

• 日志恢复：数据库里有两个日志，一个是redo log，一个是undo log。redo log记录的是已经成功提交的事务操作信息，用来恢复数据，保证事务的持久性。undo log记录的是事务修改之前的数据信息，用来回滚数据，保证事务的原子性。
• 并发控制：并发控制主要靠读写锁和MVCC（多版本并发控制）来实现。读写锁包括共享锁和排他锁，保证事务的隔离性。MVCC通过为数据添加时间戳来实现。

事务处理

定义：用来维护数据库的完整性，保证成批的MySQL操作要么执行，要么完全不执行。在关系数据库中，事务可以是一条SQL语句，也可以是一组SQL语句。

MySQL使用下面的语句来表示事务的开始：

START TRANSACTION

使用ROLLBACK来回退（撤销）MySQL语句

控制事务

SELECT * FROM ordertotals;
-- 启动事务管理
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
-- 只能在一个事务处理内使用（在执行一条START TRANSACTION命令之后）
ROLLBACK;
SELECT * FROM ordertotals;

COMMIT

• 在事务处理中，提交不会隐含地进行。为了明确的提交，使用COMMIT语句。

• 当COMMIT或ROLLBACK语句执行后，事务会自动关闭（将来的更改会隐含提交）。

  START TRANSACTION;
  DELETE FROM orderitems WHERE order_num=20010;
  DELETE FROM orders WHERE order_num=20010;
  COMMIT;
  

  使用事务处理保证订单不被部分删除。最后commit仅在不出错时写出更改。如果第一条delete起作用，但第二条失败，则delete不会提交。

使用保留点

为了支持回退部分事务处理，必须能在事务处理模块中合适的位置放置占位符，如果需要回退，可以回退到某个占位符。这样需要回退，可以回退到某个占位符。
保留点越多越好，保留点越多，你就越能按自己的意愿灵活地进行回退。
保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放。也可以使用RELEASE SAVEPOINT明确地释放保留点。

SAVEPOINT delete1;

更改默认的提交行为

默认的mysql行为时自动提交所有的更改，任何时候执行一条mysql语句，改语句实际上都是针对表执行的，而且所做的更改立即生效。

-- autocommit是针对每个连接而不是服务器的。
SET autocommit = 0;# 不自动提交更改

事务的ACID特性

事务具有4个基本特征，分别是：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Duration），简称ACID。

• 原子性

  • 事务所包含的一系列数据库操作要么全部成功执行，要么全部回滚
  • 事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响

• 一致性：数据库事务的一致性是指：在事务开始以前，数据库中的数据有一个一致的状态。在事务完成后，数据库中的事务也应该保持这种一致性。事务应该将数据从一个一致性状态转移到另一个一致性状态。

  比如在银行转账操作后两个账户的总额应当不变。

• 隔离性：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。

• 持久性：事务完成后，事务对数据库的所有更新将被保存到数据库，即使系统崩溃，修改的数据页不会丢失。

事务的ACID是通过InnoDB日志和锁来保证。

• 事务的隔离性是通过数据库锁和MVCC的机制实现的

• 持久性通过redo log（重做日志）来实现

• 原子性和一致性通过Undo log（回撤日志）来实现。

  • Undo Log的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了roll back语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

  • 和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将RedoLog持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是RedoLog已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。

事务的并发问题

• 脏读
  • 一个事务读取了另一个事务未提交的数据；
  • 事务A读取事务B更新的数据，然后事务B回滚，此时事务A读到的是脏数据

• 不可重复读

  • 对于数据库中的某一个字段，一个事务多次查询却返回了不同的值，这是由于在查询的间隔中，该字段被另一个事务修改并提交了。

  比如A第一次查询自己的账户有1000元，此时另一个事务给A的账户增加了1000元，所以A再次读取他的账户得到了2000的结果，跟第一次读取的不一样。

• 幻读

  • 幻读的重点在于插入或者删除，同样条件下两次读出来的记录数不一样。
  • 事务A统计表中的数据，此时事务B想表中添加或删除了数据，当事务A再次统计表中的数据时，发现两次的记录不一样。
  • 幻读中的“读”即是针对当前读

避免不可重复读需要锁行，避免幻读则需要锁表。

“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本上可分为以下两种：

• 一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
• 另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称MVCC或MCC）

事务隔离级别

为了保证数据库事务一致性，解决脏读，不可重复读和幻读的问题，数据库的隔离级别一共有四种隔离级别：

• 读未提交。最低的隔离级别，会产生脏读，不可重复读，幻读问题

  • 事务读不加锁，不阻塞其他事务的读和写
  • 事务写阻塞其他事务写，但不阻塞其他事务读

• 读提交。会产生不可重复读，幻读问题

• 可重复读。确保事务可以多次从一个字段中读取相同的值，在此事务持续期间，禁止其他事务对此字段的更新，可以避免脏读和不可重复读，仍会出现幻读问题

  在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据

• 串行化。最高的隔离级别，在这个隔离级别下，不会产生任何异常。并发的事务，就像事务是在一个个按照顺序执行一样

  • 所有的SELECT语句隐式转化为SELECT……FOR SHARE ，即加共享锁
  • 读加共享锁，写加排他锁，读写互斥。如果有未提交的事务正在修改某些行，所有SELECT这些行的语句都会阻塞

MySql使用不同的锁策略(Locking Strategy)/MVCC来实现四种不同的隔离级别。

• 读提交、可重复读的实现原理跟MVCC有关
• 读未提交和串行化跟锁有关。

设置隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

mysql的默认隔离级别

https://blog.csdn.net/fuzhongmin05/article/details/91126936

• Mysql默认的事务隔离级别是可重复读(Repeatable Read)，，并且实现了所有四种隔离级别。在项目中一般用**读已提交(Read Commited)**这个隔离级别！
• Oracle的默认隔离级别是读已提交，实现了四种隔离级别中的读已提交和串行化隔离级别

MySQL默认采用可重复读隔离级别，MySQL通过nex-key lock在可重复读隔离级别下解决了幻读的问题

Next-Key锁是行锁和GAP（间隙锁）的合并

• 事务隔离级别：未提交读时，写数据只会锁住相应的行。
• 事务隔离级别为：可重复读时，写数据会锁住整张表。
• 事务隔离级别为：串行化时，读写数据都会锁住整张表。

查看MySQL数据库当前事务的隔离级别：

select @@tx_isolation;

在MySQL数据库中设置事务的隔离级别：

set  [glogal | session]  transaction isolation level 隔离级别名称;
set tx_isolation=’隔离级别名称;’

多版本并发控制MVCC

https://juejin.cn/post/6844904115353436174#heading-8

MVCC，中文叫多版本并发控制，它是通过读取历史版本的数据，来降低并发事务冲突，从而提高并发性能的一种机制。它的实现依赖于隐式字段、undo日志、快照读&当前读、Read View

多版本并发控制不支持myisam存储引擎。

不同的事务在并发过程中， SELECT 操作可以不加锁而是通过 MVCC 机制读取指定的版本历史记录，并通过一些手段保证保证读取的记录值符合事务所处的隔离级别，从而解决并发场景下的读写冲突。

在Mysql的InnoDB引擎中就是指在提交已读和可重复读两种隔离级别下的事务对于SELECT操作会访问版本链中的记录的过程。

InnoDB的MVCC实现机制

InnoDB存储引擎，每一行记录都有两个隐藏列DB_TRX_ID、DB_ROLL_PTR

• DB_TRX_ID，记录每一行最近一次修改（修改/更新）它的事务ID，大小为6字节；
• DB_ROLL_PTR，相当于一个指针，指向回滚段的undo日志，大小为7字节

多个事务并行操作某一行数据时，不同事务对该行数据的修改会产生多个版本，然后通过回滚指针（DB_ROLL_PTR）连一条Undo日志链。

Read View

Read View就是事务执行快照读时，会生成数据库系统当前的一个快照，记录当前系统中还有哪些活跃的读写事务，把它们放到一个列表里。也就是begin了还未提交的事务,通过这个列表来判断记录的某个版本是否对当前事务可见

• 如果访问的记录版本的事务id比当前列表最小的id小，表明生成该版本的事务在生成ReadView前已经提交(因为事务ID是递增的)，所以该版本可以被当前事务访问。

• 如果访问的记录版本的事务id比当前列表最小的id大，表明生成该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问。

• 如果你要访问的记录版本的事务id在列表id最大值和最小值之间，再判断是否在列表内，如果在就说明此事务还未提交，当前事务也是看不见的。如果不在那说明事务已经提交，当前事务是能看见的

可重复读每次读取数据前都生成一个ReadView，而已提交读只在第一次读取数据时生成一个ReadView。

Mysql的MVCC通过版本链，实现多版本，可并发读-写，写-读。通过ReadView生成策略的不同实现不同的隔离级别。

• 提交已读存在不可重复读问题

• 可重复读解决不可重复读问题

快照读&当前读

快照读：

读取的是记录数据的可见版本（有旧的版本），不加锁,普通的select语句都是快照读,如：

简单的select操作，没有lock in share mode或for update，快照读不会加任何的锁，而且由于mysql的一致性非锁定读的机制存在，任何快照读也不会被阻塞。但是如果事务的隔离级别是SERIALIZABLE的话，那么快照读也会被加上共享的next-key锁

select * from account where id>2;
复制代码

当前读：

读取的是记录数据的最新版本，显示加锁的都是当前读

就是insert，update，delete,select…in share mode和select…for update,当前读会在所有扫描到的索引记录上加锁，不管它后面的where条件到底有没有命中对应的行记录。当前读可能会引起死锁。

select * from account where id>2 lock in share mode;
select * from  account where id>2 for update

Mysql中有哪几种锁

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-avnl9DPa-1649155292013)(pic/mysql锁.jpg)]

如果按锁粒度划分，有以下3种：

• 表锁： 开销小，加锁快；锁定力度大，发生锁冲突概率高，并发度最低;不会出现死锁。
• 行锁： 开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。
• 页锁： 开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

读写锁（共享和排他锁）

读锁（共享锁）

是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

写锁（排他锁）

如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

SELECT * FROM table_name WHERE ... FOR UPDATE

注意事项

• 在使用读锁、写锁时都需要注意，读锁、写锁属于行级锁。即事务1 对商品A 获取写锁，和事务2 对商品B 获取写锁互相不会阻塞的。需要我们注意的是我们的SQL要合理使用索引，当我们的SQL 全表扫描的时候，行级锁会变成表锁。
• 使用EXPLAIN查看 SQL是否使用了索引，扫描了多少行

行页表锁

首先从锁的颗粒级别来看可分为三种：表级，页级，行级。从引擎的角度看Innodb支持表级锁和行级锁，myisam只支持表级锁。

行锁

行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。行锁又分共享锁和排他锁,由字面意思理解，就是给某一行加上锁，也就是一条记录加上锁。

注意：行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。

特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

2.表锁

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

特点：

• 开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

什么时候用表锁：

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁：

• （1）事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
• （2）事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。
  当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表了。

在InnoDB下，使用表锁要注意以下两点。

• （1）使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层──MySQL Server负责的，仅当autocommit=0、InnoDB_table_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁，否则，InnoDB将无法自动检测并处理这种死锁。
• （2）在用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。正确的方式见如下语句：
  例如，如果需要写表t1并从表t读，可以按如下做：

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

Innodb中的行锁与表锁

在Innodb引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表，什么时候或只锁住一行呢？

• 只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁
• innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的.

3.页级锁

表级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁.表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁

特点

• 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

乐观锁和悲观锁

按锁机制分有：乐观锁，悲观锁

乐观锁

乐观锁假设数据一般情况下不会造成冲突，在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回给用户错误的信息，让用户决定如何去做。

可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型,可以提高吞吐量

一般的做法用版本号机制实现。

• 即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

SELECT data AS old_data, version AS old_version FROM …;
//根据获取的数据进行业务操作，得到new_data和new_version
UPDATE SET data = new_data, version = new_version WHERE version = old_version
if (updated row > 0) {
    // 乐观锁获取成功，操作完成
} else {
    // 乐观锁获取失败，回滚并重试
}

CAS算法：java中的compareandswap即cas，解决多线程并行情况下使用锁造成性能损耗的一种机制。

• CAS操作包含三个操作数，内存位置（V）,预期原值（A）和新值（B）。如果内存位置的值与预期原值相匹配，那么处理器会西东将该位置值更新为新值。否则，处理器不做任何操作。

悲观锁

悲观锁假设数据在并发操作中一定会发生冲突，所以在数据开始读取的时候就把数据锁住

传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

悲观锁主要分为共享锁或排他锁

• 共享锁【Shared lock】又称为读锁，简称S锁。顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
• 排他锁【Exclusive lock】又称为写锁，简称X锁。顾名思义，排他锁就是不能与其他锁并存，如果一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据行读取和修改。

悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证

悲观锁和乐观锁是数据库用来保证数据并发安全防止更新丢失的两种方法，例子在select … for update前加个事务就可以防止更新丢失。悲观锁和乐观锁大部分场景下差异不大，一些独特场景下有一些差别，一般我们可以从如下几个方面来判断。

• 响应速度：如果需要非常高的响应速度，建议采用乐观锁方案，成功就执行，不成功就失败，不需要等待其他并发去释放锁。
• 冲突频率：如果冲突频率非常高，建议采用悲观锁，保证成功率，如果冲突频率大，乐观锁会需要多次重试才能成功，代价比较大。
• 重试代价：如果重试代价大，建议采用悲观锁。

使用场景
一般情况下，读多写少更适合用乐观锁，读少写多更适合用悲观锁。乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能。

Record lock

单条索引记录上加锁，record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引

间隙锁(Gap Locks)

在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。gap lock的机制主要是解决可重复读模式下的幻读问题(严格意义上，间隙锁并没有完全解决幻读)

• 如果当前索引行是唯一索引，则只会锁定当前索引，不会锁定索引的前后行
• 如果当前索引非唯一索引或不存在数据，则会锁定前后行数据

GAP锁主要针对插入语句，更新语句不会产生GAP

innodb自动使用间隙锁的条件：
（1）必须在RR级别下
（2）检索条件必须有索引（没有索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）

Next-Key Locks

Next-Key Locks是在存储引擎innodb、事务级别在可重复读的情况下使用的数据库锁，Next-Key Locks是行锁和gap锁的组合

next-key锁其实包含了记录锁和间隙锁，即锁定一个范围，并且锁定记录本身，InnoDB默认加锁方式是next-key 锁

当select for update为索引加上锁（行锁）的同时，索引值所处的区间会被加上Gap锁，而被加上Gap锁的区间无法insert数据

原理：

数据库中的锁机制和封锁协议

锁主要用于解决事务并发控制中产生的一些问题，在此之前我先列举一些相关的基本概念。

• 事务：用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。（事务的四个特性：原子性，一致性，隔离性，持续性）
• 并发控制：本文以单处理器系统为主，单处理器中多个用户并发地存取数据库就会产生多个事务同时存取同一数据的情况，若对并发操作不加控制就可能会存取和存储不正确的数据，破坏事务的一致性和数据库的一致性，所以数据库管理系统必须提供并发控制机制。

并发操作带来的数据不一致性包括丢失修改、不可重复读和读“脏”数据：

• 丢失修改：两个事务T1和T2同时读入同一数据并修改，T2提交的结果破坏了T1提交的结果，导致T1的修改被丢失。
• 不可重复读：事务T1读取数据后，事务T2执行更新操作，使T1无法再现前一次读取结果，包括三种情况：1）事务T1读取某一数据后，事务T2对其进行了修改，当事务T1再次读取该数据时，得到与前一次不同的值。2）事务T1按一定条件从数据库读取了某些数据记录后，事务T2删除了其中部分记录，当T1再次按相同条件读取数据时，发现某些记录神奇地消失了。3）事务T1按一定条件从数据库中读取某些数据记录后，事务T2插入了一些记录，当T1再次按相同条件读取数据时，发现多了一条数据。
• 读“脏”数据：事务T1修改某一数据并将其写回磁盘，事务T2读取同一数据后，T1由于某种原因被撤销，这时被T2修改过的数据恢复原值，T2读到的数据就与数据库中的数据不一致，则T2读到的数据就为“脏”数据，即不正确的数据。

并发控制就是要用正确的方式调度并发操作，使一个用户事务的执行不受其他事务的干扰，常用的并发控制技术有封锁、时间戳、乐观控制法和多版本并发控制等，本文主要讲利用锁机制进行并发控制。

封锁

封锁就是事务T在对某个数据对象操作之前，先向系统发出请求对其加锁。加锁后事务T就对该数据对象有了一定的控制，在事务T释放它的锁之前，其他事务不能更新此数据对象。基本的锁类型有两种：排他锁（又称写锁，X锁）和共享锁（又称读锁，S锁），此处再加一个更新锁和意向锁。

• 排他锁（X锁）：若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他任何事务都不能在对A加任何类型的锁，直到T释放A上的锁为止。这就保证了其他事务在T释放A上的锁之前不能再读取和修改A。
• 共享锁（S锁）：若事务T对数据对象A加上S锁，则只允许T读A但不能修改A，其他事务只能再对A加S锁而不能加X锁，知道T释放A上的S锁为止。
• 更新锁（U锁）：更新锁在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。因为使用共享锁时，修改数据的操作分为两步，首先获得一个共享锁，读取数据，然后将共享锁升级为排它锁，然后再执行修改操作。这样如果同时有两个或多个事务同时对一个事务申请了共享锁，在修改数据的时候，这些事务都要将共享锁升级为排它锁。这时，这些事务都不会释放共享锁而是一直等待对方释放，这样就造成了死锁。如果一个数据在修改前直接申请更新锁，在数据修改的时候再升级为排它锁，就可以避免死锁。
• 意向锁：对多粒度树中的结点加意向锁，则说明该结点的下层结点正在被加锁；对任一结点加锁时，必须先对它的上层结点加意向锁。

封锁协议

在运用X锁和S锁这两种基本封锁对数据对象加锁时，还需要约定一些规则。例如，何时申请X锁或S锁、持锁时间、何时释放等。这些规则称为封锁协议。通常使用三级封锁协议来在不同程度上解决并发操作的不正确调度带来的丢失修改、不可重复读和读“脏”数据等不一致性问题。

• 一级封锁协议
  一级封锁协议是指，事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放。一级封锁协议可以防止丢失修改，并保证事务T是可恢复的。

• 二级封锁协议
  二级封锁协议是指，在一级封锁协议基础上增加事务T在读数据R之前必须先对其加S锁，读完后即可释放S锁。二级封锁协议出防止了丢失修改，还可以进一步防止读“脏”数据。

• 三级封锁协议
  三级封锁协议是指，在一级封锁协议的基础上增加事务T在读数据R之前必须先对其加S锁，直到事务结束才释放。三级封锁协议出防止了丢失修改和读“脏”数据外，还可以进一步防止了不可重复读。

MySQL主从复制

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。

MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

原理

主从复制的原理其实就是把主服务器上的 bin 日志复制到从服务器上执行一遍，这样从服务器上的数据就和主服务器上的数据相同了

Mysql 中有一种日志叫做 bin 日志（二进制日志）。这个日志会记录下所有修改了数据库的SQL 语句（insert,update,delete,create/alter/drop table, grant 等等）。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-p3s4FQck-1649155292018)(pic/主从复制流程图.png)]

复制过程：

• 主节点必须启用二进制日志，记录任何修改了数据库数据的事件。
• 从节点开启一个线程（I/O Thread)把自己扮演成 mysql 的客户端，通过 mysql 协议，请求主节点的二进制日志文件中的事件
• 主节点启动一个线程（dump Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。
• 从节点接收到主节点发送过来的数据把它放置到中继日志（Relay log）文件中。并记录该次请求到主节点的具体哪一个二进制日志文件内部的哪一个位置（主节点中的二进制文件会有多个，在后面详细讲解）。
• 从节点启动另外一个线程（sql Thread ），把 Relay log 中的事件读取出来，并在本地再执行一次

**思考：**从节点需要建立二进制日志文件吗？
看情况，如果从节点需要作为其他节点的主节点时，是需要开启二进制日志文件的。这种情况叫做级联复制。如果只是作为从节点，则不需要创建二进制文件。

MySQL 主从复制主要用途

• 读写分离
  在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
• 数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换
• 高可用(指通过设计减少系统不能提供服务的时间)
• 架构扩展
  随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。

三种复制方式

• 同步

  • master的变化，必须等待slave-1,slave-2,…,slave-n完成后才能返回。 这样，显然不可取，也不是MySQL复制的默认设置。比如，在WEB前端页面上，用户增加了条记录，需要等待很长时间。

• 异步

  • master只需要完成自己的数据库操作即可。至于slaves是否收到二进制日志，是否完成操作，不用关心,MySQL的默认设置，这样可能会丢失数据

• 半同步

  • master一般至少有两个slave，主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的 relay log 之后，接着会返回一个 ack 给主库，主库接收到至少一个从库的 ack 之后才会认为写操作完成了。

• 注意

  • 从库同步主库数据的过程是串行化的，也就是说主库上并行的操作，在从库上会串行执行。
    • 由于从库从主库拷贝日志以及串行执行SQL 的特点，在高并发场景下，从库的数据一定会比主库慢一些，是有延时的
    • 经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到。
  • 解决思路
    • 从库中开启多线程并行操作

主从同步延迟原因

mysql 用主从同步的方法进行读写分离，减轻主服务器的压力的做法现在在业内做的非常普遍。 主从同步基本上能做到实时同步。

在配置好了， 主从同步以后， 主服务器会把更新语句写入binlog, 从服务器的IO 线程(这里要注意， 5.6.3 之前的IO线程仅有一个，5.6.3之后的有多线程去读了，速度自然也就加快了)回去读取主服务器的binlog 并且写到从服务器的Relay log 里面，然后从服务器的 的SQL thread 会一个一个执行 relay log 里面的sql ， 进行数据恢复。

1. 主从同步的延迟的原因

我们知道， 一个服务器开放Ｎ个链接给客户端来连接的，　这样有会有大并发的更新操作, 但是从服务器的里面读取binlog 的线程仅有一个， 当某个SQL在从服务器上执行的时间稍长 或者由于某个SQL要进行锁表就会导致，主服务器的SQL大量积压，未被同步到从服务器里。这就导致了主从不一致， 也就是主从延迟。

2. 主从同步延迟的解决办法

实际上主从同步延迟根本没有什么一招制敌的办法， 因为所有的SQL必须都要在从服务器里面执行一遍，但是主服务器如果不断的有更新操作源源不断的写入， 那么一旦有延迟产生， 那么延迟加重的可能性就会原来越大。 当然我们可以做一些缓解的措施。

a. 我们知道因为主服务器要负责更新操作， 他对安全性的要求比从服务器高， 所有有些设置可以修改，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置，而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog，innodb_flush_log_at_trx_commit 也 可以设置为0来提高sql的执行效率 这个能很大程度上提高效率。另外就是使用比主库更好的硬件设备作为slave。

b. 就是把，一台从服务器当度作为备份使用， 而不提供查询， 那边他的负载下来了， 执行relay log 里面的SQL效率自然就高了。

c. 增加从服务器喽，这个目的还是分散读的压力， 从而降低服务器负载。

3. 判断主从延迟的方法

MySQL提供了从服务器状态命令，可以通过 show slave status 进行查看， 比如可以看看Seconds_Behind_Master参数的值来判断，是否有发生主从延时。
其值有这么几种：
NULL - 表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No,而非Yes.
0 - 该值为零，是我们极为渴望看到的情况，表示主从复制状态正常

分片、分区、分库、分表

https://www.cnblogs.com/littlecharacter/p/9342129.html

https://www.jianshu.com/p/32b3e91aa22c

MySql的Sharding策略包括垂直切分和水平切分两种。

• **垂直(纵向)拆分：**是指按功能模块拆分，以解决表与表之间的io竞争。比如分为订单库、商品库、用户库…这种方式多个数据库之间的表结构不同。
• **水平(横向)拆分：**将同一个表的数据进行分块保存到不同的数据库中，来解决单表中数据量增长出现的压力。这些数据库中的表结构完全相同。

分表和分区比较类似，侧重点不同，分区侧重提高读写性能，分表侧重提高并发性能。两者不冲突，可以配合使用

分库分表方案:

• 水平分库：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
• 水平分表：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
• 垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
• 垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

不管是IO瓶颈，还是CPU瓶颈，最终都会导致数据库的活跃连接数增加，进而逼近甚至达到数据库可承载活跃连接数的阈值。在业务Service来看就是，可用数据库连接少甚至无连接可用。接下来就可以想象了吧（并发量、吞吐量、崩溃）。

1、IO瓶颈

• 第一种：磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询时会产生大量的IO，降低查询速度 -> 分库和垂直分表。

• 第二种：网络IO瓶颈，请求的数据太多，网络带宽不够 -> 分库。

2、CPU瓶颈

• 第一种：SQL问题，如SQL中包含join，group by，order by，非索引字段条件查询等，增加CPU运算的操作 -> SQL优化，建立合适的索引，在业务Service层进行业务计算。
• 第二种：单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈 -> 水平分表。

分表从表面意思说就是把一张表分成多个小表，分区则是把一张表的数据分成N多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上。

分表和分区的区别：
1，实现方式上
mysql的分表是真正的分表，一张表分成很多表后，每一个小表都是完正的一张表，都对应三个文件（MyISAM引擎：一个.MYD数据文件，.MYI索引文件，.frm表结构文件）。

2，数据处理上
分表后数据都是存放在分表里，总表只是一个外壳，存取数据发生在一个一个的分表里面。分区则不存在分表的概念，分区只不过把存放数据的文件分成了许多小块，分区后的表还是一张表，数据处理还是由自己来完成。

3，提高性能上
分表后，单表的并发能力提高了，磁盘I/O性能也提高了。分区突破了磁盘I/O瓶颈，想提高磁盘的读写能力，来增加mysql性能。 在这一点上，分区和分表的测重点不同，分表重点是存取数据时，如何提高mysql并发能力上；而分区呢，如何突破磁盘的读写能力，从而达到提高mysql性能的目的。

4，实现的难易度上
分表的方法有很多，用merge来分表，是最简单的一种方式。这种方式和分区难易度差不多，并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。 分区实现是比较简单的，建立分区表，跟建平常的表没什么区别，并且对代码端来说是透明的。

分表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

优化服务器硬件

服务器的硬件性能直接决定着MySQL数据库的性能，硬件的性能瓶颈，直接决定MySQL数据库的运行速度和效率。

需要从以下几个方面考虑：

1、 配置较大的内存。足够大的内存，是提高MySQL数据库性能的方法之一。内存的IO比硬盘快的多，可以增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘的IO。

2、 配置高速磁盘，比如SSD。

3、 合理分配磁盘IO，把磁盘IO分散到多个设备上，以减少资源的竞争，提高并行操作能力。

4、 配置多核处理器，MySQL是多线程的数据库，多处理器可以提高同时执行多个线程的能力。

select for update有什么含义，会锁表还是锁行还是其他。

select for update 含义

select查询语句是不会加锁的，但是select for update除了有查询的作用外，还会加锁呢，而且它是悲观锁哦。至于加了是行锁还是表锁，这就要看是不是用了索引/主键啦。

没用索引/主键的话就是表锁，否则就是是行锁。

select for update 获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。

大表数据查询，怎么优化

• 优化shema、sql语句+索引；
• 第二加缓存，memcached, redis；
• 主从复制，读写分离；
• 垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统
• 水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表

分库分表

某个表有近千万数据，可以考虑优化表结构，分表（水平分表，垂直分表），当然，你这样回答，需要准备好面试官问你的分库分表相关问题呀，如

• 分表方案（水平分表，垂直分表，切分规则hash等）
• 分库分表中间件（Mycat，sharding-jdbc等）
• 分库分表一些问题（事务问题？跨节点Join的问题）
• 解决方案（分布式事务等）

索引优化

除了分库分表，优化表结构，当然还有所以索引优化等方案~

有兴趣可以看我这篇文章哈~ 后端程序员必备：书写高质量SQL的30条建议

数据库连接池

连接池基本原理： 数据库连接池原理：在内部对象池中，维护一定数量的数据库连接，并对外暴露数据库连接的获取和返回方法。

应用程序和数据库建立连接的过程：

• 通过TCP协议的三次握手和数据库服务器建立连接
• 发送数据库用户账号密码，等待数据库验证用户身份
• 完成身份验证后，系统可以提交SQL语句到数据库执行
• 把连接关闭，TCP四次挥手告别。

数据库连接池好处：

• 资源重用 (连接复用)
• 更快的系统响应速度
• 新的资源分配手段
• 统一的连接管理，避免数据库连接泄漏

有兴趣的伙伴可以看看我这篇文章哈~ 数据库连接池内存泄漏问题的分析和解决方案

什么情况下会造成死锁

• 所谓死锁: 是指两个或两个以上的进程在执行过程中。
• 因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。
• 此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程。
• 表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的 InnoDB。

死锁的关键在于：两个(或以上)的 Session 加锁的顺序不一致。

那么对应的解决死锁问题的关键就是：让不同的 session加锁有次序。

死锁的解决办法

• 查出的线程杀死 kill

SELECT trx_MySQL_thread_id FROM information_schema.INNODB_TRX;

• 设置锁的超时时间

Innodb 行锁的等待时间，单位秒。可在会话级别设置，RDS 实例该参数的默认值为 50（秒）。

生产环境不推荐使用过大的 innodb_lock_wait_timeout参数值

该参数支持在会话级别修改，方便应用在会话级别单独设置某些特殊操作的行锁等待超时时间，如下：

set innodb_lock_wait_timeout=1000; —设置当前会话 Innodb 行锁等待超时时间，单位秒。

varchar 和 char 的使用场景？

char 的长度是不可变的，而 varchar 的长度是可变的。

定义一个 char[10]和 varchar[10]。

如果存进去的是‘csdn’,那么 char 所占的长度依然为 10，除了字符‘csdn’外，后面跟六个空格，varchar 就立马把长度变为 4 了，取数据的时候，char 类型的要用 trim()去掉多余的空格，而 varchar 是不需要的。

char 的存取数度还是要比 varchar 要快得多，因为其长度固定，方便程序的存储与查找。

char 也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率。

varchar 是以空间效率为首位。

char 的存储方式是：对英文字符（ASCII）占用 1 个字节，对一个汉字占用两个字节。

varchar 的存储方式是：对每个英文字符占用 2 个字节，汉字也占用 2 个字节。

两者的存储数据都非 unicode 的字符数据。