MySQL优化

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u012557298/article/details/78932223

并发高的时候利用缓存,自带缓存失效时间?

返回的列尽可能少

问题点:limit起始位置

SQL语句优化

 

那么如果我们也要查询所有列,有两种方法,一种是id>=的形式,另一种就是利用join,看下实际情况:

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20

SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id

 

可以使用 SHOW [FULL] PROCESSLIST 命令来查看哪些线程正在运行,及其查询状态,Command列显示了状态。

 

 

 

五、联接查询

 

 

 

 

分解联接,把一个多表联接分解成多个单表查询,然后在应用程序端实现联接

  > 例如有如下的一个连接查询:

    SELECT * FROM tag JOIN tag_post ON tag.id = tag_post.tag_id WHERE tag.title = 'test';

   分解成两个查询:

    SELECT * FROM tag WHERE tag.title = 'test'; -- 假设返回id有 (10,11,12,13,14,15);

    SELECT * FROM tag_post WHERE tag_id IN (10,11,12,13,14,15);

   这样分解查询,看似浪费,但其针对一些耗时的多表联接能带来很好的性能提升:

    》 缓存的性能更高:上面的查询已经被缓存起来,下次再查询tag.title = 'test',则会直接从缓存中取出;第二条IN操作,下次查询(11,12,14, 20,25),对于11,12,14则直接从缓存中取出,只去读取20,25。如果一个表经常改变,分解联接可以减少缓存失效的次数。

    》 可以减少多余的行访问,联接操作,每从tag表中检查一行,就会去tag_post中去检查。

   > 什么时候使用分解联接更好: 可以缓存早期查询的大量数据 , 数据分布在不同的服务器上 , 对于大表使用IN()替换联接

 

 

 

 

三、MySql如何优化和执行查询

你也可以通过EXPLAINEXTENDED SELECT ... ... ; SHOW WARNINGS; 查看最终优化后的执行sql。

 

 

首先需要知道,客户端用一个数据包将查询发送到服务器,一旦客户端发送了查询,剩下的就是等待结果。如果一个查询过大,比如批量插入,有时会出现"MySQL server has gone away"的错误,导致的原因可能就是传送的数据太大,导致连接断开了,可以通过 SHOW VARIABLES LIKE "max_allowed_packet"  命令查看你的服务器所允许传送的最大数据,可在my.ini里配置。  

  > 服务器发送的响应由许多数据包组成,服务器发送响应的时候客户端必须接收完整的结果集,不能只提取几行数据后要求服务器停止发送剩下的数据。所以,使用LIMIT来获取你所需要的数据行数。  

  > 每个MySql连接【线程】,状态来标识正在进行的事情。可以使用 SHOW [FULL] PROCESSLIST,Command列显示了状态。

   一些常见的状态:其它的查找MySql手册

    Sleep  线程正在等待客户端,以向它发送一个新语句

    Query  线程正在执行查询或往客户端发送数据

    Locked  该查询被其它查询锁定

    Copying to tmp table on disk  临时结果集合大于tmp_table_size。线程把临时表从存储器内部格式改变为磁盘模式,以节约存储器

    Sending data  线程正在为SELECT语句处理行,同时正在向客户端发送数据

    Sorting for group  线程正在进行分类,以满足GROUP BY要求

    Sorting for order  线程正在进行分类,以满足ORDER BY要求  

 

5.优化特定类型的查询

 

 

还不够,就需要考虑更改应用程序的架构。可以考虑使用汇总表,还可以利用外部缓存系统。

 

 

关于COUNT(NULL)的应用:当你统计某列不同值的数量时,可以像下面这样写SQL

 

 1.使用SUM()函数:SELECT SUM(IF(c1='red', 1, 0)) AS red, SUM(IF(c1='blue'), 1, 0) AS blue, SUM(IF(c1='black'), 1, 0) AS black FROM color;

    2.使用COUNT():SELECT COUNT(c1='red' OR NULL) AS red, COUNT(c1='blue' OR NULL) AS blue, COUNT(c1='black' OR NULL) AS black FROM color;

 

 

