WordPress网站打不开nginx,永久建站平台,个人网站 云服务器,淮安集团网站建设SQL的执行流程
mysql主要分为Server层和存储引擎层#xff0c;Server层#xff1a;主要包括连接器、查询缓存、分析器、优化器、执行器等#xff0c;所有跨存储引擎的功能都在这一层实现#xff0c;比如存储过程、触发器、视图#xff0c;函数等#xff0c;还有一个通用…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 PushdownICP就是把where条件下推到存储层去过滤更多的数据从而提高查询效率。 下面这种情况一般使用到了索引下推extra Using index condition
Mysql如何选择索引
Mysql底层会为各种情况计算一个成本cost ,首先Mysql会对SQL语句做一些优化然后会根据where条件获取到可以选择的索引然后会预估出选择每种索引所需要的成本(Cost)以及全表扫描所需要的成本。Mysql会选择成本最少的那个方案去执行
那么如何看到Mysql的索引选择方案呢我们通过 set session optimizer_traceenabledon,end_markers_in_jsonon; 开启Mysql的SQL自行计划信息跟踪然后通过SELECT * FROM information_schema.OPTIMIZER_TRACE;来跟踪执行计划如下
mysql set session optimizer_traceenabledon,end_markers_in_jsonon;
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/2100 可以优化为 select id from t where num100*2
减少or in 的使用
少用or或in 比如 select id from t where num10 or num20 可以优化为select id from t where num10 union all select id from t where num20 ,对于连续的数值能用 between 就不要用 in 了
避免null值查询
应尽量避免在 where 子句中对字段进行 null 值判断否则将导致引擎放弃使用索引而进行全表扫描如select id from t where num is null 可以在num上设置默认值0确保表中num列没有null值然后这样查询 select id from t where num0
全文检索代替Like
下面SQL会导致索引失效 select id from t where name like %abc% 若要提高效率可以考虑全文检索或者采用ES
避免过度索引
每个额外的索引都会占用存储空间并可能降低写操作的性能,索引并不是越多越好索引固然可以提高相应的 select 的效率但同时也降低了 insert 及 update 的效率因为 insert 或 update 时有可能会重建索引所以怎样建索引需要慎重考虑视具体情况而定若太多则应考虑一些不常使用到的列上建的索引是否有必要。 9. 强制使用索引 即使满足索引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。但是如果ID不连续的话那么这个结果就不准确了。
减少数据范围再分页
另外一个方案就是我们可以先通过子查询把需要分页的数据范围过滤出来在进行分页比如我们查询第N页数据
SELECT * FROM user
WHERE id (SELECT id FROM userLIMIT (N-1)*10, 1) 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 * fro 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
类型占用空间有符号范围无符号范围TINYINT1-128到1270到255SMALLINT2-32768到327670到65535MEDIUMINT3-8388608到83886070到16777215INT4-2147483648到21474836470到4294967295BIGINT8-9223372036854775808到92233720368547758070到18446744073709551615FLOAT4DOUBLE8
2.字符串类型
char : 如果能确定字符串长度建议用 char,查找效率会比较高比如年龄身份证手机号varchar : 如果不能确定字符串长度使用varchar他能根据内容长度确定空间大小更能节约空间。比如商品标题商品描述对于大文本可以使用text,建议单独抽一个表来存储然后做外键关联避免大文本拖慢主表的查询。
文章就写到这如果对你有帮助不要吝啬点赞收藏评论哦
