Mysql高级知识概括

• Mysql简介
• MysqlLinux版本的安装
• Mysql配置文件
• Mysql逻辑架构介绍
• Mysql存储引擎
• 索引优化分析
• • 索引介绍
  • 性能分析
  • • Explain各个字段解释
  • 子查询和连接查询
  • 索引优化
• 查询截取分析
• • 查询优化
  • 慢查询日志
  • 批量数据脚本
  • Show profiles
  • 全局查询日志
• Mysql锁机制
• 主重复制
• 读写分离

Mysql简介

简介：

高级MySQL：

• mysql内核
• sql优化工程师
• mysql服务器的优化
• 查询语句优化
• 主重复制
• 软硬件升级
• 容灾备份
• sql编程

完整的mysql优化需要很深的功底,大公司甚至有专门的DBA写上述

MysqlLinux版本的安装

Mysql配置文件

二进制日志log-bin:

• 主从复制

错误日志log-error:

• 默认是关闭的,记录严重的警告和错误信息,每次启动和关闭的详细信息等.

查询日志log:

• 默认关闭,记录查询的sql语句，如果开启会减低mysql的整体性能，因为记录日志也是需要消耗系统资源的(对应下面介绍的慢查询日志)

数据文件:

• 两系统:

  windows:D:\ProgramFiles\MySQL\MySQLServer5.5\data目录下可以挑选很多库

  linux:看看当前系统中的全部库后再进去,默认路径：/var/lib/mysql

• frm文件:

  存放表结构

• myd文件:

  存放表数据

• myi文件:

  存放表索引

如何配置:

• windows:

  my.ini文件

• Linux:

  /etc/my.cnf文件

Mysql逻辑架构介绍

• 1、Connectors

  指的是不同语言中与SQL的交互

• 2、 Management Serveices & Utilities：

  系统管理和控制工具

• 3、 Connection Pool: 连接池

  管理缓冲用户连接，线程处理等需要缓存的需求。

  负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，

  接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。

• 4、 SQL Interface: SQL接口。

  接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface

• 5、 Parser: 解析器。

  SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。

  在 MySQL中我们习惯将所有 Client 端发送给 Server 端的命令都称为 query ，在 MySQL Server 里面，连接线程接收到客户端的一个 Query 后，会直接将该 query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。

  主要功能：

  a . 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。

  b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的

• 6 、Optimizer: 查询优化器。

  SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求的 query（sql语句） ，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果

  他使用的是“选取-投影-联接”策略进行查询。

  用一个例子就可以理解： select uid,name from user where gender = 1;

  这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤

  这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤

  将这两个查询条件联接起来生成最终查询结果

• 7、 Cache和Buffer： 查询缓存。

  他的主要功能是将客户端提交 给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值 做一个对应。该 Query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。

  如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

• 8 、存储引擎接口

  存储引擎接口模块可以说是 MySQL 数据库中最有特色的一点了。目前各种数据库产品中，基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是 一个抽象类，但正是因为它成功地将各种数据处理高度抽象化，才成就了今天 MySQL 可插拔存储引擎的特色。

  从图2还可以看出，MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。

  注意：存储引擎是基于表的，而不是数据库。

Mysql存储引擎

查看命令：

MyISAM和InnoDB：

阿里巴巴，淘宝用哪个：

索引优化分析

性能下降SQL慢 、执行时间长 、等待时间长：

• 查询语句写的烂
• 索引失效
• 关联查询太多join(设计缺陷或不得已的需求)
• 服务器调优及各个参数设置(缓冲\线程数等)

SQL执行顺序：

• 手写：

  

• 机读：

  

• 总结：

  

常见通用的join查询：

索引介绍

索引简介：

• MySQL官方对索引的定义为：索引(Index)是帮助MySQL高校获取数据的数据结构。 可以得到索引的本质：索引是数据结构。

  

• 你可以简单理解为

  "排好序的快速查找数据结构"

  。

  

• 结论：数据本身之外,数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。
• 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以文件形式存储在硬盘上。
• 我们平时所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉树)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然,除了B+树这种类型的索引之外，还有哈希索引(hash index)等。

索引优势：

• 类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本
• 通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗

索引劣势：

• 实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录,所以索引列也是要占用空间的

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度,如果对表INSERT,UPDATE和DELETE。

  因为更新表时，MySQL不仅要不存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

• 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句

mysql索引分类：

