Sql Optimize

19 November, 2020

本文主要讲关于SELECT语句的优化问题. 会涉及到一些关于表索引的知识.

性能

很简单, 你每建立一个索引, 数据库就会根据索引类型，帮你建立一个索引数据结构（B+树非常常用）.

NOTE:B+树

B+树对范围查询和直接查询都很在行. 直接查询的时间复杂度均为O(log), 也就是用的二分法, 具体为什么就去看B+树的数据结构, 在看B+树之前最好先看B树, 不然东西太多消化不了.

不过每次插入数据和删除数据就需要重构索引树的结构, 滥用索引反而会降低写入效率.

设计

索引有好几种, 最常用的有:

主键索引: 比如用户表的ID字段
唯一索引: 比如不可重复的用户名
组合索引: 需要经常被一起查询的字段, 比如用户名, 密码
普通索引: 这个怎么用就仁者见仁智者见智了.

索引一经创建不能修改，如果要修改索引，只能删除重建. 具体怎么创建就baidu吧。

如果要查看一张表的索引, 如下:

mysql> SHOW INDEX FROM users;

+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          0 | PRIMARY   |            1 | id          | A         |        9847 |     NULL | NULL   |      | BTREE      |         |               |
| users |          0 | id_UNIQUE |            1 | id          | A         |        9847 |     NULL | NULL   |      | BTREE      |         |               |
| users |          0 | username  |            1 | username    | A         |        9847 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3 rows in set (0.00 sec)

不重要的就不说了。

Non_unique: 能不能重复，不能为0，可以为1.
Seq_in_index: 组合索引的一个位置。等会就知道。
Sub_part: 前置索引长度，不使用前置索引则为NULL。
Cardinality: 基数，是否做为索引的最重要的因素，基数越大说明重复性越低，越有可能查询时触发索引。
Index_type: 索引类型，常用的有FULLTEXT(用与搜索技术，主要解决模糊查询效率低下的问题)，和BTREE（就是树形数据结构存储），还有Hash（由于Hash的特点，只能判断值相等，不能判断值范围）。

QUESION: 什么是前置索引？

对于一些特殊字段比如TEXT，或者VARCHAR(255)这种很大的字段，建立的索引树会非常肥，这样的索引非常慢，我们对与这种字段会采用它们的前几个字符做为索引。选择几个字符的诀窍就是要保证要极高的非重复性，和尽量短的字符，来节省空间。

NOTE: 关于基数

通常我们在选择索引时会尽可能选择基数大的列做为索引。因为MySQL在执行查询时，基数对比总数是是否触发索引的一个判断条件，这个值大概在30%左右。

调优

查看一次SELECT在MySQL使用了哪些策略，我们可以通过在SQL语句前加上EXPLAIN来做到。

mysql> EXPLAIN SELECT id, username FROM users WHERE username = 'sdttttt';
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | const | username      | username | 50      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

possible_keys: 可能触发的索引。
key: 本次SQL执行触发的索引。
filtered: 过滤了多少数据，单位：百分比。
Extra: 执行策略。

下面说一下Extra，这个比较重要：

Using index

查询的列被索引覆盖，并且where筛选条件是索引的是前导列，Extra中为Using index。

Using where Using index

查询的列被索引覆盖，并且where筛选条件是索引列之一, 但是不是索引的不是前导列，Extra中为Using where; Using index，意味着无法直接通过索引查找来查询到符合条件的数据。
查询的列被索引覆盖，并且where筛选条件是索引列前导列的一个范围，同样意味着无法直接通过索引查找查询到符合条件的数据。

(查询的列被索引覆盖，这种情况是不会发生回表的，只是进行索引扫描罢了)

NULL

（既没有Using index，也没有Using where Using index，也没有using where）

1，查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引，Extra中为NULL(没有信息)。

Using where

1，查询的列未被索引覆盖，where筛选条件非索引的前导列，Extra中为Using where.

（using where 意味着通过索引或者表扫描的方式进程where条件的过滤，反过来说，也就是没有可用的索引查找，这里的type都是all，说明MySQL认为全表扫描是一种比较低的代价。）

EXT: ICP(Index Condition Pushdown)

这个是MySQL5.6新加入的特新，MySQL的设计分为两层，服务层和存储引擎。

在没有的ICP的时候，你对索引执行过滤：首先SE（Storage Engine）将一条条索引数据取出，并且一条条给服务层看，由服务层来对照where条件。

在使用ICP后，你对索引执行过滤：首先SE（Storage Engine）将一条条索引数据取出，并且对照下推的索引条件，如果满足条件，就返回给服务层，服务层下推到没有被SE执行的where条件。

你是不是觉得很绕？其实就是索引的where条件SE帮服务层做了，服务层不>用去管索引的Where条件了。

说实话我对这个功能还是挺迷的，服务层的执行速度是不如SE么？为什么ICP能提高速度？MySQL中的秘密还挺多。

All in All

在设计数据表的时候需要考虑这个表的读写情况，根据字段来适当的增加索引。

在编写SQL语句时，尽可能使用索引。能调优就尽量调优。