MySQL高级02

索引简介

索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。
你可以简单理解为“排好序的快速查找数据结构”。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，
这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址
为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

结论：数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上

优势：类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本；通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息；实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

mysql索引结构

BTree索引

【初始化介绍】 
一颗b树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），
如磁盘块1包含数据项17和35，包含指针P1、P2、P3，
P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
 
【查找过程】
如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。
 
真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

时间复杂度

同一问题可用不同算法解决，而一个算法的质量优劣将影响到算法乃至程序的效率。算法分析的目的在于选择合适算法和改进算法。

B+Tree索引

B树和B+树的区别

B+Tree与B-Tree 的区别
 
　1）B-树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。
　 2）在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好，而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B-树多，树高比B-树小，这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好些，而且B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用B+树的缘故。 
　
思考：为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？ 
1) B+树的磁盘读写代价更低 
　　B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。 
2) B+树的查询效率更加稳定 
　　由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

聚簇索引与非聚簇索引（两种方式比喻为字典的字母表的索引和偏旁的索引）

mysql索引分类

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引；

随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name)
);
  
单独建单值索引：
CREATE  INDEX idx_customer_name ON customer(customer_name); 
 
删除索引：
DROP INDEX idx_customer_name  on customer;

唯一索引：索引列的值必须唯一，但允许有空值

随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_no)
);
  
单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
 
删除索引：
DROP INDEX idx_customer_no on customer ;

主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引

随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
   
CREATE TABLE customer2 (id INT(10) UNSIGNED   ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
 
 单独建主键索引：
ALTER TABLE customer 
 add PRIMARY KEY customer(customer_no);  
 
删除建主键索引：
ALTER TABLE customer 
 drop PRIMARY KEY ;  
 
修改建主键索引：
必须先删除掉(drop)原索引，再新建(add)索引

复合索引：即一个索引包含多个列

随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_name),
  KEY (customer_no,customer_name)
);
 
单独建索引：
CREATE  INDEX idx_no_name ON customer(customer_no,customer_name); 
 
删除索引：
DROP INDEX idx_no_name  on customer ;

基本语法

创建
CREATE  [UNIQUE ]  INDEX [indexName] ON table_name(column)) 
删除
DROP INDEX [indexName] ON mytable; 
查看
SHOW INDEX FROM table_name\G
使用ALTER命令：有四种方式来添加数据表的索引：
①ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
 
②ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
 
③ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
 
④ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

哪些情况需建索引：

　　主键自动建立唯一索引、频繁作为查询条件的字段应该创建索引、查询中与其它表关联的字段，外键关系建立索引、单键/组合索引的选择问题，组合索引性价比更高、

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度、查询中统计或者分组字段。

哪些情况不要创建索引：

　　表记录太少、经常增删改的表或者字段（Why:提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。

因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件）

Where条件里用不到的字段不创建索引、过滤性不好的不适合建索引

性能分析

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
表的读取顺序、哪些索引可以使用、数据读取操作的操作类型、哪些索引被实际使用、表之间的引用、每张表有多少行被物理查询（扫描）

Explain + SQL语句；执行计划包含的信息

各个字段的名词解释：

id：select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序；

id相同，执行顺序由上至下；

select_type：查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

SIMPLE：简单的 select 查询,查询中不包含子查询或者UNION；

PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为Primary；

DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询, 把结果放在临时表里。

SUBQUERY：在SELECT或WHERE列表中包含了子查询；
DEPENDENT SUBQUERY：在SELECT或WHERE列表中包含了子查询,子查询基于外层；

UNCACHEABLE SUBQUREY

UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED

UNION RESULT：从UNION表获取结果的SELECT

type：

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是： 
 
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 
 
 
 range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询

这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

ref:非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
index:出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组


 
 
system>const>eq_ref>ref>range>index>ALL
 
 
一般来说，得保证查询至少达到range级别，最好能达到ref。

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len字段能够帮你检查是否充分的利用上了索引

