MySql性能调优笔记(一)

一。Mysql内部整个结构和执行流程

MySQL简易模块图:

简易图：1.连接/线程处理:主要负责连接和线程处理、授权认证、安全。

                                           当客户端连接到mysql服务器时，服务器需要对其进行认证，认证基于用户名，原始主机信息和密                                                   码。

2.查询缓存/解析器：功能有解析,分析,优化,缓存，内置函数。

                                 mysql解析查询并创建内部数据结构(解析树)，然后对其优化，包括重写查询、决定表的读取顺序，以及选                                    择合适的索引。优化器会请求存储引起提供容量或某个具体的开销信息，以及表数据的统计信息等。

3.存储引擎:负责Mysql中数据存储和提取。

数据库范式

    第一范式：字段列不可分，如:【联系人】（姓名，性别，电话），一个联系人有家庭电话和公司电话，不符合 1NF；

   第二范式：有主键，非主键字段完全依赖主键。如:订单明细表【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName），Discount（折扣），Quantity（数量）完全依赖（取决）于主键（OderID，ProductID），而 UnitPrice，ProductName 只依赖于 ProductID，不符合2NF；

  第三范式：非主键字段不能相互依赖(如：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况)，

              每列都与主键有直接关系，不存在传递依赖。如:订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID），CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。

1）锁

    处理并发读或写时，可以通过实现一个由两种类型的锁组成的锁系统拉埃解决。

    也就是共享锁(shared lock)和排他锁(exclusive lock),也叫读锁 和写锁。

    读锁 :是共享的,多个客户端在同一时刻可以同时读取同一个资源，互不干扰。

    写锁:是排他的只有一个用户(客户端)能执行写入，并防止其他用户读取正在写入的同一资源。

    尽量只锁定修改的部分数据，而不是所有资源，来提供共享资源的

Mysql各存储引起使用了三种类型(级别)的锁定机制：行级锁定，页级锁定和表级锁定

表锁：与行锁相反，最大颗粒度锁定机制,实现逻辑简单，系统开销负面影响小，获取锁和释放锁速度快，会锁定整张表，一个用            户在对表进行写操作(插入、删除、更新等)前，需要先获得写锁，会阻塞其他用户对该表读写操作。 读锁之间是不                        相互阻塞的。

            ---------》主要使用该锁的是: MyISAM,Memory,CSV等一些非事务性存储引擎，

 行锁:    最大特点，锁定对象颗粒度很小，每次获取锁和释放锁需要做很多事情，所以开销消耗比较大，行锁页最容易发生死                     锁，在InnoDB和XtraDB,以及其他的一些存储 引擎中实现了行级锁。

               --------》主要使用该锁的是:Innodb存储引擎和NDB存储引擎，

               ------>InnoDB的行锁是基于索引的，也就是如果那个表那一行没有索引，行锁是无效的。

  页锁：特点是锁定颗粒度介于行级锁定与表级锁定之间，会发生死锁，并发处理能力也介于两者之间。

              ----------》主要使用该锁的是:BerkeleyDB存储引擎的锁定方式。

InnoDB的锁定机制和Oracle有很多相似之处，InnoDB的行级锁定分为两种类型，共享锁和排他锁。

InnoDB：使用意向锁(表级锁定)的概念，分为了意向共享锁和意向排他锁。

 意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果