只有通过索引进行检索的时候才会使用行级锁，如果不是通过索引进行检索就会升级成表锁。
行锁是为了最大并发化所提供的一种锁，封锁某一行数据。我知道的 mysql 行锁有三种，就间隙锁使用场景，我分成了唯一索引和非唯一索引两种情况。记住所有的for update都是当前读并且加上行锁，跟快照读不一样，
（1）按对数据操作：
读锁（共享锁）：多个读操作可以同时进行，互不干扰
写锁（互斥锁）：如果当前写操作没有完毕，则无法进行其他读操作和写操作。
（2）按粒度分：表锁和行锁
表锁：
偏小 myisam 存储引擎，开销小，无死锁，容易发生锁冲突，并发度低。
lock table 表名字 1 read(write)，表名字 2 read(write)，其它
行锁
偏向 innodb,开销大，支持事务
总的来说，Innodb 共有其中类型的锁

1：共享/排它锁（Shared and Exclusive Locks）

2：意向锁（Intention Locks）

InnoDB 为了支持多粒度锁机制(multiple granularity locking)，即允许行级锁与表级锁共存，而引入了意向锁(intention locks)。意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。

1. 意向锁是一个表级别的锁(table-level locking)；
2. 意向锁又分为：
   • 意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享 S 锁；
   • 意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它 X 锁；

加锁的语法为：
select … lock in share mode;　　要设置IS锁；
select … for update;　　　　　　 要设置IX锁；
事务要获得某些行的 S/X 锁，必须先获得表对应的 IS/IX 锁，意向锁仅仅表明意向，意向锁之间相互兼容，兼容互斥表如下：

虽然意向锁之间互相兼容，但是它与共享锁/排它锁互斥，其兼容互斥表如下:

记录锁（Record Locks）

记录锁，它封锁索引记录

间隙锁（Gap Locks）

间隙锁，它封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。
间隙锁一般都是针对非唯一索引而言的，它会对相邻的区间进行封锁，被锁上的区间内的值是无法再被使用的。当那个条件不存在的时候会找表中存在的相邻数据，然后再进行加锁。
当主键做为条件的时候，不存在间隙锁，除非手动指定范围。
间隙锁：是 Innodb 在可重复读提交下为了解决幻读问题时引入的锁机制，幻读是因为新增或则更新操作，这时如果进行范围查询的时候（加锁查询）会出现不一致的问题。
加锁特性：
1.加锁的基本单位是（next-key lock）,他是前开后闭原则
2.插叙过程中访问的对象会增加锁
3.索引上的等值查询—给唯一索引加锁的时候，next-key lock 升级为行锁
4.索引上的等值查询—部分命中或者全不命中，next-key lock 退化为间隙锁
5.唯一索引上的范围查询会访问到不满足条件的第一个值为止
间隙锁死锁问题：
不同于写锁相互之间是互斥的原则，间隙锁之间不是互斥的，如果一个事务 A 获取到了（5,10]之间的间隙锁，另一个事务 B 也可以获取到（5,10]之间的间隙锁。这时就可能会发生死锁问题，

临键锁（Next-key Locks）

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。
默认情况下，innodb 使用 next-key locks 来锁定记录。但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为 Record Lock，即仅锁住索引本身，不是范围。
但是 id 降级为普通索引(key)，也就是说即使这里声明了要加锁(for update)，而且命中的是索引，但是因为索引在这里没有 UK 约束，所以 innodb 会使用 next-key locks，数据库隔离级别 RR：
事务A执行如下语句，未提交：
select * from lock_example where id = 20 for update;

事务B开始，执行如下语句，会阻塞：
insert into lock_example values(‘zhang’,15);
如上的例子，事务 A 执行查询语句之后，默认给 id=20 这条记录加上了 next-key lock，所以事务 B 插入 10(包括)到 30(不包括)之间的记录都会阻塞。临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为 RC，临键锁则也会失效。

插入意向锁（Insert Intention Locks）

对已有数据行的修改与删除，必须加强互斥锁(X 锁)，那么对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？插入意向锁，孕育而生。
插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对 insert 操作的。多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。
事务 A 先执行，在 10 与 20 两条记录中插入了一行，还未提交：
insert into t values(11, xxx);
事务 B 后执行，也在 10 与 20 两条记录中插入了一行：
insert into t values(12, ooo);
因为是插入操作，虽然是插入同一个区间，但是插入的记录并不冲突，所以使用的是插入意向锁，此处 A 事务并不会阻塞 B 事务。

