mysql一文整理汇总

我会带着你远行 2022-10-17 00:38 226阅读 0赞

### mysql ###

*   *  mysql 存储引擎
     *  sql 优化
     *  mysql 分库分表如何实现
     *  mysql 读写分离原理
     *  mysql 索引原理
     *  mysql 索引类型
     *  mysql都有什么锁
     *  mysql 死锁产生的原因如何解决
     *  mysql 如何避免死锁
     *  mysql如何处理死锁
     *  mysql 乐观锁和悲观锁
     *  数据库三大范式

## mysql 存储引擎 ##

MySQL 的存储引擎种类很多，有**MyISAM、InnoDB、、CSV、Memory、Merge、NDB**等等，以下介绍常用的存储引擎：

*  在MySQL 5.1 及之前的版本，MyISAM 是默认的存储引擎。MyISAM  
    提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM 不支持事务、不支持外键、不支持行级锁（仅支持表级锁）。特点是访问速度快，对事务完整性没有要求。以select、insert为主的应用基本上都可以用这个引擎来创建表
 *  MySQL  
    5.5之后默认的事务型引擎修改为InnoDB。它被设计用来处理大量的短期事务，短事务大部分情况是正常提交的，很少会被回滚。它是事务安全的，具有提交、回滚和崩溃恢复能力。InnoDB表默认为行级锁，是基于聚簇索引建立的。聚簇索引对主键查询有很高的性能。不过它的二级索引中必须包含主键列，所以如果主键列很大的话，其他的索引都会很大。存储格式是平台独立的，可以将数据和索引文件复制到其他平台。

## sql 优化 ##

考虑表有没有加上索引,加上索引考虑索引是否失效,失效的原因是：

*  数据类型不匹配
 *  索引列加上函数
 *  索引列参与逻辑运算
 *  组合索引配置最左前缀 如abc 只要a出现就走索引 否则失效
 *  or 关键字必须前后都要加上索引否则索引失效
 *  like 前面加% 索引失效

**建立索引的原则：**

*  经常在where条件后面出现的字段加上索引，但是经常被更新的字段，不建议加索引， 一张表的索引不超过6个，原因是更新、删除、添加要更新索引文件，做成大量的磁盘io，如果必须得建立6个以上索引，考虑使用组合索引表示状态的字段不建议加索引。
 *  如果做表关联查询，表的数据过大达到千万级。可以进行sql拆分。基于单表查询。
 *  可以考虑加上冗余字段。减少表连接的操作。

## mysql 分库分表如何实现 ##

分库分表：mycat  
server.xml:配置用户名密码  
schema.xml ：配置逻辑库和逻辑表，和表的分片规则，和分片的物理路径

rule.xml: 具体的分片规对应的配置文件和class 文件

## mysql 读写分离原理 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Byb2dyYW1tZXJfdW5pb24_size_16_color_FFFFFF_t_70]

## mysql 索引原理 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Byb2dyYW1tZXJfdW5pb24_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Byb2dyYW1tZXJfdW5pb24_size_16_color_FFFFFF_t_70 2]

## mysql 索引类型 ##

*  normal：表示普通索引
 *  unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique
 *  full textl: 表示 全文搜索的索引。 FULLTEXT  
    用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
 *  主键 索引：主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”
 *  单列索引：单列索引就是常用的一个列字段的索引，常见的索引。
 *  多列索引 （组合索引）：多列索引就是含有多个列字段的索引

## mysql都有什么锁 ##

MySQL有三种锁的级别：**页级、表级、行级**。

1.  表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
2.  行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
3.  页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

## mysql 死锁产生的原因如何解决 ##

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

