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 ：配置逻辑库和逻辑表，和表的分片规则，和分片的物理路径

<schema> <table>  <dataNode> <dataHost> <writeHost> <readHost>

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

mysql 读写分离原理

mysql 索引原理

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是会自动从小到大排序，加锁也是一条条从小到大加的锁

参考资料：点击这里

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列）

参考：点击这里

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条件不加索引  为表级锁

页面锁：

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

参考资料:点击这里

数据库三大范式

参考地址：点击这里

欢迎交流沟通

求一键三连,谢谢!