|
-- 此处 indexName 索引名同样可以省略
/*
（2）唯一索引，它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式（仅仅在创建普通索引时关键字 INDEX 前加 UNIQUE）：
*/
-- 创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(10));
-- 修改表结构来创建索引
ALTER TABLE mytable ADD UNIQUE INDEX indexName (username(10));-- 也可简写成 ALTER TABLE mytable ADD UNIQUE indexName (username(10));
-- 创建表的时候直接指定
CREATE TABLE mytable(
id INT,
username VARCHAR(16),
city VARCHAR(16),
age INT,
UNIQUE INDEX indexName (username(10)) -- 也可简写成 UNIQUE indexName (username(10))
);
/*
（3）主键索引，它是一种特殊的唯一索引，不允许有空值。在建表的时候同时创建的主键即为主键索引
主键索引无需命名，一个表只能有一个主键。主键索引同时可是唯一索引或者全文索引，但唯一索引或全文索引不能共存在同一索引
*/
-- 修改表结构来创建索引
ALTER TABLE mytable ADD PRIMARY KEY (id);
-- 创建表的时候直接指定
CREATE TABLE mytable(
id INT,
username VARCHAR(16),
city VARCHAR(16),
age INT,
PRIMARY KEY(id)
);
/*
（4）全文索引，InnoDB存储引擎不支持全文索引
*/
-- 创建索引
CREATE FULLTEXT INDEX indexName ON mytable(username(10));
-- 修改表结构来创建索引
ALTER TABLE mytable ADD FULLTEXT INDEX indexName (username(10));-- 也可简写成 ALTER TABLE mytable ADD FULLTEXT indexName (username(10));
-- 创建表的时候直接指定
CREATE TABLE mytable(
id INT,
username VARCHAR(16),
city VARCHAR(16),
age INT,
FULLTEXT INDEX indexName (username(10)) -- 也可简写成 FULLTEXT indexName (username(10))
)ENGINE=MYISAM;
-- 建表时创建全文索引，要设置该表的存储引擎为MYISAM,新版mysql默认InnoDB存储引擎不支持全文索引
-- 删除索引
DROP INDEX indexName ON mytable;
/*
Mysql自动使用索引规则：
btree索引
当使用 <，<=，=，>=，>，BETWEEN，IN，!=或者<>，以及某些时候的LIKE才会使用btree索引，因为在以通配符%和_开头作查询时，MySQL不会使用索引。btree索引能用于加速ORDER BY操作
hash索引
当使用=，<=>，IN，IS NULL或者IS NOT NULL操作符时才会使用hash索引，并且不能用于加速ORDER BY操作，并且条件值必须是索引列查找某行该列的整个值
对where后边条件为字符串的一定要加引号，字符串如果为数字mysql会自动转为字符串，但是不使用索引。
mysql目前不支持函数索引，只能对列的前一部分（指定长度前缀）进行索引，对于char和varchar列，使用前缀索引（该列从起始字符到指定长度字符位置建立索引）将大大节省空间。
mysql列建议列是非null的。说是如果是允许null的列，对索引会有影响（索引不会包括有NULL值）。因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该用0、一个特殊的值或者一个空串代替空值。
尽量不使用NOT IN和<>操作
username,city,age建立这三列的组合索引，其实是相当于分别建立了下面三组组合索引：
username,city,age
username,city
username
使用组合索引，比如where等条件，列名必须从组合索引最左列至右连续的列名做条件才可以，组合索引类似单一索引前缀
*/
-- 调用组合索引
SELECT * FROM mytable WHERE username = 'admin' AND city = 'DaLian';
-- 多表联查中的条件也可运用索引
-- 全文索引的使用
SELECT * , MATCH (username,city) AGAINST ('name100 name200 city500 thisisname') FROM mytable WHERE MATCH (username,city) AGAINST ('name100 name200 city500 thisisname');
-- 返回 mytable 表在 MATCH 的参数中的任意列中包含 AGAINST 字符串参数中被 "空格" "," 和 "." 分割的任意单词的行(断字的字符： "空格" "," 和 "." 但是不用这些符号断字的语言，如中文，就得自行手动断字。)
-- 表列中的单词也是以空格分割来区分
-- 这里指定 MATCH...AGAINST 两次。这不会引起附加的开销，因为 MySQL 优化器会注意到两次是同样的 MATCH...AGAINST 调用，并只调用一次全文搜索代码。
/*
函数 MATCH() 对照一个列名集(一个 FULLTEXT 索引中的一个或多个列的列名)。搜索字符串做为 AGAINST() 的参数给定。搜索以忽略字母大小写的方式执行，预设搜寻是不分大小写，若要分大小写，列的字符集设置要从utf8改成utf8_bin。
虽然同一个表格可以有不同字符集的字段，但是同一个FULLTEXT 索引里的字段必须是同一个字符集与collation。
任何在 stopword 列表上出现的，或太短的(3 个字符或更少的)的单词将被忽略。(可以覆写内建的 stopword 列表。可以修改最少四个字符的设定。 )
搜索的词有一个权重性，如果搜索的词在表中包含它的行太多，则这个搜索词将有较低的权重(可能甚至有一个零权重)，否则，它将得到一个较高的权重。然后，权重将被结合用于计算行的相似性。
如果搜索词在表中超过一半的行中出现。则它被有效地处理为一个 stopword (即，一个零语义值的词)，不搜索该词。
MATCH...AGAINST可以跟所有MySQL语法搭配使用，像是JOIN或是加上其他过滤条件。
对于表中的每行记录，MATCH...AGAINST 返回一个相关性值。即，返回的每行与搜索条件之间的相似性尺度。
当 MATCH() 被使用在一个 WHER |