mysql索引那些事

最新推荐文章于 2023-05-16 23:15:07 发布
最新推荐文章于 2023-05-16 23:15:07 发布
阅读量495 收藏 1
点赞数
分类专栏： Mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/e421083458/article/details/82261299
版权

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

减少随机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字节　　
时间类型　
    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

e421083458
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
Mysql索引详细介绍
09-10
主要介绍了Mysql索引详细介绍,本文讲解了Mysql索引简介、设计索引的原则、如何使用索引等内容,需要的朋友可以参考下
全网都在说一个错误的结论
weixin_57907028的博客
10-13 3003
前文我将 product_no 字段设置为了索引，这种二级索引只有一个字段。如果将多个字段组合成一个索引，那么这种二级索引就被称为联合索引。联合索引 ``(product_no, name)` 的 B+Tree 示意图如下：可以看到，联合索引的非叶子节点用两个字段的值作为 B+Tree 的索引值。**联合索引的 B+Tree 是先按 product_no 进行排序，然后再 product_no 相同的情况再按 name 字段排序。**记住这句话，很重要！
【数据库】Mysql中between...and引起的索引失效
等待中的小码农
09-11 7016
mysql索引失效问题
9. 三星索引和Mysql内核查询成本计算实战
俺就是菜的很的博客
05-16 411
MySQL执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正的执行查询。什么是执行成本呢？I/O成本我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。CPU成本读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。
Mysql中三星索引
Zhuxiaoyu_91的博客
10-05 791
三星索引
mysql between and 索引_MySQL复合索引和运算符BETWEEN
weixin_34528410的博客
01-19 446
I have a question about this query:SELECT *FROM runsWHERE (NOW() BETWEEN began_atAND finished_at)Do you think it makes sense to create composite index for began_at and finished_at columns?Or it makes ...
MySql索引算法原理解析
03-01
而B+tree是B-tree的一个变种，大名鼎鼎的MySQL就普遍使用B+tree实现其索引结构。那数据库为什么使用这种结构？一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样...
php mysql索引问题
10-30
朋友问：今天我碰到个问题，我建个表，索引是REF，怎么才能写进一条信息，自动生成一个REF号码，记录到表中去呢？希望生成的REF号码是连续的，从00000开始。
MYSQL索引
02-24
mysql中索引是在存储引擎层而不是服务器层实现的B-Tree索引B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。图中根节点没有画出来。B-Tree对索引列是顺序组织存储的，索引很适合查找...
Mysql索引步骤及优化
12-14
MySQL索引的概念 　索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。 　...
mysql的datetime字段建立索引并比较大小
太上码农
09-20 8004
查询效率低的排查优化过程，索引的创建删除
Mysql——》三星索引
小仙~
04-21 771
三星索引其实是衡量一个索引是否达到最佳表现的三个维度。 三星索引在实际的业务中如果无法同时达到，一般认为第三颗星最重要，第一和第二颗星重要性差不多，根据业务情况调整这两颗星的优先度。一次查询通常只需要进行一次磁盘随机读以及一次窄索引片的扫描，因此其响应时间通常比使用一个普通索引的响应时间少几个数量级。
五、MySql索引基本介绍
Jian Sun_的博客
09-14 945
上篇文章介绍了查询命令中过慢的语句，总结原因如下： 查询命令过慢的原因： 在执行时需要对所有的数据来进行排序 在执行时需要对表中所有数据进行遍历 通过I/O流加载表文件次数 索引介绍： 【定义】： 索引存在于对应的索引文件中，索引本质就是排好了顺序的一组数据 【作用】： 在索引数据中进行排序时，避免再次排序 在索引数据中对数据行进行定位时，可以避免全表扫面 【索引文件位置】： 如果当前的表依赖于innodb存储引擎，索引存在于FRM文件中 如果当前的表依赖于myisam存储引擎，所以.
Sql Server 创建索引 、复合索引
热门推荐
cas3$#%nca%6nes_3sdf的博客
03-05 1万+
无
mysql优化之高效索引
gzpgood的博客
12-17 235
首先声明，非本人同意不得转载！！！ 高效索引策略   1. 尽量使用组合索引 2. 尽量不要在索引列上使用函数 3. 前缀索引也是有效的，关键是确定索引的选择性 select count(DISTINCT left(address,3)) / count(*), count(DISTINCT left(address,4)) / count(*), count(DISTINCT l...
Mysql（三）（索引）
qq_41449402的博客
12-05 509
Mysql的索引(Innodb引擎)
sql 索引 聚集索引和非聚集索引，复合索引
weixin_54477571的博客
04-18 843
1.聚集索引 --索引 优化数据查询和处理速度 快速定位到我们要查找的数据 --缺点，占储存空间 索引并不是越多越来好 合理利用索引可以有效提高数据查询效率 --聚集索引（主键索引） create clustered index pk_productinfos on pi(id) --为表pi的列id创建索引 with ( drop_existing=on --先删除原来，create一个新的 ) --删除主键索引 alter table pi drop pk_producti
10 分钟让你明白 MySQL 是如何利用索引的
weixin_45727359的博客
10-01 655
一、前言在MySQL中进行SQL优化的时候，经常会在一些情况下，对 MySQL 能否利用索引有一些迷惑。譬如:MySQL 在遇到范围查询条件的时候就停止匹配了，那么到底是哪些范围条件？M...
索引覆盖与回表
AF_0228的博客
09-09 5352
懂的越多，不懂的越多 上回书说到：什么情况下，索引下推没办法提高sql查询效率？ 表 info 主键 id 名称 name 值 value 别名 realname 对于info表，我们现在有（name,value）联合索引
mysql索引
最新发布
05-29
MySQL索引是一种数据结构，可以帮助MySQL快速定位和访问表中的数据。使用索引可以提高查询效率，降低数据库的负载。下面是MySQL索引的一些基本概念和使用方法： 1. 索引类型 MySQL支持多种类型的索引，包括B树索引...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

e421083458

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值