# MySQL详解&优化记录

# 概述

# 新特性

• 表和索引的分区
• 行级复制
• MySQL 基群基于磁盘的数据支持
• MySQL 集群复制
• 增强的全文本搜索函数
• 增强的信息模式(数据字典)
• 可插入的 API
• 服务器日志表
• XML（标准通用标记语言的子集）/ XPath支持
• 实例管理器
• 表空间备份
• mysql_upgrade 升级程序
• 内部任务/事件调度器
• 新的性能工具和选项如 mysqlslap

# DBMS

数据库管理系统(DataBase Manage System)。

• DQL

• 数据查询语言（Data Query Language, DQL）是SQL语言中，负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。

• DDL

• 数据定义语言（Data Definition Language，DDL）是SQL语言集中负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成，最早是由Codasyl（Conference on Data Systems Languages）数据模型开始，现在被纳入SQL指令中作为其中一个子集。

• DML

• 数据操纵语言（Data Manipulation Language, DML）欧美地区的开发人员把这四种指令，以“CRUD”(分别为 Create, Read, Update, Delete英文四前缀字母缩略的术语)来称呼；而亚洲地区使用汉语的开发人员，或可能以四个汉字：增 查 改 删 来略称。

• DCL

• 数据控制语言 (Data Control Language) 在SQL语言中，是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。

# MVCC

MVCC 是一种多版本并发控制机制，是通过保存数据在某个时间点的快照来实现的. 不同存储引擎的MVCC. 不同存储引擎的MVCC实现是不同的,典型的有乐观并发控制和悲观并发控制。

• 大多数的MYSQL事务型存储引擎,如InnoDB，Falcon以及PBXT都不使用一种简单的行锁机制.事实上,他们都和MVCC–多版本并发控制来一起使用.
• 众所周知,锁机制可以控制并发操作,但是其系统开销较大,而MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销.

# 事务隔离级别

• 事务是数据库系统区别于其他一切文件系统的重要特性之一

• 事务是一组具有原子性的SQL语句，或者一个独立的工作单元

mysql默认的事务隔离级别为 repeatable-read，可以使用如下命令查询：

select @@tx_isolation;

| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 | | ------------- |:-------------😐 :-------------😐 | 读未提交（read-uncommitted） | 是 | 是 | 是 | | 不可重复读（read-committed） | 否 | 是 | 是 | | 可重复读（repeatable-read） | 否 | 否 | 是 | | 串行化（serializable） | 否 | 否 | 否 |

# 事务的基本要素（ACID）

• 1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

• 2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

• 3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

• 4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

# 事务的并发问题

• 1、脏读：未提交读(READ UNCOMMITED)，事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。

• 2、不可重复读：已提交读(READ COMMITED)符合隔离性的基本概念，事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

• 3、可重复读：(REPEATABLE READ) InnoDB的默认隔离等级。事务进行时，其它所有事务对其不可见，即多次执行读，得到的结果是一样的！

• 4、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

# 数据库锁

MySQL中锁主要作用是针对共享资源的并发访问以及用于实现事务的隔离性。

MySQL数据中常见的锁解读

# 乐观锁

乐观并发控制（又名“乐观锁”，Optimistic Concurrency Control，缩写“OCC”）是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。乐观事务控制最早是由孔祥重（H.T.Kung）教授提出。

乐观并发控制多数用于数据争用不大、冲突较少的环境中，这种环境中，偶尔回滚事务的成本会低于读取数据时锁定数据的成本，因此可以获得比其他并发控制方法更高的吞吐量。

优缺点：

乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。

# 悲观锁

悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作读某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。

悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

按使用性质划分

# 共享锁（Share Lock）

• S锁，也叫读锁，用于所有的只读数据操作。共享锁是非独占的，允许多个并发事务读取其锁定的资源。

1. 多个事务可封锁同一个共享页；
2. 任何事务都不能修改该页；
3. 通常是该页被读取完毕，S锁立即被释放。

# 更新锁（Update Lock）

• U锁，在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。 因为当使用共享锁时，修改数据的操作分为两步：
• 首先获得一个共享锁，读取数据，然后将共享锁升级为排他锁，再执行修改操作。
• 这样如果有两个或多个事务同时对一个事务申请了共享锁，在修改数据时，这些事务都要将共享锁升级为排 他锁。这时，这些事务都不会释放共享锁，而是一直等待对方释放，这样就造成了死锁。
• 如果一个数据在修改前直接申请更新锁，在数据修改时再升级为排他锁，就可以避免死锁。

