读高性能MySql笔记
1.1 MySql逻辑架构
SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。
MySql服务器逻辑架构图
1、连接管理与安全性
每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销毁线程。
当客户端（应用）连接到ＭｙＳｑｌ服务器时，服务器需要对其进行认证。认证基于用户名、原始主机信息和密码。一旦客户端连接成功，服务器会继续验证该客户端是否具有执行某个特定查询的权限。
２、优化与执行
MySql会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示（hint）优化器，影响它的决策过程。也可以请求优化器解释（explain）优化过程的各个因素，使用户知道服务器时如何进行优化决策的，并提供一个参考基准，便于用户重构查询和schema、修改相关配置，使应用尽可能高效运行。（sql优化）
如：对于SELECT语句，在解析查询之前，服务器会先检查查询缓存（Query Cache），如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。
1.2 并发控制
无论何时，只要有多个查询需要再同一时刻修改数据，都会产生并发控制的问题
1、读写锁
共享锁（share lock），也叫读锁（read lock）
读锁是共享的，相互不阻塞的。多个线程在同一时刻可以读取同一个资源，而相互不干扰
排他锁（exclusive lock），也叫写锁
写锁是排他的，一个写锁会阻塞其他的读锁和写锁，只有这样才能确保在给定的时间里，只有一个线程能执行写入，并阻止其他线程读取正在写入的同一资源
在实际的数据库系统值，每时每刻都在发生锁定，当某个用户在修改某一部分数据时，MySql会通过锁定防止其他用户读取同一数据，大多数时候，MySql锁的内部管理都是透明的。
1.2 锁粒度
一种提高共享资源并发性的方式就是让锁定资源对象更有选择性，尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是只对修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。
加锁也需要消耗各种资源。锁的各种操作，包括获得锁、检查锁是否已经解除、释放锁等，都会增加系统的开销。如果系统花费大量的时间来管理锁，而不是读取数据，那么系统的性能可能会因此受到影响。
所谓的锁策略，就是在锁的开销和数据的安全性之间追求平衡，这种平衡当然也会影响到性能。
表锁（table lock）
表锁是MySql开销最小的策略。表锁会锁定整张表。一个用户在对表进行写操作（插入、修改、删除）前需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获得读锁，读锁之间是不相互阻塞的。
写锁比读锁有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面，而读锁不能插入到写锁的前面。
如服务器会为ALTER TABLE之类的语句使用表锁
行锁（row lock）
行锁可以最大程度的支持并发处理（同事也带来了最大的锁开销），行级锁只在存储引擎层实现。
1.3 事务
事务就是一组原子性的SQL语句。如果数据库引擎能够全部成功执行该组语句，那么就执行该组语句。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所欲语句都不会执行（执行过的回滚）。也就是说事务内的语句，要么全部执行成功，要么全部执行失败。
事务的四大特性
原子性（atomicity）：一个事务必须被视为一个不可分割的最小工作单元（可联想化学中的原子是最小单位，不
可分割），整个事务中的所有操作要么全部提交成功，要么全部失败回滚。
一致性（consistency）：数据库总是从一个一致性状态转换成另外一个一致性状态。如转账扣钱后加钱失败，扣
钱也会回滚，事务中的修改不会保存到数据库中
隔离性（isolation）：通常来说，一个事务所作的修改在最终提交之前，对其他事务是不可见的。
持久性（durability）：一旦事务提交，则其所作的修改就会永久保存到数据库中。即使系统崩溃，修改的数据也不
会丢失。
隔离级别
读未提交（READ UNCOMMITED）：事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未
提交的数据，这也被称为脏读（Dirty Read）。这个隔离级别会导致很多问题，一般不会使用。
读已提交（READ COMMITED），也叫不可重复读：只能读取到其他事务已经提交的数据，即一个事务从开始到
提交之前，所做的修改对其他事务都是不可见的。所以两次执行同样的查询，可能会得到不一样的结果
可重复读（REPEATABLE READ）：
该隔离级别保证了在同一个事务中多次读取同样的记录结果是一致的。解决了脏读的问题，但是无法解决幻读
的问题，所谓幻读，指的是当某个事务在读取某个范围内的记录时，会产生幻行，即两次读取的数据行不
一致。InnoDB使用多版本并发控制MVCC来解决幻读问题。
可重复读是MySql默认的隔离级别
可串行化（SERIALIZABLE）：最高的隔离级别，强制事务串行执行，避免了脏读和幻读的问题，但是会在读取
的每一行数据上都加锁，可能导致大量的超时和锁竞争的问题，性能很低，所以很少用此隔离级别
1.3.2 死锁
死锁是指两个或多个事务在同意资源上相互占用，并请求锁定对方占用的资源（相互请求锁定对方已经锁定占
用的资源），从而导致恶性循环等待的现象。
多个事务视图以不同的顺序锁定多个相同的资源时，可能导致死锁；多个事务同时锁定相同的资源时，也会产
生死锁。
如：两个事务以不同的顺序同时修改两行记录，两个事务分别在修改了第一行记录后，也锁定了该行记录，同
时尝试去修改第二条记录时，发现已被对方锁定。这时两个事务都等待对方释放锁，同时又持有对方需要的锁
，则陷入死循环，造成死锁。
为了解决死锁问题，数据库系统实现了各种死锁检测和死锁超时机制。InnDB能检测到死锁的循环依赖，并立即返回一个错误。InnDB目前处理的死锁方法是，将持有最少行级排他锁的事务进行回滚。
1.4 多版本并发控制
MySQL中的大多数事务存储引擎实现的都不是简单的行级锁，同时基于并发性能的考虑，它们一般都同时实现了多版本并发控制（Multiy Version Concurrent Control）MVCC。
MVCC同样实现了行级锁的功能，但是它在很多情况下避免了加锁的操作，因此开销更低。
MVCC是通过保存数据在某个时间点的快照来实现的。即根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。但是对每个事务来说，在它的执行期间，不管它执行多长时间，它看到的数据都是一致的。
存储引擎实现MVCC典型的有乐观并发控制（乐观锁）和悲观并发控制（悲观锁）
InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列一个保存了行的创建系统版本号，一个保存行的过期（或删除）版本号。
每开始一个新的事务，所有记录的系统版本号（应该是创建系统版本号）都会递增。事务开始时刻的系统版本号会作为当前事务的版本号（疑问：也就是说每行版本号都相同？）用来和查询到的每行记录的版本号进行比较（在事务执行期间，可能会有部分记录行）。下面是可重复读隔离级别下，InnoDB实现MVCC的具体操作：
SELECT：
1、InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号）
，这样可以确保事务读取的行，要么在事务开始前已经存在的，要么是事务自身插入或修改过的（这是执行插
入和修改系统版本号未变）。
2、行的删除版本号要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前是
存在的，未被删除。
只有满足这两个条件的记录，才能返回作为查询结果
INSERT：InnoDB为新插入的每一行保存当前系统版本号作为行版本号
DELETE：InnoDB为删除的每一行保存当前系统版本号作为删除版本号
UPDATE：删除原来的行插入当前系统版本号作为删除版本号，并插入一条新的记录，保存当前系统版本号作为行版本号
MVCC只在REPEATABLE READ和READ COMMITED中工作，因为READ UNCOMMITED总会读取最新的数据行，而SERIALIZABLE会对所有读取的行加锁。
创建高性能的索引
一、索引的类型
1、B-Tree索引（实际是B+Tree）：使用B-Tree数据结构来存储数据。
B-Tree通常意味着所有的值都是按顺序存储的，并且每个叶子页到根的距离相同。
B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下一层子节点，直到找到为止。
可以使用B-Tree索引的查询类型
全值匹配：查找条件和索引中的所有列进行匹配
匹配最左前缀：查询条件是索引的第一列
匹配列前缀：查询条件是索引第一列的开头的部分，模糊查询
匹配范围值：使用索引列作为查询范围的条件
精确匹配某一列并范围匹配另外一列：即查询条件一个是索引第一列全匹配，索引第二列开头部分前缀匹配
只访问索引的查询：查询的列和条件只需要根据索引即可查询到，不需要访问叶子结点
因为索引中的节点是有序的，索引除了按值查找之外，order by的列也可以使用索引（按顺序排序）。即如果B-Tree索引可以按照某种方式查找到值，也可以按照这种方式进行排序。
B-Tree索引的限制：
1、如果不是按照索引的最左列开始查找，则无法使用索引（最左原则）
2、不能跳过索引中的列，即索引是abc，ac作为条件不会使用索引（最左原则）
3、如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引优化查找。
因此索引中列的顺序很重要，在优化性能的时候可能使用相同的列但顺序不同的索引来满足不同类型的范围查找。
2、哈希索引
基于哈希表实现，只有精确匹配索引所有列的查询才有效。
3、空间数据索引（R-Tree）
4、全文索引
查找文本中的关键词，而不是直接比较索引中的值。
二、索引的优点
索引可以让服务器快速定位到表的指定位置，但并不是唯一作用。最常见的B-Tree索引，按照索引列顺序存储数据，索引还可以用来ORDER BY和GROUP BY操作。另外，索引中存储了存储了索引列的实际值，所以某些查询只使用索引就能够完成全部查询。总结有下面三个优点：
1、索引大大减少了服务器需要扫描的数据量
2、索引可以帮助服务器避免排序和临时表
3、索引可以将随机I/O变为顺序I/O
索引是否适合某个查询的三星系统：1、索引将相关的记录放到一起则获取一星 2、如果索引中的数据顺序和查找中的排列顺序一直则或得二星 3、如果索引中的列包含了查询中需要的全部列则获得三星。
三、高性能的索引策略（重要）
sql优化中的索引优化技巧，如何避免优化器放弃使用索引
1、独立的列
如果查询条件中的列不是独立（索引列不能是表达式的一部分也不能是函数的参数）的，则MySQL就不会使用该列
的索引。
2、前缀索引和索引选择度
有时候需要索引很长的字符串，但会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，
从而提高查询效率。索引的选择度是指，索引列中的不重复的值占数据表记录总数（T）的比值（范围1/T-1）之间，索引的选择度越高，则过滤掉的数据行越多，查询效率越高。唯一索引的选择度最高为1，因为索引列中的每行值都不同，性能最好。
一般情况下某个列前缀的选择度也是足够高的，足以满足查询的性能，对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引。
