MySQL数据库的优化技术二

纵论

对mysql优化时一个综合性的技术，主要包括 

1. 表的设计合理化(符合3NF)
2. 添加适当索引(index)  [ 四种: 普通索引、主键索引、唯一索引unique、全文索引 ]
3. 分表技术( 水平分割、垂直分割 ) 水平分割根据一个标准重复定义几个字段值相同，表名称不同的表，表的结构相同
4. 读( 将查询query分配到多个数据库中，子数据库)；写（主数据库用来写）[ 写: update/delete/add ]分离
5. 存储过程 [ 模块化编程，可以提高速度 ]，存储过程其实是一个数据对象，从数据库的三层结构来说，分为 客户端java程序---dbms（数据库管理系统）通常说的数据库指的是数据库管理系统---db（本质就是一个文件），java程序一个sql语句发送到dbms的时候，dbms会将sql语句进行编译，然后执行，执行过后将数据暂时缓存一下，第二次操作的时候，数据就会快。Sql语句最致命的问题就是，sql编译很耗费时间，这时候有人就提出了将经常使用的语句在数据库里面编码，让它形成一个二进制文件，到时候直接调用，这样提高速度，存储过程，最常见的就是，分页存储过程，配合触发器的存储过程，存储过程是一个已经编译好，这时候就会直接执行，存储过程有利于模块化开发。存储过程的致命缺点：移植性不好
6. 对mysql配置优化 [ 配置最大并发数my.ini  max_connections=100,一般网站调整到1000左右即可，如果更大会把内存撑破； 调整缓存大小 ]
7. mysql服务器硬件升级，服务器内存的提高，优化到位了，硬件不够要向上反馈提高硬件性能
8. 定时的去清除不需要的数据,定时进行碎片整理( MyISAM引擎要定时清理，不然会越来越大 ) 

 表的范式优化

什么样的表才是符合3NF (范式)，最高级别是6范式

表的范式，是首先符合1NF, 才能满足2NF , 进一步满足3NF

1NF: 即表的列的具有原子性,不可再分解，即列的信息，不能分解,（也就是一个字段值是能够明确地表示一个含义就满足1范式） 只要数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF

☞ 数据库的分类
关系型数据库: mysql/oracle/db2/informix/sysbase/sql server
非关系型数据库: (特点: 面向对象或者集合)，关系型数据库存在结构上的缺陷，所以提出非关系数据库，设计理念变化了，比如没有主外键了
NoSql数据库: MongoDB(特点是面向文档)，介于关系型和非关系型之间

2NF: 表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现，
真正的主键一般不包含业务逻辑，不包含业务逻辑,不需要修改,因此数据比较稳定。是自增长的

3NF: 即表中不要有冗余数据, 就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放. 比如下面的设计就是不满足3NF:

 反3NF : 但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。
案例 ： 

 
 Sql语句本身的优化

① 运行状态查询

如何从一个大项目中，迅速的定位执行速度慢的语句

首先我们了解mysql数据库的一些运行状态如何查询(比如想知道当前mysql运行的时间/一共执行了多少次select/update/delete.. / 当前连接)

• show status
  • show status like ‘uptime’ ：查看当前数据库执行了多长时间，开启了多长时间
  • show  stauts like ‘com_select’  show stauts like ‘com_insert’ ...类推 update  delete查看dml语句的执行次数

☞ show [session|global] status like .... 如果你不写  [session|global] 默认是session 会话，指取出当前窗口的执行，如果你想看所有，从mysql 启动到现在，则应该 global

show status like ‘connections’：客户端随机分配一个端口与本机上的数据库的3306端口对应；

netstat –anb可以将进程号显示出来 netstat –an显示连接到本机数据库的具体信息

//显示慢查询次数
show status like ‘slow_queries’;
② 如何去定位慢查询

这里用一个实际案例来说明：

构建一个大表(400 万)-> 存储过程构建

默认情况下，mysql认为10秒才是一个慢查询

Select语句要查询10秒的情况很难出现，如果这个表的记录没有达到4千万，根本没有机会出现10秒，但是4千万都不一定能够出现10秒的慢查询，4亿倒是可以出现10秒的慢查询，所以合理的做法是修改慢查询的默认时间.

③ 修改mysql的慢查询

上来将慢查询时间设置为1秒，让他跑一天，去日志文件中查看哪些语句的查询时间超过1秒，这时候叫程序员优化，要么表结构不合理，要么是索引没有建立，要么就是程序有问题。

show variables like ‘long_query_time’ ;  //可以显示当前慢查询时间

set long_query_time=1 ; //可以修改慢查询时间

④ 模拟数据生成慢查询

构建大表->大表中记录有要求, 记录是不同才有用，否则测试效果和真实的相差大

CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,  /*编号*/
dname VARCHAR(20)  NOT NULL  DEFAULT "", /*名称*/
loc VARCHAR(13) NOT NULL DEFAULT "" /*地点*/
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;


CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;


CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

测试数据

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

为了存储过程能够正常执行，我们需要把命令执行结束符修改

delimiter $$

先创建一个随机字符串的函数，用于数据插入

create function rand_string(n INT) 
returns varchar(255) #该函数会返回一个字符串
begin 
#chars_str定义一个变量 chars_str,类型是 varchar(100),默认值'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare chars_str varchar(100) default
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare return_str varchar(255) default '';
 declare i int default 0;
 while i < n do 
   set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
   set i = i + 1;
   end while;
  return return_str;
  end $$

再创建一个存储过程，用于数据写入

create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0; 
#set autocommit =0 把autocommit设置成0
 set autocommit = 0;  
 repeat
 set i = i + 1;
 insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
  until i = max_num
 end repeat;
   commit;
 end $$

# 调用刚刚写好的函数, 1800000条记录,从100001号开始，配合存储过程，生成库表数据

call insert_emp(100001,4000000);

这时我们如果出现一条语句执行时间超过1秒中，就会统计到.

如何把慢查询的sql记录到我们的一个日志中

在默认情况下，我们的mysql不会记录慢查询，需要在启动mysql时候，指定记录慢查询才可以

bin\mysqld.exe - -safe-mode  - -slow-query-log [mysql5.5 可以在my.ini指定]

bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]

Linux下

MySQL配置文件my.cnf中增加 log-slow-queries = /var/lib/mysql/slowquery.log指定日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log，然后指定慢查询的默认时间long_query_time = 2;   log-queries-not-using-indexes(log下来没有使用索引的query，可以根据情况决定是否开启) log-long-format（如果设置了，所有没有使用索引的查询也将被记录）

Windows下

在my.ini的myslqd添加如下语句：log-slow-queries = E:/web/mysql/log/mysql/slowqury.log, long_query_time=1;

先关闭mysql,再启动, 如果启用了慢查询日志，默认把这个文件放在

my.ini 文件中记录的位置

# Path to the database root

datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"

测试,可以看到在日志中就记录下我们的mysql慢sql语句

⑤ 如何优化

通过 explain 语句可以分析，mysql如何执行你的sql语句, 这个工具的使用放一下，一会说，需要下面优化措施配合，效果会更明显

 索引优化建议

四种索引 (主键索引/唯一索引/全文索引/普通索引)

添加索引

1.1 主键索引

当一张表，把某个列设为主键的时候，则该列就是主键索引 

create table aaa
(id int unsigned primary key auto_increment ,
name varchar(32) not null defaul ‘’);
id unsigned primary key auto_increment

 这是id 列就是主键索引.

Unsigned无符号有符号主要体现在范围上，-128----+127之间

如果你创建表时，没有指定主键索引，也可以在创建表后，在添加, 指令:

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

举例:

create table bbb (id int , name varchar(32) not null default ‘’);

alter table bbb add primary key (id);

论述：

表的引擎不一样，那么存储结构就会不一样，如果是innodb，那么只会记录表的结构，表的数据就会在这层目录的外面，如果是myisam，那么就会有三张表，其中.frm是表的结构，.MYD是表的数据，最大；.MYI表示索引

不创建索引的时候，假设要查询emp表empno字段为4的记录，那么数据库他都会一条记录一条记录去匹配，匹配到了，如果后面还有其他记录，他还会继续匹配，直到全部检索完毕，这就是不建立索引的时候，查询效率慢的原因，因为数据库不敢保证，字段为的4的记录就一条，所以要全表检索。

数学、物理、电子工程、语音识别系统要靠电子系的人来做，搜索引擎要靠做数学的来做，计算机最后要做系统集成，或者架构，或者网络安全

那么创建索引的时候为什么查询效率就会变高呢，首先用到了一种二叉树的算法，以及hash算法

创建索引过程中会先建立一个索引文件.MYI文件，根据字段值扫描查询的字段有多少数据，根据数量构建一颗二叉树，找到数量的中间一个字段值M，记录一下磁盘位置，然后寻找比M这个中间值小的BF树，寻找BF中的中间那个位置，记录磁盘位置，这个时候要进行向上或者向下取整，所以一次类推，最后会建立起一颗完整的二叉树。每一个树的节点都会有一个磁盘位置与他对应。

而有了索引之后，那么他就会根据这个创建起来的树进行查询，比如M是6，查询字段是4，4<6,就会在6的左边树中查询，一次类推。

那么这个二叉树创建出来的索引可以根据查询次数查询多少数据量呢，假设检索10次，那么就可以检索2的10次方条数据。比如检索30次，就可以扫描10亿个数量的数据。

所以数据库文件进行迁移的时候要注意索引的磁盘位置变化，不然会导致检索速度变慢。

迁移表的时候，经常将索引先禁用，alter table bbb disable keys，迁移好之后再启动索引，alter table bbb enable keys

1.2 普通索引

