欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

mysql索引那些事

程序员文章站 2022-05-07 08:12:44
...

疑问：数据库为什么要设计索引? 加快速度？（哈希索引是不是更好？）

减少随机IO，单行访问是很慢的，使用索引可以创建位置引用以提升查询效率

一、常用索引类型：

B-TREE&&B+TREE

最常用索引、时间复杂度：O(log(n))、中序遍历保证顺序、

哈希索引

目前只有Memory支持、、时间复杂度：O(1)、可使用B-TREE创建自定义哈希索引

R-TREE

mysql对GIS支持不太完善，postGIS是pgsql的插件

全文索引

建议不使用，使用es等搜索引擎查询

二、MyISAM和InnoDB的选择

关于count(*)

MyISAM会直接存储总行数，insert和count(*)速度极快，InnoDB则不会，需要按行扫描，insert数据是创建在聚集索引上的。

关于全文索引

MyISAM支持全文索引，InnoDB5.6之前不支持全文索引。

关于事务

MyISAM不支持事务，InnoDB支持事务。

关于外键

MyISAM不支持外键，InnoDB支持外键。不管哪种存储引擎，在数据量大并发量大的情况下，都不应该使用外键。

关于行锁与表锁

MyISAM只支持表锁，InnoDB可以支持行锁。

三、InnoDB的索引分类

分为两类：聚集索引与普通索引

聚集索引
mysql索引那些事

普通索引
mysql索引那些事

存储特点：

  1. 索引类型都是B+TREE
  2. 在索引结构中，非叶子节点存储key，叶子节点存储value；
  3. 聚集索引，叶子节点存储行记录(row)；
  4. 普通索引，叶子节点存储了PK的值；
  5. 叶子之间，增加了链表，获取所有节点及范围查询时，不再需要中序遍历；

InnoDB聚集索引规则：

  1. 如果表定义了PK，则PK就是聚集索引；
  2. 如果表没有定义PK，则第一个非空unique列是聚集索引；
  3. 否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

与MyISAM的索引类型区别：

  1. 都是B+TREE
  2. 只是叶子节点只存储了PK没有存储value，所以myisam无法使用覆盖索引(下文会提到)。
  3. 查询一次数据，至少有一次回表查询。

聚集索引与非聚集表对比图
mysql索引那些事

四、如何构建三星索引？

三星定义：

  • 第一颗星：与查询相关的索引行是相邻的，也就是where后面的等值谓词，可以匹配索引列顺序
  • 第二颗星：索引行的顺序与查询语句需求一致，也就是order by 中的排序和索引顺序是否一致
  • 第三颗星：索引行包含查询语句中所有的列

索引创建原则：

1、最左前缀匹配原则，非常重要的原则。

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如 a=1 and b=2 and c>3 and d=4。

如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2、=和in可以乱序，in不属于范围查询。

IN属于全值匹配，但尽量少用IN查询，因为他的耗时会升高。

select * from human where eye_cloor IN('brown','blue','hazel') and hair_color IN('black','red');

这种查询优化器会转换成3*2种查询组合。

3、尽量选择区分度高的列作为索引

特殊情况下除外比如：以性别建立索引，但是实际数据性别比例严重失衡。 查询数据量小的数据将会很快。

对应列数据的区分度越大越好，最好>0.1

select count(DISTINCT(`logic_junction_id`))/count(*) from real_time_alarm

尽量设置短的索引前缀，保证查询性能，最好>0.1。

问题：无法做ORDER BY/GROUP BY也无法做索引覆盖扫描，所以尽量不要用。

select count(DISTINCT(left(`logic_junction_id`,18)))/count(*) from real_time_alarm

4、索引列不能参与计算

where from_unixtime(create_time) =’2014-05-29’

where count+1=3

这种查询都无法命中索引。

5、尽量的扩展索引成复合索引

不新建索引，因为多余的索引会造成资源浪费，还可能造成错误的索引命中。

6、select中查询的列都是索引中的列

