MySQL优化（一）

MySQL优化（一）

MySQL的逻辑架构

第一层：连接处理层（处理客户端的连接、权限认证、身份认证…）
第二层：核心服务层（解析sql语句，并且进行优化选择，存储过程、函数、视图都是在这一层解析执行的）
第三层：存储引擎层（提取和存储数据）
第四层：物理磁盘层（硬盘）

线程处理

（1）每个客户端连接（可以是客户端工具的一个连接或应用程序创建的一个连接）都会对应一个单独的线程。该连接的查询操作只会在这个单独的线程中执行。该线程只能轮流在某个CPU核心或者CPU中运行；
（2）服务器端有线程缓存和线程池（mysql5.6才有线程池），所以并不需要为每个新的连接创建线程。

优化与执行

（1）客户端发送一条查询给服务器

（2）服务器通过权限检查之后（用户名和密码等），先检查查询缓存，如果命中了缓存，立即返回缓存中的数据。否则进入下一阶段。

（3）服务器端进行SQL解析，预处理（词法、语法分析）再由又花钱生成对应的执行计划。

（4）根据优化器生成的执行计划，调用存储引擎的API来执行查询。

（5）将结果返回给客户端。

并发控制

只要有多个查询需要在同一时间修改数据都会产生并发控制的问题。

读写锁

在处理并发读或者写时，可以通过实现一个由两种类型的锁组成的系统来解决问题。这两种类型的锁通常被称为共享锁（shared lock）和排他锁（exclusive lock）,也叫读锁(read lock) 和写锁（write lock）。
读锁是共享的，或者说是相互不阻塞的。多个客户同一时刻可以同时读取同一个资源，互不干扰。写锁则是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，这是出于安全策略考虑，只有这样，才能确保在给定的时间里，只有一个用户执行写入，并防止其他用户读取正在写入的同一资源。
在实际的数据库系统中，每时每刻都在发生锁定，当某个用户在修改某一部分数据时，MySQL会通过锁定防止其他用户读取同一数据。大多时间，MySQL锁内部管理是透明的。

锁粒度

锁粒度就是锁的级别，如果只锁定修改的部分而不是所有的资源那么耗费的资源就会比较少，因为加锁也会耗费资源，锁定的数据量越少，则系统的并发程度就会越高。锁策略就是在锁的开销和数据的安全性之间寻求平衡。
一般都是在表上施加行锁（level lock）但是MySQL会在存储引擎层将锁粒度固定在某个级别。
优点：可以为某些特定的应用场景提供很好的性能
缺点：会失去两外一些应用场景的良好支持

表锁（table lock）

表锁是MySQL中最基本，且开销最小的策略。表锁类似于邮箱加锁机制，它会锁整张表。一个用户对表进行操作前需要先获得写锁，这回足鳃其他用户对该表的所有读写操作，只有没写锁时，读取时其他用户才可以获得读锁，读锁之间不互相阻塞。

行级锁（row lock）

行级锁可以最大程度地支持并发处理（同时带来了最大的锁开销）。在InnoDB和XtraDB，以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎层实现，而MySQL服务器层没有实现。服务器层完全不了解存储引擎中的锁实现

MySQL的存储引擎

数据库存储引擎是数据库底层软件组件，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不多的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
MySQL提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。MySQL5.7支持的存储引擎有：InnoDB,MyISAM,Memory,Merge,Archive,Federate,CSV,BLACKHOLE等。可以使用SHOW ENGINES语句查看系统支持的引擎类型。

相关命令：
show engines; - 查询当前数据库支持的存储引起
show variables like '%storage_engine%' - 查询当前默认的存储引擎  
show table status like '表名' - 查询表的相关信息（存储引擎）

选择存储引擎考虑因素

事务、备份、崩溃恢复

MyISAM Vs InnoDB

MyISAM：MySQL5.5之前，默认的存储引起
InnoDB：MySQL5.5之后，包括5.5，默认的存储引擎
区别：
1、MyISAM不支持事务，InnoDB支持事务
2、MyISAM只有表锁，InnoDB引入了行锁
3、MyISAM不支持外键，InnoDB支持外键
4、MyISAM支持全文索引，InnoDB（5.6以后的版本支持）
5、MyISAM读写数据的速度相对比InnoDB要快

MEMORY存储引擎

MEMORY 存储引擎将表中的数据存储在内存中，为查询和引用其他表数据提供快速访问。
MEMORY 主要特性有：
1、使用表级锁，虽然内存访问快，但如果频繁的读写，表级锁会成为瓶颈；
2、只支持固定大小的行。Varchar类型的字段会存储为固定长度的Char类型，浪费空间；
3、不支持TEXT、BLOB字段。当有些查询需要使用到临时表（使用的也是MEMORY存储引擎）时，如果表中有TEXT、BLOB字段，那么会转换为基于磁盘的MyISAM表，严重降低性能；
4、由于内存资源成本昂贵，一般不建议设置过大的内存表，如果内存表满了，可通过清除数据或调整内存表参数来避免报错；
5、服务器重启后数据会丢失，复制维护时需要小心。