Mysql数据库的优化
Mysql数据库的优化成本从高到底可以分为硬件，系统配置，数据库表结构，SQL和索引。
一、SQL的优化
根本目标：
（1）最大化利用索引
（2）尽量避免全表扫描
（3）减少无效数据的查询
WHERE优化
- 尽量避免在where子句中对字段进行null值判断，建表的时候可以用NOT NULL 或一些特殊字符0,-1做默认值。
- 尽量避免在where子句中使用or来连接条件，使用UNION优化，否则数据库引擎放弃索引进行全表扫描。
示例：explain select * from student where stu_sn like '70203%' or stu_id<10; SELECT * FROM t WHERE id = 1 OR id = 3 优化方式：可以用 union 代替 or。 如下： SELECT * FROM t WHERE id = 1 UNION SELECT * FROM t WHERE id = 3
-
应尽量避免在 where 条件中等号左侧进行算术运算或函数操作，例如： select id from t where num/2=100应改为select id from t where num=100*2。
-- 全表扫描 SELECT * FROM T WHERE score/10 = 9 -- 走索引 SELECT * FROM T WHERE score = 10*9
-
调整 Where 字句中的连接顺序
MySQL 采用从左往右，自上而下的顺序解析 where 子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。
- 尽量避免在where子句中使用！=或是<>操作符，MySql只有对以下操作符采使用索引：<,=,=,>,>=,BETWEEN,IN以及某些时候的LIKE【模糊查询，指操作数不是以通配符开头】。
- order by 条件要与 where 中条件一致，否则 order by 不会利用索引进行排序。
-- 不走age索引 SELECT * FROM t order by age; -- 走age索引 SELECT * FROM t where age > 0 order by age;
Select优化
（1）多表关联查询时，小表在前，大表在后
在 MySQL 中，执行 from 后的表关联查询是从左往右执行的(Oracle 相反)，第一张表会涉及到全表扫描。
所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前 100 行就符合返回条件并 return 了。
（2）当在 SQL 语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些有列名歧义引起的语法错误。
（3）隐式类型转换造成不使用索引
SQL 语句由于索引对列类型为 varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。
select col1 from table where col_varchar=123;
（4）尽量避免in和NOT IN，【导致引擎走全表扫描】，用BETWEEN,EXISTS或NOT EXISTS，JOIN代替。
如果是连续数据，用Between优化 SELECT * FROM t WHERE id IN (2,3) Select * From t where if between 2 and 3 如果是子查询，可以用 exists 代替。 select * from A where A.id in (select id from B); select * from A Where exists(select * from where B.id=A.id)
（5）任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段
Insert优化
1.一条SQL语句插入多条数据
INSERT INTO MyTable ( Column1, Column2, Column3 ) VALUES ('John', 123, 'Lloyds Office'), ('Jane', 124, 'Lloyds Office'), ('Billy', 125, 'London Office'), ('Miranda', 126, 'Bristol Office');
这种方式销量高的主要原因是合并后日志量(MySQL的binlog和innodb的事务让日志)减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO。
2.适当使用 commit，例如在事务中进行插入处理**
START TRANSACTION; INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0); INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1); COMMIT;
这是因为进行一个INSERT操作时，mysql 内部会建立一个事务，在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交操作。
3. 数据有序插入
数据有序的插入是指插入记录在主键上是有序排列
三种方法的测试总结
合并数据+事务的方法在较小数据量时，性能提高是很明显的，数据量较大时(1千万以上)，性能会急剧下降，这是由于此时数据量超过了innodb_buffer的容量，每次定位索引涉及较多的磁盘读写操作，性能下降较快。而使用**合并数据+事务+有序数据**的方式在数据量达到千万级以上表现依旧是良好，在数据量较大时，有序数据索引定位较为方便，不需要频繁对磁盘进行读写操作，所以可以维持较高的性能
4.大量插入时先关闭约束及索引，完成数据插入，再重新生成索引及约束。
多条 insert或者Load data into table（从文件里载入数据到表里）
建议，先关闭约束及索引，完成数据插入，再重新生成索引及约束。
针对于myisam，步骤：
Alter table table_name disable keys; 禁用索引约束
大量的插入
Alter table table_name enable keys; 启用
针对innodb，步骤：
Drop index, drop constraint 删除索引及约束，要保留主键
Begin transaction|set autocommit=0; 开启事务，不让他自动提交
[数据本身已经按照主键值排序]
大量的插入
Commit;
Add index, add constraint
DELETE优化
delete优化
同时删除过多的数据，会造成CPU利用率过高影响别人对数据库的访问，如果用单一循环，效率更低，折中的方法是分批操作。
delete product where id<1000
delete product where id>=1000 and id<2000
delete 表的结构，属性，索引保持不变
GROUP BY优化
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.
having优化
用Where子句替代having子句，having只会在检索出所有记录之后才对结果集进行过滤。而 where 则是在聚合前刷选记录，如果能通过 where 字句限制记录的数目，那就能减少这方面的开销。
HAVING 中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在 where 字句中。
where 和 having 的区别：where 后面不能使用组函数。
Limit 优化
Limit 的使用，会大大提升无效数据的检索（被跳过），因为是先检索，检索会检索全部，再取得想要的。好的做法是使用条件等过滤方式，将检索到的数据尽可能精确定位到需要的数据上。
示例：
select * from student limit 5,5;
select * from student where id>5 limit 5;
union 优化
MySQL 通过创建并填充临时表的方式来执行 union 查询。除非确实要消除重复的行，否则建议使用 union all。
原因在于如果没有 all 这个关键词，MySQL 会给临时表加上 distinct 选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。
高效： SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 UNION ALL SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST'; 低效： SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 UNION SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';
二、索引的优化
1.在表中建立索引，优先考虑 where、order by 使用到的字段。
2. 尽量使用数字型字段(如性别，男：1 女：2)，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
3. 用 varchar/nvarchar 代替 char/nchar。
尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值(NULL 也包含在内)，都是占用 100 个字符的空间的，如果是 varchar 这样的变长字段， null 不占用空间。
三、建表
首先建表需要参考数据库的三范式
第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。
下表如何修改成第一范式？ 学生编号 学生姓名 联系方式 —————————————————————————— 1001 张三 88888qq.com,1358773633 1002 李四 99999qq.com,2776636882 1003 王五 77777qq.com,7763684943 不满足，没有主键，原子性 学生编号 （PK） 学生姓名 邮箱 电话 —————————————————————————— 1001 张三 88888qq.com 1358773633 1002 李四 99999qq.com 2776636882 1003 王五 77777qq.com 7763684943
第二范式：建立在第一范式的基础上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
多对多：口决：多对多，三张表，关系表，两个外键 学生编号 学生姓名 教师编号 教师姓名 ——————————————————————————— 1001 张三 001 王老师 1002 李四 002 赵老师 1003 王五 003 赵老师 1001 张三 002 赵老师 如何满足第一范式？ （学生编号 + 教师编号）pk 学生姓名 教师姓名 ——————————————————————————— 1001 001 张三 王老师 1002 002 李四 赵老师 1003 001 王五 王老师 1001 002 张三 赵老师 学生编号和教师编号联合做主键，修改之后满足第一范式，但是不满足第二范式。 “张三”依赖1001,王老师依赖001，产生了部分依赖 学生表 学生编号（pk） 学生姓名 —————————————————————————— 1001 张三 1002 李四 1003 王五 教师表 教师编号（pk） 教师姓名 ——————————————————————————— 001 王老师 002 赵老师 学生教师关系表 id（pk） 学生编号（fk） 教师编号（fk） ——————————————————————————— 1 1001 001 2 1002 002 3 1003 001 4 1001 002
第三范式：建立在第二范式的基础上，要求所有非主键字段直接依赖主键，不产生传递依赖。
学生编号(pk) 学生姓名 班级编号 班级名称 1001 张三 01 一年级一班 1002 张四 02 一年级二班 1003 张五 03 一年级三班 1004 张六 03 一年级三班 以上表示一对多的关系。 分析以上满足第一范式？ 满足，有主键 分析以上满足第二范式？ 满足第二范式，主键是单一主键，没有产生部分依赖 分析以上满足第三范式？ 一年级一班依赖01,01依赖1001，产生了传递依赖。 不符合第三范式的要求，产生了数据的冗余。 那么怎么设计一对多的关系呢？**诀窍口诀：一对多，两张表，多的表加外键** 班级表 班级编号 (pk) 班级名称 01 一年级一班 02 一年级二班 03 一年级三班 学生编号(pk) 学生姓名 班级编号(fk) 1001 张三 01 1002 张四 02 1003 张五 03 1004 张六 03