• 自增锁（Auto-inc Locks）

自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入 AUTO_INCREMENT 类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。
比如：
事务 A 先执行，还未提交： insert into t(name) values(xxx);
事务 B 后执行： insert into t(name) values(ooo);
此时事务 B 插入操作会阻塞，直到事务 A 提交。
总结：
1. 按锁的互斥程度来划分，可以分为共享、排他锁；

• 共享锁(S 锁、IS 锁)，可以提高读读并发；
• 为了保证数据强一致，InnoDB 使用强互斥锁(X 锁、IX 锁)，保证同一行记录修改与删除的串行性；

2. 按锁的粒度来划分，可以分为：

• 表锁：意向锁(IS 锁、IX 锁)、自增锁；
• 行锁：记录锁、间隙锁、临键锁、插入意向锁；

其中

1. InnoDB 的细粒度锁(即行锁)，是实现在索引记录上的(我的理解是如果未命中索引则会失效)；
2. 记录锁锁定索引记录；间隙锁锁定间隔，防止间隔中被其他事务插入；临键锁锁定索引记录+间隔，防止幻读；
3. InnoDB 使用插入意向锁，可以提高插入并发；
4. 间隙锁(gap lock)与临键锁(next-key lock)只在 RR 以上的级别生效，RC 下会失效；

   4：间隙锁：

   当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”， InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（GAP Lock）。
   因为 Query 执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。 间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害
   —查看哪些表加了锁
   show open tables;—1代表被加了锁
   —分析表锁定的严重程度
   show status like ‘table%’
   参数：Table_locks_immediate:即可能获取到的锁数
   参数：Table_waited:需要等待的表锁数，该值越大锁竞争越大

   mysql 是如何实现悲观锁与乐观锁的？

   一锁二查三更新”即指的是使用悲观锁。通常来讲在数据库上的悲观锁需要数据库本身提供支持，即通过常用的 select … for update 操作来实现悲观锁。
   当数据库执行 select for update 时会获取被 select 中的数据行的行锁，因此其他并发执行的 select for update 如果试图选中同一行则会发生排斥（需要等待行锁被释放），因此达到锁的效果。select for update 获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。
   mysql 还有个问题是 select… for update 语句执行中，如果数据表没有添加索引或主键，所有扫描过的行都会被锁上，这一点很容易造成问题。因此如果在 mysql 中用悲观锁务必要确定走了索引，而不是全表扫描。
   要使用悲观锁，我们必须关闭 mysql 数据库的自动提交属性，因为 MySQL 默认使用 autocommit 模式，也就是说，当你执行一个更新操作后，MySQL 会立刻将结果进行提交。
   乐观锁的三种实现方式：
   1：使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将 version 字段的值一同读出，数据每更新一次，对此 version 值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的 version 值进行比对，如果数据库表当前版本号与第一次取出来的 version 值相等，则予以更新，否则认为是过期数据，
   2：使用时间戳（timestamp）, 和上面的 version 类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则 OK，否则就是版本冲突。

实这种版本号的方法并不是适用于所有的乐观锁场景。举个例子，当电商抢购活动时，大量并发进入，如果仅仅使用版本号或者时间戳，就会出现大量的用户查询出库存存在，但是却在扣减库存时失败了，而这个时候库存是确实存在的。想象一下，版本号每次只会有一个用户扣减成功，不可避免的人为造成失败。这种时候就需要我们的第二种场景的乐观锁方法。
UPDATE t_goods
SET num = num - #{buyNum}
WHERE
id = #{id}
AND num - #{buyNum} >= 0
AND STATUS = 1
说明：num-#{buyNum}>=0 ，这个情景适合不用版本号，只更新是做数据安全校验，适合库存模型，扣份额和回滚份额，性能更高。这种模式也是目前我用来锁产品库存的方法，十分方便实用。
更新操作，最好用主键或者唯一索引来更新,这样是行锁，否则更新时会锁表

死锁问题

