mysql查询优化

Mysql优化

##1：索引可以大幅度提高查询性能

1.1 缺省情况下建立的索引是非群集索引，但有时它并不是最佳的。在非群集索引下，数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说：

a.有大量重复值、且经常有范围查询( > ,< ，> =,< =)和order by、group by发生的列，可考虑建立群集索引;

b.经常同时存取多列，且每列都含有重复值可考虑建立组合索引;

c.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好，恰好相反过多的索引会导致系统低

效。用户在表中每加进一个索引，维护索引集合就要做相应的更新工作。

1.2 用explain语句查询索引使用情况

如何使用请查看文章：https://www.360docs.net/doc/fa3383592.html,/yueguanghaidao/article/details/6933387

索引注意事项：

a. 使用FULLTEXT参数可以设置索引为全文索引，全文索引只能创建在CHAR ,VARCHAR ,TEXT类型字段上。->>但只有MyISAM存储引擎支持全文索引。

b: 多列索引：在表的多列字段上建立一个索引，但只有在查询这些字段的第一个字段时，索引才会被使用。

c. 查询语句使用like关键字进行查询，如果匹配的第一个字符为”%“时，索引不会被使用

d. 查询语句中使用or关键字时，只有or前后两个条件的列都是索引时，查询时才使用索引

e.最好在相同类型的字段间进行比较，如不能将建有索引的int字段与bigint字段进行比较

如在一个DATE类型的字段上使用YEAE()函数时，将会使索引不能发挥应有的作用。所以，下面的两个查询虽然返回的结果一样，但后者要比前者快得多。

SELECT * FROM order WHERE YEAR(OrderDate)<2001;

SELECT * FROM order WHEREOrderDate<"2001-01-01";

f. 任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边

因为在WHERE子句中对字段进行函数或表达式操作，这将导致引擎放弃使用索引而进行全表扫描

如：SELECT * FROM inventory WHERE Amount/7<24;

SELECT * FROM inventory WHERE Amount<24*7;

上面的两个查询也是返回相同的结果，但后面的查询将比前面的一个快很多

SELECT * FROM RECORD WHERESUBSTRING(CARD_NO,1,4)=?5378?

应改为: SELECT *FROM RECORD WHERE CARD_NO LIKE …5378%?

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

例如下面的查询将会比较表中的每一条记录。

SELECT * FROM books WHERE name like "MySQL%"

但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：

SELECT * FROM books WHERE name>="MySQL"andname<"MySQM";

h. 避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符,因为这会使系统无法使用索引,而只能直接搜索表中的数据。

例如: SELECT id FROM employee WHERE id !="B%" 优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。

i. 能够用BETWEEN的就不要用IN,因为IN会使系统无法使用索引,而只能直接搜索表中的数据

如：SELECT * FROM T1 WHERE ID IN(10,11,12,13,14)改成：SELECT *FROM T1 WHERE ID BETWEEN 10 AND 14

##2：在可能的情况下尽量限制尽量结果集行数

2.1:使用top

如：SELECT TOP 300COL1,COL2,COL3 FROM T

2.2:增加limit 1 会让查询更加有效

这样数据库引擎发现只有1后停止扫描，而不会去扫描整个表或索引

2.3:尽量避免slecect * 命令，而是需要什么字段，查询什么字段

##3：合理使用EXISTS,NOT EXISTS子句

如果想校验表里是否存在某条纪录，不要用count(*)那样效率很低，而且浪费服务器资源。可以用EXISTS 代替。如：

IF (SELECT COUNT(*) FROM table_name WHEREcolumn_name = 'xxx')

可以写成：IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')

##4：数据类型

4.1: 只要能满足需求，应尽可能使用小的数据类型，

比如能用tinyint 就不用int

4.2: varchar比char节省空间，但效率比char低，想要获得效率就得牺牲一定空间。

如果一个varchar的列经常被修改，而且修改的数据长度不同，会引起…行迁移?问题，造成多余I/O花费，这时最好用char代替varchar

如果是像身份证定长的字段，一定要用char ,查询时是全字段匹配，能获取更高效率。

这里考虑一个问题？？-》》使用varchar(5)和varchar(200)保存…hello?占用的空间都是一样的，但是使用较短的列有巨大优势，因为较大的列会占用更多的

内存。

4.3:如果字段类型只有少量的几个，最好使用enum类型，因为enum类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多.

例如省份，性别等字段。

4.4:尽量使用数字型字段，一部分开发人员和数据库管理人员喜欢把包含数值信息的字段设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了

4.5：一般情况下日期和时间类型最好选择timestamp，因为datetime占用8字节存储空间，而timestamp 占用4字节存储空间，明显更节约空间。

##5.在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。

##6.使用连接查询(join)代替子查询

因为子查询时，mysql需要为内层查询结果建立一个临时表，然后外层查询在临时表中查找，查询完后需要撤销临时表。

而连接查询不需要建立临时表，所以比子查询快。

##7. 使用联合(union)来代替手动创建的临时表

MySQL 从4.0 的版本开始支持UNION 查询，它可以把需要使用临时表的两条或更多的SELECT 查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自

动删除，从而保证数据库整齐、高效。使用UNION 来创建查询的时候，我们只需要用UNION作为关键字把多个SELECT 语句连接起来就可以了，要注意的是所有

SELECT语句中的字段数目要想同。下面的例子就演示了一个使用UNION的查询。

select name,phone from clinet union select name,bitthdate from author union slect name,supplier from product

##8. 充分利用连接条件

在某种情况下，两个表之间不止一个连接条件，这时可在where子句中将连接条件完整写上，可大大提高查询速度。如：

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD BWHERE A.CARD_NO = B.CARD_NO SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD BWHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO

第二句将比第一句执行快得多。

##9. 能用DISTINCT的就不用GROUP BY

SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID

可改为：SELECT DISTINCT OrderID FROMDetails WHERE UnitPrice > 10

##10.尽量不要用SELECT INTO语句。SELECTINTO 语句会导致表锁定，阻止其他用户访问该表

##11.UPDATE语句建议：

a. 尽量不要修改主键字段。

b. 当修改VARCHAR型字段时，尽量使用相同长度内容的值代替。

c. 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。

d. 避免UPDATE将要复制到其他数据库的列。

e. 避免UPDATE建有很多索引的列。

f. 避免UPDATE在WHERE子句条件中的列。