【SQL】MySQL进阶1:索引

1. 索引的类型

存储结构视角下:B+Tree索引,Hash索引,R-tree索引,Full-text索引
SQL语法视角下:主键索引(PRIMARY,创建主键约束时自动创建,只能一个),唯一索引(UNIQUE,可以多个),常规索引,全文索引(FULLTEXT)
InnoDB视角下:聚集索引(索引和数据放在一起,必须有且只能有一个),二级索引(索引和数据分开存放,可以有多个)
• 如果表未指定主键而指定了唯一约束,则对唯一约束列建立聚集索引。如果未指定主键,未指定唯一约束,则对默认的rowid列建立聚集索引。如果指定的主键,则对主键建立聚集索引。除了聚集索引外的索引是二级索引,二级索引叶子结点存放key和建立聚集索引的key值(一般是主键)。
• 查询过程:从二级索引查出主键,用主键在聚集索引中查出整行数据,这称为回表查询。
• 指针大小6byte,主键类型tinyint占8byte,一页大小16KB。设索引使用n阶B+tree,则一个索引页的情况是:8*(n-1)+6n=161024 --> n约为1171。只有一个索引页时B+tree深度为2,索引1171行数据(18736KB)。
在这里插入图片描述
存储引擎所支持的索引

存储引擎B+ treeHashR treeFull text
InnoDB支持不支持不支持支持(>=5.6)
MyISAM支持不支持支持支持
Memory支持支持不支持不支持

2. B+tree索引

2.1 前置概念

二叉树:根结点,左子树,右子树
二叉查找树/二叉搜索树/二叉排序树:二叉树,要求左孩子 <= 根 <= 右孩子
平衡查找二叉树/平衡二叉搜索树:二叉搜索树,要求左子树深度和右子树之差的绝对值小于2。AVL树,红黑树都是平衡查找二叉树。

2.2 使用B+tree的理由

非平衡二叉排序树作为索引的结构:平均情况O(logn),边界情况O(n)
红黑树作为索引的结构:一种平衡二叉树,每个结点只有一个数据,数据量大时,深度依然大
Btree作为索引的结构:一种多路平衡查找二叉树。对于n阶Btree,结点最多存放n-1个key,最多有n个子结点。结点存储key和数据,树的深度进一步降低。仅支持等值查找
Btree

B+tree作为索引的结构:Btree的叶子结点加链(范围查找)。内部结点存储只存储key(索引存放集中,查询效率高),叶子结点存储key和数据,一个叶子结点对应一个数据。
B+tree
Hash表作为索引的存储结构:hashValue = hashMap(key),只支持等值查找,不支持范围查找。当不同key经过hash函数映射到相同的hash值,则产生冲突,称之为hash碰撞。碰撞的解决办法:拉链法,开放地址法(线性探测,二次探测),多重哈希,建立溢出公共区。

3. 索引的语法

-- 创建索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name(col0, col1);

-- 查看索引
SHOW INDEX FROM table_name;

-- 删除索引
DROP INDEX index_name ON table_name;

4. 查看SQL语句执行性能

4.1 查看增删改查操作频次

为何需要关心操作频次:如果对数据的更新,插入删除操作居多,则不适合建立索引来进行优化;如果查询数据居多,则应该考虑使用索引进行优化。

SHOW [GLOABL|SESSION] STATUS LIKE 'Com%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 0     |
| Com_import    | 0     |
| Com_insert    | 0     |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 1     |
| Com_signal    | 0     |
| Com_update    | 0     |
| Com_xa_end    | 0     |
+---------------+-------+

4.2 查看执行耗时

4.2.1 达到指定时限的查询:慢查询

-- 查看慢查询是否开启(默认关闭)
SHOW VARIABLES LIKE "slow_query_log"-- 开启慢查询, my.cnf
[mysqld]
show_query_log = 1
long_query_time = 2  # 超过2s算慢查询

-- 查看慢查询日志:/var/lib/mysql/localhost-slow.log

4.2.2 未达到指定时限的查询:profile

-- 版本号大于或等于 5.0.37,理论支持
-- 查看profile设置
SELECT @@@profiling;
SHOW VARIABLES LIKE 'profiling';

-- 开启profile功能
SET [global] profiling = 1-- 查看当前会话执行的操作的耗时
SHOW PROFILES;

