mysql开发库,MySQL 数据库规范--开发篇

最新推荐文章于 2024-05-31 14:19:25 发布

addis ababa

最新推荐文章于 2024-05-31 14:19:25 发布

阅读量116

点赞数

文章标签： mysql开发库

1.sql语句编写2.explain 工具的使用--重点

1.执行大的delete、update、insert操作要慎重，特别是对业务繁忙的系统，要尽量避免对线上业务产生影响。

解决办法是：大操作切割为小操作，使用limit子句限制每次操作的记录数，也可以利用一些日期字段基于更小粒度的时间范围进行操作。

2.避免使用select * 语句，select语句之用于获取需要的字段。

3.使用预编译语句，可以提高性能并且防范 sql注入攻击。

4.一般情况下update,delete 语句中不要使用limit。

5.where 条件语句中必须使用合适的类型，避免mysql进行隐式转换。

6.insert into 必须显式指明字段名称，不要使用insert into table()。

7.避免在sql 语句中进行数学运算或函数运算，避免将业务逻辑和数据存储耦合在一起。

8.insert 语句如果使用批量提交，如insert into table values(),()...那么values 的个数不应过多。一次性提交过多记录，会导致I/O紧张，出现慢查询。

9.避免使用存储过程、触发器、函数等，这些特性会将业务逻辑与数据库耦合在一起，并且MySQL的存储过程，触发器，函数中可能存在bug。

10.尽量避免使用子查询，连接。尽量将子查询转化为连接查询，mysql 查询优化器会优化连接查询，但连接的表要尽可能的少，如果很多，可以考虑反范式设计。即对设计阶段做一些改造。

11.使用合理的sql语句以减少与数据库的交互次数。

12.建议使用合理的分页技术以提高操作效率。

1.使用 explain 工具可以确认执行计划是否良好，查询是否走了合理的索引。

2.不同版本MySQL 优化器各有不同，一些优化规则随着版本的发展可能有变化，

查询的执行计划随着数据的变化也可能发生变化，这类情况就需要使用explain 来验证自己的判断。

执行如下脚本，观察控制台输出

explain select name from test where id = 32;

注意数据表使用如下脚本：

CREATE TABLE `test` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8;

table name = test、column1 = id、column2 = name.

执行结果如下所示：

下面详细阐述explain 输出的各项内容：

包含一组数字，表示查询中执行 select子句或操作表的顺序。

如果 id 相同，则执行顺序由上到下。

select_type:

表示查询中每个 select 子句的类型(是简单还是复杂)输出结果类似如下：1.simple查询中不包含子查询或者union2.primary查询中若包含任何复杂子查询，最外层查询被标记为primary3.subquery在select 或 where 列表中包含了子查询，则该查询被标记为subquery4.derived在from列表中包含的子查询被标记为derived(衍生)5.union若第二个select出现在union之后，则被标记为derived。6.union result从union表中获取结果的select将被标记为 union result。select_type 只需要了解分类即可，这个信息并不是最有价值的。

type:最有价值信息之一

possible_keys

possible_keys 将指出MySQL能使用哪个索引在表中找到行，查询涉及的字段上若存在索引，则该索引将被列出，但不一定会被查询使用。

key:最有价值信息之二

key 将显示MySQL在查询中实际使用到的索引，若没有使用索引，则显示为null。查询中若使用到了覆盖索引，则该索引仅仅出现在 key 列表中，possible_keys中并不显示。

key_len表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。

ref表示上述表的连接匹配条件，即哪些列或常亮被用于查找索引列上的值。

rows:最有价值信息之三

rows 表示MySQL根据表统计信息及索引选用的情况，估算查找所需记录需要读取的行数。使用到索引一般情况下会使得rows的值降低。

Extra:最有价值信息之四

Extra 包含不适合在其他列中显示但十分重要的额外信息。可能包如下4种信息。1.Using index该值表示相应的select操作中使用到了覆盖索引，包含满足查询需要的数据的索引称为覆盖索引。2.Using where如果查询未能使用索引，则Using where 的作用只是提醒我们 MySQL 将用where 子句来过滤结果集。3.Using temporary表示MySQL需要使用临时表来存储结果集，常见于order by 与 group by，事实上group by会进行隐式的order by。

如果我们在group by 时利用索引分组(其实包含排序的过程)排序，则可以提高性能，因为不会此时查询输出里没有了Using temporary,Using filesort。

4.Using filesortUsing filesort 即文件排序，MySQL 中将无法使用索引完成的排序操作，称为文件排序。

上文篇幅有点长，但都是必须了解的概念。最有价值信息是我们判断sql语句执行是否高效的基准，了解四个最有价值信息是最重要的。

继续演示explain的使用，使用上文的评判标准来看下语句的执行效率：1.主键查询