数据库内容复习02-MySql的索引们

复习数据库方面的内容, 以下为笔记内容, 参考了:
[关于索引的-1
关于索引的-2
全文检索
全文搜索无结果原因]
外键
…

索引

索引是用来快速的寻找具有特定值的记录, 提高检索效率, 但同时需要额外的空间存储这些索引, 并在进行插入,修改和删除操作时需要对索引进行维护.

普通索引(Index):

普通索引的唯一作用是: 提高数据检索效率.

创建

-- 方法一:
CREATE TABLE `table-name` (
    -- ...
    `column` VARCHAR(100),
    -- ...
    KEY `index-name` (`column`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 方法二:
ALTER TABLE `table-name` ADD INDEX `index-name`(`column`);

-- 联合索引:
ALTER TABLE `table-name` ADD INDEX `index-name`(`column-1`, `column-2`, `column-3`);

删除

ALTER TABLE `table-name` DROP INDEX `index-name`;

使用建议:
应该只对那些经常出现在查询条件(WHERE column=)或者排序条件(ORDERBY column) 中对应的column建立索引

唯一索引(Unique):

唯一索引属于普通索引, 如果确定某个数据列只包含彼此不同的值, 就应该把对应的column设置为唯一索引

创建

-- 方法一:
CREATE TABLE `table-name` (
    -- ...
    `column` VARCHAR(100),
    -- ...
    UNIQUE KEY `index-name` (`column`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 方法二:
ALTER TABLE `table-name` ADD  UNIQUE INDEX `index-name`(`column`);

-- 联合索引:
ALTER TABLE `table-name` ADD  UNIQUE INDEX `index-name`(`column-1`, `column-2`, `column-3`);

删除

ALTER TABLE `table-name` DROP INDEX `index-name`;

主键(Primary Key):

主键用于唯一鉴定唯一的一行记录, 区别于唯一索引: - 不能有NULL(空)值 - 一张表最多只能有一个主键, 但可以很多个索引 - 主键可以作为外键, 唯一索引不可以

创建

-- 方法一:
CREATE TABLE `table-name` (
    -- ...
    `column` INT NOT NULL,
    -- ...
    PRIMARY KEY (`column`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 方法二:
ALTER TABLE `table-name` ADD CONSTRAINT `pk-name` PRIMARY KEY (`column`);

-- 多列做为主键:
ALTER TABLE `table-name` ADD CONSTRAINT `pk-name` PRIMARY KEY(`column-1`, `column-2`, `column-3`);

删除

ALTER TABLE `table-name` DROP PRIMARY KEY;

全文索引(FullText)

还记得 SELECT * FROM table-name where title like ‘%中文%’ 吗? 全文索引比它效率高的多.

创建

-- 方法一:
CREATE TABLE `table-name` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(200) DEFAULT NULL,
    `content` text,
    PRIMARY KEY (`id`),
    FULLTEXT KEY `ft-name` (`title`, `content`) with parser ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 方法二: 注意: ngram 是支持中文的分词器, 如果不设置这个分词器, 检索时将按照英文的分词习惯分词(中文将检索不到结果)
ALTER TABLE `table-name` ADD FULLTEXT `ft-name` (`title`, `content`) with parser ngram;

使用

-- 先插入几条数据
insert into `table-name` (title, content) values('英文索引', '分词，全文索引以词为基础的，MySQL默认的分词是所有非字母和数字的特殊符号都是分词符)');
insert into `table-name` (title, content) values('中文索引', '分词，全文索引以词为基础的，MySQL默认的分词是所有非字母和数字的特殊符号都是分词符)');
insert into `table-name` (title, content) values('日文索引', '分词，全文索引以词为基础的，MySQL默认的分词是所有非字母和数字的特殊符号都是分词符)');

SELECT * FROM `table-name` WHERE `title` LIKE '%索引%';
-- 检索出3条

-- 注意MATCH里的字段必须与"FULLTEXT KEY `ft-name` (`title`, `content`)"中的一样, 不然会报错 (1191, "Can't find FULLTEXT index matching the column list")
SELECT title FROM `table-name` WHERE MATCH(`title`, `content`) AGAINST('索引');
-- 检索出3条

SELECT title FROM `table-name` WHERE MATCH(`title`, `content`) AGAINST('索');
-- 检索出0条; 因为检索是按分词的方式检索 "索引"是一个词, "所"不是

SELECT title FROM `table-name` WHERE MATCH(`title`, `content`) AGAINST('索' IN BOOLEAN MODE);

使用Boolean mode 来检索

删除

ALTER TABLE `table-name` DROP INDEX `ft-name`;

建议:
如果对此检索有更高的要求, 建议使用全文搜索引擎比如: sphinx

外键(Foreign Key):

没有找到好的解释, 简单的说就是一张子表关联到另一张父表的一种有效的方法(用来建立两个表格之间关系的约束)(有些牵强…) 外键值可以为空, 但不能存在子表对应字段数据在关联父表记录找不到的情况

创建

CREATE TABLE `tab_father` (
    `pk_left` INT(11) UNSIGNED NOT NULL,
    `pk_right` INT(11) UNSIGNED NOT NULL,
    `ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
ALTER TABLE `tab_father` ADD INDEX `pk_right` (`pk_right`); -- 被关联键可以是主键,唯一索引,普通索引,联合索引(必须为最左值),

CREATE TABLE `tab_child` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `col` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 方法一:
ALTER TABLE `tab_child` ADD CONSTRAINT `FK_tab_child_tab_father` FOREIGN KEY (`column`) REFERENCES `tab_father` (`pk_right`) ON UPDATE CASCADE ON DELETE CASCADE;

关于外键创建的条件:
如果（父表）字段没有索引，创建外键会失败, 父表关联字段必须显式创建索引（单字段索引或位于最左位置的组合索引）；子表外键字段也是索引字段（如果不是索引字段建立外键关系的时候MySQL会隐式地为该字段创建一个普通索引）。即外键相关字段最后一定会是索引字段。参考论证过程

删除

ALTER TABLE `tab_child` DROP FOREIGN KEY `FK_tab_child_tab_father`;

在高并发系统中其实很少使用外键, 对于并发小的系统中, 外键常在以下几种情况中被使用:

• 有关联关系的表:

  • 一对一
  • 一对多:
  • 多对多: 这种情况需要建立一张中间表, 比如: 一个老师会教不同的班级, 一个班级又有很多不同的老师, 此时可以建一张中间表 teacher_class(id, teacher_id, class_id)

• 优化查询速度: 有时候在一张表中有很多的字段, 有些字段在大多数的时候很好少被使用, 另一些字段却常常被使用, 比如: 一个用户信息表中 user(id, name, paswd, address, hobbies…) [id, name, paswd] 会被经常使用, [address, hobbies] 很少被使用, 此时可以将user差分为 user_info(id, name, paswd) user_profile(user_id, address, hobbies)

补充:

查询数据表的结构

SHOW CREATE TABLE `tab_name`\G;