mysql or 索引_MySql那些事

                          今天很热，要有冰西瓜和空调的那种...

一、MySql底层为什么使用B+树？

B+树相比B树，新增叶子节点和非叶子节点关系，叶子节点中包含了key和value，非叶子节点中只包含了key，不包含value。

所有相邻的叶子节点包含非叶子节点，使用链表进行结合，有一定顺序性，从而范围查询效率非常高。

MyISAM和InnoDB是对B+树索引不同的实现方式。

MyISAM底层使用B+树，叶子节点的value对应存放行数的地址，再通过行数定位到数据。

InnoDB底层使用B+树，叶子节点的value对应存放的是行的data数据，相比MyISAM效率要高，但是比较占内存。

二、索引文件对应的存放位置

默认数据与索引文件位置：/var/lib/mysql

MyISAM引擎的文件：

.myd 即my data，表数据文件。

.myi  即my index，索引文件。

.log   日志文件。

InnoDB引擎的文件：

采用表空间(tablespace)来管理数据，存储表数据和索引。

InnoDB数据库文件(即InnoDB文件集，ib-file set)：

ibdata1、ibdata2等：系统表空间文件，存储InnoDB系统信息和用户数据库表数据和索引，所有表共用。

.ibd文件：单表表空间文件，每个表使用一个表空间文件，存放用户数据库表数据和索引。

三、慢查询

slow_query_log	慢查询开启状态
slow_query_log_file	慢查询日志存放的位置(一般为MySQL的数据存放目录)
long_query_time	查询超过多少秒才记录

show variables like 'slow_query%';	查询慢查询配置
set global slow_query_log = 'on';	将slow_query_log 全局设置为“on”状态
set global long_query_time = 1;	查询查过1秒就记录
cat /var/lib/mysql/localhost-slow.log	查看慢查询日志

四、查看执行计划

type的几种类型

由上至下，效率越来越高

all	全表扫描
index	索引全扫描
range	索引范围扫描，常用于=,between等操作
ref	使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq-ref	类似ref，区别在于使用的是唯一索引，使用主键的关联查询
const/system	单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
null	MySQL不访问任何表或索引，直接返回结果

五、索引失效

索引为啥会失效呢？索引失效的几种场景：

1、索引无法存储null值

2、如果条件中有or，即使其中有条件带索引整体也不会使用索引(这也是为什么尽量少用or的原因)想要使用or，又不想索引失效，那么就得在or查询的每个条件都加上索引。

3、对于多列索引，如果第一列没有使用索引，则不会使用索引。

4、like查询以%开头

5、如果列类型是字符串，则一定要在查询中使用引号，否则不会使用索引。

6、如果MySQL估计使用全表扫描要比使用索引查询快，也不会使用索引。

联合索引，左前缀原则

六、分库分表

第三方中间件：MyCat 或 Sharding-jdbc

使用分库分表提高数据库查询效率：将一张表的数据拆分成n张表进行存储，以后在使用第三方中间件的时候并行同时查询，然后交给中间件进行组合返回给客户端。

MyCat 和 Sharding-Jdbc的区别：

1、MyCat是一个基于第三方应用中间件数据库代理框架，客户端所有的JDBC请求都得交给MyCat，再由MyCat转发到真实的服务器中；

2、Sharding-JDBC是一个Jar的形式，在本地应用层重写Jdbc原生的方法，实现数据库分片的形式。

3、MyCat属于服务器端数据库中间件，而Sharding-JDBC是一个本地数据库中间件框架。

4、主要流程都是：SQL解析-> SQL路由 -> SQL改写 -> SQL执行 -> 结果合并。但架构设计是不同的，MyCat是基于Proxy，它复写了MySQL协议，将MyCat伪装成一个MySQL数据库，而Sharding-JDBC是基于JDBC的扩展，是以jar包的形式提供轻量级服务的。

类似于 SpringCloud Ribbon 和 Nginx 区别。

七、主从复制

通过主从复制可以实现数据备份、故障转移、MySQL集群、高可用、读写分离等。

MySQL的主从复制是MySQL自带的一个功能，不需要额外的第三方软件就能实现，借助binlog日志文件中的SQL命令实现主从复制，可以理解为在Master执行了一条SQL命令，那么在Salve端同样也会执行一遍，从而达到主从复制的效果。

主服务器节点：

1、开启日志文件

2、重启MySQL服务：

service mysqId restart

3、验证：

show variables like '%server_id%'	能够查询到对配置文件中的server_id
show master status	能够看到同步的文件和行数

从服务器节点：

1、从服务器同步主节点配置：

change master to master_host ='127.0.0.1' , master_user='root', master_log_file = 'mysql-bin.00001', master_log_pos = 216;

2、开始同步

start slave

3、检查从服务器复制功能状态

show slave status