MySQL|索引应用

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

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(alg-channel)

原文发表时间:2018-03-11

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏程序员历小冰

MySQL探秘(七):InnoDB行锁算法

 在上一篇《InnoDB一致性非锁定读》中,我们了解到InnoDB使用一致性非锁定读来避免在一般的查询操作(SELECT FOR UPDATE等除外)时使用锁。...

19410
来自专栏性能与架构

Mysql group by实现方式(一) - 临时表

当MySQL Query Optimizer无法找到可以利用的合适索引时,就不得不先读取需要的数据,然后通过临时表来完成GROUP BY操作 例如 EXPLAI...

30660
来自专栏我的博客

mysql创建临时表,将查询结果插入已有表中

        今天遇到一个很棘手的问题,想临时存起来一部分数据,然后再读取。我记得学数据库理论课老师说可以创建临时表,不知道mysql有没有这样的功能呢?临时...

67250
来自专栏java达人

oracle、mysql 分页查询比较

1、 Oracle的分页查询语句 分页查询格式: SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * ...

32480
来自专栏文渊之博

mysql表分区简述

数据库分区是一种物理数据库设计技术。虽然分区技术可以实现很多效果,但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间,同时对于应...

15430
来自专栏游戏杂谈

Node.js调用mysql的存储过程

例子仅在windows下测试通过,没有放在linux下测试。如有问题,可以电邮给我~

21110
来自专栏王磊的博客

MS SQL查询库、表、列数据结构信息汇总

前言 一般情况我们下,我们是知道数据库的表、列信息的(因为数据库是我们手动设计),但特殊情况下,如果你只能拿到数据库连接信息,也就是知道的一个数据库名的情况下,...

45040
来自专栏极客慕白的成长之路

MySQL从安装到使用

Columns 列;Indexes 索引;Views 视图;Events 事件;Fields 字段;

10540
来自专栏极客慕白的成长之路

SQL Sever基本知识

create table tablename(col_name1 type,col_name2 type,...) 在数据库school下创建一个student...

6020
来自专栏lgp20151222

技术无关 自己写的项目 一些数据库的想法

+----------+--------------+------+-----+---------+----------------+ | Field    |...

8730

扫码关注云+社区

领取腾讯云代金券