（所需字段不需要回表查询）称之为：覆盖索引

MariaDB [its_tool]> EXPLAIN select updated_at,hour from real_time_12 where updated_at='2018-08-20 00:08:00' and hour='00:08:00' limit 1;          +------+-------------+--------------+------+-------------------------------------------+----------------------------+---------+-------------+------+--------------------------+
| id   | select_type | table        | type | possible_keys                             | key                        | key_len | ref         | rows | Extra                    |
+------+-------------+--------------+------+-------------------------------------------+----------------------------+---------+-------------+------+--------------------------+
|    1 | SIMPLE      | real_time_12 | ref  | idx_hour_update_stop_delay,idx_updated_at | idx_hour_update_stop_delay | 50      | const,const |   64 | Using where; Using index |
+------+-------------+--------------+------+-------------------------------------------+----------------------------+---------+-------------+------+--------------------------+
1 row in set (0.25 sec)

五、EXPLAIN 使用浅析

核心整理

  1. 当type为：index_merge、range、ALL时应该考虑优化。
  2. Extra为：Using temporary、Using filesort 时应该考虑优化。
  3. 基于key_len判断当前符合索引，使用了几个字段。

EXPLAIN 简介

通过 EXPLAIN 我们可以知道以下信息：表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间的引用，每张表有多少行被优化器查询等信息。

MariaDB [its_tool]> EXPLAIN select * from real_time_134 where id=19;        
+------+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table         | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | real_time_134 | const | PRIMARY       | PRIMARY | 8       | const |    1 |       |
+------+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

利用 EXPLAIN 查询优化器优化的SQL

EXPLAIN extended sql + show warnings

MariaDB [its_tool]> explain extended select * from real_time_134 where id=19;        
+------+-------------+---------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id   | select_type | table         | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+------+-------------+---------------+-------+---------------+---------+---------+-------+------+----------+-------+
|    1 | SIMPLE      | real_time_134 | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 |       |
+------+-------------+---------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

MariaDB [its_tool]> show warnings;
select 19 AS `id`,'2017030116_1082731' AS `logic_junction_id`,'11:10:00' AS `hour`,'2017030116_i_14597910_2017030116_o_14644330' AS `logic_flow_id`,0 AS `stop_time_cycle`,0 AS `spillover_rate`,0 AS `queue_length`,0 AS `stop_delay`,0 AS `stop_rate`,0 AS `twice_stop_rate`,4.5 AS `speed`,10 AS `free_flow_speed`,1 AS `traj_count`,'2018-08-13 11:10:00' AS `created_at`,'2018-08-13 11:10:00' AS `updated_at` from `its_tool`.`real_time_134` where

EXPLAIN 中的列

select_type 简单查询还是复杂查询

  • simple：简单查询，不包含子查询和union
  • primary：复杂查询中最外层的 select
  • subquery：包含在 select 中的子查询（不在 from 子句中）
  • union：在 union 中的第二个和随后的 select
  • union result：从 union 临时表检索结果的 select

table 正在访问哪个表

type 代表查找行的方式（重要）

13种（不完全统计）依次从最优到最差分别为：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

const, system：mysql能对查询的某部分进行优化并将其转化成一个常量

mysql> explain extended select * from (select * from film where id = 1) tmp;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL  |
|  2 | DERIVED     | film       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+

mysql> show warnings;
+-------+------+---------------------------------------------------------------+
| Level | Code | Message                                                       |
+-------+------+---------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from dual |
+-------+------+---------------------------------------------------------------+

eq_ref：primary key 或 unique key 索引的所有部分被连接使用，联合查询的联合关联键

mysql> explain select * from film_actor left join film on film_actor.film_id = film.id;
+----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+
| id | select_type | table      | type   | possible_keys | key               | key_len | ref                     | rows | Extra       |
+----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | film_actor | index  | NULL          | idx_film_actor_id | 8       | NULL                    |    3 | Using index |
|  1 | SIMPLE      | film       | eq_ref | PRIMARY       | PRIMARY           | 4       | test.film_actor.film_id |    1 | NULL        |
+----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+

ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

