mysql刨根问底

索引：排好序的数据结构

二叉树：

红黑树

hash表：

b-tree：

   叶子相同深度，叶节点指针=空，索引元素不重复，从左到右递增排序

   节点带data

b+tree：

   非叶子节点只存储索引，可放更多索引

   叶子节点含all索引 用指针双向连接，提高区间访问性能

页大小16kb，2000w 高度3   3次磁盘io

   根节点常驻内存，减少磁盘io

引擎

innodb

    frm:           ibd:

   表数据文件本身按b+tree组织多索引结构文件

   聚集索引-叶节点含完整数据

myisam：索引/数据文件分离

    frm。 MYD。 MYI

    主键b+tree存储 data是地址引用，非聚集

explain少不了这个大佬

type关联类型或访问类型 

  mysql如何查找表中的行，system const  eq_ref  ref  range  index  all

  null:mysql在优化阶段分解查询语句，执行阶段不需要访问表 索引

  system,const:查询某部分优化并将其转化成一个常量

  eq_ref:primary key或unique_key 索引的all部分被连接使用，最多只会返回一条符合条件的记录

  ref：没有用唯一索引

不走索引：

   引擎本身不支持索引

   不等于 

   is null / is not null

   like 通配符: select 查询具体的列 

   复杂函数

   字符串不加单引号：类型转换

   or / in 查询时  不一定用索引，检索比例 表大小等因素不一定用索引

   范围查询优化

索引(a,b,c)

where a = 4 and b like 'kk%' and c=6;  //使用abc

where a = 4 and b like 'k%kk%' and c=7; //使用ab c

索引下推5.6后

trace工具：命令行打开 

  影响性能，分析执行计划

   sql准备阶段  格式化sql   

   sql优化阶段：去掉无意义语句

优化

order by与group by

 最左前缀法则，中间字段不能断

 using filesort 标识没走索引，order by * asc ，* desc  索引排序方式不同8后降序索引可以支持

• using index指mysql扫描索引本身完成排序 效率高， filesort效率低没有用索引
• order by使用索引最左前列 / where与order by条件组合满足索引最左前列
•  尽量在索引列上完成排序，遵循索引建立时最左前缀法则
• order by的条件不在索引列上，会产生using filesort
• group by与order by类似，先排序后分组，索引创建顺序最左前缀法则
• group by不需要排序可加group by null禁止排序

where 高于having，能写where中的限定条件就不要去having限定

filesort 文件排序

 单路排序：一次性取出满足条件的字段，sort buffer (1M)排序；trace看sort_mode显示sort_key additional_fields 或 sort_key，packed_additional_fields

 双路排序：回表排序，据相应条件取出排序字段和可直接定位的行数id，sort buffer排序，再次取需要的字段

  字段总长度 < max_length_for_sort_data，单路排序

  字段总长度 > max_length_for_sort_data，双路排序

索引设计原则

代码先行，索引后上

联合索引尽量覆盖

小基数字段不建索引：重复值少

长字符串前缀索引，order by和group by 中不能用

where与order by冲突时优先where

执行：小表驱动大表

left  join左表驱动表  right join右边驱动表

   NLJ： 循环驱动表 读取关联字段  取出另一张被驱动表满足条件的数据，扫描磁盘

   BNL:驱动表数据读到join buffer中，被驱动表每一行取出与join buffer对比

   straight_join t1 on t2.a=t1.a 强制指定驱动表

      被驱动表的关联字段无索引的关联查询，BNL ；有索引NLJ

in:B表的数据集小于a表，in 优先exists

     select * from A where id in(select id from B);

exists当A表数据小于B，exists优先in

     select* from A where exists (select 1 from B where B.id=A.id)

count(*)  count(1)> count(字段) > count(id) 

  字段有索引：二级索引 ，比主键索引大小要小

  count(1) count(字段) 执行过程类似，count(1)不需要取字段

  count(*)做了优化，不取值 按行累加 效率高

查询mysql自己维护总行数

   myisam维护总行数，innodb不会存储总行数

show table status可查询表总行数

整型无负数，unsigned可扩大一倍

tinyint代替enum bitenum set

避免使用整数的显示宽度：不要int(10) 直接int，10是显示长度

decimal注意设置长度，精确计算

整形运算 存储实数：实数乘以相应的倍数后再操作

整数通常是最佳的数据类型：速度快 auto_increment

锁

 myisam查询自动加给表读锁，改增删自动给涉及表加写锁

乐观锁：版本对比 CAS /   悲观锁：

表锁/页锁

行锁：

  索引上加锁，

  RR升级表锁需要解决不可重复读 (扫描过的索引被其他修改)

     幻读(间隙被其他事务插入记录) 

  RC不会升级

读锁 共享锁 悲观锁 / 写锁 排他锁

意向锁：针对表锁，mysql自己加，表的一个标识 代表已经有锁了

    意向共享锁：加共享锁前 先获取

   意向排他锁：

间隙锁：RR 两个值之间的空隙，可重复读隔离级别下才会生效

临键锁：

锁等待：

show status  like 'innodb_row_lock%';

  innodb_row_lock_current_waits  锁等待的数量

  innodb_row_lock_time_avg 每次等待平均时间

 innodb_row_lock_time_max 等待最长的一次时间

 innodb_row_lock_time 锁定的总时间

 innodb_row_lock_waits 总共等待的次数

select * from information_schema.innodb_trx;#查看事务

select * from information_schema.innodb_locks;#查看锁 8后更换data_locks

select * from information_schema.innodb_lock_waits;#锁等待  8更换data_lock_waits

show engine  innodb  status；#锁等待详细信息

 所有数据检索通过索引来完成，避免索引升级

 合理设计索引，缩小锁范围

  减少检索条件范围，避免间隙锁

  控制事务大小，减少锁定资源量和时间长度

  尽可能低的事务隔离级别

MVCC

RR事务开启，执行查询sql生成当前事务一致性视图read-view，该视图在事务结束前不会变化

   读已提交隔离级别每次执行查询sql都会重新生成

   视图由查询时未提交事务id数组min_id 和 已创建最大事务id max_id

   事务中查询从对应版本链最新数据逐条跟read-view做对比 read-view(100,300)300

事务

原子  一致  隔离  持久

大事务：

    并发时连接池撑爆/锁太多超时阻塞/

    执行时间长 主从延迟 /回滚时间长/undo log膨胀/易死锁

优化：

 查询放事务外 (rr除外) / 避免远程调用 设置超时 / 避免一次性处理太多数据  / 

 更新设计加锁操作尽可能放到事务靠后的位置/能异步尽量异步 /  应用保证数据一致性 

隔离级别：undo多版本链

select 快照读 历史数据

update  insert   delete 当前读，最新数据

读未提交：read uncommit

  脏读，

读已提交:read commit 

    乐观锁 行锁  语句级快照读

    tx_id：

    roll_pointer：undo log

可重复读:repeatable read

    不能用版本号标识 读的是之前的   事务级快照

    幻读：新增的数据

  查询需要加锁吗？rr  读同一时间维度  需要

串行:serializable 

  读了之后不能修改