Mysql优化学习一 SQL语句优化

MySql优化学习笔记

为什么需要优化数据库

1、 避免出现页面访问错误

• 由数据库连接timeout产生页面5xx错误。
• 由慢查询导致的无法加载
• 由阻塞造成的无法提交

2、增加数据库稳定性

• 低效率查询可能会导致数据库宕机

3、优化用户体验

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

Sql的语句优化

如何发现有问题的Sql？
使用慢查询日志对有问题的SQL进行监控

/*查看数据库是否开启了慢查询日志*/
show variables like 'slow_query_log';
/*指定慢查询日志文件位置在哪*/
set global slow_query_log_file = 'home/mysql/sql_log/mysql-slow.log';
/*把没使用索引的sql记录到查询日志*/
set global log_queries_not_using_indexes = on;
/*把查询时间大于1秒的sql，记录到查询日志中*/
set global long_query_time = 1; 

操作步骤以及效果
1

show variables like 'slow_query_log';/*1、查看数据库是否开启了慢查询日志*/

off表示没有开启

2

show variables like '%log%';    /*执行该语句可以看到所有和log有关的variable的Value*/

可以看到log_queries_not_using_indexs也是off状态。
3

set global log_queries_not_using_indexes = on;/*把没使用索引的sql记录到查询日志*/

4

show variables  like 'long_query_time'/*查看sql执行可允许的时间*/ 

可以看到我们的sql达到10s就算是慢查询了，需要被记录到日志中

5

set global long_query_time = 0;
/*为了产生日志我把超过0秒的时间定义为慢查询,这里要先关闭连接，
再打开连接才能看到是已经修改了，我再次以为执行show语句，看到还是10，还以为没改过来呢*/

6

set global slow_query_log = on;    /*开启慢查询日志记录*/

把数据慢查询日记开启后我们可以选择一个数据库进行查询，测试了。这里为了方便我使用mysql官方给我们提供的案例数据库Sakila，他是一个模拟dvd租赁业务场景的数据库（需要学习数据库设计的可以多看看，从官方那获得灵感），关于安装说明官网有详细介绍：

MySQL :: Sakila Sample Database :: 4 Installationhttps://dev.mysql.com/doc/sakila/en/sakila-installation.html下载链接：

 http://downloads.mysql.com/docs/sakila-db.zip 

7

/*导入数据库，最好在用管理员命令行下登录mysql -uroot -p 执行，
在Navicat上执行会报错*/
SOURCE D:/Programming/MyCode/sakila-db/sakila-schema.sql;
SOURCE D:/Programming/MyCode/sakila-db/sakila-data.sql;

8

use sakila;
show tables;

9

 成功看到数据库里有23张表导入成功！

10

/*查询store表的前10行*/
select * from store limit 10;

可以看到执行时间为2s 

11

/*查询慢查询日志所在位置*/
show variables like 'slow_query_log_file';

 12打开日志文件

可以看到步骤10对store表的前10条查询，符合我们对慢查询的定义被记录下来了。 

慢查询日志包含的内容

# Time: 211111 10:22:08        执行时间

# User@Host: root[root] @ localhost [127.0.0.1]        主机信息

# Query_time: 0.000992  Lock_time: 0.000992 Rows_sent: 2  Rows_examined: 2     执行信息   

SET timestamp=1636597328;        执行的时间戳

select * from store limit 10;        执行内容

慢查询日志分析工具

在上面可以看到，我们只是简单的执行几条sql已经产生了如此多的慢查询日志，相识工作中我们一台服务器就可能生成几个G的慢查询日志，所以咱程序员当然需要借助慢查询日志分析工具才行。

1、Mysqldumpslow

这是mysql自带的日志分析工具。

 使用             

perl mysqldumpslow.pl --help

查看它有哪些参数：

window下麻烦点要perl才能执行。

 如果是CentOS上直接

mysqldumpslow -h

 

 查询前三条慢查询日志

