数据库优化-(三)索引优化实战

最新推荐文章于 2024-08-16 18:55:06 发布

乖巧程序员

最新推荐文章于 2024-08-16 18:55:06 发布

阅读量280

点赞数 4

分类专栏：数据库优化专题文章标签：数据库

本文链接：https://blog.csdn.net/xuhanyii/article/details/140838674

版权

数据库优化专题专栏收录该内容

3 篇文章 0 订阅

订阅专栏

SQL的执行流程

mysql主要分为Server层和存储引擎层，Server层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog日志模块。

存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持InnoDB、MyISAM、Memory等多个存储引擎，其中InnoDB引擎有自有的日志模块redolog 模块。InnoDB 5.5.5版本作为默认引擎。

SQL执行流程如下：

连接器

主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。

查询缓存

连接建立后，执行查询语句的时候，会先查询缓存，Mysql会先校验这个sql是否执行过，以Key-Value的形式缓存在内存中，Key是查询预计，Value是结果集。如果缓存key被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

Mysql 查询不建议使用缓存，因为对于经常更新的数据来说，缓存的有效时间太短了，往往带来的效果并不好，对于不经常更新的数据来说，使用缓存还是可以的，Mysql 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

分析器

mysql 没有命中缓存，那么就会进入分析器，分析器主要是用来分析SQL语句是来干嘛的，分析器也会分为几步：

第一步，词法分析，一条SQL语句有多个字符串组成，首先要提取关键字，比如select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。

第二步，语法分析，主要就是判断你输入的sql是否正确，是否符合mysql的语法。

完成这2步之后，mysql就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

优化器

优化器的作用就是它认为的最优的执行方案去执行（虽然有时候也不是最优），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

执行器

当选择了执行方案后，mysql就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用存储引擎的接口，返回接口执行的结果。

提示：在Mysql 8.0之后去掉了查询缓存，因为命中率比较低。

Mysql索引下推

什么是索引下推呢，这里先举个例子：select * from user where name like "zs%" and age = 10 ，加入我们按照(name,age)创建了组合索引,Mysql执行这个SQL的顺序是：

先拿着 zs 去最终匹配 name辅助索引
回表到聚餐索引查询匹配的所有数据
基于匹配到的所有数据再进行 age 过滤
把age过滤后的数据返回给客户端

在这个过程中，根据zs去匹配name辅助索引的这个动作是在Mysql存储层完成的，然后过滤age这个动作是在Mysql的服务层完成的。

而索引下推（Index Condition Pushdown，ICP）就是把where条件下推到存储层去过滤更多的数据从而提高查询效率。

下面这种情况一般使用到了索引下推：extra = Using index condition

Mysql如何选择索引

Mysql底层会为各种情况计算一个成本cost ,首先：Mysql会对SQL语句做一些优化，然后会根据where条件获取到可以选择的索引，然后会预估出选择每种索引所需要的成本(Cost)，以及全表扫描所需要的成本。Mysql会选择成本最少的那个方案去执行

那么如何看到Mysql的索引选择方案呢？我们通过 set session optimizer_trace="enabled=on",end_markers_in_json=on; 开启Mysql的SQL自行计划信息跟踪，然后通过SELECT * FROM information_schema.OPTIMIZER_TRACE;来跟踪执行计划，如下

mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;
mysql> select * from user where name > 'a' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;

执行完之后，在trace里面就是执行计划

trace内容格式化后如下

