深入浅出MySQL索引

8.2.1.3 Index Merge Optimization 索引合并优化

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1 = t1.some_col;

SELECT * FROM t1, t2 WHERE t1.key1 = 1 AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

当 EXPLAIN 语句type列为index_merge的时候代表出现了索引合并优化

EXPLAIN SELECT * FROM student WHERE sname = "学生6" OR height = 180;

根据 Extra 的不同分为几种情况

• Using intersect(…)

• Using union(…)

• Using sort_union(…)

8.2.1.23 Avoiding Full Table Scans 避免全表扫描

EXPLAIN select  * from t1

当type列显示为ALL的时候代表全表扫描。

为什么会出现全表扫描呢 ？

• 表很小的时候，全表扫描反而更快。
• where 或者 on 语句没有索引或者没有覆盖索引列
• 将索引列和where进行比较，MySQL经过计算得出全表扫描更快
• 使用的索引列区分度太低导致MySQL全表扫描更快

如何避免全表扫描呢？

• ANALYZE TABLE t1 更新key的分布
• 使用 FORCE INDEX 强制走索引

SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
  WHERE t1.col_name=t2.col_name;

• SET max_seeks_for_key=1000
  

8.3.1 How MySQL Uses Indexes（MySQL怎么使用索引）

8.3.1 How MySQL Uses Indexes

索引的作用：
如果没有索引，查找操作必须从第一行开始逐行扫描，表越大则越慢。
如果有索引，可以快速定位到一个位置开始扫描。

Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions: Indexes on spatial data types use R-trees; MEMORY tables also support hash indexes; InnoDB uses inverted lists for FULLTEXT indexes.

大多数MySQL索引存储在B树（主键、唯一、全文索引）。
空间数据使用R树。
InnoDB 引擎使用倒排列表存储全文索引。

8.3.2 Primary Key Optimization 主键优化

The primary key for a table represents the column or set of columns that you use in your most vital queries. It has an associated index, for fast query performance. Query performance benefits from the NOT NULL optimization, because it cannot include any NULL values. With the InnoDB storage engine, the table data is physically organized to do ultra-fast lookups and sorts based on the primary key column or columns.

它有一个相关的索引，以提高查询性能。
使用InnoDB存储引擎，可以对表数据进行物理组织，以便根据主键列进行超快速查找和排序。

8.3.3 SPATIAL Index Optimization 空间索引优化

略

8.3.4 Foreign Key Optimization 外键优化

https://dev.mysql.com/doc/refman/8.0/en/foreign-key-optimization.html

这里说的外键优化就是分表的概念。将一个大表拆分成为小表通过外键关联，减少IO从而提升效率。

8.3.5 Column Indexes 列索引

https://dev.mysql.com/doc/refman/8.0/en/column-indexes.html

字符串类型的列可以指定索引前缀，也就是索引列的一部分值。

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

When you index a BLOB or TEXT column, you must specify a prefix length for the index.
当你想要给 BLOB or TEXT 列加索引的时候，你必须指定索引前缀的长度。

全文索引：
使用限制：only for CHAR, VARCHAR, and TEXT columns.

8.3.6 Multiple-Column Indexes（多列索引）

https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html

什么是多列索引？

一个索引设计多个列。

假设这里有一个表test，在last_name,first_name上设置一个索引

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

写入几条数据

INSERT INTO `test` VALUES (2, 'Jones', 'John');
INSERT INTO `test` VALUES (1, 'Jones', 'Jon');
INSERT INTO `test` VALUES (3, 'Jones', 'Mary');

走索引

SELECT * FROM test WHERE last_name='Jones';

SELECT * FROM test WHERE last_name='Jones' AND first_name='John';

SELECT * FROM test WHERE last_name='Jones' AND (first_name='John' OR first_name='Jon');

SELECT * FROM test WHERE last_name='Jones' AND first_name >='M' AND first_name < 'N';

不走索引

SELECT * FROM test WHERE first_name='John';

SELECT * FROM test WHERE last_name='Jones' OR first_name='John';

当在多列建立索引的时候要想使用索引，语句必须包含最左边的列。

8.3.9 Comparison of B-Tree and Hash Indexes B树和散列索引的比较

https://dev.mysql.com/doc/refman/8.0/en/index-btree-hash.html

走索引

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

不走索引

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

B-tree

B树特点：树形结构，不同于二叉树，可以有多个子节点，始终有序。

hash index

use equality operators, rather than range operators

擅长使用 = 运算符而不是范围运算符。

关于B tree index 和 hash index

由于树结构有序，所以更擅长范围运算符，而hash散列擅长等于的场景。

index

