【MySQL | 第八篇】索引的使用

目录一、索引的使用规则1.最左前缀法则2.范围查询3.索引的失效情况3.1索引列运算3.2字符串不加引号3.3模糊查询3.4or连接的条件3.5数据分布影响4.SQL提示5.覆盖索引⭐⭐⭐⭐⭐6.前缀索引7.单列索引与联合索引二、索引的涉及原则一、索引的使用规则1.最左前缀法则如果索引了多列(联合索引)要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始并且不跳过索引中的列。如果跳跃某一列索引将部分失效(后面的字段索引失效)。联合索引带的字段explain select * from tb user where profession 软件工程 and age 31 and status 0; explain select * from tb_user where profession 软件工程 and age 31; explain select * from tb_user where profession 软件工程; explain select * from tb_user where age 31 and status 0; explain select * from tb_user where status 0;根据最左前缀法则从索引的最左列profession开始后面紧接着的是age、status如果将profession字段去掉直接按age、status或只根据status查询那么该索引就会失效。2.范围查询联合索引中出现范围查询()范围查询右侧的列索引失效。explain select * from tb_user where profession 软件工程 and age 30 and status0; explain select * from tb_user where profession 软件工程 and age 30 and status 0;第一个sql语句出现了范围查询语句因此尽管满足最左前缀法则依然会使右侧的列索引失效。因此在使用复合索引时如果业务允许的情况下尽量使用来进行判断。3.索引的失效情况3.1索引列运算不要在索引列上进行运算操作索引将失效。3.2字符串不加引号字符串类型字段使用时不加引号索引将失效。3.3模糊查询如果仅仅是尾部模糊匹配索引不会失效。如果是头部模糊匹配索引失效。尾部模糊匹配头部模糊匹配3.4or连接的条件用or分割开的条件如果or前的条件中的列有索引而后面的列中没有索引那么涉及的索引都不会使用。也就是如果要使用索引就要求使用or连接的前面以及后面涉及到的列必须都有索引。3.5数据分布影响如果MySQL评估使用索引比全表更慢则不使用索引。4.SQL提示SQL提示是优化数据库的一个重要手段简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。use index():explain select * from tb_user use index(idx_user_pro) where profession 软件工程;ignore index():explain select * from tb_user ignore index(idx_user_pro) where profession 软件工程;force index():explain select * from tb_user force index(idx_user_pro) where profession 软件工程;5.覆盖索引⭐⭐⭐⭐⭐尽量使用覆盖索引查询使用了索引并且需要返回的列在该索引中已经全部能够找到减少select *。using index condition查找使用了索引但是name字段需要回表查询数据。using whereusing indx查找使用了索引但是需要的数据都在索引列中能找到所以不需要回表查询数据。6.前缀索引当字段类型为字符串(varchartext等)时有时候需要索引很长的字符串这会让索引变得很大查询时浪费大量的磁盘IO影响查询效率。此时可以只将字符串的一部分前缀建立索引这样可以大大节约索引空间从而提高索引效率。语法CREATE INDEX idx_xxxx ON 表名(column(n));提取column字段的前n个字符作为前缀索引。前缀长度可以根据索引的选择性来决定而选择性是指不重复的索引值(基数)和数据表的记录总数的比值索引选择性越高则查询效率越高唯一索引的选择性是1这是最好的索引选择性性能也是最好的。select count(distinct email) / count(*) from tb_user; select count(distinct substring(email, 1, 5)) / count(*) from tb_user;7.单列索引与联合索引在业务场景中如果存在多个查询条件考虑针对于查询字段建立索引时建议建立联合索引而非单列索引。多条件联合查询时MySQL优化器会评估哪个字段的索引效率更好会选择这个效率更高的索引来完成本次查询而不会同时使用两个因此此次查询还是涉及了回表查询。创建联合索引时索引字段的顺序是有要求的如果我们要查询数据并且要求用到这个索引时必须满足最左前缀原则。二、索引的涉及原则1.针对于数据量较大且查询比较频繁的表建立索引。2. 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。3. 尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高。4.如果是字符串类型的字段字段的长度较长可以针对于字段的特点建立前缀索引。5.尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提高查询效率。6.要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价也就越大会影响增删改的效率。7. 如果索引列不能存储NULL值请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时它可以更好地确定哪个索引最有效地用于查询。