摘要：

　　一、 MySQL架构

　　　　1.1 架构简介

　　　　1.2 MariaDB编译版安装

　　　　1.3 存储引擎

 　　　  1.4 并发控制及事务

　　二、MySQL基础

　　　　2.1 SELECT

　　　　2.2 查询缓存

　　　　2.3 索引

　　　　2.4 explain

参考资料：

　　http://www.jianshu.com/p/91e3af27743f

一、MySQL架构

1.1 MySQL架构简介

　　MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区进行维护，采用GPL授权许可。Oracle在收购MySQL之后，有将MySQL闭源的潜在可能。Google Facebook等在大约2年前基本完全弃用了MySQL转向了MariaDB，而在较新的Linux发行版，例如CentOS7中也把MySQL改为了MariaDB。

　　MariaDB由MySQL的创始人麦格尔主导研发，直到5.5版本，MariaDB均按照MySQL的版本，而从2012年开始，不再使用MySQL的版本号，10.0.x均以5.5版为基础，进行新特性的开发。

　　MariaDB的API完全兼容MySQL，因此之后的MySQL均以MariaDB为准使用。

 MySQL的架构如下图所示：

MySQL 组件：

• Connetcion Pool：MySQL的线程池，负责客户端连接的维护，包含连接密码认证，线程重用，连接数控制，内存检测以及连接线程的缓存功能。
• SQL Interface：SQL接口，提供了MySQL的大多数逻辑组件，类似Linux的Shell，包含了DML,DDL,视图，过程等等MySQL中常用的组件。
• Parser：MySQL的Query Translation，将SQL接口接收的语句转换为MySQL内部接口的语句，并提供了权限验证功能。
• Optimizer:　　SQL语句在查询之前会使用查询优化器对其进行优化，并记录到统计数据中。
• Caches & Buffers:  MySQL自我管理的缓存和Buffer
• Pluggable Storeage Engines: MySQL支持插件式的存储引擎，存储引擎是MySQL和文件系统打交道的具体系统。
• File system: 外部的文件系统
• Files & logs: MySQL的物理视图，即MySQL在文件系统中的具体文件，例如事务中的Redo日志，Undo日志，Data数据文件，Index索引文件，错误日志，慢查询日志等等。

MySQL的设计是一个单进程多线程的架构设计，通过线程池来进行管理的每一个用户连接的设计，因此从一开始MySQL对多核心系统的利用就欠佳，直到MySQL5.5开始大规模企业化之后，一直致力于MySQL在多核系统中的表现。但是MySQL在每一个连接中依旧只能利用到单核心，即你的SQL语句哪怕再复杂，也只能用一个CPU去处理。

由于MySQL只有1个进程，而单个进程在Linux系统上能使用的内存是有限制的，因此MySQL没有其他选择，必须使用64位的操作系统，否则内存的限制将是一个无法解决的瓶颈。

1.2 安装 

MaraiDB的安装方式多种多样，Centos7自带yum源中的就是MariaDB

yum install -y jemalloc jemalloc-devel #依赖于其中的一个共享库文件

(1) rpm:
        OS: 
        官方
(2) 源码包
(3) 通用二进制格式的程序包

下面以CentOS7为例，使用通用二进制格式的程序包安装：准备admin用户mysql,mysql组

    (1) 准备数据目录；
        以/mydata/data目录为例；（最好是创建一个新的分区专门存放数据）
        chown -R mysql.mysql /mydata/data/
    (2) 安装配置mariadb                        
        # useradd  -r  mysql
        # tar xf  mariadb-VERSION.tar.xz  -C  /usr/local
        # cd /usr/local
        # ln  -sv  mariadb-VERSION  mysql
        # cd  /usr/local/mysql
        # chown  -R  root:mysql  ./*
        # scripts/mysql_install_db  --user=mysql  -datadir=/mydata/data 初始化数据，不能使用绝对路径，只能在这个位置
        # cp  support-files/mysql.server   /etc/init.d/mysqld
        # chkconfig   --add  mysqld
    (3) 提供配置文件
        ini格式的配置文件；各程序均可通过此配置文件获取配置信息；
            [program_name]
                            
        OS Vendor提供mariadb rpm包安装的服务的配置文件查找次序：
            /etc/mysql/my.cnf  --> /etc/my.cnf  --> --default-extra-file=/PATH/TO/CONF_FILE  --> ~/.my.cnf
            
        通用二进制格式安装的服务程序其配置文件查找次序：
            /etc/my.cnf  --> /etc/mysql/my.cnf  --> --default-extra-file=/PATH/TO/CONF_FILE  --> ~/.my.cnf
            
        获取其读取次序的方法：
            mysqld  --verbose  --help | less
            
        # cp  support-files/my-large.cnf  /etc/my.cnf
        
        添加三个选项：
            datadir = /mydata/data
            innodb_file_per_table = ON
            skip-name-resolve = ON
            
    (4) 启动服务
        # service  mysqld  start