如何计算
1 、先看索引上字段的类型+长度比如 int=4 ;  varchar(20) =20 ; char(20) =20  
2  、如果是varchar或者char这种字符串字段，视字符集要乘不同的值，比如utf-8  要乘 3,GBK要乘2，
3 、varchar这种动态字符串要加2个字节
4、 允许为空的字段要加1个字节

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数（const）。哪些列或常量被用于查找索引列上的值。

Extra：

Using filesort ：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”

Using where：表明使用了where过滤

最佳左前缀法则

查询优化

一、单表使用索引及常见索引失效

案例(索引失效)

①全值匹配我最爱

②最佳左前缀法则--如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

③不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

④存储引擎不能使用索引中范围条件右边的列；

⑤mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描；

⑥is not null 也无法使用索引,但是is null是可以使用索引的；

⑦like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作；

⑧字符串不加单引号索引失效；

 #批量删除某个表上的所有索引-->执行存储过程
CALL proc_drop_index("mydb","emp");

#建立3个索引
CREATE INDEX idx_age_deptId_name ON emp(age, deptId, NAME);
#最佳左前缀法则
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30   AND emp.name = 'abcd'   
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptid=1   AND emp.name = 'abcd'   
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.`age` = 30 AND deptId = 4 AND emp.name = 'abcd';

#单值索引
CREATE INDEX idx_name ON emp(NAME);
#索引中不能使用函数等
EXPLAIN  SELECT SQL_NO_CACHE * FROM emp WHERE   emp.name  LIKE 'abc%' 
EXPLAIN   SELECT SQL_NO_CACHE * FROM emp WHERE   LEFT(emp.name,3)  = 'abc'    

#不能使用索引中范围条件右边的列
EXPLAIN SELECT  SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc' ; 
CREATE INDEX idx_age_name_deptid ON emp(age,NAME,deptid)  #这样子建索引就都用上了;

#不能使用不等于
CREATE INDEX idx_name ON emp(NAME)
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <> 'abc' 
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name != 'abc' 

#is not null 也无法使用索引,但是is null是可以使用索引的
CREATE INDEX idx_age ON emp(age);
EXPLAIN SELECT * FROM emp WHERE age IS NULL
EXPLAIN SELECT * FROM emp WHERE age IS NOT NULL

#like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
CREATE INDEX idx_age_name ON emp(age, NAME);
EXPLAIN  SELECT SQL_NO_CACHE * FROM emp WHERE emp.`age`=30 AND  emp.name  LIKE 'abc%' #type=range
EXPLAIN  SELECT SQL_NO_CACHE * FROM emp WHERE emp.`age`=30 AND emp.name  LIKE '%abc%' #type=ref

#字符串不加单引号索引失效
CREATE INDEX idx_name ON emp(NAME)
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.`name` = '123'; #type=ref;  ref=const
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.`name` = 123;

View Code

建议：

对于单键索引，尽量选择针对当前query过滤性更好的索引；在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引；

在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面；

书写sql语句时，尽量避免造成索引失效的情况。

二、关联查询优化

# 下面开始explain分析
EXPLAIN SELECT * FROM book  LEFT JOIN class ON class.card = book.card;
#结论：type 有All
 
# 添加索引优化
ALTER TABLE `book` ADD INDEX Y ( `card`);
 
换成inner join
 
delete from class where id<5;
 
# 第2次explain
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
#可以看到第二行的 type 变为了 ref,rows 也变成了优化比较明显。
#这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,
#所以右边是我们的关键点,一定需要建立索引。
 
# 删除旧索引 + 新建 + 第3次explain
DROP INDEX Y ON book;
ALTER TABLE class ADD INDEX X (card);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

1、保证被驱动表的join字段已经被索引； 2、left join 时，选择小表作为驱动表（就是主表），大表作为被驱动表（从表）。；

3、inner join 时，mysql会自己帮你把小结果集的表选为驱动表。 4、子查询尽量不要放在被驱动表，有可能使用不到索引。