{
  "steps": [
    {
      "join_preparation": {	//SQL优化阶段
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `t_user`.`id` AS `id`,`t_user`.`name` AS `name`,`t_user`.`age` AS `age`,`t_user`.`sex` AS `sex`,`t_user`.`dept_id` AS `dept_id`,`t_user`.`phone` AS `phone`,`t_user`.`notes` AS `notes`,`t_user`.`username` AS `username` from `t_user` where ((`t_user`.`name` = 'zs') and (`t_user`.`age` = 19))"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {	//索引优化阶段
        "select#": 1,
        "steps": [
          {
            "condition_processing": { //处理条件
              "condition": "WHERE",
              "original_condition": "((`t_user`.`name` = 'zs') and (`t_user`.`age` = 19))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`t_user`.`name` = 'zs') and multiple equal(19, `t_user`.`age`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`t_user`.`name` = 'zs') and multiple equal(19, `t_user`.`age`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`t_user`.`name` = 'zs') and multiple equal(19, `t_user`.`age`))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`t_user`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`t_user`",
                "field": "name",
                "equals": "'zs'",
                "null_rejecting": false
              },
              {
                "table": "`t_user`",
                "field": "name",
                "equals": "'zs'",
                "null_rejecting": false
              },
              {
                "table": "`t_user`",
                "field": "age",
                "equals": "19",
                "null_rejecting": false
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [	//计算表的访问成本
              {
                "table": "`t_user`",
                "range_analysis": {
                  "table_scan": {	//全表扫描的成本
                    "rows": 2,	//扫描行数
                    "cost": 3.5	//扫描成本
                  } /* table_scan */,
                  "potential_range_indexes": [	//扫描可以用到的索引
                    {
                      "index": "PRIMARY",
                      "usable": false,	//不使用主键索引
                      "cause": "not_applicable"
                    },
                    {
                      "index": "name",
                      "usable": true,	//可以使用name索引
                      "key_parts": [
                        "name",
                        "id"
                      ] /* key_parts */
                    },
                    {
                      "index": "name_2",
                      "usable": true,	//可以使用name2索引
                      "key_parts": [
                        "name",
                        "age",
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": { //分析索引的成本
                    "range_scan_alternatives": [
                      {
                        "index": "name",	//索引名：name索引
                        "ranges": [
                          "zs <= name <= zs"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,	//是否使用覆盖索引
                        "rows": 2,
                        "cost": 3.41,
                        "chosen": true	//是否可以选择该索引
                      },
                      {
                        "index": "name_2",
                        "ranges": [
                          "zs <= name <= zs AND 19 <= age <= 19"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 1,
                        "cost": 2.21,
                        "chosen": true
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "intersecting_indexes": [
                        {
                          "index": "name_2",
                          "index_scan_cost": 1,
                          "cumulated_index_scan_cost": 1,
                          "disk_sweep_cost": 1,
                          "cumulated_total_cost": 2,
                          "usable": true,
                          "matching_rows_now": 1,
                          "isect_covering_with_this_index": false,
                          "chosen": true
                        },
                        {
                          "index": "name",
                          "cumulated_total_cost": 2,
                          "usable": false,
                          "cause": "does_not_reduce_cost_of_intersect"
                        }
                      ] /* intersecting_indexes */,
                      "clustered_pk": {
                        "clustered_pk_added_to_intersect": false,
                        "cause": "no_clustered_pk_index"
                      } /* clustered_pk */,
                      "chosen": false,
                      "cause": "too_few_indexes_to_merge"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "name_2",
                      "rows": 1,
                      "ranges": [
                        "zs <= name <= zs AND 19 <= age <= 19"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 1,
                    "cost_for_plan": 2.21,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t_user`",
                "best_access_path": {
                  "considered_access_paths": [	//最终选择的索引
                    {
                      "access_type": "ref",
                      "index": "name",
                      "rows": 2,
                      "cost": 2.4,
                      "chosen": true	//选择name索引
                    },
                    {
                      "access_type": "ref",
                      "index": "name_2",
                      "rows": 1,
                      "cost": 1.2,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "name_2"
                      } /* range_details */,
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 1.2,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`t_user`.`age` = 19) and (`t_user`.`name` = 'zs'))",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t_user`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`t_user`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

需要注意的是，虽然Mysql可以帮我们优化SQL，选择合适的索引，但是他的优化只是一种预估，它没有办法把每种索引都去执行一遍然后选择最后，所以我们不能完全依赖Mysql的优化，有的时候它的选择也不一定是最优的。所以我们一定要尽可能的按照最优的方式去编写SQL。

OrderBy原理

如果排序基于索引列排序性能是最优的，也就是expain的extra的值是：use index 如：explain select name from t_user order by name

如果没发按照索引列排序那么会基于文件进行排序(use filesort)，效率会比较低一些，如：explain select * from t_user order by age

但是也不是绝对，即使使用索引列排序Mysql也有可能会基于 file进行排序，Mysql有自己的优化，如果where条件没有选择索引，那排序也不会走索引排序。所以我们编写SQL是需要尽可能按照索引列排序的。

Mysql排序分为单路和双路排序，单路排序是：把SQL查询的列全部取出来在sort buffer内存中进行排序，双路排序是：只把数据ID和排序列取出来，排序好之后，再根据ID取出需要查询的列。一般查询的数据列比较多导致结果比较大那么就会选择多路排序，可以通过： max_length_for_sort_data(默认1024字节) 来控制单路或者多路

Mysql通过计算查询列结果数据大小如果大于 max_length_for_sort_data 大小，就会采用双路排序，小于就会采用单路排序，所以：max_length_for_sort_data 越大就越少使用多路排序

我们先看单路排序的详细过程：

从索引中找到满足条件的数据，回表查询出所有的列的数据，存入 sort_buffer 中
对 sort_buffer 中的数据按照排序字段进行排序
返回结果给客户端

我们再看下双路排序的详细过程：

从索引中找到满足条件的数据，取出id和排序列
把排序字段和主键 id 这两个字段放到 sort buffer 中
对 sort_buffer 中的数据按照排序字段进行排序
遍历排序好的数据，按照 id 的值回到原表中取出所有字段的值返回给客户端

其实对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。
如果 MySQL 排序内存有条件可以配置比较大，可以适当增大 max_length_for_sort_data 的值，让优化器优先选择全字段排序(单路排序)，把需要的字段放到 sort_buffer 中，这样排序后就会直接从内存里返回查询结果了。如果没有条件增加内存那就把max_length_for_sort_data设置小一点走双路排序，另一个优化原则是尽可能让查询的结果变少来优化排序性能。

索引优化最佳实践

了解了索引的原理之后我们就可以针对性的进行优化了，下面是一些优化技巧

选择最有效的索引

如果能按照ID列作为条件查询性能是最优的，如果不能，那么需要找到where条件中`唯一性最高，且查询频率最高`的列，比如：手机号，用户名，这样的列让他去命中索引。并且把它放到where条件的一个条件比较好，这样Mysql可以直接选择选择它作为索引查询列

如果order by 排序的列是索引列那么性能是最优的，因为索引列本来就是有序的,group by 和 order by类似，having尽可能少用，优先把条件放到where中

尽可能使用覆盖索引

如果查询的列整合包含在辅助索引的健值中，那么就不用回表，性能会大大提升，所以尽可能去命中覆盖索引，比如按照：a,b,c 三个列创建组合索引，而查询SQL是 select a , b from table where a = x and b = x 这样a,b两个列直接包含在索引中，命中索引后直接返回不需要回表。所以尽可能避免用select *

复合索引-最左匹配

当多个列经常一起出现在查询条件中时，考虑使用复合索引。注意复合索引的列顺序，将最常用于筛选的列放在前面。联合索引尽可能覆盖到查询的列或排序的列
在复合索引中，应将最常用于查询条件的列放在最左侧，以提高查询效率，比如按照：a,b,c 三个列创建组合索引，那么where 条件尽可能按照 a , b ,c的顺序where a = x and b = x and c = x 排列

避免在索引列进行计算

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描，比如： where date(time) ='2018-09-30'; 可以优化成where time >='2018-09-30 00:00:00' and time <='2018-09-30 是可以走索引的.再比如：select id from t where num/2=100 可以优化为 select id from t where num=100*2

减少or in 的使用

少用or或in 比如： select id from t where num=10 or num=20 可以优化为select id from t where num=10 union all select id from t where num=20 ,对于连续的数值，能用 between 就不要用 in 了

避免null值查询

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

全文检索代替Like

下面SQL会导致索引失效：select id from t where name like '%abc%' 若要提高效率，可以考虑全文检索，或者采用ES

避免过度索引

每个额外的索引都会占用存储空间并可能降低写操作的性能,索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

强制使用索引

即使满足索引Mysql也不一定会使用索引，Mysql会按照自己的方式对SQL进行优化，它认为不走索引更快那就不会走索引，如果你认为使用了索引效率会更高可以在SQL中指定强制使用索引比如：select * from table force index（索引名）where . . . ，但是也应该针对使用索引和不使用索引做一个测试，哪种快用哪种。

长字符串采用前缀索引

对于较长的文本可以采用前缀索引 KEY name ( name (20)) USING BTREE,那么在查询的时候可以最左匹配20个字符，然后再回表到主键索引回表取完整数据

小结果集驱动大结果集

如果2张表或者2个结果集关联查询，把数据量小的结果集放到join左边，然后给关联的字段增加一个索引(外键)，这样性能可以得到提升。举例：A表100W JOIN B表 10W，如果A表在左边，那么MySQL需要取出100W次数据取B表的索引中去比较，如果B表再左边那就只需要取出10W次数据取A表的索引中去比较，性能是有所提升的。

再说几个实际的优化案例：

在查询用户的账户流水的时候，由于数据量比较大进行了索引优化，这种数据和时间关联比较强，优化手段是让用户必须选择一个时间范围，然后给表以流水时间字段创建索引，这样每次查询都可以命中时间字段索引
一条SQL有多个条件 where aa=值 and bb=值 and cc=值，这些字段查询也比较频繁，那么我们完全可以创建一个联合索引(aa,bb,cc)
比如有下面这条SQL select * from user u join dept d on d.id = u.dept_id where u.name = "张三" 完全可以优化成 select * from (select * from user where name = "张三") u join dept d on u.dept_id = d.id 然后给name字段增加一个索引。这样的好处是通过子查询已经把name="张三"的数据过滤出来，用很少的数据取join dept,这样性能非常高

Limit 分页优化

连续ID分页优化

先来看一条SQL select * from user limit 100000,10 ,这条SQL会比较慢，因为MySql会全表扫描100010条数据，然后取最后10条数据，我在自己电脑上测试400W数据需要 2 S才可以查询出来。那么SQL可以如何优化呢？

因为该SQL没有指定Order By 条件，所以默认按照主键ID排序的，如果在ID值连续没有断层的情况下我们可以这样改造：

select * from user where id > 10000 limit 10

解题思路是：先通过：SELECT id FROM user LIMIT (N-1)*10, 1 取到需要的结果集中最小的那条数据ID，然后再以 id > 来取后面的数据范围，然后再limit 取结果，下面画了一个示意图：

我们通过expain来分析区别，第一条SQL EXPLAIN select * from t_user limit 4000000,10 很明显是走的全表扫描，性能差

第二条SQL：子查询是走了辅助索引的 EXPLAIN select * from t_user where id > (select id from t_user limit 4000000,1) limit 10：

因为辅助索引的叶子存储就是ID，所以他走辅助索引进行index扫描就能拿到ID值。然后在执行 'where id > ID值’的时候可以走主键索引的range扫描，然后再少量的数据中进行 limit,性能会快很多。优化之后的SQL在我自己电脑上测试 0.5S内就能查询出来

利用覆盖索引优化

另外一种情况：如果我们的SQL带有 Order By的情况如：select * from t_user order by name limit 4000000,10, 这种SQL简直是灾难，400W数据这条SQL在我的电脑上查询了12S,这里用expain分析一下：

EXPLAIN select * from t_user order by name  limit 4000000,10

可以看到虽然给name列创建了索引，但是依然没有走索引，因为数据量太大会导致很多次回表性能并不高所以Mysql放弃了走索引排序。 那这种SQL怎么优化呢？

因为有分页，我们可以先分页，在少量数据中进行排序，优化如下：

explain select * from t_user u1 
JOIN (select id from t_user order by name limit 4000000,10) u2 
on u1.id = u2.id

除此之外还需要给name列创建索引，因为要让order by name 走索引排序，优化之后可以在0.6S查询出结果，分析一下SQL：

可以看到，子SQL是走了索引的，因为select id from t_user limit 4000000,10,只查询id列，那么可以直接走覆盖索引，拿到排好序和分页后的ID再去取完整的数据列。这个优化技巧其实就是想办法让他命中覆盖索引。

JOIN连表优化

Mysql在处理JOIN连表的时候采用了2种算法：

Nested-Loop Join ：嵌套循环连接算法，简称 NLJ 算法
Block Nested-Loop Join 算法：基于块的嵌套循环连接，简称 BNLJ

Nested-Loop Join

NLJ 适用于在连表条件有索引的情况，而BNLJ适用于链表条件没有索引的情况，我们分开来说一说

先说 NLJ 算法，下面有一个JOIN的SQL
EXPLAIN select u.dept_id from t_dept d inner join t_user u on u.dept_id = d.dept_id ，在user表的dept_id字段创建了索引，通过explain查看执行计划如下

可以看出它的执行流程是：先从 d 表(驱动表)中全表扫描拿出每个 dept_id ，去 u表(被驱动表)的索引中进行扫描，有点类似于

for(long d in d表){
	 select * from u where u.dept_id = d.dept_id	//走索引扫描
}

这种链表方式叫做：嵌套循环连接 Nested-Loop Join 算法（NLJ算法），连接条件有索引的时候默认采用的算法，它通过一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

Block Nested-Loop Join

然后再来说说 BNLJ 算法，它是在连表条件没有索引的情况下使用，如果把上面SQL的索引去掉，再执行EXPLAIN select u.dept_id from t_dept d inner join t_user u on u.dept_id = d.dept_id

可以看到现在是走的全表扫描，并且：extra = Using where; Using join buffer (Block Nested Loop) 这个就是基于块的嵌条循环连接，他的执行流程是：把d(驱动表)的数据读入到 join_buffer 中，然后扫描被驱动表(u)，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。然后取出两张表的结果合集

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话会采用分段的方式，就是一次放一部分。所以join_buffer 的大小对SQL的性能影响也很关键

JOIN优化

对于表JOIN条件如果有索引最好走索引性能会比较高，对于没有索引的JOIN条件就没法走索引，但是Mysql基于内存进行过滤性能也不会太差，所以对于我们来说我们需要做的是就是2个事情

表关联字段尽可能去加索引，这样的话就可以采用NLJ嵌套循环算法
尽可能使用小表驱动大表，比如：用100条数据的表去驱动10000条数据的表，那么只需要扫描100条数据，然后去大表的索引中进行匹配效率较高, 可以使用 straight_join 来代替join ，理论上Mysql会自己优化为小表驱动大表，但是有可能Mysql会选错，所以我们可以通过straight_join 来强制指定左边的表来驱动右边的表
如果只能使用BNL基于块的连表算法，那么需要注意调整join buffer的内存大小来提高JOIN效率

其他优化

in 和 exist 优化

对于in和exist的优化原则，小表驱动大表，即小的数据集驱动大的数据集下面有2个SQL：

select * from a where id in (select id from b )
select * from a where id EXISTS(select 1 from b where b.id = a.id)

这2个SQL的区别在于前者是先执行 select id from b ，后者是先执行 select * from a ,所以按照小结果驱动大结果集的原则，如果是b表小就使用 in ，如果是a表小就使用 exists 。

Count性能比较

对于Count也是可以走索引统计条数的，下面有几种情况我们来比较一下性能区别，下面是有索引的情况：

count(id) : 走主键索引扫描，取出id列的值，然后统计条数，explain 分析如下：

count(1) : 走主键索引扫描，不会取字段值，直接使用常量1 统计条数，explain 分析如下：

count(name) : 走二级索引扫描，然后取出name的值进行统计条数，explain 分析如下：

count( * ) : 有二级索引走二级，没二级索引就走主键索引扫描，然后统计条数，count(*) 是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高explain 分析如下：

其实四种方式都差不多，只是有细微的性能差别，在有索引的情况下性能从高到底如下： count(*) > count(1) > count(name) > count(id) 。分析：count( * )可以走主键索引，且MySql做了单独优化性能最高；count(1) 也可以走主键索引，统计条数的时候直接以常量方式统计； count(name)走二级索引扫描，它比count(id)快的原因是主键索引(聚簇)体积更大。

在没有索引的情况下：count(name) 性能是最差的，因为无法索引，而其他三者都可以走主键索引。所以在统计条数的时候可以放心的使用count(*)

对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算

如果只需要知道表总行数的估计值可以用如下sql查询，性能很高。或者可以把count值维护到redis中。

数据库字段选择

在MySQL中，选择正确的数据类型，对于性能至关重要。一般应该遵循下面两步：

确定合适的大类型：数字、字符串、时间、二进制；
确定具体的类型：有无符号、取值范围、变长定长等。

在MySQL数据类型设置方面，尽量用更小的数据类型，因为它们通常有更好的性能，花费更少的硬件资源。并且，尽量把字段定义为NOT NULL，避免使用NULL。

1.数值类型

如果能确定值的大小尽可能选择小类型存储可以节约空间，比如对于性别：可以采用TINYINT 来存储，而不要选择 INT
如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。
如果数值需要计算，对精度要求高使用decimal

类型	占用空间	有符号范围	无符号范围
TINYINT	1	-128到127	0到255
SMALLINT	2	-32768到32767	0到65535
MEDIUMINT	3	-8388608到8388607	0到16777215
INT	4	-2147483648到2147483647	0到4294967295
BIGINT	8	-9223372036854775808到9223372036854775807	0到18446744073709551615
FLOAT	4
DOUBLE	8

2.字符串类型

char : 如果能确定字符串长度，建议用 char,查找效率会比较高，比如：年龄，身份证，手机号
varchar : 如果不能确定字符串长度，使用varchar，他能根据内容长度确定空间大小，更能节约空间。比如：商品标题，商品描述
对于大文本可以使用text,建议单独抽一个表来存储，然后做外键关联，避免大文本拖慢主表的查询。

文章结束啦，如果对你有帮助的话，请一定给个好评哦~~

乖巧程序员

关注

4
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
数据库优化-(三)索引优化实战

主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。如果能按照ID列作为条件查询性能是最优的，如果不能，那么需要找到where条件中`唯一性最高，且查询频率最高`的列，比如：手机号，用户名，这样的列让他去命中索引。所以我们编写SQL是需要尽可能按照索引列排序的。
复制链接

扫一扫