本文主要是介绍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
读了之后不能修改
这篇关于mysql刨根问底的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
