MySQL学习笔记

索引的使用：

优化的本质是减少IO次数

btree索引应该尽可能使用数字，因为非数字最后都会变成ASCII码存储

回表查询会使性能下降

使用索引查询结果的数据量应占全表的30%以下，才建议使用索引。否则性能可能不如普通查询

优化器分析(相当于explain的执行过程信息和依据)：

TRACE: QUERY查询语句

1，重写查询语句，优化字段和字段别名

2，对where/having 对象进行 常量转换、去除无意义查询(如1=1)、类型转换(如'1'转为1)

3，记录单个表或多个表的查询扫描的数据量

4，记录使用哪个索引

explain分析sql执行逻辑过程，从而进行优化

 

索引优化使用：

1，where和order、group(？) 都需要创建索引，最左边的字段值尽可能唯一不重复或少重复

判断方法：

select

count(distinct yearbonus)/ count(*) as year_select,

count(distinct monthsalary)/ count(*) as mon_select,

count(distinct TIMESTAMPDIFF(YEAR, birthdate, CURDATE())) / count(*) as bir_select,

count(*) as counts

from customers1s;

数值最大的字段，就是出重复后占比总数据量最大的字段，也就是适合放在最左边的字段。按数值大小从左到右制作联合索引

2，根据sql语句建立索引，索引的先后顺序会影响sql语句的执行效率

3，多条sql语句要相互结合来设计组合索引的字段和顺序

4，group 和 order 语法使用时，需要和id组成联合索引来快速查询并解决数据不一致的问题

？？？

函数索引【虚拟字段】

要求：

1，

？？？

大数据统计问题

使用统计表记录统计数据

1，定时任务进行定时统计

2，redis消息队列

超卖问题

1，MySQL锁

    a,mvcc 乐观锁

    b,悲观锁

2，redis队列

单表优化：

索引优化、拆表优化(垂直、水平)、砍需求

join多表优化

1，join_buff(默认256kb) 块设置 尽可能的大

2，小的数据表去驱动大的数据表

    a，表的大小

    b，根据条件看扫描的数据量

    c，条件和关联字段会影响性能(排序？)

3，关联字段尽可能是索引字段

4，建议联表的数量小于等于3张表

5.6开始对子查询优化，把子查询当成join去查询。同时也对max和group优化

 

数据库设计：

三大范式

1，原子性

2，依赖于主键

3，主意明确(每张表的主题/主要内容明确，不会有多个主要内容。如：订单表主要存储订单数据，而下单产品数据和下单客户数据必须存在其他的表数据上)

反范式：目的是为了减少join次数

字符串类型：

存储

char与varchar

char(10) 255字符       存储5个字符 空格补充

varchar(10) 65535字符  存储5个字符 无空格补充

取数据

char 会自动清除空搞

varchar 不会清除空搞

uft8 1个字符 占3个字节

gbk  1个字符 占2个字节

char超出会报错，而varchar超出不会报错直接截取

text不建议使用，数据太大不利于索引扫描

小说存储是用txt存储数据，用varchar存储路径

索引扫描的效率 char > varchar > text

char 适合存储 手机号码、密码、md5或hash值。固定长度的

varchar 适合存储 name email

text字段要和主表分离，单独的表进行存储，预防制作索引的时候影响索引扫描效率

数据库设计

冷热数据拆分、长字段数据拆分

不推荐存储的数据

1，二进制、长文本、音频数据

2，队列任务处理比较频繁：日志记录，

3，超文本

 

15-主从复制

主-写-数据 从-读-数据库 从-实时备份-数据库

问题：

1.如果从服务器的数据库不存在怎么办

备份主库数据导入到从库

2.数据不一致怎么办

使用中间件，进行数据校验

备份主库的数据

备份的原因：主从复制保持一致

1，冷备份（逻辑备份：导出导入）

    mysqldump（MySQL自带）

    shell语句

    mydumper（第三方插件，本地情况下，比自带的工具更快）

   

    需要锁表锁库后进行备份

2，热备份

付费：ImnoDB Hot Backup

免费：xtrabackup 查看版本，版本不对应话不成功（最低支持版本MySQL8.0？）

3，暴力备份

备份：停止MySQL，直接压缩打包MySQL的data目录，启动MySQL

还原：停止MySQL，直接解压覆盖MySQL的data目录，启动MySQL

 

16-主从复制问题

主从复制用途：

1，读写分离

2，数据实时备份，主数据库崩坏或死机断电时，方便切换

3，高可用HA

4，架构扩展

搭建：

