mysql使用
索引原理
MySQL使用的是B+树作为索引的数据结构
B树是一个分支内按顺序存放多个节点数据的数据结构;而B+树在此基础上,在分支内只存储索引,只在叶子节点存储数据(这样每一层可以存储更多索引,减少层数),并且在叶节点之间用指针互相连接,提高访问效率。
引擎
MyISAM,B+树存储的Data就是数据的地址(非聚集索引、稀疏索引)
InnoDB,直接存储数据(聚集索引)
为什么InnoDB建议每张表必须建立主键,并用自增整型?
ibd必须用B+树索引,而整型是天然的索引;否则ibd会自己维护一个唯一id行(隐藏的主键)。
因此UUID比较效率会比整型更低。
而自增则根本避免了重复,并且只在一端变化,已经有的数据无需做修改,减少了维持有序的成本。
如果不自增,而是随机添加,那么新增的数很可能会触发分裂、平衡,造成冗余索引。
B+树如何支持范围查询
Hash结构的索引,不支持范围查询;而B+树只用找到两端,然后顺着指针拿到所有节点就好了(叶节点是双指针连接的,并且有序)。
联合主键索引,为什么是最左前缀原则?
最左前缀原则:不能跳过左边的索引,必须从最左边索引开始,逐步增加条件。
因为联合主键索引底层的B+树就是按照主键顺序排序的,会从左到右进行比较;如果跳过了左边的主键,那就找不到了,因为第二个主键不一定是排好序的!。
首先按照第一个主键排序,然后按照第二个主键排序。在同一个主键内,二级主键是有序的,但是跳出这个圈,就是无序的。
索引优化原则
explain性能分析
explain extended
: 可以估算出将要和explain中前一个表达式进行连接的行数。
show warning
: Mysql的提示信息,可能会帮你优化。
字段解释
select_type
- Primary 最外层的select。
- Subquery 不在from语句中,包含在select中的子查询。
- Derived 包含在from语句中的子查询。派生表。
id - 表示执行顺序,顺序越靠后优先级越高
type
效率优先级 system > const > eq_ref > ref > range > index > ALL
一般来说range是及格线,最好达到ref
- NULL mysql通过优化和底层原理,不访问表或索引就取到值。如求最小值,通过B+树直接拿到。
- system, const system是const的特例,const表示常量查询。表只有一行,为const查询。只有一条元组匹配,为system查询。
- eq_ref 主键关联查询,表有几行。
- ref 使用非主键(不唯一)索引,表有很多行。使用普通索引或唯一索引的部分前缀。
- range 范围查找,包括比较符号。
- index 无查询条件,全选。扫描全索引就能拿到结果,一般扫描二级索引(Mysql优先选择同等条件下更小的索引)。
- ALL 全表扫描。扫描聚集索引,比index更大
sql语句优化
- 尽量使用覆盖索引
-- 推荐,其中name和age都有索引 |
- 少用or或in;不对索引用函数;不让索引发生隐式转换
- 减少搜索范围;范围过大,mysql会认为全表扫描更快,从而不走索引
force index(your_index)
强制索引(注意,不一定更快!可能更多回表)- 试试用
like
代替范围查询
缓存
Mysql8以后移除了缓存。
Mysql缓存的本质是KV Map。然而,对于高频修改的数据,Map缓存下来的是脏数据,因此不实用。
事务
原子性 Atomicity
BUSINESS |
原子性:事务操作要么同时发生,要么同时失败,不存在中间情况
通过Undo Log回滚实现
一致性 Consistency
账户500元 -> 扣除1000元 -> 账户-500元 |
一致性:每个操作都必须是合法的,账户信息应该从一个有效状态到另一个有效状态。
隔离性 Isolation
商户1转账500元 -> 余额更新为500元 |
隔离性:两个操作对同一个账户并发操作时,应该表现为不相互影响类似串行的操作。
持久性 Durability
转账500元到余额 --服务器宕机--> 余额0元 |
持久性:操作更新成功后,更新的结果应该永久地保留下来,不会因为宕机等问题而丢失。