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的时候，同时会锁定相应的主键索引

因为二级索引查询是通过主键索引来进行的