1，主节点配置

    开启binlog

    看看主节点的server_id，主从server_id不能重复

    创建一个用户主从复制的用户。不能使用root用户，不安全、IP限制、

    找到中继日志

 

2，从节点配置

    配置需要同步的主节点的host、port、user、password、中继日志文件名、日志节点

    开启主从

    查看启动是否成功。如果不存在，则排查问题（如防火墙、安全组、是否开放端口）

 

主从复制一致性校验？？

17-mysql负载均衡集群

    主从复制复习

        1. 主库开启binlog日志

        在配置文件中进行配置：

            log-bin=mysql-bin

            binlog_format=mixed

            server-id = 1

        2. 创建一个用于主从复制的用户

            Create user `slave`@`%` identified by “root”;

            Grant all on *.* to `slave`@`%` with grant option;

        3. 从库开启中继日志

            通过show variables like “%relay%”;查看

        4. 配置主从

            change master to master_host='192.168.29.102',master_port=3306,master_user='slave',master_password=‘root',master_log_file='mysqlbin.000002',master_log_pos=155;

        5. 注意事项

            服务uuid不可重复(虚拟机克隆的时候会出现)

                cat /www/server/data/auto.cnf

            Mysql server_id不可重复

            主库防火墙需要关闭，如果是服务器请开启安全组端口

            主从用户权限

       

    负载均衡

        是什么：

            在一个服务器集群中尽可能地的平均负载量。

            基于这个思路，我们通常的做法是在服务器前端设置一个负载均衡器。负载均衡器的作用是将请求的连接路由到最空闲的可用服务器上

        演化：

            单台数据库-》读写分离-》负载均衡

        服务选型：

            haproxy

                供高可用性、负载均衡以及基于TCP和HTTP应用的代理，支持虚拟主机，它是免费、快速并且可靠的一种解决方案

                HAProxy 特别适用于那些负载特大的 web 站点，这些站点通常又需要会话保持或七层处理。

                HAProxy 运行在当前的硬件上，完全可以支持数以万计的并发连接。并且它的运行模式使得它可以很简单安全的整合进您当前的架构中， 同时可以保护你的 web 服务器不被暴露到网络上。

                HAProxy 实现了一种事件驱动，单一进程模型，此模型支持非常大的并发连接数。多进程或多线程模型受内存限制 、系统调度器限制以及无处不在的锁限制，很少能处理数千并发连接。事件驱动模型因为在有更好的资源和时间管理的用户空间 (User-Space) 实现所有这些任务，所以没有这些问题。此模型的弊端是，在多核系统上，这些程序通常扩展性较差。这就是为什么他们必须进行优化以使每个 CPU 时间片 Cycle 做更多的工作。

                相较与 Nginx，HAProxy 更专注与反向代理，因此它可以支持更多的选项，更精细的控制，更多的健康状态检测机制和负载均衡算法。

                包括 GitHub、Bitbucket、Stack Overflow、Reddit、Tumblr、Twitter 和 Tuenti 在内的知名网站，及亚马逊网络服务系统都使用了 HAProxy。

                带可视化界面 ip:1080/haproxyadmin?stats 账号密码：admin admin

        安装

        启动

            ./usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg

        测试

            mysql -h192.168.1.1 -uroot -p123456 -P3307 -e "show variables like 'server_id'"

            # 这里的3306是mysql的端口号，1080是haproxy的端口号

    高可用

        Keepalived简介

            Keepalived的作用是检测服务器的状态，如果有一台服务器宕机，或工作出现故障，Keepalived将检测到，并将有故障的服务器从系统中剔除，同时使用其它服务器代替该服务器的工作，当服务器工作 正常后Keepalived自动将服务器加入到服务器群中，这些工作全部自动完成，不需要人工干涉，需要人工做的只是修复故障的服务器。

            本质上来说就是，两台服务设备或服务软件同时出现问题的概率远远小于一台服务器出问题的概率

       

        两个从库服务器都搭建Keepalived，Keepalived分主从，主的权重比从的高，权重高的拿到虚拟ip

    实践和设计架构图

