# 1、分类
#  　　MySQL索引分为普通索引、唯一索引、主键索引、组合索引、全文索引。索引不会包含有null值的列，索引项可以为null（唯一索引、组合索引等），
#     但是只要列中有null值就不会被包含在索引中。
#     （1）普通索引：create index index_name on table(column)；
#     或者创建表时指定，create table(..., index index_name column);
#     （2）唯一索引：类似普通索引，索引列的值必须唯一（可以为空，这点和主键索引不同）
#     create unique index index_name on table(column)；或者创建表时指定unique index_name column
#     （3）主键索引：特殊的唯一索引，不允许为空，只能有一个，一般是在建表时指定primary key(column)
#     （4）组合索引：在多个字段上创建索引，遵循最左前缀原则。alter table t add index index_name(a,b,c);
# 　　最左前缀原则：https://mp.weixin.qq.com/s/RemJcqPIvLArmfWIhoaZ1g
#     （5）全文索引：主要用来查找文本中的关键字，不是直接与索引中的值相比较，像是一个搜索引擎，配合match against使用，现在只有char，
#     varchar，text上可以创建全文索引。在数据量较大时，先将数据放在一张没有全文索引的表里，然后再利用create index创建全文索引，
#     比先生成全文索引再插入数据快很多。

# 修改 alter mytable add[unique] index[indexname]on(columnname(length))
# 删除 drop index [indexName] on mytable
# 查看 show index from table_name\G

# 从另外的角度还可以分为
# 1、聚集索引。
# 　　表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子结点即存储了真实的数据行，
# 不再有另外单独的数据页。 在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种。
# 2、非聚集索引。
# 　　表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。

# 2、使用
# 2.1、何时使用索引

#     MySQL每次查询只使用一个索引。与其说是“数据库查询只能用到一个索引”，倒不如说，和全表扫描比起来，去分析两个索引B+树更加耗费时间。
#     所以where A=a and B=b这种查询使用（A，B）的组合索引最佳，B+树根据（A，B）来排序。
#     （1）主键，unique字段；
#     （2）和其他表做连接的字段需要加索引；
#     （3）在where里使用＞，≥，＝，＜，≤，is null和between等字段；
#     （4）使用不以通配符开始的like，where A like 'China%'；
#     （5）聚集函数MIN()，MAX()中的字段；
#     （6）order by和group by字段；
# 2.2、何时不使用索引
# 　  （1）表记录太少；
#     （2）数据重复且分布平均的字段（只有很少数据值的列）；
#     （3）经常插入、删除、修改的表要减少索引；
#     （4）text，image等类型不应该建立索引，这些列的数据量大（假如text前10个字符唯一，也可以对text前10个字符建立索引）；
#     （5）MySQL能估计出全表扫描比使用索引更快时，不使用索引；

# 2.3、索引何时失效
#     （1）组合索引未使用最左前缀，例如组合索引（A，B），where B=b不会使用索引；
#     （2）like未使用最左前缀，where A like '%China'；
#     （3）搜索一个索引而在另一个索引上做order by，where A=a order by B，只使用A上的索引，因为查询只使用一个索引 ；
#     （4）or会使索引失效。如果查询字段相同，也可以使用索引。例如where A=a1 or A=a2（生效），where A=a or B=b（失效）
#       注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
#     （5）如果列类型是字符串，要使用引号。例如where A='China'，否则索引失效（会进行类型转换）；
#     （6）在索引列上的操作，函数（upper()等）、or、！=(<>)、not in等；

# others
# 1) 没有查询条件，或者查询条件没有建立索引
# 2) 在查询条件上没有使用引导列
# 3) 查询的数量是大表的大部分，应该是30％以上。
# 4) 索引本身失效
# 5) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等) 错误的例子：select * from test where id-1=9; 正确的例子：select * from test where id=10;
# 6) 对小表查询
# 7) 提示不使用索引
# 8) 统计数据不真实
# 9) CBO计算走索引花费过大的情况。其实也包含了上面的情况，这里指的是表占有的block要比索引小。
# 10)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效. 错误的例子：select * from test where tu_mdn=13333333333; 正确的例子：select * from test where tu_mdn='13333333333';
# 12) 1,<> 2,单独的>,<,(有时会用到，有时不会)
# 13,like "%_" 百分号在前.
# 4,表没分析.
# 15,单独引用复合索引里非第一位置的索引列.
# 16,字符型字段为数字时在where条件里不添加引号.
# 17,对索引列进行运算.需要建立函数索引.
# 18,not in ,not exist.
# 19,当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。
# 20,B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走
# 21,联合索引 is not null 只要在建立的索引列（不分先后）都会走, in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,其他建立索引的列可以是is null（但必须在所有列 都满足is null的时候）,或者=一个值； 当建立索引的第一位置是=一个值时,其他索引列可以是任何情况（包括is null =一个值）,以上两种情况索引都会走。其他情况不会走

# 3、其他
# 3.1、explain语句

# 如果在select语句前放上关键词explain，mysql将解释它如何处理select，提供有关表如何联接和联接的次序。
# explain属性：
# id:select查询的序列号
# select_type:select查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询
# table: 输出的行所引用的表
# type:
# 联合查询所使用的类型，type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
# system > const > eq_ref > ref >fulltext > ref_or_null > index_merge > unique_subquery >index_subquery >
# range > index > ALL
# 一般来说，得保证查询至少达到range级别，最好能达到ref。
#          type=const表示通过索引一次就找到了；
#          type=all,表示为全表扫描；
#          type=ref,因为这时认为是多个匹配行，在联合查询中，一般为REF；
# key:
# 显示MySQL实际决定使用的键。如果没有索引被选择，键是NULL。
#          key=primary的话，表示使用了主键；
#          key=null表示没用到索引。
# possible_keys:
# 指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，
# 或者检查字段不是适合索引。
# key_len:显示MySQL决定使用的键长度。如果键是NULL，长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。
# ref:显示哪个字段或常数与key一起被使用。
# rows：这个数表示mysql要遍历多少数据才能找到，在innodb上是不准确的。
# Extra:
# 如果是Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。
# 如果是where used，就是使用上了where限制。
# 如果是impossible where 表示用不着where，一般就是没查出来啥。

 

# 3.2、\g、\G
# \g 的作用是分号和在sql语句中写’;’是等效的
# \G 的作用是将查到的结构旋转90度变成纵向　

# 查看profiling
show variables like 'profiling';
# 启动profiling
set profiling =on;

# 4、例子
create table test(
id1 int ,
id2 int,
id3 int,
id4 int,
key index_id12(id1,id2)
);

# 用到索引
explain select * from test where id1 < 10;
# 用到索引
explain select * from test where id1 < 10 and id2 > 1;
# 用到索引
explain select * from test where id2 > 1 and id1 < 2;
# 未用到索引，组合索引要满足最左原则
explain select * from test where id2 > 1;
# 未用到索引
explain select * from test order by id1 desc ;
# 用到索引
explain select id1 from test order by id1 desc ;
explain select id1,id2 from test order by id1 desc ;
# 未用到索引
explain select id1,id2,id3 from test order by id1 desc ;

# 5常见面试问题
# 5.1、什么是最左前缀原则
# 5.2、为什么用 B+ 树做索引而不用哈希表做索引
# 5.3、主键索引和非主键索引有什么区别
# 5.4、为什么建议使用主键自增的索引
# 5.5、一条SQL语句执行得很慢的原因有哪些