上海整形网站建设,开封市建设局网站,网站做推广页需要什么,产品线上推广渠道收录于热门专栏Java基础教程系列#xff08;进阶篇#xff09;
1、先了解MySQL的执行过程
了解了MySQL的执行过程#xff0c;我们才知道如何进行sql优化。
客户端发送一条查询语句到服务器#xff1b;服务器先查询缓存#xff0c;如果命中缓存#xff0c;则立即返回存…
收录于热门专栏Java基础教程系列进阶篇
1、先了解MySQL的执行过程
了解了MySQL的执行过程我们才知道如何进行sql优化。
客户端发送一条查询语句到服务器服务器先查询缓存如果命中缓存则立即返回存储在缓存中的数据未命中缓存后MySQL通过关键字将SQL语句进行解析并生成一颗对应的解析树MySQL解析器将使用MySQL语法进行验证和解析。例如验证是否使用了错误的关键字或者关键字的使用是否正确预处理是根据一些MySQL规则检查解析树是否合理比如检查表和列是否存在还会解析名字和别名然后预处理器会验证权限根据执行计划查询执行引擎调用API接口调用存储引擎来查询数据将结果返回客户端并进行缓存 2、数据库常见规范
所有数据库对象名称必须使用小写字母并用下划线分割所有数据库对象名称禁止使用mysql保留关键字数据库对象的命名要能做到见名识意并且最后不要超过32个字符临时库表必须以tmp_为前缀并以日期为后缀备份表必须以bak_为前缀并以日期(时间戳)为后缀所有存储相同数据的列名和列类型必须一致
3、所有表必须使用Innodb存储引擎
没有特殊要求即Innodb无法满足的功能如列存储存储空间数据等的情况下所有表必须使用Innodb存储引擎mysql5.5之前默认使用Myisam5.6以后默认的为Innodb。
Innodb 支持事务支持行级锁更好的恢复性高并发下性能更好。
4、每个Innodb表必须有个主键
Innodb是一种索引组织表数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引但是表的存储顺序只能有一种。
Innodb是按照主键索引的顺序来组织表的
不要使用更新频繁的列作为主键不适用多列主键不要使用UUID、MD5、HASH、字符串列作为主键无法保证数据的顺序增长主键建议使用自增ID值
5、数据库和表的字符集统一使用UTF8
兼容性更好统一字符集可以避免由于字符集转换产生的乱码不同的字符集进行比较前需要进行转换会造成索引失效如果数据库中有存储emoji表情的需要字符集需要采用utf8mb4字符集。
6、查询SQL尽量不要使用select *而是具体字段
select *的弊端
增加很多不必要的消耗比如CPU、IO、内存、网络带宽增加了使用覆盖索引的可能性增加了回表的可能性当表结构发生变化时前端也需要更改查询效率低
7、避免在where子句中使用 or 来连接条件
使用or可能会使索引失效从而全表扫描对于or没有索引的salary这种情况假设它走了id的索引但是走到salary查询条件时它还得全表扫描也就是说整个过程需要三步全表扫描索引扫描合并。如果它一开始就走全表扫描直接一遍扫描就搞定虽然mysql是有优化器的处于效率与成本考虑遇到or条件索引还是可能失效的
8、尽量使用数值替代字符串类型
因为引擎在处理查询和连接时会逐个比较字符串中每一个字符而对于数字型而言只需要比较一次就够了字符会降低查询和连接的性能并会增加存储开销
9、使用varchar代替char
varchar变长字段按数据内容实际长度存储存储空间小可以节省存储空间char按声明大小存储不足补空格其次对于查询来说在一个相对较小的字段内搜索效率更高
10、财务、银行相关的金额字段必须使用decimal类型
非精准浮点float,double精准浮点decimal
Decimal类型为精准浮点数在计算时不会丢失精度占用空间由定义的宽度决定每4个字节可以存储9位数字并且小数点要占用一个字节可用于存储比bigint更大的整型数据
11、避免使用ENUM类型
修改ENUM值需要使用ALTER语句ENUM类型的ORDER BY操作效率低需要额外操作禁止使用数值作为ENUM的枚举值
12、去重distinct过滤字段要少
带distinct的语句占用cpu时间高于不带distinct的语句当查询很多字段时如果使用distinct数据库引擎就会对数据进行比较过滤掉重复数据然而这个比较、过滤的过程会占用系统资源如cpu时间
13、where中使用默认值代替null
并不是说使用了is null或者 is not null就会不走索引了这个跟mysql版本以及查询成本都有关如果mysql优化器发现走索引比不走索引成本还要高就会放弃索引这些条件 !is nullis not null经常被认为让索引失效其实是因为一般情况下查询的成本高优化器自动放弃索引的如果把null值换成默认值很多时候让走索引成为可能同时表达意思也相对清晰一点
14、避免在where子句中使用!或操作符
使用!和很可能会让索引失效应尽量避免在where子句中使用!或操作符否则引擎将放弃使用索引而进行全表扫描实现业务优先实在没办法就只能使用并不是不能使用
15、inner join 、left join、right join优先使用inner join
三种连接如果结果相同优先使用inner join如果使用left join左边表尽量小。
inner join 内连接只保留两张表中完全匹配的结果集left join会返回左表所有的行即使在右表中没有匹配的记录right join会返回右表所有的行即使在左表中没有匹配的记录
为什么
如果inner join是等值连接返回的行数比较少所以性能相对会好一点使用了左连接左边表数据结果尽量小条件尽量放到左边处理意味着返回的行数可能比较少这是mysql优化原则就是小表驱动大表小的数据集驱动大的数据集从而让性能更优
16、提高group by语句的效率
1、反例
先分组再过滤
select job, avgsalary from employee
group by job
having job develop or job test;2、正例
先过滤后分组
select jobavgsalary from employee
where job develop or job test
group by job;3、理由
可以在执行到该语句前把不需要的记录过滤掉
17、清空表时优先使用truncate
truncate table在功能上与不带 where子句的 delete语句相同二者均删除表中的全部行。但 truncate table比 delete速度快且使用的系统和事务日志资源少。
delete语句每次删除一行并在事务日志中为所删除的每行记录一项。 truncate table通过释放存储表数据所用的数据页来删除数据并且只在事务日志中记录页的释放。
truncate table删除表中的所有行但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值请改用 DELETE。如果要删除表定义及其数据请使用 drop table语句。
对于由 foreign key约束引用的表不能使用 truncate table而应使用不带 where子句的 DELETE 语句。由于 truncate table不记录在日志中所以它不能激活触发器。
truncate table不能用于参与了索引视图的表。
18、操作delete或者update语句加个limit或者循环分批次删除
1降低写错SQL的代价
清空表数据可不是小事情一个手抖全没了删库跑路如果加limit删错也只是丢失部分数据可以通过binlog日志快速恢复的。
2SQL效率很可能更高
SQL中加了limit 1如果第一条就命中目标return 没有limit的话还会继续执行扫描表。
3避免长事务
delete执行时,如果age加了索引MySQL会将所有相关的行加写锁和间隙锁所有执行相关行会被锁住如果删除数量大会直接影响相关业务无法使用。
4数据量大的话容易把CPU打满
如果你删除数据量很大时不加 limit限制一下记录数容易把cpu打满导致越删越慢。
5锁表
一次性删除太多数据可能造成锁表会有lock wait timeout exceed的错误所以建议分批操作。
19、UNION操作符
UNION在进行表链接后会筛选掉重复的记录所以在表链接后会对所产生的结果集进行排序运算删除重复的记录再返回结果。 实际大部分应用中是不会产生重复的记录最常见的是过程表与历史表UNION。如
select username,tel from user
union
select departmentname from department这个SQL在运行时先取出两个表的结果再用排序空间进行排序删除重复的记录最后返回结果集如果表数据量大的话可能会导致用磁盘进行排序。 推荐方案采用UNION ALL操作符替代UNION因为UNION ALL操作只是简单的将两个结果合并后就返回。
20、SQL语句中IN包含的字段不宜过多
MySQL的IN中的常量全部存储在一个数组中这个数组是排序的。如果值过多产生的消耗也是比较大的。如果是连续的数字可以使用between代替或者使用连接查询替换。
21、批量插入性能提升
1多条提交
INSERT INTO user (id,username) VALUES(1,哪吒编程);INSERT INTO user (id,username) VALUES(2,妲己);2批量提交
INSERT INTO user (id,username) VALUES(1,哪吒编程),(2,妲己);默认新增SQL有事务控制导致每条都需要事务开启和事务提交而批量处理是一次事务开启和提交效率提升明显达到一定量级效果显著平时看不出来。
22、表连接不宜太多索引不宜太多一般5个以内
1表连接不宜太多一般5个以内
关联的表个数越多编译的时间和开销也就越大每次关联内存中都生成一个临时表应该把连接表拆开成较小的几个执行可读性更高如果一定需要连接很多表才能得到数据那么意味着这是个糟糕的设计了阿里规范中建议多表联查三张表以下
2索引不宜太多一般5个以内
索引并不是越多越好虽其提高了查询的效率但却会降低插入和更新的效率索引可以理解为一个就是一张表其可以存储数据其数据就要占空间索引表的数据是排序的排序也是要花时间的insert或update时有可能会重建索引如果数据量巨大重建将进行记录的重新排序所以建索引需要慎重考虑视具体情况来定一个表的索引数最好不要超过5个若太多需要考虑一些索引是否有存在的必要
23、禁止给表中的每一列都建立单独的索引
真有这么干的我也是醉了。
24、如何选择索引列的顺序
建立索引的目的是希望通过索引进行数据查找减少随机IO增加查询性能 索引能过滤出越少的数据则从磁盘中读入的数据也就越少。
区分度最高的放在联合索引的最左侧区分度列中不同值的数量/列的总行数。
尽量把字段长度小的列放在联合索引的最左侧因为字段长度越小一页能存储的数据量越大IO性能也就越好。
使用最频繁的列放到联合索引的左侧这样可以比较少的建立一些索引。
25、对于频繁的查询优先考虑使用覆盖索引
覆盖索引就是包含了所有查询字段(where,select,ordery by,group by包含的字段)的索引。
覆盖索引的好处
1避免Innodb表进行索引的二次查询
Innodb是以聚集索引的顺序来存储的对于Innodb来说二级索引在叶子节点中所保存的是行的主键信息如果是用二级索引查询数据的话在查找到相应的键值后还要通过主键进行二次查询才能获取我们真实所需要的数据。
而在覆盖索引中二级索引的键值中可以获取所有的数据避免了对主键的二次查询 减少了IO操作提升了查询效率。
2可以把随机IO变成顺序IO加快查询效率
由于覆盖索引是按键值的顺序存储的对于IO密集型的范围查找来说对比随机从磁盘读取每一行的数据IO要少的多因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。
26、建议使用预编译语句进行数据库操作
预编译语句可以重复使用这些计划减少SQL编译所需要的时间还可以解决动态SQL所带来的SQL注入的问题。
只传参数比传递SQL语句更高效。
相同语句可以一次解析多次使用提高处理效率。
27、避免产生大事务操作
大批量修改数据一定是在一个事务中进行的这就会造成表中大批量数据进行锁定从而导致大量的阻塞阻塞会对MySQL的性能产生非常大的影响。
特别是长时间的阻塞会占满所有数据库的可用连接这会使生产环境中的其他应用无法连接到数据库因此一定要注意大批量写操作要进行分批。
28、避免在索引列上使用内置函数
使用索引列上内置函数索引失效。
29、组合索引
排序时应按照组合索引中各列的顺序进行排序即使索引中只有一个列是要排序的否则排序性能会比较差。
create index IDX_USERNAME_TEL on user(deptid,position,createtime);
select username,tel from user where deptid 1 and position java开发 order by deptid,position,createtime desc; 实际上只是查询出符合 deptid 1 and position java开发条件的记录并按createtime降序排序但写成order by createtime desc性能较差。
30、复合索引最左特性
1创建复合索引
ALTER TABLE employee ADD INDEX idx_name_salary (name,salary)2满足复合索引的最左特性哪怕只是部分复合索引生效
SELECT * FROM employee WHERE NAME哪吒编程3没有出现左边的字段则不满足最左特性索引失效
SELECT * FROM employee WHERE salary50004复合索引全使用按左侧顺序出现 name,salary索引生效
SELECT * FROM employee WHERE NAME哪吒编程 AND salary50005虽然违背了最左特性但MySQL执行SQL时会进行优化底层进行颠倒优化
SELECT * FROM employee WHERE salary5000 AND NAME哪吒编程6理由
复合索引也称为联合索引当我们创建一个联合索引的时候如(k1,k2,k3)相当于创建了k1、(k1,k2)和(k1,k2,k3)三个索引这就是最左匹配原则。
联合索引不满足最左原则索引一般会失效。
31、必要时可以使用force index来强制查询走某个索引
有的时候MySQL优化器采取它认为合适的索引来检索SQL语句但是可能它所采用的索引并不是我们想要的。这时就可以采用forceindex来强制优化器使用我们制定的索引。
32、优化like语句
模糊查询程序员最喜欢的就是使用like但是like很可能让你的索引失效。
首先尽量避免模糊查询如果必须使用不采用全模糊查询也应尽量采用右模糊查询 即like ‘…%’是会使用索引的左模糊like ‘%...’无法直接使用索引但可以利用reverse function index的形式变化成 like ‘…%’全模糊查询是无法优化的一定要使用的话建议使用搜索引擎。
33、统一SQL语句的写法
对于以下两句SQL语句 程序员认为是相同的数据库查询优化器认为是不同的。
select * from user;
select * From USER;这都是很常见的写法也很少有人会注意就是表名大小写不一样而已。然而查询解析器认为这是两个不同的SQL语句要解析两次生成两个不同的执行计划作为一名严谨的Java开发工程师应该保证两个一样的SQL语句不管在任何地方都是一样的。
34、不要把SQL语句写得太复杂
经常听到有人吹牛逼我写了一个800行的SQL语句逻辑感超强我们还开会进行了SQL讲解大家都投来了崇拜的目光。。。
一般来说嵌套子查询、或者是3张表关联查询还是比较常见的但是如果超过3层嵌套的话查询优化器很容易给出错误的执行计划影响SQL效率。SQL执行计划是可以被重用的SQL越简单被重用的概率越大生成执行计划也是很耗时的。
35、关于临时表
避免频繁创建和删除临时表以减少系统表资源的消耗在新建临时表时如果一次性插入数据量很大那么可以使用 select into 代替 create table避免造成大量 log如果数据量不大为了缓和系统表的资源应先create table然后insert如果使用到了临时表在存储过程的最后务必将所有的临时表显式删除。先 truncate table 然后 drop table 这样可以避免系统表的较长时间锁定。
36、将大的DELETEUPDATE、INSERT 查询变成多个小查询
能写一个几十行、几百行的SQL语句是不是显得逼格很高然而为了达到更好的性能以及更好的数据控制你可以将他们变成多个小查询。
37、使用explain分析你SQL执行计划
1type
system表仅有一行基本用不到const表最多一行数据配合主键查询时触发较多eq_ref对于每个来自于前面的表的行组合从该表中读取一行。这可能是最好的联接类型除了const类型ref对于每个来自于前面的表的行组合所有有匹配索引值的行将从这张表中读取range只检索给定范围的行使用一个索引来选择行。当使用、、、、、、IS NULL、、BETWEEN或者IN操作符用常量比较关键字列时可以使用rangeindex该联接类型与ALL相同除了只有索引树被扫描。这通常比ALL快因为索引文件通常比数据文件小all全表扫描性能排名system const eq_ref ref range index all。实际sql优化中最后达到ref或range级别。
2Extra常用关键字
Using index只从索引树中获取信息而不需要回表查询Using whereWHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行如果Extra值不为Using where并且表联接类型为ALL或index查询可能会有一些错误。需要回表查询。Using temporarymysql常建一个临时表来容纳结果典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时
38、读写分离与分库分表
当数据量达到一定的数量之后限制数据库存储性能的就不再是数据库层面的优化就能够解决的这个时候往往采用的是读写分离与分库分表同时也会结合缓存一起使用而这个时候数据库层面的优化只是基础。
读写分离适用于较小一些的数据量分表适用于中等数据量而分库与分表一般是结合着用这就适用于大数据量的存储了这也是现在大型互联网公司解决数据存储的方法之一。
39、使用合理的分页方式以提高分页的效率
select id,name from user limit 100000, 20使用上述SQL语句做分页的时候随着表数据量的增加直接使用limit语句会越来越慢。
此时可以通过取前一页的最大ID以此为起点再进行limit操作效率提升显著。
select id,name from user where id 100000 limit 2040、尽量控制单表数据量的大小建议控制在500万以内。
500万并不是MySQL数据库的限制过大会造成修改表结构备份恢复都会有很大的问题。
可以用历史数据归档应用于日志数据分库分表应用于业务数据等手段来控制数据量大小。
41、谨慎使用Mysql分区表
分区表在物理上表现为多个文件在逻辑上表现为一个表谨慎选择分区键跨分区查询效率可能更低建议采用物理分表的方式管理大数据。
42、尽量做到冷热数据分离减小表的宽度
Mysql限制每个表最多存储4096列并且每一行数据的大小不能超过65535字节。
减少磁盘IO,保证热数据的内存缓存命中率表越宽把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO
更有效的利用缓存避免读入无用的冷数据
经常一起使用的列放到一个表中避免更多的关联操作。
43、禁止在表中建立预留字段
预留字段的命名很难做到见名识义预留字段无法确认存储的数据类型所以无法选择合适的类型对预留字段类型的修改会对表进行锁定
44、禁止在数据库中存储图片文件等大的二进制数据
通常文件很大会短时间内造成数据量快速增长数据库进行数据库读取时通常会进行大量的随机IO操作文件很大时IO操作很耗时。
通常存储于文件服务器数据库只存储文件地址信息。
45、建议把BLOB或是TEXT列分离到单独的扩展表中
Mysql内存临时表不支持TEXT、BLOB这样的大数据类型如果查询中包含这样的数据在排序等操作时就不能使用内存临时表必须使用磁盘临时表进行。而且对于这种数据Mysql还是要进行二次查询会使sql性能变得很差但是不是说一定不能使用这样的数据类型。
如果一定要使用建议把BLOB或是TEXT列分离到单独的扩展表中查询时一定不要使用select * 而只需要取出必要的列不需要TEXT列的数据时不要对该列进行查询。
46、TEXT或BLOB类型只能使用前缀索引
因为MySQL对索引字段长度是有限制的所以TEXT类型只能使用前缀索引并且TEXT列上是不能有默认值的。
47、一些其它优化方式
1当只需要一条数据的时候使用limit 1
limit 1可以避免全表扫描找到对应结果就不会再继续扫描了。
2如果排序字段没有用到索引就尽量少排序
3所有表和字段都需要添加注释
使用comment从句添加表和列的备注从一开始就进行数据字典的维护。
4SQL书写格式关键字大小保持一致使用缩进。
5修改或删除重要数据前要先备份。
6很多时候用 exists 代替 in 是一个好的选择
7where后面的字段留意其数据类型的隐式转换。
8尽量把所有列定义为NOT NULL
NOT NULL列更节省空间NULL列需要一个额外字节作为判断是否为 NULL的标志位。 NULL列需要注意空指针问题NULL列在计算和比较的时候需要注意空指针问题。
9伪删除设计
10索引不适合建在有大量重复数据的字段上比如性别排序字段应创建索引
11尽量避免使用游标
因为游标的效率较差如果游标操作的数据超过1万行那么就应该考虑改写。 Java学习路线总结搬砖工逆袭Java架构师
10万字208道Java经典面试题总结(附答案)
Java基础教程系列
Java基础教程系列进阶篇
