create table table_name(
	[col_name data type]
    [unique|fulltext][index|key] [index_name](col_name[length]) [asc|desc]
)

show index from table_name [\G] -- 如果是在cmd窗口，可以换行

-- 索引名字可以不写，不写默认使用列名
alter table 表名 add index/unique/fulltext 索引名(列名); 

create index 索引名 on 表名(列值)

alter table 表名 drop index 索引名字。

drop table if exists index_test;
create table index_test(
    id int(7) primary key,
    a int(7),
    b int(7),
    c varchar(20),
    d varchar(20)
);

insert into index_test values(1,100,10,'aaa','A');
insert into index_test values(2,300,30,'aba','BB');
insert into index_test values(3,200,20,'caa','CC');
insert into index_test values(4,100,10,'daa','DD');
insert into index_test values(5,500,50,'aad','FF');

-- 给a列单独建立一个索引：普通索引，非聚簇索引
create index index_test_a_index on index_test(a);
-- 查询index_test的索引
show index from index_test;
-- 删除索引
alter table index_test drop index index_test_a_index;
-- 创建一个组合索引：非聚簇索引
create index index_test_abc on index_test(a,b,c);

-- 给定一个索引长度 key_len 列的数据类型应该是字符串类型
create index index_test_a_index on index_test(d(1));

alter table 表名 drop index 索引名;

alter table 表名 drop primary key;

explain select * from index_test where id = 4;

mysql> explain select * from index_test where id = 4;
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | index_test | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

drop table index_test;
create table index_test(
    id int(7) primary key,
    a int(7),
    b int(7),
    c varchar(20),
    d varchar(20)
);
insert into index_test values(1,100,10,'aaa','A');
insert into index_test values(2,300,30,'aba','BB');
insert into index_test values(3,200,20,'caa','CC');
insert into index_test values(4,100,10,'daa','DD');
insert into index_test values(5,500,50,'aad','FF');

-- 举例：创建复合索引
create index index_test_abc on index_test(a,b,c);

-- a,b,c都是走了索引的
explain select * from index_test where a = 100 and b = 10 and c='aaa';

-- a列走了索引,但是c列没有走索引,原因是跳过了复合索引的b列
explain select * from index_test where a=100 and c='aaa';

-- a列和b列都是走了索引的,并且它们是连续的.
explain select * from index_test where a=100 and b=10;

-- 最左原则
-- 索引完全失效,不符合最左匹配原则.where最左边的检索列不是复合索引的第一个列a

-- 原因同上
explain select * from index_test where b=10;//没有走索引

explain select * from index_test where a=100;//走了索引

explain select * from index_test where c='aaa';//没有走索引

-- a列和b列是走了索引的,但是c列没有走索引.因为c列是范围之后的判断
explain select * from index_test where a=100 and b>10 and c='aaa';

-- like '%'出现在末尾,仍然a,b,c都是走索引
explain select * from index_test where a=100 and b=10 and c like 'a%';

-- 只有a,b是走了索引的,c是没有走索引的
explain select * from index_test where a=100 and b=10 and c like '%a';

-- 只有a,b是走了索引的,c是没有走索引的
explain select * from index_test where a=100 and b=10 and c like '%a%';

-- 索引列套在函数中使用,将会导致索引失效
explain select * from index_test where abs(id)=1;

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | index_test | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 索引失效
explain select * from index_test where id+5>7;

-- 应用场景:
explain select * from index_test where months>5*12;

-- is null(走索引)和is not null(不走索引)
explain select * from s_emp where commission_pct is not null;
-- is not null是不支持索引的
-- in(走了索引)  not in(不走索引)

select * from `user` where user_id = 1 or user_id = 2;

select * from `user` where user_id = 1 or age = 20;

select * from `user` where user_id = 1 or age = 20;

select * from `user` where user_id = 12;

select * from `user` where CAST(user_id AS signed int) = 12;

select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;