MYSQL8.0优化方案集锦

数据库(表)设计合理

1.1我们的表设计尽量要符合3NF 3范式(规范的模式) , 有时因为需求的原因我们需要适当的逆范式

1.2 主键最好用单一字段且是没有业务语义的，尽量不用联合主键，尽量将数据类型选择为数值型，因为它检索速度快，联合主键可以出现在中间表中，该中间表没有其它表可以被引用,注意当没有表引用某个表的时候，是通过一些数据运行时生成出来的，比如两表之间的中间表。通常是多个字段既是主键又是外键（联合主外键）

1.3 关于冗余字段的问题，根据需求的具体情况来决定是否加入，冗余规则 1-多应该尽可能地把逆范式的内容放在1这一边

1.4 最好加入外键约束（在开发阶段不要设置外键约束），在运行阶段加入外键约束（但如果是高并发的，也可考虑画出外键但不真正建立外键），为了查询性能的提升应该在外键上建立索引

1.5 如果是开发通用性产品涉及数据库移植则尽量不要使用数据库特性除非万不得已

1.6 数据量非常大且根据某些字段频繁的查询则需要建立索引

sql语句的优化(索引，常用小技巧.)

数据的配置(缓存设大)

适当硬件配置和操作系统 (读写分离.)

Sybase PowerDesigner

Check Model检查模型，如果有问题可以用自动修复功能，如果没有问题导出SQL脚本

去掉双引号，在Model Option->Naming Convention

Name下的Code选项卡下，将Character case 选择Uppercase（变大写）

去掉外键约束，在Database->Generation Database ->Options->Table & Column 去掉Foreign key的勾选

数据的3NF

1NF :就是具有原子性，不可分割.(只要使用的是关系性数据库，就自动符合)

2NF: 在满足1NF 的基础上，我们考虑是否满足2NF: 只要表的记录满足唯一性,也是说,你的同一张表，不可能出现完全相同的记录, 一般说我们在 表中设计一个主键即可.

3NF: 在满足2NF 的基础上，我们考虑是否满足3NF：即我们的字段信息可以通过关联的关系，派生即可.(通常我们通过外键来处理)

逆范式: 为什么需呀逆范式:

(相册的功能对应数据库的设计)

适当的逆范式.

不适当的逆范式

sql语句的优化

面试题 ：sql语句有几类

ddl (数据定义语言) [create alter drop]

dml(数据操作语言)[insert delete upate ]

select

dtl(数据事务语句) [commit rollback savepoint]

dcl(数据控制语句) [grant revoke]

show status命令

该命令可以显示你的mysql数据库的当前状态.我们主要关心的是 “com”开头的指令

show status like ‘Com%’ <=> show session status like ‘Com%’ //显示当前控制台的情况

show global status like ‘Com%’ ; //显示数据库从启动到 查询的次数

显示连接数据库次数

show status like 'Connections';

这里我们优化的重点是在 慢查询. (在默认情况下是10 ) mysql5.5.19

显示查看慢查询的情况

show variables like ‘long_query_time’

为了教学，我们搞一个海量表(mysql存储过程)

目的，就是看看怎样处理，在海量表中，查询的速度很快!

select * from emp where empno=123456;

需求：如何在一个项目中，找到慢查询的select , mysql数据库支持把慢查询语句，记录到日志中，程序员分析. (但是注意，默认情况下不启动.)

步骤:

要这样启动mysql

进入到 mysql安装目录

2. 启动 xx>bin\mysqld.exe –slow-query-log 这点注意

测试 ,比如我们把

select * from emp where empno=34678 ；

用了1.5秒，我现在优化.

快速体验: 在emp表的 empno建立索引.

alter table emp add primary key(empno);

//删除主键索引

alter table emp drop primary key

然后，再查速度变快.

索引的原理

介绍一款非常重要工具 explain, 这个分析工具可以对 sql语句进行分析,可以预测你的sql执行的效率.

他的基本用法是:

explain sql语句\G

//根据返回的信息，我们可知,该sql语句是否使用索引，从多少记录中取出,可以看到排序的方式.

在什么列上添加索引比较合适

