【MySQL】：查询，索引及优化，高性能存储myCat

文章目录

• 慢查询
• 慢查询配置
• 慢查询解读
• 慢查询工具
• mysqldumpslow
• pt_query_digest
• 索引
• 聚簇索引跟非聚簇索引
• 聚簇索引
• 非聚簇索引：
• 覆盖索引（Covering Index）
• 总结：
• 重点执行计划
• 执行计划详解
• 重点 MySQL优化
• 高性能存储
• keepalive+haproxy实现高可用
• MyCat
• 拦截
• 常见概念
• 逻辑库(schema)
• 逻辑表（table）
• 分片表
• 非分片表
• ER 表
• 全局表
• 分片节点(dataNode)
• 节点主机(dataHost)
• 分片规则(rule)
• 全局序列号(sequence)
• 参考

慢查询

慢查询:顾名思义，就是查询慢的日志，是指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。

show status like ‘com_insert’;—显示执行了多少次插入操作
show status like ‘com_update’;—显示执行了多少次更新
show status like ‘com_delete’;—显示执行了多少次删除
show status like ‘com_select’;—显示执行多少次查询
show status like ‘uptime’;—显示mysql数据库启动多长时间

慢查询配置

常用配置参数：

slow_query_log 启动停止技术慢查询日志
slow_query_log_file 指定慢查询日志得存储路径及文件（默认和数据文件放一起）
long_query_time 指定记录慢查询日志SQL执行时间得伐值（单位：秒，默认10秒）
log_queries_not_using_indexes 是否记录未使用索引的SQL
log_output 日志存放的地方【TABLE】【FILE】【FILE,TABLE】

show VARIABLES like ‘%slow_query_log%’
show VARIABLES like ‘%slow_query_log_file%’
show VARIABLES like ‘%long_query_time%’
show VARIABLES like ‘%log_queries_not_using_indexes%’
set global long_query_time=0;
set GLOBAL slow_query_log = 1;

记录符合条件得SQL

查询语句
数据修改语句
已经回滚得SQL

慢查询解读

慢查询工具

show status like ‘com_insert’;—显示执行了多少次插入操作
show status like ‘com_update’;—显示执行了多少次更新
show status like ‘com_delete’;—显示执行了多少次删除
show status like ‘com_select’;—显示执行多少次查询
show status like ‘uptime’;—显示mysql数据库启动多长时间

mysqldumpslow

常用的慢查询日志分析工具（mysqldumpslow）汇总除查询条件外其他完全相同的SQL，并将分析结果按照参数中所指定的顺序输出。

-s 按照那种方式排序
c：访问计数
l：锁定时间
r:返回记录
al：平均锁定时间
ar：平均访问记录数
at：平均查询时间
-t 是top n的意思，返回多少条数据。
-g 可以跟上正则匹配模式，大小写不敏感。

demo:
得到返回记录最多的20个sql

mysqldumpslow -s r -t 20 sqlslow.log

得到平均访问次数最多的20条sql

mysqldumpslow -s ar -t 20 sqlslow.log

得到平均访问次数最多,并且里面含有ttt字符的20条sql

mysqldumpslow -s ar -t 20 -g “ttt” sqldlow.log

pt_query_digest

pt-query-digest 是用于分析mysql慢查询的一个工具，与mysqldumpshow工具相比，py-query_digest 工具的分析结果更具体，更完善。
有时因为某些原因如权限不足等，无法在服务器上记录查询。这样的限制我们也常常碰到，所以开发了两种替代的技术，都集成到了Percona Toolkit 中的 pt-query-digest 中。百度安装然后参考教程即可实现基本的使用，比mysql自带的日志分析指令更好更全面。重点看里面执行计划。

索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
mysql中提到,一个表最多16个索引,最大索引长度256字节.
索引的数据结构：

1. HASH 索引
   这个索引类似就是将我们的Key通过一个hash算法将数据映射到一个地址中去，跟Java中到HashMap的Key寻址类似，这个类型的索引只适合等值查找，对于范围性查找无能为力。
