MySql Index索引使用注意

        MySql Index索引使用注意事项

use exercise_linux;
-- 索引使用
-- 最左前缀法则（联合索引才遵循）
-- 如果使用了多列（联合索引），要遵守最左前缀法则；最左前缀法则是指查询从索引的最左列开始，并且不能跳过索引中的
-- 任何一列；若跳过了某一列，那么索引将部分失效（跳过那列之后的字段的索引将失效）

-- 查询一张表中存在的索引
show index from tb_user;     -- 发现profession、age、status构成一个联合索引
-- 案例
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
-- 可见其type是ref，说明使用了索引，而确实也使用了索引
explain select * from tb_user where profession = '软件工程' and age = 31;
-- 只根据两个条件查询，但中间没有跳过任何一列，所以说type仍然是ref，实际上仍然使用了索引
explain select * from tb_user where profession = '软件工程';
-- 只根据两个条件查询，但中间没有跳过任何一列，所以说type仍然是ref，实际上仍然使用了索引
explain select * from tb_user where age = 31 and status = '0';
-- type变成all，说明是全表查询，没有使用索引，因为跳过了profession这一列，但是age和status都在其之后，所以说其索引失效
explain select * from tb_user where status = '0';
-- type变成all，说明是全表查询，没有使用索引，因为跳过了profession这一列，但是age和status都在其之后，所以说其索引失效

-- 注：即使不按照索引关联顺序，并不会影响到索引，字段只需要存在
explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程';



-- 范围查询
-- 在联合索引中，若出现范围查询<, >，范围查询右侧的列索引失效
explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';
-- 用了索引，但是根据key_len可以判断，status的索引并没有使用，因为在查询age时用了范围查询，所以说其右侧索引失效
explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';
-- 虽然范围查询可以使范围查询右侧的索引失效，但是≥和≤不这种范围查询不会使右侧索引失效，所以说只要业务允许，在范围查询时应该使用≥这种



-- 索引列运算
-- 不要在索引列上进行运算操作，否则索引将失效
explain select * from tb_user where phone = '17799990015';
-- type为const 且 使用了索引
explain select * from tb_user where substring(phone, 10, 2) = '15';
-- type为all 且 没有使用索引，因为在索引上使用了substring进行函数运算，所以说索引失效了

-- 字符串要加引号
-- 字符串类型字段使用时，若不加引号，索引将失效
select * from tb_user where phone = 17799990015;
-- phone是varchar类型的字符串，但是查询时不加引号也可以正确的查询
explain select * from tb_user where phone = 17799990015;
-- 但是type是all，是全表查询，索引失效了，所以说字符串类型的字段使用时必须严格加上双引号



-- 模糊查询
-- 若是尾部进行模糊匹配，索引不会失效；若头部进行模糊匹配，则索引失效
-- 进行尾部模糊匹配，索引没有失效
explain select * from tb_user where profession like '软件%';
-- 进行头部模糊匹配，索引失效
explain select * from tb_user where profession like '%工程';
-- 所以说在大数据量的情况下，一定要避免头部的模糊匹配，否则会极大影响效率



-- or连接的条件
-- 使用or分割开的条件，若or前的条件中的条目有有索引，而后面的条目中没索引，那么不会使用任何的索引，索引将失效
-- 必须要or前后的条件的条目中均存在索引，才可以使用索引
explain select * from tb_user where id = 10 or age = 23;
-- 这条SQL不会使用索引，因为id有索引；而age没有索引（age有联合索引，但是左侧的条目被跳过了，根据最左前缀法则，age索引失效）

explain select * from tb_user where id = 10 or profession = '软件工程';
-- 这条SQL就有索引了，因为id有索引，profession有联合索引，且没有跳过条目，or前后的条件的条目中均存在索引，所以说索引生效