• 单值（单列）索引：

  ①

  普通索引

  ： MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一 点。

  ②

  唯一索引：

  索引列的值必须唯一，但允许有空值。（

  唯一约束就是一个唯一索引

  ）

  <1>

  主键索引：是一种特殊的唯一索引

  ，不允许有空值。（

  主键约束就是一个主键索引

  ）

• 复合（组合）索引：

  ① 即一个索引包含多个列

• 全文索引：

  ①全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。
• 建议：一张表索引不要超过5个且优先考虑复合索引
• 注意：当索引的列名太长可以转换成前缀索引。
• 链接：索引的类型分类、区别、优缺点

添加PRIMARY KEY（主键索引） 
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

添加UNIQUE(唯一索引) 
ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

添加INDEX(普通索引) 
ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 

添加FULLTEXT(全文索引) 
ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

添加多列索引 
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
           

唯一索引：

• 普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。
• 如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。
• 这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是,MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。
• 事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

前缀索引：

• 当要索引的列字符很多时，索引则会很大且变慢。（

  对于 BLOB、TEXT 和 VARCHAR 类型的列

  ）
• 因此可以只用索引列

  开始的部分字符串

  ，节约索引空间，从而提高索引效率。

• 索引的选择性，是指不重复的索引数量除以总记录数，范围是(0,1]。

• 但前提要保证截取后的索引列与截取前

  选择性是一样的！

• 前缀索引详细链接

全文索引：

• 通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。
• 你可能会说，用 like + % 就可以实现模糊匹配了，为什么还要全文索引？like + %在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N倍，速度不是一个数量级，但是全文索引可能存在精度问题。
• 你可能没有注意过全文索引，不过至少应该对一种全文索引技术比较熟悉：各种的搜索引擎。虽然搜索引擎的索引对象是超大量的数据，并且通常其背后都不是关系型数据库，不过全文索引的基本原理是一样的。
• 全文索引详解

mysql索引基本语法：

• 创建：

  ①CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));

  ②ALTER mytable ADD [UNIQUE] INDEX [indexName] ON(columnname(length));

• 删除：

  DROP INDEX [indexName] ON mytable;

• 查看：

  SHOW INDEX FROM table_name\G

• 使用ALTER命令：

  

mysql索引结构(按索引底层的数据结构分类)：

• 链接：MySQL索引数据结构
• 正如上文中说到，索引是提高查询效率的数据结构，而能够提高查询效率的数据结构有很多，如二叉搜索树，红黑树，跳表，哈希表（散列表）等，而MySQL中用到了

  B+Tree

  和

  散列表（Hash表）

  作为索引的底层数据结构。

• Hash索引：

  ①需要注意的是，

  MySQL并没有显式支持Hash索引，而是作为内部的一种优化，对于热点的数据会自动生成Hash索引，也叫自适应Hash索引。

  ②Hash索引在等值查询中，可以O（1）时间复杂度定位到数据，效率非常高，但是不支持范围查询。在许多编程语言以及数据库中都会用到这个数据结构，如Redis支持的Hash数据结构。具体结构如下：

  

• B+Tree索引：

  ①提到B+Tree首先不得不

  提B-Tree

  ，B-Tree（多路搜索树，并不是二叉的）是一种常见的数据结构。使用B-tree结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。

  

  ②

  B+ 树是基于B-Tree升级后的一种树数据结构

  ，通常用于数据库和操作系统的文件系统中。B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入，这与二叉树恰好相反。

  ③MySQL索引的实现也是基于这种高效的数据结构。具体数据结构如下：

  

  ④首先要声明一下，

  不要将B树，B-Tree以及B+Tree弄混淆

  。首先，

  B-Tree就是B树

  ，中间的“-”是一个中划线，而不是减号，并不存在"B减树"这种数据结构。其次，就是B+Tree和B-Tree实现索引时有两个区别：

  B+Tree只在叶子节点存储数据

  ，

  而B-Tree的数据存储在各个节点中

  。具体可见下图：

  

Hash索引：

• 哈希表也为散列表，又直接寻址改进而来。在哈希的方式下，一个元素k处于h(k)中，即利用哈希函数h，根据关键字k计算出槽的位置。函数h将关键字域映射到哈希表T[0…m-1]的槽位上。

  

• 上图中哈希函数h有可能将两个不同的关键字映射到相同的位置，这叫做碰撞，在数据库中一般采用链接法来解决。在链接法中，将散列到同一槽位的元素放在一个链表中，如下图所示：

  

BTree索引和哈希索引的区别：

• Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree索引。

• Hash索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。哈希索引只支持等值比较查询，包括＝、 IN 、<=> (注意<>和＜＝＞是不同的操作）。 也不支持任何范围查询，例如WHERE price > 100。　　

  由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。

• Hash索引无法被用来避免数据的排序操作。　　

  由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算;

• Hash索引不能利用部分索引键查询。　　

  对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用。

• Hash索引在任何时候都不能避免表扫描。　　

  前面已经知道，Hash索引是将索引键通过Hash运算之后，将 Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

• Hash索引遇到大量Hash值相等的情况后性能并不一定就会比BTree索引高。　

  对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

Mysql索引（数据存储方式分类）：

• 聚簇索引(聚集索引)

  ①聚簇索引就是按照每张表的主键构造一颗B+树，

  同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。

  这个特性决定了索引组织表中数据也是索引的一部分，

  每张表只能拥有一个聚簇索引。

  ②

  Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

  ③优点：

  1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快

  2.聚簇索引对于主键的排序查找和范围查找速度非常快

  ④缺点：

  1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键

  2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

  3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

  

• 辅助索引（非聚簇索引）

  ①在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。

  辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

  通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的PageDirectory找到数据行。

  ②Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。

  ③辅助索引的存在不影响数据在聚簇索引中的组织，

  所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。

  

• 详细链接：

  聚簇索引和非聚簇索引详解

  聚簇索引和非聚簇索引比较

• 案例：

  ①InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，

  若使用"where id = 14"

  这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

  ②若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中

  检索Name，

  到达其叶子节点获取

  对应的主键

  。第二步

  使用主键

  在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

MyISAM索引和InnoDB索引总结：

• Innobd存储引擎中的索引：

  <1>

  Innobd中的主键索引是一种聚簇索引，

  <2>

  Innobd非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。

• MyISAM存储引擎中，数据文件和索引文件是单独分离的在MyISAM中，无论是主键索引还是辅助索引，都是非聚簇索引，

  在结构上两者没有什么区别

  ，它们各自存放对应主键或辅助键，最后指向对应地址的表数据。不过，主键索引中key是唯一的，而辅助索引中key是可重复的。
• MyISAM，不支持数据库ACID事务，也不支持行级锁和外键的约束，但是会保存表的行数。

  

哪些情况需要创建索引:

• 1.主键自动建立唯一索引
• 2.频繁作为查询的条件的字段应该创建索引
• 3.查询中与其他表关联的字段，外键关系建立索引
• 4.查询中统计或者分组字段
• 5.查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
• 6.单间/组合索引的选择问题，who？（在高并发下倾向创建组合索引）

哪些情况不要创建索引：

• 1.表记录太少
• 2.经常增删改的表

• 3.数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。

  注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

• 4.频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引，加重IO负担
• 5.Where条件里用不到的字段不创建索引

性能分析

MySQL常见瓶颈：

• CPU:CPU在饱和的时候一般发生在数据装入在内存或从磁盘上读取数据时候
• IO:磁盘I/O瓶颈发生在装入数据远大于内存容量时
• 服务器硬件的性能瓶颈：top,free,iostat和vmstat来查看系统的性能状态

Explain：

• 简介：

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

• 能干嘛：

  ①表的读取顺序

  ②数据读取操作的操作类型

  ③哪些索引可以使用

  ④哪些索引被实际使用

  ⑤表之间的引用

  ⑥每张表有多少行被优化器查询

• 怎么玩：

  ① Explain+SQL语句

  ② 执行计划包含的信息：

  

Explain各个字段解释

id:

• 简介：

  select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。

• 三种情况：

  ①id相同，执行顺序由上至下

  

  ②id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

  

  ③id相同不同，同时存在

  

select_type:

简介：查询的类型，主要用于区别普通查询、联合查询、子查询等的复杂查询

• 1.SIMPLE:

  简单的select查询，查询中不包含子查询或者UNION

• 2.PRIMARY:

  查询中若包含任何复杂的子部分，最外层查询则被标记为

• 3.SUBQUERY:

  在SELECT或者WHERE列表中包含了子查询

• 4.DERIVED:

  在FROM列表中包含的子查询被标记为DERIVED（衍生）

  MySQL会递归执行这些子查询，把结果放在临时表里。

• 5.UNION:

  若第二个SELECT出现在UNION之后，则被标记为UNION;

  若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

• 6.UNION RESULT:

  从UNION表获取结果的SELECT

table：

简介：

①显示这一行的数据是关于哪张表的

type:

访问类型排列:

详解：

• system：

  表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计

• const：

  表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键至于where列表中，MySQL就能将该查询转换为一个常量。（用在单表查询时）

• eq_ref：

  唯一性索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。（用在多表查询时）const和ref_eq区别

• ref：

  非唯一索引扫描，返回匹配某个单独值的所有行。

  本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，

  它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

• range：

  只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引

  一般就是在你的where语句中出现了between、<、>、in等的查询

  这种范围扫描索引扫描比全表扫描要好，因为他只需要开始索引的某一点，而结束语另一点，不用扫描全部索引

• index：

  Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。

  （也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

• all：

  FullTable Scan,将遍历全表以找到匹配的行

possible_keys：

简介：

①显示可能应用在这张表中的索引,一个或多个。

②查询涉及的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key：

简介：

①实际使用的索引。如果为null则没有使用索引。

②查询中若使用了覆盖索引，则索引和查询的select字段重叠。

key_len：

简介：

①表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。

②key_len显示的值为索引最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

ref：

简介：

①显示索引那一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值。

rows：

简介：

①根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

Extra：

• 1.Using filesort：

  说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。

  MySQL中无法利用索引完成排序操作成为“文件排序”

• 2.Using temporary：

  使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by

• 3.USING index：

  表示相应的select操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！

  如果同时出现using where，表明索引被用来执行索引键值的查找；

  如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

• 4.Using where：

  表明使用了where过滤

• 5.using join buffer：

  使用了连接缓存

• 6.impossible where：

  where子句的值总是false，不能用来获取任何元组

• 7.select tables optimized away：

  在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者

  对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，

  查询执行计划生成的阶段即完成优化。

• 8.distinct：

  优化distinct，在找到第一匹配的元组后即停止找同样值的工作

覆盖索引（Covering Index）:

子查询和连接查询

简介：

• 连接查询

  ①优点是可以用尽可能少的SQL进行查询。简化了应用和数据库之间的IO调用。

  ②缺点是如果表设计不好，SQL写得差，会造成数据库大量的内部IO操作，特别是大量没必要的全表扫描。使用这种方式必须要么是确实要读取的数据量非常大，要么是能够通过索引等方式控制住全表扫描的数量。全表扫描在连接情况下的消耗可以说是指数性的升高的。

• 子查询

  ①缺点是应用和数据库之间的IO调用比较多，损耗了数据库的带宽。

  ②优点是对原来的被驱动表来说数据是明确的，可以通过大量的索引，特别是主键索引避免全表扫描。

• 总结：

  ①

  用哪种没有一定之规，要看读取的数据量、表设计结构、数据库规模、程序设计等多种因素综合考虑。

索引优化

案例分析：

案例（索引失效）：

• 1.全值匹配我最爱

  

• 2.最佳左前缀法则

  如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

  

• 3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

  

• 4.存储引擎不能使用索引中范围条件右边的列

  

• 5.尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*

  

• 6.mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

  

• 7.is null,is not null 也无法使用索引

  

• 8.like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作

  

• 9.字符串不加单引号索引失效

  

• 10.少用or,用它连接时会索引失效

  

问题：解决like’%字符串%'索引不被使用的方法？？

• 1、可以使用主键索引
• 2、使用覆盖索引，查询字段必须是建立覆盖索引字段
• 3、当覆盖索引指向的字段是varchar(380)及380以上的字段时，覆盖索引会失效！

小总结:

一般性建议:

• 对于单键索引，尽量选择针对当前query过滤性更好的索引
• 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
• 在选择组合索引的时候，尽量选择可以能包含当前query中的where子句中更多字段的索引
• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

查询截取分析

查询优化

永远小表驱动大表类似嵌套循环Nested Loop：

order by关键字优化：

• ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序
• 尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
• 如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序。
• 优化策略：

  

GROUP BY关键字优化：

• groupby实质是先排序后进行分组，遵照索引建的最佳左前缀。
• 当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
• where高于having,能写在where限定的条件就不要去having限定了。

慢查询日志

慢查询日志是什么：

慢查询日志说明：

查看是否开启及如何开启：

开启慢查询日志后，什么样的SQL参会记录到慢查询里面？

查看当前多少秒算慢：

SHOW VARIABLES LIKE ‘long_query_time%’;

设置慢的阙值时间：

为什么设置后看不出变化？

• ①需要重新连接或者新开一个回话才能看到修改值。 SHOW VARIABLES LIKE ‘long_query_time%’;
• ②show global variables like ‘long_query_time’;

记录慢SQL并后续分析：

查询当前系统中有多少条慢查询记录：

日志分析工具mysqldumpshow：

• 查看mysqldumpshow的帮助信息：

  

• 工作常用参考：

  

批量数据脚本

Show profiles

Show profiles概述：

①Show profiles是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量。

②默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

步骤：

备注：

全局查询日志

注意：

• 永远不要在生产环境开启这个功能。
• 一般在测试环境中使用此功能。

Mysql锁机制

概述：

锁的分类：

• 从数据操作的类型（读、写）分：

  ①读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响

  ②写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

• 从对数据操作的颗粒度：

  ①表锁

  ②行锁

表锁（偏读）:

• 特点:

  偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发最低

  

  

  

行锁（偏写）:

• 特点:偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）;二是采用了行级锁。
• 无索引或索引失效时行锁升级为表锁。（因为Innodb引擎是根据索引来实现行锁的，如果不存在索引，那么就不能对此行加record lock，而mysql为了防止错误，只好全部锁定了。）
• 间隙锁危害

  

  

• 面试题：常考如何锁定一行。

  

• 结论：

  

• 触发机制：

  innodb的行锁是根据索引触发，如果没有相关的索引，那行锁将会退化成表锁（即锁定整个表里的行）。而锁锁定的是索引即索引树里面的数据库字段的值。

• 链接：mysql 如何触发行锁_MySQL的锁到底有多少内容？

• 优化建议：

  ①尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

  ②合理设计索引，尽量缩小锁的范围

  ③尽可能较少检索条件，避免间隙锁

  ④尽量控制事务大小，减少锁定资源量和时间长度

  ⑤尽可能低级别事务隔离

页锁：

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

主重复制

复制的基本原理：

• 底层主要依靠二进制日志文件（binary log）

  。

复制的基本原则：

• 每个slave只有一个master
• 每个slave只能有一个唯一的服务器ID
• 每个master可以有多个salve

复制最大问题：

• 延时

一主一从常见配置：

注意：

• ①mysql版本一致且后台以服务运行
• ②主从都配置在【mysqld】结点下，都是小写

步骤：

• ①主机修改my.ini配置文件
• ②从机修改my.cnf配置文件
• ③主机从机都关闭防火墙
• ④在Windows主机上简历账户并授权slave
• ⑤在Linux从机上配置需要复制的主机
• ⑥主机新建库、新建表、insert记录，从机复制
• ⑦如何停止从服务复制功能：stop slave;

主从复制的作用：

• 一是

  确保数据安全；做数据的热备

  ，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据的丢失。

  ①数据分为主表和

  历史表

  ，

  历史表是容灾备份表

  。
• 二是提升I/O性能；随着日常生产中业务量越来越大,I/O访问频率越来越高，单机无法满足，此时做多库的存储，有效降低磁盘I/O访问的频率，提高了单个设备的I/O性能。
• 三是读写分离，使数据库能支持更大的并发；在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

MYCAT简介：

• 数据库中间件,前身是阿里的 cobar
• MYCAT 原理 “拦截”：Mycat 的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的 SQL 语句，首先对 SQL 语 句做了一些特定的分析：如

  分片分析、路由分析、读写分离分析、缓存分析

  等，然后将此 SQL 发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。
• 这种方式把数据库的分布式从代码中解耦出来，程序员察觉不出来后台使用 mycat 还是 mysql。

读写分离

• MySQL读写分离是指让master处理写操作，让slave处理读操作，非常适用于读操作量比较大的场景，可减轻master的压力。
• 使用mysql-proxy实现mysql的读写分离，mysql-proxy实际上是作为后端mysql主从服务器的代理，它直接接受客户端的请求，对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上。

• mysql-proxy是官方提供的mysql中间件产品可以实现负载平衡，读写分离，failover等

• MySQL Proxy就是这么一个中间层代理，简单的说，MySQL Proxy就是一个连接池，负责将前台应用的连接请求转发给后台的数据库，并且通过使用lua脚本，可以实现复杂的连接控制和过滤，从而实现读写分离和负载平衡。对于应用来说，MySQL Proxy是完全透明的，应用则只需要连接到MySQL Proxy的监听端口即可。
• 当然，这样proxy机器可能成为单点失效，但完全可以使用多个proxy机器做为冗余，在应用服务器的连接池配置中配置到多个proxy的连接参数即可。