2. BTREE
   首先要了解TREE 然后延伸到BTREE，最后目前mysql的索引底层大部分都是B+Tree，这个类型的索引数据都存在叶子节点，比较适合范围查找。

索引分类：

普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：即一个索引包含多个列(索引负责最左原则)
聚簇索引：并不是一种单独的索引类型，而是一种数据存储方式。将数据存储与索引放到了一块，找到索引也就找到了数据，其实就是在同一个InnoDB的聚簇索引结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。
非聚簇索引：不是聚簇索引，将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行。

聚簇索引跟非聚簇索引

聚簇索引

我们在平时创建数据表的时候，在某些关系型数据库中，数据表只能有一个自增主键AUTO_INCREMENT。
如果一个表没有加索引，数据按顺序一条一条的在磁盘上按插入顺序存储着。
如果一张表一旦加了索引，比如加了自增主键，那么表中的数据在磁盘中就不是按顺序排列的，而是变成了树状结构，也就是我们常说的平衡树，换句话说就是整个表都变成了一个索引树，也就是所谓的聚簇索引。

这也就说明白了为什么一个表只能有一个自增主键以及只能有一个聚簇索引了，因为自增主键的作用根儿上就是根据一定算法把表的数据按照一定格式转换成平衡树存放在磁盘的。所谓聚簇索引，就是指：主索引文件和数据文件为同一份文件，目前我所了解的聚簇索引只是在Innodb存储引擎中存在。

在创建表的时候，INNODB类型的表存储的是一个文件，后缀为.frm。这个文件包含了数据、表结构、索引树。在数据结构实现方式中B+Tree的叶子节点中data就是数据本身，key为主键，如果是二级索引(自己创建的)的话，data便会指向对应的主索引。

当然任何事物都是有两面的，索引能让数据库查询数据的速度提升， 同时对表的写入数据速度就会下降，原因很简单的。 因为平衡树这个结构必须一直维持在一个正确的状态，增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构。

因此，在每次数据改变时，DBMS必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销，也就是为什么索引会给查询以外的操作带来副作用的原因。

MySQL中InnoDB 的主键一定是聚集索引。如果没有定义主键，聚集索引可能是第一个不允许为 null 的唯一索引，也有可能是 row id。

非聚簇索引：

其实他们俩差不多，同样都是使用平衡树作为索引的数据结构。

区别：非聚集索引这棵树中所有的节点都来自于表中二级索引字段。假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，在数据改变时， DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ，那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。

每次给字段建一个新索引，字段中的数据就会被复制一份出来，用于生成索引。因此，给表添加索引，会增加表的体积，占用磁盘存储空间。

非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值 ，再使用主键的值通过聚集索引查找到需要的数据，

聚集和非聚集索引不管以任何方式查询表，最终都会利用主键通过聚集索引来定位到数据，聚集索引（主键）是通往真实数据所在的唯一路径。

不过，有一种例外可以不使用聚集索引就能查询出所需要的数据这种非主流的方法称之为覆盖索引查询，也就是平时所说的复合索引或者多字段索引查询。

刚才说过了，每次给字段加一次索引，所对应的内容就会被复制出来一份。如果为一个索引指定两个字段，那么这个两个字段的内容都会被同步至索引之中。

覆盖索引可以完美的解决二级索引回表查询问题。但是前提是一定得注意查询时候索引的。最左侧匹配原则

使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。

聚集索引两点关键信息：根据主键值创建了 B+ 树结构，每个叶子节点包含了整行数据。

总的来说，在MySQL中MyIsAm使用的是B+tree索引结构，叶节点的data仅仅存放的是指向数据记录的一个地址，在MyIsAm中主键索引和辅助索引没有任何区别，只是主键索引要求key是唯一的，而辅助索引的key可以重复。

