文章目录
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+ tree | Hash | R tree | Full 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和数据，树的深度进一步降低。仅支持等值查找。
B+tree作为索引的结构：Btree的叶子结点加链（范围查找）。内部结点存储只存储key（索引存放集中，查询效率高），叶子结点存储key和数据，一个叶子结点对应一个数据。
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
2万+
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