2、优化联接

  > 确保ON或USING使用的列上有索引。

  > 确保GROUP BY或ORDER BY只引用了一个表中的列,这样,MySql可以尝试对这些操作使用索引。

3、优化GROUP BY和DISTINCT

  > 在很多情况下,MySql对这两种方式的优化方式基本都是一样的。实际上,优化过程要求他们可以互相转化。通常来说,索引是优化它们的一种重要的手段。

  > 当不能使用索引的时候,MySql有两种优化GROUP BY的策略:使用临时表或文件排序进行分组。任何一种方式对于特定的查询都有可能是高效的。可以使用SQL_SMALL_RESULT强制MySql选择临时表,或者使用SQL_BIG_RESULT强制它使用文件排序。

 

 

 

6.查询优化提示

 

如果不满意MySql优化器选择的优化方案,可以使用一些优化提示来控制优化器的行为。可以将适当的提示放入查询中,它只会影响当前的查询。

1、DELAYED:这个提示用于INSERT和UPDATE。

  应用了这个提示的语句会立即返回并将待插入的列放入缓冲区中,在表空闲的时候再执行插入。它对于记录日志很有用,对于某些需要插入大量数据也很有用。它有很多限制,比如,延迟插入不能运行于所有的存储引擎上,并且无法使用LAST_INSERT_ID();

2、STRAIGHT_JOIN:

  这个提示可用于SELECT语句中SELECT关键字后面,也可以用于联接语句。它的一个用途是强制MySql按照查询中表出现的顺序来联接表;另一个用途是联接两个表时,强制这两个表按照顺序联接。

3、SQL_SMALL_RESULT和SQL_BIG_RESULT

  用于SELECT语句。它们告诉MySql在GROUP BY或DISTINCT查询中如何并且何时使用临时表。SQL_SMALL_RESULT告诉优化器结果集会比较小,可以放在索引过的临时表中,以避免对分组后的数据排序。SQL_BIG_RESULT表明结果集比较大,最好使用磁盘上的临时表排序。

4、SQL_BUFFER_RESULT

  这个提示告诉优化器将结果放在临时表中,并且尽快释放掉表锁。

5、SQL_CACHE和SQL_NO_CACHE

  SQL_CACHE表明将查询缓存;SQL_NO_CACHE则相反。

6、USING INDEX、IGNORE INDEX和FORCE INDEX

  这几个提示告诉优化器从表中寻找行的时候使用或忽略索引

 

 

参考文章http://www.cnblogs.com/shenzikun1314/p/6396105.html

 

 

参数书籍 网易深入理解Mysql

 

熟悉 innodb 和myisam 存储引擎,熟悉 mysql 锁机制,熟悉 MySQL 的索引优化以及查询语句分析

 