安装完成后，使用mysql_secure_installation进行固化操作：

　　root密码

　　禁止root远程登录

　　移除匿名用户

1.3 MySQL存储引擎

　　存储引擎说白了就是如何在文件系统上存储数据、建立索引、更新、查询等等，是MySQL的真正实现方案，MySQL是采用一种插件式的存储引擎管理方案，可以通过show engines查看MySQL支持的所有存储引擎，可以使用show table status like [table_name]的方式来查看表的存储引擎。

(1) InnoDB

　　在mysql5.5之后，InnoDB就被指定为是默认的存储引擎，而MariaDB使用percona提供的XtraDB引擎作为InnoDB的增强版，非常适合大量的短期事务。InnoDB将数据和索引都存储在一所谓的"表空间"(table space)中。

　　根据配置有2种情况：

　　　　第一种所有的InnoDB表中的数据和索引放置于同一表空间中，而表空间文件会放放置datadir指定的目录中，数据文件大致上为ibddata1,ibddata2.....

　　　　第二种更为常用，每个表使用独自的表空间，需要在配置文件或者启动参数中设置innodb_file_per_table=ON来启用该功能，此时每个数据文件+索引在tb_name.idb中，表格式定义元数据文件 tb_name.frm

　　特性：

　　　　存储能力64TB

　　　　通过MVVC机制来支持高并发

　　　　聚集索引机制，聚集索引是指每一行的数据都和当前行的索引信息放在一起，一般聚集索引都是主键，其他的索引称为辅助索引，任何索引想要找到数据信息都要先找到聚集索引，再由聚集索引找到数据信息。

　　　　支持预读操作，自适应hash，查询缓存等等

　　　　支持热备(xtrabackup)

　　　　支持事务

　　　　锁粒度支持行级别锁

(2) MyISAM

　　MySQL自己研发的一个存储引擎，在MariaDB中使用aira作为增强版实现

　　表格式文件，数据文件，索引文件单独存放，分别是tb_name.frm,tb_name.myd,tb_name.myi

　　特性：

　　　　最大存储能力为256TB

　　　　支持全文索引Full Text

　　　　只支持表级锁

　　　　不支持事务、外键、数据缓存、不支持MVVC

　　　　只能手工修复

　　　　索引是非聚集索引

　　　　支持延迟更新索引

　　　　支持表压缩机制

 MySQL中其他常见的存储引擎：

　　csv：将普通的csv作为MySQL使用

　　MRG_MYISAM:将多个MyISAM表合成为一个虚拟表

　　BLACKHOLE：黑洞

　　MMEORY:　内存，支持hash索引，表级别锁

　　PERFORMANCE_SCHEMA: 伪存储引擎

　　FEDERTED: 远程MySQL代理

MariaDB中还支持：

　　OQGraph:　

　　SpinxSE: 可以和spinx集成

　　TokuDB：海量数据存储

　　Cassandra：可以和Cassandra集成

　　...

总结，如何选择存储引擎：

　　需要事务？InnoDB

　　需要外键？InnoDB

　　需要热备？InnoDB

　　需要Fulltext？MyISAM

1.4 并发控制及事务