16-主从复制问题

    主从复制用途：

        1，读写分离

        2，数据实时备份，主数据库崩坏或死机断电时，方便切换

        3，高可用HA

        4，架构扩展

   

    主从复制搭建：

        1，主节点配置

            开启binlog

            看看主节点的server_id，主从server_id不能重复

            创建一个用户主从复制的用户。不能使用root用户，不安全、IP限制、

            找到中继日志

   

        2，从节点配置

                配置需要同步的主节点的host、port、user、password、中继日志文件名、日志节点

                开启主从

                查看启动是否成功。如果不存在，则排查问题（如防火墙、安全组、是否开放端口）

     主从复制一致性校验

        percona-toolkit组合工具集合：

            1，pt-table-checksum 检测

            2，pt-table-sync修复不一致的数据

            3，pt-heartbeat 监控同步延迟

       

        主库安装：

            [root@localhost ~]# yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl perl-DBI -y

            [root@localhost home]# yum localinstall percona-toolkit-3.2.1-1.el7.x86_64.rpm

            [root@localhost home]# yum list | grep percona-toolkit

        使用：

        pt-table-checksum的使用：

            pt-table-checksum [options] [dsn]

            pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --databases=test --tables=t --user=mytest --password=rot

        部分参数解释：

            --nocheck-replication-filters ：不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库。

            --no-check-binlog-format : 不检查复制的binlog模式，要是binlog模式是ROW，则会报错。

            --replicate-check-only :只显示不同步的信息。

            --replicate= ：把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。

            --databases= ：指定需要被检查的数据库，多个则用逗号隔开。

            --tables= ：指定需要被检查的表，多个用逗号隔开

            --host | h= ：Master的地址

            --user | u= ：用户名

            --passwork | p=：密码

            --Post | P= ：端口

        注意：关闭防火墙、取消检查过滤器和binlog模式，注意不同MySQL数据库和工具的兼容性，推荐使用MySQL8.0

        pt-table-sync的使用

            pt-table-sync [options] dsn [dsn]

            pt-table-sync --sync-to-master h=192.168.29.103,u=mytest,p=rot,P=3306 --databases=mytest --print

        部分参数解释：

            --replicate= ：指定通过pt-table-checksum得到的表，这2个工具差不多都会一直用。

            --databases= : 指定执行同步的数据库，多个用逗号隔开。

            --tables= ：指定执行同步的表，多个用逗号隔开。

            --sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。

            h=127.0.0.1 ：服务器地址，命令里有2个ip，第一次出现的是Master的地址，第2次是Slave的地址。

            u=root ：帐号。

            p=123456 ：密码。

            --print ：打印，但不执行命令。

            --execute ：执行命令

        建议:

            修复数据的时候，用--print打印出来，这样就可以知道那些数据有问题

            修复数据之前一定要备份数据库 ； 然后再 手动执行或者 添加 --execute

        制作定时任务脚本：

            #!/usr/bin/env bash

            NUM=`pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --databases=mytest --tables=t --user=mytest --password=rot | awk 'NR>1{sum+=$3}END{print sum}'`

            if [ $NUM -eq 0 ] ;then

            echo "Data is ok!"

            else

            echo "Data is error!"

            pt-table-sync --sync-to-master h=192.168.29.103,u=mytest,p=rot,P=3306 --databases=mytest --print

            pt-table-sync --sync-to-master h=192.168.29.103,u=mytest,p=rot,P=3306 --databases=mytest --execute

            pt-table-sync --sync-to-master h=192.168.29.104,u=mytest,p=rot,P=3306 --databases=mytest --print

            pt-table-sync --sync-to-master h=192.168.29.104,u=mytest,p=rot,P=3306 --databases=mytest --execute

            fi

       

        定时任务设置：

            20 23 * * * /home/pt-check-sync.sh

            表示每天晚上23:20运行这个脚本

        延迟问题：

            主从延迟和网络因素、服务器配置、服务器状态有关，没办法直接解决，只能监控和尽量减少从服务器的写入速度

        原理：

            在percona toolkit 产品中也提供了可以对于MySQL主从延时检查的工具pt-heartbeat, pt-heartbeat 的工作原理是通过使用时间戳方式在主库上更新特定表，然后再从库上读取呗更新的时间戳然后与本地系统时间对比来得出其延迟。

        延迟处理：

            1.MySQL从库产生配置

                1.1 网络

                1.2 sync_binlog参数配置

                    sync_binlog 配置说明：

                    sync_binlog”：这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下： sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者 cache满了之后才同步到磁盘。 sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

                    在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。

                    从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

                1.3 innodb_flush_log_at_trx_commit参数

                    innodb_flush_log_at_trx_commit 配置说明： 默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。 设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘，所以你一般不会丢失超 过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。

            2. 硬件

                从库的性能要比主库的性能要高一些，从而缓解延迟问题

            3. 架构优化

                3.1. 可以考虑对于一些库进行单独分离。

                3.2. 服务的基础架构在业务和MySQL之间加入memcache或者redis的cache层。

                3. 3. 从库的配置要好