MySQL三层体系结构 |-----------------------------------------------------------------------------------| | mysqld-SQL层 | |-----------------------------------------------------------------------------------| | mysqld-表引擎适配层 | ------------------------------------------------------------------------------------- | | | | | | ---------------- ----------- ------------- ------------ | -------------- | innoDB表引擎 | |BDB表引擎| MERGE表引擎| |HEAP表引擎| | |MYISAM表引擎 | ---------------- ----------- ------------- —----------- | |------------- | ------------ |ISAM表引擎| ------------ SQL层主要完成SQL语句的词法分析,语法分析,权限检查等工作, 表适配器层根据用户SQL语句上下文,选择并激活用户当前操作的表引擎,执行具体的语句操作. 用户可用通过配置文件设定默认表引擎,default_table_type=HEAP 一条SQL语句的执行过程 1:用户通过客户端向SQL服务器发送SQL语句 2:服务器对应于用户的工作线程(执行数据库连接时建立)接收该SQL语句 3:工作线程执行SQL层权限检查 4:工作线程执行SQL层SQL语句解析 5:工作线程执行SQL层必要的优化工作 6:在3,4,5步发生错误时,SQL层层构造相应的错误信息,由工作线程返回客户端 7:工作线程执行完成SQL层操作后,激活表引擎适配层,选择对应的引擎. 8:工作线程在被选择表引擎上执行,并在相应表引擎系统线程的配合下,完成具体的语句操作 9:工作线程根据表引擎执行情况,给客户端返回结果 InnoDB表引擎的配置 1：旁路配置 当MySQL服务器本身支持InnoDB,但管理员不打算使用InnoDB时使用该配置,这样即使数据库用户指定创建InnoDB表类型,服务器也不予支持. 可以节省内存以及磁盘空间,并可加快某些语句的执行(MYISAM和InnoDB表的区别) 2：线程配置 2.1:并发线程数(innodb_thread_concurrency) 用于指定InnoDB引擎内允许同时并发的线程数量,当多个用户同时连接到服务器并使用InnoDB引擎时,将可能出现多个用户工作线程进入InnnoDB表引擎. 同时处于InnoDB层的用户工作线程越多,系统并发性越高,同时线程之间的互斥冲突可能越多,当达到一定量的时候,系统性能急剧下降,根据实际情况配置. 2.2:最长锁等待时间(innodb_lock_wait_timeout) 该配置项指定用户工作线程等待一个锁的最长时间,当一个InnoDb事务等待某个锁的时间超过该值时,InnoDB将自动回滚该事务. 这对于打破InnoDB可能发生的死锁相当重要,特别是执行Lock Tables命令时,InnoDB将无法控制这来自外部的锁.这就可能导致用户直接锁与InnoDB内部执行过程的 加锁发生死锁.该配置以秒单位,值过小,未发生死锁的事务频繁回滚,过大,死锁时间过长,影响InnoDB的有效执行效率. 2.3:I/O线程数(innodb_file_io_threads) 为了提高速度,InnoDB首先将数据读入内存缓存区,然后再写入磁盘,当读取数据时,InnoDB批量读入数据到缓冲区,方便后期查找. 这样就产生了内存缓冲区和磁盘之间的读写问题,InnoDB设置了专门的I/O线程来完成磁盘于内存缓冲区之间的读写操作. 3:缓存管理配置 3.1：缓冲池大小(innodb_buffer_pool_size) 该配置项用于指定InnoDB所用缓冲池的大小.该缓冲池用户存储InnoDB表索引和表中数据,以缓存磁盘I/O速度.缓冲池越大,缓冲到内存中的索引和数据越多,其他进程能用的内存就越小. 此外,配置越大,数据丢失的风险越大(宕机时缓存区中的数据尚未刷新到磁盘,永久性丢失). 3.2：元数据缓冲池大小(innodb_additional_mem_pool_size) innodb_buffer_pool_size指定的缓冲池用于缓存表索引和表数据,该配置指定缓冲池中用于存放表定义信息等元数据,该配置为一初始化配置,若InnoDB存放元数据 大小超过该配置,自动向操作系统申请. 3.3：日志缓冲区大小(innodb_log_buffer_size) 当事务执行过程中,InnoDB会不断产生日志,以提供数据恢复功能.当InnoDB生成日志时,首先将日志写入缓冲区,然后再刷新到磁盘.该配置用于指定 日志缓冲区大小.过小,将使InnoDB频繁刷新日志到磁盘,同时难以支持长事务.过大浪费内存,同时延长刷新时间,增加日志丢失风险.