MySQL笔记07-MySQL细节汇总

• 要保证数据库的效率，要做好以下四个方面的工作：

（1） 数据库（表）设计合理

数据表设计要符合3NF（3范式，规范的模式）， 有时需要适当的逆范式

（2） sql语句优化

（3） 数据库参数配置

（4） 恰当的硬件资源和操作系统

 

• 数据表的三范式：

第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；(只要是关系型数据库都满足1NF)

第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；

第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。

逆范式：在适当的情况下需要字段冗余，例如：相册和图片（内含统计）的功能对应数据库的设计。

 

• SQL优化的一般步骤：

（1）通过show status命令了解各种SQL的执行频率。

（2）定位执行效率较低的SQL语句-（重点select）

（3）通过explain分析低效率的SQL语句的执行情况

（4）确定问题并采取相应的优化措施

 

• SQL语句有几类：

数据定义语言（DDL）：CREATE、DROP、ALTER，用于定义和管理数据对象（库，表，索引，视图），包括数据库、数据表等。例如：CREATE、DROP、ALTER等语句。INSERT UPDATE DELETE

数据操作语言（DML）： 和表中的数据记录，用于操作数据库对象中所包含的数据。例如：INSERT、UPDATE、DELETE语句。SELECT 60%

数据查询语言（DQL）：用于查询数据库对象中所包含的数据，能够进行单表查询、连接查询、嵌套查询，以及集合查询等各种复杂程度不同的数据库查询，并将数据返回到客户机中显示。例如：SELECT语句。

数据控制语言（DCL）：是用来管理数据库的语言，包含管理权限及数据更改。例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句。

 

• 【show status命令】

SHOW STATUS LIKE 'COM%' 相当于： SHOW SESSION STATUS LIKE 'COM%' //显示当前控制台的情况

SHOW GLOBAL STATUS LIKE 'COM%' //显示数据库从启动到 查询的次数

重点注意：Com_select,Com_insert,Com_update,Com_delete通过这几个参数，可以容易地了解到当前数据库的应用是以插入更新为主还是以查询操作为主，以及各类的SQL大致的执行比例是多少。

可以详细查询具体的某个（举例）：

SHOW STATUS LIKE 'COM_select'

还有几个常用的参数便于用户了解数据库的基本情况。 Connections：试图连接MySQL服务器的次数：SHOW STATUS LIKE 'Connections';

Uptime：服务器工作的时间（单位秒）：SHOW STATUS LIKE 'Uptime';

Slow_queries：慢查询的次数 (默认是10)：SHOW STATUS LIKE 'Slow_queries';

（补充）慢查询 (在默认情况下是10 )，显示查看慢查询的情况 SHOW VARIABLES LIKE 'long_query_time'

设置慢查询的语句：SET long_query_time=2;

然后再执行刚才的命令：

 

• 【explain分析问题】

explain SQL语句，会产生如下信息：

select_type:表示查询的类型。

table:输出结果集的表

type:表示表的连接类型

possible_keys:表示查询时，可能使用的索引

key:表示实际使用的索引

key_len:索引字段的长度

rows:扫描的行数

Extra:执行情况的描述和说明

• 【关于索引】

（1）分析：在什么列上添加索引比较合适

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

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

3. 内容频繁变化，不合适加索引

（2）说明：索引的种类

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

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

③ index （普通索引）

④全文索引 (FULLTEXT)（只有MyISAM存储引擎支持）

（3）对索引的相关操作：

• 创建索引

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

方法一：create [unique|FULLTEXT] index 索引名 on 表名 (列名...)

方法二：alter table 表名 add index 索引名 (列名...)

如果要创建主键索引：

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

• 删除索引

drop index 索引名 on 表名

alter table 表名 drop index索引名;

[删除主键] alter table 表名 drop primary key

• 显示索引

show index(es) from 表名

show keys from 表名

desc 表名

（4）哪些列上适合添加索引

较频繁的作为查询条件字段应该创建索引；

如：select * from emp where empno = 1

唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；

如：select * from emp where sex = '男'

更新非常频繁的字段不适合创建索引；

如：select * from emp where logincount = 1

不会出现在WHERE子句中字段不该创建索引

（5）查看索引的使用情况：SHOW STATUS LIKE 'Handler_read%'

handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。

handler_read_rnd_next:这个值越高，说明查询低效。

 

【MyISAM 和 Innodb区别】

① MyISAM 不支持外键, Innodb支持

② MyISAM 不支持事务, Innodb支持

③ 对数据信息的存储处理方式不同。如果存储引擎是MyISAM的，则创建一张表，对应三个文件：db.frm（数据结构）db.MYD（mydatabase，数据库文件）db.MYI（myindex，索引文件）；如果是Innodb则只有一张文件 *.frm,数据存放到ibdata1。

④ 对于 MyISAM 数据库，需要定时清理：optimize table 表名.

总结：MyISAM:默认的MySQL存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高。其优势是访问的速度快。InnoDB：提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM，写的处理效率差一些并且会占用更多的磁盘空间。

 

【常见的SQL优化手法】

（1） 使用order by null 禁用排序，比如 select * from dept group by ename order by null

实例说明：EXPLAIN SELECT * FROM dept GROUP BY dname

禁用排序后：EXPLAIN SELECT * FROM dept GROUP BY dname ORDER BY NULL

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

例如：1000000.32：

create table ts1(t1 float(10,2));插入数值后查询结果是：1000000.31

create table ts2(t1 decimal(10,2)); 插入数值后查询结果是：1000000.32

 

[补充]PHP中，int是4个字节，所以int的最大值：2的(4 * 8 - 1 = 31) 次方 - 1 = 2147483647

所以，当日期村委时间戳int型的时候，最大显示2038年1月19日：date(‘Y-m-d’, 2147483647);

 

【对表进行水平划分和垂直划分】：

水平划分就是：大数据存储，分别存储，可以取一个id%10，得出0-9，然后分别存在tb0、tb1、tb2…tb9中；

垂直划分就是：记录数并不多，但是字段却很长，就把他们存在不同的表中。比如会员登录表、会员信息表。

 

【数据库参数配置】

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

　　innodb_additional_mem_pool_size = 64M

　　innodb_buffer_pool_size =1G

• 对于myisam，需要调整key_buffer_size

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

 

[补充]1.在dos命令下操作MySQL，给SQL语句后面加上\G 可以让结果竖排显示。

2.不断翻倍的添加数据的方法：insert into tb select * from tb;