MySQL索引

1.索引类型：

主键索引（primary key）：列值唯一，且不允许为空，主键可以有多个列组成。

唯一索引（unique index）：列值唯一，允许为空。

普通索引：可以创建在任何一个列上，没有任何限制。

组合索引：多个列做成的组合索引，遵循”最左前缀“原则。

全文索引（fulltext key）：5.6版本以后支持全文索引。

2.主键索引：创建主键就是创建主键索引

PRIMARY KEY 约束来创建主键。一个表只能有一个 PRIMARY KEY 约束

?

1 2 3

CREATE TABLE student(  id INT UNSIGNED COMMENT '无符号' PRIMARY KEY AUTO_INCREMENT,  NAME VARCHAR(32) NOT NULL DEFAULT ' ');

 主键索引(创建表后添加)

?

1	CREATE TABLE student( id INT, NAME VARCHAR(32) NOT NULL DEFAULT ' ');alter table student add primary key(id)

 联合主键创建的两种方法

?

1	ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

?

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE `t_accounts` (   `<strong>F_user_id</strong>` bigint(20) unsigned NOT NULL DEFAULT '0',   `<strong>F_sub_account_type</strong>` smallint(5) unsigned NOT NULL DEFAULT '1',   `<strong>F_currency</strong>` tinyint(3) unsigned NOT NULL DEFAULT '1',   `F_enabled` tinyint(3) unsigned DEFAULT '1',   `F_create_time` datetime NOT NULL,   `<strong>F_modify_time</strong>` datetime NOT NULL,   `F_balance_amount` bigint(20) DEFAULT '0',   PRIMARY KEY (`F_user_id`,`F_sub_account_type`,`F_currency`),   KEY `I_modify_time` (`F_modify_time`) )

3.联合索引

总结：联合索引根据第一个字段确定数据在哪个叶子结点，然后再按照其他字段在进行排序。相比与多个单列索引，联合索引会先根据第一列进行查询，然后再根据第二列进行查询。多个单列索引会选择最严格的一个索引进行查询。

联合索引多个字段之间顺序：把识别度最高的字段放到最前面。

联合索引(col1, col2,col3)也是一棵B+Tree，其非叶子节点存储的是第一个关键字的索引，而叶节点存储的则是三个关键字col1、col2、col3三个关键字的数据，且按照col1、col2、col3的顺序进行排序。

配图可能不太让人满意，因为col1都是不同的，也就是说在col1就已经能确定结果了。col1表示的是年龄，col2表示的是姓氏，col3表示的是名字。如下图

如果执行的是：
select * from STUDENT where 年龄=1 and 姓氏='李';
那么当执行查询的时候，索引是能生效的，从图中很直观的看出，age=1的是第一个叶子节点的前6条记录，在age=1的前提下，姓氏=’李’的是前3条。因此最终查询出来的是这三条，从而能获取到对应记录的地址

4.普通索引：

?

1 2 3 4 5 6 7 8 9

1、普通索引      CREATE INDEX indexName ON mytable(username(length));      创建表的时候直接指定：   CREATE TABLE mytable(        ID INT NOT NULL,        username VARCHAR(16) NOT NULL,        INDEX [indexName] (username(length))  );

 5.唯一索引：

?

1 2 3 4 5 6 7 8 9 10

创建索引： CREATE UNIQUE INDEX indexName ON mytable(username(length)) 修改表结构： ALTER table mytable ADD UNIQUE [indexName] (username(length)) 创建表的时候直接指定： CREATE TABLE mytable(      ID INT NOT NULL,       username VARCHAR(16) NOT NULL,       UNIQUE [indexName] (username(length))  );

 6.全文索引

?

1	ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):

7. 索引建立

频繁更新或者唯一性太差的字段不能建立索引

8. 索引失效

违反最左原则

对索引进行表达式计算或者进行大小判断

?

1	select id from t where num/2=100

如where 子句中使用!=或<>操作符或者对字段进行NULL判断，否则将引擎放弃使用索引而进行全表扫描。

 某些关键词的使用不当，如or/like/in/not in

where 子句中使用 or 来连接条件，也会导致索引失效。

?

1 2 3 4 5

select id from t where num=10 or num=20 可以这样查询： select id from t where num=10 union all select id from t where num=20

in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3

在使用 like 关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置索引才会起作用。