在经常查询的列上加索引.

列的数据，内容就只有少数几个值,不太适合加索引.

内容频繁变化，不合适加索引即使它会出现在WHERE，HAVING，ORDER BY这些子句上面，是否要加索引也要慎重。

索引的种类

主键索引 (把某列设为主键，则就是主键索引)

唯一索引(unique) （即该列具有唯一性，同时又是索引）

index （普通索引）

全文索引(FULLTEXT)

select * from article where content like ‘%李连杰%’;

hello, i am a boy

你好，我是一个男孩 =>中文 sphinx

复合索引(多列和在一起)

create index myind on 表名 (列1,列2);

联合索引经验总结

如果某个列只有一个值，意味着它是常量列。它如果不出现在WHERE子句中，且在ORDER BY或GROUP BY子句中要么不出现（主要是ORDER BY），要么出现的顺序不会对造成using filesort降低查询性能

Using filesort MySQL有两种方式可以生成有序的结果，通过排序操作或者使用索引，当Extra中出现了Using filesort 说明MySQL使用了后者，但注意虽然叫filesort但并不是说明就是用了文件来进行排序，只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快，所以一般这时也要考虑优化查询了。

Using temporary说明使用了临时表，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。

如何创建索引

如果创建unique / 普通/fulltext 索引

1. create [unique|FULLTEXT] index 索引名 on 表名 (列名...)

2. alter table 表名 add index 索引名 (列名...)

//如果要添加主键索引

alter table 表名 add primary key (列...)

删除索引

drop index 索引名 on 表名

alter table 表名 drop index index_name;

alter table 表名 drop primary key

显示索引

show index(es) from 表名

show keys from 表名

desc 表名

如何查询某表的索引

show indexes from 表名

使用索引的注意事项

查询要使用索引最重要的条件是查询条件中需要使用索引。

下列几种情况下有可能使用到索引：
1，对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。
2，对于使用like的查询，查询如果是 ‘%aaa’ 不会使用到索引

‘aaa%’ 会使用到索引。

下列的表将不使用索引：
1，如果条件中有or，即使其中有条件带索引也不会使用。
2，对于多列索引，不是使用的第一部分，则不会使用索引。
3，like查询是以%开头
4，如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。
5，如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

如何检测你的索引是否有效

结论: Handler_read_key 越大越少

Handler_read_rnd_next 越小越好

fdisk

find

MyISAM 和 Innodb区别是什么

MyISAM 不支持外键, Innodb支持

MyISAM 不支持事务,不支持外键.

对数据信息的存储处理方式不同.（如果存储引擎是MyISAM的，则创建一张表，对于三个文件..,如果是Innodb则只有一张文件 *.frm,数据存放到ibdata1）

对于 MyISAM 数据库，需要定时清理

optimize table 表名

MYISAM有三个文件frm(表结构)、myd(数据)、myi(索引)

INNODB只有一个文件frm

常见的sql优化手法

使用order by null 禁用排序

引入的原因group by（它默认会排序）分组后如果不加order by null它会使用到Using filesort

比如 select * from dept group by ename order by null

在精度要求高的应用中，建议使用定点数(decimal)来存储数值，以保证结果的准确性

1000000.32 万

create table sal(t1 float(10,2));

create table sal2(t1 decimal(10,2));

问?在php中 ,int 如果是一个有符号数，最大值. int- 4*8=32 2 31 -1

数据库参数配置

最重要的参数就是内存，我们主要用的innodb引擎，所以下面两个参数调的很大

　　innodb_additional_mem_pool_size = 64M

　　innodb_buffer_pool_size =1G

对于myisam，需要调整key_buffer_size

当然调整参数还是要看状态，用show status语句可以看到当前状态，以决定改调整哪些参数

表的水平划分

垂直分割表

如果你的数据库的存储引擎是MyISAM的，则当创建一个表，后三个文件. *.frm 记录表结构. *.myd 数据 *.myi 这个是索引.

mysql5.5.19的版本，他的数据库文件，默认放在 （看 my.ini文件中的配置.）

读写分离

uml 课程.(uml 架构.)=>效果，做一个项目后再说.