mysql索引和sql规范

转载 2015年07月10日 15:40:46

索引规范


1、索引的数量要控制:

(1) 单张表中索引数量不超过5个

(2) 单个索引中的字段数不超过5个

(3) 对字符串使⽤用前缀索引,前缀索引长度不超过8个字符

(4) 建议优先考虑前缀索引,必要时可添加伪列并建立索引

2、主键准则

(1) 表必须有主键

(2) 不使用更新频繁的列作为主键

(3) 尽量不选择字符串列作为主键

(4) 不使用UUID MD5 HASH这些作为主键(数值太离散了)

(5) 默认使⽤非空的唯一键作为主键

(6) 建议选择自增或发号器

3、重要的SQL必须被索引,比如:

(1) UPDATE、DELETE语句的WHERE条件列

(2) ORDER BY、GROUP BY、DISTINCT的字段

4、多表JOIN的字段注意以下:

(1) 区分度最大的字段放在前面

(2) 核⼼SQL优先考虑覆盖索引

(3) 避免冗余和重复索引

(4) 索引要综合评估数据密度和分布以及考虑查询和更新比例

5、索引禁忌

(1) 不在低基数列上建立索引,例如“性别”

(2) 不在索引列进行数学运算和函数运算

6、尽量不使用外键

(1) 外键用来保护参照完整性,可在业务端实现

(2) 对父表和子表的操作会相互影响,降低可用性

7、索引命名:非唯一索引必须以 idx_字段1_字段2命名,唯一所以必须以uniq_字段1_字段2命名,索引名称必须全部小写

8、新建的唯一索引必须不能和主键重复

9、索引字段的默认值不能为NULL,要改为其他的default或者空。NULL非常影响索引的查询效率

10、反复查看与表相关的SQL,符合最左前缀的特点建立索引。多条字段重复的语句,要修改语句条件字段的顺序,为其建立一条联合索引,减少索引数量

11、能使用唯一索引就要使用唯一索引,提高查询效率

12、研发要经常使用explain,如果发现索引选择性差,必须让他们学会使用hint


SQL规范


(1) sql语句尽可能简单

大的sql想办法拆成小的sql语句(充分利QUERY CACHE和充分利用多核CPU)

(2) 事务要简单,整个事务的时间长度不要太长

(3) 避免使用触发器、函数、存储过程

(4) 降低业务耦合度,为sacle out、sharding留有余地

(5) 避免在数据库中进数学运算(MySQL不擅长数学运算和逻辑判断)

(4) 不要用select *,查询哪几个字段就select 这几个字段

(5) sql中使用到OR的改写为用 IN() (or的效率没有in的效率高)

(6) in里面数字的个数建议控制在1000以内

(7) limit分页注意效率。Limit越大,效率越低。可以改写limit,比如例子改写:

select id from tlimit 10000, 10; => select id from t where id > 10000 limit10;

(9) 使用union all替代union

(10) 避免使大表的JOIN

(11) 使用group by 分组、自动排序

(12) 对数据的更新要打散后批量更新,不要一次更新太多数据

(13) 减少与数据库的交互次数

(13) 注意使用性能分析工具

Sql explain / showprofile / mysqlsla

(14) SQL语句要求所有研发,SQL关键字全部是大写,每个词只允许有一个空格

(15) SQL语句不可以出现隐式转换,比如 select id from 表 where id='1'

(16) IN条件里面的数据数量要少,我记得应该是500个以内,要学会使用exist代替in,exist在一些场景查询会比in快

(17) 能不用NOT IN就不用NOTIN,坑太多了。。会把空和NULL给查出来

(18) 在SQL语句中,禁止使用前缀是%的like

(19) 不使用负向查询,如not in/like

(19) 关于分页查询:程序里建议合理使用分页来提高效率limit,offset较大要配合子查询使用

(20) 禁止在数据库中跑大查询

(21) 使⽤预编译语句,只传参数,比传递SQL语句更高效;一次解析,多次使用;降低SQL注入概率

(22) 止使order by rand()

(23) 单条SQL语句同时更新多个表

相关文章推荐

mysql sql中类型转换引起的索引无法使用的问题

今天开发说有个SQL特别慢,让优化下,我看了下是因为条件字段检索时的类型转换引起的索引不可用。  其中clpp字段和clnk字段都是字符串类型,他们当做数字类型使用。  原SQL: SELECT j...

Mysql 数据库优化--查询缓存及sql语句优化-索引优化,史上最全最全

查询执行基础知识 show processlist来查看当前有何进程 查询缓存 Mysql的cache功能的原理是,如果开启mysql的缓存功能,mysql自动将select语句按一定的规则生成二进...

如何知道mysql中sql语句索引是否生效

explain命令详解 用explain 来执行检验sql 例如 explain select * from a +----+-------------+------...
  • Jacabe
  • Jacabe
  • 2016年02月22日 22:33
  • 437

MySql优化-你的SQL命中索引了吗

在项目开发中SQL是必不可少的,表索也一样.这些SQL的运行性能不知道吗?有多少是命中了索引的?命中哪个索引?索引中有哪个是无效索引?这些无效索引是否会影响系统的性能?带着这些问题我们一起来学习一下....

MySQL数据库优化之SQL及索引

Mysql优化之sql语句等
  • yyywyr
  • yyywyr
  • 2015年08月16日 17:40
  • 487

sql学习笔记(15)-----------MySQL 索引与优化总结

索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点。 考虑如下情况,假设数据库中一个表有10^6条记录,DBMS的页面大小为4K,并存储100条记录。如果没有索引,查询将对整个...

mysql建立不上外键,sql语句一运行完就会生成一条索引,但是外键却建不上

案例:使用navicat生成外键关联,发现添加不了,只生成了一条索引 分析: 建立外键需要注意几点: 1,必须使用innodb表引擎 2,两个表的字符编码必须一致 3,被引用的typeid,即父表中的...

mysql sql优化和索引摘录

1 . 使用连接(JOIN)来代替子查询(Sub-Queries)   SELECT*FROMcustomerinfo WHERE CustomerID NOT in(SELECT CustomerI...

mysql建表、索引以及SQL性能优化

1 前言 参考了部分网上资料整理而成。 2 设计部分 2.1 设计表注意事项 2.1.1 定义字段类型 尽可能精确地定义字段类型,包括类型和长度:如不要以字符类型声明纯数字字段,业务上t...
  • bladedk
  • bladedk
  • 2014年11月11日 15:37
  • 324

mysql索引以及慢SQL优化记录【转慕课】

mysql索引介绍以及慢SQL优化简单原则
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql索引和sql规范
举报原因:
原因补充:

(最多只允许输入30个字)