Mysql优化

八个方式

一、选取适用的字段属性

1、根据字段存储内容来设定字段的大小；

2、尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值；

3、对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。

二、使用连接（join）方式来代替子查询

子查询：MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。

例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：

DELETE FROM customerinfo
WHERE CustomerIDNOTin(SELECT CustomerID FROM salesinfo)

连接（join）：因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作，所以效率更高。

三、使用联合（union）来代替手动创建的临时表

​ MySQL从4.0的版本开始支持union查询，它可以把临时表的两条或多条查询语句合并在同一个语句中，在客户端查询结束时，临时表会被删除，从而保证数据库整齐、高效，在使用联合（union）合并查询时，要注意的是所有select语句中的字段数目要相同**。**

四、事务

​ 在执行多个sql语句时，应该用到事务，它可以保证所有sql语句都成功或者都失败。事务以BEGIN关键字开始，COMMIT关键字结束。

1.事务回滚：在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。例如：

BEGIN; insert inito 表名 () values ();update 表名 set 字段=值； COMMIT;

2.当多个用户同时使用相同的数据源时，事务可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。

五、锁表

​ 事务维护数据库安全性的同时，也因他的独占性，有时会影响数据库的性能。由于在事务执行时会锁定数据库，使得其他用户请求只能暂时等待直到事务的结束。如果用户数量少，则影响不大；反之用户数量多了，则会导致性能的下降。例如：在高并发下，事务就会导致用户请求的延迟。

包含关键字的LOCK TABLE语句可以保证在UNLOCK TABLES命令被执行之前，不会有其它的访问来对表1进行插入、更新或者删除的操作。

锁的区别
lock tables/ flush table read

lock flush table read lock: 全局读锁 ，解锁：unlock tables
lock table table_name read/write :指定表锁，解锁:unlock table table_name /unlock tables table_names 每次只能持一个锁lock tables table read :没有会话能DML read-locked 表
lock tables table_name write :除了持有锁会话，没有会话能访问write-locked表

六、索引

​ 索引可以令数据库服务器比没有索引快的多的速度检索特定的行，尤其是查询语句中包含max()、min()和order by等命令时，性能提高的更为明显。

注意：索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。

七、优化查询语句

​ 绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

1.最好是在相同类型的字段间进行比较的操作

​ 在MySQL3.23版之前，这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。

2.在建有索引的字段上尽量不要使用函数进行操作

​ 例如，在一个DATE类型的字段上使用YEAE()函数时，将会使索引不能发挥应有的作用。

3.在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。

SELECT * FROM books ``WHERE name like "MySQL%"

SELECT * FROM books ``WHERE name ＞= "MySQL" andname ＜ "MySQM"
上面两条sql，结果是一样的，但第二条的效率会比第一条快

4.注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。

八个方法

1.创建索引

​ 在查询占主要的应用下，索引就显得更外的重要。索引可以在表内数据多时，不需要通过全表去扫描查找，而是通过建的索引去查找；但也不是一定要加索引，因为在一些数据重复比较多的情况下，给改字段建索引会导致性能。这种索引也被称为过渡索引。

2.复合索引

比如有一条语句是这样的：select * from users where area=‘beijing’ and age=22;

如果我们在area和age两个字段创建了单个索引，由于mysql查询只能使用一个索引，所以这样子相对于不做索引时的全表扫描效率快了，但如果在area和age上创建了复合索引，那么效率又能再一次的提高。如果我们创建了（area，age，salary）的复合索引，也就相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

3.索引不会包含有null值的列

​ 如果添加索引的字段中带有null值，那么这个索引则对改列是无效的。

4.使用短索引

​ 对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

5.排序的索引问题

​ mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

6.like语句操作

​ 一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

7、不要在列上进行运算
select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2007-01-01’;

8、不使用NOT IN和<>操作
NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替

原文连接：(32条消息) MySQL数据库优化的八种方式(经典必看)_repoman_的博客-CSDN博客_mysql优化的几种方法