一、索引类型:
1、unique(唯一索引):不可以出现相同的值,可以有NULL值
2、index(普通索引):允许出现相同的索引内容
3、promary key(主键索引):不允许出现相同的值
4、fulltext index(全文索引):可以针对值中的某个单词
组合索引:实质上是将多个字段建到一个索引里,列值的组合必须唯一
二、创建索引
alter table 表名 add 索引类型 (unique,primary key,fulltext,index)[索引名](字段名)
//普通索引
alter table table_name add index index_name (column_list) ;
//唯一索引
alter table table_name add unique (column_list) ;
//主键索引
alter table table_name add primary key (column_list) ;
alter table可用于创建普通索引、UNIQUE索引和PRIMARY KEY索引3种索引格式,table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以同时创建多个索引。
三、组合索引与前缀索引
组合索引
alter table test add index id_name (user_id,user_name(16),team_id)
建表时,user_name长度为100,这里用16,是因为一般情况下名字的长度不会超过16,这样会加快索引查询速度,还会减少索引文件的大小,提高INSERT,UPDATE的更新速度
如果分别给user_id,user_name,team_id建立单列索引,让该表有3个单列索引,查询时和组合索引的效率是大不一样的,甚至远远低于我们的组合索引。虽然此时有三个索引,但mysql只能用到其中的那个它认为似乎是最有效率的单列索引,另外两个是用不到的,也就是说还是一个全表扫描的过程
建立这样的组合索引,就相当于分别建立如下三种组合索引:
user_id,user_name,team_id
user_id,user_name
user_id
为什么没有CITY,AGE等这样的组合索引呢?这是因为mysql组合索引“最左前缀”的结果。简单的理解就是只从最左边的开始组合,并不是只要包含这三列的查询都会用到该组合索引。也就是说name_city_age(user_id,user_name,team_id)从左到右进行索引,如果没有左前索引,mysql不会执行索引查询
前缀索引
如果索引列长度过长,这种列索引时将会产生很大的索引文件,不便于操作,可以使用前缀索引方式进行索引,前缀索引应该控制在一个合适的点,控制在0.31黄金值即可(大于这个值就可以创建)。
SELECT COUNT(DISTINCT(LEFT(title
,10)))/COUNT(*) FROM Arctic; – 这个值大于0.31就可以创建前缀索引,Distinct去重复
ALTER TABLE user
ADD INDEX uname
(title(10)); – 增加前缀索引SQL,将人名的索引建立在10,这样可以减少索引文件大小,加快索引查询速度
四、校验sql是否走索引
explain sql语句
EXPLAIN SELECT * FROM test WHERE user_name = ‘we’