【问题】数据库占用空间太大，我把一个最大的表删掉了一半的数据，怎么表文件的大小还是没变？

要达到收缩空洞的目的，可以使用重建表的方式。

- [《高性能 MySQL》](https://book.douban.com/subject/23008813/)

- [MySQL 实战 45 讲](https://time.geekbang.org/column/intro/139)

**_索引优化应该是查询性能优化的最有效手段_**。

B+ 树索引，按照顺序存储数据，所以 Mysql 可以用来做 ORDER BY 和 GROUP BY 操作。因为数据是有序的，所以 B+ 树也就会将相关的列值都存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。

- **索引需要占用额外的物理空间**，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立组合索引那么需要的空间就会更大。

- 写操作（`INSERT`/`UPDATE`/`DELETE`）时很可能需要更新索引，导致数据库的写操作性能降低。

- 列名不经常出现在 `WHERE` 或连接（`JOIN`）条件中 - 索引就会经常不命中，没有意义，还增加空间开销。

- 对于特大型表，建立和使用索引的代价将随之增长。可以考虑使用分区技术或 Nosql。

- 哈希索引**只支持等值比较查询**，不支持任何范围查询，如 `WHERE price > 100`。

- 哈希索引有**可能出现哈希冲突**，出现哈希冲突时，必须遍历链表中所有的行指针，逐行比较，直到找到符合条件的行。

通常我们所说的索引是指`B-Tree`索引，它是目前关系型数据库中查找数据最为常用和有效的索引，大多数存储引擎都支持这种索引。使用`B-Tree`这个术语，是因为 MySQL 在`CREATE TABLE`或其它语句中使用了这个关键字，但实际上不同的存储引擎可能使用不同的数据结构，比如 InnoDB 就是使用的`B+Tree`。

这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

必须使用 GIS 相关的函数来维护数据。

主流的关系型数据库一般都支持以下索引类型：

主键索引：一种特殊的唯一索引，不允许有空值。一个表只能有一个主键（在 InnoDB 中本质上即聚簇索引），一般是在建表的时候同时创建主键索引。

)

唯一索引：**索引列的值必须唯一，但允许有空值**。如果是组合索引，则列值的组合必须唯一。

)

普通索引：最基本的索引，没有任何限制。

)

全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

)

组合索引：多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

)

假设有以下表：

) ENGINE=InnoDB;

- **索引不是越多越好，不要为所有列都创建索引**。要考虑到索引的维护代价、空间占用和查询时回表的代价。索引一定是按需创建的，并且要尽可能确保足够轻量。一旦创建了多字段的联合索引，我们要考虑尽可能利用索引本身完成数据查询，减少回表的成本。

- 要**尽量避免冗余和重复索引**。

- 要**考虑删除未使用的索引**。

- **尽量的扩展索引，不要新建索引**。

- **频繁作为 `WHERE` 过滤条件的列应该考虑添加索引**。

**“独立的列” 是指索引列不能是表达式的一部分，也不能是函数的参数**。

SELECT ... WHERE TO_DAYS(current_date) - TO_DAYS(date_col) <= 10;

【示例】范围查询

**由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。**

Mysql 有两种方式可以生成排序结果：通过排序操作；或者按索引顺序扫描。

2. 从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；

3. 重复执行步骤 2，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束。

有时候需要索引很长的字符列，这会让索引变得大且慢。

from SUser;

不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

**不需要考虑 `=`、`IN` 等的顺序**，Mysql 会自动优化这些条件的顺序，以匹配尽可能多的索引列。

【示例】如有索引 (a, b, c, d)，查询条件 `c > 3 and b = 2 and a = 1 and d < 4` 与 `a = 1 and c > 3 and b = 2 and d < 4` 等顺序都是可以的，MySQL 会自动优化为 a = 1 and b = 2 and c > 3 and d < 4，依次命中 a、b、c、d。

创建了索引，并非一定有效。比如不满足前缀索引、最左前缀匹配原则、查询条件涉及函数计算等情况都无法使用索引。此外，即使 SQL 本身符合索引的使用条件，MySQL 也会通过评估各种查询方式的代价，来决定是否走索引，以及走哪个索引。

因此，在尝试通过索引进行 SQL 性能优化的时候，务必通过执行计划（`EXPLAIN`）或实际的效果来确认索引是否能有效改善性能问题，否则增加了索引不但没解决性能问题，还增加了数据库增删改的负担。如果对 EXPLAIN 给出的执行计划有疑问的话，你还可以利用 `optimizer_trace` 查看详细的执行计划做进一步分析。

- [《高性能 MySQL》](https://book.douban.com/subject/23008813/)

- [数据库两大神器【索引和锁】](https://juejin.im/post/5b55b842f265da0f9e589e79)