1.MySQL Server 系统架构
逻辑模块组成：MySQL 可以看成是二层架构，第一层我们通常叫做SQL Layer，在MySQL 数据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断，sql 解析，执行计划优化，query cache 的处理等等；第二层就是存储引擎层，我们通常叫做StorageEngine Layer，也就是底层数据存取操作实现部分，由多种存储引擎共同组成。
逻辑架构图
SQL-Layer组成：
1、初始化模块：对整个系统做各种各样的初始化操作，比如各种buffer，cache 结构的初始化和内存空间的申请，各种系统变量的初始化设定，各种存储引擎的初始化设置
2、核心API：提供一些需要非常高效的底层操作功能的优化实现，包括各种底层数据结构的实现，特殊算法的实现，字符串处理，数字处理等，小文件I/O，格式化输出，以及最重要的内存管理部分。
3、网络交互模块：，实现底层网络数据的接收与发送
4、Client & Server 交互协议模块：实现了客户端与MySQL 交互过程中的所有协议。当然这些协议都是建立在现有的OS 和网络协议之上的，如TCP/IP 以及Unix Socket
5、用户模块：主要包括用户的登录连接权限控制和用户的授权管理
6、访问控制模块：控制用户对数据的访问，将用户模块和访问控制模块两者结合起来，组成了MySQL 整个数据库系统的权限安全管理的功能。
7、连接管理、连接线程和线程管理：连接线程的主要工作就是负责MySQL Server 与客户端的通信，接受客户端的命令请求，传递Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的cache 等。
8：Query 解析和转发模块：主要工作就是将query 语句进行语义和语法的分析，然后按照不同的操作类型进行分类，然后做出针对性的转发。
9、Query Cache 模块：主要功能是将客户端提交给MySQL 的Select 类query 请求的返回结果集cache 到内存中
10、Query 优化器模块：优化客户端请求的query
11、表变更管理模块：表变更管理模块主要是负责完成一些DML 和DDL 的query，如：update，delte，insert，create table，alter table 等语句的处理
12、表维护模块:表的状态检查，错误修复，以及优化和分析等工作都是表维护模块需要做的事情。
13、系统状态管理模块：系统状态管理模块负责在客户端请求系统状态的时候，将各种状态数据返回给用户
14、表管理器：主要内容是各个表的结构信息。此外它还维护table 级别的锁管
15、日志记录模块日志记录模块主要负责整个系统级别的逻辑层的日志的记录，包括error log，binarylog，slow query log 等。
16、复制模块：复制模块又可分为Master 模块和Slave 模块两部分， Master 模块主要负责在Replication 环境中读取Master 端的binary 日志，以及与Slave 端的I/O 线程交互等工作。Slave 模块比Master 模块所要做的事情稍多一些，在系统中主要体现在两个线程上面。一个是负责从Master 请求和接受binary 日志，并写入本地relay log 中的I/O 线程。另外一个是负责从relay log 中读取相关日志事件，然后解析成可以在Slave 端正确执行并得到和Master 端完全相同的结果的命令并再交给Slave 执行的SQL 线程。
17、存储引擎接口模块：实现可插拔存储引擎
处理流程图
2.日志文件
1、错误日志：Error Log
错误日志的默认存放位置在数据目录下，以hostname.err 命名
log-error[=file_name]，修改其存放目录和文件名。
2、二进制日志：Binary Log & Binary Log Index
log-bin[=on|off]：打开/关闭记录功能
max_binlog_size：设置binlog 的最大存储上限，当日志达到该上限时，MySQL 会重新创建一个日志开始继续记录
3、查询日志：query log
general_log[=on|off]：打开/关闭查询日志
general_log_file[=filename]： 查询日志文件名
4、慢查询日志：slow query log
slow_query_log[=on|off]：打开/关闭慢查询日志
long_query_time[=long]：超过多少秒的查询就写入日志
5、Innodb 的在线redo 日志：innodb redo log
Innodb 是一个事务安全的存储引擎，其事务安全性主要就是通过在线redo 日志和记录在表空间中的undo 信息来保证的。redo 日志中记录了Innodb 所做的所有物理变更和事务信息，可以通过innodb_log_group_home_dir 来更改设置日志的存放位置，通过innodb_log_files_in_group 设置日志的数量。
3.数据文件
1、“.MYD”文件：MyISAM 存储引擎专用，存放MyISAM 表的数据。每一MyISAM 表都会有一个“.MYD”文件与之对应。
2、“.MYI”文件：专属于MyISAM 存储引擎，主要存放MyISAM 表的索引相关信息
3、“.ibd”文件和ibdata 文件：独享表空间存储方式使用“.ibd”文件来存放数据，且每个表一个“.ibd”文件，文件存放在和MyISAM 数据相同的位置。如果选用共享存储表空间来存放数据，则会使用ibdata 文件来存放，所有表共同使用一个（或者多个，可自行配置）ibdata 文件。ibdata 文件可以通过innodb_data_home_dir 和innodb_data_file_path两个参数共同配置组成
innodb_data_file_path默认值是ibdata1:12M:autoextend，ibdata共享表文件，12M设置表空间大小，autoextend自动扩展。
4.Replication相关文件
1、master.info 文件：master.info 文件存在于Slave 端的数据目录下，里面存放了该Slave 的Master 端的相关信息，包括Master 的主机地址，连接用户，连接密码，连接端口，当前日志位置，已经读取到的日志位置等信息。
2、relay log 和relay log index：mysql-relay-bin.xxxxxn 文件用于存放Slave 端的I/O 线程从Master 端所读取到的Binary Log 信息，然后由Slave 端的SQL 线程从该relay log 中读取并解析相应的日志信息，转化成Master 所执行的SQL 语句，然后在Slave 端应用。
3、 relay-log.info 文件：类似于master.info，它存放通过Slave 的I/O 线程写入到本地的relay log 的相关信息
5.其他文件
1、system config file：MySQL 的系统配置文件一般都是“my.cnf”，Unix/Linux 下默认存放在"/etc"目录下，Windows 环境一般存放在“c:/windows”目录下面。
2、pid file：mysqld 应用程序在Unix/Linux 环境下的一个进程文件，和许多其他Unix/Linux 服务端程序一样，存放着自己的进程id。
3、socket file：用户在Unix/Linux 环境下客户端连接可以不通过TCP/IP 网络而直接使用Unix Socket 来连接MySQL。
6.mysql自带工具
mysqladmin：与MySQL 管理相关的各种功能
Usage: mysqladmin [OPTIONS] command command ...
mysqladmin -uroot -p123 -hlocalhost ping //检测MySQL Server 是否还能正常提供服务
>mysqld is alive
mysqladmin -uroot -p123 -hlocalhost status // 获取当前MySQL Server 的几个基本的状态值
>Uptime: 20960 Threads: 1 Questions: 75 Slow queries: 0 Opens: 15 Flush
>tables: 1 Open tables: 9 Queries per second avg: 0.3
mysqladmin -uroot -p123 -hlocalhost processlist //获取当前数据库的连接线程信息
此外，还可以通过mysqladmin 来start slave 和stop slave，kill 某个连接到MySQL Server 的线程等等。
mysqldump :将MySQL Server中的数据以SQL 语句的形式从数据库中dump 成文本文件。这
个功能实际上是调用了MySQL 中的“select * into OUTFILE from ...”语句而实现
Usage: mysqldump [OPTIONS] database [tables].
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
可以通过“-d,--no-data”仅仅生成结构创建的语句,每次执行mysqldump 程序的时候都通过尽量做到“--defaultcharacter-set=name”显式指定字符集内容
mysqlimport ：mysqlimport 程序是一个将以特定格式存放的文本数据（如通过“select * into OUTFILE from ...”所生成的数据文件）导入到指定的MySQL Server 中的工具程序
Usage: mysqlimport [OPTIONS] database textfile ...
mysqlbinlog：通过mysqlbinlog，我们可以解析出binlog 中指定时间段或者指定日志起始和结束位置的内容解析成SQL 语句，并导出到指定的文件中，在解析过程中，还可以通过指定数据库名称来过滤输出内容。
Usage: mysqlbinlog [OPTIONS] log-files
mysqlcheck
Usage: mysqlcheck [OPTIONS] database [tables]
OR mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...]
OR mysqlcheck [OPTIONS] --all-databases
mysqlcheck 工具程序可以检查（-c），修复（-r），分析（ -a）和优化 （-o）MySQL Server 中的表
myisamchk
Usage: myisamchk [OPTIONS] tables[.MYI]
功能有点类似“mysqlcheck -c/-r”，对检查和修复MyISAM 存储引擎的表，但只能对MyISAM 存储引擎的索引文件有效
myisampack
Usage: myisampack [OPTIONS] filename ...
对MyISAM 表进行压缩处理，以缩减占用存储空间，一般主要用在归档备份的场景下，而且压缩后的MyISAM 表会变成只读，不能进行任何修改操作。
mysqlhotcopy
Usage: mysqlhotcopy db_name[./table_regex/] [new_db_name | directory]
仅能在Unix/Linux 环境下使用。他的主要功能就是对MySQL 中的MyISAM 存储引擎的表进行在线备份操作，其备份操作实际上就是通过对数据库中的表进行加锁，然后复制其结构，数据和索引文件来完成备份操作
7.数据引擎
MySQL 的插件式存储引擎主要包括MyISAM，Innodb，NDB Cluster，Maria，Falcon，Memory，Archive，Merge，Federated 等
MyISAM 存储引擎
特点：1、表的行结构紧凑，浪费空间少，因此比较适合数据仓库系统
2、因为没有redo、没有undo，因此进行insert的时候，速度相对较快
MyISAM 支持以下三种类型的索引：1、B-Tree 索引 2、R-Tree 索引 3、Full-text 索引
Innodb 存储引擎
特点：1、支持事务安装 2、数据多版本读取 3、使用行级锁 4、实现外键
Innodb 的物理结构分为两大部分：
1、数据文件（表数据和索引数据）
在Innodb 中，存在了表空间（tablespace）这样一个概念，Innodb 的表空间分为两种形式。一种是共享表空间，也就是所有表和索引数据被存放在同一个表空间（一个或多个数据文件idata）中，通过innodb_data_file_path 来指定，增加数据文件需要停机重启,Innodb 的undo 信息和其他一些元数据信息都是存放在共享表空间里面的。另外一种是独享表空间，也就是每个表的数据和索引被存放在一个单独的.ibd 文件中。
共享表空间增加数据文件的操作比较简单， 只需要在innodb_data_file_path 参数后面按照标准格式设置好文件路径和相关属性即可，不过这里有一点需要注意的，就是Innodb 在创建新数据文件的时候是不会创建目录的，如果指定目录不存在，则会报错并无法启动
2、日志文件
由于Innodb 是事务安全的存储引擎，所以系统Crash 对他来说并不能造成非常严重的损失，由于有redo 日志的存在，有checkpoint 机制的保护，Innodb 完全可以通过redo 日志将数据库Crash 时刻已经完成但还没有来得及将数据写入磁盘的事务恢复，也能够将所有部分完成并已经写入磁盘的未完成事务回滚并将数据还原。
Innodb 的所有参数基本上都带有前缀“innodb_”。
NDB Cluster存储引擎
NDB 存储引擎也叫NDB Cluster 存储引擎，主要用于MySQL Cluster 分布式集群环境，Cluster 是MySQL 从5.0 版本才开始提供的新功能。
一般来说，一个Mysql Cluster 的环境主要由以下三部分组成：
a) 负责管理各个节点的Manage 节点主机：于管理节点上保存在整个Cluster 环境的配置，同时担任了集群中各节点的基本沟通工作，所以他必须是最先被启动的节点。
b) SQL 层的SQL 服务器节点（后面简称为SQL 节点），也就是我们常说的Mysql Server：主要负责实现一个数据库在存储层之上的所有事情，比如连接管理，query 优化和响应，cache 管理等等，只有存储层的工作交给了NDB 数据节点去处理了。SQL 层各Mysql 服务器的启动与普通的Mysql 启动有一定的区别，必须要添加ndbcluster 项，可以添加在my.cnf 配置文件中，也可以通过启动命令行来指定。
c) Storage 层的NDB 数据节点，也就是上面说的NDB Cluster：NDB 节点主要是实现底层数据存储的功能，保存Cluster 的数据。每一个NDB 节点保存完整数据的一部分。
其他存储引擎
Merge存储引擎：MERGE 存储引擎可以简单的理解为其功能就是实现了对结构相同的MyISAM 表，通过一些特殊的包装对外提供一个单一的访问入口，以达到减小应用的复杂度的目的
Memory存储引擎：将数据存储在内存中的存储引擎。MySQL Crash 或者主机Crash 之后，Memory 的表就只剩下一个结构了。Memory 表支持索引，并且同时支持Hash 和B－Tree 两种格式的索引。
另外还有BDB 存储引擎、FEDERATED存储引擎、ARCHIVE存储引擎、BLACKHOLE存储引擎、CSV存储引擎，不一一列举。
8.权限系统
MySQL 的权限系统在实现上比较简单，相关权限信息主要存储在几个被称为granttables 的系统表中，即： mysql.User，mysql.db，mysql.Host，mysql.table_priv 和mysql.column_priv。
mysql 在启动的时候，就会将所有的权限信息都Load 到内存中保存在几个特定的结构中,所以才有我们每次手工修改了权限相关的表之后，都需要执行“FLUSH PRIVILEGES”命令重新加载MySQL的权限信息。当然，如果我们通过GRANT，REVOKE 或者DROP USER 命令来修改相关权限，则不需要手工执行FLUSH PRIVILEGES 命令，因为通过GRANT，REVOKE 或者DROP USER 命令所做的权限修改在修改系统表的同时也会更新内存结构中的权限信息。
权限授予与去除：要为某个用户授权，可以使用GRANT 命令，要去除某个用户已有的权限则使用REVOKE命令。当给某个用户授权的时候，不仅需要指定用户名，同时还要指定来访主机。
查看某个用户目前拥有的权限可以通过“SHOW GRANTS FOR 'username'@'hostname'”和查询mysql.user里面的权限信息。
创建用户：CREATE USER 'username'@'host' IDENTIFIED BY 'password';
授权：GRANT privileges ON databasename.tablename TO 'username'@'host'
用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL。 如果想让该用户可以授权，用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
设置与更改用户密码:SET PASSWORD FOR 'username'@'host' =PASSWORD('newpassword');
撤销用户权限：:REVOKE privilege ON databasename.tablename FROM 'username'@'host';
删除用户：DROP USER 'username'@'host';
9.数据库备份
mysqldump逻辑备份
”--single-transaction”选项，可以达到备份数据的一致性和完整性
“--lock-tables”和“--lock-all-tables”，让数据库在备份过程中仅提供数据的查询服务，锁定写入的服务，来使数据暂时处于一个一致的不会被修改的状态
“--master-data[=value]”会将当前MySQL 使用到binlog 日志的名称和位置记录到dump 文件中。这个选项在实施slave 的在线搭建的时候是非常有用的，即使不是进行在线搭建slave，也可以在某些情况下做恢复的过程中通过备份的binlog 做进一步恢复操作。
"--no-data”仅仅dump 数据库结构创建脚本
mysqldump逻辑恢复
可以通过在mysql 中执行“source /path/backup.sql”或者“\. /path/backup.sql”来进行恢复。或在命令行使用mysql -uusername -p < backup.sql
数据库物理课备份
数据库的物理备份就是对数据库的物理对象所做的备份。
MyISAM物理备份需要备份的内容有：.frm”文件，存储表数据的“.MYD”文件，以及存储索引数据的“.MYI”文件
Innodb 存储引擎“innodb_data_home_dir”和“innodb_data_file_path”参数所设定的所有数据文件，“datadir”中相应数据库目录下的所有Innodb 存储引擎表的“.frm”文件“.idb”文件，redo文件
NDB Cluster 存储引擎1、元数据（Metadata）2、表数据（Table Records）3、事务日志数据（Transaction Log）：
我们也可以通过登录数据库中手工加锁，然后再通过操作系统的命令来复制相关文件执行热物理备份，且在完成文件copy 之前，不能退出加锁的session（因为退出会自动解锁），执行FLUSH TABLES WITH READ LOCK;
10.MyISAM 锁优化
MyISAM 表锁优化建议优化MyISAM 存储引擎锁定问题的时候，最关键的就是如何让其提高并发度。由于锁定级别是不可能改变的了，所以我们首先需要尽可能让锁定的时间变短，然后就是让可能并发进行的操作尽可能的并发。
1、缩短锁定时间
a) 尽两减少大的复杂Query，将复杂Query 分拆成几个小的Query 分布进行；
b) 尽可能的建立足够高效的索引，让数据检索更迅速；
c) 尽量让MyISAM 存储引擎的表只存放必要的信息，控制字段类型；
d) 利用合适的机会优化MyISAM 表数据文件；
2、分离能并行的操作
配置是Concurrent Insert（并发插入），MyISAM 存储引擎有一个控制是否打开Concurrent Insert 功能的参数选项：concurrent_insert，可以设置为0，1 或者2。三个值的具体说明如下：
a) set global concurrent_insert=2（always）2，无论MyISAM 存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都允许在数据文件尾部进行Concurrent Insert;
b) set global concurrent_insert=1(auto)当MyISAM 存储引擎表数据文件中间不存在空闲空间的时候，可以从文件尾部进行Concurrent Insert;
c) set global concurrent_insert=0(never)，无论MyISAM 存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都不允许Concurrent Insert。
3、合理利用读写优先级
如果我们的系统是一个以读为主，而且要优先保证查询性能的话，我们可以通过设置系统参数选项low_priority_updates=1，如果我们的系统需要有限保证数据写入的性能的话，则可以不用设置low_priority_updates参数
11.innodb锁优化
Innodb 存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM 的表级锁定的
a) 尽可能让所有的数据检索都通过索引来完成，从而避免Innodb 因为无法通过索引键加锁而升级为表级锁定；
b) 合理设计索引，让Innodb 在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query 的执行；
c) 尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；
d) 尽量控制事务的大小，减少锁定的资源量和锁定时间长度；
e) 在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL 因为实现事务隔离级别所带来的附加成本；
减少死锁产生概率建议：
a) 类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；
b) 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
c) 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
系统锁定争用情况查询：show status like 'table%';
争用状态变量
Table_locks_immediate：产生表级锁定的次数；
Table_locks_waited：出现表级锁定争用而发生等待的次数，如果Table_locks_waited 状态值比较高，那么说明系统中表级锁定争用现象比较严重
innodb锁定争用情况查询：show status like 'innodb_row_lock%';
innodb锁争用查询
Innodb_row_lock_current_waits：当前正在等待锁定的数量；
Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
Innodb_row_lock_time_avg：每次等待所花平均时间；
Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
Innodb_row_lock_waits：系统启动后到现在总共等待的次数；
12.Query 的优化
Query 语句优化基本思路和原则
1. 优化更需要优化的Query；
一般来说，高并发低消耗（相对）的Query 对整个系统的影响远比低并发高消耗的Query 大(io次数高cpu消耗大)。
2. 定位优化对象的性能瓶颈；
PROFILING 功能很清楚的找出一个Query 的瓶颈所在。
1、 开启 profiling 参数set profiling=1; 2、 执行 Query
3、show profiles;获取系统中保存的所有 Query 的 profile 概要信息
4、 针对单个 Query 获取详细的 profile 信息show PROFILE cpu ,block io for query 116
详细的 profile 信息
3. 从Explain 入手；
Explain 功能中给我们展示的各种信息的解释：
ID：Query Optimizer 所选定的执行计划中查询的序列号；
Select_type：所使用的查询类型，主要有以下这几种查询类型
◇ DEPENDENT SUBQUERY：子查询中内层的第一个SELECT，依赖于外部查询的结果集；◇ DEPENDENT UNION：子查询中的UNION，且为UNION 中从第二个SELECT 开始的后面所有SELECT，同样依赖于外部查询的结果集；◇ PRIMARY：子查询中的最外层查询，注意并不是主键查询；◇ SIMPLE：除子查询或者UNION 之外的其他查询；◇ SUBQUERY：子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集；◇ UNCACHEABLE SUBQUERY：结果集无法缓存的子查询；◇ UNION：UNION 语句中第二个SELECT 开始的后面所有SELECT，第一个SELECT 为PRIMARY◇ UNION RESULT：UNION 中的合并结果；
Table：显示这一步所访问的数据库中的表的名称；
Type：告诉我们对all：全表扫描表所使用的访问方式
◇ const：读常量，且最多只会有一条记录匹配，由于是常量，所以实际上只需要读一次；◇ eq_ref：最多只会有一条匹配结果，一般是通过主键或者唯一键索引来访问；◇ fulltext：◇ index：全索引扫描；◇ index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行merge 之后再读取表数据；◇ index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或者唯一索引；◇ rang：索引范围扫描；◇ ref：Join 语句中被驱动表索引引用查询；◇ ref_or_null：与ref 的唯一区别就是在使用索引引用查询之外再增加一个空值的查询；◇ system：系统表，表中只有一行数据；◇ unique_subquery：子查询中的返回结果字段组合是主键或者唯一约束
Possible_keys：该查询可以利用的索引. 如果没有任何索引可以使用，就会显示成null
Key：MySQL Query Optimizer 从possible_keys 中所选择使用的索引；
Key_len：被选中使用索引的索引键长度；
Ref：列出是通过常量（const），还是某个表的某个字段（如果是join）来过滤（通过key）的；
Rows：MySQL Query Optimizer 通过系统收集到的统计信息估算出来的结果集记录条数；
Extra：查询中每一步实现的额外细节信息
◇ Distinct：查找distinct 值，所以当mysql 找到了第一条匹配的结果后，将停止该值的查询而转为后面其他值的查询；◇ Full scan on NULL key：子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用使用；◇ Impossible WHERE noticed after reading const tables：MySQL Query Optimizer 通过收集到的统计信息判断出不可能存在结果；◇ No tables：Query 语句中使用FROM DUAL 或者不包含任何FROM 子句；◇ Not exists：在某些左连接中MySQL Query Optimizer 所通过改变原有Query 的组成而使用的优化方法，可以部分减少数据访问次数；◇ Range checked for each record (index map: N)：通过MySQL 官方手册的描述，当MySQL Query Optimizer 没有发现好的可以使用的索引的时候，如果发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL 检查是否可以使用range 或index_merge 访问方法来索取行。◇ Select tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段的时候，MySQL Query Optimizer 会通过索引而直接一次定位到所需的数据行完成整个查询。当然，前提是在Query 中不能有GROUP BY 操作。如使用MIN()或者MAX（）的时候；◇ Using filesort：当我们的Query 中包含ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。◇ Using index：所需要的数据只需要在Index 即可全部获得而不需要再到表中取数据；◇ Using index for group-by：数据访问和Using index 一样，所需数据只需要读取索引即可，而当Query 中使用了GROUP BY 或者DISTINCT 子句的时候，如果分组字段也在索引中，Extra 中的信息就会是Using index for group-by；◇ Using temporary：当MySQL 在某些操作中必须使用临时表的时候，在Extra 信息中就会出现Using temporary 。主要常见于GROUP BY 和ORDER BY 等操作中。◇ Using where：如果我们不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现Using where 信息；◇ Using where with pushed condition：这是一个仅仅在NDBCluster 存储引擎中才会出现的信息，而且还需要通过打开Condition Pushdown 优化功能才可能会被使用。控制参数为engine_condition_pushdown 。
a) 永远用小结果集驱动大的结果集。因为在MySQL 中的Join，只有Nested Loop 一种Join 方式，也就是MySQL 的Join 都是通过嵌套循环来实现的。
b）只取出自己需要的Columns。对于任何Query，返回的数据都是需要通过网络数据包传回给客户端，如果取出的Column 越多，需要传输的数据量自然会越大。如果是需要排序的Query 来说，影响就更大了。在MySQL 中存在两种排序算法，一种是在MySQL4.1 之前的老算法，实现方式是先将需要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在我们所设定的排序区（通过参数sort_buffer_size 设定）中进行排序，完成排序之后再次通过行指针信息取出所需要的Columns，也就是说这种算法需要访问两次数据。第二种排序算法是从MySQL4.1 版本开始使用的改进算法，一次性将所需要的Columns 全部取出，在排序区中进行排序后直接将数据返回给请求客户端。改行算法只需要访问一次数据，减少了大量的随机IO，极大的提高了带有排序的Query 语句的效率。但是，这种改进后的排序算法需要一次性取出并缓存的数据比第一种算法要多很多，如果我们将并不需要的Columns 也取出来，就会极大的浪费排序过程所需要的内存。在MySQL4.1 之后的版本中，我们可以通过设置max_length_for_sort_data 参数大小来控制MySQL 选择第一种排序算法还是第二种排序算法。当所取出的Columns 的单条记录总大小max_length_for_sort_data 设置的大小的时候，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种优化后的算法。为了尽可能提高排序性能，我们自然是更希望使用第二种排序算法，所以在Query 中仅仅取出我们所需要的Columns 是非常有必要的。
c)仅仅使用最有效的过滤条件。因为不同的索引键长度，如果多个索引键都能与表一一对应，那么应该只使用键长度较短的索引作为过滤条件。
d)尽可能避免复杂的Join 和子查询。Query 语句所涉及到的越复杂的Join 语句，所需要锁定的资源也就越多，所表越多，所需要锁定的资源就越多。也就是说，阻塞的其他线程也就越多。如果我们将比较复杂的Query 语句分拆成多个较为简单的Query 语句分步执行，每次锁定的资源也就会少很多，所阻塞的其他线程也要少一些。（牺牲响应时间提高整体处理能力）
4.合理利用索引
如何判定是否需要创建索引?
◆ 较频繁的作为查询条件的字段应该创建索引；
◆ 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；
◆ 更新非常频繁的字段不适合创建索引；
◆ 不会出现在WHERE 子句中的字段不该创建索引；
使用组合索引
只要不是其中如果某个过滤字段在大多数场景下都能过滤出90%以上的数据，而且其他的过滤字段会存在频繁的更新，都建议创建组合索引。因为当我们的并发量较高的时候，即使我们为每个Query 节省很少的IO 消耗，但因为执行量非常大，所节省的资源总量仍然是非常可观的。
强制使用索引：
在有些情况下，可能是由于我们的系统统计信息的不够准确完整，也可能是MySQL Query Optimizer 自身功能的缺陷，会造成他并没有选择一个真正最优的索引而选择了其他查询效率较低的索引。这时可以使用 FORCE INDEX(index_name)强制使用索引。
5. MySQL 中索引的限制
1. MyISAM 存储引擎索引键长度总和不能超过1000 字节；
2. BLOB 和TEXT 类型的列只能创建前缀索引；
3. MySQL 目前不支持函数索引；
4. 使用不等于（!= 或者<>）的时候MySQL 无法使用索引；
5. 过滤字段使用了函数运算后（如abs(column)），MySQL 无法使用索引；
6. Join 语句中Join 条件字段类型不一致的时候MySQL 无法使用索引；
7. 使用LIKE 操作的时候如果条件以通配符开始（ '%abc...'）MySQL 无法使用索引；
8. 使用非等值查询的时候MySQL 无法使用Hash 索引；
13.Join 的实现原理及优化思路
在MySQL 中只有Nested Loop Join算法，实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。
当join类型为all,index,rang,index_merge时，会使用Join Buffer(缓存)结果集，可以通过join_buffer_size 参数设置Join buffer的大小
Join 语句的优化
1. 尽可能减少Join 语句中的Nested Loop 的循环总次数。永远用小结果集驱动大的结果集
2. 优先优化Nested Loop 的内层循环。因为内层循环是循环中执行次数最多的
3.当无法保证被驱动表的Join 条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer 的设置
4.保证Join 语句中被驱动表上Join 条件字段已经被索引
14.ORDER BY的实现与优化
ORDER BY 的实现 。在MySQL 中，ORDER BY 的实现有如下两种类型：
a)一种是通过有序索引而直接取得有序的数据
利用索引实现数据排序的方法是MySQL 中实现结果集排序的最佳做法，可以完全避免因为排序计算所带来的资源消耗。
b)通过MySQL 的排序算法将存储引擎中返回的数据进行排序
排序有两种实现：1、取出满足过滤条件的用于排序条件的字段以及可以直接定位到行数据的行指针信息，在SortBuffer 中进行实际的排序操作，然后利用排好序之后的数据根据行指针信息返回表中取得客户端请求的其他字段的数据 2、根据过滤条件一次取出排序字段以及客户端请求的所有其他字段的数据，并将不需要排序的字段存放在一块内存区域中，然后在Sort Buffer 中将排序字段和行指针信息进行排序，最后再利用排序后的行指针与存放在内存区域中和其他字段一起的行指针信息进行匹配合并结果集
ORDER BY 的优化：
a)尽可能根据索引排序
b)当我们无法避免排序操作的时候，可以配置一下参数进行优化：
1.加大max_length_for_sort_data 参数的设置
在MySQL 中，决定使用第一种老式的排序算法还是新的改进算法的依据是通过参数max_length_for_sort_data 来决定的。当我们所有返回字段的最大长度小于这个参数值的时候，MySQL 就会选择改进后的排序算法，反之，则选择老式的算法（需多访问一次数据库）
2..去掉不必要的返回字段
3. 增大sort_buffer_size 参数设置
让MySQL可以尽量减少在排序过程中对需要排序的数据进行分段，因为这样会造成MySQL 不得不使用临时表来进行交换排序。
15.GROUP BY 的实现与优化
GROUP BY 实际上也同样需要进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。
GROUP BY 的实现有三种：
a)使用松散（Loose）（效率高）索引扫描实现GROUP BY。实际上就是当MySQL 完全利用索引扫描来实现GROUP BY 的时候，并不需要扫描所有满足条件的索引键即可完成操作得出结果。在执行计划的Extra 信息中有信息显示“Using index for group-by”，实际上这就是告诉我们，MySQL Query Optimizer 通过使用松散索引扫描来实现了我们所需要的GROUP BY 操作。 要利用到松散索引扫描实现GROUP BY，需要至少满足以下几个条件：1、GROUP BY 条件字段必须在同一个索引中最前面的连续位置；2、在使用GROUP BY 的同时，只能使用MAX 和MIN 这两个聚合函数；3、如果引用到了该索引中GROUP BY 条件之外的字段条件的时候，必须以常量形式存在。
b)使用紧凑（Tight）索引扫描实现GROUP BY。紧凑索引扫描实现GROUP BY 和松散索引扫描的区别主要在于他需要在扫描索引的时候，读取所有满足条件的索引键，然后再根据读取的数据来完成GROUP BY 操作得到相应结果。执行计划的Extra 信息中显示“Using where; Using index”
c)使用临时表实现GROUP BY。当MySQL QueryOptimizer 无法找到合适的索引可以利用的时候，就不得不先读取需要的数据，然后通过临时表来完成GROUP BY 操作。执行计划的Extra 信息中显示“Using where; Using index; Using temporary; Using filesort”
针对GROUP BY有两种优化思路：
1. 尽可能让MySQL 可以利用索引来完成GROUP BY 操作
2. 当无法使用索引完成GROUP BY 的时候，由于要使用到临时表且需要filesort，所以我们必须要有足够的sort_buffer_size。尽量不要进行大结果集的GROUP BY 操作，因为如果超出系统设置的临时表大小的时候会出现将临时表数据copy 到磁盘上面再进行操作，这时候的排序分组操作性能将是成数量级的下降；
16.DISTINCT 的实现与优化
DISTINCT 实际上和GROUP BY 的操作非常相似，只不过是在GROUP BY 之后的每组中只取出一条记录而已。但是，和GROUP BY 有一点差别的是，DISTINCT 并不需要进行排序。也就是说，在仅仅只是DISTINCT 操作的Query 如果无法仅仅利用索引完成操作的时候，MySQL 会利用临时表来做一次数据的“缓存”，但是不会对临时表中的数据进行filesort 操作。
16.高效的模型设计
1、适度冗余- 让Query 尽量减少Join
2、大字段垂直分拆
3、大表水平分拆
4、统计表- 准实时优化
17.合适的数据类型
1. 通过选用更“小”的数据类型减少存储空间，使查询相同数据需要的IO 资源降低；
2. 通过合适的数据类型加速数据的比较；
数据类型的存储长度和取值范围
对于数字类型，这里分别列出了整数类型和小数类型，也就是浮点数类型。实际上，还有一类通过二进制格式以字符串来存放的数字类型如DECIMAL(DEC)[(M[,D])]，NUMERIC[(M[,D])]，由于其存放长度主要通过其定义时候的的M 所决定，M 定义为多大，则实际存放就有多长。M 代表整个位数长度，而D 则表示小数点后的位数，默认M 为10，D 为0。一般来说，主要用在固定精度的场合，由于其存放长度较大，而且考虑到这种数据完全可以变化形式以整数存放，所以并不是特别推荐。
时间存储格式总类并不是太多，我们常用的主要就是DATETIME，DATE 和TIMESTAMP，从存储空间来看TIMESTAMP 最少，四个字节，而其他两种数据类型都是八个字节，多了一倍。而TIMESTAMP 的缺点在于他只能存储从1970 年之后的时间，而另外两种时间类型可以存放最早从1001 年开始的时间。但是只要我们不需要使用1970 年之前的时间，最好尽量使用TIMESTAMP 来减少存储空间的占用。
字符存储类型
CHAR[(M)]类型属于静态长度类型，存放长度完全以字符数来计算，所以最终的存储长度是基于字符集的。VARCHAR[(M)]属于动态存储长度类型，仅存占用实际存储数据的长度。TINYTEXT，TEXT，MEDIUMTEXT 和LONGTEXT 这四种类型同属于一种存储方式，都是动态存储长度类型，不同的仅仅是最大长度的限制。
18.MySQL Server 性能优化
源码包的编译参数推荐
19.MySQL 日志设置优化
在默认情况下，系统仅仅打开错误日志，关闭了其他所有日志，但是在一般稍微重要一点的实际应用场景中，都至少需要打开二进制日志，因为这是MySQL很多存储引擎进行增量备份的基础，也是MySQL 实现复制的基本条件。一般情况下，在生产系统中最好关闭查询日志，减少io负担。
Binlog 相关参数及优化策略：
查看相关参数命令 show variables like '%binlog%';
binlog_cache_size：在事务过程中容纳二进制日志SQL 语句的缓存大小，注意，是每个Client 都可以分配设置大小的binlog cache 空间。可以通过MySQL 的以下两个状态变量来判断当前的binlog_cache_size 的状况：Binlog_cache_use 和Binlog_cache_disk_use。
max_binlog_cache_size：和"binlog_cache_size"相对应，但是所代表的是binlog 能够使用的最大cache 内存大小。
max_binlog_size：Binlog 日志最大值，一般来说设置为512M 或者1G，但不能超过1G。
sync_binlog：这个参数是对于MySQL 系统来说是至关重要的，他不仅影响到Binlog 对MySQL 所带来的性能损耗，而且还影响到MySQL 中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下： 1)sync_binlog=0，当事务提交之后，MySQL 不做fsync 之类的磁盘同步指令刷新binlog_cache 中的信息到磁盘，而让Filesystem 自行决定什么时候来做同步，或者cache 满了之后才同步到磁盘。2）sync_binlog=n，当每进行n 次事务提交之后，MySQL 将进行一次fsync 之类的磁盘同步指令来将binlog_cache 中的数据强制写入磁盘。
Slow Query Log 相关参数及使用建议：
查看慢查询相关设置：show variables like 'slow_query%';
show variables like 'long_query%';
20.连接池相关优化
网络连接的性能配置项：
max_conecctions：整个MySQL 允许的最大连接数。 这个参数主要影响的是整个MySQL 应用的并发处理能力，当系统中实际需要的连接量大于max_conecctions 的情况下，由于MySQL 的设置限制，那么应用中必然会产生连接请求的等待，从而限制了相应的并发量。所以一般来说，只要MySQL 主机性能允许，都是将该参数设置的尽可能大一点。一般来说500 到800 左右是一个比较合适的参考值
max_user_connections：每个用户允许的最大连接数，针对于单个用户的连接限制。在一般情况下我们可能都较少使用这个限制
net_buffer_length：网络包传输中，传输消息之前的net buffer 初始化大小；这个参数主要可能影响的是网络传输的效率，由于该参数所设置的只是消息缓冲区的初始化大小，所以造成的影响主要是当我们的每次消息都很大的时候MySQL 总是需要多次申请扩展该缓冲区大小。系统默认大小为16KB，一般来说可以满足大多数场景，当然如果我们的查询都是非常小，每次网络传输量都很少，而且系统内存又比较紧缺的情况下，也可以适当将该值降低到8KB。
max_allowed_packet：在网络传输中，一次传消息输量的最大值。这个参数net_buffer_length 相对应，只不过是net buffer 的最大值。当我们的消息传输量大于net_buffer_length 的设置时，MySQL 会自动增大net buffer 的大小，直到缓冲区大小达到max_allowed_packet 所设置的值。系统默认值为1MB，最大值是1GB，必须设定为1024 的倍数，单位为字节。
back_log：在MySQL 的连接请求等待队列中允许存放的最大连接请求数。
相关的系统参数及状态变量说明如下：
thread_cache_size：Thread Cache 池中应该存放的连接线程数。在短连接的应用系统中，thread_cache_size 的值应该设置的相对大一些
thread_stack：每个连接线程被创建的时候，MySQL 给他分配的内存大小。当MySQL 创建一个新的连接线程的时候，是需要给他分配一定大小的内存堆栈空间，以便存放客户端的请求Query 以及自身的各种状态和处理信息。
show status like 'connections'：系统被连接的次数
show status like '%thread%'：当前系统中连接线程的状态
21.Sort Buffer，Join Buffer 和Read Buffer
查看相关配置信息：show variables like '%buffer%';可以查看join_buffer_size和sort_buffer_size。
join_buffer_size ：当我们的Join 是ALL ， index ，rang 或者index_merge 的时候使用的Buffer；实际上这种Join 被称为Full Join。实际上参与Join 的每一个表都需要一个Join Buffer，所以在Join 出现的时候，至少是两个。Join Buffer 的设置在MySQL 5.1.23 版本之前最大为4GB，但是从5.1.23 版本开始，在除了Windows 之外的64 位的平台上可以超出4BG 的限制。系统默认128KB。
sort_buffer_size：系统中对数据进行排序的时候使用的Buffer；Sort Buffer 同样是针对单个Thread的，所以当多个Thread 同时进行排序的时候，系统中就会出现多个Sort Buffer。一般我们可以通过增大Sort Buffer 的大小来提高ORDER BY 或者是GROUP BY的处理性能。系统默认大小为2MB，最大限制和Join Buffer 一样，在MySQL 5.1.23 版本之前最大为4GB，从5.1.23 版本开始，在除了Windows 之外的64 位的平台上可以超出4GB 的限制。
如果应用系统中很少有Join 语句出现，则可以不用太在乎join_buffer_size 参数的大小设置，但是如果Join 语句不是很少的话，个人建议可以适当增大join_buffer_size 的设置到1MB 左右，如果内存充足甚至可以设置为2MB。对于sort_buffer_size 参数来说，一般设置为2MB 到4MB 之间可以满足大多数应用的需求。
22.MyI SAM存储引擎优化
MyISAM 存储引擎的索引和数据是分开存放于“.MYI”文件中，每个“.MYI”文件由文件头和实际的索引数据。“.MYI”的文件头中主要存放四部分信息，分别称为：state（主要是整个索引文件的基本信息），base（各个索引的相关信息，主要是索引的限制信息）， keydef（每个索引的定义信息）和recinfo（每个索引记录的相关信息）。在文件头后面紧接着的就是实际的索引数据信息了。索引数据以Block（Page）为最小单位，每个block 中只会存在同一个索引的数据，这主要是基于提高索引的连续读性能的目的。在MySQL 中，索引文件中索引数据的block 被称为Index Block，每个Index Block 的大小并不一定相等。
在“.MYI”中，Index Block 的组织形式实际上只是一种逻辑上的，并不是物理意义上的。在物理上，实际上是以File Block 的形式来存放在磁盘上面的。在Key Cache 中缓存的索引信息是以“Cache Block”的形式组织存放的，“Cache Block”是相同大小的，和“.MYI”文件物理存储的Block（ File Block ） 一样。在一条Query 通过索引检索表数据的时候， 首先会检查索引缓存（key_buffer_cache）中是否已经有需要的索引信息，如果没有，则会读取“.MYI”文件，将相应的索引数据读入Key Cache 中的内存空间中，同样也是以Block 形式存放，被称为Cache Block。不过，数据的读入并不是以Index Block 的形式来读入，而是以File Block 的形式来读入的。以File Block 形式读入到Key Cache 之后的Cache Block 实际上是于File Block 完全一样的。如下图所示：
索引缓存优化
MyISAM 索引缓存相关的几个系统参数和状态参数：
◆ key_buffer_size，索引缓存大小；
这个参数用来设置整个MySQL 中的常规Key Cache 大小。一般来说，如果我们的MySQL 是运行在32 位平台纸上，此值建议不要超过2GB 大小。如果是运行在64 位平台纸上则不用考虑此限制，但也最好不要超过4GB。
◆ key_buffer_block_size，索引缓存中的Cache Block Size；
在Key Cache 中的所有数据都是以Cache Block 的形式存在，而key_buffer_block_size 就是设置每个Cache Block 的大小，实际上也同时限定了我们将“.MYI”文件中的Index Block 被读入时候的File Block 的大小。
◆ key_cache_division_limit，LRU 链表中的Hot Area 和Warm Area 分界值；
实际上，在MySQL 的Key Cache 中所使用的LRU 算法并不像传统的算法一样仅仅只是通过访问频率以及最后访问时间来通过一个唯一的链表实现，而是将其分成了两部分。一部分用来存放使用比较频繁的Hot Cacke Lock（Hot Chain），被成为Hot Area，另外一部分则用来存放使用不是太频繁的Warm Cache Block（Warm Chain），被成为Warm Area。这样做的目的主要是为了保护使用比较频繁的Cache Block 更不容易被换出。而key_cache_division_limit 参数则是告诉MySQL该如何划分整个Cache Chain划分为Hot Chain和Warm Chain 两部分，参数值为WarmChain 占整个Chain 的百分比值。设置范围1～100，系统默认为100，也就是只有Warm Chain。
◆ key_cache_age_threshold，控制Cache Block 从Hot Area 降到Warm Area 的限制；
key_cache_age_threshold参数控制Hot Area 中的Cache Block 何时该被降级到Warm Area 中。系统默认值为300，最小可以设置为100。值越小，被降级的可能性越大。
key_buffer_size计算指标：Key_Size = key_number * (key_length+4)/0.67
Key Cache 的命中率：
Cache 相关的性能状态参数变量。
◆ Key_blocks_not_flushed，已经更改但还未刷新到磁盘的Dirty Cache Block；
◆ Key_blocks_unused，目前未被使用的Cache Block 数目；
◆ Key_blocks_used，已经使用了的Cache Block 数目；
◆ Key_read_requests，Cache Block 被请求读取的总次数；
◆ Key_reads，在Cache Block 中找不到需要读取的Key 信息后到“.MYI”文件中读取的次数；
◆ Key_write_requests，Cache Block 被请求修改的总次数；
◆ Key_writes，在Cache Block 中找不到需要修改的Key 信息后到“.MYI”文件中读入再修改的次
数；
Key_buffer_UsageRatio = (1 - Key_blocks_used/(Key_blocks_used + Key_blocks_unused)) *
100% //缓存使用率，如果该值过低说明key_bufffer_size设置过大。
Key_Buffer_Read_HitRatio = (1 - Key_reads/Key_read_requests) * 100% //缓存命中率，如果该值较低可能是key_buffer_size设置较小；或key_cache_age_thresholdkey_cache_division_limit的设置不当，造成Key Cache cache失效太快。
多Key Cache 的使用
MySQL 官方建议在比较繁忙的系统上一般可以设置三个Key Cache：
一个Hot Cache 使用20%的大小用来存放使用非常频繁且更新很少的表的索引；
一个Cold Cache 使用20%的大小用来存放更新很频繁的表的索引；
一个Warm Cache 使用剩下的60%空间，作为整个系统默认的Key Cache；
Key Cache 预加载：LOAD INDEX INTO CACHE tb_name_list ...； 对于这种启动后立即加载的操作，可以利用MySQL 的init_file 参数来设置相关的命令，如下：
表读取缓存优化
在MySQL 中有两种读取数据文件的缓冲区，一种是Sequential Scan 方式（如全表扫描）扫描表数据的时候使用，另一种则是在Random Scan（如通过索引扫描）的时候使用。虽然这两种文件读取缓冲区并不是MyISAM 存储引擎所特有的，但是由于MyISAM 存储引擎并不会Cache 数据（.MYD）文件，每次对数据文件的访问都需要通过调用文件系统的相关指令从磁盘上面读取物理文件。所以，每次读取数据文件需要使用的内存缓冲区的设置就对数据文件访问的性能非常重要了。
◆ read_buffer_size，以Sequential Scan 方式扫描表数据时候使用的Buffer；
每个Thread 进行Sequential Scan 的时候都会产生该Buffer，所以在设置的时候尽量不要太高，避免因为并发太大造成内存不够。一般来说，可以尝试适当调大此参数看是否能够改善全表扫描的性能。
◆ read_rnd_buffer_size，进行Random Scan 的时候使用的Buffer；
一般来说，read_rnd_buffer_size 值的适当调大，对提高ORDER BY 操作的性能有一定的效果。
并发优化
由于MyISAM 存储引擎的表级锁定机制，以及读写互斥的问题，其并发写的性能较差。如果觉得光靠Key Cache 来缓存索引还是不够快的话，我们还可以通过Query Cache 功能来直接缓存Query 的结果集。
1. 打开concurrent_insert 的功能，提高INSERT 操作和SELECT 之间的并发处理，大部分情况下concurrent_insert 的值都被设置为1，当表中没有删除记录留下的空余空间的时候都可以在尾部并行插入。如果我们的系统主要以写为主，尤其是有大量的INSERT 的时候。为了尽可能提高INSERT 的效率，我们可以将concurrent_insert 设置为2，也就是告诉MyISAM，不管在表中是否有删除行留下的空余空间，都在尾部进行并发插入，使INSERT 和SELECT 能够互不干扰。
2. 控制写入操作的大小，尽量让每次写入操作都能够很快的完成
3. 通过牺牲读取效率来提高写入效率。为了尽可能让写入更快，可以适当调整读和写的优先级别，让写入操作的优先级高于读操作的优先级。
除了上面我们分析的这几个方面之外，MyISAM还存在其他一些可以优化的地方和一些常用的优化技巧。1. 通过OPTIMIZE 命令来整理MyISAM 表的文件 2. 设置myisam_max_[extra]_sort_file_size 足够大，对REPAIR TABLE 的效率可能会有较大改善。3. 在执行CREATE INDEX 或者REPAIR TABLE 等需要大的排序操作的之前可以通过调整session 级别的myisam_sort_buffer_size 参数值来提高排序操作的效率。4. 通过打开delay_key_write 功能，减少IO 同步的操作，提高写入性能。5. 通过调整bulk_insert_buffer_size 来提高INSERT...SELECT...这样的bulk insert 操作的整体性能，LOAD DATA INFILE...的性能也可以得到改善。
23.I nnodb 存储引擎优化
Innodb 存储引擎和MyISAM 存储引擎最大区别主要有四点，第一点是缓存机制（索引+数据），第二点是事务支持，第三点是锁定实现（行级锁定），最后一点就是数据存储方式的差异（共享表空间）。
无论是对于哪一种数据库来说，缓存技术都是提高数据库性能的关键技术，物理磁盘的访问速度永远都会与内存的访问速度永远都不是一个数量级的。通过缓存技术无论是在读还是写方面都可以大大提高数据库整体性能。
Innodb_buffer_pool_size 的合理设置
系统内存分配
可以同过show status like 'Innodb_buffer_pool_%' 指令查看Buffer pool使用情况
buffer_pool相关参数信息
上面的值可以看出总共有8192pages,还有6765是free状态，只有1420个pages有数据，read请求47848次，其中1066次请求buffer pool中没有，也就是说有1066次是通过读取物理磁盘获取数据的，很容易的出read命中率大概为：(47848-1066)/47848*100%=97.7%
当然，通过上面的数据，我们还可以分析出write 命中率，可以得到发生了多少次read_ahead_rnd，多少次read_ahead_seq，发生过多少次latch，多少次因为Buffer 空间大小不足而产生wait_free 等等。
单从这里的数据来看，我们设置的Buffer Pool 过大，仅仅使用1420/ 8192* 100% = 17.33%。
innodb_log_buffer_size 参数的使用
顾名思义，这个参数就是用来设置Innodb 的Log Buffer 大小的，系统默认值为1MB。Log Buffer的主要作用就是缓冲Log 数据，提高写Log 的IO 性能。一般来说，如果你的系统不是写负载非常高且以大事务居多的话，8MB 以内的大小就完全足够了。
查看log buffer使用情况：show status like 'innodb_log%';
log信息
如果完全从Log Buffer 本身来说，自然是大一些会减少更多的磁盘IO。但是由于Log 本身是为了保护数据安全而产生的，而Log 从Buffer 到磁盘的刷新频率和控制数据安全一致的事务直接相关，并且也有相关参数来控制（innodb_flush_log_at_trx_commit）所以得进行权衡。
24.事务优化
Innodb 的事务隔离级别：
1.READ UNCOMMITTED：常被成为Dirty Reads（脏读）,最低隔离级别，在普通的非锁定模式下SELECT 的执行使我们看到的数据可能并不是查询发起时间点的数据，因而在这个隔离度下是非Consistent Reads（一致性读）；
2. READ COMMITTED：这一隔离级别下，不会出现Dirty Read，但是可能出现Non-Repeatable Reads(不可重复读)和Phantom Reads（幻读）。属于语句级别的隔离，如通过SELECT ... FOR UPDATE 和SELECT ... LOCK IN SHARE MODE 来执行的请求仅仅锁定索引记录，而不锁定之前的间隙，因而允许在锁定的记录后自由地插入新记录。
3. REPEATABLE READ：InnoDB 默认的事务隔离级别。在这一级中，同一事务中所有的Consistent Reads 均读取第一次读取时已确定的快照。在REPEATABLE READ 隔离级别下，不会出现Dirty Reads，也不会出现Non-Repeatable Reads，但是仍然存在Phantom Reads 的可能性。SELECT ... FOR UPDATE, SELECT... LOCK IN SHARE MODE, UPDATE, 和DELETE ，这些以唯一条件搜索唯一索引的，只锁定所找到的索引记录，而不锁定该索引之前的间隙。
4.SERIALIZABLE：。设置为SERIALIZABLE 隔离级别之后，在事务中的任何时候所看到的数据都是事务启动时刻的状态。不论在这期间有没有其他事务已经修改了某些数据并提交。所以，SERIALIZABLE 事务隔离级别下，Phantom Reads 也不会出现。（幻读：并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读）
解决不可重复读的方法是 锁行，解决幻读的方式是 锁表
25.悲观锁和乐观锁
数据库中的乐观锁和悲观锁以及实现方式
乐观锁：获取数据时不会考虑并发情况造成的数据冲突，然后再数据更新提交时正式对数据的冲突与否进行检测，如果发现冲突了，则返回错误信息，让用户重新操作。
悲观锁：总是做最坏的打算，每次去读取数据都会认为会被其它线程修改，所以会加锁,当其它线程想要访问数据时，都需要阻塞挂起。
乐观锁实现方式
version方式：
一般在数据表中加一个version版本字段,表示数据被修改的版本次数，当数据被修改时，version会被加一。当线程A读取数据时也要同时读取version值，在提交更新的时候，如果刚才读取的version值和当前数据库里的version值一致，那么才能更新，否则重新更新操作，直到更新成成功。乐观锁在获得锁的同时已经完成了更新操作
SQL代码实现：
悲观锁实现方式
for update方式:
一般使用select … for update 对所选择的数据加锁，例如 select * from t_goods where id =1 for update, 这条sql语句就锁定了t_goods表中符合id=1的这条记录，本次事务提交之前，外界无法修改这些记录。悲观锁遵循一锁、二判、三更新、四释放的原则
网友评论