MySQL2-key与index

`id` int(11) NOT NULL auto_increment, `name` varchar(32) default ‘‘, PRIMARY KEY (`id`) ); CREATE TABLE `xiaodi` ( `id` int(11) NOT NULL auto_increment, `dage_id` int(11) default NULL, `name` varchar(32) default ‘‘, PRIMARY KEY (`id`), KEY `dage_id` (`dage_id`), CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`) ); insert into dage(name) values(‘铜锣湾‘); insert into xiaodi(dage_id,name) values(1,‘铜锣湾_小弟A‘); （2）如果在还有小弟的情况下删除大哥，结果如下 [SQL] delete from dage where id=1; [Err] 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`sample`.`xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)) （3）如果想在没有建立大哥的情况下，强行插入小弟，结果如下 [SQL] insert into xiaodi(dage_id,name) values(2,‘旺角_小弟A‘); [Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`sample`.`xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)) （4）修改事件触发设置 show create table xiaodi;#查看键名称 alter table xiaodi drop foreign key xiaodi_ibfk_1; alter table xiaodi add foreign key(dage_id) references dage(id) on delete cascade on update cascade; （5）如果在还有小弟的情况下删除大哥：大哥和大哥对应的小弟一起被删除；如果想在没有建立大哥的情况下，强行插入小弟，结果并不变，即失败。       四、索引【参考：http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html】 1、索引入门 （1）作用：索引对查询的速度有着至关重要的影响。如果没有索引，查询将对整个表进行扫描；如果有索引，查询只对索引进行。由于数据库的数据不在内存中，每次查询都需要将数据由硬盘调入内存，IO将浪费大量时间。考虑到索引比数据小的多，使用索引可以大幅提高查询速度；尤其是在数据量大时。 （2）索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。目前最常用的存储引擎是InnoDB。   2、选择索引的数据类型：MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则【（1）（2）条不适用于哈希索引】： （1）越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。 （2）简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。注意，对于索引，能用整型，就不要用字符串，尤其是在数据量大的时候；整型的一个弊端是，与客户端的配合可能需要一些额外的工作(尤其是大整型)，但是对效率几乎没有影响。 （3）尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。   3、B-tree索引：结果为B-tree（平衡二叉树） （1）概述：索引存储的值按索引列中的顺序排列。可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询。如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。 （2）示例：其索引包含表中每一行的last_name、first_name和dob列。 CREATE TABLE People ( last_name varchar(50) not null, first_name varchar(50) not null, dob date not null, gender enum(‘m‘, ‘f‘) not null, key(last_name, first_name, dob) ); （3）匹配方式：既可以查找，也可以order by【结果是排序的，因此搜索很快】

1）匹配全值：对索引中的所有列都指定具体的值。 2）匹配最左前缀：你可以利用索引查找last name为Allen的人，仅仅使用索引中的第1列。 3）匹配列前缀：例如，你可以利用索引查找last name以J开始的人，这仅仅使用索引中的第1列。 4）匹配值的范围查询：可以利用索引查找last name在Allen和Barrymore之间的人，仅仅使用索引中第1列。 5）匹配部分精确而其它部分进行范围匹配：可以利用索引查找last name为Allen，而first name以字母K开始的人。 6）仅对索引进行查询：如果查询的列都位于索引中，则不需要读取元组的值。 7）如果索引字段为A+B，查询A+C时，会使用A索引吗->会，使用explain可以证实

（4）限制

1）查询必须从索引的最左边的列开始。 2）不能跳过某一索引列。例如，你不能利用索引查找last name为Smith且出生于某一天的人。 3）存储引擎不能使用索引中范围条件右边的列。例如，如果你的查询语句为WHERE last_name="Smith" AND first_name LIKE ‘J%‘ AND dob=‘1976-12-23‘，则该查询只会使用索引中的前两列，因为LIKE是范围查询。

  4、Hash索引 （1）概述

1）Hash索引通过哈希函数计算Hash值进行检索，可以查到要查数据的行指针，从而定位数据。 2）Hash值不取决于列的数据类型，一个TINYINT列的索引与一个长字符串列的索引一样大。 3）Memory存储引擎支持非唯一hash索引，如果多个值有相同的hash code，索引把它们的行指针用链表保存到同一个hash表项中。

（2）限制

1）由于索引仅包含hash code和记录指针，所以，MySQL不能通过使用索引避免读取记录。但是访问内存中的记录是非常迅速的，不会对性造成太大的影响。 2）不能使用hash索引排序。 3）Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的。 4）Hash索引只支持等值比较，例如使用=，IN( )和<=>。对于WHERE price>100并不能加速查询。

（3）示例 CREATE TABLE testhash ( fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, KEY USING HASH(fname) )ENGINE=MEMORY;   5、其他索引 （1）空间(R-Tree)索引：MyISAM支持空间索引，主要用于地理空间数据类型，例如GEOMETRY。 （2）全文(Full-text)索引：全文索引是MyISAM的一个特殊索引类型，主要用于全文检索。    

MySQL2-key与index

标签：大哥   bbs   htm   order   基本概念   uniq   一致性   reference   查询优化