1. 简单 select 查询，name是普通索引（非唯一索引）
mysql> explain select * from film where name = "film1";
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | film  | ref  | idx_name      | idx_name | 33      | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+

2.关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。
mysql> explain select * from film left join film_actor on film.id = film_actor.film_id;
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+
| id | select_type | table      | type  | possible_keys     | key               | key_len | ref          | rows | Extra       |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+
|  1 | SIMPLE      | film       | index | NULL              | idx_name          | 33      | NULL         |    3 | Using index |
|  1 | SIMPLE      | film_actor | ref   | idx_film_actor_id | idx_film_actor_id | 4       | test.film.id |    1 | Using index |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+

ref_or_null：类似ref，但是可以搜索值为NULL的行。

mysql> explain select * from film where name = "film1" or name is null;
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type        | possible_keys | key      | key_len | ref   | rows | Extra                    |
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | film  | ref_or_null | idx_name      | idx_name | 33      | const |    2 | Using where; Using index |
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+

index_merge：表示使用了索引合并的优化方法（优化器执行后的结果）。

例如下表：id是主键，tenant_id是普通索引。or 的时候没有用 primary key，而是使用了 primary key(id) 和 tenant_id 索引

mysql> explain select * from role where id = 11011 or tenant_id = 8888;
+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+
| id | select_type | table | type        | possible_keys         | key                   | key_len | ref  | rows | Extra                                           |
+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+
|  1 | SIMPLE      | role  | index_merge | PRIMARY,idx_tenant_id | PRIMARY,idx_tenant_id | 4,4     | NULL |  134 | Using union(PRIMARY,idx_tenant_id); Using where |
+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+

range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

mysql> explain select * from actor where id > 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | actor | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

index：和ALL一样，不同就是mysql只需扫描索引树，这通常比ALL快一些。

mysql> explain select count(*) from film;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | NULL          | idx_name | 33      | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

ALL：即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了

mysql> explain select * from actor;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

possible_keys列

这一列显示查询可能使用哪些索引来查找。

key列

mysql实际采用哪个索引来优化对该表的访问，如果想强制使用某索引，在查询中使用 force index、ignore index。

key_len列

mysql在索引里使用的字节数

比如下例中：50 = len(hour)*4+2+len(updated_at)构成。

MariaDB [its_tool]> EXPLAIN extended SELECT hour,traj_count FROM `real_time_134` WHERE `hour`='16:52:00' AND `traj_count`=10 AND `updated_at`>='2018-08-29 00:00:00' AND `updated_at`<='2018-08-29 23:59:59' ORDER BY `stop_time_cycle` DESC LIMIT 20;
+------+-------------+---------------+-------+----------------------------------------------------+-----------------+---------+------+------+----------+----------------------------------------------------+
| id   | select_type | table         | type  | possible_keys                                      | key             | key_len | ref  | rows | filtered | Extra                                              |
+------+-------------+---------------+-------+----------------------------------------------------+-----------------+---------+------+------+----------+----------------------------------------------------+
|    1 | SIMPLE      | real_time_134 | range | uniq_idx_flow_hour,idx_hour_update,idx_update_hour | idx_hour_update | 50      | NULL | 2429 |   100.00 | Using index condition; Using where; Using filesort |
+------+-------------+---------------+-------+----------------------------------------------------+-----------------+---------+------+------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.04 sec)

key_len计算规则如下：

字符串
    char(n)：n字节长度
    varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2，如果是utf8mb4，则长度 4n + 2
数值类型
    tinyint：1字节
    smallint：2字节
    int：4字节
    bigint：8字节　　
时间类型　
    date：3字节
    timestamp：4字节
    datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量

rows列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

Extra列

这一列展示的是额外信息。常见的重要值如下：

distinct

一旦mysql找到了与行相联合匹配的行，就不再搜索了

