【JAVA秒会技术之玩转SQL】MySQL优化技术(二)

原创 2017年01月03日 14:36:57

MySQL优化技术(二)

【前文连接】MySQL优化技术(一)

(五)常用SQL优化

1.默认情况,在使用group by 分组查询时,会先分组,其后还会默认对组内其他条件进行默认的排序,可能会降低速度。这与在查询中指定order by col1, col2类似。

如果查询中包括group by但用户想要避免排序结果的消耗,则可以使用order by null禁止排序。

例子:

 

2.尽量使用左连接(或右连接)来替代普通多表联查。因为使用JOIN,MySQL不需要在内存中创建临时表

   select * from dept,emp where dept.deptno=emp.deptno; 【普通联表查询】

   select * from dept left join empon dept.deptno=emp.deptno;  【左外连接,效率更高】

3.如果想要在含有or的查询语句中利用索引,则or之间的每个条件列都必须用到索引,如果没有索引,则应该考虑增加索引;

   select * from 表名 where 条件1=‘t1’ or 条件2=‘t2’;

4.选择合适的存储引擎

在开发中,我们经常使用的存储引擎 MyISAM / INNODB / Memory

1)MyISAM存储: 如果表对事务要求不高,同时是以查询和添加为主的,我们考虑使用MyISAM存储引擎,比BBS中的发帖表,回复表

2)INNODB存储MySQL 5.5以上默认): 对事务要求高,保存的数据都是重要数据,我们建议使用INNODB,比如订单表,账号表

【引申】 MyISAM 和 INNODB的主要区别

①MyISAM不支持事务;而INNODB支持事务;

②MyISAM批量查询的速度比INNODB快(因为INNODB在插入数据时默认会排序);

③MyISAM支持全文索引;而INNODB不支持;

④MyISAM是表锁;而INNODB在有索引时,默认为行锁,无索引时,为行锁;

⑤MyISAM 不支持外键;INNODB支持外键;(在PHP开发中,通常不设置外键,通常是在程序中保证数据的一致)

3)Memory存储,比如我们数据变化频繁不需要入库(重启mysql后,数据会清空),同时又频繁的查询和修改,我们考虑使用memory,数据全部在内存中,速度极快


4)MySQL行级锁、表级锁、页级锁

①表级:引擎 MyISAM默认。直接锁定整张表,在你锁定期间,其它进程无法对该表进行写操作。如果你是写锁。则其它进程则读也不允许;

②行级:有索引时,引擎 INNODB默认(无索引时,为表级锁)。仅对指定的记录进行加锁,这样其它进程还是可以对同一个表中的其它记录进行操作;

③页级:引擎 BDB默认。表级锁速度快,但冲突多,行级冲突少,但速度慢。所以取了折衷的页级,一次锁定相邻的一组记录。

各自的特点

①表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。

②行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

③页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

【补充】InnoDB使用行锁定,BDB使用页锁定。对于这两种存储引擎,都可能存在死锁。这是因为,SQL语句处理期间InnoDB自动获得行锁定和BDB获得页锁定,而不是在事务启动时获得

Ⅰ)MySQL表级锁有两种模式

表共享读锁(Table Read Lock)表独占写锁(Table Write Lock)。什么意思呢,就是说对MyISAM表进行读操作时,它不会阻塞其他用户对同一表的读请求,但会阻塞 对同一表的写操作;而对MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作。

MyISAM表的读和写是串行的,即在进行读操作时不能进行写操作,反之也是一样。但在一定条件下MyISAM表也支持查询和插入的操作的并发进行,其机制是通过控制一个系统变量(concurrent_insert)来进行的:

①当其值设置为0时,不允许并发插入