在可重复读级别下，因为加锁导致两个事务出现死锁。
排查：
— Oracle
—查看死锁用户，状态，机器，程序
select username,lockwait,status,machine,program from v$session where sid in (select session_id from v$locked_object)
— 查看被死锁的语句
select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object))
—-mysql
1：查看当前事务
select from information_schema.innodb_trx;
2:查看当前锁定的事务
select from information_schema.innodb_lock;
3:查看当前等锁的事务
select * from information_schema.innofb_lock_waits;
解决方案：
1：已经出现，查找，杀掉
—-Oracle
—查找死锁进程
select s.username,I.object_id,I.session_id,s.serial#,I.oracle_username,I.os_user_name,I.process from v$locked_object I,v$session s where I.session_id = s.sid;
—kill 掉这个死锁的进程
alter system kill session ‘sid,serial#’;(其中sid = I.session_id)
1：数据库级别设置超时时间
[mysqld]
log-error =/var/log/mysqld3306.log
innodb_lock_wait_timeout=60 #锁请求超时时间(秒)
innodb_rollback_on_timeout = 1 #事务中某个语句锁请求超时将回滚整个事务
innodb_print_all_deadlocks = 1 #死锁都保存到错误日志

死锁的可能情况：

1：程序问题

用户X访问表a锁住然后又申请访问B，用户Y访问B并锁住申请访问a。

解决：调整逻辑，仔细分析程序逻辑，对于数据的多表操作，尽量按照相同的顺序进行处理。

2：锁升级

用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A 有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。——真的会出现吗，独占和共享可以同时存在？

解决：

3：全表扫描

如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。

解决：

SQL语句不要使用太复杂的关联多表查询；explain对于有全表扫描的SQL语句建立索引；

19：服务器级常用 sql 语句

—查看表空间
select concat(round(sum(index_length)/(1024*1024),2),’MB’) AS ‘MB’ ,’Index Data Size’ as TABLESPACE from information_schema.TABLE where table_schema=’alp’

select
a.tablespace_name “表空间名”，
total “表空间大小”,
free “表空间剩余大小”,
(total - free) “表空间使用大小”,
total/(102410241024) “表空间大小(G)”,
free/(102410241024) “表空间剩余大小(G)”,
(total - free)/(102410241024) “表空间使用大小(G)”,
round((total - free)/total,4) * 100 “使用率%”,
from (select tablespace_name,sum(bytes) free
from dba_free_space
group by tablespace_name) a,
(select tablespace_name,sum(bytes) total
from dba_data_files
group by tablespace_name) b

19、数据库设计的三范式

19.1、第一范式

数据库表中不能出现重复记录，每个字段是原子性的不能再分
不符合第一范式的示例

存在问题：

• 最后一条记录和第一条重复（不唯一，没有主键）
  • 联系方式字段可以再分，不是原子性的 | 学生编号(pk) | 学生姓名 | email | 联系电话 | | —- | —- | —- | —- | | 1001 | 张三 | zs\@gmail.com | 1359999999 | | 1002 | 李四 | ls@gmail.com | 13699999999 | | 1003 | 王五 | ww@163.net | 13488888888 |

关于第一范式，每一行必须唯一，也就是每个表必须有主键，这是我们数据库设计的最基本要求，主要通常采用数值型或定长字符串表示，关于列不可再分，应该根据具体的情况来决定。如联系方式，为了开发上的便利行可能就采用一个字段了。

19.2、第二范式

第二范式是建立在第一范式基础上的，另外要求所有非主键字段完全依赖主键，不能产生部分依赖
示例：

确定主键：

以上虽然确定了主键，但此表会出现大量的冗余，主要涉及到的冗余字段为“学生姓名”和“教师姓名”，出现冗余的原因在于，学生姓名部分依赖了主键的一个字段学生编号，而没有依赖教师编号，而教师姓名部门依赖了主键的一个字段教师编号，这就是第二范式部分依赖。
解决方案如下：
学生信息表

教师信息表

教师和学生的关系表

如果一个表是单一主键，那么它就复合第二范式，部分依赖和主键有关系
以上是一种典型的“多对多”的设计

19.3、第三范式

建立在第二范式基础上的，非主键字段不能传递依赖于主键字段。（不要产生传递依赖）

从上表可以看出，班级名称字段存在冗余，因为班级名称字段没有直接依赖于主键，班级名称字段依赖于班级编号，班级编号依赖于学生编号，那么这就是传递依赖，解决的办法是将冗余字段单独拿出来建立表，如：
学生信息表

班级信息表

以上设计是一种典型的一对多的设计，一存储在一张表中，多存储在一张表中，在多的那张表中添加外键指向一的一方的主键