-- 查看指定查询的具体耗时情况
SHOW PROFILE FOR QUERY <queryID>;
-- 查看CPU计算耗时
SHOW PROFILE CPU FOR QUERY <queryID>;

-- 支持查询的类型
type: {
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS
}

4.3 查看执行计划

-- 语法
EXPLAIN SELECT col FROM table_name WHERE condition;

-- case1: 多表连接查询的执行计划
EXPLAIN SELECT c.cust_id, c.cust_name, o.order_num FROM Customers AS c LEFT OUTER JOIN Orders AS o ON c.cust_id = o.cust_id;
+----+-------------+-------+------------+------+---------------------+---------------------+---------+----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref            | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+----------------+------+----------+-------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL                | NULL                | NULL    | NULL           |    5 |   100.00 | NULL        |
|  1 | SIMPLE      | o     | NULL       | ref  | FK_Orders_Customers | FK_Orders_Customers | 40      | test.c.cust_id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+----------------+------+----------+-------------+

case2:子查询的执行计划
SELECT * FROM Customers AS cust WHERE cust.cust_id IN (
    SELECT o.cust_id FROM Orders AS o WHERE o.order_num IN (
        SELECT oi.order_num FROM OrderItems AS oi WHERE oi.quantity = 50
    )
);
+----+--------------+-------------+------------+--------+-----------------------------+---------------------+---------+---------------------+------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys               | key                 | key_len | ref                 | rows | filtered | Extra       |
+----+--------------+-------------+------------+--------+-----------------------------+---------------------+---------+---------------------+------+----------+-------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL    | NULL                        | NULL                | NULL    | NULL                | NULL |     0.00 | NULL        |
|  1 | SIMPLE       | cust        | NULL       | eq_ref | PRIMARY                     | PRIMARY             | 40      | <subquery2>.cust_id |    1 |   100.00 | NULL        |
|  2 | MATERIALIZED | o           | NULL       | index  | PRIMARY,FK_Orders_Customers | FK_Orders_Customers | 40      | NULL                |    5 |   100.00 | Using index |
|  2 | MATERIALIZED | oi          | NULL       | ref    | PRIMARY                     | PRIMARY             | 4       | test.o.order_num    |    1 |    10.00 | Using where |
+----+--------------+-------------+------------+--------+-----------------------------+---------------------+---------+---------------------+------+----------+-------------+
/* 返回结果含义
id: 标记用到的表在执行中的次序,如果id相同,则从上到下执行(先执行关于表c的第一行,再执行关于表o的第二行),若id不同则id大的先执行。
select_type:  查询类型,SIMPLE,SUBQUERY,PRIMARY,UNION
type:NULL(不涉及任何表的查询,如SELECT VERSION();),system,const,eq_ref,ref,range,index,all。从左到右性能依次递减。
possible_key:可以使用的索引
key:实际使用的索引
key_len:索引长度。长度越短效率越高。可能使用字段值的部分建立索引。
ref:和索引匹配的关键字所属的列或者常量
rows:扫描的行数,估计值
filtered:WHERE过滤后的行占总行数的百分比
*/ 

5. 联合索引

5.1 索引使用的最左前缀法则

• 使用多字段联合索引是,筛选条件必须包含最左索引列
• 为查询选取筛选列时,要从索引列中从左往右连续地选取。
• 不要求筛选条件的顺序和索引列顺序保持一致。
• 如果筛选列包含索引最左列,而缺失索引列次左列,则联合索引只能生效关于最左列的部分,就即时其他索引列也出现在筛选条件中。
• 如果筛选条件包含的那些符合最左前缀原则的索引列,某列出现范围查找,则该列右侧的索引列失效。

-- case1: 最左前缀原则
-- 创建联合字段索引,索引列从左到右顺序为:cust_name, cust_address, cust_city
CREATE INDEX cust_idx_name_addr_city ON Customers(cust_name, cust_address, cust_city);

-- 筛选条件不包含索引最左侧列cust_name,不走索引。key_len=NULL
EXPLAIN SELECT * FROM Customers WHERE cust_address='1 Sunny Place' AND cust_city = 'Muncie';


-- 筛选条件包含索引最左列cust_name,走索引。筛选条件中cust_name的位置不影响索引使用。筛选条件包含所有索引列(cust_name,cust_address, cust_city),查询完整地使用索引,key_len=602
EXPLAIN SELECT * FROM Customers WHERE cust_city = 'Muncie' AND cust_name = 'Fun4All' AND cust_address='1 Sunny Place';
+----+-------------+-----------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys           | key                     | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | Customers | NULL       | ref  | cust_idx_name_addr_city | cust_idx_name_addr_city | 602     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+


-- 筛选条件包含索引最左列cust_name,走索引。筛选条件包含索引最左的2列,查询部分地使用索引(cust_name, cust_address),key_len=401
EXPLAIN SELECT * FROM Customers WHERE cust_name = 'Fun4All' AND cust_address='1 Sunny Place';

-- 筛选条件包含索引最左列,不包含索引次左列,包含其他索引列,则索引只生效关于最左列的部分(cust_name)
EXPLAIN SELECT * FROM Customers WHERE cust_name = 'Fun4All' AND cust_city = 'Muncie';  -- key_len = 200
EXPLAIN SELECT * FROM Customers WHERE cust_name = 'Fun4All';  -- key_len = 200


-- case2:范围查找,右侧失效
-- 创建索引
CREATE INDEX oi_idx_prod_quantity_price ON OrderItems(prod_id, quantity, item_price);
-- 完整使用索引,key_len=48
EXPLAIN SELECT * FROM OrderItems WHERE prod_id = 'BNBG01' AND quantity = 100 AND item_price = '2.99';

-- 中间索引列(quantity)使用范围查询,导致右侧索引(item_price)列失效。
EXPLAIN SELECT * FROM OrderItems WHERE prod_id = 'BNBG01' AND quantity > 50 AND item_price = '2.99';  -- key=44
EXPLAIN SELECT * FROM OrderItems WHERE prod_id = 'BNBG01' AND quantity = 100;  -- key_len = 44

-- 使用>=则可以避免右侧索引列失效,key_len = 48
EXPLAIN SELECT * FROM OrderItems WHERE prod_id = 'BNBG01' AND quantity >= 50 AND item_price = '2.99';  -- key=44

5.2 索引覆盖

索引覆盖指的是在数据库查询过程中,查询所需要的所有数据都可以直接从索引中获取,而无需再回到数据表中去查找对应的数据行(称为回表)。查询计划中Extra=NULL,意味着有回表;Extra为Using Index,意味着直接使用索引就得到所需列,没有回表。

-- cust_name为索引列,cust_email不是索引列
CREATE INDEX idx_cust_name ON Customers(cust_name);
-- 查询使用二级索引idx_cust_name,该索引根据cust_name建立,根据索引查到匹配的cust_name值及其对应主键值,还需要根据主键值查聚集索引,找到匹配行,从匹配行中取出cust_email值。
EXPLAIN SELECT cust_name, cust_email FROM Customers WHERE cust_name = 'Fun4All';  -- Extra=NULL
EXPLAIN SELECT cust_id, cust_name FROM Customers WHERE cust_name = 'Fun4All';  -- Extra=Using index

5.3 单列索引和联合索引比较

为多列单独建立索引,筛选条件包含这些索引列,则MySQL会使用最高效率的那一个索引列,不用其他索引列。而为多列建立联合索引,筛选条件包含这些索引列,MySQL只会使用这个联合索引。建议优先使用联合索引,利用索引覆盖避免回表。同时索引数量更少,DBMS要维护的索引个数也就更少。

-- 为cust_name, cust_address单独建立索引,查询只会使用其中一个索引
CREATE INDEX idx_cust_name ON Customers(cust_name);
CREATE INDEX idx_cust_address ON Customers(cust_address);
-- possible_key=idx_cust_name,idx_cust_address; key=idx_cust_address
EXPLAIN SELECT * FROM Customers WHERE cust_name = 'Fun4All' AND cust_address = '1 Sunny Place';

-- 为cust_name, cust_address建立联合索引
CREATE INDEX idx_cust_name_addr ON Customers(cust_name,. cust_address);
-- possible_key=idx_cust_name_addr; key=idx_cust_name_addr
EXPLAIN SELECT * FROM Customers WHERE cust_name = 'Fun4All' AND cust_address = '1 Sunny Place';

索引失效的情况
-- case1: 对索引列使用函数
CREATE INDEX csut_idx_name ON Customers(cust_name);
-- 对索引列使用函数(有两条cust_name=’Fun4All‘的数据),索引失效。possible_key=NULL
EXPLAIN SELECT * FROM Customers WHERE substring(cust_name, 5, 3) = 'All';