# 排他锁（Exclusive Lock）

• X锁，也叫写锁，表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。（某个顾客把试衣间从里面反锁了，其他顾客想要使用这个试衣间，就只有等待锁从里面打开了。）

**按作用范围划分 **

# 行锁

• 锁的作用范围是行级别。

# 表锁

• 锁的作用范围是整张表。数据库能够确定那些行需要锁的情况下使用行锁，如果不知道会影响哪些行的时候就会使用表锁。

# 页锁

• 对表中一组连续的行进行加锁。

• 性能比较

• 表锁：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低

• 行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

• 页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

优缺点：

悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；另外，在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数据。

# MySQL性能

影响MySQL性能的几个因素：

• 服务器硬件
• 服务器系统（系统参数优化）
• 磁盘IO
• 磁盘IO性能突然下降、大量消耗磁盘性能的计划任务。解决：更快磁盘设备、调整计划任务、做好磁盘维护。
• 网络延迟
• 存储引擎。
• MyISAM： 不支持事务，表级锁。
• InnoDB: 支持事务，支持行级锁，事务ACID。
• 数据库参数配置
• 数据库结构设计和SQL语句。（重点优化）

# 默认库解读

• information_schema

• information_schema提供了访问数据库元数据的方式。(元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有时用于表述该信息的其他术语包括“数据词典”和“系统目录”。)

• 换句换说，information_schema是一个信息数据库，它保存着关于MySQL服务器所维护的所有其他数据库的信息。(如数据库名，数据库的表，表栏的数据类型与访问权 限等。) 在INFORMATION_SCHEMA中，有几张只读表。它们实际上是视图，而不是基本表。

• mysql

• mysql的核心数据库，类似于sql server中的master表，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息。(常用的，在mysql.user表中修改root用户的密码)。

• performance_schema

• 主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。MySQL5.7默认是开启的。

• sys

• Sys库所有的数据源来自：performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。

# 体系结构

• 客户端

• 服务层

• 连接管理、查询缓存、查询解析、查询优化器

• 存储引擎

• 执行流程

# 存储引擎

MySQL常见的存储引擎有InnoDB和MyISAM两种，其他NDBCluster、Merge、Memory等稍微了解即可。

# InnoDB

• 在InnoDB中，如果只需要查找索引的列，就尽量不要加入其它的列，这样会提高查询效率。

• InnoDB 使用 B+Tree 作为索引结构，叶节点直接存储的是完整的数据记录，索引的 key 是数据表的主键,因此 InnoDB 表数据文件本身就是主索引。

• 查看InnoDB数据存储

  show variables like 'innodb_file_per_table'

• 如果innodb_file_per_table 为 ON 将建立独立的表空间，文件为tablename.ibd；

• 如果innodb_file_per_table 为 OFF 将数据存储到系统的共享表空间，文件为ibdataX（X为从1开始的整数）；

• MySQL5.5默认系统表空间与MySQL5.6及以后默认独立表空间

• 1.系统表空间无法简单的收缩文件大小，造成空间浪费，并会产生大量的磁盘碎片。

• 2.独立表空间可以通过optimeze table 收缩系统文件，不需要重启服务器也不会影响对表的正常访问。

• 3.如果对多个表进行刷新时，实际上是顺序进行的，会产生IO瓶颈。

• 4 独立表空间可以同时向多个文件刷新数据。

• 5 强烈建立对Innodb 使用独立表空间，优化什么的更方便，可控。

• 特性：支持事务和行级锁（具体说明参考事务和锁）

  InnoDB的存储文件格式分为：

    .frm:表结构定义信息
    .ibd:表数据和索引数据
  

# MyISAM

• MyISAM存储引擎采用的是非聚簇索引，非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。

• 非聚簇索引的数据表和索引表是分开存储的。

• 非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。插入顺序不受键值影响。

• 只有在MyISAM中才能使用FULLTEXT索引。

• MyISAM 引擎默认使用 B+Tree 作为索引结构,叶节点的 data 域存放的是数据记录的地址。

• MyISAM存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件。首先肯定会有任何存储引擎都不可缺少的存放表结构定义信息的.frm文件，另外还有.MYD和.MYI文件，分别存放了表的数据（.MYD）和索引数据（.MYI）。每个表都有且仅有这样三个文件做为MyISAM存储类型的表的存储，也就是说不管这个表有多少个索引，都是存放在同一个.MYI文件中。