MySQL中的并发控制通过锁来实现，MySQL中的lock从大体上分为read lock和write lock，和一般的读锁，写锁一样，写锁和什么都互斥，读锁和读锁不互斥，写锁优先级高于读锁等等。

而从用户的角度看又可以分为显示锁，用户可以显示请求调用的锁称为显示锁，是表级锁，而隐式锁则是由存储引擎自动施加的锁，用户不可见，由存储引擎决定。

从锁的粒度来看分为表级锁和行级锁，锁的粒度精细，实现越复杂，因此锁的使用策略就是在粒度和控制上实现一种平衡。

用户显示调用锁的方法

(1) LOCK TABLE

Syntax:
LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE

UNLOCK TABLES

(2) FLUSH

FLUSH TABLES tb_name[,...] [WITH READ LOCK] [FOR UPDATE]

(3)

SELECT clase [FOR UPDATE] [WITH READ LOACK]

事务： A transaction is a group of SQL queries that one threated atomically, as a single unit of work.

Atomicity:

　　A transaction must function as a single indivisible unit of work so that the entire transaction is either applied or rolled back

Consistency:

　　The database should always move from one consistent state to the next

Isolation:

　　The results of a transaction are usually invisble to the other transactions until the transaction is complete.

Durability:

　　Once commited, a transaciton‘s changes are permanent.

tx_isolation:

可以使用命令show variables like ‘tx_%‘;查看事务隔离级别tx_isolation

事务隔离级别：(Isolation Levels)

READ-UNCOMMITED:

　　Transactions can view the results of uncommited transactions

READ-COMMITED:

　　A transaction will see only those changes made by transactions that were already commited when it began, and its change won‘t be visible to other unitl it has commited

REPEATABLE-READ

　　It gurantees that any rows a transaction read will "look the same" in subsequent reads within the same transaction, but in theory it still allows another tricky problem: phantom reads.

SERIALIZABLE:

　　one by one.

Transaction Logging helps make transactions more efficient.
　　事务日志，可以帮助提高事务的效率。使用事务日志，存储引擎在修改表数据时，只需要修改其内存拷贝，采用追加的方式，之后再同步到磁盘中，这样将写操作由随机IO变成了顺序IO，可以有效的提高性能，这种方式称为预写式日志。

　　InnoDB中查看事务日志

　　　　innodb_log_file_size　　　　　　　　文件大小
　　　　innodb_log_files_in_group　　　　　单组文件个数
　　　　innodb_log_group_home_dir　　　　文件home目录

　　可以在MySQL初始化时在配置文件中指定。注意文件大小要适量。

二、MySQL基础

 2.1 SELECT

SELECT
    [ALL | DISTINCT | DISTINCTROW]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [ FROM table_references [PARTITION (partition_list)]
      [{USE|FORCE|IGNORE} INDEX [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list]) ]
      [WHERE where_condition]
      [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
      [HAVING where_condition]
      [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
      [LIMIT {[offset,] row_count | row_count OFFSET offset}]
      [PROCEDURE procedure_name(argument_list)]
      [INTO OUTFILE ‘file_name‘ [CHARACTER SET charset_name] [export_options]
         | INTO DUMPFILE ‘file_name‘ | INTO var_name [, var_name] ]
      [FOR UPDATE | LOCK IN SHARE MODE] ]

export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY ‘string‘]
        [[OPTIONALLY] ENCLOSED BY ‘char‘]
        [ESCAPED BY ‘char‘]
    ]
    [LINES
        [STARTING BY ‘string‘]
        [TERMINATED BY ‘string‘]
    ]

DISTINCT: 数据去重

SQL_CACHE: 显示指定查询缓存

支持别名

where字句，逻辑操作符等等

支持分组以及分组函数

支持排序

FOR UPDATE 加上写锁

LOCK IN SHARE MODE 加上读锁

支持多表连接以及子查询...

2.2 查询缓存

　　从磁盘中读数据会产生磁盘IO,因此在读多写少的场景下，可以使用缓存。

　　MySQL天然支持查询缓存，在某些情况下还是可以用的，比如说单台服务器，多台服务器一般采用专门的缓存服务器比如Memcached，redis等等服务器实现。

