Mysql优化

1 数据库设计要合理（3F）

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。关系型数据库中这种规则称为范式。

1.1 三大范式

1.1.1 第一范式

1NF是对属性的原子性约束，要求属性列具有原子性，不可再分解；

id	name	address
1	xiaoming	广州市番禺区大山村

以上图User表为例，所谓的原子性其实取决于业务需求，对于address属性，如果是电商业务，那么可能就要区分这个地址中省市县等，因此此时address就不具有原子性也就不满足1NF。而对于一些普通的项目可能就满足了原子性。

1.1.2 第二范式

2NF是对记录的唯一性约束，表中的记录是唯一的，就满足2NF。比如主键约束、唯一性unique等。比如在订单系统中，对于订单号就不使用默认的id生成而是用另外的orderId，而次id又如何保证在高并发下不会重复呢？可以提前生成好，存放在Redis中，这样需要（新订单）时直接取即可。

1.1.3 第三范式

3NF是对字段冗余性的约束，他要求字段没有冗余。比如

id	name	address	courseId	courseName
1	xiaoming	广州	1	高等数学
2	xiaogou	上海	2	数据库

对于courseId，courseName两个属性其实只要其中一个即可，因此字段就有冗余了。不过没有冗余的数据库未必是好的数据库，有时候为了提高运行效率，降低范式标准也未尝不可。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

2 mysql定位慢查询

如何在一个大的项目中，迅速的定位执行速度满的语句。（定位慢查询）

2.1 show status

使用show status使用show status查看MySQL服务器状态信息

常用命令

--mysql数据库启动了多少时间 show status like 'uptime';

show  stauts like 'com_select'  show stauts like 'com_insert' ...类推 update  delete（显示数据库的查询，更新，添加，删除的次数）

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

//显示到mysql数据库的连接数 show status like  'connections ';

//显示慢查询次数 show status like 'slow_queries';

2.2 慢查询

mysql默认10秒内没有响应sql结果的语句则为慢查询。当然这个默认时间是可以修改的。

--查询慢查询时间

show variables like 'long_query_time';

--修改慢查询时间

set long_query_time=1; ---但是重启mysql之后，long_query_time依然是my.ini中的值

2.3 定位慢查询案例

sql语句：

/*员工表*/
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);

/*随机字符串生成函数*/
create FUNCTION rand_string(n INT)
	RETURNS VARCHAR(255)#改函数会返回一个n位数的随机字符串
BEGIN
#chars_str定义一个变量 char 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 FUNCTION rand_num()
	returns int(5)
BEGIN
	DECLARE i int DEFAULT 0;
	set i=FLOOR(10+RAND()*500);
	return i;
END
/*存储过程*/
delimiter $$
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 $$
执行存储过程
call insert_emp (100001,40000000);  

 接下来就进行如何将慢查询定位到日志文件中。

在默认情况下，我们的mysql不会记录慢查询，需要在启动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指定]

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

my.ini 文件中记录的位置

#Path to the database root

datadir=" C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

1 、设置慢查询时间为1秒

mysql> set long_query_time=1;
Query OK, 0 rows affected

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set

mysql> 

2、查看有多少次慢查询

/*慢查询次数*、
mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set

/*执行一条慢查询语句后再次查询*/
mysql> select * from emp where empno=16777195;
+----------+--------+----------+-----+------------+---------+--------+--------+
| empno    | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |
+----------+--------+----------+-----+------------+---------+--------+--------+
| 16777195 | SQzaDQ | SALESMAN |   1 | 2018-08-09 | 2000.00 | 400.00 |    194 |
+----------+--------+----------+-----+------------+---------+--------+--------+
1 row in set (2.54 sec)
/*慢查询增加一次*/
mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1     |
+---------------+-------+
1 row in set (0.00 sec)


/**
1、怎么定位到该语句呢？进入该目录C:\ProgramData\MySQL\MySQL Server 5.7\Data
2、找到*-slow.log的文件，清空后再执行上述慢查询语句即可看到
*/
# Time: 2018-08-08T23:50:07.875848Z
# User@Host: root[root] @ localhost [::1]  Id:     3
# Query_time: 2.541265  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 16677214
use test;
SET timestamp=1533772207;
select * from emp where empno=16777195;

 

