MySQL性能优化简记

1.选择最合适的字段属性

2.尽量把字段设置不为空（NOT NULL）

3.使用连接（join）查询代替子查询

4.使用联合（UNION）代替手动创建临时表

5.锁定表

6.使用外键

可以保证数据的完整性，外键可以把customerinfo表中的customerid映射到salesinfo表中customerid，任何一条没有办法合法customerid的记录都不会被跟新或插入到salesinfo中.

建表必须设置类型为事务安全表InnoDB类型 即+（engine=innoDB）

CREATE TABLE customerinfo (customerid INT PRIMARY KEY) ENGINE = INNODB;

CREATE TABLE salesinfo (

salesid INT NOT NULL,

customerid INT NOT NULL,

PRIMARY KEY (customerid, salesid),

FOREIGN KEY (customerid) REFERENCES customerinfo (customerid) ON DELETE CASCADE

) ENGINE = INNODB;

存储引擎分类【InnoDB,MyISAM】

MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。

每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引

MyISAM

优点：查询数据相对较快，适合大量的select，可以全文索引。

缺点：不支持事务，不支持外键，并发量较小，不适合大量update

InnoDB

优点：支持事务，支持外键，并发量较大，适合大量update

缺点：查询数据相对较快，不适合大量的select

区别：1.InnoDB支持事务，MyiSAM不支持事务

           2.InnoDB适合频繁修改以及涉及安全性较高的应用，MyiSAM适合查询以及插入为主的应用

           3.InnoDB支持外键，MyiSAM不支持

           4.InnoDB需要指定引擎，MyiSAM则是默认

           5.InnoDB不支持FULLTEXT类型索引

           6.InnoDB中不保存表的行，查询count时时扫描全表计算；而MyiSAM只是简单读取保存的                行数即可，当然如使用where则是要扫描全表计算的

           7.对于自增长的字段，InnoDB中必须包含该字段的索引，但是MyiSAM表中可以和其他字                  段 一起建立联合索引

           8.清空整个表时，InnoDB是一行一行删除，效非常慢；MyiSAM则会重建表

           9.InnoDB支持行索（有些情况下还还是锁整表，如：update table set  a=1 where name like '%bbbb%'）

7.使用索引

索引：【从逻辑角度】

普通索引:它没有任何限制

主键索引:一个表只能有一个主键，不允许有空值

唯一索引:索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

复合索引:指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才                   会被使用。使用组合索引时遵循最左前缀集合

全文索引:主要用来查找文本中的关键字，而不是直接与索引中的值相比较

注意事项（索引失效）：

1.索引不会包含null值的列

2.使用短索引

3.索引列排序  【查询每次只会使用一个索引，如果where中已经使用，那么order by中的列就不会使用到索引的】

4.like语句操作【like “%ttt%”不会使用索引，like “tt%”可以使用索引】

5.不要在列上进行运算，这会代价索引失效而进行全表扫描【SELECT * FROM a WHERE YEAR(name)>2022;】

6.不使用not in和<>操作

8.使用事务

语法：BEGIN;开始事务

SQL1;执行sql

SQL2;执行sql

COMMIT;提交事务   //SQL失败使用Rollback回滚事务

事务的特性（原子性，一致性，隔离性，持久性）

事务并发问题：脏读，不可重复读，幻读

事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交	√	√	√
不可重复读	x	√	√
可重复读	x	x	√
可串行化	x	x	x

9.优化查询语句

1>不使用子查询

2>避免函数索引

3>用IN代替OR

4>LIKE 双%无法使用索引

5>读取数据适当的使用 LIMIT M,N （即分页取法）

6>避免数据类型不一致

7>分组统计可以禁止排序

8>避免随机取记录

9>禁止不必要的ORDER BY排序

10>批量INSERT插入

mysql查询缓慢原因

1.没有索引或者没有命中索引（程序设计的缺陷）

解决：对应建立索引，分析sql是否命中索引，然后优化sql命中索引

2.锁或者死锁（程序设计的缺陷）

死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。

解决：

• 按同一顺序访问对象
• 避免事务中的用户交互
• 保持事务简短并在一个批处理中
• 使用低隔离级别
• 使用绑定连接

3.查询出来的数据量过大（

解决：可以采用多次查询，其他的方法降低数据量

4.内存不足

解决:加大内存

5.网络速度慢

解决：1、宽带的兆数不够，需要升级宽带；2、传输终端不行，需要更换配置高的传输终端（例如光猫、路由器）；3、区域的宽带资源不行，需要联系运营商来增大资源数量或者维修勘测。

6.返回了不必要的行和列

解决：去掉返回不必要的行和列

7.sp_lock,sp_who,活动的用户查看,原因是读写竞争资源

8.查询语句不好，没有优化。

9.I/O吞吐量小，形成了瓶颈效应。

10.没有创建计算列导致查询不优化