网站什么认证对做电商好,做网站界面需要注意什么,河南网站推广优化排名,北京东八区网站建设一、SQL执行流程
MySQL是客户端-服务器的模式。一条SQL的执行流程如下#xff1a; 在执行过程中#xff0c;主要有三类角色#xff1a;客户端、服务器、存储引擎。
大致可以分为三层#xff1a;
第一层#xff1a;客户端连接到服务器#xff0c;构造SQL并发送给服务器…一、SQL执行流程
MySQL是客户端-服务器的模式。一条SQL的执行流程如下 在执行过程中主要有三类角色客户端、服务器、存储引擎。
大致可以分为三层
第一层客户端连接到服务器构造SQL并发送给服务器。
第二层服务器收到SQL进行解析及优化最终生成执行计划并执行
第三层服务器调用存储引擎的API进行数据的查询和存储。 上述的查询缓存在MySQL8.0版本后移出原因是不太实用对表进行修改操作后需要进行删除缓存并重新添加对于一些写并不频繁的表还好但市面上还有一些更加实用做缓存的工具因此这个功能比较尴尬。 二、执行计划分析
可以通过explain提前知道当前MySQL是如何处理SQL语句的。
在我们要执行的SQL前加上explain即可。
explain select * from user;explain insert into user values(user_name, password, email) values(1, 1, 1); 通过分析执行计划我们可以得出以下几点 1.表的读取顺序 - id 2.表读取操作的类型 - select_type 3.可以被使用的索引 - possible_keys 4.实际使用的索引 - key 5.表之间的引用 - ref 6.每张表有多少行被优化器查询 - rows 上述查询结果字段的含义 1.id表示查询中执行select子句或操作表的顺序id越大执行优先级越高id相同则从上至下 2.select_type表示查询的类型用来区分普通查询、联合查询、子查询等。一共有9种类型。 3.table: 输出行所引用的表名如果使用了别名则显示别名 4.partitions使用的哪个分区 5.type查询使用了那种类型。描述的是当前如何去找数据的如all 表示扫描全表。 6.possible_keys可能有助于查询的索引 7.key实际使用的索引 8.key_len: 使用的索引的长度 9.ref显示索引的哪一列被使用了 10.rows请求数据大概返回的行数 11.filtered表示存储引擎返回的数据在server层过滤后剩下多少满足查询的记录数量的比例 12.extra: 其他信息出现Using filesort、Using temporary 意味着不能使用索引,效率会受到重大影响。应尽可能对此进行优化。 其中比较重要的字段
1.type可以看出是如何查询数据的方式。一般需要达到 ref、eq_ref 级别范围查找需要达到 range。
2.key是否使用索引如果为NULL表示没有使用索引需要优化调整。
3.rows表示返回的行数可以直观观察到结果。
4.extra有Using filesort、Using temporary 的一定需要优化。
三、表结构优化
数据库效率的影响主要是因为数据量太大进行一次查找需要扫描很多数据(硬盘上的磁头需要越过很多数据来找到目标数据)通过表结构优化的方式可以减轻当前访问的数据量。
3.1 数据类型优化
主旨就是能用小字段类型就不用大字段类型
使用简单的数据类型。int 要比 varchar 类型在mysql处理简单尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int尽可能使用 not null 定义字段因为 null 占用4字节空间。数字可以默认0字符串默认“”尽量少用 text 类型非用不可时最好考虑分表尽量使用 timestamp而非 datetime单表不要有太多字段建议在 20 以内
3.2 分库分表优化
当数据太多的时候即使走索引啥的也不能解决效率问题根本就在于要扫描的数据太多了并且存储也是比较难的。
这时候就可以采用分表的方式将一张表拆分成多张然后通过编号等手段进行查询。
拆分大致也有两种方式
垂直拆分 按照列的维度将表中的列拆分开来分别放在多张表中。例如某些字段在一张表中可能更加平凡的查询可以将这些字段放到一张表中不常用的放在另一张表中。 但需要注意的是这种方式需要保证原子性可以在进行插入的时候使用事务
水平拆分 按照行的维度将一张表的数据切分。如0-100的数据放在这张表中101-200的数据放在另一张表中。
3.3 读写分离优化
由于一台数据库服务器的性能肯定是有瓶颈的可以进行部署一个数据库集群。并采用主从的方式。设置一些主库一些从库主库用来负责写入数据从库用来负责读取数据当一个新的数据写入的时候主库需要将数据同步到从库中以保证数据的完整性。
四、查询语句优化
4.1避免使用 select *
sql在解析过程中还需要把*依次转换为所有的列名这个工作需要查询数据字典完成。额外开销因此建议将需要的列写出来。
4.2多表联查时小表在前大表在后
from 后的表关联查询是从左往右执行的Oracle相反第一张表会涉及到全表扫描所以将小表放在前面先扫小表扫描快效率较高在扫描后面的大表。
4.3调整where子句中的连接顺序
where子句是从左往右自上而下的顺序执行的Oracle相反根据这个原理应将过滤数据多的条件往前放最快速度缩小结果集。
4.4调整group by和order by子句中的顺序
group by和order by子句是从左往右的顺序执行的根据这个原理应将排序影响数据多的条件往前放最快速度缩小结果集。
4.5用exists、not exists和in、not in相互替代
exists以外层表为驱动表先被访问适合于外表小而内表大的情况。
in则是先执行子查询适合外表大而内表小的情况一般情况是不推荐使用not in因为效率非常低。
原则是哪个的子查询产生的结果集小就选哪个
4.6用where子句替换having子句
where子句搜索条件在进行分组操作之前应用而having子句条件在进行分组操作之后应用。
尽可能让where来缩小结果集
4.7分段和分页查询
使用合理的分页方式在数据表量级逐渐增加的时候limit分页查询的效率会降低。
可以根据字段索引进行快速定位直接找到偏移量。
五、索引优化
众所周知索引是通过牺牲空间来换取时间的。当我们频繁的去进行插入/删除的时候会影响性能因此索引更适合于“读多写少”的场景。
索引可以帮助我们提高查询效率因此需要避免索引失效的场景。
5.1尽量避免在字段开头模糊查询
select * from user where user_name like %陈%; -不走索引
select * from user where user_name like 陈%; -走索引
5.2尽量避免使用not in和in
如果是连续数值可以用between代替
select * from user id in(1,2); -不走索引
select * from user id between 1 and 2; -走索引
如果是子查询可以用exists代替
select * from A where A.id in (select id from B); -- 不走索引
select * from A where exists (select * from B where B.id A.id); -- 走索引
5.3尽量避免使用or或in
SELECT * FROM t WHERE id 1 OR id 3; -- 不走索引
SELECT * FROM t WHERE id in (1, 3); -- 不走索引
SELECT * FROM t WHERE id 1UNION
SELECT * FROM t WHERE id 3; -- 走索引
5.4尽量避免查询条件使用用!或者
5.5尽量避免进行null值的判断
优化方式可以给字段添加默认值0对0值进行判断.
SELECT * FROM t WHERE score IS NULL; -- 不走索引
SELECT * FROM t WHERE score 0; -- 走索引
5.6尽量避免在where条件中等号的左侧进行表达式、函数操作
SELECT * FROM T WHERE score/10 9; -- 不走索引
SELECT * FROM T WHERE score 9*10; -- 走索引
5.7尽量避免隐式数据类型转换
有些字符串可以自动转换为数字类型可以避免转换
5.8尽量避免使用复合索引时不使用第一个索引列
复合联合索引包含key1key2key3三列SQL语句没有包含索引前置列key1
select col1 from table where key22 and key33; -- 不走索引
select col1 from table where key11 and key22 and key33; -- 走索引
5.9order by 条件要与where中条件一致否则order by不会利用索引进行排序
SELECT * FROM t order by age; -- 不走age索引
SELECT * FROM t where age 0 order by age; -- 走age索引
当order by 中的字段出现在where条件中时才会利用索引而不再二次排序更准确的说order by 中的字段在执行计划中利用了索引时不用排序操作。
