目录
1,MySQL 索引的最左原则
2,InnoDB 和 MyIsam 引擎的区别?
3,有哪些优化数据库性能的方法?
4,如何定位慢查询?
5,MySQL 支持行锁还是表锁?分别有哪些优缺点?
1,MySQL 索引的最左原则
mysql的索引是通过B+树来实现的,不管是普通索引还是联合索引,对于普通索引来说,就是在非节点上记录的是索引的值,叶子节点上记录的是主键索引的值,它的排序条件是根据主键的值。例如:
该图是一个以num为索引的B+树。
对于联合索引来说,如果以(num,age)为索引,非叶子节点上就是num,age的值,叶子节点上就是num,age两个字段以及主键count的值。
当num不同的时候,就按照num排序,当num相同时,就按照age排序。
所以,最左前缀匹配就是:因为索引的底层是一个B+树,如果是联合索引,在构造B+树的时候,就会先按照最左的索引值排序,当左边的相同,再依次按照右边的索引来排序。
再通过索引查询的时候,需要遵守最左前缀匹配的原则。
再MYSQL8.0中,加入了索引跳跃扫描。
对于range查询(范围扫描),引入了索引跳跃扫描(Index Skip Scan)优化支持不符合组合索引最左前缀原则条件下的SQL,依然能够使用组合索引,减少不必要的扫描。
他是将最左边的索引设为查询条件,来进行范围查找,但是同样也有限制条件。
对于最左边的这个索引,取值范围不可以太多,不然会导致更慢,而且最好将区分度高,查询频繁鹅字段放在索引的最左边。
2,InnoDB 和 MyIsam 引擎的区别?
我们先将区别列出来:
InnoDB | MyIsam |
支持事务 | 不支持事务 |
支持外键 | 不支持外键 |
支持行锁 | 不支持行锁,支持表锁 |
写操作效率高 | 读操作效率高 |
支持全文索引 | 支持全文索引 |
表占用空间较小 | 表占用空间较大 |
聚集索引 | 非聚集索引 |
InnoDB:MYSQL默认的事务型引擎,它成为大量短期的事务,短期事务很少回滚;它的性能和自动崩溃恢复的性能,在非事务存储需求中也很流行。
MyIsam:在MYSQL5.1之前,MyIsam是默认引擎,提供大量的特性,包括全文索引,压缩,空间函数等,但是不支持事务和行锁,缺点就是崩溃后无法自动恢复。
行锁,表锁:表锁有两种,表共享读锁和表独占写锁。对于MyIsam引擎的表,多个用户可以对同一个表发出读的请求,但是一个用户对表进行写操作,就会阻塞其他用户对这个表的读写。InnoDB引擎的表是通过索引项来加锁实现的,只有通过索引条件监检测数据的时候,InnoDB才会使用行级锁,否则也会使用表级锁。
物理空间的存储:数据库文件都在data目录下,一个文件夹对应一个数据库,本质是文件的存储。InnoDB在数据库中只存在一个*.frm文件,以及上级目录的libdata文件。MyIsam在磁盘存储为三个文件:1,*.frm文件(存储表定义)2,MYD(MyData,数据文件)3,MY|(MyIndex,索引文件)。所以MyIsam表占用空间大。
保存数据表中的行数:InnoDB中不保存表的具体行数,执行select count from table语句的时候,InnoDB要扫描一遍整个表来查找,但是MyIsam只要简单的读出保存的行数即可。
3,有哪些优化数据库性能的方法?
3.1 从硬件角度思考
采用固态硬盘(SSD)代替机械硬盘,固态硬盘IO读取速度比机械硬盘快。
3.2 从数据库部署结构,采用集群架构,读写分离
1,采用多个副本机制,当主库不可用,可以从库中选择一台作为主,减少数据库宕机造成的影响。
2,主从架构可以水平扩展,提升数据库集群整体的并发影响能力。
缺点:数据冗余。
3.3 从整体的应用架构出发
减低数据库的压力,可以在数据库层上设置缓存,redis,缓存热点数据。但是要预防缓存穿透,缓存雪崩的问题。
3.4 从连接数据库出发
为了避免平凡的建立数据库连接,降低IO的开销,可以采用数据库连接池技术,比如druid连接池,初始话一批连接,当需要连接时,直接从数据库连接池获取已经创建好的连接。
缺点:客户端如果对关闭没做好,导致链接泄漏,会白白占用连接资源。
3.5 从执行SQL出发
就是对索引的调优,也就是避免索引出现失效的情况:
- 针对索引字段,模糊检索时,%不能在最左边。
- 针对索引字段,不可以添加函数表达式。
- 针对组合索引,需要满足最左匹配原则。、
- 针对索引字段,索引字段不能包含表达式计算。
- 针对索引字段,取值与字段类型要匹配。
3.6 垂直拆库,拆表,水平分库,分表
高效,解耦,扩展,维护,性能,减少了磁盘 IO。
MySQL 底层是通过数据页存储的,一条记录占用空间过大会导致跨页
另外数据库以行为单位将数据加载到内存中,这样表中字段长度较短且访问频率较高,内存能加载更多的数据,命中率更高,减少了磁盘 IO,从而提升了数据库性能。
- 垂直分表:可以把一个宽表的字段按访问频次、是否是大字段的原则拆分为多个表,这样既能使业务清晰,还能提升部分性能。拆分后,尽量从业务角度避免联查,否则性能方面将得不偿失
- 垂直分库:可以把多个表按业务耦合松紧归类,分别存放在不同的库,这些库可以分布在不同服务器,从而使访问压力被多服务器负载,大大提升性能,同时能提高整体架构的业务清晰度,不同的业务库可根据自身情况定制优化方案。但是它需要解决跨库带来的所有复杂问题。
- 水平分库:可以把一个表的数据(按数据行)分到多个不同的库,每个库只有这个表的部分数据,这些库可以分布在不同服务器,从而使访问压力被多服务器负载,大大提升性能。它不仅需要解决跨库带来的所有复杂问题,还要解决数据路由的问题(数据路由问题后边介绍)。
- 水平分表:可以把一个表的数据(按数据行)分到多个同一个数据库的多张表中,每个表只有这个表的部分数据,这样做能小幅提升性能,它仅仅作为水平分库的一个补充优化。
4,如何定位慢查询?
慢查询就是很慢的查询
1,定位慢查询可以根据慢日志定位慢查询SQL:
- show varlables like‘%query%’ 查询慢日志相关信息
- slow_query_log 默认是off关闭的,使用时,需要改为on打开
- slow_query_log_file 记录的是慢日志的记录文件
- long_query_time 默认是10S,每次执行的sql达到这个时长,就会被记录
- show status like‘%slow_queries%’ 查看慢查询状态
- slow_queries 记录的是慢查询数量 当有一条sql执行一次比较慢时,这个vlue就是1
打开慢查询:set global slow_query_log=ON;
将默认时间改为1秒:set global long_query_time=1;
2,使用explain等工具分析sql
5,MySQL 支持行锁还是表锁?分别有哪些优缺点?
MyISAM 只支持表锁,InnoDB 支持表锁和行锁,默认为行锁。
行锁:访问数据库的时候,锁定整个行数据,防止并发错误。
表锁:访问数据库的时候,锁定整个表数据,防止并发错误。
优缺点:
行级锁:开销大,加锁慢,会出现死锁。锁力度小,发生锁冲突的概率小,并发度最高。
表级锁:开销小,加锁快,不会出现死锁。锁定粒度大,发生锁冲突的概率最高,并发量最低。