②当其值设置为1时,如果MyISAM表中没有空洞(即表中没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录

③当其值设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录

MyISAM锁调度是如何实现的呢,这也是一个很关键的问题。例如,当一个进程请求某个MyISAM表的读锁,同时另一个进程也请求同一表的写锁,此时MySQL将会如优先处理进程呢?通过研究表明,写进程将先获得锁(即使读请求先到锁等待队列)。但这也造成一个很大的缺陷,即大量的写操作会造成查询操作很难获得读锁,从而可能造成永远阻塞。所幸我们可以通过一些设置来调节MyISAM的调度行为。我们可通过指定参数,设置set low_priority_updates=1,使优先级降低。

Ⅱ)InnoDB有两种模式的行锁 

1)共享锁:允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。

   Select * from table_name where ......lock in share mode;

2)排他锁:允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和  排他写锁。

   select * from table_name where.....for update;

InnoDB行锁是通过给索引项加锁来实现的,即只有通过索引条件检索数据,InnoDB才使用行级锁,否则将使用表锁

Ⅲ)插入时,更新性能优化的几个重要参数:

① bulk_insert_buffer_size  批量插入缓存大小

    这个参数是针对MyISAM存储引擎来说的.适用于在一次性插入100-1000+条记录时, 提高效率.默认值是8M.可以针对数据量的大小,翻倍增加;

② concurrent_insert 并发插入

当表没有空洞(删除过记录), 在某进程获取读锁的情况下,其他进程可以在表尾部进行插入;

值可以设0不允许并发插入, 1当表没有空洞时, 执行并发插入, 2不管是否有空洞都执行并发插入(默认是1 针对表的删除频率来设置);

③ delay_key_write 针对MyISAM存储引擎,延迟更新索引

    意思是说,update记录时,先将数据up到磁盘,但不up索引,将索引存在内存里,当表关闭时,将内存索引,写到磁盘. 值为 0不开启, 1开启. 默认开启.

④ delayed_insert_limit, delayed_insert_timeout, delayed_queue_size

延迟插入, 将数据先交给内存队列, 然后慢慢地插入.但是这些配置,不是所有的存储引擎都支持, 目前来看, 常用的InnoDB不支持, MyISAM支持.根据实际情况调大, 一般默认够用了

5.能用deciaml 的地方,尽量不要用float

【引申】float、double和decimal的区别:

float:浮点型,占4个字节,数值范围为-3.4E38~3.4E38(7个有效位

double:双精度实型,占8个字节,数值范围-1.7E308~1.7E308(15个有效位

decimal:数字型,占16个字节,不存在精度损失,常用于银行帐目计算。(28个有效位

decimal(a,b):

a——指定指定小数点左边和右边可以存储的十进制数字的最大个数,最大精度38。

b——指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 a之间的值。默认小数位数是 0。

6.对于存储引擎是MyISAM的数据库,如果经常做删除和修改记录的操作,要定时执行optimize table table_name;功能对表进行碎片整理

(六)分表技术

1.水平分割(分表)

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表,我们可以称为子表,每个表都对应三个文件,MYD数据文件,MYI索引文件,frm表结构文件。这些子表可以分布在同一块磁盘上,也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名,然后去操作它。

简单理解:将一张大表,分割成多个数据类型与大表相同的子表,在访问时,根据事先定义好的规则等到对应的表名,然后去操作;

2.垂直分割(分区)

分区和分表相似,都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表,而分区是将数据分段划分在多个位置存放,可以是同一块磁盘也可以在不同的机器。分区后,表面上还是一张表,但数据散列到多个位置了。app读写的时候操作的还是大表名字,db自动去组织分区的数据。

简单理解:是将一张大表的某(几)列,提取成一张单独的表。一般情况两者的关系是一对一;

3.分表的几种方式

1)mysql集群

它并不是分表,但起到了和分表相同的作用。集群可分担数据库的操作次数,将任务分担到多台数据库上。集群可以读写分离,减少读写压力。从而提升数据库性能。

2)自定义规则分表

大表可以按照业务的规则来分解为多个子表。通常为以下几种类型,也可自己定义规则。

①Range(范围):这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。

②Hash(哈希):这中模式允许通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。

③Key(键值):上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。

④List(预定义列表):这种模式允许系统通过预定义的列表的值来对数据进行分割。

⑤Composite(复合模式):以上模式的组合使用 

3)利用merge存储引擎来实现分表

