MySQL的默认事务隔离级别是?
- 读未提交(RU): 一个事务还没提交时, 它做的变更就能被别的事务看到.
- 读提交(RC): 一个事务提交之后, 它做的变更才会被其他事务看到.
- 可重复读(RR): 一个事务执行过程中看到的数据, 总是跟这个事务在启动时看到的数据是一致的. 当然在可重复读隔离级别下, 未提交变更对其他事务也是不可见的.
- 串行化(S): 对于同一行记录, 读写都会加锁. 当出现读写锁冲突的时候, 后访问的事务必须等前一个事务执行完成才能继续执行.
谈谈锁机制与InnoDB锁算法?
MyISAM和InnoDB存储引擎使用的锁:
- MyISAM采用表级锁(table-level locking)。
- InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁
表级锁和行级锁对比:
- 表级锁:MySQL中锁定 粒度最大 的一种锁,对当前操作的整张表加锁,实现简单,资源消耗也比较少,加锁快,不会出现死锁。其锁定粒度最大,触发锁冲突的概率最高,并发度最低,MyISAM和 InnoDB引擎都支持表级锁。
- 行级锁:MySQL中锁定 粒度最小 的一种锁,只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小,并发度高,但加锁的开销也最大,加锁慢,会出现死锁。
Mysql 的存储引擎,myisam和innodb的区别?
- InnoDB支持事务, MyISAM不支持.
- InnoDB支持行级锁, MyISAM支持表级锁.
- InnoDB支持多版本并发控制(MVVC), MyISAM不支持.
- InnoDB支持外键, MyISAM不支持.
- MyISAM支持全文索引, InnoDB不支持(但可以使用Sphinx插件)
MyISAM 是非事务的存储引擎;适合用于频繁查询的应用;表锁,不会出现死锁;不支持事务。适合小数据,小并发
Innodb 是支持事务的存储引擎;适合于插入和更新操作比较多的应用;设计合理的话是行锁(最大区别就在锁的级别上);适合大数据,大并发。
MySQL数据库作发布系统的存储,一天五万条以上的增量,预计运维三年,怎么优化?
(1)设计良好的数据库结构,允许部分数据冗余,尽量避免join查询,提高效率。
(2) 选择合适的表字段数据类型和存储引擎,适当的添加索引。
(3) 做mysql主从复制读写分离。
(4)对数据表进行分表,减少单表中的数据量提高查询速度。
(5)添加缓存机制,比如redis,memcached等。
(6)对不经常改动的页面,生成静态页面(比如做ob缓存)。
(7)书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.
如何进行SQL优化?
(1)选择正确的存储引擎
MyISAM 适合于一些需要大量查询的应用,但其对于有大量写操作并不是很好。甚至你只是需要update一个字段,整个表都会被锁起来,而别的进程,就算是读进程都无法操作直到读操作完成。另外,MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。InnoDB 的趋势会是一个非常复杂的存储引擎,对于一些小的应用,它会比 MyISAM 还慢。但是它支持“行锁” ,于是在写操作比较多的时候,会更优秀。并且,他还支持更多的高级应用,比如:事务。
(2)优化字段的数据类型 记住一个原则,越小的列会越快。如果一个表只会有几列罢了(比如说字典表,配置表),那么,我们就没有理由使用 INT 来做主键,使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间,使用 DATE 要比 DATETIME 好得多。当然,你也需要留够足够的扩展空间。
(3)为搜索字段添加索引 索引并不一定就是给主键或是唯一的字段。如果在你的表中,有某个字段你总要会经常用来做搜索,那么最好是为其建立索引,除非你要搜索的字段是大的文本字段,那应该建立全文索引。
(4)避免使用Select 从数据库里读出越多的数据,那么查询就会变得越慢。并且,如果你的数据库服务器和WEB服务器是两台独立的服务器的话,这还会增加网络传输的负载。即使你要查询数据表的所有字段,也尽量不要用通配符,善用内置提供的字段排除定义也许能给带来更多的便利。
(5)使用 ENUM 而不是 VARCHAR ENUM 类型是非常快和紧凑的。在实际上,其保存的是 TINYINT,但其外表上显示为字符串。这样一来,用这个字段来做一些选项列表变得相当的完美。例如,性别、民族、部门和状态之类的这些字段的取值是有限而且固定的,那么,你应该使用 ENUM 而不是 VARCHAR。
(6)尽可能的使用 NOT NULL 除非你有一个很特别的原因去使用 NULL 值,你应该总是让你的字段保持 NOT NULL。NULL其实需要额外的空间,并且,在你进行比较的时候,你的程序会更复杂。当然,这里并不是说你就不能使用NULL了,现实情况是很复杂的,依然会有些情况下,你需要使用NULL值。
(7)固定长度的表会更快 如果表中的所有字段都是“固定长度”的,整个表会被认为是 “static” 或 “fixed-length”。例如,表中没有如下类型的字段:VARCHAR,TEXT,BLOB。只要你包括了其中一个这些字段,那么这个表就不是“固定长度静态表”了,这样,MySQL 引擎会用另一种方法来处理。
固定长度的表会提高性能,因为MySQL搜寻得会更快一些,因为这些固定的长度是很容易计算下一个数据的偏移量的,所以读取的自然也会很快。而如果字段不是定长的,那么,每一次要找下一条的话,需要程序找到主键。并且,固定长度的表也更容易被缓存和重建。不过,唯一的副作用是,固定长度的字段会浪费一些空间,因为定长的字段无论你用不用,他都是要分配那么多的空间。
唯一索引比普通索引快吗, 为什么?
唯一索引不一定比普通索引快, 还可能慢.
查询时, 在未使用limit 1的情况下, 在匹配到一条数据后, 唯一索引即返回, 普通索引会继续匹配下一条数据, 发现不匹配后返回. 如此看来唯一索引少了一次匹配, 但实际上这个消耗微乎其微. 更新时, 这个情况就比较复杂了. 普通索引将记录放到change buffer中语句就执行完毕了。
而对唯一索引而言, 它必须要校验唯一性, 因此, 必须将数据页读入内存确定没有冲突, 然后才能继续操作. 对于写多读少的情况, 普通索引利用change buffer有效减少了对磁盘的访问次数, 因此普通索引性能要高于唯一索引.
MySQL由哪些部分组成, 分别用来做什么?
Server
- 连接器: 管理连接, 权限验证.
- 分析器: 词法分析, 语法分析.
- 优化器: 执行计划生成, 索引的选择.
- 执行器: 操作存储引擎, 返回执行结果.
- 存储引擎: 存储数据, 提供读写接口.
MySQL查询缓存有什么弊端, 应该什么情况下使用, 8.0版本对查询缓存有什么变更?
查询缓存可能会失效非常频繁, 对于一个表, 只要有更新, 该表的全部查询缓存都会被清空. 因此对于频繁更新的表来说, 查询缓存不一定能起到正面效果.
对于读远多于写的表可以考虑使用查询缓存.
8.0版本的查询缓存功能被删了。
MySQL怎么恢复半个月前的数据?
通过整库备份+binlog进行恢复. 前提是要有定期整库备份且保存了binlog日志.
做过哪些MySQL索引相关优化?
尽量使用主键查询: 聚簇索引上存储了全部数据, 相比普通索引查询, 减少了回表的消耗。MySQL5.6之后引入了索引下推优化, 通过适当的使用联合索引, 减少回表判断的消耗。
若频繁查询某一列数据, 可以考虑利用覆盖索引避免回表。联合索引将高频字段放在最左边。