perl mysqldumpslow.pl -t 3 D:\Programming\mysql-5.5.60-winx64\data\DESKTOP-EFN0R2Q-slow.log | more

 

2、下载pt-query-digest 并执行

curl -o pt-query-digest https://www.percona.com/get/pt-query-digest

 perl pt-query-digest --help

 

 由于cmd不好看我下了个cmder。

执行

 perl pt-query-digest D:\Programming\mysql-5.5.60-winx64\data\DESKTOP-EFN0R2Q-slow.log | more

对日志进行分析，可以看到它比mysqldumpslow的信息详细很多 

 它的信息有

日志的时间范围，总的sql数量（18） ，不同的sql数量(4)

# Overall: 18 total, 4 unique, 0.00 QPS, 0.00x concurrency _______________
# Time range: 2021-11-11 08:31:14 to 11:52:05

响应时间占比（Response time），calls(sql执行次数)

# Profile
# Rank Query ID                            Response time Calls R/Call V/M
# ==== =================================== ============= ===== ====== ====
#    1 0x9277183CCDDC5C9148701A8733C04373   0.0080 50.1%     8 0.0010  0.00 SELECT INFORMATION_SCHEMA.PROFILING
#    2 0x733019F33E3027F6693EB59382A27420   0.0060 37.4%     8 0.0008  0.00 SELECT INFORMATION_SCHEMA.PROFILING
#    3 0xB69F9989DE7C26732D3E585B1FBD6FBF   0.0010  6.3%     1 0.0010  0.00 SELECT INFORMATION_SCHEMA.PROFILING
#    4 0xDF9ED0790D537E7948AB709485D784C3   0.0010  6.2%     1 0.0010  0.00 SELECT store

如何通过慢日志查询查询找到有问题的sql

1、查询次数多且每次查询占用时间长的sql。

一般是pt-query-digest分析的前几个查询。

2.IO大的Sql

注意pt-query-digest分析中的Rows examine项

3、未命中索引的sql

看pt-query-digest分析中Rows examine(扫描行数)和RowsSend（发送行数）的对比。

扫描远大于发送

分析sql查询

使用explain可以查询Sql的执行计划

explain select customer_id,first_name,last_name from customer;

 它返回各列的含义

• table:显示这行数据关于哪张表。
• type:这是重要的列，显示连接使用何种类型型，从最好到最差，连接类型为const，eq_reg,ref,index和ALL。
• possible_keys:显示可能应用在这张表中的索引。如果为空可能没有索引。
• key：实际使用的索引，为空表示没有索引。
• key_len:使用索引的长度在不损失精确度的情况下，越短越好。
• ref:索引的哪一列被使用了，如果可能的话是一个常数。
• rows：mysql认为必须检查的必须返回请求数据的行数。
• extra列需要注意的返回值：
  • Using filesort：看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配全部行的行指针来排序。全部行。
  • Using temporary：看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时
    表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。

Count()和Max()的优化方法(重点)

1、查询最后支付时间——优化max()函数

select max(payment_date) from payment;

 执行计划分析

explain select max(payment_date) from payment;

可以看到我们rows值显示sql需要检查16509条数据才能给我们返回一个结果，很明显明显如果请求量过多的话对我们的mysql服务器是很不友好的！

 优化方案

/*给payment的pay_date字段添加一个普通索引*/
create index idx_paydate on payment(payment_date)

 再次执行计划分析效果

explain select max(payment_date) from payment;

 这样我们的mysql就不用1万6这么多的检查才能给我们放回结果了，Extra字段中也显示了

Select tables optimized away 这样一段话，我去翻译一下大意是——选择最优的表。（这句话并不能代表优化方案一定正确，以真实情况为准）

这操作在很多借贷业务或者租赁业务都有应用比如很多用户都要通过走这样一条sql取得知自己最后的归还日期。案例比如：你还借呗的最后期限，你能取快递的最后日期（过期驿站要收保管费）。

