MySQL数据库规范

1、mysql数据库规范目录1. sql语句编写2. explain工具的使用-重点1. sql语句编写sql编写1. 执行大的delete, update> insert操作要慎重，特别是对业务繁忙的系统，要尽量避免 对线上业务产生影响。解决办法是：大操作切割为小操作，使用limit子句限制每次操作的记录数，也可以利 用一些日期字段基于更小粒度的时间范围进行操作。2. 避免使用select *语句，select语句之用于获取需要的字段。3. 使用预编译语句，可以提高性能并且防范sql注入 攻击。4. 一般情况下update, delete语句中不要使用limito5. where条件语句中

2、必须使用合适的类型，避免mysql进行隐式转换。6. insert into必须显式指明字段名称，不要使用insert into table () o7避免在sql语句中进行数学运算或函数运算，避免将业务逻辑和数据存储耦合在一起。8. insert 语句如果使用批量提交，如 insert into table values(),().那么 values 的 个数不应过多。一次性提交过多记录，会导致i/o紧张，出现慢查询。9. 避免使用存储过程、触发器、函数等，这些特性会将业务逻辑与数据库耦合在一起，并 且mysql的存储过程，触发器，函数中可能存在bug。10. 尽量避免使用子查询，连接。尽量

3、将子查询转化为连接查询，mysql查询优化器会优化 连接查询，但连接的表要尽可能的少，如果很多，可以考虑反范式设计。即对设计阶段做 一些改造。11. 使用合理的sql语句以减少与数据库的交互次数。12. 建议使用合理的分页技术以提高操作效率。2. explain工具的使用explain工具的作用1. 使用explain工具可以确认执行计划是否良好，查询是否走了合理的索引。2.不同版本 mysql优化器各有不同，一些优化规则随着版本的发展可能有变化，査询的执行计划随着数据的变化也可能发生变化，这类情况就需要使用explain来验证 自己的判断。explain工具实操执行如下脚本，观察控制台输出e

