MySQL知识点总结
MySQL介绍：
MySQL是一种关系型数据库，它的架构可以在多种不同场景中应用，它很灵活，能够适应高要求的环境。它既可以嵌入到应用程序中，也可以支持数据仓库、内容索引和部署软件、高可用的冗余系统、在线事务处理系统等各种类型。
MySQL逻辑架构：
客户端口连接层：每个客户端连接会在服务器进程中对应一个线程，所有的查询都会在这个单独的线程中执行。服务器负责创建、缓存和销毁线程。
核心服务层：包含查询解析、分析、优化、缓存以及大多数内置函数，所有跨存储引擎的功能都在这一层实现。
存储引擎层：存储引擎负责MySQL中数据的存储和提取。每种存储引擎各有优劣，不同存储引擎之间不会相互通信。
优化与执行：
MySQL会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包含重写查询、决定表的读写顺序，以及选择合适的索引等。用户可通过关键字提示（hint）优化器，从而影响它的决策过程。也可以请求优化器解释（explain）优化过程的各个因素。优化器并不关心表使用的是什么存储引擎，但是存储引擎对于优化查询却是有影响的。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表信息统计信息等。对于SELECT语句，在解析查询之前，服务器会先检查查询缓存，如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。
并发控制：
任何时候，只要有多个查询需要同一时刻修改数据，都会产生并发控制问题。这时候，我们就要引入锁的概念，以保证数据的一致性。
读锁和写锁：
读锁是共享的，或者说是相互不阻塞，即使同一时刻多个用户读取同一资源也互不干扰。
写锁则是排他的，也就是说一个写锁会阻塞其他写锁和读锁，只有这样，才能确保在给定时间内，有且只有一个用户执行写入，保证数据的完整和一致性。
锁粒度：
一种提高共享资源并发性的方式就是让锁定对象更有选择性，尽量只锁定需要修改的那部分数据。在给定的资源上，锁定的数据量越少，则系统并发程度越高，只要它们相互之间不发生冲突即可。然而加锁也是会消耗资源的，锁的各种操作，包含获得锁、检查锁是否已经解除和释放锁等，都会增加系统开销。为了在锁的开销和数据安全性之间寻求平衡，就叫锁策略。
表锁：
这是最基本的销策略，是开销最小的，并发性不高。在特定场景中，表锁也可以有良好的性能。例如：READ LOCAL 表锁支持某些类型的并发写入操作。
行级锁：
行级锁可以最大程度地支持并发操作，与此同时锁开销也是最大的。行级锁只在存储引擎层实现，支持行级锁的存储引擎有InnoDB和XtraDB等。
事务：
事务就是一组原子性的SQL查询，或者说是一个独立的最小工作单元。如果数据库能够成功查询全部语句，则执行该组查询；如果其中任何一条语句因为崩溃或其他原因无法执行，则所有语句都不会执行。也就是说，事务内的语句，要么全部执行成功，要么全部失败。衡量事务的质量要用到ACID特性。
事务的特性（ACID）：
原子性：一个事务被视为最小工作单元，整个事务中的所有操作，要么全部提交成功，要么全部失败回滚。
一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。
隔离性：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。
持久性：一旦事务提交，则其所做的修改会永久保存到数据库中。
隔离级别：
未提交读（READ UNCOMMITTED）：事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，被称为脏读（dirty read）。这个级别会导致许多问题，而性能也不比其他级别好多少，一般实际应用中很少使用。
提交读（READ COMMONTTED） ： 一个事务所做的修改在没提交之前，对其他事务是不可见的。又叫不可重复读（nonrepeatable read），因为执行两次同样的查询可能会得到不一样的结果。
可重复读（REPEATABLE READ）：这个级别保证了在同一人个事务中多次读取同样的记录，结果是一致的，但还是无法解决幻读（phantom read）问题。
可串行化（SERIALIZABLE）：它是最高隔离级别，通过强制事务串行执行，从而避免幻读问题。简单来说，这个级别会在读取的每一条数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这么高的隔离级别。
幻读：在一个事务中，同一个范围内的记录被读取时，其他事务向这个范围添加了新的记录。
死锁：
死锁是指两个或多个事务在同一资源上的相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能产生死锁。死锁产生有双重原因：有些是因为真正的数据冲突，这种情况很难避免，但有些则完全是由于存储引擎实现方式导致的。为了解决死锁问题，系统实现了各种死锁检测和死锁超时机制。对于事务系统，死锁是无法避免的，所以应用程序在设计时必须考虑如何处理死锁，通常情况下，只需要重新执行因死锁而回滚的事务即可。
事务日志
可以提高事务的效率，在数据修改时，只需要将修改行为以追加方式持久化到事务日志中。然后在后台，慢慢将事务日志中的数据修改刷回到磁盘中。
自动提交：
MySQL默认采用自动提交模式，也就是说，如果不是显示地开始一个事务，则每条查询都被当作一个事务执行提交操作。可以通过设置AUTOCOMMIT变量来启用或禁用自动提交模式。
在事务中混合使用存储引擎：
MySQL服务层不管理事务，事务是由下层的存储引擎实现的，所以在同一事务中，使用多种存储引擎是不可靠的。例如：在事务中同时使用了事务型和非事务型的表，在事务需要回滚，非事务型的表就无法回滚，最终导致数库处于不一致的状态，而事务的最终结果将无法确定。
隐式和显式锁定：
InnoDB等支持事务的存储引擎会根据隔离级别在需要的时候自动加锁。另外，InnoDB也支持通过特定语句进行显示锁定：SELECT LOCK IN SHARE MODE、SELECT FOR UPDATE。MySQL服务层也支持LOCK TABLES、UNLOCK TABLES语句。
多版本并发控制
大多数事务型存储引擎实现的都不是简单的行级锁，基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制（MVCC）。它没有统一的标准，也可以理解为行级锁的一个变种，它可以在很多情况下避免加锁操作，因此开销更低。它是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。MVCC只在提交读和可重复读两个隔离级别下工作。典型的有乐观并发控制和悲观并发控制。
乐观并发控制：是一种思想，它会先尝试对资源进行修改，在写回时判断资源是否进行了改变，如果没有发生改变就会写回，否则就会进行重试，在整个的执行过程中其实并没有对数据库进行加锁。多用于数据争用不大，冲突较少的环境中。这种环境中偶尔回滚事务的成本会低于读取数据时锁定数据的成本，因此可以获得比其他并发控制方法更高的吞吐量。
悲观并发控制：它会在获取资源前对资源进行加锁，确保同一时刻只有有限的线程能够访问该资源，其他想要尝试获取资源的操作都会进入等待状态，直到该线程完成了对资源的操作并且释放了锁后，其他线程才能重新操作资源。主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。
MyISAM存储引擎：如果一张表中的数据，创建之后，一般不再修改，那么种这表或许适合采用MyISAM存储引擎。MyISAM引擎设计简单，数据以紧密格式存储，所以在某些场景下的性能很好。
选择适合的存储引擎：大多数情况下，InnoDB都是正确的选择。如果需要应用不同的存储引擎，则要考虑到以下几个因素：事务，备份，崩溃恢复，独有特性。
mysql 数据类型： mysql 支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。
更小的通常理好：一般情况应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快。如:保存字符串的可以用 char,varchar,text等,那么优先选择char和varchar
简单就好： 简单数据类型的操作通常需要更少的CPU周期。例如：整型比字符串操作代价更低。
尽量避免NULL：查询中包含可为NULL的列会占用更多存储空间。同时，它也使得索引、索引统计和值比较，都更为复杂。
数据库的优化可以从四个方面来优化
1.从架构层: web服务器采用负载均衡服务器,mysql服务器采用主从复制,读写分离
2.从储存层: 采用合适的数据类型，存储引擎,采用三范式
3.从设计层: 采用分区分表,索引,表的字段采用合适的字段属性,适当的采用逆范式,开启mysql缓存
4.sql语句层:结果一样的情况下,采用效率高,速度快节省资源的sql语句执行
SQL语句优化有哪些方法
1）Where子句中：where表之间的连接必须写在其他Where条件之前，那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。
2）用EXISTS替代IN、用NOT EXISTS替代NOT IN。
3） 避免在索引列上使用计算
4）避免在索引列上使用IS NULL和IS NOT NULL
5）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
6）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
7）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
开启慢查询日志：
#开启慢查询日志
set global slow_query_log = on;
#设置慢查询日志文件路径
set global slow_query_log_file = 'e:\warehouse\mysql-slow.log';
#是否记录未使用索引的sql语句
set global log_queries_not_using_indexes = on;
#慢查询时间长度
set global long_query_time = 0;
#查看设置后的结果
show variables like 'slow_query_log';
show variables like 'log_queries_not_using_indexes';
show variables like 'slow_query_log_file';
show variables like 'long_query_time';
#执行一些sql语句
SELECT * FROM address limit 110;