2、在一条SQL中同时查出2006年和2007年电影的数量——count()优化。

首先我们要保证自己写的Sql能查出正确结果才能去优化，下面两种写法都是不可取的。

/*统计film表中发布时间在2006年或2007年的数据
**错误不能分开统计2006和2007的数据
*/
select count(release_year='2006' OR release_year='2007') from film;

/*统计film表中发布时间即是2006又是2007的数据
* release_year不可能即是2006又是2007，有逻辑错误
*/
select count(*) from film where release_year = '2006' and release_year = '2007';

count()的认识（重点）

在写正确的的查询语句之前我们先来看两条语句的返回结果

select count(release_year='2006') from film;

 

select count(release_year='2007') from film;

两个结果都是1000；其实这里有两个知识点:

1. count()函数的执行流程

          执行count时mysql会逐行去判断括号里的条件，一旦有一条数据不符合条件就返回FALSE所以上面两条语句其实都相当于执行了

select count(false) from film;--结果是1000

        2.coun(null)和count(*)的区别

        随便设计一个只有id字段，且id可以为空的test表来测试一下。给它插入三个值

          

select count(*) from test;

select count(null) from test;

 

count(*)：会统记空值；而 count(null)不会，而且返回的一定是0；

正确的写法：

根据count的知识点可以写出正确的sql

/*分别查出06和07年发布的电影数量*/
select 
    count(release_year = '2006' Or null) 
        AS '2006发布的电影数量',
    count(release_year = '2007'  or null) 
        AS '2007发布的电影数量' 
from film;

接下来对这条sql进行分析

EXPLAIN SELECT
	count( release_year = '2006' OR NULL ) AS '2006发布的电影数量',
	count( release_year = '2007' OR NULL ) AS '2007发布的电影数量' 
FROM
	film;

 rows是956。其实这张表的数字比较特别，不是2006就是2007，看不出我们把两条语句合成一条语句的作用，网上老师和博客也讲得不太好，这里压根都没体现出优化作用！

其实我觉得他想表达的应该是把原来两条语句做的事，写成一条sql来查去优化sql。来看原来1条语句来分开查的时候的执行计划分析。

explain select count(release_year='2006' or null) as '2006' from film;

分析出来的rows值和上面是一样的，查2007的也是一样rows都是956。这样我们把原来要两句sql干的活用一句sql干，mysql服务器的压力应该是又小了一半。

这一招的应用场景的话：我们的一些直播项网站，那些标签的统计是不是可以用一条sql去查出来再放到缓存服务器中，来减少mysql的压呢？比如斗鱼的标签分类页面。

 子查询优化

通常情况下，需要把子查询优化为join查询,但在优化时要注意关联键是否有一对多的关系，需要注意重复数据，可以考虑通过distinct去重解决。

例如：查询sandra出演的所有影片。

EXPLAIN
SELECT
	title,
	release_year,
	LENGTH 
FROM
	film 
WHERE
	film_id IN ( 
			SELECT film_id FROM film_actor WHERE actor_id IN 
				( SELECT actor_id FROM actor WHERE first_name = 'sandra' ) );

可以看到每张表都生成了执行计划解析 。

再看join语句的方式

EXPLAIN
SELECT
	title,
	release_year,
	LENGTH 
FROM
	film 
WHERE
	film_id IN ( 
		SELECT fa.film_id FROM film_actor AS fa 
				JOIN actor AS a ON fa.actor_id = a.actor_id 
								WHERE a.first_name = 'sandra' );

 两种执行方式给出的结果都是rows值都是 956 、2、 1三个

但是type得出的值却不一样

子查询方式是：ALL、index_subquery、unique_subquery；

join方式是：ALL、ref、eq_ref

根据mysql手册上的接收可以看出join的sql语句性能要比子查询好。

mysql 8.0中文手册

 至于这招的使用场景，一般在涉及到多表的业务都会用到，比如在一些教育视频网站中查询某个  老师的所   有作品之类。

group by 优化（结果让我意外了）

