目录

MySQL的索引

索引的代价

  • 额外的存储空间

  • 插入和修改数据时,还需额外修改索引

适合建索引的场景

  • join 操作操作使用的列

  • where条件中使用的列

  • 需要经常进行范围搜查的列

  • 需要进行排序输出的列

全文索引

只有MySIAM引擎支持全文索引。

避免全表扫描

  • 避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

  • 能用 between 就不要用 in，in 和 not in 会导致全表扫描

  • 用 exists 代替 in 是一个好的选择

select num from a where num in(select num from b)
select num from a where exists(select 1 from b where num=a.num)

常见索引

主健索引

alter table 表名 add primary key (列名);

普通索引：很普通的索引：最基本的索引，没有任何约束限制
唯一索引：唯一性约束：与普通索引类似，但是具有唯一性约束
主键索引：唯一性约束+不允许为空：特殊的唯一索引，不允许有空值
一个表只能有一个主键索引，可以有多个唯一索引
主键可以与外键构成参照完整性约束，防止数据不一致
组合索引：将多个列组合在一起创建索引，可以覆盖多个列，复合索引遵循前缀原则
外键索引（很少用）：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作
全文索引（很少用）：MySQL自带的全文索引只能用于MyISAM，并且只能对英文进行全文检索
对字符串进行索引，应该制定一个前缀长度，可以节省大量的索引空间

like查询，%不能在前，否则全文索引失效

如果or前的条件中的列有索引，后面的没有，索引都不会被用到

查询索引

show index from 表名;
show keys from 表名;

普通索引

create index 索引名 on 表 (列1);

-- 注意1:前缀索引一般只能用于普通索引当中,不能使用在unique当中,如果强行unique中索引有可能无法被使用上
-- 注意2:前缀索引只支持英文和数字,一般使用场景在网站中的用户注册模块,因为用户名注册多用邮箱和手机号码为主
-- 查询索引的长度
select length(old_kefuid) from xw_user_applyloan LIMIT 1;
-- 查询索引长度是5的时候的数据重复次数
select left(old_kefuid,4) as 左边5位2长度,count(*) as 总数 from xw_user_applyloan GROUP by left(old_kefuid,4);
-- 设置前缀索引
alter table xw_user_applyloan add index uname( old_kefuid(4) )

唯一索引

ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);

组合索引

alter table 表名 add index 索引名 (列1,列2);

必须使用列1作为起始查询列，组合索引才会生效。否则，退化成全表扫描。

全文索引

优化

group by 分组后，还会排序，可能会降低速度。

select * from emp group by deptno order by null; // 禁止排序

B-Tree索引三大优点

  • 索引大大减少服务器扫描的数据量

  • 索引帮助服务器避免排序和临时表

  • 索引可以减少随机I/O变为顺序I/O

索引是否为最好的解决方案？

  • 非常小的表，大部分情况全表扫描更高效

  • 中到大型表，索引非常有效

  • 特大表【TB级别】，可使用分区技术，块级元数据技术替换索引