3 分表分库技术（取模分表、水平分割、垂直分割）

通俗理解：水平拆分行，行数据拆分到不同表中， 垂直拆分列，表数据拆分到不同表中

3.1 垂直分割（分库），基于不同系统进行数据库的划分

垂直分割就是要把表按模块划分到不同的数据库表中。比如支付模块、用户模块、订单模块等采用不同的数据库。这样的作用是将一个大的项目拆分成多个小项目，小项目所使用的数据库间互不影响。

其实，相对于垂直切分更进一步的是服务化改造，说得简单就是要把原来强耦合的系统拆分成多个弱耦合的服务，通过服务间的调用来满足业务需求看，因此表拆出来后要通过服务的形式暴露出去，而不是直接调用不同模块的表，淘宝在架构不断演变过程，最重要的一环就是服务化改造，把用户、交易、店铺、宝贝这些核心的概念抽取成独立的服务，也非常有利于进行局部的优化和治理，保障核心模块的稳定性垂直拆分用于分布式场景。

3.2 水平分割（分表），基于同一个系统，将一个表划分为多个表

上面谈到垂直切分只是把表按模块划分到不同数据库，但没有解决单表大数据量的问题，而水平切分就是要把一个表按照某种规则把数据划分到不同表或数据库里。因此水平分割就是把一个表按照某种规则划分到不同的表或者数据库中，例如像日志记录，可以按照年份进行分表、对于QQ号码可以按照位数或者尾数（不过这样不均匀）、手机号码前三位、取模等等。

缺点：分页（查询时去多个表查，不好分页）、查询限制（比如根据id取模，那么我查询用户名呢？我根本不知道去那张表查询，因此三张表都要去）

4 索引优化

为什么要建索引？可以看2里面的emp表。

mysql> show keys from emp;
Empty set
/**没建索引查询时间*/
[SQL] select * from emp where empno=1234567;
受影响的行: 0
时间: 2.556ms
/**创建索引，然后执行*/
alter table emp add primary key (empno);
select * from emp where empno=1234567;
/*可以看到时间极短,效率提高接近500倍*/
[SQL] select * from emp where empno=1234567;
受影响的行: 0
时间: 0.004ms

因此索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍

4.1 分类

4.1.1. 主键索引

主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。 删除主键索引：alter table articles drop primary key;查询索引show index from 表名show keys from 表名

4.1.2 唯一索引

索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种方式创建： 

创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表)； 

修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表)； 

创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) )； 

unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复，但是不能存有重复的空字符串‘’

4.1.3 普通索引

 普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

create table ccc(

id int unsigned,

name varchar(32)

)

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

4.1.4 全文索引

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%'; 错误用法 索引不会生效

正确用法:

select * from articles where match(title,body) against ( 'database')

在mysql中fulltext 索引只针对 myisam生效。

mysql自己提供的fulltext针对英文生效->sphinx (coreseek) 技术处理中文

使用方法是 match(字段名..) against(‘关键字’)

全文索引：停止词,  因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符，就不会创建，这些词，称为停止词.比如（a，b，mysql，the）

mysql> select match(title,body) against ('database') from articles;（输出的是每行和database的匹配度）

4.1.5 聚集索引

聚集索引在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。

如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

简答来说就是如果是主键的聚簇索引，那么该索引节点就包含了记录的其他值，如果不是主键的也就是二次索引，那么该索引节点就包含了一个指向主键索引的值。所以数据访问速度是很快的。这门课中说的很好

4.2 索引底层实现

索引的实现通常使用 B 树及其变种 B+ 树。为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)

上图展示了一种可能的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在 O(log2n)的复杂度内获取到相应数据。

创建索引可以大大提高系统的性能：

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

增加索引也有许多不利的方面：

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

一般来说，应该在这些列上创建索引：在经常需要搜索的列上，可以加快搜索的速度；在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度。

