数据库优化

数据库优化的目的是什么？

避免错误页面的发生

    1.请求数据库服务器超时，会发生数据库的内部错误

    2.慢sql，造成页面无法加载

    3.阻塞，数据无法提交

增加数据库的稳定性

    很多都是因为由于低效的查询引起的

优化用户体验

    流畅页面的访问速度

    良好的网站功能体验

数据库可以从哪几个方面优化呢？

max

io就相当高

建索引

count（*） 全部行

count（id）不包括null

select count (code='18') as 'good',count(code='19')as 'hahah' from study

哪些查询需要优化：

mysql中有个慢查询日志进行sql监控

目的：

数据库连接超时

慢查询：出现页面无法加载

阻塞：内部锁的原因，轻则影响性能，还会影响业务，有锁超时，超过时间就会被回滚

优化：

sql及索引优化：结构良好的sql，有效适量的索引

表结构设计：减少冗余 

系统配置：tcp文件数/打开文件数限制/安全性限制，没查询一个表就会打开一个文件，打开文件数

硬件：cpu。更快的io 内存越大可能越好，cpu不一定越多越好，对核数也有限制；io级别的选择，io并不能减少锁的机制，硬件是成本最高效果最差的

数据库优化，SQL及索引优化效果最高，成本最低。

选择合适类型做数据库索引

索引并不是越多越好，作为主键的字段不要再做索引。

SQL索引优化

可优化的方面： 

SQL及索引优化：

测试

打开文件数的限制，可以使用 ulimit-a 查看目录的各位限制，可以修改 /etc/ security/ limits.conf文件，增加以下内容以修改打开文件数量的限制 soft nofile 65535 hard nofile 65535

除此之外最好在 MySQL 服务器上关闭 iptables, selinux 等防火墙软件。

数据库是基于操作系统的，目前大多数 MySQL 都是安装在 Linux 系统之上，所以对于操作系统的一些参数配置也会影响到 MySQL 的性能，下面就列出一些常到的系统配置。

网络方面的配置，要修改 /etc/ sysctl.conf 文件

#增加 tcp 支持的队列数

net.ipv4.tcp_max_syn_backlog = 65535

#减少断开连接时，资源回收

net.ipv4.tcp_max_tw_buckets = 8000

net.ipv4.tcp_tw_reuse = 1

net.ipv4.tcp_tw_recycle = 1

net.ipv4.tcp_fin_timeout = 10

常用的水平拆分方法为：

1. 对 customer id 进行 hash 运算，如果要拆分成 5 个表则使用 mod(customer_id,5) 取出 0-4 个值

2. 针对不同的 hashID 把数据存到不同的表中。

挑战：

1. 跨分区表进行数据查询

2. 统计及后台报表操作

表的水平拆要是为了解决单表的数据量过大的问题，水平插分的表每一个表的结构都是完成一致的。以下面的 payment 表为例

CREATE TABLE `film` (
  `film_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `release_year` YEAR(4) DEFAULT NULL,
  `language_id` TINYINT(3) UNSIGNED NOT NULL,
  `original_language_id` TINYINT(3) UNSIGNED DEFAULT NULL,
  `rental_duration` TINYINT(3) UNSIGNED NOT NULL DEFAULT'3',
  `rental_rate` DECIMAL(4,2) NOT NULL DEFAULT'4.99',
  `length` SMALLINT(5) UNSIGNED DEFAULT NULL,
  `replacement_cost` DECIMAL(5,2) NOT NULL DEFAULT '19.99',
  `rating` VARCHAR(5) DEFAULT'G',
  `special_features` VARCHAR (10) DEFAULT NULL,
  `last_update` TIMESTAMP, PRIMARY KEY(`film_id`))

CREATE TABLE file_text (
  `film_id` SMALLINT (5) UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT
  primary key(film_id)
) engine = innodb

所谓的垂直拆分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。通常垂直拆分可以按以下原则进行：

1. 把不常用的字段单独存放到一个表中。

2. 把大字段独立存放到一个表中。

3. 把经常一起使用的字段放到一起。
   

思考：反范式化后再查询订单信息

SELECT a.用户名，a.电话，a.地址，a.订单ID, a.订单价格 
FROM `订单表` a

对下面的表进行反范式化后

思考：如何查询订单信息？

SELECT b.用户名，b.电话，b.地址，a.订单ID, SUM(c.商品价价*c.商品数量）as 订单价格 
FROM `订单表` a
JOIN `用户表` b ON a.用户ID=b.用户ID 
JOIN `订单商品表` c ON c.订单ID=b.订单ID 
GROUP BY b.用户名，b.电话，b.地址，a.订单ID

反范式化是指为了查询效率的考虑把原本符合第三范式的表适当的增加冗余，以达到优化查询效率的目的，反范式化是一种以空间来换取时间的操作。

表的范式化和反范式化

不符合第三范式要求的表存在下列问题：

1. 数据余：（分类，分类描述）对于每一个商品都会进行记录

2. 数据的插入异常

3. 数据的更新异常

4. 数据的删除异常

范式化是指数据库设计的规范，目前说到范式化一般是指第三设计范式，也就是要求数据表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式

存在以下传递函数依赖关系：（商品名称）->（分类）->（分类描述）

也就是说存在非关键字段“分类描述”对关键字段“商品名称”的传递函数依赖。

使用 bigint 来 IP 地址，利用 INET_ATON(), INET_NTOA() 两个函数来进行转换 

CREATE TABLE sessions(id INT AUTO_INCREMENT NOT NULL, ipaddress BIGINT, PRIMARY KEY(id));

INSERT INTO sessions(ipaddress) VALUES (INET_ATON('192.168.0.1'));

SELECT INET_NTOA(ipaddress) FROM sessions;

使用 int 来存储日期时间，利用 FROM_UNIXTIMEO(), UNIX_ TIMESTAMP() 两个函数来进行转换

CREATE TABLE test(id INT AUTO INCREMENT NOT NULL
, timestr INT, PRIMARY KEY (id);
INSERT INTO test(timestr) VALUES (UNIX_TIMESTAMP('2014-06-01 13:12:00');

SELECT FROM_UNIXTIME(timestr) FROM test;

数据类型的选择，重点在于合适二字，如何确定选择的数据类型是否合适？

1. 使用可以存下你的数据的最小的数据类型。

2. 使用简单的数据类型。Int 要比 varchar 类型在 mysql5 处理上简单。

3. 尽可能的使用 not null 定义字段。

4. 尽量少用 text 类型，非用不可时最好考虑分表。

删除不用索引1234

目前 MySQL 中还没有记录索引的使用情况，但是在 PerconMySQL 和 MariaDB 中可以通过 INDEX_STATISTICS 表来查看那些索引未使用，但在 MySQL 中目前只能通过慢查日志配合 pt-index-usage 工具来进行索引使用情况的分析。

pt-index-usage \
  -uroot -p' '\
  mysql-slow.log

查找重复及冗余索引

使用 pt- duplicate-key- checker 工具检查重复及冗余索引

pt-duplicate-key-checker \ 
-uroot \ 
-p " \ 
-h 127.0.0.1