Mysql优化

查看mysql支持的 存储引擎
show engines;

如果我们需要查找id=12的用户信息，利用我们创建的二叉查找树索引，查找流程如下： 

1. 将根节点作为当前节点，把12与当前节点的键值10比较，12大于10，接下来我们把当前节点>的右子节点作为当前节点。 

2. 继续把12和当前节点的键值13比较，发现12小于13，把当前节点的左子节点作为当前节点。 

3. 把12和当前节点的键值12对比，12等于12，满足条件，我们从当前节点中取出data，即id=12,name=xm。

利用二叉查找树我们只需要3次即可找到匹配的数据。如果在表中一条条的查找的话，我们需要6次才能找到。

在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过1。 

从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。 

如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。 

如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，我们都知道平衡二叉树可是每个节点只存储一个键值和数据的。


说明每个磁盘块仅仅存储一个键值和数据！

可以想象到二叉树的节点将会非常多，高度也会及其高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低！

B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。


假如我们要查找id=28的用户信息，那么我们在上图B树中查找的流程如下： 

1. 先找到根节点也就是页1，判断28在键值17和35之间，我们那么我们根据页1中的指针p2找到页3。 

2. 将28和页3中的键值相比较，28在26和30之间，我们根据页3中的指针p2找到页8。 

3. 将28和页8中的键值相比较，发现有匹配的键值28，键值28对应的用户信息为(28,bv)。

1.B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。之所以这么做是因为在数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。另外，B+树的阶数是等于键值的数量的，如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000×1000=10亿个数据。一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘IO。 

2.因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。

1. 聚集索引（聚簇索引）：以innodb作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B+树中的，而B+树的键值就是主键，在B+树的叶子节点中，存储了表中所有的数据。这种以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引。 

2. 非聚集索引（非聚簇索引）：以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引。非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。  
明白了聚集索引和非聚集索引的定义，我们应该明白这样一句话：数据即索引，索引即数据。

用户写数据只往master节点写，而读的请求分摊到各个slave节点上

binlog是实现MySQL主从复制功能的核心组件。master节点会将所有的写操作记录到binlog中，slave节点会有专门的I/O线程读取master节点的binlog，将写操作同步到当前所在的slave节点。

查看  开启 binlog 

show variables like 'log_bin';

查看binlog日志存放的位置

show global variables like '%log%';

查看binlog 日志内容

show binlog events in 'binlog.000016' ;

主库 配置


#主服务器唯一Id[必填]
server-id=1
#启用二进制日志[必填]
#log-bin=mysql-bin 
log-bin=D:\work\application\mysql-8.0.27-winx64\data\mysql-bin     #开启mysql的binlog日志功能
#主机，读写都可以
read-only=0
#设置不要复制的数据库[可选]
#binlog-ignore-db=mysql
#设置需要复制的数据库[可选]（输数据库名字） 
#binlog-do-db=dlj


sync_binlog = 1         #控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
binlog_format = mixed   #binlog日志格式，mysql默认采用statement，建议使用mixed
expire_logs_days = 7                           #binlog过期清理时间
max_binlog_size = 100m                    #binlog每个日志文件大小
binlog_cache_size = 4m                        #binlog缓存大小
max_binlog_cache_size= 512m              #最大binlog缓存大
binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行
auto-increment-offset = 1     # 自增值的偏移量
auto-increment-increment = 1  # 自增值的自增量

从库配置
#主从复制配置 
#从服务器唯一Id 
server-id=2 
#只读，对拥有super权限的账号是不生效的 
read_only = 1


log-bin=D:\mysql-8.0.27-winx64\data\mysql-bin

创建并授权用来做复制的用户

create user 'liushuai'@'10.133.46.114' IDENTIFIED by 'liushuai';

ALTER USER 'liushuai'@'10.133.46.114' IDENTIFIED WITH mysql_native_password BY 'liushuai';

show grants for 'liushuai'@'10.133.46.114';

GRANT REPLICATION SLAVE ON *.* TO 'liushuai'@'10.133.46.114';

select * from  user   where user='liushuai'

update user set host='%' where user='root';

select * from user root;

flush privileges;

show master status;


create table user33(
    id   VARCHAR(2),
    name VARCHAR(2),
    age VARCHAR(2)
)


CREATE DATABASE demo5;

从库 执行sql


MASTER_HOST ：master主机名(或IP地址)
MASTER_PORT ：mysql实例端口号
MASTER_USER：用户名
MASTER_PASSWORD：密码
MASTER_AUTO_POSITION：如果进行change master to时使用MASTER_AUTO_POSITION = 1，slave连接master将使用基于GTID的复制协议
MASTER_RETRY_COUNT：重连次数
MASTER_HEARTBEAT_PERIOD：复制心跳的周期

CHANGE MASTER TO MASTER_HOST='10.133.47.103', MASTER_PORT=3306, MASTER_USER='liushuai', MASTER_PASSWORD='liushuai', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=1176 ,MASTER_RETRY_COUNT = 60,MASTER_HEARTBEAT_PERIOD = 10000;


start slave;

stop slave;

reset master;

show slave status;

set global sql_slave_skip_counter =1;

服务端的最大连接数

show variables like 'max_connections';

set GLOBAL max_connections=200



查询ip  连接数 状态
SELECT substring_index(host, ':',1) AS host_name, state, count(*) FROM information_schema.processlist GROUP BY state, host_name;


过一段时间释放不活动的连接
show variables like 'wait_timeout';

应用侧与mysql底层的连接，是基于TCP协议的长链接，而TCP协议，需要经过三次握手和四次挥手来实现建连和释放。如果我每次执行sql都重新建立一个新的连接的话，那就要不断握手和挥手，这很耗时。所以一般会建立一个长连接池，连接用完之后，塞到连接池里，下次要执行sql的时候，再从里面捞一条连接出来用，非常环保。