一般来说，不应该创建索引的的这些列具有下列特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为 text, image 和 bit 数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

4.3 执行计划

实际项目开发中，由于我们不知道实际查询的时候数据库里发生了什么事情，数据库软件是怎样扫描表、怎样使用索引的，因此，我们能感知到的就只有sql语句运行的时间，在数据规模不大时，查询是瞬间的，因此，在写sql语句的时候就很少考虑到性能的问题。但是当数据规模增大，如千万、亿的时候，我们运行同样的sql语句时却发现迟迟没有结果，这个时候才知道数据规模已经限制了我们查询的速度。所以，查询优化和索引也就显得很重要了。

MySql提供了EXPLAIN语法用来进行查询分析，在SQL语句前加一个"EXPLAIN"即可。比如我们要分析如下SQL语句：

explain select * from table where table.id = 1 

运行上面的sql语句后你会看到，下面的表头信息：

table | type | possible_keys | key | key_len | ref | rows | Extra

EXPLAIN列的解释

table 
显示这一行的数据是关于哪张表的

type 
这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

说明：不同连接类型的解释（按照效率高低的顺序排序）

system：表只有一行：system表。这是const连接类型的特殊情况。

const ：表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待。

eq_ref：在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用。

ref：这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好。

range：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况。

index：这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）。

ALL：这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免。

possible_keys 
显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key 
实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len 
使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref 
显示索引的哪一列被使用了，如果可能的话，是一个常数

rows 
MYSQL认为必须检查的用来返回请求数据的行数

Extra 
关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

说明：extra列返回的描述的意义

Distinct ：一旦mysql找到了与行相联合匹配的行，就不再搜索了。

Not exists ：mysql优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。

Range checked for each Record（index map:#） ：没有找到理想的索引，因此对从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。

Using filesort ：看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。

Using index ：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。

Using temporary ：看到这个的时候，查询需要优化了。这里，mysql需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。

Where used ：使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题。

因此，弄明白了explain语法返回的每一项结果，我们就能知道查询大致的运行时间了，如果查询里没有用到索引、或者需要扫描的行过多，那么可以感到明显的延迟。因此需要改变查询方式或者新建索引。mysql中的explain语法可以帮助我们改写查询，优化表的结构和索引的设置，从而最大地提高查询效率。当然，在大规模数据量时，索引的建立和维护的代价也是很高的，往往需要较长的时间和较大的空间，如果在不同的列组合上建立索引，空间的开销会更大。因此索引最好设置在需要经常查询的字段中。

5 mysql的数据引擎

使用的存储引擎 myisam / innodb（用的最多、事物机制）/ memory

myisam:事物要求不高、查询和添加为主

Innodb:事物要求高、保存的数据都是重要数据。

区别：

1、事物安全（Myisam不支持、innodb支持）

2、查询和添加速度（myisam批量插入速度快）

3、支持全文索引（myisam支持，innodb不支持）

4、所机制（myisam表锁、innodb行锁）

5、外键Myisam不支持、innodb支持外键

Memory 存储，比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快. （如果mysql重启的话，数据就不存在了）

myisam注意事项：

如果你的数据库的存储引擎是myisam,请一定记住要定时进行碎片整理

举例说明:

create table test100(id int unsigned ,name varchar(32))engine=myisam;

insert into test100 values(1,’aaaaa’);

insert into test100 values(2,’bbbb’);

insert into test100 values(3,’ccccc’);

insert into test100 select id,name from test100;

当插入很多条数据后，如果删除其中一部分，他是不会物理删除的，主要是为了恢复，如果想要物理删除只需要执行以下语句进行碎片整理。

optimize table test100;

6 对mysql配置优化（配置mysql最大连接数my.ini、调整缓存大小）

7 mysql服务器升级

8 定时的去除不需要的数据，定时进行碎片整理（MyISAM）

9 sql语句调优

9.1组合索引

