为什么要实现数据库的优化?
我们在平时项目中发送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中