　　这里的关注重点在MySQL本身的查询缓存，MySQL可以对每次查询做hash键，查询的数据为value进行缓存，而这里的查询key需要考略多方面因数：

　　　　查询本身

　　　　查询的数据库

　　　　客户端使用的协议版本。。。
　　注意： 查询语句任何的不同，哪怕是一个空格，一个大小写都会引起查询缓存的不同，因此所有程序员保持一定的SQL书写风格还是有必要的。

　　在前面的MySQL架构中描述了查询缓存实现的组件，而InnoDB，最常见的存储引擎是支持查询缓存的。

　　在实战中，查询缓存也有可能成为并发热点问题，这往往是由于多核竞争引起的，由于计算机的核心数较多，高并发场景下产生的竞争问题，这也是查询缓存的内存大小默认不会太大的原因，默认是16M.

　　同时要注意到，不是所有的数据都会缓存，比如我查询一个select now()，这完全不能缓存，一般包含以下内容的都不会缓存，比如用户自定一函数，存储过程，自定义变量，临时表，mysql库中的系统表，不确定值等等。

　　variables中的相关变量有：

　　query_cache_min_res_unit: 查询缓存中内存块的最小分配单位；
　　　　较小值会减少浪费，但会导致更频繁的内存分配操作；
　　　　较大值会带来浪费，会导致碎片过多；
　　query_cache_limit：能够缓存的最大查询结果；
　　　　对于有着较大结果的查询语句，建议在SELECT中使用SQL_NO_CACHE
　　query_cache_size：查询缓存总共可用的内存空间；单位是字节，必须是1024的整数倍；
　　query_cache_type: ON, OFF, DEMAND

　　query_cache_wlock_invalidate：如果某表被其它的连接锁定，是否仍然可以从查询缓存中返回结果；默认值为OFF，表示可以在表被其它连接淘宝的场景中继续从缓存返回数据；ON则表示不允许；

　　上述参数可以用来对查询缓存进行调优，调优流程图如下：

 　　而缓存的命中率可以作为一个参考，注意只是一个参考，因为可能命中率很低，10条语句只有3条，但是如果这3条返回的数据量值得缓存，计算方式是Qcache_hits/(Qcache_hits+Com_select)

　　以上变量都是variables中的值，而相关变量大概如下：

　　　　　　SHOW GLOBAL STATUS LIKE ‘Qcache%‘;
        +-------------------------+----------+
        | Variable_name           | Value    |
        +-------------------------+----------+
        | Qcache_free_blocks      | 1        |
        | Qcache_free_memory      | 16759688 |
        | Qcache_hits             | 0        |
        | Qcache_inserts          | 0        |
        | Qcache_lowmem_prunes    | 0        |
        | Qcache_not_cached       | 0        |
        | Qcache_queries_in_cache | 0        |
        | Qcache_total_blocks     | 1        |
        +-------------------------+----------+

2.3 索引

说起sql优化，大部分都落实在索引优化上，什么是索引，MySQL不可能在每次查询数据时将磁盘的数据全部加载到内存中，需要利用数据结构加载少量数据集到内存中即可，这部分数据集就是索引。索引的优点大致如下：

　　索引可以降低服务器查询的io次数

　　索引可以帮助服务器避免排序和使用临时表

　　索引可以帮助将随机I/O转换为顺序IO(在硬盘非固态的时候还是有用的)

　索引功能主要由存储引擎来实现，不同的引擎可以使用不同的索引结构。

(1) B+树索引

　B+树是一种常见的数据结构，是B树的变种，相对于B树，(B树是什么？略)，增加了以下特性

　　1. 所有的关键字存储在叶子节点中

　　2. 为所有叶子节点增加了一个链指针

B+树是很适合硬盘读写的数据结构。红黑树等虽然也适合用来实现索引，比如JAVA中的Treemap就是红黑树，适合用来在内存中做索引。