5、能够直接多表关联的尽量直接关联，不用子查询。

三、子查询优化

尽量不要使用not in 或者 not exists，用left outer join on xxx is null 替代；

##用left outer join  on  xxx is null 替代
EXPLAIN SELECT SQL_NO_CACHE age, COUNT(*)  FROM emp e WHERE  id  NOT  IN
(SELECT ceo FROM dept d WHERE ceo IS NOT NULL)
GROUP BY age 
HAVING COUNT(*)<10000
#加上两个索引；
CREATE INDEX idx_age ON emp(age);
CREATE INDEX idx_ceo ON dept(ceo);
#
  EXPLAIN SELECT SQL_NO_CACHE age,COUNT(*) FROM  emp a LEFT OUTER JOIN dept b ON a.id =b.ceo
  WHERE    b.ceo IS   NULL
  GROUP BY age 
  HAVING COUNT(*)<10000

四、排序分组优化

①case

#case;  以下是否能使用到索引，能否去掉using filesort
CALL proc_drop_index("mydb","emp");
CREATE INDEX idx_age_deptid_name ON emp (age,deptid,NAME)

EXPLAIN SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptid; #不能使用索引,因为它没有进行过滤;
EXPLAIN  SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptid LIMIT 10; #可以使用索引
 #无过滤 不索引（没有过滤就不会使用索引）

EXPLAIN  SELECT * FROM emp WHERE age=45 ORDER BY deptid; #可以去掉using filesort
EXPLAIN  SELECT * FROM emp WHERE age=45 ORDER BY  deptid,NAME; #可以去掉using filesort

EXPLAIN  SELECT * FROM emp WHERE age=45 ORDER BY  deptid,empno; #不能去掉using filesort
EXPLAIN  SELECT * FROM emp WHERE age=45 ORDER BY  NAME,deptid; #不能去掉using filesort
EXPLAIN SELECT * FROM emp WHERE deptid=45 ORDER BY age; #不能去掉using filesort
#顺序错，必排序（与索引的顺序错了，就手动排序，不能使用上索引）

EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY  deptid DESC, NAME DESC ; #可以去掉using filesort; 方向都是降序
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY  deptid ASC, NAME DESC ; #不能去掉using filesort
#方向反 必排序；desc与asc

②ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序（Extra --> Using FileSort ）

③索引的选择：执行案例前先清除emp上的索引，只留主键

#索引的选择
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ;
#结论：很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
#开始优化：
思路： 尽量让where的过滤条件和排序使用上索引
但是一共两个字段(age,empno)上有过滤条件，一个字段(ename)有索引 
1、我们建一个三个字段的组合索引可否？


CREATE INDEX idx_age_empno_name ON emp(age,empno,NAME); #只用到了age和empno索引
#我们发现using filesort 依然存在，所以name 并没有用到索引。原因是因为empno是一个范围过滤，所以索引后面的字段不会再使用索引了。
#所以我们建一个3值索引是没有意义的 
DROP INDEX idx_age_empno_name ON emp
CREATE INDEX idx_age_name ON emp(age,NAME);

#也就是说empno 和name这个两个字段我只能二选其一。这样我们优化掉了 using filesort。

#但是 如果我们选择那个范围过滤，而放弃排序上的索引呢,建立 
DROP INDEX idx_age_name ON emp(age, NAME);
CREATE INDEX idx_age_eno ON emp(age,empno); 
#结果竟然有 filesort的 sql 运行速度，超过了已经优化掉 filesort的 sql ，而且快了好多倍。何故？
 
#原因是所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。  
相对的 empno<101000 这个条件如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。
 
#结论:当范围条件和group by 或者 order by  的字段出现二选一时 ，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

④ 如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序