-- 数据分布影响——可以理解为，若查找出来的数据是大部分的，则不会使用索引（全表扫描更快），查找出来的数据是少部分的，则会使用索引
-- 若MySql评估使用索引比全表扫描更慢，则不使用索引
explain select * from tb_user where phone >= '17799990000';
select * from tb_user where phone >= '17799990000';
-- 因为用该条件去查找数据，所有数据都会被找到，所以说MySql评估全表扫描的速度优于使用索引，所以说不会使用索引



-- Sql提示，若字段有多个索引，可以通过Sql提示选择索引使用或选择索引不用
-- Sql提示，是优化数据库的一个重要手段，简单而言，就是在Sql语句中加入一些人为的提示来达到优化操作的目的

create index idx_user_pro on tb_user(profession);

-- use index ：提示Sql要用哪一个索引
-- 注意：use index 只是一个建议，MySql评估效率之后可能不会接受你的建议
-- explain select * from tb_user use index(index_name) where ... ;
explain select * from tb_user where profession = '软件工程';
-- 可能的索引列表中有profession条目所有的索引，然后会根据MySql的评估来选择一个索引使用
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程'; -- 指定了之后
-- 可能的索引列表中只有指定的索引，成功指定了索引

-- ignore index ：提示Sql要忽略（不要用）哪一个索引
-- explain select * from tb_user ignore index(index_name) where ... ;
explain select * from tb_user ignore index(idx_user_pro_age_sta, idx_user_pro) where profession = '软件工程';
-- ignore index()，忽略了指定的索引，MySql就不会在被忽略的索引中选择了

-- force index ：强制Sql必须用哪一个索引，不是建议，是强制！
-- explain select * from tb_user force index(index_name) where ...;
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';
-- force 强制MySql必须使用指定的索引

explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

-- 覆盖索引
-- 在查询中尽可能的使用覆盖索引（查询使用了索引，并且需要返回的列在该索引中能够全部找到），其目的是为了减少回表查询
-- 同样，为了减少回表查询，尽量不要使用select*

-- 一个避免了回表查询的Sql语句
select id, profession, age, status from tb_user where profession = '软件工程' and age= 31 and status = '0';
explain select id, profession, age, status from tb_user where profession = '软件工程' and age= 31 and status = '0';

-- 一个导致了回表查询的Sql语句
select name, id, profession, age, status from tb_user where profession = '软件工程' and age= 31 and status = '0';
-- 因为返回的列在索引中无法全部找到，所以说需要回表查询，效率降低



-- 前缀索引
-- 当字段类型为字符串（varchar，text等）时，有时候需要很长的字符串，这会使得索引变得很大，查询时浪费IO，影响查询效率
-- 那么此时可以只将字符串的一部分前缀建立索引，可以极大节约索引空间，从而提高效率

-- 前缀长度：可以根据索引的选择性决定，选择性是指：不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询
-- 效率越高，唯一索引的选择性是1，这是最好的、性能最好的索引
-- select count(distinct email) / count(*) from tb_user;
-- select count(distinct substring(email, 1, 5)) / count(*) from tb_user;
-- 不断的尝试算选择性，越接近于1越好，但是也需要考虑前缀的长度，二者是成反比的，前缀越短，效率高了；选择性低了



-- 单列索引和联合索引的选择问题
-- 在业务场景，如果存在多个查询条件，考虑针对查询字段建立索引时，建议建立联合索引，而非单列索引，可以避免回表查询

-- 多表联合查询时，MySql优化器会评估哪个字段的索引效率更高，会选择效率高的索引完成本次查询


-- 索引设计的原则

-- 1.针对数据量大（至少得万级），且查询频繁的表建立索引

-- 2.针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引

-- 3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高（性别这种列的区分度就十分低）

-- 4.如果是字符串类型的字段，字段的长度较长，建议根据字段的特点，建立前缀索引

-- 5.尽量使用联合索引，减少单列索引的使用，因为在查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表查询

-- 6.要控制索引的数量，并非索引越多越好，索引多了之后，维护索引的代价也就越大，会影响效率

-- 7.若索引列不能存储null值，那么在创建表时就应该用not null约束，优化器若知道每列是否包含null值，可以更好的确定用哪个索引有效查询