MySQL数据库知识点汇总

innodb_buffer_pool_size
在MySQL 5.6/MariaDB 10.1版本里,调整innodb_buffer_pool_size大小必须重启mysql进程才可以生效,而在MySQL 5.7版本里,可以直接动态设置,MySQL的InnoDB引擎中的innodb_buffer_pool_size参数可以设置为物理内存的70%~80%。

max_connections
连接数是直接反应数据库性能好坏的关键指标,连接数过多,很可能有多种原因,比如,被一条SQL查询给堵死了,造成了后面的DML操作等待,又比如,增、删、改、查操作很频繁,磁盘I/O遇到了瓶颈,导致无法处理繁忙的请求等。
max_connections在大多数情况下可以将其设置为500~1000,腾讯云默认3600。

MySQL的锁

  • 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。MyISAM引擎属于这种类型。

  • 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。InnoDB引擎属于这种类型。

  • 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。NDB属于这种类型。

行锁
InnoDB存储引擎是通过给索引上的索引项加锁来实现的,这就意味着:只有通过索引条件检索数据,InnoDB才会使用行级锁,否则,InnoDB将使用表锁。
在并发访问比较高的情况下,如果大量事务因无法立即获得所需的锁而挂起,会占用大量计算机资源,造成严重的性能问题,甚至拖垮数据库,这时需要通过设置合适的锁等待超时阀值参数innodb_lock_wait_timeout来解决,一般设置为100秒即可,腾讯云默认60秒。

InnoDB引擎与MyISAM引擎的性能对比
MyISAM支持全文索引,这是一种基于分词创建的索引,支持一些比较复杂的查询,但不是事务安全的,而且不支持外键。每张MyISAM表存放在3个文件中:frm文件存放表格定义;数据文件是MYD (MYData);索引文件是MYI(MYIndex)。对于MyISAM表,可以手工或者自动执行检查或修复操作,这一点要注意跟InnoDB的事物恢复区分开来。
InnoDB是事务型引擎,支持回滚,具有崩溃恢复能力,多版本并发控制(MVCC)、支持ACID事务、支持行级锁定(InnoDB表的行锁不是绝对的,如果执行一个SQL语句没有使用到索引,InnoDB表同样会锁全表)。
InnoDB的工作原理:就是把数据捞到内存中,被用户读写,这样大大增加了性能,因为从内存进行读写比磁盘要快得多。当数据全部加载到内存中,这时的性能是最好的。它的设计理论是充分利用内存,减少磁盘I/O使用率。

MyISAM和InnoDB之间的主要区别

  • MyISAM是非事务安全型的,而InnoDB是事务安全型的,也就是ACID事务支持;

  • MyISAM锁是表级锁,锁开销最小,而InnoDB支持行级锁定,锁管理开销大,支持更好的并发写操作;

  • MyISAM支持全文索引,而InnoDB不支持全文索引,但在最新的5.6版本中已提供支持;

  • MyISAM相对简单,管理方便,因此在效率上要优于InnoDB,小型应用可以考虑使用MyISAM;

  • MyISAM表是保存成文件的形式,在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦;

  • InnoDB表比MyISAM表更安全,可以在保证数据不会丢失的情况下,切换非事务表到事务表。

SQL注意事项

  • 在生产环境里,应尽量避免使用子查询,可用left join表连接取代之。

  • 字段使用函数,将不能用到索引。

  • 当取出的数据量超过表中数据的20%,优化器就不会使用索引,而是全表扫描。

备份方式

  • 冷备份,此时数据库处于关闭状态,能够较好地保证数据库的完整性。

  • 热备份,数据库正处于运行状态,这种备份方法依赖于数据库的日志文件。

  • 逻辑备份,使用mysqldump命令从数据库中提取数据,并将结果写到一个文件上,文件内容为纯文本的SQL语句。