• 支持R-Tree索引、R-Tree索引、全文索引。

  MyISAM的存储文件格式分为： .frm:表结构定义信息 .MYD:表的数据 .MYI:索引数据

• InnoDB和MyISAM的区别

• 使用主索引的时候，更适合使用聚簇索引，因为聚簇索引只需要查找一次，而非聚簇索引在查到数据的地址后，还要进行一次I/O查找数据。

• 因为聚簇辅助索引存储的是主键的键值，因此可以在数据行移动或者页分裂的时候降低委会成本，因为这时不用维护辅助索引。但是辅助索引会占用更多的空间。

• 聚簇索引在插入新数据的时候比非聚簇索引慢很多，因为插入新数据时需要减压主键是否重复，这需要遍历主索引的所有叶节点，而非聚簇索引的叶节点保存的是数据地址，占用空间少，因此分布集中，查询的时候I/O更少，但聚簇索引的主索引中存储的是数据本身，数据占用空间大，分布范围更大，可能占用好多的扇区，因此需要更多次I/O才能遍历完毕。

# Archive

• Archive非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive 拥有高效的插入速度，但其对查询的支持相对较差。

# Memory

• Memory所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在 MySQL 重新启动时丢失

# Merge

• Merge是将一定数量的 MyISAM 表联合而成一个整体，在超大规模数据存储时很有用。

# CSV

• 逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个 .csv 文件。这是一种普通文本文件，每个数据行占用一个文本行。CSV 存储引擎不支持索引。

# BlackHole

• 黑洞引擎，写入的任何数据都会消失，一般用于记录 binlog 做复制的中继。

# EXAMPLE

• EXAMPLE 存储引擎是一个不做任何事情的存根引擎。它的目的是作为 MySQL 源代码中的一个例子，用来演示如何开始编写一个新存储引擎。同样，它的主要兴趣是对开发者。EXAMPLE 存储引擎不支持编索引。

# 数据预热

默认情况，仅仅有某条数据被读取一次，才会缓存在innodb_buffer_pool。所以，数据库刚刚启动，须要进行数据预热，将磁盘上的全部数据缓存到内存中。数据预热能够提高读取速度。对于InnoDB数据库，能够用下面方法，进行数据预热。

• 1.将下面脚本保存为 MakeSelectQueriesToLoad.sql

SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
    ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
    FROM
    (
        SELECT
            engine,table_schema db,table_name tb,
            index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
        FROM
        (
            SELECT
                B.engine,A.table_schema,A.table_name,
                A.index_name,A.column_name,A.seq_in_index
            FROM
               information_schema.statistics A INNER JOIN
                (
                    SELECT engine,table_schema,table_name
                    FROM information_schema.tables WHERE
                    engine='InnoDB'
                ) B USING (table_schema,table_name)
            WHERE B.table_schema NOT IN ('information_schema','mysql')
            ORDER BY table_schema,table_name,index_name,seq_in_index
        ) A
        GROUP BY table_schema,table_name,index_name
    ) AA
ORDER BY db,tb
;

• 2.运行

mysql -uroot -AN < /root/MakeSelectQueriesToLoad.sql > /root/SelectQueriesToLoad.sql

• 3.加入启动脚本到 my.cnf ``bash vim /etc/my.cnf

[mysqld]

init-file=/var/lib/mysql/upcache.sql

### MySQL执行线程

**1、查看`MySQL`当前连接数**