悲观锁(Pessimistic Lock

当数据库执行selectfor update时会获取被select中的数据行的行锁,因此其他并发执行的selectfor update如果试图选中同一行则会发生排斥(需要等待行锁被释放),selectfor update获取的行锁会在当前事务结束时自动释放,因此必须在事务中使用。

mysql中用悲观锁务必要确定走了索引,而不是全表扫描。

乐观锁(Optimistic Lock

乐观锁的特点先进行业务操作,不到万不得已不去拿锁。即乐观的认为拿锁多半是会成功的,因此在进行完业务操作需要实际更新数据的最后一步再去拿一下锁就好。

乐观锁在数据库上的实现完全是逻辑的,不需要数据库提供特殊的支持。一般的做法是在需要锁的数据上增加一个版本号,或者时间戳,然后按照如下方式实现:

 

1. SELECT data AS old_data, version AS old_version FROM …;
2. 根据获取的数据进行业务操作,得到new_data和new_version
3. UPDATE SET data = new_data, version = new_version WHERE version = old_version
if (updated row > 0) {
    // 乐观锁获取成功,操作完成
} else {
    // 乐观锁获取失败,回滚并重试
}

 

乐观锁是否在事务中其实都是无所谓的,其底层机制是这样:在数据库内部update同一行的时候是不允许并发的,即数据库每次执行一条update语句时会获取被update行的写锁,直到这一行被成功更新后才释放。因此在业务操作进行前获取需要锁的数据的当前版本号,然后实际更新数据时再次对比版本号确认与之前获取的相同,并更新版本号,即可确认这之间没有发生并发的修改。如果更新失败即可认为老版本的数据已经被并发修改掉而不存在了,此时认为获取锁失败,需要回滚整个业务操作并可根据需要重试整个过程。

总结

·     乐观锁在不发生取锁失败的情况下开销比悲观锁小,但是一旦发生失败回滚开销则比较大,因此适合用在取锁失败概率比较小的场景,可以提升系统并发性能

1.1.1     1. mysql锁机制

InnoDB存储引擎既支持行级锁(row-levellocking),也支持表级锁,但默认情况下是采用行级锁。

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

innodb 行级锁record-level 
lock
大致有三种:recordlock, gap lock and Next-KeyLocks

record lock 锁住某一行记录 
gap lock
锁住某一段范围中的记录 
next key lock
是前两者效果的叠加。

InnoDB还有两种内部使用的意向锁(意向共享锁和意向排他锁)。这两种意向锁都是表锁。意向锁是InnoDB自动加的,不需要用户干预。 
对于UPDATEDELETEINSERT语句,InnoDB会自动给涉及数据集加排他锁;对于普通SELECT语句,InnoDB不会加任意锁。

事务可以通过以下语句显示给记录集加共享锁或者排他锁:

并发高的时候利用缓存,自带缓存失效时间?

返回的列尽可能少

问题点:limit起始位置

SQL语句优化

那么如果我们也要查询所有列,有两种方法,一种是id>=的形式,另一种就是利用join,看下实际情况:

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20

SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id

 

可以使用 SHOW [FULL] PROCESSLIST 命令来查看哪些线程正在运行,及其查询状态,Command列显示了状态。

 

五、联接查询


 

分解联接,把一个多表联接分解成多个单表查询,然后在应用程序端实现联接

  > 例如有如下的一个连接查询:

    SELECT * FROM tag JOIN tag_post ON tag.id = tag_post.tag_id WHERE tag.title = 'test';

   分解成两个查询:

    SELECT * FROM tag WHERE tag.title = 'test'; -- 假设返回id (10,11,12,13,14,15);

    SELECT * FROM tag_post WHERE tag_id IN (10,11,12,13,14,15);

   这样分解查询,看似浪费,但其针对一些耗时的多表联接能带来很好的性能提升:

    》 缓存的性能更高:上面的查询已经被缓存起来,下次再查询tag.title = 'test',则会直接从缓存中取出;第二条IN操作,下次查询(11,12,14, 20,25),对于11,12,14则直接从缓存中取出,只去读取20,25。如果一个表经常改变,分解联接可以减少缓存失效的次数。

    》 可以减少多余的行访问,联接操作,每从tag表中检查一行,就会去tag_post中去检查。

   > 什么时候使用分解联接更好: 可以缓存早期查询的大量数据数据分布在不同的服务器上对于大表使用IN()替换联接


 

 

三、MySql如何优化和执行查询

你也可以通过EXPLAINEXTENDED SELECT ... ... ; SHOW WARNINGS; 查看最终优化后的执行sql

首先需要知道,客户端用一个数据包将查询发送到服务器,一旦客户端发送了查询,剩下的就是等待结果。如果一个查询过大,比如批量插入,有时会出现"MySQL server has gone away"的错误,导致的原因可能就是传送的数据太大,导致连接断开了,可以通过 SHOW VARIABLES LIKE "max_allowed_packet"  命令查看你的服务器所允许传送的最大数据,可在my.ini里配置。  

  > 服务器发送的响应由许多数据包组成,服务器发送响应的时候客户端必须接收完整的结果集,不能只提取几行数据后要求服务器停止发送剩下的数据。所以,使用LIMIT来获取你所需要的数据行数。  

  > 每个MySql连接【线程】,状态来标识正在进行的事情。可以使用 SHOW [FULL] PROCESSLISTCommand列显示了状态。

   一些常见的状态:其它的查找MySql手册

    Sleep  线程正在等待客户端,以向它发送一个新语句

    Query  线程正在执行查询或往客户端发送数据

    Locked  该查询被其它查询锁定

    Copying to tmp table on disk  临时结果集合大于tmp_table_size。线程把临时表从存储器内部格式改变为磁盘模式,以节约存储器

    Sending data  线程正在为SELECT语句处理行,同时正在向客户端发送数据

    Sorting for group  线程正在进行分类,以满足GROUP BY要求

    Sorting for order  线程正在进行分类,以满足ORDER BY要求  


 

5.优化特定类型的查询


 

还不够,就需要考虑更改应用程序的架构。可以考虑使用汇总表,还可以利用外部缓存系统。


 

关于COUNT(NULL)的应用:当你统计某列不同值的数量时,可以像下面这样写SQL
 

 1.使用SUM()函数:SELECT SUM(IF(c1='red', 1, 0)) AS red, SUM(IF(c1='blue'), 1, 0) AS blue, SUM(IF(c1='black'), 1, 0) AS black FROM color;

    2.使用COUNT()SELECT COUNT(c1='red' OR NULL) AS red, COUNT(c1='blue' OR NULL) AS blue, COUNT(c1='black' OR NULL) AS black FROM color;


 

2、优化联接

  > 确保ONUSING使用的列上有索引。

  > 确保GROUP BYORDER BY只引用了一个表中的列,这样,MySql可以尝试对这些操作使用索引。

3、优化GROUP BYDISTINCT

  > 在很多情况下,MySql对这两种方式的优化方式基本都是一样的。实际上,优化过程要求他们可以互相转化。通常来说,索引是优化它们的一种重要的手段。

  > 当不能使用索引的时候,MySql有两种优化GROUP BY的策略:使用临时表或文件排序进行分组。任何一种方式对于特定的查询都有可能是高效的。可以使用SQL_SMALL_RESULT强制MySql选择临时表,或者使用SQL_BIG_RESULT强制它使用文件排序。


 


 

6.查询优化提示

如果不满意MySql优化器选择的优化方案,可以使用一些优化提示来控制优化器的行为。可以将适当的提示放入查询中,它只会影响当前的查询。

1DELAYED:这个提示用于INSERTUPDATE

  应用了这个提示的语句会立即返回并将待插入的列放入缓冲区中,在表空闲的时候再执行插入。它对于记录日志很有用,对于某些需要插入大量数据也很有用。它有很多限制,比如,延迟插入不能运行于所有的存储引擎上,并且无法使用LAST_INSERT_ID();

2STRAIGHT_JOIN

  这个提示可用于SELECT语句中SELECT关键字后面,也可以用于联接语句。它的一个用途是强制MySql按照查询中表出现的顺序来联接表;另一个用途是联接两个表时,强制这两个表按照顺序联接。

3SQL_SMALL_RESULTSQL_BIG_RESULT

  用于SELECT语句。它们告诉MySqlGROUP BYDISTINCT查询中如何并且何时使用临时表。SQL_SMALL_RESULT告诉优化器结果集会比较小,可以放在索引过的临时表中,以避免对分组后的数据排序。SQL_BIG_RESULT表明结果集比较大,最好使用磁盘上的临时表排序。

4SQL_BUFFER_RESULT

  这个提示告诉优化器将结果放在临时表中,并且尽快释放掉表锁。

5SQL_CACHESQL_NO_CACHE

  SQL_CACHE表明将查询缓存;SQL_NO_CACHE则相反。

6USING INDEXIGNORE INDEXFORCE INDEX

  这几个提示告诉优化器从表中寻找行的时候使用或忽略索引



 

参考文章http://www.cnblogs.com/shenzikun1314/p/6396105.html
 

参数书籍 网易深入理解Mysql

悲观锁(Pessimistic Lock)

当数据库执行selectfor update时会获取被select中的数据行的行锁,因此其他并发执行的select for update如果试图选中同一行则会发生排斥(需要等待行锁被释放),select for update获取的行锁会在当前事务结束时自动释放,因此必须在事务中使用。

mysql中用悲观锁务必要确定走了索引,而不是全表扫描。

 

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

innodb 行级锁record-level 
lock
大致有三种:recordlock, gap lock and Next-KeyLocks

record lock 锁住某一行记录 
gap lock 
锁住某一段范围中的记录 
next key lock 
是前两者效果的叠加。

InnoDB还有两种内部使用的意向锁(意向共享锁和意向排他锁)。这两种意向锁都是表锁。意向锁是InnoDB自动加的,不需要用户干预。 
对于UPDATEDELETEINSERT语句,InnoDB会自动给涉及数据集加排他锁;

事务可以通过以下语句显示给记录集加共享锁或者排他锁:

1

2

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE  #共享锁

SELECT * FROM table_name WHERE ... FOR UPDATE  #排他锁

·         共享锁:所有事务共享一把锁;

·        排他锁:不应许别的事务获取锁。

因为MySQL的行锁是针对索引加的锁, 对于键值在条件范围内但并不存在的记录,叫做间隙(Next-Key锁)。间隙锁作用:一是为了防止幻读【读取到另一个线程插入的数据】,二是为了满足其恢复和复制的需要。

InnoDB如何解决死锁问题的:

发生死锁后,InnoDB一般都能自动检测到,并使一个事务释放锁并回退,另外一个事务获得锁,并继续完成事务。但在涉及外部锁, 或涉及表锁的情况下,InnoDB并不能完全自动检测到死锁,这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。

MySQL锁解决并发问题应用

https://www.cnblogs.com/running-crusader/articles/6402923.html

理解innodb的锁(record,gap,Next-Keylock)

http://blog.csdn.net/cug_jiang126com/article/details/50596729

 

 

乐观锁(Optimistic Lock)

 乐观锁在不发生取锁失败的情况下开销比悲观锁小,但是一旦发生失败回滚开销则比较大,因此适合用在取锁失败概率比较小的场景,可以提升系统并发性能

代码实现:

1. SELECT data AS old_data, version AS old_version FROM …;

2. 根据获取的数据进行业务操作,得到new_datanew_version

3. UPDATE SET data = new_data, version = new_version WHERE version = old_version

if (updated row > 0) {

    // 乐观锁获取成功,操作完成

} else {

    // 乐观锁获取失败,回滚并重试

}

乐观锁是否在事务中其实都是无所谓的,其底层机制是这样:在数据库内部update同一行的时候是不允许并发的,即数据库每次执行一条update语句时会获取被update行的写锁,直到这一行被成功更新后才释放。

设置环境变量

Set global所有会话都有效】环境变量名=value

查看当前事务的锁情况

http://blog.csdn.net/meseal/article/details/76668802


 

根据业务场景设计高效的表结构

数据类型设定:设定字符串转CRC值进行索引,由字符转成的唯一整数来定字符串的唯一性

尽可能满足右匹配规则

枚举类型用索引,关键字in

索引方式

Innodb不支持hash方式索引

临时表提高查询方式

监控当前session连接情况,show status like

SQL优化

优化 INSERT 语句当进行数据 INSERT 的时候,可以考虑采用以下几种优化方式。

l 如果同时从同一客户插入很多行,尽量使用多个值表的 INSERT 语句,这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗,使得效率比分开执行的单个 INSERT 语句快(在一些情况中几倍)。下面是一次插入多值的一个例子: insert into test values(1,2),(1,3),(1,4)…

l 如果从不同客户插入很多行,能通过使用 INSERT DELAYED 语句得到更高的速度。 DELAYED 的含义是让 INSERT 语句马上执行,其实数据都被放在内存的队列中,并没有真正写入磁盘,这比每条语句分别插入要快的多;LOW_PRIORITY 刚好相反,在所有其他用户对表的读写完后才进行插入;

 l 将索引文件和数据文件分在不同的磁盘上存放(利用建表中的选项);

l 如果进行批量插入,可以增加 bulk_insert_buffer_size 变量值的方法来提高速度,但是, 这只能对 MyISAM 表使用;

l 当从一个文本文件装载一个表时,使用 LOAD DATA INFILE。这通常比使用很多 INSERT 语句快 20

如果查询包括 GROUP BY 但用户想要避免排序结果的消耗,则可以指定 ORDER BY NULL 禁止排序

WHERE 条件和 ORDER BY 使用相同的索引,并且ORDER BY 的顺序和索引顺序相同,并且 ORDER BY 的字段都是升序或者都是降序。

嵌套查询尽量使用JOIN

 

实际是对 OR 的各个字段分别查询后的结果进行了 UNION

有复合索引的列 company_id moneys 上面做 OR 操作的时候,却不能用到索引

 

 

·         共享锁:所有事务共享一把锁;

·        排他锁:不应许别的事务获取锁。

 

InnoDB的行锁实现的特点:只有通过索引条件检索数据,InnoDB才会使用行级锁,否则,InnoDB将会使用表锁。因为MySQL的行锁是针对索引加的锁, 
 

对于键值在条件范围内但并不存在的记录,叫做间隙。InnoDB会对这个间隙加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。 
InnoDB
使用间隙锁的目的:一是为了防止幻读,二是为了满足其恢复和复制的需要。

InnoDB如何解决死锁问题的:

InnoDB中,锁是逐步获得的,因此发生死锁是可能的。发生死锁后,InnoDB一般都能自动检测到,并使一个事务释放锁并回退,另外一个事务获得锁,并继续完成事务。但在涉及外部锁, 
或涉及表锁的情况下,InnoDB并不能完全自动检测到死锁,这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。

https://www.cnblogs.com/running-crusader/articles/6402923.html

 

理解innodb的锁(record,gap,Next-Keylock)

http://blog.csdn.net/cug_jiang126com/article/details/50596729

 

设置环境变量

Set global所有会话都有效】环境变量名=value

查看当前事务的锁情况

http://blog.csdn.net/meseal/article/details/76668802

 

 

根据业务场景设计高效的表结构

 

数据类型设定:设定字符串转CRC值进行索引,由字符转成的唯一整数来定字符串的唯一性

尽可能满足右匹配规则

枚举类型用索引,关键字in

索引方式

Innodb不支持hash方式索引

临时表提高查询方式

监控当前session连接情况,show status like

SQL优化

优化 INSERT 语句当进行数据 INSERT 的时候,可以考虑采用以下几种优化方式。

l 如果同时从同一客户插入很多行,尽量使用多个值表的 INSERT 语句,这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗,使得效率比分开执行的单个 INSERT 语句快(在一些情况中几倍)。下面是一次插入多值的一个例子: insert into test values(1,2),(1,3),(1,4)…

l 如果从不同客户插入很多行,能通过使用 INSERT DELAYED 语句得到更高的速度。 DELAYED 的含义是让 INSERT 语句马上执行,其实数据都被放在内存的队列中,并没有真正写入磁盘,这比每条语句分别插入要快的多;LOW_PRIORITY 刚好相反,在所有其他用户对表的读写完后才进行插入;

 l 将索引文件和数据文件分在不同的磁盘上存放(利用建表中的选项);

l 如果进行批量插入,可以增加 bulk_insert_buffer_size 变量值的方法来提高速度,但是, 这只能对 MyISAM 表使用;

l 当从一个文本文件装载一个表时,使用 LOAD DATA INFILE。这通常比使用很多 INSERT 语句快 20 倍

如果查询包括 GROUP BY 但用户想要避免排序结果的消耗,则可以指定 ORDER BY NULL 禁止排序

WHERE 条件和 ORDER BY 使用相同的索引,并且ORDER BY 的顺序和索引顺序相同,并且 ORDER BY 的字段都是升序或者都是降序。

嵌套查询尽量使用JOIN

 

实际是对 OR 的各个字段分别查询后的结果进行了 UNION。

有复合索引的列 company_id 和 moneys 上面做 OR 操作的时候,却不能用到索引

 

 

 

阅读更多
换一批

没有更多推荐了,返回首页