去哪里做网站比较好,cms网站开发模式,废旧网站那个做的最好,上海中企动力做网站多少钱一.在查询SQL尽量不要使用select *#xff0c;查询具体字段
1、反例
SELECT * FROM user
2、正例
SELECT id,username,tel FROM user
3、理由 节省资源、减少网络开销。 可能用到覆盖索引#xff0c;减少回表#xff0c;提高查询效率。
二、避免在where子句中使用 o…一.在查询SQL尽量不要使用select *查询具体字段
1、反例
SELECT * FROM user
2、正例
SELECT id,username,tel FROM user
3、理由 节省资源、减少网络开销。 可能用到覆盖索引减少回表提高查询效率。
二、避免在where子句中使用 or 来连接条件
1、反例
SELECT * FROM user WHERE id1 OR salary5000
2、正例
1使用union all
SELECT * FROM user WHERE id1
UNION ALL
SELECT * FROM user WHERE salary5000
2分开两条sql写
SELECT * FROM user WHERE id1SELECT * FROM user WHERE salary5000
3、理由 使用or可能会使索引失效从而全表扫描 对于or没有索引的salary这种情况假设它走了id的索引但是走到salary查询条件时它还得全表扫描 也就是说整个过程需要三步全表扫描索引扫描合并。如果它一开始就走全表扫描直接一遍扫描就搞定 虽然mysql是有优化器的出于效率与成本考虑遇到or条件索引还是可能失效的
三、尽量使用数值替代字符串类型
1、正例 主键idprimary key优先使用数值类型inttinyint 性别sex0代表女1代表男数据库没有布尔类型mysql推荐使用tinyint
2、理由 因为引擎在处理查询和连接时会逐个比较字符串中每一个字符 而对于数字型而言只需要比较一次就够了 字符会降低查询和连接的性能并会增加存储开销
四、使用varchar代替char
1、反例
address char(100) DEFAULT NULL COMMENT 地址
2、正例
address varchar(100) DEFAULT NULL COMMENT 地址3、理由 varchar变长字段按数据内容实际长度存储存储空间小可以节省存储空间 char按声明大小存储不足补空格 其次对于查询来说在一个相对较小的字段内搜索效率更高
五、技术延伸char与varchar2的区别
1、char的长度是固定的而varchar2的长度是可以变化的。
比如存储字符串“101”对于char(10)表示你存储的字符将占10个字节包括7个空字符在数据库中它是以空格占位的而同样的varchar2(10)则只占用3个字节的长度10只是最大值当你存储的字符小于10时按实际长度存储。
2、char的效率比varchar2的效率稍高。
3、何时用char何时用varchar2?
char和varchar2是一对矛盾的统一体两者是互补的关系varchar2比char节省空间在效率上比char会稍微差一点既想获取效率就必须牺牲一点空间这就是我们在数据库设计上常说的“以空间换效率”。
varchar2虽然比char节省空间但是假如一个varchar2列经常被修改而且每次被修改的数据的长度不同这会引起“行迁移”现象而这造成多余的I/O是数据库设计中要尽力避免的这种情况下用char代替varchar2会更好一些。char中还会自动补齐空格因为你insert到一个char字段自动补充了空格的,但是select后空格没有删除因此char类型查询的时候一定要记得使用trim这是写本文章的原因。
如果开发人员细化使用rpad()技巧将绑定变量转换为某种能与char字段相比较的类型当然与截断trim数据库列相比填充绑定变量的做法更好一些因为对列应用函数trim很容易导致无法使用该列上现有的索引可能必须考虑到经过一段时间后列长度的变化。如果字段的大小有变化应用就会受到影响因为它必须修改字段宽度。
正是因为以上原因定宽的存储空间可能导致表和相关索引比平常大出许多还伴随着绑定变量问题所以无论什么场合都要避免使用char类型。
六、where中使用默认值代替null
1、反例
SELECT * FROM user WHERE age IS NOT NULL2、正例
SELECT * FROM user WHERE age03、理由 并不是说使用了is null或者is not null就会不走索引了这个跟mysql版本以及查询成本都有关 如果mysql优化器发现走索引比不走索引成本还要高就会放弃索引这些条件!is nullis not null经常被认为让索引失效 其实是因为一般情况下查询的成本高优化器自动放弃索引的 如果把null值换成默认值很多时候让走索引成为可能同时表达意思也相对清晰一点
七、避免在where子句中使用!或操作符
1、反例
SELECT * FROM user WHERE salary!5000SELECT * FROM user WHERE salary50002、理由 使用!和很可能会让索引失效 应尽量避免在where子句中使用!或操作符否则引擎将放弃使用索引而进行全表扫描 实现业务优先实在没办法就只能使用并不是不能使用
八、inner join 、left join、right join优先使用inner join
三种连接如果结果相同优先使用inner join如果使用left join左边表尽量小。 inner join 内连接只保留两张表中完全匹配的结果集 left join会返回左表所有的行即使在右表中没有匹配的记录 right join会返回右表所有的行即使在左表中没有匹配的记录
为什么 如果inner join是等值连接返回的行数比较少所以性能相对会好一点 使用了左连接左边表数据结果尽量小条件尽量放到左边处理意味着返回的行数可能比较少 这是mysql优化原则就是小表驱动大表小的数据集驱动大的数据集从而让性能更优
九、提高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、理由
可以在执行到该语句前把不需要的记录过滤掉
十、表连接不宜太多索引不宜太多一般5个以内
1、表连接不宜太多一般5个以内 关联的表个数越多编译的时间和开销也就越大 每次关联内存中都生成一个临时表 应该把连接表拆开成较小的几个执行可读性更高 如果一定需要连接很多表才能得到数据那么意味着这是个糟糕的设计了 阿里规范中建议多表联查三张表以下
2、索引不宜太多一般5个以内 索引并不是越多越好虽其提高了查询的效率但却会降低插入和更新的效率 索引可以理解为一个就是一张表其可以存储数据其数据就要占空间 索引表的数据是排序的排序也是要花时间的 insert或update时有可能会重建索引如果数据量巨大重建将进行记录的重新排序所以建索引需要慎重考虑视具体情况来定 一个表的索引数最好不要超过5个若太多需要考虑一些索引是否有存在的必要
十一、避免在索引列上使用内置函数
1、反例
SELECT * FROM user WHERE DATE_ADD(birthday,INTERVAL 7 DAY) NOW();2、正例
SELECT * FROM user WHERE birthday DATE_ADD(NOW(),INTERVAL 7 DAY);3、理由
使用索引列上内置函数索引失效。
十二、创建联合索引
当查询条件有多个且固定时应创建联合索引。对于符合条件查询创建联合索引的效率要大于创建多个单独索引。 注 1.当查询时如果查询字段条件字段索引字段即覆盖索引那么此次查询就只需要在索引中进行查询不需要回表 2.当查询字段索引字段时即使条件字段全部覆盖在索引中索引也成功生效。但因为索引中并未存在某些字段的值也需要根据索引查到主键id再回表去表中取得字段的值。 3.使用联合索引时应满足最左原则如创建id,name,age索引查询条件为name,age或age则索引失效为id,name或id索引生效 十三、多列进行order排序
排序时应按照组合索引中各列的顺序进行排序即使索引中只有一个列是要排序的否则排序性能会比较差。
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性能较差。 注 1.当采用order排序时会把查出的所有数据先放到sort_buffer缓冲区中为了避免多个线程对同一块内存进行操作带来锁竞争的问题每个线程有自己的缓冲区。当全部读入缓冲区后才会对结果进行排序。 2.sort_buffer缓冲区有最大限制 InnoDB 存储引擎中这个值是默认是256K当数据量大于限制时MySQL会采用归并排序的思想把要排序的数据分成若干份每一份数据在内存中排序后会放入一个个磁盘的临时文件中最终对这些已经排序好的临时文件的数据再做一次合并排序即文件排序。 3.如果不想走文件排序可以通过建立联合索引在索引中包含所有需要排序和查询的字段这样在索引中存储的列就已经是排序好的结果不需要回表只查询索引就可以完美的解决这个问题了。 十四、优化like语句
模糊查询程序员最喜欢的就是使用like但是like很可能让你的索引失效。
1、反例
select * from citys where name like %大连 (不使用索引)select * from citys where name like %大连% (不使用索引)2、正例
select * from citys where name like 大连% (使用索引) 。3、理由 首先尽量避免模糊查询如果必须使用不采用全模糊查询也应尽量采用右模糊查询 即like ‘…%’是会使用索引的 左模糊like ‘%...’无法直接使用索引但可以利用reverse function index的形式变化成like ‘…%’ 全模糊查询是无法优化的一定要使用的话建议使用搜索引擎。
十五、使用explain分析你SQL执行计划
1、type system表仅有一行基本用不到 const表最多一行数据配合主键查询时触发较多 eq_ref对于每个来自于前面的表的行组合从该表中读取一行。这可能是最好的联接类型除了const类型 ref对于每个来自于前面的表的行组合所有有匹配索引值的行将从这张表中读取 range只检索给定范围的行使用一个索引来选择行。当使用、、、、、、IS NULL、、BETWEEN或者IN操作符用常量比较关键字列时可以使用range index该联接类型与ALL相同除了只有索引树被扫描。这通常比ALL快因为索引文件通常比数据文件小 all全表扫描 性能排名system const eq_ref ref range index all。 实际sql优化中最后达到ref或range级别。
2、Extra常用关键字 Using index只从索引树中获取信息而不需要回表查询 Using whereWHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行如果Extra值不为Using where并且表联接类型为ALL或index查询可能会有一些错误。需要回表查询。 Using temporarymysql常建一个临时表来容纳结果典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时
六、索引失效的几种情况
1.使用or
SELECT * FROM user WHERE id1 OR salary5000 当id有索引而salary没索引如果走索引的话在id走完索引后salary再走全表扫描这样效率反而更低mysql帮我们优化了这种情况使其直接全表扫描。可以通过union避免。 2.使用!is nullis not null 使用上述查询条件可能会使得mysql觉得使用索引的效率比不使用更低从而放弃索引。可以通过设置默认值为空值避免。 3.在查询条件的索引列上使用函数或进行计算 4.组合索引没有满足最左原则 5.使用like语句 使用like语句时只能在后面拼接模糊查询符号如: like 张% 6.连表查询字符集和字段类型不同会导致需要进行转换可能会导致索引失效 二十、一些其它优化方式
1、设计表的时候所有表和字段都添加相应的注释。
2、SQL书写格式关键字大小保持一致使用缩进。
3、修改或删除重要数据前要先备份。
4、很多时候用 exists 代替 in 是一个好的选择
5、where后面的字段留意其数据类型的隐式转换。
未使用索引
SELECT * FROM user WHERE NAME1101 因为不加单引号时是字符串跟数字的比较它们类型不匹配
2MySQL会做隐式的类型转换把它们转换为数值类型再做比较
6、尽量把所有列定义为NOT NULL
NOT NULL列更节省空间NULL列需要一个额外字节作为判断是否为NULL的标志位。NULL列需要注意空指针问题NULL列在计算和比较的时候需要注意空指针问题。
7、伪删除设计
8、数据库和表的字符集尽量统一使用UTF8
1可以避免乱码问题
2可以避免不同字符集比较转换导致的索引失效问题
9、select count(*) from table
这样不带任何条件的count会引起全表扫描并且没有任何业务意义是一定要杜绝的。
10、避免在where中对字段进行表达式操作
1SQL解析时如果字段相关的是表达式就进行全表扫描
2字段干净无表达式索引生效
11、关于临时表
1避免频繁创建和删除临时表以减少系统表资源的消耗
2在新建临时表时如果一次性插入数据量很大那么可以使用 select into 代替 create table避免造成大量 log
3如果数据量不大为了缓和系统表的资源应先create table然后insert
4如果使用到了临时表在存储过程的最后务必将所有的临时表显式删除。先 truncate table 然后 drop table 这样可以避免系统表的较长时间锁定
12、索引不适合建在有大量重复数据的字段上比如性别排序字段应创建索引
13、去重distinct过滤字段要少 带distinct的语句占用cpu时间高于不带distinct的语句 当查询很多字段时如果使用distinct数据库引擎就会对数据进行比较过滤掉重复数据 然而这个比较、过滤的过程会占用系统资源如cpu时间
