关系型数据库的优化+Mysql主从同步

港控/mmm° 2022-01-26 00:19 216阅读 0赞

# 为什么要实现数据库的优化？ #

我们在平时项目中发送sql，是从数据库中获取数据，如果是慢sql，就会导致性能降低。而现在用户对项目的请求效率要求是非常高的，并且所有数据放在同一个服务器上面，可能会导致一个相互的影响。

# 优化的思想 #

先考虑单机，然后再考虑多机

单机：
           1：慢sql的定位(mysql有日志记录)--》分析--》解决
           2：表的设计，索引，引擎
           		合适的表结构3NF和反3NF（比如：避免null的查询；适当的冗余字段，避免多表查询导致的效率降低）
           		合适引擎（MyISAM不支持事务，可以设置在多查的表中；Innodb支持事务，可以设置在多增删改的表中）
    			合适的索引：（一般是对列的索引，索引会存放在磁盘上面，以空间换取时间）
    					普通索引；唯一索引；主键索引，全文索引(一般不用，使用es替代)
    					较频繁的作为查询条件字段应该创建索引
    					唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
    					更新非常频繁的字段不适合创建索引
           3：分表分区分库
           		水平分表：一张表数据太多，且经常检索，可以考虑水平分表，将一张表中的数据等分成几份。
           				时间分表
           				区间分表
           				hash分表
           		垂直分表：表中的数据不多，但是字段很长，数据库在检索数据的时候，会执行大量的I/O
           4：缓存
         多机：集群，读写分离(主从同步)
         	1：集群：是让更多的服务器提供服务
         	2：读写分离采用28原则，2台提供写，8台提供读

# 慢查询的定位和分析 #

定位：
    	查询慢查询次数:   show status like 'slow_queries'
    	查看和修改慢查询时间阈值:   
    			show variables like ‘long_query_time’ ; //可以显示当前慢查询时间
    			set long_query_time=1 ;//可以修改慢查询时间
    	把慢查询记录到日志中:
    			bin\mysqld.exe --safe-mode  --slow-query-log [mysql5.5 可以在my.ini指定]
    			mysqld.exe --safe-mode  --slow-query-log
    	此时就可以在日志中获取慢查询的sql，可以对应进行优化，即可
    分析：
    	type：  all类似于全表查询，性能差
    解决：
    	① 使用反三范式。目的是少发复杂sql。
    		并直接在数据库中设计触发器，只发一条sql。当满足条件，触发事件。
    			什么是触发器？
    					触发器效率很高。
    	② 数据库存储引擎的选择。 MyISAM  Innodb....
    	③ 创建索引。
    			（以空间换取时间）
    			索引是创建在列上的。只是一种数据结构，用于查询性能的，但是增删改相对慢，因为需要更新索引。
    			分类：	普通索引、唯一索引、主键索引、全文索引

集群个分布式，解决IO的瓶颈
    
    分表分库：
    动态的添加服务器，实现高扩展性和可用性。
    尽力避免单点故障
    	ss
    	如何定位慢sql？
    			
    	分析为什么慢sql慢？
    	优化慢sql
    		①单机优化
    				数据库的表设计
    				读写分离的28原则
    		②多机优化

定位慢SQL

如何定位慢SQL：
    			数据库的操作都是发送sql语句，数据库都会记录语句、耗时、状态等等
    			数据库获取执行时间长的sql，就定位其为慢SQL，我们需要分析为什么SQL语句慢。
    			时间的长短的标准：
    					
    查看mysql的启动配置文件: 安装路径下的  my.ini
    		安安
    正式上线之后，不能开启慢sql的查询，因为mysql或对慢sql记录写入到磁盘内，会影响性能。

# MySQL的主从同步 #

使用的是mysql5.6，不同的版本，可能有稍微区别，比如配置文件这些，但是主从同步的思路是一致的。