CREATE TABLE `dept` (
  `deptno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `dname` varchar(20) NOT NULL DEFAULT '',
  `loc` varchar(13) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8；


create PROCEDURE insert_dept(in start int(10),in max_num int(10))
BEGIN
 declare i int DEFAULT 0;
 set autocommit=0;
 REPEAT
 set i=i+1;
 insert into dept values ((start+i),rand_string(10),rand_string(8));
 UNTIL i =max_num
 end REPEAT;
  commit;
END
执行
call insert_dept(100,10);
//创建组合索引
ALTER table dept add index my_index(dname,loc)

//组合索引注意事项：

/*EXPLAIN SELECT * FROM dept where dname = 'ykDmASMhAh';该语句使用索引*/
/*EXPLAIN SELECT * FROM dept where loc = 'ykDmASMhAh';该语句不使用索引*/

 9.2 索引注意

 1、对于使用like进行模糊查询的，只要第一个%存在的就不会使用索引，其他情况都会使用索引。

 2、使用or的条件参数必须都加索引，否则就不会使用索引。

3、空值Null查询时，=不使用索引 is Null使用索引。 

4、使用group by 分组查询是，默认分组后，还会排序，可能会降低速度，在group by 后面增加 order by null 就可以防止排序.

5、不要使用>=做两次扫描一次>一次=。 

 6、in和not in也没有使用索引。

7、查询量大时，可以分表、分页、缓存。

10 MySQL高可用

为什么需要高可用？在只有一台mysql服务器的时候，如果这台服务器宕机了，一般只能通过重启的方式解决，这对于一些实时性要求较高的系统是行不通的甚至对于一些高并发的访问压力也是不能承受的，所以高可用的概念就应运而生了。也就是有一台master主服务器（权限crud），一台或者多台slave备服务器(权限r)。

如上图，有一台master mysql服务器一台slave备用mysql服务器构成的mysql集群，其中master具有查询、删除、更新、插入的权限，而slave仅有select权限。这样当有一个客户端jdbc连接连接该集群做查询时，则连接备用服务器做查询，如果该连接做插入、删除、更新时则走的是master服务器。这样就实现了读写分离（提高IO性能），不过又出现了一个问题，就是master更新之后，slave的数据没有及时得到更新啊，因为客户端没有权限对其更新，因此就涉及到一个master-slave的数据同步问题。

10.1 主从复制

作用：解决上图中的集群master-slave的数据同步问题、读写分离、高可用（宕机时的容错）、集群。

原理：master和slave之间保持一个长连接，利用二进制sql执行文件（当master执行更新操作时产生，查询时不产生），这样slave节点只需要将该文件复制到本地，然后再执行两个数据库就保持一致了。【注意MYSQL会在执行语句之后，释放锁之前，写入二进制日志，确保事务安全】

日志系统A，其实它是MYSQL的日志类型中的二进制日志，也就是专门用来保存修改数据库表的所有动作，即bin log。

日志系统B，并不是二进制日志，由于它是从master的二进制日志复制过来的，并不是自己的数据库变化产生的，有点接力的感觉，称为中继日志，即relay log。

延迟：延迟肯定有，不过这种集群一般在内网，所以速度是很快的，而且安全性也比较高。

数据一致性问题：利用mycat插件

10.1.1 配置样例（这里以一主一备为例）

1、准备两个数据库（192.168.245.134,192.168.245.135），对于克隆安装的虚拟机，最好检查一下cat /var/lib/mysql/auto.cnf 
[auto]
server-uuid=e3983205-9c39-11e8-801e-000c296a8627

两台服务器的server_uuid是否一致，千万不要一致，否则后面的slave_io_running一致为no。

2、主从数据库配置

注意：Windows环境下的mysql配置文件为my.ini，Linux环境下的为/etc/my.cnf

master：
#my.cnf

server_id=134 #主服务器的id
log-bin=mysql-bin #开启二进制执行文件的生成，默认不开启

#重启mysql服务器
service mysqld restart

#查看服务器状态
systemctl status mysqld

#查看服务器serverid是否修改成功
show variables like '%server_id%';#出现134的字样

#在master上添加用户，给slave来使用该用户来对此数据库操作
#一般不用root帐号，‘%’表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.245.135，加强安全。
#mysql5.7以上可能会报错执行以下两句即可屏蔽掉密码安全读较高的问题
#set global validate_password_policy=0;
#set global validate_password_length=1;
GRANT REPLICATION SLAVE ON *.* to 'test'@'%' identified by '123456';

#登录主服务器查看master的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      581 |              |                  |                   |
#出现以上结果则成功

配置salve节点：
#/etc/my.cnf
server_id=135  #注意该id不能重复
log-bin=mysql-bin
binlog_do_db=test #需要同步的数据库名

#重启服务
service mysqld restart

#查看服务器状态
systemctl status mysqld

#查看服务器serverid是否修改成功
show variables like '%server_id%';#出现135的字样

#设置从服务器的主服务器，如下其中pos对应master执行show master status;中的position，master_log_file对应master执行show master status;中的File，user和password对应GRANT REPLICATION SLAVE ON *.* to 'test'@'%' identified by '123456';
#如果以前开启过同步，则需要先执行stop slave
change master to master_host='192.168.245.134',master_user='test',master_password='123456',
         master_log_file='mysql-bin.000002',master_log_pos=581;

#开启同步
start slave

#查看从服务器状态，不成功请查看这个文件的报错信息/var/log/mysqld.log自行百度
show slave status;
#必须保证Slave_IO_Running为Yes和Slave_SQL_Running为Yes，方能成功。


此时就可以愉快的玩耍了，试着修改master中的test库，查看slave中的test变化，是不是很神奇？？？？？？

10.2 读写分离

简单来说，就是在一个集群里，比如上面的一主一从集群中，master只做写的操作，slave只做读的操作。

如图：对于客户端连接数据库（直接和我暴露的Windows的数据库中间件通信，而不直接和我Linux上的mysql集群通信）时，他不会直接和我集群中的核心数据库（master，slave）直接通信，这样我的集群ip地址就不用暴露、安全系数也高。而且集群间数据库的通信使用内网，速度也快，也从某种程度上降低了单台服务器的并发高而导致负载过高问题。当客户端与数据库通信时，我的数据库中间件（比如我适用本地Windows的数据库中间件）根据客户端的操作，然后根据不同的操作转发到不同的数据库服务器上进行相应的操作。这样便实现了读写分离，接下来考虑的就是对数据库中间件的配置问题（其实就是mysql的反向代理，这里使用mycat这个插件进行实现）。

10.2.1 数据库反向代理配置

Mycat是一个开源的分布式数据库系统，但是因为数据库一般都有自己的数据库引擎，而Mycat并没有属于自己的独有数据库引擎，所有严格意义上说并不能算是一个完整的数据库系统，只能说是一个在应用和数据库之间起桥梁作用的中间件。

在Mycat中间件出现之前，MySQL主从复制集群，如果要实现读写分离，一般是在程序段实现，这样就带来了一个问题，即数据段和程序的耦合度太高，如果数据库的地址发生了改变，那么我的程序也要进行相应的修改，如果数据库不小心挂掉了，则同时也意味着程序的不可用，而对于很多应用来说，并不能接受；
 

  引入Mycat中间件能很好地对程序和数据库进行解耦，这样，程序只需关注数据库中间件的地址，而无需知晓底层数据库是如何提供服务的，大量的通用数据聚合、事务、数据源切换等工作都由中间件来处理；

  Mycat中间件的原理是对数据进行分片处理，从原有的一个库，被切分为多个分片数据库，所有的分片数据库集群构成完成的数据库存储，有点类似磁盘阵列中的RAID0.

1、在上面的master中执行以下sql

CREATE DATABASE IF NOT EXISTS `weibo_simple`;
-- ------------------------------------
-- Table structure for `t_users` 用户表
-- ------------------------------------
DROP TABLE IF EXISTS `t_users`;
CREATE TABLE `t_users` (
  `user_id` varchar(64) NOT NULL COMMENT '注册用户ID',
  `user_email` varchar(64) NOT NULL COMMENT '注册用户邮箱',
  `user_password` varchar(64) NOT NULL COMMENT '注册用户密码',
  `user_nikename` varchar(64) NOT NULL COMMENT '注册用户昵称',
  `user_creatime` datetime NOT NULL COMMENT '注册时间',
  `user_status` tinyint(1) NOT NULL COMMENT '验证状态  1：已验证  0：未验证',
  `user_deleteflag` tinyint(1) NOT NULL COMMENT '删除标记  1：已删除 0：未删除',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- -------------------------------------
-- Table structure for `t_message`微博表
-- -------------------------------------
DROP TABLE IF EXISTS `t_message`;
CREATE TABLE `t_message` (
  `messages_id` varchar(64) NOT NULL COMMENT '微博ID',
  `user_id` varchar(64) NOT NULL COMMENT '发表用户',
  `messages_info` varchar(255) DEFAULT NULL COMMENT '微博内容',
  `messages_time` datetime DEFAULT NULL COMMENT '发布时间',
  `messages_commentnum` int(12) DEFAULT NULL COMMENT '评论次数',
  `message_deleteflag` tinyint(1) NOT NULL COMMENT '删除标记 1：已删除 0：未删除',
  `message_viewnum` int(12) DEFAULT NULL COMMENT '被浏览量',
  PRIMARY KEY (`messages_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `t_message_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2 配置mycat中conf/server.xml，添加以下内容

	<!--添加用户，改用户可读可写，其中用户名为my_test,密码为my_test，数据库名为my_test-->
	<user name="my_test">
		<property name="password">my_test</property>
		<property name="schemas">my_test</property>
	</user>
<!--添加用户，改用户仅能读，其中用户名为my_test_read_only,密码为my_test，数据库名为my_test-->
	<user name="my_test_read_only">
		<property name="password">my_test</property>
		<property name="schemas">my_test</property>
		<property name="readOnly">true</property>
	</user>

3 配置mycat中conf/schema.xml，

<?xml version="1.0" encoding="UTF8"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
    <!-- 与server.xml中user的schemas名一致 -->
    <schema name="my_test" checkSQLschema="true" sqlMaxLimit="100">
        <table name="t_users" primaryKey="user_id" dataNode="dn1" rule="rule1"/>
        <table name="t_message" type="global" primaryKey="messages_id" dataNode="dn1" />
    </schema>
<dataNode name="dn1" dataHost="jdbchost" database="weibo_simple" />
   
    <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="1"
                writeType="0" dbType="mysql" dbDriver="native" switchType="1"
                slaveThreshold="100">
         <heartbeat>select user()</heartbeat>  
        <writeHost host="hostMaster" url="192.168.245.134:3306" user="root" password="root">
        </writeHost>
        <writeHost host="hostSlave" url="192.168.245.135:3306" user="root" password="root"/>
    </dataHost>
    
</mycat:schema>

4 配置mycat中conf/rule.xml

<?xml version="1.0" encoding="UTF8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- you may not use this file except in compliance with the License. - You 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://org.opencloudb/">
	 <tableRule name="rule1">
        <rule>
            <columns>user_id</columns>
            <algorithm>func1</algorithm>
        </rule>
    </tableRule>
    <function name="func1" class="org.opencloudb.route.function.AutoPartitionByLong">
  	<property name="mapFile">autopartition-long.txt</property>
    </function>
</mycat:rule>

5 开启日志，修改conf/log4j.xml

    <level value="debug" />

6 按照10.1主从复制，将master和slave的主从复制配置好。

7 测试，双击mycat下的bin/startup_nowrap.bat开始启动。

1）利用Navicat或者其他的客户端工具，连接mycat记住是mycat。端口号默认是8066，用户和密码是server.xml设置的。设置几个用户就连接几个，比如我这里的是两个，可以看到，表格数据名称和上面配置的一样，接下来就展示他神奇的一面。

2）在read_wirte这个连接中的t_users表新增一条记录。

查看read、主从（master、slave）三个库是否有变化，如果都能查到代表你成功了。可以自行测试下，是否my_test_read_only用户是否只能读，看看是否能创建记录，主从复制是否成功，从添加主以及mycat是否能看到记录，主添加其他是否能看到记录等等。