SQL优化、mysql数据库优化

时间：2022-08-14 23:25:15
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进行插入、更新或者删除的操作。

    使用外键

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。
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’);
见:

标签：

相关文章