在InnoDB中使用的也是B+tree索引结构，但是在实现方式来说和MyIsAm完全不通，MyIsAm的都是分开的，MyIsAm中索引文件只数据文件和索引文件以及表定义文件是存储一个指向具体数据的一个指针。但是在innodb中，Btree可以分为两种：主键索引和二级索引（也叫辅助索引）。

innodb中主键索引一定是聚集索引，表数据文件本身就是一个B+tree结构，这个树的叶节点保存了每一条记录的完整数据，这个叶节点的key就是数据的主键，innodb中二级索引保存的是索引列值以及指向主键的指针，所以我们使用覆盖索引优化其实说白了就是对MySQL的innodb索引加速的。

覆盖索引（Covering Index）

理解方式一:就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。

理解方式二:索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。
PS：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，

总结：

MyISAM引擎中leaf node存储的内容:

主键索引 ：仅仅存储行指针；二级索引：存储的也仅仅是行指针；

InnoDB引擎中leaf node存储的内容

主键索引 ：聚集索引存储完整的数据（整行数据）二级索引：存储索引列值+主键信息

查看索引

SHOW INDEX FROM table_name\G

创建索引

CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length));
ALTER TABLE 表名 ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))

删除索引

DROP INDEX [indexName] ON mytable;# 执行计划

重点执行计划

explain sql
explain extended sql : 扩展能够在原本explain的基础上额外的提供一些查询优化的信息，
这些信息可以通过mysql的show warnings命令得到。

执行计划：SQL执行计划，就是一条SQL语句，在数据库中实际执行的时候，一步步的分别都做了什么。就是我们用EXPLAIN分析一条SQL语句时展示出来的那些信息
意义：了解SQL执行计划的意义就在于我们可以通过执行计划更加清晰的认识到这一条语句，分为了哪几步，有没有用到索引，是否有一些可优化的地方等。

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

执行计划详解

其中最重要的字段为：id、type、key()、rows、Extra

1. id 可以看到大致的执行顺序
2. type可以看到每个table执行到类型
3. key 可以看到 使用到索引
4. rows 可以看到 每个表扫描的行数
5. Extra 包含很多重要额外信息，
6. key_len 可以看出联合索引计算的是索引长度和，由此延伸判断。

具体的字段含义可看blog

重点 MySQL优化

1. 尽量全值匹配

当建立了索引列后，能在where条件中使用索引的尽量所用

2. 最佳左前缀法则

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

3. 不在索引列上做任何操作

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

4. 范围条件放最后

WHERE NAME = ‘July’ and age >22 and pos=‘manager’，会导致 age 后的索引失效。

5. 覆盖索引尽量用

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

6. 不等于要慎用

mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

7. Null/Not 有影响

注意null/not null对索引的可能影响
在字段为not null的情况下，使用is null 或 is not null 会导致索引失效，解决方式：覆盖索引

8. 策略8.Like查询要当心

like以通配符开头(’%abc…’) mysql索引失效会变成全表扫描的操作,解决方式：覆盖索引或 通配符%尽量靠后。

9. 字符类型加引号

字符串不加单引号索引失效,解决方式：请加引号

10. OR改UNION效率高

查询用union来实现，或者使用覆盖索引。注意on也是where查询一种

高性能存储

随着业务量的扩增，单机版本的mysql一般都是无法直接商用化的，要考虑数据库的可用性及性能。CRUD要快，不能忽然挂掉了。因此我们需要引入MySQL集群。
目前主要的集群方法如下：

1. MySQL Cluster：由Mysql本身提供，优势：可用性非常高，性能非常好。每份数据至少可在不同主机存一份拷贝，且冗余数据拷贝实时同步。但它的维护非常复杂，存在部分Bug，目前还不适合比较核心的线上系统，所以不推荐。
2. DRBD磁盘网络镜像方案：Distributed Replicated Block Device，其实现方式是通过网络来镜像整个设备(磁盘).它允许用户在远程机器上建立一个本地块设备的实时镜像,与心跳链接结合使用,也可看做一种网络RAID。
3. MySQL Replication：在实际应用场景中，MySQL Replication是使用最为广泛的一种提高系统扩展性的设计手段。众多的MySQL使用者通过Replication功能提升系统的扩展性后，通过简单的增加价格低廉的硬件设备成倍 甚至成数量级地提高了原有系统的性能。

