MySQL索引
1.索引类型:
主键索引(primary key):列值唯一,且不允许为空,主键可以有多个列组成。
唯一索引(unique index):列值唯一,允许为空。
普通索引:可以创建在任何一个列上,没有任何限制。
组合索引:多个列做成的组合索引,遵循”最左前缀“原则。
全文索引(fulltext key):5.6版本以后支持全文索引。
2.主键索引:创建主键就是创建主键索引
PRIMARY KEY 约束来创建主键。一个表只能有一个 PRIMARY KEY 约束
CREATE TABLE student(
id INT UNSIGNED COMMENT '无符号' PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32) NOT NULL DEFAULT ' ');
主键索引(创建表后添加)
CREATE TABLE student(
id INT,
NAME VARCHAR(32) NOT NULL DEFAULT ' ');
alter table student add primary key(id)
联合主键创建的两种方法
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
CREATE TABLE `t_accounts` ( `F_user_id` bigint(20) unsigned NOT NULL DEFAULT '0', `F_sub_account_type` smallint(5) unsigned NOT NULL DEFAULT '1', `F_currency` tinyint(3) unsigned NOT NULL DEFAULT '1', `F_enabled` tinyint(3) unsigned DEFAULT '1', `F_create_time` datetime NOT NULL, `F_modify_time` 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、普通索引 CREATE INDEX indexName ON mytable(username(length)); 创建表的时候直接指定: CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );
5.唯一索引:
创建索引: 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.全文索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):
7. 索引建立
频繁更新或者唯一性太差的字段不能建立索引
8. 索引失效
违反最左原则
对索引进行表达式计算或者进行大小判断
select id from t where num/2=100
如where 子句中使用!=或<>操作符或者对字段进行NULL判断,否则将引擎放弃使用索引而进行全表扫描。
某些关键词的使用不当,如or/like/in/not in
where 子句中使用 or 来连接条件,也会导致索引失效。
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 关键字进行查询的查询语句中,如果匹配字符串的第一个字符为“%”,索引不会起作用。只有“%”不在第一个位置索引才会起作用。