一般来说，普通索引的创建，是先创建表，然后在创建普通索引

比如:

create table ccc(
id int unsigned,
name varchar(32)
)

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

create index myind on dept (dname,loc);
alter table dept add index myind (dname,loc);
创建索引的两种方式

1.3 创建全文索引

全文索引，主要是针对对文件，文本的检索, 比如文章, 全文索引针对MyISAM有用.

创建 ：

CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body)
     )engine=myisam charset utf8;

INSERT INTO articles (title,body) VALUES
     ('MySQL Tutorial','DBMS stands for DataBase ...'),
     ('How To Use MySQL Well','After you went through a ...'),
     ('Optimizing MySQL','In this tutorial we will show ...'),
     ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
     ('MySQL vs. YourSQL','In the following database comparison ...'),
     ('MySQL Security','When configured properly, MySQL ...');

如何使用全文索引:

错误用法:

select * from articles where body like ‘%mysql%’; 【不会使用到全文索引】

证明:

explain  select * from articles where body like ‘%mysql%’

正确的用法是:

select * from articles where match(title,body) against(‘database’); 【可以】

☞ 说明:

1. 在mysql中fulltext 索引只针对 myisam生效
2. mysql自己提供的fulltext针对英文生效->sphinx (coreseek) 技术处理中文
3. 使用方法是 match(字段名..) against(‘关键字’)
4. 全文索引一个 叫 停止词,  因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符，就不会创建，这些词，称为停止词.

1.4 唯一索引 

①当表的某列被指定为unique约束时，这列就是一个唯一索引

create table ddd(id int primary key auto_increment , name varchar(32) unique);

这时, name 列就是一个唯一索引.

unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复.

主键字段，不能为NULL,也不能重复.

②在创建表后，再去创建唯一索引

create table eee(id int primary key auto_increment, name varchar(32));

create unique index 索引名  on 表名 (列表..);

create unique index my_uni on eee (name);

 查询索引

desc 表名 【该方法的缺点是：　不能够显示索引名.】

show index(es) from 表名

show keys from 表名

删除索引 

alter table 表名 drop index 索引名;

如果删除主键索引。

alter table 表名 drop primary key

修改索引

先删除，再重新创建

☞  索引原理

为什么创建索引后，速度就会变快? 原理示意图:

索引使用的注意事项

索引的代价:

1. 占用磁盘空间
2. 对dml操作有影响，变慢

在哪些列上适合添加索引?

• 较频繁的作为查询条件字段应该创建索引
  • select * from emp where empno = 1
• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
  • select * from emp where sex ='男
• 更新非常频繁的字段不适合创建索引
  • select * from emp where logincount = 1
• 不会出现在WHERE子句中字段不该创建索

总结

满足以下条件的字段，才应该创建索引.

a: 肯定在where条经常使用

b: 该字段的内容不是唯一的几个值(sex)

c: 字段内容不是频繁变化.

索引使用技巧

(1) 

把dept表中，我增加几个部门:

alter table dept add index my_ind (dname,loc); //  dname 左边的列,loc就是右边的列

说明，如果我们的表中有复合索引(索引作用在多列上)， 此时我们注意:

对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。

explain select * from dept where loc='aaa'\G 

就不会使用到索引

(2) 

越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快

(3)

简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。
(4)

尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。
(5)

索引不会包含有NULL值的列

例如:
修改表expert_info中的字段birth,允许其为空
>alter table expert_info change birth birth varchar(20) null;

(6)

对于使用like的查询，查询如果是  ‘%aaa’ 不会使用到索引

‘aaa%’ 会使用到索引

比如: explain select * from dept where dname like '%aaa'\G

不能使用索引，即，在like查询时，关键的 ‘关键字’ , 最前面，不能使用 % 或者 _这样的字符

如果一定要前面有变化的值，则考虑使用 全文索引->sphinx

(7)

如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引, 我们建议大家尽量避免使用or 关键字

select * from dept where dname=’xxx’ or loc=’xx’ or deptno=45

(8)

如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’), 也就是，如果列是字符串类型，就一定要用 ‘’ 把他包括起来.

(9)

如果mysql估计使用全表扫描要比使用索引快，则不使用索引

(10)

现在回头说说上述的explain的使用

explain 可以帮助我们在不真正执行某个sql语句时，就执行mysql怎样执行，这样利用我们去分析sql指令.如下图

如何查看索引使用的情况

show status like ‘Handler_read%’;

大家可以注意：

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

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

sql语句的小技巧

1、在使用group by 分组查询是，默认分组后，还会排序，可能会降低速度.

比如:

在group by 后面增加 order by null 就可以防止排序.提高速度

2、有些情况下，可以使用连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表。

select * from dept, emp where dept.deptno=emp.deptno; [简单处理方式]

select * from dept left join emp on dept.deptno=emp.deptno;  [左外连接，更ok!]，并且推荐左连接

来代替右连接，兼容性会好一些