Mysql进阶

1.Mysql存储引擎

1.1 常见Mysql存储引擎

存储引擎是真正存储数据的地方，常见存储引擎如下：

1. MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。注意，通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎。
2. InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持。
3. BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。
4. Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。
5. Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。
6. Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。
7. Federated：能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。
8. Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。
9. Other：其他存储引擎包括CSV（引用由逗号隔开的用作数据库表的文件），Blackhole（用于临时禁止对数据库的应用程序输入），以及Example引擎（可为快速创建定制的插件式存储引擎提供帮助）。

1.2 常用存储引擎MyISAM、InnoDB、Memory的比较

（1）InnoDB存储引擎

• 高并发，更新操作比较多的表。需要使用事务的表。对自动灾难恢复有要求的表

（2）MYISAM存储引擎
有三个文件：

• .frm文件，存储表的结构
• .myd文件，存储表的数据
• .myi文件，存储表的索引

优点：占用空间小，处理速度快（相对InnoDB来说）
确定：不支持事务的完整性和并发性

（3）Memory存储引擎
数据存放在内存上，一旦服务器关机，数据将不再存在，Memory存储引擎不支持变长表列，速度要求快的，临时数据。

MyISAM表虽然count()很快（直接存储的，不用遍历扫描全表），但是不支持事务；
InnoDB直接count()会遍历全表(没有where条件)，虽然结果准确，但会导致性能问题。

2.索引

2.1 索引的类别

• 单列索引

1. 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
2. 唯一索引：索引列中的值必须是唯一的，但是允许为空值
3. 主键索引：是一种特殊的唯一索引，不允许有空值。

• 组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

• 全文索引

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 …" 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节

• 空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。
在创建空间索引时，使用SPATIAL关键字。
要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

2.2 索引失效

1. WHERE字句的查询条件里有不等于号（WHERE column!=…），MYSQL将无法使用索引
2. 类似地，如果WHERE字句的查询条件里使用了函数（如：WHERE DAY(column)=…），MYSQL将无法使用索引。
3. 在JOIN操作中（需要从多个数据表提取数据时），MYSQL只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了索引也不会使用。
4. 如果WHERE子句的查询条件里使用了比较操作符LIKE和REGEXP，MYSQL只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。比如说，如果查询条件是LIKE ‘abc%’,MYSQL将使用索引；如果条件是LIKE ‘%abc’，MYSQL将不使用索引。
5. 在ORDER BY操作中，MYSQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。尽管如此，在涉及多个数据表的查询里，即使有索引可用，那些索引在加快ORDER BY操作方面也没什么作用。
6. 如果某个数据列里包含着许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含了净是些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。
7. 索引有用的情况下就太多了。基本只要建立了索引，除了上面提到的索引不会使用的情况下之外，其他情况只要是使用在WHERE条件里，ORDER BY 字段，联表字段，一般都是有效的。 建立索引要的就是有效果。 不然还用它干吗？ 如果不能确定在某个字段上建立的索引是否有效果，只要实际进行测试下比较下执行时间就知道。
8. 如果条件中有or(并且其中有or的条件是不带索引的)，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。
9. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
10. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引。

3.锁

3.1 乐观锁和悲观锁

乐观锁
乐观锁不是数据库自带的，需要我们自己去实现。乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。

通常实现是这样的：在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加1。也就是先查询出那条记录，获取出version字段,如果要对那条记录进行操作(更新),则先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将version字段的值加1；如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。

举例：

下单操作包括3步骤：

1.查询出商品信息

select (status,status,version) from t_goods where id=#{id}

2.根据商品信息生成订单

3.修改商品status为2

update t_goods

set status=2,version=version+1

where id=#{id} and version=#{version};

除了自己手动实现乐观锁之外，现在网上许多框架已经封装好了乐观锁的实现，如hibernate，需要时，可能自行搜索"hiberate 乐观锁"试试看。

悲观锁
与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

https://blog.csdn.net/puhaiyang/article/details/72284702

4.Mysql范式

• 第一范式(1NF)：数据表的每一列都要保持它的原子特性，也就是列不能再被分割。
  即：字段不可分，每个字段是原子级别的，上节中看到第一个字段为ID，它就是ID不能在分成两个字段了，不能说我要把这个人的ID、名称、班级号都塞在一个字段里面，这个是不合适的，对以后的应用造成很大影响。
• 第二范式(2NF)：属性必须完全依赖于主键。
  即：有主键，非主键字段依赖主键，ID字段就是主键，它能表示这一条数据是唯一的，有的读者朋友记性很好，“unique”表示唯一的、不允许重复的，确实它经常会修饰某个字段，保证该字段唯一性，然后再设置该字段为主键。
• 第三范式(3NF)：所有的非主属性不依赖于其他的非主属性。
  即：非主键字段不能相互依赖，这个怎么理解呢，比如student表，班级编号受人员编号的影响，如果在这个表中再插入班级的班主任、数学老师等信息，你们觉得这样合适吗？肯定不合适，因为学生有多个，这样就会造成班级有多个，那么每个班级的班主任、数学老师都会出现多条数据，而我们理想中的效果应该是一条班级信息对应一个班主任和数学老师，这样更易于我们理解，这样就形成class表，那么student表和class表中间靠哪个字段来关联呢，肯定是通过“classNo”，这个字段也叫做两个表的外键。

5.事务的ACID

• 原子性(Atomic)：事务中各项操作，要么全做要么全不做，任何一项操作的失败都会导致整个事务的失败；
• 一致性(Consistent)：事务结束后系统状态是一致的；
• 隔离性(Isolated)：并发执行的事务彼此无法看到对方的中间状态；
• 持久性(Durable)：事务完成后所做的改动都会被持久化，即使发生灾难性的失败。通过日志和同步备份可以在故障发生后重建数据。

6.Mysql查看执行计划

https://blog.csdn.net/weixin_41558728/article/details/81704916

7.使用SHOW STATUS语句查询一些Mysql数据库的性能参数

• 1.查询mysql服务器的连接次数

SHOW STATUS LIKE ‘CONNECTIONS’;

• 2.查询mysql服务器的慢查询次数。慢查询次数参数可以结合慢查询日志，找出慢查询语句，然后针对慢查询语句进行表结构优化或者查询语句优化

SHOW STATUS LIKE ‘Slow_queries’;

• mysql服务器的上线时间

SHOW STATUS LIKE ‘Uptime’;

• 查询操作的次数

SHOW STATUS LIKE ‘Com_select’;

SHOW STATUS LIKE ‘Com_insert’;

SHOW STATUS LIKE ‘Com_update’;

SHOW STATUS LIKE ‘Com_delete’;