优化大数据表的查询

淩亂°似流年 2022-09-04 14:49 203阅读 0赞

**一个含有一千零一万条数据的表**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70][]select count(\*) from table 耗时为3.437s。

**根据数据id和其他字段查询数据**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70 1][]

根据结果可知，对于主键id查询的时间接近为0s，而对于不含有索引的字段查询达到了5.6s。

**分析不同查询的结果**

根据主键id查询

![20210815101731254.png][]

根据不含有索引的字段进行查询

![2021081510191493.png][]

根据分析可知，逐渐查询只查询了一条数据，而根据不含索引字段查询则进行全表扫描，查询了9942895行。

**给字段phone加索引**

> **create index phone\_index on app\_user(phone);**

![20210815102829584.png][]增加索引耗时为 27.687s

增加索引查询的第一次

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70 2][]

增加索引查询的第二次

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70 3][]

结果可知，第一次查询为0.016s，而第二次查询为0s

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70 4][]

分析可知，增加了索引之后，查询只是对结果集的两行进行扫描。

**删除索引**

> **Drop index phone\_index on app\_user;**

![20210815102706637.png][]

耗时0.031s.

**查看表的索引**

> **show index from app\_user;**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70 5][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70]: /images/20220829/455f443efb2649c38bd0f2f6202751cb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70 1]: /images/20220829/f43282d907b144b38d186b052c9d3b5e.png
[20210815101731254.png]: /images/20220829/592c62e4a4e34cafa324d86440688b17.png
[2021081510191493.png]: /images/20220829/033f74fb52794efd83a7ea6a9f0555d9.png
[20210815102829584.png]: /images/20220829/7adde1d3bb614f92ad3cb84a500b36db.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70 2]: /images/20220829/513a1e964c3d4221aa877a0a5c657495.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70 3]: /images/20220829/bfd46f278319468285dd8ffc205a0c29.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70 4]: /images/20220829/15d727c075f6419ca3dcff3c151c9772.png
[20210815102706637.png]: /images/20220829/a6eefbb2437940cdb7702dbaff387ed3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70 5]: /images/20220829/d110d41626924fecb62723b49f3dd862.png