19.4、三范式总结

第一范式：有主键，具有原子性，字段不可分割
第二范式：完全依赖，没有部分依赖
第三范式：没有传递依赖
数据库设计尽量遵循三范式，但是还是根据实际情况进行取舍，有时可能会拿冗余换速度，最终用目的要满足客户需求。
一对一设计，有两种设计方案：
第一种设计方案：主键共享
第二种设计方案：外键唯一

22：MySQL 配置参数

基本配置：
datadir：指定 mysql 的数据目录位置，用于存放 mysql 数据库文件、日志文件等。
配置示例：datadir=D:/wamp/mysqldata/Data
default-character-set：mysql 服务器默认字符集设置。
配置示例：default-character-set=utf8
skip-grant-tables：当忘记 mysql 用户密码的时候，可以在 mysql 配置文件中配置该参数，跳过权限表验证，不需要密码即可登录 mysql。
日志相关：
log-error：指定错误日志文件名称，用于记录当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。
配置示例：log-error=”WJT-PC.err”（默认在 mysql 数据目录下）
log-bin：指定二进制日志文件名称，用于记录对数据造成更改的所有查询语句。
配置示例：log-bin=”WJT-PC-bin.log”（默认在 mysql 数据目录下）
binlog-do-db：指定将更新记录到二进制日志的数据库，其他所有没有显式指定的数据库更新将被忽略，不记录在日志中。
配置示例：binlog-do-db=db_name
binlog-ignore-db：指定不将更新记录到二进制日志的数据库，其他没有显式忽略的数据库都将进行记录。
配置示例：binlog-ignore-db=db_name 如果想记录或忽略多个数据库，可以对上面两个选项分别使用多次。
sync-binlog：指定多少次写日志后同步磁盘。
配置示例：sync-binlog=N
general-log：是否开启查询日志记录。
配置示例：general-log=1
general_log_file：指定查询日志文件名，用于记录所有的查询语句。
配置示例：general_log_file=”WJT-PC.log”（默认在 mysql 数据目录下）
slow-query-log：是否开启慢查询日志记录。
配置示例：slow-query-log=1
slow_query_log_file：指定慢查询日志文件名称，用于记录消耗时间较长的查询语句。
配置示例：slow_query_log_file=”WJT-PC-slow.log”（默认在 mysql 数据目录下）
long_query_time：设置慢查询的时间，超过这个时间的查询语句才记录日志。
配置示例：long_query_time=10（单位：秒）
log-slow-admin-statements：是否将慢管理语句（例如 OPTIMIZE TABLE、ANALYZE TABLE 和 ALTER TABLE）写入慢查询日志。
存储引擎相关：
default-table-type：设置 mysql 的默认存储引擎。
innodb_data_home_dir：InnoDB 引擎的共享表空间数据文件根目录。若没有设置，则使用 mysql 的 datadir 目录作为缺省目录。
innodb_data_file_path：单独指定共享表空间数据文件的路径与大小。数据文件的完整路径由 innodb_data_home_dir 与这里配置的值组合起来，文件大小以 MB 单位指定。
配置示例：innodb_data_home_dir=innodb_data_file_path=ibdata1:12M;/data/mysql/mysql3306/data1/ibdata2:12M:autoextend
如果想为 innodb 表空间指定不同目录下的文件，必须指定 innodb_data_home_dir =。这个例子中会在 datadir 下建立 ibdata1，在/data/MySQL/mysql3306/data1/目录下创建 ibdata2。
innodb_file_per_table：是否开启独立表空间，若开启，InnoDB 将使用独立的.idb 文件创建新表而不是在共享表空间中创建。
配置示例：innodb_file_per_table=1
innodb_autoinc_lock_mode：配置在向有着 auto_increment 列的表插入数据时，相关锁的行为。该参数有 3 个取值：
0：tradition 传统，所有的 insert 语 句开始的时候得到一个表级的 auto_inc 锁，在语句结束的时候才能释放 这个锁，影响了并发的插入。
1：consecutive 连续，mysql 可以一次生成 几个连续的 auto_inc 的值，auto_inc 不需要一直保持到语句结束，只要 语句得到了相应的值后就可以提前释放锁（这也是 mysql 的默认模式）。
2：interleaved 交错，这个模式下已经没有了 auto_inc 锁，所以性能是最好的，但是对于同一个语句来说它得到的 auto_inc 的值可能不是连续的。
配置示例：innodb_autoinc_lock_mode=1
low_priority_updates：在 myisam 引擎锁使用中，默认情况下写请求优先于读请求，可以通过将该参数设置为 1 来使 myisam 引擎给予读请求优先权限， 所有的 insert、update、delete 和 lock table write 语句将等待直到受影响的表没有挂起的 select 或 lock table read。
配置示例：low_priority_updates=0（默认配置）
max_write_lock_count：当一个 myisam 表的写锁定达到这个值后，mysql 就暂时 将写请求优先级降低，给部分读请求获得锁的机会。
innodb_lock_wait_timeout：InnoDB 锁等待超时参数，若事务在该时间内没有获 得需要的锁，则发生回滚。
配置示例：innodb_lock_wait_timeout=50（默认 50 秒）
max_heap_table_size：设置 memory 表的最大空间大小，该变量可以用来计算 memory 表的 max_rows 值。在已有 memory 表上设置该参数是没有效果 的，除非重建表。
查询相关：
max_sort_length：配置对 blob 或 text 类型的列进行排序时使用的字节数（只对配置的前 max_sort_length 个字节进行排序，其他的被忽略）
max_length_for_sort：mysql 有两种排序算法，两次传输排序和单次传输排序。当查询需要所有列的总长度不超过 max_length_for_sort 时，mysql 使用 单次传输排序，否则使用两次传输排序。
optimizer_search_depth：在关联查询中，当需要关联的表数量超过 optimizer_search_depth 的时候，优化器会使用“贪婪”搜索的方式查找“最优”的关联顺序。