这里不做详解,因为有一个更强大的技术Mycat,可以帮我实现各种分库分表!

(七)mysql参数调优

主要是通过修改mysql的my.ini配置文件

1.如果是InnoDB引擎:

  innodb_additional_mem_pool_size= 64M

  innodb_buffer_pool_size= 1G

2.如果是MyISAM引擎:

调整key_buffer_size

当然调整参数还是要看状态,用show status语句可以看到当前状态,以决定改调整哪些参数。

3.还可以调整最大连接数

    # connection limit has been reached.

    max_connections=1000

(八)读写分离

参照《使用Spring配置多数据源,实现读写分离( MySQL实现主从复制)一文,

URL】 http://blog.csdn.net/qq296398300/article/details/53994215

版权声明:本文为博主原创文章,未经博主允许不得转载。 举报

相关文章推荐

mysql千万级大数据SQL查询优化

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎...

SQL优化34条 java面试题

我们要做到不但会写SQL,还要做到写出性能优良的SQL,以下为笔者学习、摘录、并汇总部分资料与大家分享! (1) 选择最有效率的表名顺序(只在基于规则的优化器中有效): ORACLE 的解析器按照...

我是如何成为一名python大咖的?

人生苦短,都说必须python,那么我分享下我是如何从小白成为Python资深开发者的吧。2014年我大学刚毕业..

关于Java面试中sql优化的问题浅析

在应用系统开发初期,由于开发数据库数据比较少,对于查询SQL语句,复杂视图的的编写等体会不出SQL语句各种写法的性能优劣,但是如果将应用 系统提交实际应用后,随着数据库中数据的增加,系统的响应速度就成...

如何写出高性能SQL语句—优化SQL查询

1、 首先要搞明白什么叫执行计划? 执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,比如一条SQL语句如果用来从一个 10万条记录的表中查1...

程序员老鸟写sql语句的经验之谈、百万数据查询优化技巧三十则

一、程序员老鸟写sql语句的经验之谈 做管理系统的,无论是bs结构的还是cs结构的,都不可避免的涉及到数据库表结构的设计,sql语句的编写等。因此在开发系统的时候,表结构设计是否合理,sql语句是否标...

【JAVA秒会技术之玩转SQL】MySQL优化技术(一)

MySQL优化技术(一)         开发的路上,总会碰到一些老系统,越用越慢。“慢”的原因也许有很多,但是,博主个人觉得,数据库的设计和sql语句写的好坏,对系统效率的影响是最直接,最显而易见的...

MYSQL的优化操作

1.优化查询的查询缓存 大部分MySQL服务器都有查询缓存功能。这是提高性能的最有效的方法之一,这是由数据库引擎私下处理的。当同一个查询被多次执行,结果会直接从缓存里提取,这样速度就很快。 ...

mysql优化技术

mysql优化从以下四个方面来优化: 1》数据库设计 三范式: 1NF:原子性操作,不可再分的表记录 2NF:在1NF基础上,记录的唯一性确定,避免任何记录重复,通过主键来解决 3NF:在2...

【JAVA秒会技术之玩转多线程】多线程那些事儿(一)

多线程那些事儿(一)     现在只要出去面试,关于“Java多线程”的问题,几乎没有一家单位不问的,可见其重要性。于是博主抽空研究了一下,确实很有意思!以下是我综合整理了网上的各种资料,和个人的一些...

【JAVA秒会技术之玩转高效分页】EasyUI + PageHelper实现分页

EasyUI + PageHelper实现分页 一、EasyUI页面分页 页面逻辑:页面初始化时,通过jquery easyui的DataGrid(数据表格)的url属性异步加载,返回指定的json格...
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)