【JAVA秒会技术之玩转SQL】MySQL优化技术（二）

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