21：问题

1：修改 mysql 的 root 密码

方法 1： 用 SET PASSWORD 命令
首先登录 MySQL。
格式：mysql> set password for 用户名\@localhost = password(‘新密码’);
例子：mysql> set password for root\@localhost = password(‘123’);
方法 2：用 mysqladmin
格式：mysqladmin -u 用户名 -p 旧密码 password 新密码
例子：mysqladmin -uroot -p123456 password 123
方法 3：用 UPDATE 直接编辑 user 表
首先登录 MySQL。
mysql> use mysql;
mysql> update user set password=password(‘123’) where user=’root’ and host=’localhost’;
mysql> flush privileges;
方法 4：在忘记 root 密码的时候，可以这样
以 windows 为例：

1. 关闭正在运行的 MySQL 服务。
2. 打开 DOS 窗口，转到 mysql\bin 目录。
3. 输入 mysqld —skip-grant-tables 回车。—skip-grant-tables 的意思是启动 MySQL 服务的时候跳过权限表认证。
4. 再开一个 DOS 窗口（因为刚才那个 DOS 窗口已经不能动了），转到 mysql\bin 目录。
5. 输入 mysql 回车，如果成功，将出现 MySQL 提示符 >。
6. 连接权限数据库： use mysql; 。
7. 改密码：update user set password=password(“123”) where user=”root”;（别忘了最后加分号） 。
8. 刷新权限（必须步骤）：flush privileges; 。
9. 退出 quit。
10. 注销系统，再进入，使用用户名 root 和刚才设置的新密码 123 登录

    2：Mysql 占用 CPU100%，如何处理？

    mysql CPU 使用已达到接近 400%（因为是四核，所以会有超过 100%的情况）
    在服务器上执行 mysql -u root -p 之后，输入 show full processlist; 可以看到正在执行的语句。
    但是从数据库设计方面来说，该做的索引都已经做了，SQL 语句似乎没有优化的空间。
    直接执行此条 SQL，发现速度很慢，需要 1-6 秒的时间（跟 mysql 正在并发执行的查询有关，如果没有并发的，需要 1 秒多）。如果把排序依据改为一个，则查询时间可以缩短至 0.01 秒（most_top）或者 0.001 秒（posttime）。
    优化：
    首先是缩减查询范围

    怎么防止 sql 注入？

    sql 注入：某些 sql 语句的参数没有进行合理校检，参数中可能有危害数据库的一些语句，导致语句出错。
    使用预编译语句的支持
    一条语句可能会反复执行，或许每次执行只有个别语句不同。
    使用占位符替代，一次编译，多次运行。
    mysql 使用 PrepareStatement

转载 https://www.yuque.com/jykss/jykss/mds7qx#izCti