可以先看下这篇理论介绍:
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
以上,索引的应用实例,如果有疏漏,请指导!
本文分享自 程序员郭震zhenguo 微信公众号,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ,欢迎热爱写作的你一起参与!