mysql> explain select distinct name from film left join film_actor on film.id = film_actor.film_id;
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+
| id | select_type | table      | type  | possible_keys     | key               | key_len | ref          | rows | Extra                        |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+
|  1 | SIMPLE      | film       | index | idx_name          | idx_name          | 33      | NULL         |    3 | Using index; Using temporary |
|  1 | SIMPLE      | film_actor | ref   | idx_film_actor_id | idx_film_actor_id | 4       | test.film.id |    1 | Using index; Distinct        |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+

Using index

这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录。是性能高的表现。

mysql> explain select id from film order by id;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | NULL          | PRIMARY | 4       | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

Using where

mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。

mysql> explain select * from film where id > 1;
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | film  | index | PRIMARY       | idx_name | 33      | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+

Using temporary

mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

1. actor.name没有索引，此时创建了张临时表来distinct
mysql> explain select distinct name from actor;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
2. film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表
mysql> explain select distinct name from film;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | idx_name      | idx_name | 33      | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

Using filesort

mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。

1. actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录
mysql> explain select * from actor order by name;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

2. film.name建立了idx_name索引,此时查询时extra是using index
mysql> explain select * from film order by name;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | NULL          | idx_name | 33      | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

使用的表

DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');

DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');

DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
  `id` int(11) NOT NULL,
  `film_id` int(11) NOT NULL,
  `actor_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

六、项目案例分解

案例1

优化前0.5秒，优化后0.3秒

MariaDB [its_tool]> EXPLAIN SELECT hour,traj_count FROM `real_time_134` WHERE `hour`='16:52:00' AND `traj_count`>=10 AND `updated_at`>='2018-08-29 00:00:00' AND `updated_at`<='2018-08-29 23:59:59' ORDER BY `stop_time_cycle` DESC LIMIT 20;            
+------+-------------+---------------+------+--------------------+--------------------+---------+-------+------+----------------------------------------------------+
| id   | select_type | table         | type | possible_keys      | key                | key_len | ref   | rows | Extra                                              |
+------+-------------+---------------+------+--------------------+--------------------+---------+-------+------+----------------------------------------------------+
|    1 | SIMPLE      | real_time_134 | ref  | uniq_idx_flow_hour | uniq_idx_flow_hour | 46      | const | 9048 | Using index condition; Using where; Using filesort |
+------+-------------+---------------+------+--------------------+--------------------+---------+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)

分析：
1. 范围查询之后的字段，无法命中。traj_count与updated_at 只能有一个生效。
2. 比较 traj_count 与 updated_at 区分度，哪个更大选哪个。
3. 由于索引只能命中部分索引，所以无法优化 Using filesort，也无法使用覆盖索引。

MariaDB [its_tool]> select count(DISTINCT(`traj_count`))/count(*),count(DISTINCT(`updated_at`))/count(*) from real_time_134;
+----------------------------------------+----------------------------------------+
| count(DISTINCT(`traj_count`))/count(*) | count(DISTINCT(`updated_at`))/count(*) |
+----------------------------------------+----------------------------------------+
|                                 0.0001 |                                 0.0268 |
+----------------------------------------+----------------------------------------+
1 row in set (24.90 sec)

创建索引：

ALTER TABLE `real_time_134` ADD KEY `idx_hour_update` (`hour`,`updated_at`) USING BTREE;
MariaDB [its_tool]> EXPLAIN SELECT hour,traj_count FROM `real_time_134` WHERE `hour`='16:52:00' AND `traj_count`>=10 AND `updated_at`>='2018-08-29 00:00:00' AND `updated_at`<='2018-08-29 23:59:59' ORDER BY `stop_time_cycle` DESC LIMIT 20;     
+------+-------------+---------------+-------+------------------------------------+-----------------+---------+------+------+----------------------------------------------------+
| id   | select_type | table         | type  | possible_keys                      | key             | key_len | ref  | rows | Extra                                              |
+------+-------------+---------------+-------+------------------------------------+-----------------+---------+------+------+----------------------------------------------------+
|    1 | SIMPLE      | real_time_134 | range | uniq_idx_flow_hour,idx_hour_update | idx_hour_update | 50      | NULL | 2429 | Using index condition; Using where; Using filesort |
+------+-------------+---------------+-------+------------------------------------+-----------------+---------+------+------+----------------------------------------------------+
1 row in set (0.02 sec)

案例2：

优化前：1.59, 优化后：0.08

MariaDB [its_tool]> EXPLAIN SELECT `hour` FROM `real_time_134` WHERE `updated_at`>='2018-08-29 00:00:00' AND `updated_at`<='2018-08-29 23:59:59' ORDER BY `hour` DESC LIMIT 1;
+------+-------------+--------------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| id   | select_type | table        | type  | possible_keys  | key            | key_len | ref  | rows | Extra                                    |
+------+-------------+--------------+-------+----------------+----------------+---------+------+------+------------------------------------------+
|    1 | SIMPLE      | real_time_12 | range | idx_updated_at | idx_updated_at | 4       | NULL |    1 | Using where; Using index; Using filesort |
+------+-------------+--------------+-------+----------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.16 sec)

分析：
1. 范围查询之后的字段，无法命中。所以无法优化 Using filesort，也无法使用覆盖索引。
1. 为了能让hour，可以让updated_at与hour直接同方向order by即可。
1. 强制使用创建的索引 force index(idx_update_hour)

创建索引：

ALTER TABLE `real_time_134` ADD KEY `idx_update_hour` (`updated_at`,`hour`) USING BTREE;
MariaDB [its_tool]> EXPLAIN SELECT `hour` FROM `real_time_134` force index(idx_update_hour) WHERE 1 ORDER BY updated_at DESC,hour DESC LIMIT 1;
+------+-------------+---------------+-------+---------------+-----------------+---------+------+------+-------------+
| id   | select_type | table         | type  | possible_keys | key             | key_len | ref  | rows | Extra       |
+------+-------------+---------------+-------+---------------+-----------------+---------+------+------+-------------+
|    1 | SIMPLE      | real_time_134 | index | NULL          | idx_update_hour | 50      | NULL |    1 | Using index |
+------+-------------+---------------+-------+---------------+-----------------+---------+------+------+-------------+
1 row in set (0.00 sec)

七、重构查询方式：

1、分解关联查询？

mysql > SELECT * FROM tag
>JOIN tag_post ON tag_post.tag_id=tag.id
>JOIN post ON tag_post.post_id=post.id
>WHERE tag.tag='mysql'

mysql > select * from tag where tag='mysql';
mysql > select * from tag_post where tag_id=1234;
mysql > select * from post where post.id in (123,456,567,9089,8904);

2、切分查询？

db查询数据量过大会打满带宽，dba会钉你的。

$current_id = 0;
while(1){
    $row = $db->get("select * from task where id>? order by id asc limit 1000", $current_id);
    if(count($row)==0){
        break;
    }
    foreach($row as $item){
        $current_id = $item['id'];
    }

    //todo...
}

八、补充

mysql注意点整理
1. TIMESTAMP只使用 DATETIME 一半的存储控件，并且会根据时区变化。（DATETIME 程序生成UTC时间可解决时区问题）
2. INT(1)与INT(20)，它不会限制值的合法范围，只是规定Mysql交互工具的显示字符个数。
3. 完全范式化和完全反范式化schema都是实验室才有的东西，真实世界很少会这么极端。
4. Alter table 通过的 “影子拷贝”（创建与原表结构相同结构、重命名、删除等操作实现：PT工具）可以在无锁表时更新索引。

引用文章：
http://blog.codinglabs.org/articles/theory-of-mysql-index.html
https://blog.csdn.net/dba_waterbin/article/details/8937441
https://www.cnblogs.com/butterfly100/archive/2018/01/15/8287569.html
https://www.cnblogs.com/chenhaoyu/p/8761305.html

上一篇： Nginx反向代理WebSocket的配置实例

下一篇： Spring Boot 自动配置那些事——组合注解

推荐阅读