4、xplain select name from test where id = 32;注意数据表使用如下脚本：create table 'test' ('id' int(ll) unsigned not null auto_increment,'name' varchar(11) default null,primary key cid') engine二innodb aut0_increment=41 default charset二utf8; table name = test> columnl = id、 column2 =

5、name.执行结果如下所示：mysql> explain selec七 name from 七est where id =f丄1 w/w4 ”41lu qselect_type:丄simple七 abl：_test十宀5 上 i . k 11 1 i 1partitions:mnul±type: possible.keys: key: key_len: ref: rows: filtered;extra:constprimaryprimary4const1100,00:null1 row in set,1 warning (0.00 sec)下面详细阐述explain输出的各项

6、内容：id:包含一组数字，表示查询中执行select子句或操作表的顺序。 如果id相同，则执行顺序由上到下。select_type:表示查询中每个select子句的类型（是简单还是复杂）输出结果类似如下:1. simple查询中不包含子查询或者union2. primary査询中若包含任何复杂子査询，最外层査询被标记为primary3. subquery在select或where列表中包含了子查询，则该查询被标记为subquery4. derived在from列表中包含的子查询被标记为derived(衍生)5. union若第二个select出现在union之后，则被标记为derived。6.

7、 union result从union表中获取结果的select将被标记为union resulto select_type只需要了解分类即可，这个信息并不是最有价值的。type:最有价值信息之一type表示mysql在表中找到所需行的方式，又称为“访问类型”，常见的 类型如下所示：all、index、range、ref、eq_ref> const, system, null以上类型，由左至右，由最差到最好。all: full table scan, mysql将遍历全表以找到匹配的行。index:full index scan, index与all区别为index类型只遍历索引树。 假

8、设表中有主键字段id,则select id from table_name； type即为full index scanorange:索引扫描范围，对索引的扫描开始于某一点，返回匹配的域或行，常 见于between、<、等的查询。ref:非唯一性索引扫描，将返回匹配某个单独值得所有行。常见于使用非唯 一索引或唯一索引的非唯一前缀的查找。eq_ref:唯一性索引扫描，对于每个索引键表中只有一条记录与之匹配。常 见于主键或唯一索引扫描。const、system:当mysql对查询的某部分进行优化，并转化为一个常量时, 可使用这些类型进行访问。如果主键置于where列表中，mysql就能将该查

9、 询转换为一个常量，system是const的一个特例，当查询的表只有一行的情况下，即可使用systemonull:mysql在优化过程中分解语句，执行时甚至不用访问表或索引，举例 如下：explain select from (select from tl where id = 1)dl; possible_keyspossible_keys将指出mysql能使用哪个索引在表中找到行,查询涉及的字 段上若存在索引，则该索引将被列出，但不一定会被查询使用。key:最有价值信息之二key将显示mysql在查询中实际使用到的索引，若没有使用索引，则显示为 nullo查询中若使用到了覆盖索引，则该索

10、引仅仅出现在key列表中， possible_keys中并不显示。key_lenkey_len表示索引中使用的字节数，可通过该列计算查询中使用的索引的长 度。refref表示上述表的连接匹配条件，即哪些列或常亮被用于查找索引列上的 值。rows:最有价值信息之三rows表示mysql根据表统计信息及索引选用的情况，估算查找所需记录需 要读取的行数。使用到索引一般情况下会使得rows的值降低。extra :最有价值信息之四extra包含不适合在其他列中显示但十分重要的额外信息。可能包如下4种信息。1. using index该值表示相应的select操作中使用到了覆盖索引，包含满足查询需要的数

11、据的索引称为覆盖索引。2. using where如果查询未能使用索引，则using where的作用只是提醒我们mysql将用 where子句来过滤结果集。3. using temporary表示mysql需要使用临时表来存储结果集，常见于order by与group by, 事实上group by会进行隐式的order byo如果我们在group by时利用索引分组（其实包含排序的过程）排序，则可以 提高性能，因为不会此时査询输出里没有了 using temporary, using filesorto4. using filesortusing filesort即文件排序，mysql中将

12、无法使用索引完成的排序操作，称为文件排序。 上文篇幅有点长，但都是必须了解的概念。最有价值信息是我们判断sql语句执行是否高 效的基准，了解四个最有价值信息是最重要的。继续演示explain的使用，使用上文的评判标准来看下语句的执行效率：1.主键查询mysql> explain select name from test where id = 32-> ；+i idiselect-type itable ipar七i七ions itype ipossiblekeys i j- -+ 八. 1r-|-simple g lw±rl const i primaryh+h+1 r

13、ow in set, 1 warning (0.00 sec)i上述为主键查询的explain信息type = const效率很高key = primary实际使用的索引为主键 rows = 1查找的记录数为1 extra = null ,没有任何额外信息 总体來说，性能是极高。2. 主键范围查询mysql> explain select name from 七est where id between 1 and 32;+-+一 一i idiselect.type itableipartitions itypeipossible.keysi key+i 1isimpleitestinul

14、l lrangeiprimaryiprii+-+1 row in set, 1 warning (0.01 sec)上述为主键查询的explain信息type = range范围查询，效率不是最低key = primary实际使用的索引为主键rows = 7査找的记录数为7extra = using where ,最终使用where做结果集过滤，未使用到覆盖索引。 总体来说，性能是很高。3. 未带索引查询mysql> explain selec七 name from 七est where name="llll0”；+卜+hiidiselect.type itableiparti

15、tionsi typeipossible.keysi k(+卜】-+i1isimpleitestinulliallinullinl+h卜h1 irow in set, 1 warning (000 sec)如上图所示，name并未做索引。type =all full table scan 全表查询key =null未使用索引rows = 7 10数据库中所有记录extra = using where ,最终使用where做结果集过滤，未使用到覆盖索引。 总体来说，性能极差。（这也是我司内部deviceld接口出问题的终极原因）。4. 未带索引的分组查询mysql> mysql> e

16、xplain select from test where name like ”123%” group by name; ' “ 一 + '一 +4+-+hi idiselect-type ttable ipartitions ityp tpossibl_keys ikey jkey.len iref# p 斗世亠士士ai 1 i simplei test ii nulli all i null null i null i nulu i+*4+*+1irrow in sety-jr vwirning-(0 >00 sec)t如上图所示，name并未做索引。type =a

17、ll full table scan 全表查询key =null未使用索引rows = 7 10数据库中所有记录extra = using where ,最终使用where做结果集过滤，未使用到覆盖索引。 并使用到了 temporary, filesort临时表与文件查询。总体来说，性能极差。5. 带索引的分组查询我们现在为4与3中name创建索引，再来看看分析结果创建索引脚本如下 alter table test add index idx_name(name);mysql> alter table test add index idx_name(name); query ok, 0

18、rows effected, 1 warning (0.02 sec) records: 0 duplicates: 0 warnings: 1mysql> show dex from test;_+i table nor)_uniqueikiseq_in_indextcolumn_namel-collationtxardin+4+itesti0iprimaryi1iidiaiiitesti1inamei1inameiaii七es七i1iidx_namei1inameiai+3 rows, in set (0.00 sec)再运行3和4中的查询语句 结果如下图所示：mysql> explain select name from test where n;+i idiselect-type itable ipartitions i type ipossible.keys i keytype =ref非唯一索引扫描，效率不是最低key =name实际使用的索引name索弓|（注意：idx_name与name都是在name