MySQL复制：复制是指将主数据库的 DDL和 DML 操作通过二进制日志传到复制服务器(也叫从库)上,然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步。MysQL支持一台主库同时向多台从库进行复制,从库同时也可以作为其他服务器的主库,实现链状的复制
MySQL复制好处是：

1.如果主库出现问题,可以快速切换到从库提供服务。
2.可以在从库上执行查询操作, 降低主库的访问压力。
3. 某些数据库维护工作，比如备份，可以在从库上执行，以避免备份期间影响主库的服务

大致原理：

1. mysql 主库提交事务时候把数据变更为事件Events记录早二进制文件Binlog中，主线程Binlog Dump 负责此工作。
2. 主库要主动的推送Binlog文件到从库到RelayLog中 ，
3. 从库根据RelayLog文件到数据进行数据重做，将数据倒入到从库中。从库中有I/O线程负责连接主库来接受数据，SQL线程来负责将数据追加到从库。从库还有一两个重要日志文件来记录保存跟复制到进度(master.info,relay_log.info)
   PS: BinLog三种格式(Statement,Row,Mixed)。

MySQL复制的工作模式大概如下图：

常用复制框架有一主多从，多级复制，双主复制，双主多级复制架构。

复制过程：异步复制

复制过程：半异步复制(只是将数据同步到来从库文件)

keepalive+haproxy实现高可用

keepalived：是集群管理中保证集群高可用的一个服务软件，其功能类似于heartbeat，用来防止单点故障。

Keepalived起初是为LVS设计的，专门用来监控集群系统中各个服务节点的状态，它根据TCP/IP参考模型的第三、第四层、第五层交换机制检测每个服务节点的状态。
Keepalived又加入了VRRP的功能，VRRP（Vritrual Router Redundancy Protocol,虚拟路由冗余协议)出现的目的是解决静态路由出现的单点故障问题，通过VRRP可以实现网络不间断稳定运行
因此Keepalvied 一方面具有服务器状态检测和故障隔离功能，另外一方面也有HA cluster功。

haproxy作用：高可用性，负载平衡和用于TCP和基于http的应用程序的代理。

HAProxy 是一款提供高可用性、负载均衡以及基于TCP（第四层）和HTTP（第七层）应用的代理软件，支持虚拟主机，它是免费、快速并且可靠的一种解决方案。
HAProxy特别适用于那些负载特大的web站点，这些站点通常又需要会话保持或七层处理。
HAProxy运行在时下的硬件上，完全可以支持数以万计的 并发连接。
并且它的运行模式使得它可以很简单安全的整合进您当前的架构中， 同时可以保护你的web服务器不被暴露到网络上。

MyCat

官方统计mysql的最佳存储量一般在500W多个左右，超过了1000W后CRUD可能会收到影响，对于超大型业务此时就会涉及到分表分库。

1. 分库一般从业务层考虑将不同字段分为冷数据跟热数据(然后数据可以用途不同到存储架构如MyISAM,InnoDB)。
2. 分表一般是从技术角度考虑， 将同样字段到数据分布到不同到数据表格中 。现实中一般可能用到根据主键的Hash算法或者根据日期来分存数据(比如银行APP只提供6个月消费记录，超过了要到柜台办理)。

分库分表可以有效解决存储问题不过涉及到什么事务到时候就很棘手了。这个时候可以引入第三方中间件，可以满足数据库数据大量存储；提高了查询性能。

分库分表常用第三方产品

京东金融的ShardingSphere 网址：http://shardingsphere.io/index_zh.html
开源的Cobar和MyCat
其他第三方的中间件，比如Oneproxy 网址： http://www.onexsoft.com/