这是由于局部性原理和磁盘预读，什么是局部性原理？时间上，data被用到后，之后的时间很可能再次被用到，空间上，data被用到后，附近的数据很可能被用到，这就是局部性，程序使用的数据在空间上和时间上都是比较集中的。由于磁盘的存取速度很慢，比如在机械硬盘物理上的平均寻址时间，由于linux系统不管什么io模型都必须经历的2个阶段(磁盘->内核空间，内核空间->用户空间)等等...，所以MySQL要尽可能的减少IO的存取次数，而B+树就比较合适，B+树内节点都没有data，只有叶子节点上有data，因此每个节点可以存储更多的数据，范围更大，单次IO的数据量更大，可以直接预读，而且由于新增链指针，可以很方便的进行区间查找，即MySQL中where子句中的between操作。

B+树索引在MySQL中有以下要点：

　　适用于：

　　　　精确值匹配，例如="aa"

　　　　最左前缀，例如like "Jin%"

　　　　范围匹配，例如 < >

　　　　精确匹配之后范围匹配，一般精确匹配都在左侧，而范围适合在右侧，且中间不能跳过其他字段

　　　　覆盖索引，索引中就包含要返回信息，不需要查找数据

　　不适用于:

• • 　　不是按照索引的最左列开始查找;
  • 　　不能跳过索引中的列;
  • 　　如果查询中的某个列是范围查询, 则其右边的所有列都无法使用索引;比如like, between
  • 　　索引列上使用函数, 或者算数运算

(2) hash 索引

 注意：只有Memory存储引擎支持显式hash索引；

适用场景：
只支持等值比较查询，包括=, IN(), <=>;

不适合使用hash索引的场景：
存储的非为值的顺序，因此，不适用于顺序查询；
不支持模糊匹配；

(3) 空间索引（R-Tree）：
MyISAM支持空间索引；

(4) 全文索引(FULLTEXT)：
在文本中查找关键词；

上面只是常见索引的数据结构以及用法，索引中还存在着许多的细节问题。比如索引要独立使用，不要参与运算，使用函数等等，在选择索引时候使用左前缀考虑左前缀长度等等，多列索引的陷阱等等。

1. 利用索引排序？

　　由于索引是有序，因此order中的列存在索引时，可以通过索引来排序，否则有可以使用file sorting，这是很不好的。

2. InndoDB中的聚集索引？

　　聚族(聚集)索引就是叶子节点保存的就是数据本身，索引和数据在一起，一般主键索引才是聚集索引。

　　优点：数据访问更快，索引扫描无须来回标

　　缺点：插入速度严重影响插入顺序，比如InnoDB中按照主键逆序；全表扫描会慢；二级索引会变大，且需要二次查找

2.4 explain字段说明　　

 注意explain返回不是精确值，只是估计值

id: 当前查询语句中，每个SELECT语句的编号；

注意：UNION查询的分析结果会出现一外额外匿名临时表；

select_type：
　　简单查询为SIMPLE
　　复杂查询：
　　　　SUBQUERY: 简单子查询；
　　　　DERIVED: 用于FROM中的子查询；
　　　　UNION：UNION语句的第一个之后的SELECT语句；
　　　　UNION RESULT: 匿名临时表；

table：SELECT语句关联到的表；

type：关联类型，或访问类型，即MySQL决定的如何去查询表中的行的方式；
　　ALL: 全表扫描；
　　index：根据索引的次序进行全表扫描；如果在Extra列出现“Using index”表示了使用覆盖索引，而非全表扫描；
　　range：有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束于另一点；
　　ref: 使用了索引，但是可能返回多行根据索引返回表中匹配某单个值的所有行；
　　eq_ref：仅返回一个行，但与需要额外与某个参考值做比较；
　　const, system: 直接返回单个行；

possible_keys：查询可能会用到的索引；

key: 查询中使用了的索引；

key_len: 在索引使用的字节数；

ref: 在利用key字段所表示的索引完成查询时所有的列或某常量值；

rows：MySQL估计为找所有的目标行而需要读取的行数；

Extra：额外信息

　　Using index：MySQL将会使用覆盖索引，以避免访问表；
　　Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤；
　　Using temporary：MySQL对结果排序时会使用临时表；
　　Using filesort：对结果使用一个外部索引排序；

MySQL Basic