mysql数据库优化

目录

性能优化的几种方法

1.选择合适的字段类型

2.尽量不将字段设置为NULL

3.最大化利用索引

4.优化SQL语句

4.1.避免出现select *

4.2.减少使用JOIN

4.3.减少使用in、not in、or

4.4.where查询时，避免使用函数,并且注意位置

4.5.使用join来代替子查询

5.DML语句优化

6.通过事务的优化

 7.使用外键

 8.数据库架构优化

1.负载均衡

2.读写分离

3.数据切分

---

       

在数据库开发过程中，mysql的数据内容是不断增加的，而数据越多，相应的后期开发，查询就越繁琐，导致性能下降。因此，在开发前，要注意关注数据库的性能进行开发。

性能优化的几种方法

1.选择合适的字段类型

        mysql是关系型数据库，能够支持大量的数据存储操作。一般而言，表中的数据越小查询的时候就越快。因此在创建表时，给其赋予合适的数值类型，能够有效减少数据大小。

        例如：年龄的值，通常不是很大，就不必设置为int类型（占用4个字节），给其设置为tinyint类型（占用1个字节）完全能够满足需要，并且减少其占用的内存，在查询表时，效率也就更快。

2.尽量不将字段设置为NULL

        一张表中，没有NULL值时，数据库在执行查询时，就不进行NULL的比较。如果有NULL时，数据库回进行比较，查看数据是否位NULL，从而增加CPU负担。

3.最大化利用索引

        索引的特点就是，能够提高数据库的查询效率。在创建表时，合理的设置索引，能够使后期操作更加方便。但是不能滥用索引，索引不是越多越好，需要有针对性的设置。

        在创建索引时，不要对数据库中某个含有重复值的字段设置索引。这样的设置，并不会对性能有帮助，相反，还你可能会使性能降低。

4.优化SQL语句

4.1.避免出现select *

        在企业生产中，产生的数据会是成百上千条，使用select * 查询，将会增加网络带宽的消耗，极大消耗CPU和内存。只需要列出所需的内容即可。

4.2.减少使用JOIN

        MySQL 的优势在于简单，但这在某些方面其实也是其劣势。MySQL 优化器效率高，但是由于其统计信息的量有限，优化器工作过程出现偏差的可能性也就更多。

        对于复杂的多表 Join ，一方面由于其优化器受限，再者在 Join 这方面所下的功夫还不够，所以性能表现离 Oracle 等关系型数据库前辈还是有一定距离。但如果是简单的单表查询，这一差距就会极小甚至在有些场景下要优于这些数据库前辈。

4.3.减少使用in、not in、or

        在进行值的过滤时，使用in、not in 会让数据库额外工作。

select * from student where id in(2,4);

        在数值段的操作时，可以使用 between and进行查询

select * from student where id between 2 and 4;

        如果是子查询，可以使用exists代替

select * from student where id exists(select id from course);

        使用or 可以用 union来代替，减少引擎负担

select * from student where id=2
union
select * from student where id=3;

4.4.where查询时，避免使用函数,并且注意位置

        使用where查询时，需要进行where处理则加where,不需要则不加。此外，避免在where 条件的=左边使用函数，会使数据库放弃索引，进行全表的扫描。

        使用where查询时，将过滤数据多的条件放在前面，能够最快的锁定范围，不必多次减少范围

----不会使用索引
 select * from student where age+1=3;
----使用索引
 select * from student where age=3+1;

4.5.使用join来代替子查询

        在进行多表查询时，通常需要取一个表的某个或多个值在另一张表中查询。使用join连接，使数据库不用创建临时表，从而减少消耗

---查询时没有创建临时表
select * from student s join course c on s.id=c.id;
---查询时创建临时表
select * from student s where id in (select id from course);

5.DML语句优化

1.大批量插入数据

 插入多条数据时，使用多个值的insert 要比 逐条插入效率高。

insert into student values(5,"张三","2000100105"),(6,"李四","2000100106"),(7,"王五","2000100107");

2.适当使用commit

commit能够释放事务占用的资源，减少消耗

6.通过事务的优化

        对多个表进行操作时，如果第一个表执行成功，第二个表因为数据库崩溃等原因没有执行成功，就导致两个表的数据不一致。如果要避免这种情况产生，可以使用事务处理。它可以使数据同步，要么两个都执行成功，要么两个都失败。可以保证数据的完整性，一致性。

        一般来说，事务必须满足四个条件(ACID)：原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability).

• 原子性：一个事物(transaction)中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚(Rollback)到事务开始的状态，就像这个事务从来没有执行过一样。
• 一致性：在事务开始之前和事务结束之后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性：数据库允许多个事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同的级别，包括读未提交(Read uncommitted)、读已提交(Read committed)、可重复读(repeateable read)和串行化(Serializable).
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务的并发问题：

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据就是脏数据

2、不可重复读：事务A多次读取同一事物，事务B在事务A多次读取的过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，结果不一致。

3、幻读：系统管理员A将数据库中的所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

MySQL事务隔离级别：

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

事务控制语句：

• BEGIN或START TRANSACTION：显式的开启一个事物。
• COMMIT：也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的。
• Rollback：也可以使用Rollback work，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
• SAVEPOINT identifier：SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有很多个SAVEPOINT；
• RELEASE SAVEPOINT identifier：删除一个事物的保存点,当没有指定的保存点时，执行该语句会抛出一个异常。
• ROLLBACK TO inditifier：把事务回滚到标记点。
• SET TRANSACTION:用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERLALIZABLE。

 7.使用外键

        外键能够把一张表的内容根据外键映射到另一张表中。可以保证数据相关联。

举例说明：

1.先创建班级表；

create table classes(
-> class_id int primary key auto_increment,
-> class_name varchar(40) not null unique,
-> class_remark varchar(100)
-> );

2.创建学生表，并且设置外键与主键关联
将cid设置为外键，cid 要与学生表的主键class_id 相关联，两者的类型和长度要一致

create table students(
stu_num char(8) primary key,
stu_name varchar(10) not null,
stu_gender char(2) not null,
stu_age int not null,
cid int,

#设置外键逻辑名称FK_STUDENTS_CLASSES，将students的键cid与classes相关联，关联的键为class_id

constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id));

 8.数据库架构优化

1.负载均衡

        负载均衡集群是由一组相互独立的计算机系统构成，通过常规网络或专用网络进行连接，由路由器衔接在一起，各节点相互协作、共同负载、均衡压力，对客户端来说，整个群集可以视为一台具有超高性能的独立服务器。

2.读写分离

        读写分离简单的说是把对数据库读和写的操作分开对应不同的数据库服务器，这样能有效地减轻数据库压力，也能减轻 IO 压力。

        主数据库提供写操作，从数据库提供读操作，其实在很多系统中，主要是读的操作。当主数据库进行写操作时，数据要同步到从的数据库，这样才能有效保证数据库完整性。

3.数据切分

        通过某种特定的条件，将存放在同一个数据库中的数据分散存放到多个数据库上，实现分布存储，通过路由规则路由访问特定的数据库，这样一来每次访问面对的就不是单台服务器了，而是N台服务器，这样就可以降低单台机器的负载压力。