假设我们有个需求  
将投资的钱拆成几份随机分配给借款人。  
起初业务程序思路是这样的：  
投资人投资后，将金额随机分为几份，然后随机从借款人表里面选几个，然后通过一条条select for update 去更新借款人表里面的余额等。  
抽象出来就是一个session通过for循环会有几条如下的语句：  
Select \* from xxx where id=‘随机id’ for update  
基本来说，程序开启后不一会就死锁。  
这可以是说最经典的死锁情形了。  
例如两个用户同时投资，A用户金额随机分为2份，分给借款人1，2  
B用户金额随机分为2份，分给借款人2，1  
由于加锁的顺序不一样，死锁当然很快就出现了。

对于这个问题的改进很简单，直接把所有分配到的借款人直接一次锁住就行了。  
Select \* from xxx where id in (xx,xx,xx) for update  
在in里面的列表值mysql是会自动从小到大排序，加锁也是一条条从小到大加的锁

参考资料：[点击这里][Link 1]

## mysql 如何避免死锁 ##

**如何避免发生死锁**

1.收集死锁信息：

*  利用命令 SHOW ENGINE INNODB STATUS查看死锁原因。
 *  调试阶段开启 innodb\_print\_all\_deadlocks，收集所有死锁日志。

2.减少死锁：

*  使用事务，不使用 lock tables 。
 *  保证没有长事务。
 *  操作完之后立即提交事务，特别是在交互式命令行中。
 *  如果在用 (SELECT … FOR UPDATE or SELECT … LOCK IN SHARE  
    MODE)，尝试降低隔离级别。
 *  修改多个表或者多个行的时候，将修改的顺序保持一致。
 *  创建索引，可以使创建的锁更少。
 *  最好不要用 (SELECT … FOR UPDATE or SELECT … LOCK IN SHARE MODE)。
 *  如果上述都无法解决问题，那么尝试使用 lock tables t1, t2, t3 锁多张表

## mysql如何处理死锁 ##

可以通过以下三个语句来查询被打开的表，正在执行的任务列表和开启的事务

show OPEN TABLES where In_use > 0;

show processlist; -- kill杀死进程id（id列）

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

– kill杀死进程id（trx\_mysql\_thread\_id列）

参考：[点击这里][Link 2]

## mysql 乐观锁和悲观锁 ##

**数据库扣减库存的时候 利用乐观锁解决**

update 库存表 set 库存=库存-购买数量 where 库存id=？ and 库存>=购买数量

**悲观锁**  
select \* from 库存表 where 库存id=？ for update  
事务提交释放锁

**按照锁的级别：**  
**共享锁：**

SELECT * FROM entrepot WHERE id=1 LOCK IN SHARE MODE ;

**排它锁**

select …for update

**意向锁**  
间隙锁

**锁的粒度：**

**行级锁：**

UPDATE entrepot SET price = price+500 WHERE name="aa";  - --  where条件加上索引  为行级锁

**表级锁：**

UPDATE entrepot SET price = price+500 WHERE name="aa"; -- where条件不加索引  为表级锁

**页面锁：**

*  开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般（不常用）

参考资料:[点击这里][Link 3]

## 数据库三大范式 ##

参考地址：[点击这里][Link 4]

欢迎交流沟通

求一键三连,谢谢!

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Byb2dyYW1tZXJfdW5pb24_size_16_color_FFFFFF_t_70]: /images/20221014/2750404c1eb8488ba5b4f177d652c134.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Byb2dyYW1tZXJfdW5pb24_size_16_color_FFFFFF_t_70 1]: /images/20221014/6fd8d4650cb94682b3bb680eed9c0266.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Byb2dyYW1tZXJfdW5pb24_size_16_color_FFFFFF_t_70 2]: /images/20221014/f9efa4b7caf1438e8fd5d030969ee4fe.png
[Link 1]: https://www.cnblogs.com/fps2tao/p/7894547.html
[Link 2]: https://jingyan.baidu.com/article/fdffd1f871b271f3e98ca1b4.html
[Link 3]: https://note.youdao.com/ynoteshare1/index.html?id=b3af181b2ee31107ace807a115129353&type=note
[Link 4]: https://www.jianshu.com/p/3b91a7484ff1