1、索引优化
1. 表记录很少不需创建索引 (索引是要有存储的开销).
2. 一个表的索引个数不能过多。
(1) 空间:浪费空间。每个索引都是一个索引树,占据大量的磁盘空间。
(2) 时间:
更新(插入/Delete/Update)变慢。需要更新所有的索引树。太多的索引也会增加优化器的选择时间。
所以索引虽然能够提高查询效率,索引并不是越多越好,应该只为需要的列创建索引。
3. 频繁更新的字段不建议作为索引。
频繁更新的字段引发频繁的页分裂和页合并,性能消耗比较高。
4. 区分度低的字段,不建议建索引。(仅供参考)
比如性别,男,女;比如状态。区分度太低时,会导致扫描行数过多,再加上回表查询的消耗。 如果使用索引,比全表扫描的性能还要差。这些字段一般会用在组合索引中。
姓名,手机号就非常适合建索引。
5. 在InnoDB存储引擎中,主键索引建议使用自增的长整型,避免使用很长的字段。
主键索引树一个页节点是16K,主键字段越长,一个页可存储的数据量就会越少,比较臃肿,查 询时尤其是区间查询时磁盘IO次数会增多。辅助索引树上叶子节点存储的数据是主键值,主键值越 长,一个页可存储的数据量就会越少,查询时磁盘IO次数会增多,查询效率会降低。
6. 不建议用无序的值作为索引。
例如身份证、UUID
更新数据时会发生频繁的页分裂,页内数据不紧凑,浪费磁盘空间。
7. 尽量创建组合索引,而不是单列索引。
优点:
(1)1个组合索引等同于多个索引效果,节省空间。
(2) 可以使用覆盖索引
创建原则:组合索引应该把把频繁的列,区分度高的值放在前面。频繁使用代表索引的利用率高, 区分度高代表筛选粒度大,可以尽量缩小筛选范围。
2、字段数据类型
尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT,如果非负则加上UNSIGNED VARCHAR的长度只分配真正需要的空间
使用枚举或整数代替字符串类型
单表不要有太多字段,建议在20以内
避免使用NULL字段,很难查询优化且占用额外索引空间用整型来存IP
3、LIMIT优化
如果预计SELECT语句的查询结果是一条,最好使用 LIMIT 1,可以停止全表扫描。
SELECT * FROM user WHERE username=''; -- username没有建立唯一索引2
SELECT * FROM user WHERE username='' LIMIT 1;
处理分页会使用到LIMIT ,当翻页到非常靠后的页面的时候,偏移量会非常大,这时LIMIT的效率会非常差。 LIMIT OFFSET , SIZE;
LIMIT的优化问题,其实是OFFSET 的问题,它会导致MySql扫描大量不需要的行然后再抛弃掉。
解决方案:单表分页时,使用自增主键排序之后,先使用where条件 id > offset值,limit后面只写rows
select * from (select * from tuser2 where id > 1000000 ORDER BY id) t limit 0, 20
4、查询优化
小表驱动大表,两个表做关联查询时建议使用join ,因为使用left join 的话,第一张表是必须全扫描的。
避免全表扫描,mysql在使用不等于(!=或者<>)的时候无法使用索引导致全表扫描。在查询的时 候,如果对索引使用不等于的操作将会导致索引失效,进行全表扫描
避免mysql放弃索引查询,如果mysql估计使用全表扫描要比使用索引快,则不使用索引。(最典 型的场景就是数据量少的时候)
JOIN两张表的关联字段最好都建立索引,而且最好字段类型是一样的。
SELECT * FROM orders o LEFT JOIN user u on o.user_id = u.id 2
orders表中的user_id和user表中的id,类型要一致
WHERE条件中尽量不要使用not in语句(建议使用not exists)
合理利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况。
5、阿里巴巴开发规约
不得使用外键与级联,一切外键概念必须在应用层解决
禁止使用存储过程,存储过程难以调试和扩展,更没有移植性
数据订正时,要先 select,避免出现误删除,确认无误才能执行更新语句只要涉及多个表,都需要在列名前加表的别名(或表名)进行限定
SQL 语句中表的别名前加 as,并且以 t1、t2、t3、...的顺序依次命名
in 后边的集合元素数量,控制在 1000 个之内使用 ISNULL()来判断是否为 NULL 值
NULL< >NULL 的返回结果是 NULL,而不是 false NULL=NULL 的返回结果是 NULL,而不是 true NULL< >1 的返回结果是 NULL,而不是 true
不要使用 count(列名)或 count(常量)来替代 count(*)
当某一列的值全是 NULL 时,count(col)的返回结果为 0,但 sum(col)的返回结果为NULL count(distinct col) 计算该列除 NULL 之外的不重复行数
超过三个表禁止 join
如果您觉得文章好看,欢迎点赞收藏加关注,一连三击呀,您的肯定是我持续输出的动力,感谢!!☺☻