SQL概念

数据库引擎介绍 前言 1、存储引擎其实就是对于数据库文件的一种存取机制，如何实现存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法。 2、MySQL中的数据用各种不同的技术存储在文件（或内存）中，这些技术中的每一种技术都使用不同的存储机制，索引技巧，锁定水平并且最终提供广泛的不同功能和能力。在MySQL中将这些不同的技术及配套的相关功能称为存储引擎。 3、根据需求可以在表中设置不同的存储引擎。 查看mysql中的引擎 MySQL中常用的几种存储引擎 1．MyISAM存储引擎 存放的位置 MySQL如果使用MyISAM存储引擎，数据库文件类型就包括.frm、.MYD、.MYI，默认存放位置是C:\Documentsand Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data 存放的方式 MyISAM 这种存储引擎不支持事务，不支持行级锁，只支持并发插入的表锁，主要用于高负载的select。 索引的方式 MyISAM也是使用B+tree索引但是和Innodb的在具体实现上有些不同。 该引擎基于ISAM数据库引擎，除了提供ISAM里所没有的索引和字段管理等大量功能，MyISAM还使用一种表格锁定的机制来优化多个并发的读写操作，但是需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间，否则碎片也会随之增加，最终影响数据访问性能。MyISAM还有一些有用的扩展，例如用来修复数据库文件的MyISAMChk工具和用来恢复浪费空间的 MyISAMPack工具。MyISAM强调了快速读取操作，主要用于高负载的select，这可能也是MySQL深受Web开发的主要原因：在Web开发中进行的大量数据操作都是读取操作，所以大多数虚拟主机提供商和Internet平台提供商（Internet Presence Provider, IPP）只允许使用MyISAM格式。 MyISAM类型的表支持三种不同的存储结构：静态型、动态型、压缩型。 静态型：指定义的表列的大小是固定（即不含有：xblob、xtext、varchar等长度可变的数据类型），这样MySQL就会自动使用静态MyISAM格式。使用静态格式的表的性能比较高，因为在维护和访问以预定格式存储数据时需要的开销很低；但这种高性能是以空间为代价换来的，因为在定义的时候是固定的，所以不管列中的值有多大，都会以最大值为准，占据了整个空间。 动态型：如果列（即使只有一列）定义为动态的（xblob, xtext, varchar等数据类型），这时MyISAM就自动使用动态型，虽然动态型的表占用了比静态型表较少的空间，但带来了性能的降低，因为如果某个字段的内容发生改变则其位置很可能需要移动，这样就会导致碎片的产生，随着数据变化的增多，碎片也随之增加，数据访问性能会随之降低。 对于因碎片增加而降低数据访问性这个问题，有两种解决办法： a、尽可能使用静态数据类型； b、经常使用optimize table table_name语句整理表的碎片，恢复由于表数据的更新和删除导致的空间丢失。如果存储引擎不支持 optimize table table_name则可以转储并 重新加载数据，这样也可以减少碎片； 压缩型：如果在数据库中创建在整个生命周期内只读的表，则应该使用MyISAM的压缩型表来减少空间的占用。 优缺点：MyISAM的优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。 2．innoDB存储引擎 存储位置 MySQL如果使用InnoDB存储引擎，数据库文件类型就包括.frm、ibdata1、.ibd，存放位置有两个，.frm文件默认存放位置是C:\Documents and Settings\All Users\ApplicationData\MySQL\MySQL Server 5.1\data，ibdata1、.ibd文件默认存放位置是MySQL安装目录下的data文件夹。 innodb存储引擎的mysql表提供了事务，回滚以及系统崩溃修复能力和多版本迸发控制的事务的安全。 innodb支持自增长列（auto_increment）,自增长列的值不能为空，如果在使用的时候为空的话怎会进行自动存现有的值开始增值，如果有但是比现在的还大，则就保存这个值。 innodb存储引擎支持外键（foreign key） ,外键所在的表称为子表而所依赖的表称为父表。 innodb存储引擎最重要的是支持事务，以及事务相关联功能。 innodb存储引擎支持mvcc的行级锁。 innodb存储引擎索引使用的是B+Tree 优缺点：InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差，占用的数据空间相对较大。 ...

索引 索引特点：创建与维护索引会消耗很多时间与磁盘空间，但查询速度大大提高！ 在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。 索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。 例如，对于students表： id class_id name gender score 1 1 小明 M 90 2 1 小红 F 95 3 1 小军 M 88 如果要经常根据score列进行查询，就可以对score列创建索引： 1 2 ALTER TABLE students ADD INDEX idx_score (score); 使用ADD INDEX idx_score (score)就创建了一个名称为idx_score，使用列score的索引。索引名称是任意的，索引如果有多列，可以在括号里依次写上，例如： 1 2 ALTER TABLE students ADD INDEX idx_name_score (name, score); 索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，例如gender列，大约一半的记录值是M，另一半是F，因此，对该列创建索引就没有意义。 可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。 对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。 MySQL索引的类型 1. 普通索引 这是最基本的索引，它没有任何限制，比如上文中为title字段创建的索引就是一个普通索引，MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 01 –直接创建索引 02 CREATE INDEX index_name ON table(column(length)) 03 –修改表结构的方式添加索引 04 ALTER TABLE table_name ADD INDEX index_name ON (column(length)) 05 –创建表的时候同时创建索引 06 CREATE TABLE `table` ( 07 `id` int(11) NOT NULL AUTO_INCREMENT , 08 `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , 09 `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL , 10 `time` int(10) NULL DEFAULT NULL , 11 PRIMARY KEY (`id`), 12 INDEX index_name (title(length)) 13 ) 14 –删除索引 15 DROP INDEX index_name ON table ...