SQL优化、mysql数据库优化
1、字段空间能小则小
2、字段设置not null 给默认值
3、使用
ENUM类型比文本类型效率高,如性别:男、女
4、创建正确的索引
一般说来,索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。
对于一个大的数据库,将数据装载到一个没有FULLTEXT索引的表中,然后再使用ALTERTABLE或CREATEINDEX创建索引,将是非常快的。
但如果
将数据装载到一个已经有FULLTEXT索引的表中,执行过程将会非常慢
。
5、in条件中的子查询集 用 关联查询后where条件判断 代替,如下:
用
SELECT
*
FROM
customerinfo
LEFT JOIN salesinfo
ON customerinfo.CustomerID=salesinfo.CustomerID
WHERE
salesinfo.CustomerID IS NULL;
代替
SELECT
*
FROM
customerinfo
WHERE
CustomerID NOT IN(SELECT CustomerID FROM salesinfo);
事务
事物以BEGIN关键字开始,COMMIT关键字结束。在这之间的一条SQL操作失败,那么,ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。
BEGIN;INSERT INTO salesinfo SET CustomerID=14; UPDATE inventory SET Quantity=11 WHERE item='book';COMMIT;
事务的另一个重要作用是当多个用户同时使用相同的数据源时,它可以利用锁定数据库的方法来为用户提供一种安全的访问方式,这样可以保证用户的操作不被其它的用户所干扰。
尽管事务是维护数据库完整性的一个非常好的方法,但却因为它的
独占性
,有时会影响数据库的性能,尤其是在很大的应用系统中。
减少独占的范围
减少独占的范围
锁定表
LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item='book';
INSERT INTO salesinfo SET CustomerID=14;
UPDATE inventory SET Quantity=11 WHERE Item='book';UNLOCK TABLES;
以保证insert操作后update的
同步
操作成功
这里,我们用一个select语句取出初始数据,通过一些计算,用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前,不会有其它的访问来对inventory进行插入、更新或者删除的操作。
这里,我们用一个select语句取出初始数据,通过一些计算,用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前,不会有其它的访问来对inventory进行插入、更新或者删除的操作。
使用外键
锁定表的方法可以维护数据的完整性,但是它却不能保证数据的关联性。这个时候我们就可以使用外键。
CREATE TABLE customerinfo(CustomerID INT NOT NULL,PRIMARY KEY(CustomerID))TYPE=INNODB;
CREATET ABLE salesinfo(SalesID INT NOT NULL,CustomerID INT NOT NULL,PRIMARY KEY(CustomerID,SalesID),
FOREIGN KEY(CustomerID) REFERENCE Scustomerinfo(CustomerID)ON DELETE CASCADE) TYPE=INNODB;
关系表的联级删除
注意例子中的参数“ON DELETE CASCADE”。
该参数保证当customerinfo表中的一条客户记录被删除的时候,salesinfo表中所有与该客户相关的记录也会被自动删除
。如果要在MySQL中使用外键,一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。
sql优化
1、
最好是在相同类型的字段间进行比较的操作;
2、
在建有索引的字段上
尽量不要使用函数
进行操作;
3、
在搜索字符型字段时,我们有时会使用LIKE关键字和通配符,这种做法虽然简单,但却也是以牺牲系统性能为代价的;
例如下面的查询将会比较表中的每一条记录。
SELECT * FROM books
WHERE name like"MySQL%";
但是如果换用下面的查询,返回的结果一样,但速度就要快上很多:
SELECT * FROM books
WHERE name>="MySQL" and name<"MySQM";
4、
应该注意
避免
在查询中让MySQL进行
自动类型转换
,因为
转换过程也会使索引变得不起作用;
5、分组统计可以禁止排序;
SELECT goods_id,count(*) FROM t GROUP BY goods_id;
默认情况下,MySQL对所有GROUP BY col1,col2…的字段进行排序。
如果查询包括GROUP BY,想要避免排序结果的消耗,则
可以指定ORDER BY NULL禁止排序
。
6、批量INSERT插入;
INSERT INTO t (id, name) VALUES(1,’Bea’);
INSERT INTO t (id, name) VALUES(2,’Belle’);
INSERT INTO t (id, name) VALUES(3,’Bernice’);
—–>
INSERT INTO t (id, name) VALUES(1,’Bea’), (2,’Belle’),(3,’Bernice’);
见: