`
iluoxuan
  • 浏览: 579231 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

mysql负载,查询优化,索引和锁等

 
阅读更多

mysql引擎有多种,每种实现的索引方式也不尽相同:

 

有hash索引,b树索引,b+树索引

 

我这边接触最大的是innodb引擎:

 

支持外键和事务:

b+树索引的类型“

1:InnoDB的主键索引(Primary Key)是Cluster形式的(聚簇索引)。:   按主键值生产b+树,叶子节点放的列的数据

 2:InnoDB的非主键索引(Secondary Index)是普通的B-Tree索引。 按索引值产生b+树,叶子节点放的对应的主键值

 

所以二级索引先按索引值找到主键值在按1方式找到数据

 

mysql按主键key作为内节点;所以选择非常重要

1: 尽量用自增的整形 // 插入的时候直接插入b+树后面

2:不要用随机字符串和uuid等 // 插入的时候要移动b+很多节点

3:   如果没有主键,会选择唯一索引值生成聚簇索引,都没有mysql内部会自动生成自key,效果更佳

4:key的长度不要太大

索引的优化:

     1: 最左原则(b+树性质)A B C 联合所以 只能使用到 A  ;  A  B  ; A  B C  

                            如果要查询A C 可以添加再添加一个联合索引A C

     2:范围查询:  where A < 100 and  A > 90 这个时候 只有第一个A<100才能用到索引,因为这个时候

                           使用索引会对索引的数据加行锁,

     3: order by ,group by 、union 、distinct 等排序字段 :的字段在执行计划中利用了索引时,不用排序操作,否则要排序。

     4:  索引字段不能有函数计算和表达式计算: (否则不走索引)

     5:  尽量避免使用null,

     6: 查询的时候,一个表只能使用一个索引(内部优化选择最好的) 很重要

    

 查询优化: 

     1: jion代替in子查询

      jion原理和优化:

       mysql只使用了大名鼎鼎的Nested Loop Join算法:就是通过驱动表的结果集作为循环基础数据,然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。如果有多个join,则将前面的结果集作为循环数据,再一次作为循环条件到后一个表中查询数据

    join语句的优化

  1. 用小结果集驱动大结果集,尽量减少join语句中的Nested Loop的循环总次数;

  2. 优先优化Nested Loop的内层循环,因为内层循环是循环中执行次数最多的,每次循环提升很小的性能都    能在整个循环中提升很大的性能;

 3. 对被驱动表的join字段上建立索引;

 4. 当被驱动表的join字段上无法建立索引的时候,设置足够的Join Buffer Size。

    in子查询比jion慢:

唯一不同的是对于in子查询它每次执行内部查询的时候都必须重新构造一个JOIN结构,完成相应的初始化操作,并且在这次内部查询结束之后,要完成相应的析构函数,如index_init,index_end,而当外部查询是全表扫描的时候这些操作的次数就是它的记录数,那么它们(构造,析构)所占用的性能也是显而易见的。简单一句话子查询的性能除了查询外,还消耗在JOIN的构造与析构过程

 

所以我们在选择in的时候:都不用子查询而是通过:

in(id1, id2, id3)  避免使用 in (select * xxx)

可以考虑:

如: select  b, c from A where d = (select max(d) from B) ...

优化为: select b, c from A xa , (select max(d) as md from B) as xd

where xa.d = xd.md

 

   上面介绍的优化,大部分常用;具体分析的时候最好能用decs 或者 explain看下sql执行的情况分析

和优化:

 

 mysql的锁:

   推荐阅读: http://hedengcheng.com/?p=771

 

 一个pptf分析很好:

      

mysql高负载排查思路:

1: top看下mysql进程所占有的cup,和io  

2:查看当前连接数和执行的sql语句: show processlist

3:慢查询记录:

  1. log_slow_queries = /usr/local/mysql/var/slow_queries.log   #慢查询日志路径 
  2. long_query_time = 10                                       #记录SQL查询超过10s的语句 
  3. log-queries-not-using-indexes = 1                          #记录没有使用索引的sql 

4:找出cpu占用高的sql,然后show processlist看是否在运行,然后看情况是否kill掉

 

mysql死锁排查:

 

1:查看最近死锁
 mysql> show innodb status
1: mysql行锁锁定所有范围内的记录,同时有next-key locking;即使锁定相邻的键值
比如 wherer id> 100 会同时锁定100;(对于主键索引)
2:对于二级索引,锁定相关的索引记录的next-key locking的时候,同时会锁定相应的主键索引
因为二级索引查询是通过主键索引来进行的
 
 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

分享到:
评论

相关推荐

    MySQL索引优化课件

    MySQL索引优化是数据库性能提升的关键技术之一,尤其在处理大量数据时,高效索引能够显著加快查询速度,降低服务器负载。本课件主要聚焦于MySQL数据库的索引原理、优化策略以及相关存储过程和触发器的应用。 首先,...

    MySQL索引分析及优化.pdf

    索引的优化是指通过合理地使用索引来提高查询速度和减少数据库负载。索引的优化可以从以下几个方面入手: * 选择合适的索引类型:选择合适的索引类型可以提高查询速度和减少数据库负载。 *合理地使用索引:合理地...

    2.mysql查询性能优化1

    MySQL 查询优化可以从多方面入手,包括优化查询语句、优化索引、优化数据库结构等。 6.2 查询优化技术 MySQL 查询优化技术有很多,包括使用索引、优化查询语句、使用连接优化、使用缓存优化等。 6.2.1 使用索引 ...

    Mysql数据库与SQL优化+集群+负载均衡.doc

    常见的 MySQL 负载均衡解决方案包括:HAProxy、LVS 和 MySQL Router 等。 MySQL 数据库与 SQL 优化、集群和负载均衡是提高数据库性能和可用性的关键技术。合理的数据库结构设计、索引设置、查询语句优化、存储引擎...

    MySQL数据库查询优化方案.docx

    MySQL 数据库查询优化方案 MySQL 数据库查询优化是指对数据库中的查询语句进行优化,以提高查询效率和数据库性能。以下是 MySQL 数据库查询优化的一些重要知识...只有通过合理的优化,才能提高数据库性能和查询效率。

    MYSQL索引和优化详细说明教程

    MySQL索引和优化是数据库管理中的关键环节,它直接影响到数据查询的速度和效率。本教程将详细介绍MySQL中的索引类型及其优化策略。 首先,我们需要理解什么是索引。索引是数据库为了快速查找特定记录而创建的一种...

    mysql性能优化-慢查询分析、优化索引和配置.docx

    二、查询与索引优化分析 1. 性能瓶颈定位 使用SHOW命令可以查看MySQL的状态和变量,找出性能瓶颈。例如,SHOW STATUS可以显示系统状态信息,SHOW VARIABLES显示系统变量,SHOW INNODB STATUS查看InnoDB存储引擎...

    MySQL索引分析和优化.pdf

    本文将深入探讨MySQL中的索引原理、不同类型的索引及其应用场景,并给出一些实用的索引优化建议。 #### 二、索引的基本概念 索引是一种特殊的数据结构,它的主要目的是加速数据的检索过程。在没有索引的情况下,...

    MySQL介绍和优化分享

    性能优化是确保MySQL高效运行的关键,涉及多个层面,包括查询优化、索引优化、数据库架构设计和服务器配置等。 1. 查询优化:编写高效的SQL语句,避免全表扫描,合理使用JOIN、子查询和UNION操作。利用EXPLAIN分析...

    mysql索引分析和优化

    本文将深入探讨MySQL索引的工作原理、不同类型的索引以及如何进行有效的索引优化策略。 #### 索引的作用及工作原理 索引类似于书籍的目录,可以帮助数据库快速定位数据。在没有索引的情况下,数据库执行查询时需要...

    mysql优化sql语句的优化(索引,常用小技巧.)

    ### MySQL优化之SQL语句与索引优化 #### 数据库设计合理性 在MySQL数据库的优化过程中,合理设计数据库(表)至关重要。一个合理的数据库设计能够有效地提高查询性能、减少数据冗余并确保数据完整性。 - **3NF**...

    mysql数据库sql优化

    在MySQL数据库管理中,SQL查询的性能直接影响到系统的响应时间和资源消耗。通过合理的SQL优化,可以显著提高数据处理速度,降低服务器负载,提升用户体验。 ##### 1.1 优化实战 #### 策略1:尽量全值匹配 全值...

    MySQL数据库查询优化技术

    1. **索引优化**:索引是提升查询速度的关键。合理创建主键、唯一索引、普通索引和全文索引,能加速数据查找。避免在索引列上使用非等值比较操作(如LIKE 'abc%'),这可能导致索引失效。 2. **EXPLAIN分析**:使用...

    mysql5.6性能优化

    通过合理的查询优化,可以显著提高查询效率,降低数据库的负载。其中,`EXPLAIN`语句是一种常用的工具,用于分析SQL查询的执行计划。 ##### 使用`EXPLAIN` - **语法示例**:`EXPLAIN SELECT * FROM tb_item;` - **...

    MySQL海量数据查询优化策略.

    在处理MySQL海量数据查询优化时,我们需要关注的策略包括但不限于以下几点: 1. 优化索引使用:避免全表扫描至关重要。为此,应当在查询条件(WHERE)和排序(ORDER BY)涉及的列上创建索引。索引有助于数据库管理...

    MySQL查询优化系列讲座.rar

    本系列讲座深入探讨了MySQL查询优化的多个方面,旨在帮助用户提升数据库查询效率,降低系统负载,确保服务的稳定性和响应速度。 首先,"MySQL查询优化系列讲座之查询优化器"详细介绍了MySQL查询优化器的工作原理。...

    mysql-常见问题,索引优化

    MySQL数据库在日常使用中...以上讨论了MySQL在索引优化、事务处理、性能监控等方面的问题和解决方案,为日常数据库维护提供了实用指导。通过深入了解这些问题并采取相应的优化策略,可以显著提高MySQL的性能和稳定性。

Global site tag (gtag.js) - Google Analytics