Mysql日常开发注意要点

时间:2021-10-08 12:44:19

1、MySQL存储引擎介绍

MyISAM:低版本MySQL默认的MySQL插件式存储引擎,存储文件易损坏,不支持事务。
InnoDB:目前默认的MySQL存储引擎,用于事务处理应用程序,具有众多特性,包括ACID事务支持

建表语句建议使用 ENGINE=InnoDB 字段,例如:

CREATE TABLE `t_user` (
....
) ENGINE=InnoDB AUTO_INCREMENT=初始值 DEFAULT CHARSET=utf8;

2、explain执行计划mysql explain用法
type: all 全表扫描
rows: 扫描的行数
Extra: Using temporary; Using filesort

3、字段使用函数,将无法使用索引
例:SELECT * FROM t WHERE YEAR(d) >= 2016;
由于MySQL不像Oracle那样支持函数索引,即使d字段有索引,也会直接全表扫描。
应改为-->SELECT * FROM t WHERE d >= '2016-01-01';

建议改为如下,reffereeTime字段建索引
reffereeTime >= '2016-07-03' AND reffereeTime < '2016-07-10'
reffereeTime >= '2016-07-03 00:00:00' AND reffereeTime < '2016-07-10 00:00:00'

4、以%开头的like无法使用到索引
SELECT * FROM t WHERE name LIKE '%de%'; #无法使用到索引
SELECT * FROM t WHERE name LIKE 'de%'; #使用到索引

5、避免隐式转换
列类型是字符串,那么一定记得在where条件中把字符串常量值用引号引起来,否则即便这个列上有索引,MySQL也不会用到

6、让 group by 不排序
group by payment_date order by null 不排序,如统计count(*)

7、随机数的写法
SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;
MySQL不支持函数索引,会导致全表扫描
应改为SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;

8、子查询 关联查询比较
子查询效率不如关联查询(join)连接,相关子查询:DEPENDENT SUBQUERY

9、select *
Select与from语句之间只定义返回的字段名,除非返回所有的字段,尽量不要使用*,字段名应按照表的字段物理顺序编写

10、索引
经常用于WHERE子句中使用的列考虑作索引的列。
经常用于SQL语句中连结表的列考虑作为索引的列。
频繁修改的列不建议作为索引列。

11、复合索引
用户在一张表的三个列(a,b,c)建立一复合索引,该复合索引的顺序为abc。

在SQL语句的WHERE条件中使用a,ab,abc等都可利用到索引,但是如果使用bc,c等就无法利用到索引。

复合索引 idx1(a, b, c),那么下面的SQL都可以完整用到索引:
SELECT ... WHERE b = ? AND c = ? AND a = ?; --注意到,WHERE中字段顺序并没有和索引字段顺序一致
SELECT ... WHERE b = ? AND a = ? AND c = ?;
SELECT ... WHERE a = ? AND b IN (?, ?) AND c = ?;
SELECT ... WHERE a = ? AND b = ? ORDER BY c;
SELECT ... WHERE a = ? AND b IN (?, ?) ORDER BY c;
SELECT ... WHERE a = ? ORDER BY b, c;
SELECT ... ORDER BY a, b, c; -- 可利用联合索引完成排序

而下面几个SQL则只能用到部分索引,或者可利用到ICP特性:
SELECT ... WHERE b = ? AND a = ?; -- 只能用到 (a, b) 部分
SELECT ... WHERE a IN (?, ?) AND b = ?; -- EXPLAIN显示只用到 (a, b)部分索引,同时有ICP
SELECT ... WHERE (a BETWEEN ? AND ?) AND b = ?; -- EXPLAIN显示只用到 (a, b) 部分索引,同时有ICP
SELECT ... WHERE a = ? AND b IN (?, ?); -- EXPLAIN显示只用到 (a, b) 部分索引,同时有ICP
SELECT ... WHERE a = ? AND (b BETWEEN ? AND ?) AND c = ?; -- EXPLAIN显示用到 (a, b, c) 整个索引,同时有ICP
SELECT ... WHERE a = ? AND c = ?; -- EXPLAIN显示只用到 (a) 部分索引,同时有ICP
SELECT ... WHERE a = ? AND c >= ?; -- EXPLAIN显示只用到 (a) 部分索引,同时有ICP

ICP(index condition pushdown)是MySQL 5.6的新特性,其机制会让索引的其他部分也参与过滤,减少引擎层和server层之间的数据传输和回表请求,通常情况下可大幅提升查询效率。

下面的几个SQL完全用不到该索引:
SELECT ... WHERE b = ?;
SELECT ... WHERE b = ? AND c = ?;
SELECT ... WHERE b = ? AND c = ?;
SELECT ... ORDER BY b;
SELECT ... ORDER BY b, a;