统计每个演员出演的电影数量

未优化

EXPLAIN
SELECT
	a.first_name,
	a.last_name,
	count(*) 
FROM
	film_actor AS fa
	INNER JOIN actor AS a USING ( actor_id ) 
GROUP BY
	a.actor_id;

type——index，ref 。 rows——15,13。 Extra——空 ，Using index

优化后

EXPLAIN
SELECT
	a.first_name,
	a.last_name,
	c.cnt 
FROM
	actor AS a
	INNER JOIN 
    ( SELECT actor_id, count(*) AS
         cnt FROM film_actor GROUP BY actor_id )
  AS c USING ( actor_id );

 

 type——ALL，eq_rel,index; rows——200,1,5538 ; Extra——空，空，Using index

好家伙我明明也是用mysql5.5，执行的结果确和视频里老师的对不上。 按照视频上未优化前

actor表的Extra应该是Using filesort、Using temporary，是有产生文件排序的。所以从现在explain产生的结果来看未优化那种写法要比优化后的写法性能好太多了。所以搞sql优化要根据项目环境测试选择合适的sql的。

这种语句现实中真实的场景：直播平台的标签统计，粉丝统计。

limit优化

limit常用于分页处理，伴随order by从句一起，大多会使用Fiesorts容易造成大量IO问题。

例子:

flim表中按照标题升序，从第50条数据起连续取5条数据，展示该id、title、description

EXPLAIN SELECT
	film_id,
	title,
	description 
FROM
	film 
ORDER BY
	title ASC 
	LIMIT 50,
	5;

截图方式不太清，还是通过cmder来复制执行结果好。

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1129
        Extra: Using filesort

优化步骤1： 使用有索引的列或者主键进行order by操作

EXPLAIN SELECT
	film_id,
	title,
	description 
FROM
	film 
ORDER BY
	film_id ASC 
	LIMIT 50,
	5;

    *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 2
          ref: NULL
         rows: 55
        Extra:

这样就避免了filesort，但是这个还不能算优化完成，试想一下，若我们要查询500后的5行呢？rows的值就会变成505，这样的话我们的数据量越大，随着翻页的增加，mysql要扫描的行数也就越多。

优化步骤2：先记录下上次查询的主键，下次查询时使用主键过滤。例如我们想从步骤1查询出来的id为51到55的记录，继续往翻一页，我们可以这样写查出id为56到60的数据

EXPLAIN SELECT
	film_id,
	title,
	description 
FROM
	film 
WHERE
	film_id > 55 
	AND film_id <= 60 
ORDER BY
	film_id 
	LIMIT 0,
	5;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: NULL
         rows: 5
        Extra: Using where

可以看到type变成了range，比起步骤1时的index要好，rows变成了5（即每页要显示的数目)，Extra的值也变成了Using where。从这三个值来看我们的性能的确得到了提高。

注意： 主键要顺序排序并连续的，如果主键中间空缺了某一列，或者某几列，会出现列出数据不足5行的数据；如果不连续的情况，建立一个附加的列index_id列，保证这一列数据要自增的，并添加索引即可。

其实这部分内容个人觉得老师准备得不够好，从title为主键变成id为主键，直接把业务需求给变了，让人不适，但这一招在一定场景都用还是得上的，比如我们的银行流水信息要做分页的时候就适合通过这种方法去优化我们的sql。

索引优化

如何选择合适的列建立索引

1、在where从句，group by 从句，order by从句，on从句出现的列

2、索引字段越小越好

就是建立索引的列的数据，不能在一个列对应的是text列上面建立一个索引，而是选择简单的数据类型比如int，长度比较短的varchar，这样一次IO读取的索引会更多，越容易命中对应的值。

3、离散度大的列放到联合索引前

问题来了：

/*从payment表中查staff_id=2且customer*/
SELECT
* 
FROM
	payment 
WHERE
	staff_id = 2 
	AND customer_id = 584;