​```bash
show full processlist;

statistics 状态的线程，表示正在计算统计数据，以制定一个查询执行计划。 如果一个线程很长一段时间处于这种状态，可能是磁盘绑定的，即磁盘IO性能很差，说到底就是数据库服务器IO遇到问题了，可以通过增加 buffer_pool 来缓存更多的数据，或者提高服务器IO能力，可能磁盘在执行其他工作。

服务器IO状态会一直占用cpu，导致性能严重下降。

2、显示MySQL状态

show status;

• Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
• Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。
• Connections 试图连接MySQL服务器的次数。
• Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。
• Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。
• Open_tables 打开表的数量。
• Open_files 打开文件的数量。
• Open_streams 打开流的数量(主要用于日志记载）
• Opened_tables 已经打开的表的数量。
• Questions 发往服务器的查询的数量。
• Slow_queries 要花超过long_query_time时间的查询数量。
• Threads_connected 当前打开的连接的数量。
• Threads_running 不在睡眠的线程数量。
• Uptime 服务器工作了多少秒。

3、终止正在执行的MySQL线程

kill id;

杀死耗时或者长时间处于Sending to Client状态的慢sql。

减少磁盘绑定：

• 1. 扩大innodb_buffer_pool_size的缓冲池大小。如果你用Innodb然后，当表数据缓存在InnoDB缓冲池中时，可以通过查询一次又一次地处理表数据，而不需要任何磁盘I/O。这个内存区域非常重要，以至于繁忙的数据库通常指定的大小约为物理内存量的80%。
• 2. 在GNU/Linux和Unix的某些版本中，使用Unixfsync()调用(InnoDB默认使用)将文件刷新到磁盘，类似的方法非常慢。如果数据库写入性能是一个问题，则使用INNODB_FLUSH_Method参数设置为O_DSYNC进行基准测试。
• 3. 扩大innodb_buffer_pool_size的数据库日志缓冲区大小-设置分配给存储InnoDB预写日志条目的缓冲区的内存量。对于大型事务，可以将日志加载到日志缓冲区中，而不是将日志写入磁盘上的日志文件，直到日志缓冲区在每次事务提交时被刷新为止。如果您在运行时在显示nodb状态输出中看到大型日志I/O，则可能需要为innodb_log_buffer_size参数设置更大的值，以保存磁盘I/O。
• 4. 增加用于缓存表和查询的内存-检查表和查询的缓存命中率，检查并增加这些MySQL变量：query_cache_size, query_cache_limit, query_cache_min_res_unit, tmp_table_size, join_buffer_size , sort_buffer_size等等。
• 5. 确保对服务器上的所有表都应用了适当的索引，并使用正确的数据类型。

  建议：查看当前io性能状态，例如iostat

# SQL调优

# SQL执行顺序

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

# SQL优化

• 禁用select *
• 使用select count(*) 统计行数
• 尽量少运算
• 尽量避免全表扫描，如果可以，在过滤列建立索引
• 尽量避免在where子句对字段进行null判断
• 尽量避免在where子句使用!= 或者<>
• 尽量避免在where子句使用or连接
• 尽量避免对字段进行表达式计算
• 尽量避免对字段进行函数操作
• 尽量避免使用不是复合索引的前缀列进行过滤连接
• 尽量少排序，如果可以，建立索引
• 尽量少join
• 尽量用join代替子查询
• 尽量避免在where子句中使用in,not in或者having，使用exists,not exists代替
• 尽量避免两端模糊匹配 like %*%
• 尽量用union all代替union
• 尽量早过滤
• 避免类型转换
• 尽量批量insert
• 优先优化高并发sql，而不是频率低的大sql
• 尽可能对每一条sql进行explain
• 尽可能从全局出发

# Order By优化

• filesort优化算法.

• 在mysql version()<4.1之前，优化器采用的是filesort第一种优化算法，先提取键值和指针，排序后再去提取数据，前后要搜索数据两次，第一次若能使用索引则使用，第二次是随机读(当然不同引擎也不同)。mysql version()>=4.1,更新了一个新算法，就是在第一次读的时候也把selcet的列也读出来，然后在sort_buffer_size中排序(不够大则建临时表保存排序顺序)，这算法只需要一次读取数据。所以有这个广为人传的一个优化方法，那就是增大sort_buffer_size。Filesort第二种算法要用到更的空间，sort_buffer_size不够大反而会影响速度，所以mysql开发团队定了个变量max_length_for_sort_data，当算法中读出来的需要列的数据的大小超过该变量的值才使用，所以一般性能分析的时候会尝试把max_length_for_sort_data改小。

• 单独order by 用不了索引，索引考虑加where 或加limit。

• 当order by 和 group by无法使用索引时，增大max_length_for_sort_data参数设置和增大sort_buffer_size参数的设置。

# 索引命中&失效

索引是一种特殊的文件（InnoDB 数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。索引不是万能的，索引可以加快数据检索操作，但会使数据修改操作变慢。每修改数据记录，索引就必须刷新一次。为了在某种程度上弥补这一缺陷，许多 SQL 命令都有一个 DELAY_KEY_WRITE 项。这个选项的作用是暂时制止 MySQL 在该命令每插入一条新记录和每修改一条现有之后立刻对索引进行刷新，对索引的刷新将等到全部记录插入/修改完毕之后再进行。在需要把许多新记录插入某个数据表的场合，DELAY_KEY_WRITE 选项的作用将非常明显。另外，索引还会在硬盘上占用相当大的空间。因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引（又叫二级索引）两种。

# 聚簇索引

Innodb的聚簇索引在同一个B-Tree中保存了索引列和具体的数据，在聚簇索引中，实际的数据保存在叶子页中，中间的节点页保存指向下一层页面的指针。“聚簇”的意思是数据行被按照一定顺序一个个紧密地排列在一起存储。一个表只能有一个聚簇索引，因为在一个表中数据的存放方式只有一种。

• 聚簇索引的优点

• 聚簇索引将索引和数据行保存在同一个B-Tree中，查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要 第二次查询（非覆盖索引的情况下）效率要高。

• 聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的，

• 聚簇索引的缺点

• 聚簇索引的更新代价比较高，如果更新了行的聚簇索引列，就需要将数据移动到相应的位置。这可能因为要插 入的页已满而导致“页分裂”。

• 插入速度严重依赖于插入顺序，按照主键进行插入的速度是加载数据到Innodb中的最快方式。如果不是按照主 键插入，最好在加载完成后使用OPTIMIZE TABLE命令重新组织一下表。

• 聚簇索引在插入新行和更新主键时，可能导致“页分裂”问题。

• 聚簇索引可能导致全表扫描速度变慢，因为可能需要加载物理上相隔较远的页到内存中（需要耗时的磁盘寻道操作）。

# 非聚簇索引

非聚簇索引，又叫二级索引。二级索引的叶子节点中保存的不是指向行的物理指针，而是行的主键值。当通过二级索引查找行，存储引擎需要在二级索引中找到相应的叶子节点，获得行的主键值，然后使用主键去聚簇索引中查找数据行，这需要两次B-Tree查找。

# 普通索引

普通索引（由关键字 KEY 或 INDEX 定义的索引）的任务是加快对数据的访问速度。因此，应该只为那些最经常出现查询条件（WHERE column =）或排序条件（ORDER BY column）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

# 唯一索引

与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

# 主键索引

主键索引是一种特殊的唯一索引，不允许有空值，这个索引就是所谓的“主索引”。主索引区别是：前者在定义时使用的关键字是 PRIMARY 而不是 UNIQUE。

# BTree索引

叶子结点指针都为null； 非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的数据； BTree的结构下，就可以使用二分查找的查找方式，查找复杂度为h*log(n)，一般来说树的高度是很小的，一般为3左右，因此BTree是一个非常高效的查找结构。

# B+Tree索引

B+Tree是BTree的一个变种，设d为树的度数，h为树的高度，B+Tree和BTree的不同主要在于： B+Tree中的非叶子结点不存储数据，只存储键值； B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应的数据的物理地址；

# 组合索引

遵循最左前缀原则，索引可以覆盖多个数据列，如像 INDEX (columnA, columnB) 索引。这种索引的特点是 MySQL 可以有选择地使用一个这样的索引。如果查询操作只需要用到 columnA 数据列上的一个索引，就可以使用复合索引 INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX (A，B，C) 可以当做 A 或 (A,B) 的索引来使用，但不能当做 B、C 或 (B,C) 的索引来使用。

# 全文索引

仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。

索引失效

• 关联字段类型不同
• 索引字段使用函数表达式
• like %AAA
• is null

• GROUP BY 的索引应用

• 1、查询字段必须和后面GROUP BY 一致 select TeamID from competeinfo where TeamID >10 group by TeamID。 这里就是通过TeamID 来查找。完成group by 。

• 2、联合索引的应用，切记注意GROUP BY 顺序，Where 条件和GROUP BY 字段得是一个索引里面的 这个表CompeteID,TeamID建立联合索引 1）select TeamID from competeinfo where TeamID >10 and CompeteID > 100020 group by CompeteID 这个查询用到了CompeteID,TeamID联合索引 2）select TeamID from competeinfo where TeamID >10 and CompeteID > 100020 group by TeamID 这样的话查询group by中就没有用到索引了

# 主从配置文件

MySQL主从复制时时同步，是在开启log-bin日志文件的基础上，开始的时候记录主库的Position，作为从库同步的开始节点,所以主从库都必须开启log-bin日志，恢复数据也是根据此文件来操作要恢复的节点。

查看主库Position：

# 主库配置

GRANT replication slave ON *.* TO 'root'@'10.10.253.%' identified by '123456';
flush privileges;

# 主库配置文件

#安装目录
basedir = /usr/local/mysql
#数据目录
datadir = /usr/local/mysql/data
#端口
port = 3306
#socket 路径
socket = /var/lib/mysql/mysql.sock
#log-bin日志文件
log-bin=mysql-bin-1
#serverId，一般为ip的最后一段，避免重复
server_id=119
#开启查询缓存
explicit_defaults_for_timestamp=true
#设置最新日志保留时间，节省磁盘空间
expire_logs_days = 10
#sql_mode模式，定义了mysql应该支持的sql语法，数据校验等
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# sql_mode

常见的sql_mode有以下几种

# NO_ENGINE_SUBSTITUTION模式

• 如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常。

# STRICT_TRANS_TABLES模式

• 严格模式，进行数据的严格校验，错误数据不能插入，报error错误。在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制。

# ANSI模式

• 宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。

# TRADITIONAL模式

• 严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。

# ONLY_FULL_GROUP_BY模式

• 出现在select语句、HAVING条件和ORDER BY语句中的列，必须是GROUP BY的列或者依赖于GROUP BY列的函数列。

# NO_AUTO_VALUE_ON_ZERO模式

• 该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

# NO_ZERO_IN_DATE模式

• 这个模式影响了是否允许日期中的月份和日包含0。如果开启此模式，2016-01-00是不允许的，但是0000-02-01是允许的。它实际的行为受到 strict mode是否开启的影响1。

# NO_AUTO_CREATE_USER模式

• 禁止GRANT创建密码为空的用户

# ANSI_QUOTES模式

• 启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符。

# 从库设置

• 根据查看到的主库status，记录log-bin和position。

change master to master_host='192.168.80.9',master_user='root',master_password='123456',master_log_file='mysql-bin-1.000003',master_log_pos=13177757 ;

# 从库配置文件

#安装目录
basedir = /usr/local/mysql
#数据目录
datadir = /usr/local/mysql/data
#端口
port = 3306
#socket 路径
socket = /var/lib/mysql/mysql.sock
#log-bin日志文件
log-bin=mysql-bin-1
#serverId，一般为ip的最后一段，避免重复
server_id=119
#开启查询缓存
explicit_defaults_for_timestamp=true
#设置需要同步主库的DB
replicate-do-db=hsg_oa
#设置不需要同步主库的DB
replicate-ignore-db=information_schema,mysql,performance_schema,sys

删除主从库data下面的auto.cnf，会导致主从库同步节点不一致问题。

# 默认配置参数

• sort_buffer_size
• 定义了每个线程排序缓存区的大小，MySQL在有查询、需要做排序操作时才会为每个缓冲区分配内存（直接分配该参数的全部内存）；
• join_buffer_size
• 定义了每个线程所使用的连接缓冲区的大小，如果一个查询关联了多张表，MySQL会为每张表分配一个连接缓冲，导致一个查询产生了多个连接缓冲；
• read_buffer_size
• 定义了当对一张MyISAM进行全表扫描时所分配读缓冲池大小，MySQL有查询需要时会为其分配内存，其必须是4k的倍数；
• read_rnd_buffer_size
• 索引缓冲区大小，MySQL有查询需要时会为其分配内存，只会分配需要的大小。

注意：以上四个参数是为一个线程分配的，如果有100个连接，那么需要×100。

MySQL数据库实例：

• MySQL是单进程多线程（而oracle是多进程），也就是说MySQL实例在系统上表现就是一个服务进程，即进程；

• MySQL实例是线程和内存组成，实例才是真正用于操作数据库文件的；

• 一般情况下一个实例操作一个或多个数据库；集群情况下多个实例操作一个或多个数据库。

如何为缓存池分配内存：

• Innodb_buffer_pool_size，定义了Innodb所使用缓存池的大小，对其性能十分重要，必须足够大，但是过大时，使得Innodb 关闭时候需要更多时间把脏页从缓冲池中刷新到磁盘中；

总内存-（每个线程所需要的内存*连接数）-系统保留内存

• key_buffer_size，定义了MyISAM所使用的缓存池的大小，由于数据是依赖存储操作系统缓存的，所以要为操作系统预留更大的内存空间；

• select sum(index_length) from information_schema.talbes where engine='myisam'

注意：即使开发使用的表全部是Innodb表，也要为MyISAM预留内存，因为MySQL系统使用的表仍然是MyISAM表。max_connections 控制允许的最大连接数，一般2000更大。不要使用外键约束保证数据的完整性。

# 日志

事务性存储引擎及主要日志类型：Redo Log 、 Undo Log 和 general log

• Redo Log：实现事务的持久性(已提交的事务)。
• Undo Log：未提交的事务，独立于表空间，需要随机访问，可以存储在高性能io设备上
• general log：mysql将对DB的所有操作都可以记录在general log中，我们可以在线开启也可以通过修改初始化参数开启，调试用。

主要说明一下general log的使用：

在线打开和关闭

set global general_log=on;
set global general_log=off;

通过下面sql方法可以查看mysql.general_log表的存储引擎是什么 show table status from mysql where name='general_log';

这个表的缺省存储引擎是CSV，我们可以将该存储引擎修改成myisam来达到更好的性能。

mysql> alter table mysql.general_log engine=myisam;
1580 - You cannot 'ALTER' a log table if logging is enabled
mysql> set global general_log=off;
Query OK, 0 rows affected

mysql> alter table mysql.general_log engine=myisam;
Query OK, 11 rows affected
Records: 11  Duplicates: 0  Warnings: 0

mysql> set global general_log=on;
Query OK, 0 rows affected

记录general log可以很好的监控当前数据库中的所有操作，可以选择在忙时打开，这样就可以对性能进行优化。 但general log比较消耗系统性能，生产系统不建议打开

# 视图

视图概述

视图本身是一个虚拟表，不存放任何数据，查询视图的数据集由其他表生成。MySQL底层通过两种算法来实现视图：临时表算法（TEMPTABLE）和合并算法（MERGE）。

临时表算法就是将SELECT语句的结果存放到临时表中，当需要访问视图的时候，直接访问这个临时表即可。

相比于其它关系型数据库的视图，MySQL的视图在功能上会弱很多，比如ORACLE和MS SQL SERVER都支持物化视图。物化视图是指将视图结果数据存放在一个可以查询的表中，并定期从原始表中刷新数据到这张表中，这张表和普通物理表一样，可以创建索引、主键约束等等，性能相比于临时表会有质的提升。但遗憾的是MySQL目前并不支持物化视图，当然MySQL也不支持在视图中创建索引。

视图的实现算法是视图本身的属性决定的，跟作用在视图上的SQL没有任何关系。一般来说，只要原表记录和视图中的记录无法建立一一映射的关系时，MySQL都将使用临时表算法来实现视图。比如创建视图的SQL中包含GROUP BY、DISTINCT、UNION、聚合函数、子查询的时候，视图都将采用临时表算法（这些规则在以后的版本中，可能会发生改变，具体请参考官方手册）

视图用途

首先视图可以简化应用上层的操作，让应用更专注于其所关心的数据。其次，视图能够对敏感数据提供安全保护，比如：对不同的用户定义不同的视图，可以使敏感数据不出现在不应该看到这些数据的用户视图上；也可以使用视图实现基于列的权限控制，而不需要真正的在数据库中创建列权限。再者，视图可以方便系统运维，比如：在重构schema的时候使用视图，使得在修改视图底层表结构的时候，应用代码还可以继续运行不报错。

# Explain&Profiling

# Explain

• 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是 如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

• 使用方式：Explain+SQL语句

• 详细参数说明参见MySQL 性能优化神器 Explain 使用分析，里面写的很有特点。

# Profiling

• 开启profiling设置

查看profiling信息状态: on为开启，off关闭

show variables like '%profiling%';    
set profiling=1;

3. 执行SQL查询

SELECT * FROM oa_sign_in ; 
show  profiles;

show profile cpu,block io for query 168;

{% blockquote %}

# 参考文献

• statistics State in MySQL Processlist

• List of background operations performed by MySQL InnoDB Main thread

• processlist中哪些状态要引起关注

• 是什么导致MySQL数据库服务器磁盘I/O高？

• table_open_cache 与 table_definition_cache 对MySQL(内存)的影响

• 8.5.8 Optimizing InnoDB Disk I/O

• MySQL的四种事务隔离级别

• 轻松理解MYSQL MVCC 实现机制

• MySQL性能管理及架构设计

• MySQL索引背后的数据结构及算法原理