参考文章
1,建立索引,
建立索引,但索引不能建立的太多。insert,update 数据时会修改索引,索引太多,也会影响性能。
-,在where 和 order by 上经常使用的列建立索引
-,建立索引的列不经常变动
-,建立索引的列的值的差异要大,不能是枚举值
-,多列经常使用的情况下,把多列建成复合索引会更好。使用复合索引时要列的顺序,要和索引的顺序一致
2,索引失效和sql优化:
like : like 列的左边使用%时,会使索引失效,使用时尽量避免。
select * from contents where content_title like '%可%'
全盘搜索
select * from contents where content_title like '可%';
走索引
in,not in :in 的集合较小是依旧会使用索引,但过大时会全盘搜索。优化:用between and ,exist,join 替换
or : 如果or的条件中有个条件不带索引的话,那这条sql就不会使用到索引了,如果全部是索引,则会走索引。
优化:全部索引的拆成一个sql,非索引的成为另一个sql最后用 union all 或 union 联结
!=: != 和 <> 使索引失效。优化:用 >value or <value 替换
null,not null:null和not null 使索引失效。数据库中不要有null,要尽量设置默认值,如 0,空字符等
where 条件中运算符左边有函数,索引也会失效。优化:把运算最好放在运算符右边。
where中运算符左右类型不对等时,默认会通过转化函数转化类型,最后导致索引不可使用。建议尽量避免类型转换。
优化
1,数据量过大时单纯的使用limit 分页不合适。可以结合 >id 再使用limit 。
2,表结果不能过大,可以分表。
3,像日志表,每日报表等数据量容易过大的表可以按时期分表,如log_2019,log-2020
4,select 时不要使用* ,用明确字段,不要放回用不到的字段。
5,查询是明确一条记录时,使用limit 1, top ,终止 可能的全盘搜索。
6,尽量用 union all 替换 union。因为union 是在union all 后在进行去重操作,数据多的话会耗时好性能。
7,Inner join 和 left join、right join、子查询:
- inner join 是内连接,left join ,right join 是 外连接。内连接的性能比外连接更好
- 外连接比子查询性能更好
- 使用JOIN时候,应该用小的结果驱动打的结果
8,exist 代替 in
SELECT * from A WHERE idin (SELECT id from B)
SELECT * from A WHERE id EXISTS(SELECT 1 from A.id= B.id)
in()适合B表比A表数据小的情况
exists()适合B表比A表数据大的情况
in 是在内存中遍历比较,exist 需要查询数据库比较
9,对于大数据字段,独立表进行存储,以便影响性能(例如:简介字段)