MySql系列 (二): MySql索引
1、什么是索引?
索引可以理解为字典的音序表一样,可以根据它来查找匹配的数据项。
实际上索引是一种对数据表的一列或多列字段进行排序的数据结构,常见的索引数据结构就是B+树(什么是b+树?)。
2、索引有什么用?
索引主要是启提高数据查询的效率,帮助我们快速的在大量数据中定位到我们要找的数据。
简单的例子:下面这张表有4个字段,主键id、会员id、优惠券模板id、优惠券code,整张表大概有72万条左右的数据,大体数据分布: 一个优惠券模板id对应100到10000条优惠券code,优惠券id对应的code是顺序分布的(这种数据的分布结构可以减少磁盘操作的耗时)。
id | member_id | coupon_template_id | ticket_code |
---|---|---|---|
1 | 100000008 | 18 | 049B9K |
2 | 100000008 | 19 | 059G9K |
3 | 100000001 | 22 | 059G8D |
4 | 000000010 | 30 | 078G8D |
5 | 100000012 | 80 | 078G6D |
现在表中只有一个主键id,别的没有建索引:
SELECT ticket.ticket_code
FROM mkt_coupon_deliver_task_ticket ticket WHERE ticket.coupon_template_id = #{templateId}
查出100条code耗时:0.354秒
给coupon_template_id字段建一个普通索引:
查出100条code耗时:0.057秒
简单来说,通过索引我们能减少所需要的扫描的数据条数,减少耗时。
3、怎么建索引?
添加索引:
- CREATE INDEX idx_coupon_template_id ON mkt_coupon_deliver_task_ticket (coupon_template_id);
- ALTER TABLE mkt_coupon_deliver_task_ticket ADD INDEX idx_coupon_template_id (coupon_template_id);
删除索引:
- DROP INDEX idx_coupon_code ON mkt_coupon_deliver_task_ticket;
- ALTER TABLE mkt_coupon_deliver_task_ticket DROP INDEX idx_coupon_template_id;
索引还可以分为:唯一索引、普通索引、全文索引。一般命名规则就是唯一加上'uk'、普通加上'idx'来做索引的命名开头。
一个索引如果包含多个字段,叫做组合索引,组合索引生效根据最左前缀原则。
4、索引的原理(b+树)
现在的程度感觉还讲不清楚,留后面补充...
5、回表 、索引覆盖
回表:
当我们的SQL使用到索引的时候,会先根据索引字段查找出索引树满足条件的叶子节点 (索引树(非主键)的叶子节点包含索引字段和主键id),查找到目标数据的id后,还需要去主键id的索引树 (索引树(主键)的叶子节点包含一行的所有数据),获取select后面的其余字段。
索引覆盖:
索引覆盖是不会有回表这个操作,当你要select的字段都在索引树能找到的时候,是不必再回到主键索引树查找其他字段的,根据这个特性,可以适当的创建组合索引。
6、索引带来的性能影响
空间的占用增加:索引树的数据结构肯定要花费多余的空间来进行存储。
删除、修改操作的耗时增加:索引虽然大大减少了查询的耗时,但是修改等操作要同时修改索引树,会增加耗时。