A data structure that provides a fast lookup capability(快速查找能力） for rows of a table, typically by forming a tree structure（通常通过形成树形结构） (B-tree) representing all the values of a particular column or set of columns.

---

InnoDB tables always have a clustered index（聚集索引） representing the primary key（主键）.

---

They can also have one or more secondary indexes（一个或多个次要索引） defined on one or more columns.

---

Depending on（根据） their structure, secondary indexes can be classified（分类） as partial（部分）, column（列）, or composite（聚合） indexes.

---

Indexes are a crucial aspect of query performance（查询性能的关键方面。）.

---

Database architects design tables, queries, and indexes to allow fast lookups for data（支持快速数据查找） needed by applications.

---

The ideal database design uses a covering index（覆盖索引） where practical;

---

the query results are computed entirely from the index（完全根据索引计算）, without reading the actual table data（实际表数据）.

---

Each foreign key constraint also requires an index, to efficiently check whether values exist in both the parent and child tables.

---

Although a B-tree index is the most common（是最常见的）, a different kind of data structure is used for hash indexes, as in the MEMORY storage engine（内存存储引擎） and the InnoDB adaptive hash index（InnoDB 自适应哈希索引）.

---

R-tree indexes are used for spatial indexing of multi-dimensional information（多维信息空间索引）.

---

See Also adaptive hash index, B-tree, child table, clustered index, column index, composite index, covering index, foreign key, hash index, parent table, partial index, primary key, query, R-tree, row, secondary index, table.

InnoDB

A MySQL component that combines high performance（高性能） with transactional capability for reliability（可靠的事务能力）, robustness（健壮性）, and concurrent access（并发访问）.

---

It embodies（体现） the ACID design philosophy（设计原则）.

---

Represented（表现为） as a storage engine; it handles tables created or altered with the ENGINE=INNODB clause（语句）.

---

See Chapter 15, The InnoDB Storage Engine for architectural details（结构细节） and administration procedures（管理程序）, and Section 8.5, “Optimizing for InnoDB Tables” for performance advice（性能建议）.

---

In MySQL 5.5 and higher, InnoDB is the default storage engine（默认存储引擎） for new tables and the ENGINE=INNODB clause is not required.

---

InnoDB tables are ideally suited for hot backups.（非常适合热备份）

---

See Section 30.2, “MySQL Enterprise Backup Overview” for information about the MySQL Enterprise Backup product for backing up MySQL servers without interrupting normal processing.

---

See Also ACID, hot backup, MySQL Enterprise Backup, storage engine, transaction.

primary key

A set of columns—and by implication, the index based on this set of columns—that can uniquely identify（唯一识别） every row in a table.

---

As such, it must be a unique index（唯一索引） that does not contain any NULL values.

---

InnoDB requires that every table has such an index (also called the clustered index or cluster index（聚集索引）), and organizes the table storage based on the column values of the primary key.

---

When choosing primary key values, consider using arbitrary（随机） values (a synthetic key) rather than relying on（依赖） values derived from some other source (a natural key).

---

See Also clustered index, index, natural key, synthetic key.

primary key总结

主键必须是不为null的唯一索引。主键考虑使用随机值例如a synthetic key而不是a natural key。

natural key

An indexed column, typically a primary key, where the values have some real-world significance（含义）.

Usually advised against（通常建议不要） because:

If the value should ever change, there is potentially（潜在） a lot of index maintenance（维护） to re-sort the clustered index（重排序聚集索引） and update the copies of the primary key value that are repeated in each secondary index.

Even seemingly stable values（看似稳定） can change in unpredictable ways（通过不可预料的方式） that are difficult to represent correctly（正确表示） in the database.

For example, one country can change into two or several（几个）, making the original country code obsolete（废弃 ）.

Or, rules about unique values might have exceptions（例外）.

For example, even if taxpayer（纳税人） IDs are intended（预期）* to be unique to a single person, a database might have to handle records that violate（违背） that rule, such as in cases of identity theft.（身份盗窃）

Taxpayer IDs and other sensitive（敏感） ID numbers also make poor（不好的） primary keys, because they may need to be secured（安全）, encrypted（加密）, and otherwise treated（处理） differently than other columns.

Thus（因此）, it is typically（通常） better to use arbitrary（随机） numeric values to form a synthetic（组合） key, for example using an auto-increment column（使用自增列）.

See Also auto-increment, clustered index, primary key, secondary index, synthetic key.

natural key总结

natural key是指具有现实意义的key，例如身份证号，在被盗窃的情况下会出现重复的问题。所以应该使用随机数或者自增列。

synthetic key

An indexed column, typically a primary key, where the values are assigned arbitrarily（任意的）.

Often done using an auto-increment column. 通常使用自增列。

By treating the value as completely arbitrary（完全任意）, you can avoid overly restrictive rules（避免过度限制规则） and faulty（错误） application assumptions（假设））.

For example, a numeric sequence representing employee numbers might have a gap（代表雇员编号的数字序列可能有一个间隙） if an employee was approved for hiring（批准雇佣） but never actually joined.

Or employee number 100 might have a later hiring date than employee number 500, if they left the company and later rejoined.

Numeric values also produce shorter values of predictable length.

For example, storing numeric codes meaning “Road”, “Boulevard”, “Expressway”, and so on is more space-efficient than repeating those strings over and over.

Also known as a surrogate key（代理key）. Contrast with（对比） natural key.

See Also auto-increment, natural key, primary key, surrogate key.

synthetic key总结

8.3.9 Comparison of B-Tree and Hash Indexes B树和哈希索引的比较

https://dev.mysql.com/doc/refman/8.0/en/index-btree-hash.html#hash-index-characteristics

简单说 B树更适合于进行范围搜索，而哈希索引更适用于等值查找。

Navicat创建MySQL数据库索引

索引类型：
全文索引、普通索引、空间索引（基本不考虑）、唯一索引。
索引方法：
HASH、BTREE（一般默认使用）
字段：

可以在子部分指定索引前缀。

点击右侧这个按钮可以查看 DDL 语句。

DDL 语句如下

CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8_general_ci NOT NULL,
  `s_code` int DEFAULT NULL,
  `address` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8_general_ci DEFAULT NULL,
  `height` double DEFAULT NULL,
  `classid` int DEFAULT NULL,
  `create_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_height` (`height`) USING BTREE,
  KEY `idx_same_s_code_address` (`sname`,`s_code`,`address`(10)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;

可以看到 主键 id 默认已经有索引了，所以无需额外创建主键索引。