双路排序
MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，
读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。
单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，
因为它把每一行都保存在内存中了。
由于单路是后出的，总体而言好过双路，但是单路有问题：
在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而多次I/O。
本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。
优化策略：
增大sort_buffer_size参数的设置；增大max_length_for_sort_data参数的设置；减少select 后面的查询的字段。
提高Order By的速度
1. Order by时select * 是一个大忌只Query需要的字段， 这点非常重要。在这里的影响是：
  1.1 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序。
  1.2 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。
2. 尝试提高 sort_buffer_size
不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的  1M-8M之间调整
3. 尝试提高 max_length_for_sort_data
提高这个参数， 会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率. 1024-8192之间调整

⑤ GROUP BY关键字优化：

group by 使用索引的原则几乎跟order by一致，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引。

五、最后使用索引的手段：覆盖索引

什么是覆盖索引？
简单说就是，select 到 from 之间查询的列 <=使用的索引列+主键
EXPLAIN SELECT * FROM emp WHERE age <> 20;

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE NAME LIKE '%abc'; 
 
#使用覆盖索引后
EXPLAIN SELECT SQL_NO_CACHE id, age, deptId FROM emp WHERE NAME LIKE '%abc';

MySQL主从复制

复制的基本原理--slave会从master读取binlog来进行数据同步；

①master将改变记录到二进制日志（binary log），这些记录过程叫做二进制日志事件，binary log events；

②slave将master的binary log events拷贝到它的中继日志（relay log）；③slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的。

复制的基本原则

每个slave只有一个master；每个slave只能有一个唯一的服务器ID；每个master可以有多个salve；

复制的最大问题---延迟

一主一从复制的配置

mysql版本一致且后台以服务运行；主从都配置在[mysqld]结点下，都是小写；

一、主机修改my.ini配置文件：

①主服务器唯一ID 
②启动二进制日志
③设置不要复制的数据库
④设置需要复制的数据库
⑤设置logbin格式
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
server-id=1 #主数据库端ID号
log-bin=F:/MySQLData/MySQLServer5.5/data/mysqlbin  #开启二进制日志   
binlog-ignore-db=mysql #设置不要复制的数据库
binlog-do-db=mydb2  #设置需要复制的数据库
binlog_format=STATEMENT #设置logbin格式，默认的

mysql主从复制起始时，从机不继承主机数据；

二、从机配置文件修改my.cnf的[mysqld]栏位下；

[root@kris ~]# cd /etc
[root@kris etc]# vim my.cnf

server-id = 2 #注意my.cnf 中有server-id = 1，把它修改了即可，与主服务的server-id不能一样。
relay-log=mysql-relay

因修改过配置文件，请主机+从机都重启后台mysql服务；主机从机都关闭防火墙

三、在Windows主机上建立帐户并授权slave：

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.1.100' IDENTIFIED BY '123456'; #授权账号

查询master的状态--->>
 SHOW MASTER STATUS;
  -->> 记录下File和Position的值,执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化。

四、在Linux从机上配置需要复制的主机：

CHANGE MASTER TO MASTER_HOST='192.168.43.179',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysqlbin.000001',MASTER_LOG_POS=107;  
#注意这里是数字，不加单引号；登陆的账号是主服务授权的账号和密码

START SLAVE; 

SHOW SLAVE STATUS;

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.43.179
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqlbin.000004
          Read_Master_Log_Pos: 645
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 790
        Relay_Master_Log_File: mysqlbin.000004
             Slave_IO_Running: Yes #两个都为yes表示主从配置成功！
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
          ......
1 row in set (0.00 sec)

　stop slave;　停止从服务复制功能

五、主机新建库、新建表、insert记录，从机复制；测试。

CREATE DATABASE mydb2;
USE mydb2;

CREATE TABLE users (
uid INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(100) NOT NULL, email VARCHAR(200));

INSERT INTO users (username, email) VALUES ('kris', 'kris@qq.com'),
('smile','smile@123.com');

posted @ 2020-02-19 11:27 啊啊啊啊鹏阅读(90) 评论(0) 编辑收藏举报

刷新页面返回顶部

数据指南