1：找到自己的mysql：在cmd中输入services.msc,打开服务，找到mysql这个服务：
      "C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin\mysqld" 
      --defaults-file="C:\Program Files (x86)\MySQL\MySQL Server 5.5\my.ini" MySQL
      2：准备两个mysql服务器：
        2.1：master：
            复制C:\Program Files (x86)\MySQL\MySQL Server 5.5一份，重命名：msyqlMaster;
    	打开配置文件：my.ini，找到你的路径的配置：datadir="C:/ProgramData/MySQL/mysqlMaster/Data/"
    	找到mysql的数据存在哪里：把这个Data也复制一份。重命名msyqlMaster;
    	修改配置文件：my.ini
    	   
    		# The TCP/IP Port the MySQL Server will listen on
    		port=3307  #端口
    
    		#Path to installation directory. All paths are usually resolved relative to this.
    		basedir="C:/Program Files (x86)/MySQL/mysqlMaster/"
    
    		#Path to the database root  数据路径
    		datadir="C:/ProgramData/MySQL/mysqlMaster/Data/"
    
                    #日志文件：如果没有这个文件，自己先创建一些
    		log="C:/ProgramData/MySQL/mysqlMaster/log/master.log"
    		#唯一就行
    		server_id=2
    		 #二进制的日志文件
    		log-bin=mysql-bin
    	注册成一个服务：
    	  打开cmd，使用管理员打开；进入到C:\Program Files (x86)\MySQL\mysqlMaster\bin路径：
    	   执行命令安装服务：  mysqld --install mysqlmaster --defaults-file="C:\Program Files (x86)\MySQL\mysqlMaster\my.ini";
    	进入服务启动这个服务：在Navicat中使用3307端口连接测试==》成功
        2.2：slave：
           复制C:\Program Files (x86)\MySQL\MySQL Server 5.5一份，重命名：msyqlSlave;
    	打开配置文件：my.ini，找到你的路径的配置：datadir="C:/ProgramData/MySQL/mysqlSlave/Data/"
    	找到mysql的数据存在哪里：把这个Data也复制一份。重命名msyqlSlave;
    	修改配置文件：my.ini
    	   
    		# The TCP/IP Port the MySQL Server will listen on
    		port=3308  #端口
    
    		#Path to installation directory. All paths are usually resolved relative to this.
    		basedir="C:/Program Files (x86)/MySQL/mysqlSlave/"
    
    		#Path to the database root  数据路径
    		datadir="C:/ProgramData/MySQL/mysqlSlave/Data/"
    
                    #日志文件：如果没有这个文件，自己先创建一些
    		log="C:/ProgramData/MySQL/mysqlSlave/log/slave.log"
    		#唯一就行
    		server_id=12
    		 #二进制的日志文件
    		log-bin=mysql-bin
    	注册成一个服务：
    	  打开cmd，使用管理员打开；进入到C:\Program Files (x86)\MySQL\mysqlSlave\bin路径：
    	   执行命令安装服务：  mysqld --install mysqlmaster --defaults-file="C:\Program Files (x86)\MySQL\mysqlSlave\my.ini";
    	进入服务启动这个服务：在Navicat中使用3307端口连接测试==》成功
    	
    	
      3:授权：
        在master中：分别执行命令：
         GRANT REPLICATION SLAVE ON *.* to 'wbtest'@'172.16.7.254'identified  by 'admin';
    
    　   FLUSH PRIVILEGES;
         show master status;
    
        在slave中：分别执行：
           
    	stop slave;
    
    	change master to
    			   master_host='172.16.7.254',   #master IP
    			   master_user='wbtest',          #master数据库通过GRANT授权的账号
    			   master_password='admin',         #master数据库通过GRANT授权的密码
    			   master_port=3307,              #master数据库的密码
    			   master_log_file='mysql-bin.000006', 
    				      #master数据库中通过show master status显示的File名称
    			   master_log_pos=378    
    
    	start slave;
    
    	show slave status;
    
    	看到两个yes就ok；
     4：测试：
       在master中写数据，同步到slave中