转载:https://blog.csdn.net/HXNLYW/article/details/82979088
MyISAM 和 InnoBD区别:
| MyISAM | InnoDB |
主键 | 允许没有任何索引和主键的表存在, myisam的索引都是保存行的地址。 | 如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见) innodb的数据是主索引的一部分,其他索引保存的是主索引的值。 |
事务处理上方面: | MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持、不支持外键 | InnoDB提供事务支持事务,外部键(foreign key)等高级数据库功能 |
DML操作 | 如果执行大量的SELECT,MyISAM是更好的选择 | 1.如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表 2.DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。 |
自动增长 | myisam引擎的自动增长列必须是索引,如果是组合索引,自动增长可以不是第一列,他可以根据前面几列进行排序后递增。 | innodb引擎的自动增长必须是索引,如果是组合索引也必须是组合索引的第一列。 |
count()函数 | myisam保存有表的总行数,如果select count(*) from table;会直接取出出该值 | innodb没有保存表的总行数,如果使用select count(*) from table;就会遍历整个表,消耗相当大,但是在加了wehre 条件后,myisam和innodb处理的方式都一样。 |
锁 | 表锁 | 提供行锁,另外,InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表, 例如update table set num=1 where name like "%aaa%" |
SQL性能优化:
① SQL优化
- 避免 SELECT *,只查询需要的字段。
- 小表驱动大表,即小的数据集驱动大的数据集:详情见 https://blog.csdn.net/HXNLYW/article/details/86523201
当B表的数据集比A表小时,用in优化 exist两表执行顺序是先查B表再查A表查询语句:SELECT * FROM A WHERE id in (SELECT id FROM B) ;
当A表的数据集比B表小时,用exist优化in ,两表执行顺序是先查A表,再查B表,查询语句:SELECT * FROM A WHERE EXISTS (SELECT id FROM B WHERE A.id = B.ID) ;- 尽量使用连接代替子查询,因为使用 join 时,MySQL 不会在内存中创建临时表。
② 优化索引的使用
- 尽量使用主键查询,而非其他索引,因为主键查询不会触发回表查询。
- 不做列运算,把计算都放入各个业务系统实现
- 查询语句尽可能简单,大语句拆小语句,减少锁时间
- or 查询改写成 union 查询
- 不用函数和触发器
- 避免 %xx 查询,可以使用:select * from t where reverse(f) like reverse('%abc');
- 少用 join 查询
- 使用同类型比较,比如 '123' 和 '123'、123 和 123
- 尽量避免在 where 子句中使用 != 或者 <> 操作符,查询引用会放弃索引而进行全表扫描
- 列表数据使用分页查询,每页数据量不要太大
- 避免在索引列上使用 is null 和 is not null
③ 表结构设计优化
- 使用可以存下数据最小的数据类型。
- 尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int。
- 尽可能使用 not null 定义字段,因为 null 占用 4 字节空间。数字可以默认 0 ,字符串默认 “”
- 尽量少用 text 类型,非用不可时最好独立出一张表。
- 尽量使用 timestamp,而非 datetime。
- 单表不要有太多字段,建议在 20 个字段以内。
Mysql常用数据类型存储大小及范围:https://blog.csdn.net/HXNLYW/article/details/100104768
3.如果以上优化还是有问题,可以使用show profiles 分析sql 性能
show profiles
show profile for query [queryId]