你的位置:首页 > 数据库

[数据库]MySQL引擎、索引和优化(li)


一、存储引擎

  存储引擎,MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术,你能够获得额外的速度或者功能,从而改善你的应用的整体功能。InnoDB存储引擎是5.5版本后Mysql的默认数据库,事务型数据库的首选引擎,支持ACID事务,支持行级锁定。另外还有常见的MyISAM存储引擎,它拥有较高的插入,查询速度,但不支持事务。所以,很明显:插入不频繁,查询非常频繁,没有事务,用MyISAM;可靠性要求高,表更新频繁,事务多,用InnoDB。

//#查看本机MySQL提供的什么存储引擎//show ENGINES;////#查看Mysql当前默认的存储引擎//show variables like '%storage_engine%';////#查看当前表用什么存储引擎(DDL最后)//show create table idc_work_order_main;

//#修改当前表的存储引擎
//ALTER TABLE idc_work_order_main ENGINE = 'MyISAM'

  MySQL官方对InnoDB是这样解释的,InnoDB给MySQL提供了具有提交、回滚和奔溃恢复能力的事务安全存储引擎。InnoDB是为处理巨大数据量时的最大性能设计,它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。InnoDB存储引擎被完全与MySQL服务器整合,InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。

  如果使用innodb存储引擎,我们知道该引擎最主要的特点是transactional和row lock(行级锁)。按理说不会出现表锁才对,但是事实上还是会出现锁表的的情况,也会比较严重,下面主要就是来探讨一下这个问题。查看mysql文档会发现,虽然innodb使用的的row lock(行级锁),但是在处理具有auto increment字段的表的时候,会使用一种特殊的表锁:AUTO-INC。简单来说就是Innodb会在内存里保存一个计数器用来记录 auto_increment的值,当插入数据时,就会用一个表锁来锁住这个计数器,直到插入结束。一条一条插入问题不大,但是如果高并发插入,就会造成 sql阻塞。

  解决方案:1.不使用auto increment字段,自己维护主键生成。该方法中选择主键生成策略很重要, 要综合考虑简单和效率问题。假设使用uuid,虽然简单但是会造成该表的主键效率很低(innodb的主键是特殊的index,其他的index会引用主键)。2.升级到最新的5.2版本。

//#MySQL5.1.22版本之前,这种方式的特点就是“表级锁定”,并发性较差//innodb_autoinc_lock_mode = 0 (“traditional” lock mode:全部使用表锁)////#推荐使用“consecutive”,并发性相对较高,特点是,即保证同一条insert语句中新插入的auto_increment id都是连续的//innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)////#这种模式是来一个分配一个,而不会锁表,只会锁住分配id的*过程*,和innodb_autoinc_lock_mode = 1的区别在于//#不会预分配多个,这种方式并发性最高。但是在replication中当binlog_format为statement-based时//#(简称SBR statement-based replication)存在问题,因为是来一个分配一个,这样当并发执行时,//#“Bulk inserts”在分配时会同时向其他的INSERT分配,会出现主从不一致(从库执行结果和主库执行结果不一样),因为binlog只会记录开始的 insert id。//innodb_autoinc_lock_mode = 2 (“interleaved” lock mode:全部使用新方式,不安全,不适合replication)

  关于数据的拷贝问题,常用的数据表引擎是MyISAM和InnoDB。MyISAM的数据表的后缀名是.frm(表结构)、.myd(数据)和.myi(索引),其索引和数据是分开的,可以直接拷贝;InnoDB的数据表的后缀名是.frm(表结构)和.ibd(数据),索引和数据都在同个文件ibdata*,不能直接拷贝,需要先导出再导入。拷贝完之后别忘了重启数据库服务。

    

  既然是存储引擎,那么我们看看这些数据库的存储是什么样的。block是相对于磁盘来讲的,page是相对于内存来讲的。第一幅图是新建一个txt文档,文件写入1,然后再属性中看占用空间的大小,也就是一个block的大小是4k个字节。通过右图的方式可以用来查看内存中页的大小。

              

磁盘是分block块的,同一表的数据页是以链表的形式串联在一起的,数据库数据按行存在各个block中,并且以block为单位来存取数据。执行一条SQL的时候,从命令解析、确定执行计划、增删改查。这样磁盘的I/O带来了性能问题。如何减少磁盘的I/O次数呢?

// 1.保证读取数据量在合理大小// 2.保证存取数据能够顺序读取// 3.减少需要扫描数据占用空间

保证读取数据量在合理大小;保证存取数据能够顺序读取;减少需要扫描数据占用空间。解决措施,就是使用index索引。dense index是稠密索引,也叫全索引。sparse index是稀疏索引。dense indexes是通过对每一个record在磁盘上持久保存一些额外的数据,用于提高查询的效率。Sparse index结合sequential file和dense index file的优点,通过保存部分key K作为它的record,能很好的支持二分查找快速查找record,并且能进一步减少所需的磁盘I/O。

                       

二、索引  

  索引是帮助MySQL高效获取数据的数据结构。介绍MySQL的索引结构,索引原理,进而学习索引的优化。MySQL的索引结构包括:B-tree索引、Tree索引、哈希索引(Hash)、位图索引(Bitmap)、跳表。

//#查看表的当前索引 执行结果显示(Index_type: BTREE)//SHOW INDEX FROM idc_work_order_main

  命令用于查看我们的数据库中表的当前索引,执行结果显示当前表结构使用功能的索引是BTREE。通常我们通过如下方式给表建索引:

//#查询表当前使用的索引(表的主键自动建立唯一索引unique index)//SHOW INDEX FROM idc_work_order_main;////#创建索引index//CREATE INDEX aaa ON idc_work_order_main(remark)//DROP INDEX aaa ON idc_work_order_main////#创建唯一索引unique INDEX(唯一的索引意味着两个行不能拥有相同的索引值,否则创建失败)//CREATE UNIQUE INDEX aaa ON idc_work_order_main(id)//DROP INDEX aaa ON idc_work_order_main////#创建组合索引//CREATE INDEX aaa ON idc_work_order_main(id,remark)//DROP INDEX aaa ON idc_work_order_main

我们知道索引并不是随便乱建的,在考虑是否建索引时,我们一般考虑如下的一些情况:

  1.表记录太少。如果一个表只有5条记录,采用索引去访问记录的话,那首先需访问索引表,再通过索引表访问数据表,一般索引表与数据表不在同一个数据块,这种情况下至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出,处理速度显然会比用索引快。

  2.经常插入、删除、修改的表。对一些经常处理的业务表应在查询允许的情况下尽量减少索引。

  3.数据重复,且分布平均的表字段。假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。

  建立索引,一般是在对针对百万级以上的数据才建立索引的,以期来提高性能。在创建索引时,首先要考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据,在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。其次,在对建立索引的时候要对表进行加锁,因此应当注意操作在业务空闲的时候进行。其次考虑因素便是磁盘I/O。物理上应当尽量把索引与数据分散到不同的磁盘上。逻辑上,数据表空间与索引表空间分开。这是在建索引时应当遵守的基本准则。

//一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。//NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替,id3则可使用id>3 or id

  索引