一、mysql架构图
二、mysql索引
1、索引是什么?
索引是可以让mysql快速查询出数据的一种数据结构
2、索引的分类？
索引分为:
主键索引(创建表时会自动创建)
唯一索引(普通列唯一值会创建唯一索引)
普通索引(普通列的索引)
全文索引(不是mysql的)
3、创建一个mysql的索引命令
create index 索引名称 on 表名称(字段名称)
4、工作中适合添加索引的场景
字段本身是唯一值(适合添加索引)
频繁作为where查询条件的适合创建索引
经常作为order by和group by的适合创建索引
对操作特别频繁的不适合创建索引
5、索引的数据结构
三、mysql优化实战
1、mysql语句分析 explain sql 性能依次降低 const>ref>ref_eq>range>index>all
const:主键查询 ref:命中普通索引 ref_eq:连表查询命中索引 range:范围索引命中 index:覆盖索引,查询的字段建立了索引,刚好全表扫描 all:全表扫描
2、索引失效场景: 1）最左前置,比如符合索引建立了a、b、c,条件查询使用了b、c就会导致索引失效 2) 模糊查询会导致索引失效 3) 计算会导致索引失效 4) 类型不正确会导致索引失效 5) 范围查询范围太大也会导致索引失效
3、mysql各种优化
1)深分页问题 select * from cus_order c inner join (select id from cus_order limit 9000000,10) temp on c.id = temp.id 避免了在几百万数据中的全表扫描
2）join优化
关联字段建立索引,而且长度类型长度一定要保持一致
在join中,会涉及到大表和小表的概念。mysql内部优化器会根据关联字段是否创建了索引使用不同的算法:
nlj(嵌套循环算法):如果关联字段使用了索引,mysql会对小表做全表扫描,用小标的数据和大表的数据去做索引字段的关联查询(type:ref) bnlj(块嵌套循环算法):如果关联字段没有使用索引,mysql会提供一个join buffer缓冲区,然后全表扫描到大表,把大表的数据和缓冲区中的小表数据在内存中进行匹配
- in和exists 优化
在sql中如果A是大表,B是小表,那么使用in会更加合适。反之应该使用exists
in:B的数据量<A的数据量
exists:B的数据量>A的数据量
4)conut优化 大坑:count(字段)如果字段是Null会记录在内 对于count是架构层次的优化 使用redis的++
5)复杂sql的优化
锁的定义和分类
1、锁的定义 锁是用来解决多个任务(线程)在并发访问统一资源时带来的数据安全问题。虽然用锁解决了数据安全问题,但是会带来性能的影响,频繁使用锁的程序性能是必然很差的。
MVCC设计思想,Mysql在数据安全上和性能上做权衡
2、锁的分类
1)从性能上划分:乐观锁和悲观锁
悲观锁:悲观的认为当前的并发是非常严重的,所以在任何时候操作都是互斥多的。保证了线程的安全,但是牺牲了并发性。
乐观锁:对于读的情况,大家都可以进行,但是对于写的情况,再进行上锁。以CAS自旋锁,在某种情况下性能是ok的,但是频繁的自旋会消耗很大的资源
2)从数据的操作粒度上划分:表锁和行锁
表锁:对整张表进行上锁 行锁:对表中的某一行上锁
3)从数据库的操作类型上划分:读锁和写锁
这两种锁都是属于悲观锁
读锁:对于同一行数据进行"读"来说,是可以同时进行读但是写不行。
写锁:在上了写锁之后,及释放写锁之前,在整个过程中是不能进行任何的其他并发操作。
3、表锁
对于整张表上锁。MYISAM储存引擎是天然支持表锁的,也就是说在MYISAM的存储引擎的表如果出现并发的情况,将出现表锁的 在innoDB上一下表锁:
对一张表上读锁/写锁格式
lock table 表名 read/write;
例子
lock table tb_book read;
查看当前会话对所有表上锁情况
show open tables;
释放当前会话的所有锁
unlock tables
读锁: 其他任务可以进行读,但是不能进行写 写锁:其他任务不能进行读和写
4、行锁
MYISAM只支持表锁,但是不支持行锁,InnoDB可以支持行锁。 在并发事务里,每个事务的增删改的操作相当于上了行锁
上锁的方式: update 表名 set name = 'ddd' where id = 8; 对id是8的数据上了行锁 select * from tb_book where id = 9 for update
5、MVCC设计思想
1)事务的特性
原子性: 一个事务时一个最小的操作单位(原子),多条sql语句在一个事务中要么同时成功,要么同时失败。
一致性: 事务提交之前和回滚之后数据是一致的
持久性: 事务一旦提交,对数据的影响是持久的
隔离性: 多个事务在并发访问下,提供了一套隔离机制,不同的隔离级别有不同的并发效果。
2)事务的隔离级别
未提交读 会产品脏读 已提交读(Mysql默认) 解决了脏读,只会读别的事务提交数据 可重复读 可串行化
