mysql索引那些事

疑问:数据库为什么要设计索引? 加快速度?(哈希索引是不是更好?)

减少随机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的索引分类

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

聚集索引
聚集索引

普通索引
普通索引

存储特点:

  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. 查询一次数据,至少有一次回表查询。

聚集索引与非聚集表对比图
image

四、如何构建三星索引?

三星定义:

  • 第一颗星:与查询相关的索引行是相邻的,也就是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字节  
时间类型 
    date3字节
    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

©️2020 CSDN 皮肤主题: 像素格子 设计师:CSDN官方博客 返回首页