关于MySQL索引

为什么要用索引以及索引是怎么工作的

多数情况下,不使用索引,试图通过其他途径来提高性能,纯粹是浪费时间(出自《MySQL技术内幕》)。

那索引是怎么提高性能的呢?* 通过索引能获取数据的结束位置,从而跳过其他部分

  • 定位算法,可以快速定位第一个匹配值

InnoDB总是使用“B树”来创建索引,对于这种索引,在使用<, <=,=,=>,!=,BETWEEN操作符时有会很有效率。

Tip: BETWEEN在Django ORM对应range操作符。

import datetime

start_date = datetime.date(2005, 1, 1)

end_date = datetime.date(2005, 3, 31)

Entry.objects.filter(pub_date__range=(start_date, end_date))

相当于

SELECT ... WHERE pub_date BETWEEN '2005-01-01' and '2005-03-31';

怎么建立索引

1. 总结业务场景,分析出最常用的会在where中出现的字段

比如以我们的项目而言,instance_nameuser_id, check_date出现的频率最高,所以这三个字段肯定需要建立索引。通过这样的索引,可以避免全表查找。

2. 数据维度势

维度就是说表中容纳的非重复值的个数。我们尽量应该选择一些区分度高的,区分度=count(distinct col)/count(*),按照美团的博客来讲,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录

3. 不要滥用索引

  • 由于在写入数据时,不仅要求写到数据行,还会影响所有的索引。所以索引建立越多,就会导致写入速度越慢。

  • 索引会占据磁盘空间。

4. 为字符串的前缀编索引

索引可以减少索引空间,从而加快速度。

5. 复合索引

比如地址,Province, City,通过这两个值得组合来建立索引。

6. 最左前缀匹配

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

7. 不要把索引列加入计算

尽量不要在where中的“=”的左边,进行计算。

怎么查询

  1. 在where,order by语句中使用索引

  2. 避免在where中去使用数据维度势低的,比如sex,isDeleted等

  3. 如果是数字型字段,则使用数字类型

  4. 尽量不要使用!=, like或者>, <,引擎可能会进行全表搜索,考虑使用between,union等来替代。

使用Explain来优化SQL

我为了测试,在本机MySQL中,建了一张表。

mysql> create table if not exists front_end_team (id bigint auto_increment, gender tinyint not null default 0, name varchar(12) not null, age smallint not null, height decimal(6,2) not null, gmt_created datetime not null, gmt_modified datetime not null, primary key(id)) engine=innodb default charset utf8;

mysql> desc front_end_team;

+--------------+--------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------------+--------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| name | varchar(12) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

| height | decimal(6,2) | YES | | NULL | |

| gmt_created | datetime | NO | | NULL | |

| gmt_modified | datetime | NO | | NULL | |

| gender | tinyint(4) | YES | | NULL | |

+--------------+--------------+------+-----+---------+----------------+

7 rows in set (0.00 sec)

并往里面写了1w条数据。

mysql> select * from front_end_team limit 3;

+----+--------+--------+-----+--------+---------------------+---------------------+

| id | gender | name | age | height | gmt_created | gmt_modified |

+----+--------+--------+-----+--------+---------------------+---------------------+

| 1 | 0 | Tom | 32 | 154.32 | 2016-06-11 14:42:09 | 2016-06-11 14:42:09 |

| 2 | 0 | Sandro | 31 | 154.43 | 2016-06-11 14:42:28 | 2016-06-11 14:42:28 |

| 3 | 0 | Cgdali | 26 | 158.30 | 2016-06-11 14:42:49 | 2016-06-11 14:42:49 |

+----+--------+--------+-----+--------+---------------------+---------------------+

3 rows in set (0.00 sec)

mysql> show index from front_end_team\G

*************************** 1. row ***************************

Table: front_end_team

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: id

Collation: A

Cardinality: 10142

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.00 sec)

mysql>

这张表里面,目前只有主键建了索引。我安装的MySQL引擎是InnoDB,所以索引类型使用B树来实现。

好,目前为止,环境已经OK。来试试Explain吧。我们的目标就是不断优化Explain返回的rows字段。

先大概解释下Explain返回的字段名吧。

Column 意义
select_type select类型
table 展示行的table
type join类型
possible_keys 索引的可能取值
key 实际使用的索引
key_len 使用索引的长度
ref 跟索引
rows 关键指标
filtered
Extra

具体可参考MySQL Explain

# 全表查询

mysql> explain select * from front_end_team\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: front_end_team

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 10142

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.00 sec)

此时,key为NULL,没有使用任何索引。

mysql> explain select * from front_end_team where id between 1 and 10004\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: front_end_team

partitions: NULL

type: range

possible_keys: PRIMARY

key: PRIMARY

key_len: 8

ref: NULL

rows: 5071

filtered: 100.00

Extra: Using where

1 row in set, 1 warning (0.00 sec)

虽然这两句话干的都是同一件事,但是使用between已经让MySQL使用索引来查询了。rows锐减一倍。

我们来验证一下刚刚的数据维度势的问题。

mysql> explain select * from front_end_team where gender = 0\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: front_end_team

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 10142

filtered: 10.00

Extra: Using where

1 row in set, 1 warning (0.00 sec)

给gender加上索引,再试试。

mysql> create index gender on front_end_team (gender smallint);

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select * from front_end_team where gender = 0\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: front_end_team

partitions: NULL

type: ref

possible_keys: gender

key: gender

key_len: 1

ref: const

rows: 4995

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.00 sec)

这个地方挺出乎我意料的,我原以为这里MySQL会直接进行全表扫描。事实上,虽然gender的区分度很低,但是MySQL还是使用了这个索引。

截止目前,我们进行的都是单表查询,接下来看看多表的。

先来复习下left join,right join, inner join, outer join:

!SQL Joins

Ref:

  1. 美团点评团队

  2. MySQL技术内幕

发表评论

电子邮件地址不会被公开。 必填项已用*标注