mysql

概述

MySQL是一个小型关系型数据库管理系统，开发者为瑞典MySQL AB公司。在2008年1月16号被Sun公司收购。而2009年，SUN又被Oracle收购。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内。这样就增加了速度并提高了灵活性。MySQL的SQL“结构化查询语言”。SQL是用于访问数据库的最常用标准化语言。MySQL软件采用了GPL（GNU通用公共许可证）。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。

体系结构

https://www.processon.com/view/link/60e4f6d65653bb46e90ece0b

数据库的基本概念

事务

事务保证一组原子性的操作，要么全部成功，要么全部失败。一旦失败，回滚之前的所有操作。MySql采用自动提交，如果不是显式的开启一个事务，则每个查询都作为一个事务。

MYSQL 数据库中一个事务具备ACID表示原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）。

多事务并发可能出现的问题

1.脏读
一个事务读到了另一个未提交事务修改过的数据。
（脏读只在读未提交隔离级别才会出现）
2.不可重复读
一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值。
（不可重复读在读未提交和读已提交隔离级别都可能会出现）
3.幻读
一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来。
（幻读在读未提交、读已提交、可重复读隔离级别都可能会出现）

未提交读
在读未提交隔离级别下，事务A可以读取到事务B修改过但未提交的数据。
可能发生脏读、不可重复读和幻读问题。
已提交读
在读已提交隔离级别下，事务B只能在事务A修改过并且已提交后才能读取到事务B修改的数据。
读已提交隔离级别解决了脏读的问题，但可能发生不可重复读和幻读问题
可重复读
在可重复读隔离级别下，事务B只能在事务A修改过数据并提交后，自己也提交事务后，才能读取到事务B修改的数据。
可重复读隔离级别解决了脏读和不可重复读的问题，但可能发生幻读问题。
串行化
最高隔离级别，强制事务串行执行

提问：为什么上了写锁（写操作），别的事务还可以读操作？
因为InnoDB有MVCC机制（多版本并发控制），可以使用快照读，而不会被阻塞。

提问：为什么innodb默认使用可重复读？
因为，binlog和sql执行顺序问题。

数据库通过锁机制来解决并发场景-共享锁（读锁）和排他锁（写锁）。读锁是不阻塞的，多个客户端可以在同一时刻读取同一个资源。写锁是排他的，并且会阻塞其他的读锁和写锁。
要锁定数据需要一定的锁策略来配合。
表锁，锁定整张表，开销最小，但是会加剧锁竞争。
行锁，锁定行级别，开销最大，但是可以最大程度的支持并发。

mysql 存储引擎
InnoDB引擎，最重要，使用最广泛的存储引擎。被用来设计处理大量短期事务，具有高性能和自动崩溃恢复的特性。5.5之后的默认设置
MyISAM引擎，不支持事务和行级锁，崩溃后无法安全恢复。

mysql的优化主要分为

• 数据备份优化
• 用户权限优化
• 部署环境优化
• 运行性能优化
• 监控管理优化
• 数据库设计优化
• 数据类型优化

尽量使用对应的数据类型。比如，不要用字符串类型保存时间，用整型保存IP
选择更小的数据类型。能用TinyInt不用Int。
标识列（identifier column），建议使用整型，不推荐字符串类型，占用更多空间，而且计算速度比整型慢。
不推荐ORM系统自动生成的Schema，通常具有不注重数据类型，使用很大的VarChar类型，索引利用不合理等问题。
创建完全的独立的汇总表\缓存表，定时生成数据，用于用户耗时时间长的操作。对于精确度要求高的汇总操作，可以采用 历史结果+最新记录的结果 来达到快速查询的目的。

查询优化
响应时间 （服务时间，排队时间）
扫描的行
返回的行

避免查询无关的列，如使用Select * 返回所有的列。
避免查询无关的行
切分查询。将一个对服务器压力较大的任务，分解到一个较长的时间中，并分多次执行。如要删除一万条数据，可以分10次执行，每次执行完成后暂停一段时间，再继续执行。过程中可以释放服务器资源给其他任务。
分解关联查询。将多表关联查询的一次查询，分解成对单表的多次查询。可以减少锁竞争，查询本身的查询效率也比较高。因为MySql的连接和断开都是轻量级的操作，不会由于查询拆分为多次，造成效率问题。
注意count的操作只能统计不为null的列，所以统计总的行数使用count（*）
关联查询延迟关联，可以根据查询条件先缩小各自要查询的范围，再关联
Union查询默认去重，如果不是业务必须，建议使用效率更高的Union All
Limit分页优化。可以根据索引覆盖扫描，再根据索引列关联自身查询其他列。

SELECT
 id,
 NAME,
 age
WHERE
 student s1
INNER JOIN (
 SELECT
   id
 FROM
   student
 ORDER BY
   age
 LIMIT 50,5
) AS s2 ON s1.id = s2.id