目前市面上常用到还是MyCat中间件
MyCat：是一个开源的分布式数据库系统，是一个实现了MySQL 协议的的Server，应用可以把它看作是一个数据库代理，用MySQL 客户端工具和命令行访问，而其本身可以用MySQL 原生（Native）协议与多个MySQL 服务器通信，也可以用JDBC 协议与大多数主流数据库服务器通信

拦截

Mycat 的原理中最重要的一个动词，它拦截了用户发送过来的SQL 语句，对SQL 语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此SQL 发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。

1、一个彻底开源的，面向企业应用开发的大数据库集群
2、支持事务、ACID、可以替代MySQL的加强版数据库
3、一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群
4、一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server
5、结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
6、一个新颖的数据库中间件产品

常见概念

逻辑库(schema)

通常对实际应用来说，并不需要知道中间件的存在，业务开发人员只需要知道数据库的概念，所以数据库中间件可以被看做是一个或多个数据库集群构成的逻辑库。

逻辑表（table）

既然有逻辑库，那么就会有逻辑表，分布式数据库中，对应用来说，读写数据的表就是逻辑表。逻辑表，可以是数据切分后，分布在一个或多个分片库中，也可以不做数据切分，不分片，只有一个表构成。

分片表

分片表，是指那些原有的很大数据的表，需要切分到多个数据库的表，这样，每个分片都有一部分数据，所有分片构成了完整的数据。

非分片表

一个数据库中并不是所有的表都很大，某些表是可以不用进行切分的，非分片是相对分片表来说的，就是那些不需要进行数据切分的表。

ER 表

关系型数据库是基于实体关系模型（Entity-Relationship Model)之上，通过其描述了真实世界中事物与关系，Mycat 中的ER 表即是来源于此。根据这一思路，提出了基于E-R 关系的数据分片策略，子表的记录与所关联的父表记录存放在同一个数据分片上，即子表依赖于父表，通过表分组（Table Group）保证数据Join 不会跨库操作。表分组（Table Group）是解决跨分片数据join 的一种很好的思路，也是数据切分规划的重要一条规则。

全局表

一个真实的业务系统中，往往存在大量的类似字典表的表，这些表基本上很少变动，字典表具有以下几个特性：

• 变动不频繁；
• 数据量总体变化不大；
• 数据规模不大，很少有超过数十万条记录。

对于这类的表，在分片的情况下，当业务表因为规模而进行分片以后，业务表与这些附属的字典表之间的关联，就成了比较棘手的问题，所以Mycat 中通过数据冗余来解决这类表的join，即所有的分片都有一份数据的拷贝，所有将字典表或者符合字典表特性的一些表定义为全局表。
数据冗余是解决跨分片数据join 的一种很好的思路，也是数据切分规划的另外一条重要规则。

分片节点(dataNode)

数据切分后，一个大表被分到不同的分片数据库上面，每个表分片所在的数据库就是分片节点（dataNode）。

节点主机(dataHost)

数据切分后，每个分片节点（dataNode）不一定都会独占一台机器，同一机器上面可以有多个分片数据库，这样一个或多个分片节点（dataNode）所在的机器就是节点主机（dataHost）,为了规避单节点主机并发数限制，尽量将读写压力高的分片节点（dataNode）均衡的放在不同的节点主机（dataHost）。

分片规则(rule)

前面讲了数据切分，一个大表被分成若干个分片表，就需要一定的规则，这样按照某种业务规则把数据分到某个分片的规则就是分片规则，数据切分选择合适的分片规则非常重要，将极大的避免后续数据处理的难度。

全局序列号(sequence)

数据切分后，原有的关系数据库中的主键约束在分布式条件下将无法使用，因此需要引入外部机制保证数据唯一性标识，这种保证全局性的数据唯一标识的机制就是全局序列号（sequence）

参考

详细聊聊MySQL中 聚簇、非聚簇索引和覆盖索引
负载均衡工具LVS讲解
mysql知识点
explain
mysq优化
keepalive+haproxy实现
分布式数据库架构及企业实践
MyCat笔记