-- case2:字符串类型列保存数值数据时,查询时可以不用单引号,但是这会导致索引失效(MySQL需要对索引列进行数据类型转换)
CREATE INDEX cust_idx_zip ON Customers(cust_zip);  -- cust_zip char(10), eg: cust_zip=44444
EXPLAIN SELECT * FROM Customers WHERE cust_zip = 44444;  -- possible_key = cust_idx_zip,key=NULL
EXPLAIN SELECT * FROM Customers WHERE cust_zip = '44444';  -- key_len = 41


--- case3: 模糊查询中,对索引字段头部使用通配符导致索引失效,对索引字段尾部使用通配符不会导致索引失效。
-- cust_name = Fun4All
-- 对索引列值头部使用通配符, possible_key=NULL
EXPLAIN SELECT * FROM Customers WHERE cust_name LIKE '%All';
-- 对索引列值尾部使用通配符,key_len=200
EXPLAIN SELECT * FROM Customers WHERE cust_name LIKE 'Fun';


-- case4: OR逻辑中,只有所有条件都涉及索引列,索引才生效
-- cust_id有主键索引,cust_city无索引,possible_key=NULL
EXPLAIN SELECT * FROM Customers WHERE cust_id = '1000000001' OR cust_city = 'Chicago';
-- cust_id有主键索引(PRIMARY),cust_name有单列的索引(csut_idx_name)。key_len=40,200(索引长度分别为40和200)
EXPLAIN SELECT * FROM Customers WHERE cust_id = '1000000001' OR cust_name = 'Fun4All';

6. 使用指定索引

-- 一个字段上创建多个索引
CREATE INDEX idx_cust_name ON Customers(cust_name);
CREATE INDEX idx_cust_name_addr ON Customers(cust_name, cust_address);

-- MySQL自己决定使用哪个索引: possible_key=idx_cust_name_addr,idx_cust_name; key=idx_cust_name
EXPLAIN SELECT * FROM Customers WHERE cust_name = 'Fun4All';

-- 建议MySQL使用指定索引,possible_key=idx_cust_name_addr; key=idx_cust_name_addr
EXPLAIN SELECT * FROM Customers USE INDEX(idx_cust_name_addr) WHERE cust_name = 'Fun4All';

-- 强制MySQL使用指定索引
EXPLAIN SELECT * FROM Customers FORCE INDEX(idx_cust_name_addr) WHERE cust_name = 'Fun4All';

-- 忽略指定索引,possible_key=idx_cust_name; key=idx_cust_name
EXPLAIN SELECT * FROM Customers IGNORE INDEX(idx_cust_name_addr) WHERE cust_name = 'Fun4All';

7. 前缀索引

索引可选择性:不重复索引值占总记录数的比值。可选择性越高,索引查询效率越高,同时可能前缀越长(占用空间大)。在创建前缀索引时,要在可选择性和前缀长度之间权衡。

-- 计算整个字段值可计算型
SELECT COUNT(DISTINCT cust_email) / COUNT(*) FROM Customers;

-- 计算前缀可选择性
SELECT COUNT(DISTINCT substring(cust_email, 1, 10)) / COUNT(*) FROM Customers;

-- 创建前缀索引
CREATE INDEX idx_cust_email ON TABLE(cust_email(10));

8. 索引小结

8.1 对什么表考虑使用索引

• 数据量大(百万记录数),查询多,更新删除少。

8.2 对那些字段考虑使用索引

• 出现在WHERE子句,GROUP BY子句,ORDER BY子句中的字段。
• 区分度高的字段作为索引(宜:身份证号,电话号码等,忌:状态等)。
• 字符串/文本类型的字段考虑使用前缀索引

8.3 创建索引考虑什么

• 尽量使用联合索引(注意最左前缀法则),而不是单列索引。
• 必要时建立索引(索引数量多,维护开销大)
• 如果希望索引列不包含NULL值,应该在建表时指定NOT NULL约束(便于优化选择效率最高的索引)。

8.4 补充

如果全表扫描比索引快,则MySQL可能不使用索引,这主要由数据的分布决定。

9. 参考

视频教程:https://www.bilibili.com/video/BV1Kr4y1i7ru/
数据结构演示网站:https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值