这条语句我是index(staff_id,customer_id)好，还是index(customer_id,staff_id)好呢？

这需要我们先判断列的离散度，执行

select count(distinct customer_id),count(distinct staff_id) from payment;

分别统计它们的唯一值(不重复的值)有多少个。

+-----------------------------+--------------------------+
| count(distinct customer_id) | count(distinct staff_id) |
+-----------------------------+--------------------------+
|                         599 |                        2 |
+-----------------------------+--------------------------+

从结果可以看到customer的唯一值更多，所以customer_id的离散度更大，所以index(customer_id,staff_id)好一些。

索引的维护以及优化

       通常情况下，我认为增加索引会加快查询效率，但会影响写入效率(insert、update、delete),但有时候过多的索引有时候不但影响写入效率，还会减低查询效率。因为我们的sql执行前要先执行分析计划，如果索引越多，执行时mysql它分析得就越慢。正如我们一本书如果目录过多（比正文还多），那么也会影响我们找内容的。

        所以我们有时候对一些冗余、重复的索引进行删除。索引并非一成不变的，也要更加实际业务来curd的。

• 重复索引

        重复索引是指相同的列以相同的顺序建立的同类型的索引，如下表中primary key和ID列上的索引就是重复索引。

        

CREATE TABLE test_1 (
	id INT NOT NULL PRIMARY KEY,
	NAME VARCHAR ( 10 ) NOT NULL,
	title VARCHAR ( 50 ) NOT NULL,
	UNIQUE ( id ) 
) ENGINE = INNODB

• 冗余索引是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引，下面这个列子中key（name，id）就是一个冗余索引。

CREATE TABLE test_2 (
	id INT NOT NULL PRIMARY KEY,
	NAME VARCHAR ( 10 ) NOT NULL,
	title VARCHAR ( 50 ) NOT NULL,
	KEY ( NAME, id ) 
) ENGINE = INNODB

• 如何查找重复和及冗余索引

        直接用sql语言来查表结构

show create table test_2;

效果：

