Mysql优化

时间:2021-11-06 06:21:57

InnoDB MyISAM
事务处理 支持 不支持
全文搜索 不支持 支持
SELECT ,UPDATE,INSERT,DELETE 大量INSERT或UPDATE | DELETE 表一行一行的删除 大量SELECTDELETE | DELETE表先DROP再重建
AUTO_INCREMENT 自增计数器仅存储在主内存中
表锁,行级锁 表锁
外键 支持 不支持
行数 扫描表来计算 保存行数
存储 把数据和索引存放在表空间里面 表被存放在三个文件
跨平台 跨平台可直接拷贝使用 跨平台很难直接拷贝
压缩
哈希索引 支持 不支持
事务ACID 支持 不支持
储存容量 64T 没上限



预编译

sql语句执行过程分为 词法语义解析、语句优化、制定执行计划

一条sql语句可能会反复执行,或者每次执行的时候只有个别的值不同,且每次都需要经过上面的词法语义解析、语句优化、制定执行计划等,效率就底下

预编译语句就是将这类语句中的值用占位符替代,可以视为将sql语句模板化或者说参数化,一次编译、多次运行,省去了解析优化等过程

缓存预编译
预编译语句被DB的编译器编译后的执行代码被缓存下来,那么下次调用时只要是相同的预编译语句就不需要编译,只要将参数直接传入编译过的语句执行代码中就会得到执行,并不是所以预编译语句都一定会被缓存,数据库本身会用一种策略

作用

优化 sql 的执行,预编译之后的 sql 多数情况下可以直接执行,DBMS 不需要再次编译,越复杂的sql,编译的复杂度将越大,预编译阶段可以合并多次操作为一个操作

防止SQL注入
使用预编译,而其后注入的参数将不会再进行SQL编译,也就是说其后注入进来的参数系统将不会认为它会是一条SQL语句,而默认其是一个参数,参数中的or或者and 等就不是SQL语法保留字了



选择最有效率的表名顺序

数据库从右到左处理FROM子句中的表名

多表无关系:数据,列名最少的表放最后

多表有关系:引用最多的表放最后


WHERE子句中的连接顺序

数据库从右到左解析WHERE子句

筛选掉最多数量记录的条件必须写在右边,表之间的连接必须写在左边

SELECT * FROM <表1>,<表2> WHERE (table1.a = table2.b) AND (table.a > 1000)


**SELECT子句中避免使用*号**

数据库会查询数据字典,将*依次转换成所有的列名,非常耗时,建议手写


用TRUNCATE替代DELETE

DELETE一条条删

TRUNCATE删表重建


使用表、列的别名

名字太长要解析,耗资源


使用内部函数提高SQL效率

优化过了


避免在索引列上使用NOT、<>、!=

范围搜索会转而执行全表扫描,最左匹配


用 >= 替代 >

低效:
SELECT * FROM <表名> WHERE id > 3   
定位到id=3,扫描第一个id大于3的数据
高效:
SELECT * FROM <表名> WHERE id >= 4  
直接跳到第一个id等于4的数据


善用索引

查询多,条件字段加索引,增删多就别加了


使用like关键字通配符%置于要查询字段的前面那么数据库将走全表扫描而不走索引


用IN替代OR

IN列表中的数值进行排序,对于查询每次使用二分查找去匹配IN列表的数值

OR每次都要遍历


避免在索引列上使用计算

SELECT * FROM  <表名> WHERE hh*10 > 1000(低效,优化器不再使用索引)

Select * from T where hh > 1000/10(更高效,优化器将使用索引)