关于Mysql的一些优化操作
自己总结了一些Mysql的相关优化,跟大家分享一下
主从复制
在一主多从的数据库体系中,多个服务器采用异步的方式更新主数据库的变化,业务在执行写或者相关修改数据库的操作是在主服务器上进行的,读操作主要是在各从服务器上进行
Mysql之间主从复制的基础是二进制日志文件(binary log file),一台Mysql数据库一旦启用二进制日志后,其作为master,他的数据库中所有的操作都会以“事件”的形式记录在二进制日志文件中,其他的数据库作为slave通过一个I/O线程与主服务器保持通信,并监控master的二进制日志文件的变化,如果发现二进制日志文件有变化,则会把变化复制到自己的中继日志文件中,然后通过salve的一个SQL线程会把相关的“事件”执行到自己的数据库中,这样就达到了主从复制,读写分离
表分区
可以把一张数据量很大的表分为多个物理子表,在开发中可以通过项目模块来进行分区,SQL语句还是原来那样写的,只不过是表中的数据分在不同的机器上,这样做就可以达到优化查询的速度,还可以对某一模块数据进行备份、数据恢复等处理,对数据的维护也变得很方便,可以对某一个分区的数据进行独立的操作。
SELECT * FROM t PARTITION(p0,p1)WHERE c <5
这就会查询p0和p1区域的符合条件的数据,不会查询t表的其他区域
凭借在多个磁盘上传输数据,实现更高的查询吞吐量
表分区也有一些缺点
# 1.分区无法使用外键约束
# 2.一个表最多能有1024个分区
# 3.所有的分区必须使用相同的存储引擎,有些存储引擎并不支持分区
# 4.分区建设计不太灵活,如果不走分区键,很容易出现全表锁,一旦并发量上来,如果在分区表中实施关联,这个就是个灾难
# 5.也有可能跨区查询效率更低
分表
分表又分为水平分表和垂直分表,一下就是两种方式的区别
水平分表
为了解决单表数据量很大的情况,一般可以通过表中的时间字段在某一时刻进行分表,这样做确实可以减少单表的数据量,但是如果要查询所有的数据,就要对所有的数据执行union操作,这样对数据库的压力就很大
垂直分表
它是根据列名来进行分表,把一些常用到的字段+主键分成一个表,另外的不常用到的字段+主键分带另外一个表中。比如说一个user表中有50个字段,可以把username、pwd、age、nickname、address等常用的字段+主键分为一个表,另外的字段+主键分为另外一个表。这样也有一些缺点,这就需要维护管理冗余列,查询所有的数据是就要使用join操作。
SQL语句优化
# 1.“*”这个符号就不用说了,是个通配符,可以把表中的所有字段数据都查出来
# 2. 对查询进行优化,就要避免全表扫描,首先考虑在where和order by后面加上索引
# 3. 尽量避免在where语句后面加"!="和"<>"操作符号,这会导致索引失效,从而进行全表扫描
# 4. 尽量避免在where语句中使用“or”来连接条件。这也会导致索引失效进行全表扫描,可以使用union All来代替,比如
select * from t where id=1 or id=2
代替为
select * from t where id=1
union all
select * from t where id=2
如果想要给数据去重可以使用union关键字,如果确定没有重复的数据或者数据不需要去重,最好不要用union,因为 union会先查询到数据结果再进行去重的操作,相对于union来说比较耗时
# 5.“in”和“not in”关键字也要慎用,也会进行全表扫描
select* from t where id in(1,2,3)
可以替换为
select * from t where id between 1 and 3
有时候exists也是很好的选择
select * from t where exists(select id from t)
exists 关键字不会是索引失效
# 6.在查询表的关联操作的时候,最好用数据小的表来查询大表,如果a表数据10条,b表数据十万条
select * from a inner join b on(a.id=b.id)
这样做表只需要查询10次就会出结果,如果b表在前就会查询十万次,这查询的效率就很明显了
# 7. 当查询一个表里的数量是不要使用
select count(*) from a
替换为
select count(1)from a 或 select count(0)from a
# 8.SQL语句最好全部大写,你写好的sql语句在执行的时候会先转化为大写然后再执行,如果你把SQL语句大写就省略了 一个转化为大写的过程,效率会高一点
# 9.可以使用存储过程来存放一些很复杂的sql语句,在调用的时候只需要call 存储过程名称再把参数传过去就行了。