| Table  | Create Table
                                                                                           |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_2 | CREATE TABLE `test_2` (
  `id` int(11) NOT NULL,
  `NAME` varchar(10) NOT NULL,
  `title` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `NAME` (`NAME`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+-----------------------------------------

使用pt-duplicate-key-checker工具来查。

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

 他会给出我们一些移除建议索引的建议，以及显示，哪些索引重复了。

删除没有用到的索引

        Mysql没有记录索引的使用情况，但在PerconMySql和MariaDB中可以通过INDEX_STATISTICS表来查看哪些索引为使用，所以Mysql中一般使用慢查询日志配合pt-index-usage工具来进行索引使用情况的分析。

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

数据库结构优化

选择合适的数据类型

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

2、使用简单的数据类型，int比varchar在mysql上处理更简单。

3、进可能使用not null 定义字段，并给出默认值。

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

例子：

• 使用int来存存储日期时间，利用FROM_UNIXTIME(),UNIX_TIMESTAMP()两个函数来进行转换。

/*新建一个表用于测试int存时间*/
CREATE TABLE test_3 (
	id INT auto_increment NOT NULL,
	timestr INT,
PRIMARY KEY ( id ));
/*插入一个字段进行测试*/
INSERT INTO test_3( timestr )
VALUES
	(
	unix_timestamp( '2021-11-12 13:12:00' ));
	
/*把int转换成时间展示*/
SELECT
	from_unixtime( timestr ) 
FROM
	test_3;

• 在我们存储ip地址时可以选择bigint来存储，它在内存中占7个字节，比我们正常用varchar8个字节来存储要好。只要利用inet_aton(),inet_ntoa()两个函数来进行转换。

CREATE TABLE test_sessions (
	id INT auto_increment NOT NULL,
	ipaddress BIGINT,
PRIMARY KEY ( id ));

INSERT INTO test_sessions ( ipaddress )
VALUES
	(
	inet_aton( '192.168.0.1' ));
	
SELECT
	inet_ntoa( ipaddress ) 
FROM
	test_sessions;

范式化设计

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

 上表中存在这样的函数依赖关系：商品名称——》分类——》分类描述；也就是说非关键字段分类描述对关键字段商品名称有传递函数依赖。

这张表不符合第三范式，存在以下问题：

1. 数据冗余：（分类，分类描述）对于每一个商品都会进行记录。
2. 数据的插入异常（我每次插入一个饮料类商品的记录，都得浪费空间去存分类和分类描述）
3. 数据更新异常（如果我要更新饮料的商品描述，要把每个饮料商品记录都更新）
4. 数据删除异常（假如我把所有饮料类的商品删除，就没地方查饮料这一类商品的商品描述了）

我们可以把表如下查分使其符合第三范式：

这样设计可以使分类和商品相互独立，也就是我们常说的解耦，分类与商品的信息可以更加灵活地增删改。比如说要把分类中的饮料改成进口饮料，原先只有一个表，有N条记录就要要修改N次，而现在只需要在分类表修改一次就行了；再比如说，老板要增加一百个分类，但是每个分类具体是什么商品只有员工知道，如果只有一张表就得两个人一起干才行，但是现在可以独立开工；其他情况也是一个道理。 

反范式化

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

假设我们有四张表

 来看下面一段sql查询出了每张订单的用户名，电话，地址，订单、订单价格。

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 a.订单ID；

通过对sql优化的学习我们知道，这样的sql会产生临时表，增加io，但是如果按照这样符合范式的表结构在sql上可优化的空间不大，这时我们就可以考虑对表进行反范式设计,

 经过反范式设计，我们原来要通过关联3张表才能查询的内容，就可以通过一张表来查了

SELECT 

用户名,电话,地址,订单ID,订单价格  

FROM '订单表' 

表的垂直拆分

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

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

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

        3.把常用字段放一起。

接下来我们尝试对电影表film进行优化：

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'))

从上面学字段类型的选择时我们这道 release_year字段可以YEAR(4)替换为int存储，description这种TEXT大字段值可以考虑分表，而title和description经常会被一起查出来展示，因此它们两个可以独立放在一个表中。在实际环境中改表的类型和结构都要注意数据的维护。对film表垂直拆分后表的结构如下：

CREATE TABLE film (
film_id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
release_year INT 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,FRIMARY KEY (film_id')
)engine=innodb

附加表结构

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

两张表通过film_id来维持主外键关系。

水平拆分

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

CREATE TABLE payment (
    payment_id' SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id' SMALLINT(5) UNSIGNED NOT NULL,
    staff_id TINYINT(3) UNSIGNED NOT NULL,
    rental_id INT(11) DEFAULT NULL,
    amount DECIMAL(5,2) NOT NULL,
    payment_date DATETIME NOT NULL,
    last_update TIMESTAMP ,
    PRIMARY KEY ('payment_id')
) ENGINE=INNODB DEFAULT CHARSET=utf8

如果我们这展表的数据达到1千万级别或者1亿这样，即使我们数据表选择的类型再好，也有合适的索引，也没有冗余的字段查询效率依然会下降，这时我们可以把这些数据分成多个表结构一样的表来存。比如把表数据拆分成10份，customer_id取模为1的存到表payment_1里，为2的存payment_2这样依次类推。

常见的水平拆分操作：

1. 对customer_id进行hash运算，如果要拆分成5个表则使用mod(customer_id,5)取出0-4个
2. 针对不同的hashID把数据存到不同表中。

水平拆分后带来的挑战：

1. 跨分区表进行数据查询。
2. 统计及后台报表操作。

应对挑战的策略：

前台展示,利用分表提高查询效率。后台统计使用总表。

系统配置优化

操作系统配置优化

        MySQ数据库是基于操作系统的，目前大多数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 

# 打开文件数的限制，可以使用ulimit-a查看目录的各种限制，

#可以修改 /etc/security/limits.conf文件，增加以下内容以修改打开文件数量的限制

* soft nofile 65535

* hard nofile 65535

         另外最好在MySql服务器上关闭iptables,selinux等防火墙软件。建议使用硬件防火墙。

Mysql配置优化

        MySQL可以通过启动时指定配置参数和使用配置文件两种方法进行配置，在大数情况下配置文件位于/etc/my.cnf或是/etc/mysql/my.cnf在windows系统配置 文件可以是位于C:/mysql/my.ini文件，MySQL查找配置文件的顺序可以通过以 下方法获得

$ /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options '

注意：如果存在多个位置存在配置文件，则后面的会覆盖前面的。

Mysql配置文件常用参数说明。

• innodb_buffer_pool_size

        非常重要的一个参数，用于配置Innodb的缓冲池如果数据库中只有Innodb表， 则推荐配置量为总内存的75%.

SELECT ENGINE, 
    ROUND(SUM(data_length + index_length)/1024/1024, 1)
     AS "Total MB", 
    FROM INFORMATION_SCHEMA.TABLES 
WHERE 
    table_schema not in ("information_schema", "performance_schema") 
GROUP BY ENGINE; 

Innodb_buffer_pool_size >= Total MB

• innodb_read_io_threads和innodb_write_io_threads

        这两个参数决定了Innodb读写的IO进程数，默认为4

• innodb_buffer_pool_instances

        MySQL5.5中新增加参数，可以控制缓冲池的个数，默认情况下只有一个缓冲池。

• innodb_stats_on_metadata

        决定了MySQL在什么情况下会刷新innodb表的统计信息。一般设置为off

• innodb_flush_log_at_trx_commit

        关键参数决定事务日志更新到磁盘的速度，对innodb的IO效率影响很大。默认值为1，可以取0,1,2三个值，一般建议设为2，但如果数据安全性要求比较高则使用默认值1。

0和2都是每秒从缓冲区写入文件一次。区别是commit操作时，0不做任何操作，为2 的时候，commit时会把缓冲区内容写入文件。文档上是这么写的：If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit.. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2.

• innodb_file_per_table

        关键参数，控制Innodb每一个表使用独立的表空间，默认为OFF，也就是所有
表都会建立在共享表空间中，drop table ，truncate table后表空间文件并不会进行收缩，也就是说表空间文件所占的磁盘空间并不会因为drop table , truncate table 而释放。建议设置成ON使得空间可以动态收缩，扩大。

我们还能用Mysql第三方配置向导，来生成配置文件。
 Percon Configuration Wizard
      官网地址

服务器硬件优化

1.cpu选择:mysql一个sql的执行只能用到单核的cpu,其次,在复制进程的时候也是只能用到单核的cpu,所以cpu并不是越多越好,mysql5.5是的服务器不要超过32核,偏向选择单核频率更快的cpu;

2.Disk IO 优化

•     2.1常用的RAID 级别简介

    RAID0: 也称为条带,多个磁盘接成一个使用,io最好(但是磁盘坏了,数据没了,安全性差)

    RAID1: 也称镜像,要求至少两个磁盘,每组磁盘存储的数据相同;

    RAID5：也是把多个（最少3个）硬盘合并成1个逻辑盘使用，数据读写时会建立奇 偶校验信          息，并且奇偶校验信息和相对应的数据分别存储于不同的磁盘上。当RAID5 的一个磁盘数据发      生损坏后，利用剩下的数据和相应的奇偶校验信息去恢复被损坏的数据。

    RAID1+0:  就是RAID1和RAID0的结合,同时具备两个级别的优缺点,推荐数据库使用这个级别;

    目前可能还有更好的存储设备:比如ssd卡。

•      2.2、SAN（Storage Area Network）和NAT(Network Address Translation)）

1. 常用于高可用方案
2. 顺序读写效率很高，但随机读写不好
3. 数据库随机读写比率高

最后附上我学习的课程地址，感谢无私奉献的老师