MySql的优化方案整理

Mysql中的索引官方定义是帮助Mysql高效的获取数据的数据结构。树结构类型，类似于图书的数目索引，可以提高数据检索的效率，降低数据库的IO成本

索引的分类：聚集索引与非聚集索引、唯一索引与非唯一索引

聚集索引的意思可以理解为顺序排列，比如一个主键自增的表即为聚集索引。一个表只能包含一个聚集索引。

唯一索引：如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique

建立索引：CREATE INDEX mycolumn_index ON mytable (myclumn)

 

数据库查询优化方案：

一、对表字段的优化：

1、最好不要给数据库留NULL，尽可能的使用 NOTNULL填充数据库.

2、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

3、尽可能的使用 varchar/nvarchar代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

 

二、对SQL语句的优化

1. 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where及 order by 涉及的列上建立索引。

2. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

3. 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。

4. 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描

5. 应尽量避免在 where子句中对字段进行表达式操作、函数操作，这将导致引擎放弃使用索引而进行全表扫描。

6. in和 not in 也要慎用，否则会导致全表扫描

对于连续的数值，能用 between就不要用 in 了

很多时候可以使用 exist 和not exist代替in和not in。

7. LIKE操作符 例如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。

8. 对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。

9. select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。

10.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

11.不要以字符格式声明数字，要以数字格式声明字符值。（日期同样）否则会使索引无效，产生全表扫描。

12.尽量避免使用游标，因为游标的效率较差（游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果）

13.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

 

三、  对数据库进行修改的优化处理

1. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个

2. (临时表 #本地临时表 ##全局临时表) 避免频繁创建和删除临时表，以减少系统表资源的消耗。在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先createtable，然后insert。

3. 尽量避免大事务操作，提高系统并发能力。

4. Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志 ORM对象关系映射（性能差）