MySQL|索引应用例子

2018-03-12 温格

可以先看下这篇理论介绍:

MySQL|索引背后

01

MySQL的几种KEY

PRIMARY KEY 有两个作用,一是约束作用(constraint),用来规范一个存储主键和唯一性,但同时也在此key上建立了一个主键索引; 每个表都应该有一个主键,并且每个表只能有一个主键。

UNIQUE  KEY 与   PRIMARY KEY相似,只不过每个表可以有多个主键。

FOREIGN KEY 也是在这个key上建立一个index  ,如下所示:    FOREIGN KEY (emp_no)  REFERENCES employees (emp_no)    ON DELETE CASCADE,表明此表的emp_no引用的是employees表的emp_no,同时 ON DELETE CASCADE 指明一旦删除employees的某emp_no的记录,相应也会在这个表中删除这条记录。

02

employees示例库

看这个概念前,先介绍一个实例库。

导入MySql的employees库,下载地址:

https://dev.mysql.com/doc/employee/en/employees-installation.html

解压缩后,找到 employees.sql,注意最后几行的source ***.dump,在往数据库中导入时候,最后指定它的绝对路径,然后导入。

这个库包括6张表,关系如下:

03

索引index

看下titles表里面的索引,

SHOW INDEX FROM employees.titles;

titles表的主索引为<emp_no, title, from_date>

titles表的行数为

443307

04

全列匹配

查看严格按照索引组合的查询解释:

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10002' AND title='Staff' AND from_date='1996-08-03';

很明显,当按照索引中所有列进行精确匹配(这里精确匹配指“=”或“IN”匹配)时,索引可以被用到。

05

最左前缀匹配

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10002';

条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARY索引,但是key_len为4,说明只用到了索引的第一列前缀。

06

缺失最左前缀匹配

EXPLAIN SELECT * FROM employees.titles WHERE title='Staff' AND from_date='1996-08-03';

由于不是最左前缀,这样的查询显然用不到索引。

07

范围查询

EXPLAIN SELECT * FROM employees.titles WHERE emp_no<'10010'

最左前缀的 范围列可以用到索引,范围索引只能有一个。

08

每张表都得建索引?

答案是否定的。因为添加索引需要付出相应的代价,索引文件本身要消耗存储空间,同时索引会加重插入、删除和修改记录时的负担。一般两种情况下不建议建索引。

第一种情况是表记录比较少,例如一两千条甚至只有几百条记录的表,没必要建索引,让查询做全表扫描就好了。

另一种索引的选择性较低时不需要建立索引。所谓索引的选择性(Selectivity),是指 不重复的索引值 (也叫基数, Cardinality )与 表记录数(#T) 的比值:

Index Selectivity = Cardinality / #T

如下所示为employees的cardinality:

09

根据选择性建立索引

SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;

得到的选择值为:0.9313

但是这样的索引组合可能导致键值过长。

取last_name的前4个字符:

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;

得到的选择值为:0.900

已经不错了。

没有添加组合索引前的查询:

SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido'

耗时:0.609s

添加组合索引:

ALTER TABLE employees.employees ADD INDEX `first_name_last_name4` (first_name, last_name(4));

再次查询,耗时:0.0009s

前缀索引兼顾索引大小和查询速度,但是其缺点是不能用于ORDER BY和GROUP BY操作,也不能用于Covering index。

参考文章:

http://blog.codinglabs.org/articles/theory-of-mysql-index.html

以上,索引的应用实例,如果有疏漏,请指导!

算法channel会有系统地,认真地推送:基础算法/机器学习/深度学习/spark/tensorflow等全栈内容。期待您的参与!QQ交流群: 646901659  或进入公众号界面->导读系列下,进入微信讨论群。