Chinaunix首页 | 论坛 | 博客
  • 博客访问: 457565
  • 博文数量: 99
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 1177
  • 用 户 组: 普通用户
  • 注册时间: 2016-09-22 09:50
  • 认证徽章:
个人简介

一个守望数据库的老菜鸟

文章分类

全部博文(99)

文章存档

2019年(20)

2018年(17)

2017年(38)

2016年(24)

我的朋友

分类: Mysql/postgreSQL

2018-11-22 16:22:56

博客文章除注明转载外,均为原创。转载请注明出处。
本文链接地址:http://blog.chinaunix.net/uid-31396856-id-5793386.html


我们在书写SQL语句的时候,总是希望sql语句使用索引进行高效查询;我们在优化SQL的时候,也希望使用索引降低IO提高效率,那么MySQL是怎样使用索引的呢,MySQL遵从B+Tree的最左前缀原理。
一、索引的最左匹配原理。
 当B+Tree的数据项是复合的数据结构,比如(name,age,sex)的时候,b+数是按照从左到右的顺序来建立搜索树的,(name,age,sex),相当于(name)单列搜素,(name,age)组合索引以及(name,age,sex)组合索引。
二、 key_len介绍
常通过执行计划中 key_len列来衡量索引的利用情况,key_len 用于表示本次查询中,所选择的索引长度有多少字节,通常我们可借此判断联合索引有多少列被选择了。key_len 大小的计算规则是:
一般地,key_len 等于索引列类型字节长度,例如int类型为4 bytes,bigint为8 bytes;
如果是字符串类型,还需要同时考虑字符集因素,例如:CHAR(30) UTF8则key_len至少是90 bytes;
若该列类型定义时允许NULL,其key_len还需要再加 1 bytes;
若该列类型为变长类型,例如 VARCHAR(TEXT\BLOB不允许整列创建索引,如果创建部分索引也被视为动态列类型),其key_len还需要再加2bytes;
三、MySQL索引类型
 可以按照索引的列数,把MySQL索引分为单列索引和多列索引。MySQL中的索引可以以一定顺序引用多个列,就是联合索引。联合索引属于多列索引,也可以把单列索引看到列数为一的特殊的多列索引。
四、索引的使用
以employees.titles表为例,我们来下面先查看的建表语句
(root@localhost:)[employees]> show create table employees.titles\G
*************************** 1. row ***************************
       Table: titles
Create Table: CREATE TABLE `titles` (
  `emp_no` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`title`,`from_date`),
  CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
表employees.titles上都有哪些索引:
(root@localhost:)[employees]> show index from employees.titles;


1、全列匹配
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';

进行精确匹配的时候(=或者in),使用索引,并且都是常量。而且不受条件顺序的影响


2、最左前缀匹配

条件为联合索引的第一列,SQL使用到联合索引。可以看出(`emp_no`,`title`,`from_date`)其实等于单列索引(`emp_no`)

3、查询条件用到了索引中列的精确匹配,但是没有部分索引列条件
(root@localhost:)[employees]> 
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26';

因为条件中没有索引的第二列,因此只用到索引的第一列。
考虑到使用索引的效率,并且结合实际情况title只有7个值,因此可以改写为如下SQL语句提高性能。
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001'
    ->        AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager')
    ->        AND from_date='1986-06-26';

可以看到完全使用到索引,思考提高了多少性能呢?

4、查询条件中没有用到索引的第一列
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26';

不满足最左前缀,无法使用到索引。oracle有index skip scan的说法,但是性能也很差。

5、匹配索引某列的前缀字符串
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';

可以看到用到索了引,如果通配符%不出现在开头,则可以用到索引。

6、范围查询
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles WHERE emp_no < '10010' and title='Senior Engineer';

再看,
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles
    ->        WHERE emp_no < 10010
    ->        AND title='Senior Engineer'
    ->        AND from_date BETWEEN '1986-01-01' AND '1986-12-31';

对联合索引进行范围扫描的时候,无法使用到接下来的索引列。
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles
    ->        WHERE emp_no BETWEEN '10001' AND '10010'
    ->        AND title='Senior Engineer'
    ->        AND from_date BETWEEN '1986-01-01' AND '1986-12-31';


可以看到BETWEEN '10001' AND '10010',这是因为BETWEEN ... AND ... 的作用相当于“IN”,IN是进行精确过滤匹配。

7、查询条件中含有函数或表达式
表达式情况下:
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles WHERE emp_no - 1='10000';

看函数的情况下
(root@localhost:)[employees]> EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND left(title, 6)='Senior';


8、覆盖索引
条件和select查询的数据都在索引里,直接从索引中取得结果,不需要做回表操作。
(root@localhost:)[employees]> EXPLAIN SELECT emp_no,from_date,title FROM employees.titles WHERE emp_no>10001;

可以看到执行计划中表示的sql执行中Using index 。
(root@localhost:)[employees]> EXPLAIN SELECT emp_no,from_date,title,to_date FROM employees.titles WHERE emp_no>10001;

显然,无法直接从索引获取to_date的数据。
以上列举了MySQL使用索引对SQL执行计划的影响,合理的使用好索引会大大的提高sql的性能。

---The End
阅读(1854) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册