关于mysql:Mysql存储引擎索引学习笔记

55次阅读

共计 97070 个字符，预计需要花费 243 分钟才能阅读完成。

利用 show profile 查看 sql 的执行周期

批改配置文件 /etc/my.cnf, 新增以下一行, 并重启 mysql

query_cache_type=1

开启 profiling:

mysql> show variables like '%profiling%';
mysql> set profiling=1;

显示最近的几次查问:

mysql> show profiles;
+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.00136600 | show variables like '%profiling%' |
|        2 | 0.00049975 | select * from mytbl2 where id = 2 |
+----------+------------+-----------------------------------+
查问 id       时长         sql

查看具体过程:

show profile cpu,block io for query 编号
就是上图的过程

查看存储引擎:

SHOW ENGINES;

1、InnoDB 存储引擎
InnoDB 是 MySQL 的默认事务型引擎，它被设计用来解决大量的短期(short-lived) 事务。除非有十分特地的起因须要应用其余的存储引擎，否则应该优先思考 InnoDB 引擎。

2、MyISAM 存储引擎
MyISAM 提供了大量的个性，包含全文索引、压缩、空间函数(GIS) 等，但 MyISAM 不反对事务和行级锁，有一个毫无疑问的缺点就是解体后无奈平安复原。

3、Archive 引擎
Archive 档案存储引擎只反对 INSERT 和 SELECT 操作，在 MySQL5.1 之前不反对索引。
Archive 表适宜日志和数据采集类利用。
依据英文的测试论断来看，Archive 表比 MyISAM 表要小大概 75%，比反对事务处理的 InnoDB 表小大概 83%。

4、Blackhole 引擎
Blackhole 引擎没有实现任何存储机制，它会抛弃所有插入的数据，不做任何保留。但服务器会记录 Blackhole 表的日志，所以能够用于复制数据到备库，或者简略地记录到日志。但这种利用形式会碰到很多问题，因而并不举荐。

5、CSV 引擎
CSV 引擎能够将一般的 CSV 文件作为 MySQL 的表来解决，但不反对索引。
CSV 引擎能够作为一种数据交换的机制，十分有用。
CSV 存储的数据间接能够在操作系统里，用文本编辑器，或者 excel 读取。

6、Memory 引擎
如果须要疾速地拜访数据，并且这些数据不会被批改，重启当前失落也没有关系，那么应用 Memory 表是十分有用。Memory 表至多比 MyISAM 表要快一个数量级。

7、Federated 引擎
Federated 引擎是拜访其余 MySQL 服务器的一个代理，只管该引擎看起来提供了一种很好的跨服务器的灵活性，但也常常带来问题，因而默认是禁用的。

罕用的有 MyISAM 和 InnoDB

它们的区别:

比照项	MyISAM	InnoDB
外键	不反对	反对
事务	不反对	反对
行表锁	表锁，即便操作一条记录也会锁住整个表，不适宜高并发的操作	行锁, 操作时只锁某一行，不对其它行有影响，适宜高并发的操作
缓存	只缓存索引，不缓存实在数据	不仅缓存索引还要缓存实在数据，对内存要求较高，而且内存大小对性能有决定性的影响
关注点	节俭资源、耗费少、简略业务	并发写、事务、更大资源

mysql 默认应用 InnoDB, 但 mysql 内置的零碎表应用 MyISAM, 因为没有高并发, 而且节俭资源.

mysql 单表瓶颈 500w 数据, 单库瓶颈 5000w 数据

MySQL 官网对索引的定义为:索引 (Index) 是帮忙 MySQL 高效获取数据的数据结构.
索引的目标在于进步查问效率，能够类比字典，

如果要查“mysql”这个单词，咱们必定须要定位到 m 字母，而后从下往下找到 y 字母，再找到剩下的 sql。

如果没有索引，那么你可能须要 a —-z，如果我想找到 Java 结尾的单词呢？或者 Oracle 结尾的单词呢？

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

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

劣势:

进步数据检索的效率, 升高数据库的 IO 老本
通过索引列对数据进行排序, 升高数据排序的老本, 升高了 CPU 的耗费

劣势:

尽管索引大大提高了查问速度, 同时却会升高更新表的速度(INSERT,UPDATE,DELETE), 因为更新表是,mysql 不仅要保留数据, 还要保留索引文件每次更新增加了索引列的字段, 都会调整因为更新所带来的键值变动后的索引信息
实际上索引也是一张表, 该表保留了主键与索引字段, 并指向实体表的记录, 所以索引也是要占用磁盘空间的

1. 索引

2. 红色方块代表指向数据的指针

3. 黄色代表向下节点的索引

比方要查找 98, 与 17,35 比照, 小于两者走 P1, 在它们之间走 P2, 大于它们走 P3, 以此类推找到最终的后果

1. 索引

2. 向下节点的索引

走到最终的叶子节点才有指向数据的指针

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+ 树的查问效率更加稳固
因为非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查问的门路长度雷同，导致每一个数据的查问效率相当。

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

聚簇索引并不是一种独自的索引类型，而是一种数据存储形式。
术语‘聚簇’示意数据行和相邻的键值聚簇的存储在一起。
如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。

聚簇索引的益处：
依照聚簇索引排列程序，查问显示肯定范畴数据的时候，因为数据都是严密相连，数据库不不必从多个数据块中提取数据，所以节俭了大量的 io 操作。
聚簇索引的限度：
对于 mysql 数据库目前只有 innodb 数据引擎反对聚簇索引，而 Myisam 并不反对聚簇索引。
因为数据物理存储排序形式只能有一种，所以每个 Mysql 的表只能有一个聚簇索引。个别状况下就是该表的主键。
为了充分利用聚簇索引的聚簇的个性，所以 innodb 表的主键列尽量选用有序的程序 id，而不倡议用无序的 id，比方 uuid 这种。

即一个索引只蕴含单个列, 一个表能够有多个单列索引

随表一起建索引：

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 ;

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，用于全文索引。

主键主动建设惟一索引
频繁作为查问条件的字段应该创立索引
查问中与其余表关联的字段, 外键关系应创立索引
单键 / 组合索引的抉择问题, 组合索引性价比更高
查问中排序的字段, 进步排序速度
查问中统计或者分组字段

表记录太少
常常增删改的表或者字段
where 条件里用不到的字段
过滤性不好的字段(如性别)

掂量是否须要加索引的真正指标

应用 EXPLAIN 关键字能够模仿优化器执行 SQL 查问语句, 从而晓得 MySQL 是如何解决你的 SQL 语句的, 剖析你的查问语句或是表构造的性能瓶颈.

通过 EXPLAIN 能够剖析出以下信息:

表的读取程序
哪些索引能够应用
数据读取操作的操作类型
哪些索引被理论应用
表之间的援用
每张表有多少行被物理查问

语法:

EXPLAIN SELECT SQL_NO_CACHE * FROM...

SQL_NO_CACHE: 敞开缓存

==id==:SELECT 查问的序列号, 蕴含一组数字, 示意查问中执行 SELECT 子句或操作表的程序.

三种状况:
- id 雷同, 执行程序由上至下
- id 不同, 如果是子查问,id 的序号会递增,id 值越大优先级越高
- id 雷同不同, 同时存在,id 如果雷同，能够认为是一组，从上往下程序执行；在所有组中，id 值越大，优先级越高，越先执行
关注点:
- id 号每个号码, 示意一趟独立的查问, 一个 sql 的查问趟数越少越好.

==type==: 显示查问应用了何种类型, 是较为重要的一个指标

后果值从最好到最坏顺次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保障查问至多达到 range 级别，最好能达到 ref。

类型	阐明
system	表只有一行记录（等于零碎表），这是 const 类型的特列，平时不会呈现，这个也能够忽略不计
const	示意通过索引一次就找到了,const 用于比拟 primary key 或者 unique 索引。因为只匹配一行数据，所以很快; 如将主键置于 where 列表中，MySQL 就能将该查问转换为一个常量
eq_ref	唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或惟一索引扫描
ref	非唯一性索引扫描，返回匹配某个独自值的所有行. 实质上也是一种索引拜访，它返回所有匹配某个独自值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
==range==	只检索给定范畴的行, 应用一个索引来抉择行。key 列显示应用了哪个索引; 个别就是在你的 where 语句中呈现了 between、<、>、in 等的查问; 这种范畴扫描索引扫描比全表扫描要好，因为它只须要开始于索引的某一点，而结束语另一点，不必扫描全副索引。
==index==	呈现 index 是 sql 应用了索引然而没用通过索引进行过滤，个别是应用了笼罩索引或者是利用索引进行了排序分组
==all==	Full Table Scan，将遍历全表以找到匹配的行
index_merge	在查问过程中须要多个索引组合应用，通常呈现在有 or 的关键字的 sql 中
ref_or_null	对于某个字段既须要关联条件，也须要 null 值得状况下。查问优化器会抉择用 ref_or_null 连贯查问。
index_subquery	利用索引来关联子查问，不再全表扫描
unique_subquery	该联接类型相似于 index_subquery。子查问中的惟一索引

==key_len==: 示意索引中应用的字节数，可通过该列计算查问中应用的索引的长度。key_len 字段可能帮你查看是否充沛的利用上了索引, 越大越好
==rows==:rows 列显示 MySQL 认为它执行查问时必须查看的行数。
越少越好
key: 理论应用的索引, 如果为 NULL, 则没有应用索引; 查问中若应用了笼罩索引，则该索引和查问的 select 字段重叠

==Extra==: 蕴含不适宜在其余列中显示但非常重要的额定信息

类型	阐明
==Using filesort==	阐明 mysql 会对数据应用一个内部的索引排序，而不是依照表内的索引程序进行读取。MySQL 中无奈利用索引实现的排序操作称为“文件排序”排序字段须要加索引
==Using temporay==	使了用长期表保留两头后果,MySQL 在对查问后果排序时应用长期表。常见于排序 order by 和分组查问 group by。分组字段须要加索引
USING index	示意相应的 select 操作中应用了笼罩索引(Covering Index)，防止拜访了表的数据行，效率不错！<br/> 如果同时呈现 using where，表明索引被用来执行索引键值的查找;<br/> 如果没有同时呈现 using where，表明索引只是用来读取数据而非利用索引执行查找。<br/> 利用索引进行了排序或分组
Using where	表明应用了 where 过滤
==using join buffer==	应用了连贯缓存,表之间的连贯条件须要加索引
impossible where	where 子句的值总是 false，不能用来获取任何元组,sql 不正确的揭示
select tables optimized away	在没有 GROUPBY 子句的状况下，基于索引优化 MIN/MAX 操作或者 <br/> 对于 MyISAM 存储引擎优化 COUNT(*)操作，不用等到执行阶段再进行计算，<br/> 查问执行打算生成的阶段即实现优化。

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

select_type: 查问的类型, 次要是用于区别一般查问, 联结查问, 子查问等简单查问

类型	阐明
SIMPLE	简略的 select 查问, 查问中不蕴含子查问或者 UNION
PRIMARY	查问中若蕴含任何简单的子局部，最外层查问则被标记为 Primary
DERIVED	在 FROM 列表中蕴含的子查问被标记为 DERIVED(衍生)MySQL 会递归执行这些子查问, 把后果放在长期表里。
SUBQUERY	在 SELECT 或 WHERE 列表中蕴含了子查问
DEPENDENT SUBQUERY	在 SELECT 或 WHERE 列表中蕴含了子查问, 子查问基于外层
UNCACHEABLE SUBQUERY	不能应用缓存的子查问, 通常是应用零碎变量作为 WHERE 条件的查问
UNION	若第二个 SELECT 呈现在 UNION 之后，则被标记为 UNION；若 UNION 蕴含在 FROM 子句的子查问中, 外层 SELECT 将被标记为：DERIVED
UNION RESULT	从 UNION 表获取后果的 SELECT

table: 显示这一行的数据是对于那张表的
partitions: 代表分区表中命中状况, 非辨别表, 该项为 null
possible_keys: 显示可能利用在这张表中的索引，一个或多个。
查问波及到的字段上若存在索引，则该索引将被列出，但不肯定被查问理论应用

mysql 索引寄存在 information_schema 元数据库 STATISTICS 统计表中

取出某个表的索引

SELECT INDEX_NAME FROM information_schema.statistics
WHERE TABLE_NAME='t_emp' AND TABLE_SCHEMA='mydb'
AND INDEX_NAME <> 'PRIMARY' AND SEQ_IN_INDEX=1
#排除主键索引

如何遍历这个索引汇合

CURSOR 游标
FETCH xxx INTO xxx

如何让 mysql 执行一个字符串

PREPARE 预编译 xxx
EXECUTE

用存储过程实现

DELIMITER $$
CREATE  PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
       DECLARE done INT DEFAULT 0;
       DECLARE ct INT DEFAULT 0;
       DECLARE _index VARCHAR(200) DEFAULT '';
       DECLARE _cur CURSOR FOR  SELECT   index_name   FROM information_schema.STATISTICS   WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND    index_name <>'PRIMARY'  ;
       DECLARE  CONTINUE HANDLER FOR NOT FOUND set done=2 ;      
        OPEN _cur;
        FETCH   _cur INTO _index;
        WHILE  _index<>'' DO 
               SET @str = CONCAT("drop index",_index,"on",tablename); 
               PREPARE sql_str FROM @str ;
               EXECUTE  sql_str;
               DEALLOCATE PREPARE sql_str;
               SET _index=''; 
               FETCH   _cur INTO _index; 
        END WHILE;
   CLOSE _cur;
   END$$

导致索引生效的细节

where 条件后的字段都应该建设索引, 达到最大索引命中

如果索引蕴含了多列，要恪守最左前缀法令。指的是查问从索引的最左前列开始并且不跳过索引中的列。

计算、函数、(主动 or 手动)类型转换，会导致索引生效而转向全表扫描, 如下:

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

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE   emp.name <>  'abc';

EXPLAIN SELECT * FROM emp WHERE age IS NULL
EXPLAIN SELECT * FROM emp WHERE age IS NOT NULL

含糊查问最好明确首字母

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name=123

留神实体类中的字段类型要与 mysql 中统一, 否则导致主动类型转换, 索引生效

对于单键索引，尽量抉择针对以后 query 过滤性更好的索引
在抉择组合索引的时候，以后 Query 中过滤性最好的字段在索引字段程序中，地位越靠前越好。
在抉择组合索引的时候，尽量抉择能够可能蕴含以后 query 中的 where 字句中更多字段的索引
在抉择组合索引的时候，如果某个字段可能呈现范畴查问时，尽量把这个字段放在索引秩序的最初面
书写 sql 语句时，尽量避免造成索引生效的状况

驱动表与被驱动表

1. 当应用 left join 时，左表是驱动表，右表是被驱动表
2. 当应用 right join 时，右表时驱动表，左表是驱动表
3. 当应用 join 时，mysql 会抉择数据量比拟小的表作为驱动表，大表作为被驱动表

在 sql 优化中，永远是以小表驱动大表

join 查问在有索引条件下:
驱动表有索引不会应用到索引
被驱动表建设索引会应用到索引

MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的后果集作为循环根底数据，而后一条一条地通过该后果集中的数据作为过滤条件到下一个表中查问数据，而后合并后果。

驱动表的全表扫描是无奈防止的, 所以应该为被驱动表建设索引

LEFT JOIN 时数据量小的表应该作为驱动表

INNER JOIN 时 mysql 会本人抉择驱动表

子查问尽量不要放在被驱动表，有可能应用不到索引

可能间接多表关联的尽量间接关联，不必子查问

STRAIGHT_JOIN 性能和 INNER JOIN 一样, 但可指定后面的表为驱动表,== 留神须要明确两张表的数量集 ==

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

SELECT * FROM emp a WHERE a.id NOT IN
(SELECT b.CEO FROM dept b WHERE b.CEO IS NOT NULL);

SELECT * FROM emp a LEFT JOIN dept b ON a.id = b.CEO
WHERE b.id IS NULL;

无过滤不索引 where 或 limit
程序错，必排序(using filesort) 优化器不会调整排序字段的程序
方向反必排序(using filesort) 多个排序字段, 要么都升序, 要么都降序

当范畴筛选条件和 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 前面的查问的字段。

Why

进步 Order By 的速度

Order by 时 select * 是一个大忌只 Query 须要的字段，这点十分重要。在这里的影响是：
1.1 当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改良后的算法——单路排序，否则用老算法——多路排序。
1.2 两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创立 tmp 文件进行合并排序，导致屡次 I /O，然而用单路排序算法的危险会更大一些, 所以要进步 sort_buffer_size。
尝试进步 sort_buffer_size
不论用哪种算法，进步这个参数都会提高效率，当然，要依据零碎的能力去进步，因为这个参数是针对每个过程的 1M-8M 之间调整
尝试进步 max_length_for_sort_data
进步这个参数，会减少用改良算法的概率。然而如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，显著症状是高的磁盘 I / O 流动和低的处理器使用率. 1024-8192 之间调整

GROUP BY 优化

group by 应用索引的准则简直跟 order by 统一，惟一区别是 group by 即便没有过滤条件用到索引，也能够间接应用索引。

最初应用索引的伎俩：笼罩索引

什么是笼罩索引？
简略说就是，select 到 from 之间查问的列 <= 应用的索引列 + 主键

所以最好不要应用 select * 应用明确的字段, 能够应用笼罩索引

剖析 GROUP BY 与长期表的关系 :

1. 如果 GROUP BY 的列没有索引, 产生长期表.
2. 如果 GROUP BY 时,SELECT 的列不止 GROUP BY 列一个, 并且 GROUP BY 的列不是主键 , 产生长期表.
3. 如果 GROUP BY 的列有索引,ORDER BY 的列没索引. 产生长期表.
4. 如果 GROUP BY 的列和 ORDER BY 的列不一样, 即便都有索引也会产生长期表.
5. 如果 GROUP BY 或 ORDER BY 的列不是来自 JOIN 语句第一个表. 会产生长期表.
6. 如果 DISTINCT 和 ORDER BY 的列没有索引, 产生长期表.

一年之后零碎变慢怎么优化?

开启慢查问日志一周~ 两周(运维), 通过日志剖析工具 mysqldumpslow 针对对访问量多的数据和慢查问进行建设索引优化

MySQL 的慢查问日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应工夫超过阀值的语句，具体指运行工夫超过 long_query_time 值的 SQL，则会被记录到慢查问日志中。

具体指运行工夫超过 long_query_time 值的 SQL，则会被记录到慢查问日志中。long_query_time 的默认值为 10，意思是运行 10 秒以上的语句。

由他来查看哪些 SQL 超出了咱们的最大忍受工夫值，比方一条 sql 执行超过 5 秒钟，咱们就算慢 SQL，心愿能收集超过 5 秒的 sql，联合之前 explain 进行全面剖析。

默认状况下 slow_query_log 的值为 OFF，示意慢查问日志是禁用的，
能够通过设置 slow_query_log 的值来开启

SHOW VARIABLES LIKE '%slow_query_log%';

开启慢查问日志只对以后数据库失效，如果 MySQL 重启后则会生效

set global slow_query_log=1

如果要永恒失效，就必须批改配置文件 my.cnf（其它零碎变量也是如此）

批改 my.cnf 文件，[mysqld]下减少或批改参数, 而后重启 MySQL 服务器。

slow_query_log =1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log

slow_query_log_file，它指定慢查问日志文件的寄存门路，零碎默认会给一个缺省的文件 host_name-slow.log

开启慢查问日志后. 指定慢查问的工夫阈值long_query_time，默认状况下 long_query_time 的值为 10 秒，

SHOW VARIABLES LIKE 'long_query_time%';

能够应用命令批改，也能够在 my.cnf 参数外面批改

set long_query_time= 0.2

my.cnf
slow_query_log=1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3
log_output=FILE

在生产环境中，如果要手工剖析日志，查找、剖析 SQL，显然是个体力活，MySQL 提供了日志剖析工具mysqldumpslow。

mysqldumpslow –help

-a: 不将数字形象成 N，字符串形象成 S
-s: 是示意依照何种形式排序；
c: 拜访次数
l: 锁定工夫
r: 返回记录
t: 查问工夫
al: 均匀锁定工夫
ar: 均匀返回记录数
at: 均匀查问工夫
-t: 即为返回后面多少条的数据；
-g: 后边搭配一个正则匹配模式，大小写不敏感的；

罕用参考:

失去返回记录集最多的 10 个 SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
 
失去拜访次数最多的 10 个 SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
 
失去依照工夫排序的前 10 条外面含有左连贯的查问语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
 
另外倡议在应用这些命令时联合 | 和 more 应用，否则有可能呈现爆屏状况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

SHOW PROCESSLIST

显示过程列表

能干什么：查问所有用户正在干什么
如果呈现不悦目的
kill [id]

是什么
将一段查问 sql 封装为一个虚构的表。
这个虚构表只保留了 sql 逻辑，不会保留任何查问后果。
作用
1、封装简单 sql 语句，进步复用性
2、逻辑放在数据库下面，更新不须要公布程序，面对频繁的需要变更更灵便
实用场景
很多中央能够共用的一组查问后果
报表统计数据常常变动, 将统计 sql 封装到一个视图中, 与程序解耦

创立

CREATE VIEW view_name 
AS
SELECT column_name(s)
FROM table_name
WHERE condition

应用

select * from view_name

更新

CREATE OR REPLACE VIEW view_name 
AS
SELECT column_name(s)
FROM table_name
WHERE condition

注意事项(实用 5.5)
mysql 的视图中不容许有 from 前面的子查问，但 oracle 能够

复制的基本原理:

MySQL 复制过程分成三步：
1 master 将扭转记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；
2 slave 将 master 的 binary log events 拷贝到它的中继日志（relay log）；
3 slave 重做中继日志中的事件，将扭转利用到本人的数据库中。MySQL 复制是异步的且串行化的

复制的根本准则
每个 slave 只有一个 master
每个 slave 只能有一个惟一的服务器 ID
每个 master 能够有多个 salve

复制的最大问题:IO 多, 延时

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

主机批改 my.ini 配置文件

主服务器惟一 ID
     server-id=1
启用二进制日志
    log-bin= 本人本地的门路 /data/mysqlbin
    log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin
设置不要复制的数据库
    binlog-ignore-db=mysql
设置须要复制的数据库
    binlog-do-db= 须要复制的主数据库名字
设置 logbin 格局
    binlog_format=STATEMENT（默认）

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

从机配置文件批改 my.cnf 的 [mysqld] 栏位下

 server-id = 2
 relay-log=mysql-relay

因批改过配置文件，请主机 + 从机都重启后盾 mysql 服务,
主机从机都敞开防火墙

主机创立一个用户受权复制权限

GRANT REPLICATION SLAVE ON *.* TO 'slave200404'@'%' IDENTIFIED BY '123456';

查看主机状态

SHOW MASTER STATUS;
|File          | Positon | Binlog_Do_DB |Binlog_lgnore_DB
mysqlbin.000001       107       mydb_200404      mysql

File: 日志文件

Postion: 接入点

Binlog_Do_DB: 要复制的数据库

Binlog_lgnore_DB: 不要复制的数据库

从机对照主机状态拜大哥

CHANGE MASTER TO MASTER_HOST='192.168.0.104',
MASTER_USER='slave200404',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysqlbin.000001',MASTER_LOG_POS=107;

如果之前做过须要先进行再设置

stop slave;
reset master;

启动从服务器复制性能

start slave;

查看主从配置状态

show slave status\G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
胜利

主机创立相应的数据库

CREATE DATABASE mydb_200404;

刷新从机呈现数据库胜利

数据库中间件, 前身是阿里的 cobar, 仅能够用作读写拆散、以及分表分库、多数据源整合, 容灾备份

基本原理:

Mycat 的原理中最重要的一个动词是“拦挡”，它拦挡了用户发送过去的 SQL 语句，首先对 SQL 语句做了
一些特定的剖析：如分片剖析、路由剖析、读写拆散剖析、缓存剖析等，而后将此 SQL 发往后端的实在数据库，
并将返回的后果做适当的解决，最终再返回给用户

这种形式把数据库的分布式从代码中解耦进去，Mycat 是代理，Mycat 前面就是物理数据库。和 Web 服务器的 Nginx 相似。对于使用者来说，拜访的都是 Mycat，不会接触到后端的数据库。

概念:

0、逻辑库 / 表：mycat 中存在的库和表

1、分片表：本来有大量数据的表

2、ER 表：关系表

3、全局表：相似于字典表这种表，字典表和很多表都有关联，mycat 采纳数据冗余存储

4、分片节点（dataNode）：一张大表分为多个数据库上，每个数据库就是分片节点

5、分片主机（dataHost）：分片节点能够在不同的主机，一个或者多个节点所在的主机就是分片主机

6、分片规定（rule）：数据划分的规定

7、全局序列号：数据切分后本来的主键就无奈应用了，因而须要引入一个值保证数据唯一性。

8、多租户（很重要）：多个环境下专用雷同的零碎，并且保障隔离性

8-1：独立数据库，隔离性高，然而代价比拟宏大

8-2：共享一个数据库，不是齐全隔离，隔离性不高，容易出错

8-3：共用数据结构，数据架构，通过 ID 进行辨别租户数据（也就是用 mycat）

简略来说，就是指通过某种特定的条件，将咱们寄存在同一个数据库中的数据扩散寄存到多个数据库（主机）
下面，以达到扩散单台设施负载的成果

数据的切分（Sharding）依据其切分规定的类型，能够分为两种切分模式。一种是依照业务将表进行分类（或者
Schema）来切分到不同的数据库（主机）之上，这种切能够称之为数据的垂直（纵向）切分；

另外一种则是依据表中的数据的逻辑关系依照某个字段的某种规定，将同一个表中的数据依照某种条件拆分到多台数据库（主机）下面，这种切分称之为数据的程度（横向）切分。

linux 下能够下载 Mycat-server-xxxxx.linux.tar.gz 解压拷贝 mycat 目录到 /usr/local 目录

cp -r mycat /usr/local

目录如下：

目录	阐明
bin	mycat 命令，启动、重启、进行等
catlet	catlet 为 Mycat 的一个扩大性能
conf	Mycat 配置信息, 重点关注
lib	Mycat 援用的 jar 包，Mycat 是 java 开发的
logs	日志文件，包含 Mycat 启动的日志和运行的日志。

配置文件:

文件	阐明
server.xml	Mycat 的配置文件，设置账号、参数等
schema.xml	Mycat 对应的物理数据库和数据库表的配置
rule.xml	Mycat 分片（分库分表）规定

schema.xml
定义逻辑库，表、分片节点等内容

将 </schema> 中的内容删除, 并加上 dataNode="dn1" 属性指定数据节点名称

<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 逻辑库    name 名称,   checkSQLschema SQL 查看优化       sqlMaxLimit 开端是否要加 limit xxx-->
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
    <!-- 逻辑库    name 名称,   dataHost 援用的哪个 dataHost      database: 对应 mysql 的 database-->
        <dataNode name="dn1" dataHost="host1" database="mydb_200404" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <!-- 心跳检测 -->
                <heartbeat>select user()</heartbeat>
                <!-- 写主机 can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.0.104:3306" user="root"
                                   password="000000">
                        <!-- 读主机 can have multi read hosts -->
                        <readHost host="hostS1" url="192.168.107.108:3306" user="root" password="000000" />
                </writeHost>
        </dataHost>
</mycat:schema>

schema:

属性	阐明
name	逻辑数据库名，与 server.xml 中的 schema 对应
checkSQLschema	数据库前缀相干设置，倡议看文档，这里临时设为 false
sqlMaxLimit	select 时默认的 limit，防止查问全表

table:

属性	阐明
name	表名，物理数据库中表名
dataNode	表存储到哪些节点，多个节点用逗号分隔。节点为下文 dataNode 设置的 name
primaryKey	主键字段名，主动生成主键时须要设置
autoIncrement	是否自增
rule	分片规定名，具体规定下文 rule 具体介绍

dataNode

属性	阐明
name	节点名，与 table 中 dataNode 对应
datahost	物理数据库名，与 datahost 中 name 对应
database	物理数据库中数据库名

dataHost

属性	阐明
name	物理数据库名，与 dataNode 中 dataHost 对应
balance	平衡负载的形式
writeType	写入形式
dbType	数据库类型
heartbeat	心跳检测语句，留神语句结尾的分号要加。

server.xml
配置 mycat 用户名明码

schemas: 数据库名，这里会和 schema.xml 中的配置关联，多个用逗号离开，例如须要这个用户须要治理两个数据库 db1,db2，则配置 db1,db2

# 批改用户名和 mysql 辨别开
<user name="mycat">
                <property name="password">123456</property>
                <property name="schemas">TESTDB</property>

验证数据库拜访状况

mysql -uroot -p123123 -h 192.168.154.1 -P 3306
mysql -uroot -p123123 -h 192.168.154.154 -P 3306

如本机近程拜访报错，请建对应用户
grant all privileges on *.* to hzy@'%'  identified by '000000';

启动程序

  控制台启动：去 mycat/bin 目录下 mycat console
  后盾启动：去 mycat/bin 目录下 mycat start

登录

后盾治理窗口(运维)
    mysql -umycat -p654321 -P9066 -h192.168.67.131
命令
    show database
    show @@help
数据窗口(开发)
    mysql -umycat -p123456 -P8066 -h192.168.107.108
连贯形式和一般数据库一样，如：jdbc:mysql://192.168.0.2:8066/

如果在启动时发现异常，在 logs 目录中查看日志。

wrapper.log 为程序启动的日志，启动时的问题看这个
mycat.log 为脚本执行时的日志，SQL 脚本执行报错后的具体谬误内容, 查看这个文件。mycat.log 是最新的谬误日志，历史日志会依据工夫生成目录保留。

查看库表

 show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+

mysql> use TESTDB;

mysql> show tables;
+-----------------------+
| Tables_in_mydb_200404 |
+-----------------------+
| mytable               |
+-----------------------+

负载平衡类型，目前的取值有 4 种：1. balance="0", 不开启读写拆散机制，所有读操作都发送到以后可用的 writeHost 上。2. balance="1"，全副的 readHost 与 stand by writeHost 参加 select 语句的负载平衡，简略的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，失常状况下，M2,S1,S2 都参加 select 语句的负载平衡。3. balance="2"，所有读操作都随机的在 writeHost、readhost 上散发。4. balance="3"，所有读申请随机的散发到 readhost 执行，writerHost 不累赘读压力
 <dataHost name="host1" maxCon="1000" minCon="10" balance="2"

在主机执行

INSERT mytable(id,`name`) VALUES(2,@@hostname);

重启 mycat, 再查问

+------+------------+
| id   | name       |
+------+------------+
|    1 | stig       |
|    2 | dockerhost |
+------+------------+
2 rows in set (0.01 sec)

mysql> select * from mytable;
+------+----------------+
| id   | name           |
+------+----------------+
|    1 | stig           |
|    2 | HUAWEIMatebook |
+------+----------------+
2 rows in set (0.00 sec)

mysql> select * from mytable;
+------+------------+
| id   | name       |
+------+------------+
|    1 | stig       |
|    2 | dockerhost |
+------+------------+

依照业务将表进行分类, 不须要 join 关系的表离开

<mycat:schema xmlns:mycat="http://io.mycat/">

    <!-- 数据库配置，与 server.xml 中的数据库对应 -->
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
                <!-- 配置表分到哪个数据节点 -->
                <table name="customer" dataNode="dn2" ></table>
                <table name="orders" dataNode="dn1"></table>
        </schema>
        <!-- 分片配置 -->
        <dataNode name="dn1" dataHost="host1" database="orders__200405" />
        <dataNode name="dn2" dataHost="host2" database="orders_200405" />
    
       <!-- 物理数据库配置 -->
       <!--balance="0" 不配置读写拆散 -->
       <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.0.104:3306" user="root"
                                   password="000000">
                </writeHost>
        </dataHost>
        
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM2" url="192.168.107.108:3306" user="hzy"
                                   password="000000">
                </writeHost>
        </dataHost>

</mycat:schema>

schema.xml

<mycat:schema xmlns:mycat="http://io.mycat/">

    <!-- 数据库配置，与 server.xml 中的数据库对应 -->
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
                <!-- 配置表分到哪个数据节点 -->
                <table name="customer" dataNode="dn2" ></table>
                <table name="orders" dataNode="dn1"></table>
<!-- dictionary 写入了 dn1、dn2 两个节点, 分片的规定为：mod-long 源于 rule.xml 文件中 tableRule 的 name -->
                <table name="dictionary" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2"  rule="mod-long" />
        </schema>
        <!-- 分片配置 -->
        <dataNode name="dn1" dataHost="host1" database="orders__200405" />
        <dataNode name="dn2" dataHost="host2" database="orders_200405" />
    
       <!-- 物理数据库配置 -->
       <!--balance="0" 不配置读写拆散 -->
       <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.0.104:3306" user="root"
                                   password="000000">
                </writeHost>
        </dataHost>
        
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM2" url="192.168.107.108:3306" user="hzy"
                                   password="000000">
                </writeHost>
        </dataHost>

</mycat:schema>

rule.xml
定义分片规定

这个文件外面次要有 tableRule 和 function 这两个标签。在具体使
用过程中能够依照需要增加 tableRule 和 function。

mod-long 就是将数据均匀拆分。因为是两台物理库，所以 rule.xml 中 mod-long 对应的 function count 为 2

 
<tableRule name="mod_rule">
             <rule>
                        <columns>customer_id</columns>
                        <algorithm>mod-long</algorithm>
             </rule>
</tableRule>
 
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- 多少数据节点 -->
                <property name="count">2</property>
        </function>

tableRule

name 属性指定惟一的名字，用于标识不同的表规定
内嵌的 rule 标签则指定对物理表中的哪一列进行拆分 (columns) 和应用什么路由算法(algorithm)

algorithm 应用 function 标签中的 name 属性。连贯表规定和具体路由算法。当然，多个表规定能够连贯到
同一个路由算法上。table 标签内应用。让逻辑表应用这个规定进行分片

function

name 指定算法的名字
class 制订路由算法具体的类名字
property 为具体算法须要用到的一些属性

罕用分片规定

   分片枚举（hash-int）：在配置文件中配置可能呈现的枚举 id，配置分片
   固定分片 Hash 算法（func1）：二进制操作的求模运算
   范畴约定（rang-long）：提前规定好字段范畴属于哪个分片
   取模（mod-long）：依据 ID 进行 10 进制的求模运算
   日期分片（sharding-by-date）：依照工夫划分
   取模范畴（sharding-by-pattern）：依照取模运算和范畴运算联合
   利用指定（sharding-by-substring）：运行阶段有利用自主决定路由到那个分片

<function name="murmur"
        class="io.mycat.route.function.PartitionByMurmurHash">
        <property name="seed">0</property><!-- 默认是 0 -->
        <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
        <property name="virtualBucketTimes">160</property><!-- 一个理论的数据库节点被映射为这么多虚构节点，默认是 160 倍，也就是虚构节点数是物理节点数的 160 倍 -->
        <!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，没有指定权重的节点默认是 1。以 properties 文件的格局填写，以从 0 开始到 count- 1 的整数值也就是节点索引为 key，以节点权重值为值。所有权重值必须是正整数，否则以 1 代替 -->
        <!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property> 
            用于测试时察看各物理节点与虚构节点的散布状况，如果指定了这个属性，会把虚构节点的 murmur hash 值与物理节点的映射按行输入到这个文件，没有默认值，如果不指定，就不会输入任何货色 -->
    </function>

    <function name="crc32slot"
              class="io.mycat.route.function.PartitionByCRC32PreSlot">
        <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
    </function>
    <function name="hash-int"
        class="io.mycat.route.function.PartitionByFileMap">
        <property name="mapFile">partition-hash-int.txt</property>
    </function>
    <function name="rang-long"
        class="io.mycat.route.function.AutoPartitionByLong">
        <property name="mapFile">autopartition-long.txt</property>
    </function>
    <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
        <!-- how many data nodes -->
        <property name="count">3</property>
    </function>

    <function name="func1" class="io.mycat.route.function.PartitionByLong">
        <property name="partitionCount">8</property>
        <property name="partitionLength">128</property>
    </function>
    <function name="latestMonth"
        class="io.mycat.route.function.LatestMonthPartion">
        <property name="splitOneDay">24</property>
    </function>
    <function name="partbymonth"
        class="io.mycat.route.function.PartitionByMonth">
        <property name="dateFormat">yyyy-MM-dd</property>
        <property name="sBeginDate">2015-01-01</property>
    </function>
    
    <function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
            <property name="mapFile">partition-range-mod.txt</property>
    </function>
    
    <function name="jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash">
        <property name="totalBuckets">3</property>
    </function>

title: MySQL 存储引擎和索引 # 题目
date: 2019/6/12 20:16:33 # 工夫
categories: # 分类

数据库
tags: # 标签
mysql
B+Tree

<!–more–>

利用 show profile 查看 sql 的执行周期

批改配置文件 /etc/my.cnf, 新增以下一行, 并重启 mysql

query_cache_type=1

开启 profiling:

mysql> show variables like ‘%profiling%’;
mysql> set profiling=1;

显示最近的几次查问:

mysql> show profiles;
Query_ID	Duration	Query
1	0.00136600	show variables like ‘%profiling%’
2	0.00049975	select * from mytbl2 where id = 2

查问 id 时长 sql

查看具体过程:

show profile cpu,block io for query 编号
就是上图的过程

查看存储引擎:

SHOW ENGINES;

1、InnoDB 存储引擎 InnoDB 是 MySQL 的默认事务型引擎，它被设计用来解决大量的短期(short-lived) 事务。除非有十分特地的起因须要应用其余的存储引擎，否则应该优先思考 InnoDB 引擎。

2、MyISAM 存储引擎 MyISAM 提供了大量的个性，包含全文索引、压缩、空间函数(GIS) 等，但 MyISAM 不反对事务和行级锁，有一个毫无疑问的缺点就是解体后无奈平安复原。

3、Archive 引擎 Archive 档案存储引擎只反对 INSERT 和 SELECT 操作，在 MySQL5.1 之前不反对索引。Archive 表适宜日志和数据采集类利用。依据英文的测试论断来看，Archive 表比 MyISAM 表要小大概 75%，比反对事务处理的 InnoDB 表小大概 83%。

4、Blackhole 引擎 Blackhole 引擎没有实现任何存储机制，它会抛弃所有插入的数据，不做任何保留。但服务器会记录 Blackhole 表的日志，所以能够用于复制数据到备库，或者简略地记录到日志。但这种利用形式会碰到很多问题，因而并不举荐。

5、CSV 引擎 CSV 引擎能够将一般的 CSV 文件作为 MySQL 的表来解决，但不反对索引。CSV 引擎能够作为一种数据交换的机制，十分有用。CSV 存储的数据间接能够在操作系统里，用文本编辑器，或者 excel 读取。

6、Memory 引擎 如果须要疾速地拜访数据，并且这些数据不会被批改，重启当前失落也没有关系，那么应用 Memory 表是十分有用。Memory 表至多比 MyISAM 表要快一个数量级。

7、Federated 引擎 Federated 引擎是拜访其余 MySQL 服务器的一个代理，只管该引擎看起来提供了一种很好的跨服务器的灵活性，但也常常带来问题，因而默认是禁用的。

罕用的有 MyISAM 和 InnoDB

它们的区别:

比照项

MyISAM

InnoDB

外键

不反对

反对

事务

不反对

反对

行表锁

表锁，即便操作一条记录也会锁住整个表，不适宜高并发的操作

行锁, 操作时只锁某一行，不对其它行有影响，适宜高并发的操作

缓存

只缓存索引，不缓存实在数据

不仅缓存索引还要缓存实在数据，对内存要求较高，而且内存大小对性能有决定性的影响

关注点

节俭资源、耗费少、简略业务

并发写、事务、更大资源

mysql 默认应用 InnoDB, 但 mysql 内置的零碎表应用 MyISAM, 因为没有高并发, 而且节俭资源.

mysql 单表瓶颈 500w 数据, 单库瓶颈 5000w 数据

MySQL 官网对索引的定义为:索引 (Index) 是帮忙 MySQL 高效获取数据的数据结构. 索引的目标在于进步查问效率，能够类比字典，

如果要查“mysql”这个单词，咱们必定须要定位到 m 字母，而后从下往下找到 y 字母，再找到剩下的 sql。

如果没有索引，那么你可能须要 a —-z，如果我想找到 Java 结尾的单词呢？或者 Oracle 结尾的单词呢？

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

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

劣势:

进步数据检索的效率, 升高数据库的 IO 老本
通过索引列对数据进行排序, 升高数据排序的老本, 升高了 CPU 的耗费

劣势:

尽管索引大大提高了查问速度, 同时却会升高更新表的速度(INSERT,UPDATE,DELETE), 因为更新表是,mysql 不仅要保留数据, 还要保留索引文件每次更新增加了索引列的字段, 都会调整因为更新所带来的键值变动后的索引信息
实际上索引也是一张表, 该表保留了主键与索引字段, 并指向实体表的记录, 所以索引也是要占用磁盘空间的

1. 索引

2. 红色方块代表指向数据的指针

3. 黄色代表向下节点的索引

比方要查找 98, 与 17,35 比照, 小于两者走 P1, 在它们之间走 P2, 大于它们走 P3, 以此类推找到最终的后果

1. 索引

2. 向下节点的索引

走到最终的叶子节点才有指向数据的指针

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+ 树的查问效率更加稳固因为非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查问的门路长度雷同，导致每一个数据的查问效率相当。

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

聚簇索引并不是一种独自的索引类型，而是一种数据存储形式。术语‘聚簇’示意数据行和相邻的键值聚簇的存储在一起。如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。

聚簇索引的益处：依照聚簇索引排列程序，查问显示肯定范畴数据的时候，因为数据都是严密相连，数据库不不必从多个数据块中提取数据，所以节俭了大量的 io 操作。聚簇索引的限度：对于 mysql 数据库目前只有 innodb 数据引擎反对聚簇索引，而 Myisam 并不反对聚簇索引。因为数据物理存储排序形式只能有一种，所以每个 Mysql 的表只能有一个聚簇索引。个别状况下就是该表的主键。为了充分利用聚簇索引的聚簇的个性，所以 innodb 表的主键列尽量选用有序的程序 id，而不倡议用无序的 id，比方 uuid 这种。

即一个索引只蕴含单个列, 一个表能够有多个单列索引

随表一起建索引：

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 ;

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，用于全文索引。

主键主动建设惟一索引
频繁作为查问条件的字段应该创立索引
查问中与其余表关联的字段, 外键关系应创立索引
单键 / 组合索引的抉择问题, 组合索引性价比更高
查问中排序的字段, 进步排序速度
查问中统计或者分组字段

表记录太少
常常增删改的表或者字段
where 条件里用不到的字段
过滤性不好的字段(如性别)

掂量是否须要加索引的真正指标

应用 EXPLAIN 关键字能够模仿优化器执行 SQL 查问语句, 从而晓得 MySQL 是如何解决你的 SQL 语句的, 剖析你的查问语句或是表构造的性能瓶颈.

通过 EXPLAIN 能够剖析出以下信息:

表的读取程序
哪些索引能够应用
数据读取操作的操作类型
哪些索引被理论应用
表之间的援用
每张表有多少行被物理查问

语法:

EXPLAIN SELECT SQL_NO_CACHE * FROM…

SQL_NO_CACHE: 敞开缓存

id:SELECT 查问的序列号, 蕴含一组数字, 示意查问中执行 SELECT 子句或操作表的程序.

三种状况:
- id 雷同, 执行程序由上至下
- id 不同, 如果是子查问,id 的序号会递增,id 值越大优先级越高
- id 雷同不同, 同时存在,id 如果雷同，能够认为是一组，从上往下程序执行；在所有组中，id 值越大，优先级越高，越先执行


关注点:

*   id 号每个号码, 示意一趟独立的查问, 一个 sql 的查问趟数越少越好.

type: 显示查问应用了何种类型, 是较为重要的一个指标

后果值从最好到最坏顺次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保障查问至多达到 range 级别，最好能达到 ref。

类型

阐明

system

表只有一行记录（等于零碎表），这是 const 类型的特列，平时不会呈现，这个也能够忽略不计

const

示意通过索引一次就找到了,const 用于比拟 primary key 或者 unique 索引。因为只匹配一行数据，所以很快; 如将主键置于 where 列表中，MySQL 就能将该查问转换为一个常量

eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或惟一索引扫描

ref

非唯一性索引扫描，返回匹配某个独自值的所有行. 实质上也是一种索引拜访，它返回所有匹配某个独自值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

range

只检索给定范畴的行, 应用一个索引来抉择行。key 列显示应用了哪个索引; 个别就是在你的 where 语句中呈现了 between、<、>、in 等的查问; 这种范畴扫描索引扫描比全表扫描要好，因为它只须要开始于索引的某一点，而结束语另一点，不必扫描全副索引。

index

呈现 index 是 sql 应用了索引然而没用通过索引进行过滤，个别是应用了笼罩索引或者是利用索引进行了排序分组

all

Full Table Scan，将遍历全表以找到匹配的行

index_merge

在查问过程中须要多个索引组合应用，通常呈现在有 or 的关键字的 sql 中

ref_or_null

对于某个字段既须要关联条件，也须要 null 值得状况下。查问优化器会抉择用 ref_or_null 连贯查问。

index_subquery

利用索引来关联子查问，不再全表扫描

unique_subquery

该联接类型相似于 index_subquery。子查问中的惟一索引
key_len: 示意索引中应用的字节数，可通过该列计算查问中应用的索引的长度。key_len 字段可能帮你查看是否充沛的利用上了索引, 越大越好
rows:rows 列显示 MySQL 认为它执行查问时必须查看的行数。越少越好
key: 理论应用的索引, 如果为 NULL, 则没有应用索引; 查问中若应用了笼罩索引，则该索引和查问的 select 字段重叠
Extra: 蕴含不适宜在其余列中显示但非常重要的额定信息

类型

阐明

Using filesort

阐明 mysql 会对数据应用一个内部的索引排序，而不是依照表内的索引程序进行读取。MySQL 中无奈利用索引实现的排序操作称为“文件排序”排序字段须要加索引

Using temporay

使了用长期表保留两头后果,MySQL 在对查问后果排序时应用长期表。常见于排序 order by 和分组查问 group by。分组字段须要加索引

USING index

示意相应的 select 操作中应用了笼罩索引(Covering Index)，防止拜访了表的数据行，效率不错！如果同时呈现 using where，表明索引被用来执行索引键值的查找; 如果没有同时呈现 using where，表明索引只是用来读取数据而非利用索引执行查找。利用索引进行了排序或分组

Using where

表明应用了 where 过滤

using join buffer

应用了连贯缓存,表之间的连贯条件须要加索引

impossible where

where 子句的值总是 false，不能用来获取任何元组,sql 不正确的揭示

select tables optimized away

在没有 GROUPBY 子句的状况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*)操作，不用等到执行阶段再进行计算，查问执行打算生成的阶段即实现优化。
ref: 显示索引的哪一列被应用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
select_type: 查问的类型, 次要是用于区别一般查问, 联结查问, 子查问等简单查问

类型

阐明

SIMPLE

简略的 select 查问, 查问中不蕴含子查问或者 UNION

PRIMARY

查问中若蕴含任何简单的子局部，最外层查问则被标记为 Primary

DERIVED

在 FROM 列表中蕴含的子查问被标记为 DERIVED(衍生)MySQL 会递归执行这些子查问, 把后果放在长期表里。

SUBQUERY

在 SELECT 或 WHERE 列表中蕴含了子查问

DEPENDENT SUBQUERY

在 SELECT 或 WHERE 列表中蕴含了子查问, 子查问基于外层

UNCACHEABLE SUBQUERY

不能应用缓存的子查问, 通常是应用零碎变量作为 WHERE 条件的查问

UNION

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

UNION RESULT

从 UNION 表获取后果的 SELECT
table: 显示这一行的数据是对于那张表的
partitions: 代表分区表中命中状况, 非辨别表, 该项为 null
possible_keys: 显示可能利用在这张表中的索引，一个或多个。查问波及到的字段上若存在索引，则该索引将被列出，但不肯定被查问理论应用

mysql 索引寄存在 information_schema 元数据库 STATISTICS 统计表中

取出某个表的索引

SELECT INDEX_NAME FROM information_schema.statistics
WHERE TABLE_NAME=’t_emp’ AND TABLE_SCHEMA=’mydb’
AND INDEX_NAME <> ‘PRIMARY’ AND SEQ_IN_INDEX=1

如何遍历这个索引汇合

CURSOR 游标
FETCH xxx INTO xxx

如何让 mysql 执行一个字符串

PREPARE 预编译 xxx
EXECUTE

用存储过程实现

DELIMITER $$
CREATE PROCEDURE proc_drop_index(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN

   DECLARE done INT DEFAULT 0;
   DECLARE ct INT DEFAULT 0;
   DECLARE _index VARCHAR(200) DEFAULT '';
   DECLARE _cur CURSOR FOR  SELECT   index_name   FROM information_schema.STATISTICS   WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND    index_name <>'PRIMARY'  ;
   DECLARE  CONTINUE HANDLER FOR NOT FOUND set done=2 ;      
    OPEN _cur;
    FETCH   _cur INTO _index;
    WHILE  _index<>'' DO 
           SET @str = CONCAT("drop index",_index,"on",tablename); 
           PREPARE sql_str FROM @str ;
           EXECUTE  sql_str;
           DEALLOCATE PREPARE sql_str;
           SET _index=''; 
           FETCH   _cur INTO _index; 
    END WHILE;

CLOSE _cur;
END$$

导致索引生效的细节

where 条件后的字段都应该建设索引, 达到最大索引命中

如果索引蕴含了多列，要恪守最左前缀法令。指的是查问从索引的最左前列开始并且不跳过索引中的列。

计算、函数、(主动 or 手动)类型转换，会导致索引生效而转向全表扫描, 如下:

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

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <> ‘abc’;

EXPLAIN SELECT * FROM emp WHERE age IS NULL
EXPLAIN SELECT * FROM emp WHERE age IS NOT NULL

含糊查问最好明确首字母

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name=123

留神实体类中的字段类型要与 mysql 中统一, 否则导致主动类型转换, 索引生效

对于单键索引，尽量抉择针对以后 query 过滤性更好的索引
在抉择组合索引的时候，以后 Query 中过滤性最好的字段在索引字段程序中，地位越靠前越好。
在抉择组合索引的时候，尽量抉择能够可能蕴含以后 query 中的 where 字句中更多字段的索引
在抉择组合索引的时候，如果某个字段可能呈现范畴查问时，尽量把这个字段放在索引秩序的最初面
书写 sql 语句时，尽量避免造成索引生效的状况

驱动表与被驱动表

1. 当应用 left join 时，左表是驱动表，右表是被驱动表 2. 当应用 right join 时，右表时驱动表，左表是驱动表 3. 当应用 join 时，mysql 会抉择数据量比拟小的表作为驱动表，大表作为被驱动表

在 sql 优化中，永远是以小表驱动大表

join 查问在有索引条件下: 驱动表有索引不会应用到索引被驱动表建设索引会应用到索引

MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的后果集作为循环根底数据，而后一条一条地通过该后果集中的数据作为过滤条件到下一个表中查问数据，而后合并后果。

驱动表的全表扫描是无奈防止的, 所以应该为被驱动表建设索引

LEFT JOIN 时数据量小的表应该作为驱动表

INNER JOIN 时 mysql 会本人抉择驱动表

子查问尽量不要放在被驱动表，有可能应用不到索引

可能间接多表关联的尽量间接关联，不必子查问

STRAIGHT_JOIN 性能和 INNER JOIN 一样, 但可指定后面的表为驱动表, 留神须要明确两张表的数量集

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

SELECT * FROM emp a WHERE a.id NOT IN
(SELECT b.CEO FROM dept b WHERE b.CEO IS NOT NULL);

SELECT * FROM emp a LEFT JOIN dept b ON a.id = b.CEO
WHERE b.id IS NULL;

无过滤不索引 where 或 limit
程序错，必排序(using filesort) 优化器不会调整排序字段的程序
方向反必排序(using filesort) 多个排序字段, 要么都升序, 要么都降序

当范畴筛选条件和 group by 或者 order by 的字段呈现二选一时，优先察看条件字段的过滤数量，如果过滤的数据足够多，而须要排序的数据并不多时，优先把索引放在范畴字段上。反之，亦然。

起因是所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很耗费性能，即便索引优化了排序但理论晋升性能很无限。绝对的范畴筛选条件如果没有用到索引的话，要对几万条的数据进行扫描，这是十分耗费性能的

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

双路排序 MySQL 4.1 之前是应用双路排序, 字面意思就是两次扫描磁盘，最终失去数据，读取行指针和 orderby 列，对他们进行排序，而后扫描曾经排序好的列表，依照列表中的值从新从列表中读取对应的数据输入从磁盘取排序字段，在 buffer 进行排序，再从磁盘取其余字段。取一批数据，要对磁盘进行了两次扫描，家喻户晓，IO 是很耗时的，所以在 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 前面的查问的字段。

Why

进步 Order By 的速度

Order by 时 select * 是一个大忌只 Query 须要的字段，这点十分重要。在这里的影响是：1.1 当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改良后的算法——单路排序，否则用老算法——多路排序。1.2 两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创立 tmp 文件进行合并排序，导致屡次 I /O，然而用单路排序算法的危险会更大一些, 所以要进步 sort_buffer_size。
尝试进步 sort_buffer_size 不论用哪种算法，进步这个参数都会提高效率，当然，要依据零碎的能力去进步，因为这个参数是针对每个过程的 1M-8M 之间调整
尝试进步 max_length_for_sort_data 进步这个参数，会减少用改良算法的概率。然而如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，显著症状是高的磁盘 I / O 流动和低的处理器使用率. 1024-8192 之间调整

GROUP BY 优化

group by 应用索引的准则简直跟 order by 统一，惟一区别是 group by 即便没有过滤条件用到索引，也能够间接应用索引。

最初应用索引的伎俩：笼罩索引

什么是笼罩索引？简略说就是，select 到 from 之间查问的列 <= 应用的索引列 + 主键

所以最好不要应用 select * 应用明确的字段, 能够应用笼罩索引

剖析 GROUP BY 与长期表的关系 :

如果 GROUP BY 的列没有索引, 产生长期表.
如果 GROUP BY 时,SELECT 的列不止 GROUP BY 列一个, 并且 GROUP BY 的列不是主键 , 产生长期表.
如果 GROUP BY 的列有索引,ORDER BY 的列没索引. 产生长期表.
如果 GROUP BY 的列和 ORDER BY 的列不一样, 即便都有索引也会产生长期表.
如果 GROUP BY 或 ORDER BY 的列不是来自 JOIN 语句第一个表. 会产生长期表.
如果 DISTINCT 和 ORDER BY 的列没有索引, 产生长期表.

一年之后零碎变慢怎么优化?

开启慢查问日志一周~ 两周(运维), 通过日志剖析工具 mysqldumpslow 针对对访问量多的数据和慢查问进行建设索引优化

MySQL 的慢查问日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应工夫超过阀值的语句，具体指运行工夫超过 long_query_time 值的 SQL，则会被记录到慢查问日志中。

具体指运行工夫超过 long_query_time 值的 SQL，则会被记录到慢查问日志中。long_query_time 的默认值为 10，意思是运行 10 秒以上的语句。

由他来查看哪些 SQL 超出了咱们的最大忍受工夫值，比方一条 sql 执行超过 5 秒钟，咱们就算慢 SQL，心愿能收集超过 5 秒的 sql，联合之前 explain 进行全面剖析。

默认状况下 slow_query_log 的值为 OFF，示意慢查问日志是禁用的，能够通过设置 slow_query_log 的值来开启

SHOW VARIABLES LIKE ‘%slow_query_log%’;

开启慢查问日志只对以后数据库失效，如果 MySQL 重启后则会生效

set global slow_query_log=1

如果要永恒失效，就必须批改配置文件 my.cnf（其它零碎变量也是如此）

批改 my.cnf 文件，[mysqld]下减少或批改参数, 而后重启 MySQL 服务器。

slow_query_log =1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log

slow_query_log_file，它指定慢查问日志文件的寄存门路，零碎默认会给一个缺省的文件 host_name-slow.log

开启慢查问日志后. 指定慢查问的工夫阈值long_query_time，默认状况下 long_query_time 的值为 10 秒，

SHOW VARIABLES LIKE ‘long_query_time%’;

能够应用命令批改，也能够在 my.cnf 参数外面批改

set long_query_time= 0.2

my.cnf
slow_query_log=1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3
log_output=FILE

在生产环境中，如果要手工剖析日志，查找、剖析 SQL，显然是个体力活，MySQL 提供了日志剖析工具mysqldumpslow。

mysqldumpslow –help

-a: 不将数字形象成 N，字符串形象成 S -s: 是示意依照何种形式排序；c: 拜访次数 l: 锁定工夫 r: 返回记录 t: 查问工夫 al: 均匀锁定工夫 ar: 均匀返回记录数 at: 均匀查问工夫 -t: 即为返回后面多少条的数据；-g: 后边搭配一个正则匹配模式，大小写不敏感的；

罕用参考:

失去返回记录集最多的 10 个 SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

失去拜访次数最多的 10 个 SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

失去依照工夫排序的前 10 条外面含有左连贯的查问语句
mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/atguigu-slow.log

另外倡议在应用这些命令时联合 | 和 more 应用，否则有可能呈现爆屏状况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

SHOW PROCESSLIST

显示过程列表

能干什么：查问所有用户正在干什么如果呈现不悦目的 kill [id]

是什么将一段查问 sql 封装为一个虚构的表。这个虚构表只保留了 sql 逻辑，不会保留任何查问后果。作用 1、封装简单 sql 语句，进步复用性 2、逻辑放在数据库下面，更新不须要公布程序，面对频繁的需要变更更灵便实用场景很多中央能够共用的一组查问后果报表统计数据常常变动, 将统计 sql 封装到一个视图中, 与程序解耦

创立

CREATE VIEW view_name
AS
SELECT column_name(s)
FROM table_name
WHERE condition

应用

select * from view_name

更新

CREATE OR REPLACE VIEW view_name
AS
SELECT column_name(s)
FROM table_name
WHERE condition

注意事项(实用 5.5) mysql 的视图中不容许有 from 前面的子查问，但 oracle 能够

复制的基本原理:

MySQL 复制过程分成三步：1 master 将扭转记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；2 slave 将 master 的 binary log events 拷贝到它的中继日志（relay log）；3 slave 重做中继日志中的事件，将扭转利用到本人的数据库中。MySQL 复制是异步的且串行化的

复制的根本准则 每个 slave 只有一个 master 每个 slave 只能有一个惟一的服务器 ID 每个 master 能够有多个 salve

复制的最大问题:IO 多, 延时

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

主机批改 my.ini 配置文件

主服务器惟一 ID

 server-id=1

启用二进制日志

log-bin= 本人本地的门路 /data/mysqlbin
log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin

设置不要复制的数据库

binlog-ignore-db=mysql

设置须要复制的数据库

binlog-do-db= 须要复制的主数据库名字

设置 logbin 格局

binlog_format=STATEMENT（默认）

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

从机配置文件批改 my.cnf 的 [mysqld] 栏位下

server-id = 2
relay-log=mysql-relay

因批改过配置文件，请主机 + 从机都重启后盾 mysql 服务, 主机从机都敞开防火墙

主机创立一个用户受权复制权限

GRANT REPLICATION SLAVE ON . TO ‘slave200404’@’%’ IDENTIFIED BY ‘123456’;

查看主机状态

SHOW MASTER STATUS;
|File | Positon | Binlog_Do_DB |Binlog_lgnore_DB
mysqlbin.000001 107 mydb_200404 mysql

File: 日志文件

Postion: 接入点

Binlog_Do_DB: 要复制的数据库

Binlog_lgnore_DB: 不要复制的数据库

从机对照主机状态拜大哥

CHANGE MASTER TO MASTER_HOST=’192.168.0.104′,
MASTER_USER=’slave200404′,
MASTER_PASSWORD=’123456′,
MASTER_LOG_FILE=’mysqlbin.000001′,MASTER_LOG_POS=107;

如果之前做过须要先进行再设置

stop slave;
reset master;

启动从服务器复制性能

start slave;

查看主从配置状态

show slave statusG;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
胜利

主机创立相应的数据库

CREATE DATABASE mydb_200404;

刷新从机呈现数据库胜利

数据库中间件, 前身是阿里的 cobar, 仅能够用作读写拆散、以及分表分库、多数据源整合, 容灾备份

基本原理:

Mycat 的原理中最重要的一个动词是“拦挡”，它拦挡了用户发送过去的 SQL 语句，首先对 SQL 语句做了一些特定的剖析：如分片剖析、路由剖析、读写拆散剖析、缓存剖析等，而后将此 SQL 发往后端的实在数据库，并将返回的后果做适当的解决，最终再返回给用户

这种形式把数据库的分布式从代码中解耦进去，Mycat 是代理，Mycat 前面就是物理数据库。和 Web 服务器的 Nginx 相似。对于使用者来说，拜访的都是 Mycat，不会接触到后端的数据库。

概念:

0、逻辑库 / 表：mycat 中存在的库和表

1、分片表：本来有大量数据的表

2、ER 表：关系表

3、全局表：相似于字典表这种表，字典表和很多表都有关联，mycat 采纳数据冗余存储

4、分片节点（dataNode）：一张大表分为多个数据库上，每个数据库就是分片节点

5、分片主机（dataHost）：分片节点能够在不同的主机，一个或者多个节点所在的主机就是分片主机

6、分片规定（rule）：数据划分的规定

7、全局序列号：数据切分后本来的主键就无奈应用了，因而须要引入一个值保证数据唯一性。

8、多租户（很重要）：多个环境下专用雷同的零碎，并且保障隔离性

8-1：独立数据库，隔离性高，然而代价比拟宏大

8-2：共享一个数据库，不是齐全隔离，隔离性不高，容易出错

8-3：共用数据结构，数据架构，通过 ID 进行辨别租户数据（也就是用 mycat）

简略来说，就是指通过某种特定的条件，将咱们寄存在同一个数据库中的数据扩散寄存到多个数据库（主机）下面，以达到扩散单台设施负载的成果

数据的切分（Sharding）依据其切分规定的类型，能够分为两种切分模式。一种是依照业务将表进行分类（或者 Schema）来切分到不同的数据库（主机）之上，这种切能够称之为数据的垂直（纵向）切分；

另外一种则是依据表中的数据的逻辑关系依照某个字段的某种规定，将同一个表中的数据依照某种条件拆分到多台数据库（主机）下面，这种切分称之为数据的程度（横向）切分。

linux 下能够下载 Mycat-server-xxxxx.linux.tar.gz 解压拷贝 mycat 目录到 /usr/local 目录

cp -r mycat /usr/local

目录如下：

阐明

bin

mycat 命令，启动、重启、进行等

catlet

catlet 为 Mycat 的一个扩大性能

conf

Mycat 配置信息, 重点关注

lib

Mycat 援用的 jar 包，Mycat 是 java 开发的

logs

日志文件，包含 Mycat 启动的日志和运行的日志。

配置文件:

文件

阐明

server.xml

Mycat 的配置文件，设置账号、参数等

schema.xml

Mycat 对应的物理数据库和数据库表的配置

rule.xml

Mycat 分片（分库分表）规定

schema.xml 定义逻辑库，表、分片节点等内容

将 </schema> 中的内容删除, 并加上 dataNode="dn1" 属性指定数据节点名称

<mycat:schema xmlns:mycat=”http://io.mycat/”>
<!– 逻辑库 name 名称, checkSQLschema SQL 查看优化 sqlMaxLimit 开端是否要加 limit xxx–>

    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    </schema>
<!-- 逻辑库    name 名称,   dataHost 援用的哪个 dataHost      database: 对应 mysql 的 database-->
    <dataNode name="dn1" dataHost="host1" database="mydb_200404" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                      writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            <!-- 心跳检测 -->
            <heartbeat>select user()</heartbeat>
            <!-- 写主机 can have multi write hosts -->
            <writeHost host="hostM1" url="192.168.0.104:3306" user="root"
                               password="000000">
                    <!-- 读主机 can have multi read hosts -->
                    <readHost host="hostS1" url="192.168.107.108:3306" user="root" password="000000" />
            </writeHost>
    </dataHost>

</mycat:schema>

schema:

属性

阐明

name

逻辑数据库名，与 server.xml 中的 schema 对应

checkSQLschema

数据库前缀相干设置，倡议看文档，这里临时设为 false

sqlMaxLimit

select 时默认的 limit，防止查问全表

table:

属性

阐明

name

表名，物理数据库中表名

dataNode

表存储到哪些节点，多个节点用逗号分隔。节点为下文 dataNode 设置的 name

primaryKey

主键字段名，主动生成主键时须要设置

autoIncrement

是否自增

rule

分片规定名，具体规定下文 rule 具体介绍

dataNode

属性

阐明

name

节点名，与 table 中 dataNode 对应

datahost

物理数据库名，与 datahost 中 name 对应

database

物理数据库中数据库名

dataHost

属性

阐明

name

物理数据库名，与 dataNode 中 dataHost 对应

balance

平衡负载的形式

writeType

写入形式

dbType

数据库类型

heartbeat

心跳检测语句，留神语句结尾的分号要加。

server.xml 配置 mycat 用户名明码

schemas: 数据库名，这里会和 schema.xml 中的配置关联，多个用逗号离开，例如须要这个用户须要治理两个数据库 db1,db2，则配置 db1,db2

            <property name="password">123456</property>
            <property name="schemas">TESTDB</property>

验证数据库拜访状况

mysql -uroot -p123123 -h 192.168.154.1 -P 3306
mysql -uroot -p123123 -h 192.168.154.154 -P 3306

如本机近程拜访报错，请建对应用户
grant all privileges on . to hzy@’%’ identified by ‘000000’;

启动程序

控制台启动：去 mycat/bin 目录下 mycat console
后盾启动：去 mycat/bin 目录下 mycat start

登录

后盾治理窗口(运维)

mysql -umycat -p654321 -P9066 -h192.168.67.131

命令

show database
show @@help

数据窗口(开发)

mysql -umycat -p123456 -P8066 -h192.168.107.108

连贯形式和一般数据库一样，如：jdbc:mysql://192.168.0.2:8066/

如果在启动时发现异常，在 logs 目录中查看日志。

wrapper.log 为程序启动的日志，启动时的问题看这个
mycat.log 为脚本执行时的日志，SQL 脚本执行报错后的具体谬误内容, 查看这个文件。mycat.log 是最新的谬误日志，历史日志会依据工夫生成目录保留。

查看库表

show databases;
DATABASE
TESTDB

mysql> use TESTDB;

mysql> show tables;
Tables_in_mydb_200404
mytable

负载平衡类型，目前的取值有 4 种：

balance=”0″, 不开启读写拆散机制，所有读操作都发送到以后可用的 writeHost 上。
balance=”1″，全副的 readHost 与 stand by writeHost 参加 select 语句的负载平衡，简略的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，失常状况下，M2,S1,S2 都参加 select 语句的负载平衡。
balance=”2″，所有读操作都随机的在 writeHost、readhost 上散发。
balance=”3″，所有读申请随机的散发到 readhost 执行，writerHost 不累赘读压力
<dataHost name=”host1″ maxCon=”1000″ minCon=”10″ balance=”2″

在主机执行

INSERT mytable(id,name) VALUES(2,@@hostname);

重启 mycat, 再查问

id	name
1	stig
2	dockerhost

2 rows in set (0.01 sec)

mysql> select * from mytable;
id	name
1	stig
2	HUAWEIMatebook

2 rows in set (0.00 sec)

mysql> select * from mytable;
id	name
1	stig
2	dockerhost

依照业务将表进行分类, 不须要 join 关系的表离开

<mycat:schema xmlns:mycat=”http://io.mycat/”>

<!-- 数据库配置，与 server.xml 中的数据库对应 -->
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
            <!-- 配置表分到哪个数据节点 -->
            <table name="customer" dataNode="dn2" ></table>
            <table name="orders" dataNode="dn1"></table>
    </schema>
    <!-- 分片配置 -->
    <dataNode name="dn1" dataHost="host1" database="orders__200405" />
    <dataNode name="dn2" dataHost="host2" database="orders_200405" />

   <!-- 物理数据库配置 -->
   <!--balance="0" 不配置读写拆散 -->
   <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                      writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <!-- can have multi write hosts -->
            <writeHost host="hostM1" url="192.168.0.104:3306" user="root"
                               password="000000">
            </writeHost>
    </dataHost>

<dataHost name=”host2″ maxCon=”1000″ minCon=”10″ balance=”0″

                      writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <!-- can have multi write hosts -->
            <writeHost host="hostM2" url="192.168.107.108:3306" user="hzy"
                               password="000000">
            </writeHost>
    </dataHost>

</mycat:schema>

schema.xml

<mycat:schema xmlns:mycat=”http://io.mycat/”>

<!-- 数据库配置，与 server.xml 中的数据库对应 -->
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
            <!-- 配置表分到哪个数据节点 -->
            <table name="customer" dataNode="dn2" ></table>
            <table name="orders" dataNode="dn1"></table>

<!– dictionary 写入了 dn1、dn2 两个节点, 分片的规定为：mod-long 源于 rule.xml 文件中 tableRule 的 name –>

            <table name="dictionary" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2"  rule="mod-long" />
    </schema>
    <!-- 分片配置 -->
    <dataNode name="dn1" dataHost="host1" database="orders__200405" />
    <dataNode name="dn2" dataHost="host2" database="orders_200405" />

   <!-- 物理数据库配置 -->
   <!--balance="0" 不配置读写拆散 -->
   <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                      writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <!-- can have multi write hosts -->
            <writeHost host="hostM1" url="192.168.0.104:3306" user="root"
                               password="000000">
            </writeHost>
    </dataHost>

<dataHost name=”host2″ maxCon=”1000″ minCon=”10″ balance=”0″

                      writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <!-- can have multi write hosts -->
            <writeHost host="hostM2" url="192.168.107.108:3306" user="hzy"
                               password="000000">
            </writeHost>
    </dataHost>

</mycat:schema>

rule.xml 定义分片规定

这个文件外面次要有 tableRule 和 function 这两个标签。在具体使用过程中能够依照需要增加 tableRule 和 function。

mod-long 就是将数据均匀拆分。因为是两台物理库，所以 rule.xml 中 mod-long 对应的 function count 为 2

         <rule>
                    <columns>customer_id</columns>
                    <algorithm>mod-long</algorithm>
         </rule>

</tableRule>

            <!-- 多少数据节点 -->
            <property name="count">2</property>
    </function>

tableRule

name 属性指定惟一的名字，用于标识不同的表规定
内嵌的 rule 标签则指定对物理表中的哪一列进行拆分 (columns) 和应用什么路由算法(algorithm)

algorithm 应用 function 标签中的 name 属性。连贯表规定和具体路由算法。当然，多个表规定能够连贯到同一个路由算法上。table 标签内应用。让逻辑表应用这个规定进行分片

function

name 指定算法的名字
class 制订路由算法具体的类名字
property 为具体算法须要用到的一些属性

罕用分片规定 分片枚举（hash-int）：在配置文件中配置可能呈现的枚举 id，配置分片固定分片 Hash 算法（func1）：二进制操作的求模运算范畴约定（rang-long）：提前规定好字段范畴属于哪个分片取模（mod-long）：依据 ID 进行 10 进制的求模运算日期分片（sharding-by-date）：依照工夫划分取模范畴（sharding-by-pattern）：依照取模运算和范畴运算联合利用指定（sharding-by-substring）：运行阶段有利用自主决定路由到那个分片

<function name=”murmur”

    class="io.mycat.route.function.PartitionByMurmurHash">
    <property name="seed">0</property><!-- 默认是 0 -->
    <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
    <property name="virtualBucketTimes">160</property><!-- 一个理论的数据库节点被映射为这么多虚构节点，默认是 160 倍，也就是虚构节点数是物理节点数的 160 倍 -->
    <!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，没有指定权重的节点默认是 1。以 properties 文件的格局填写，以从 0 开始到 count- 1 的整数值也就是节点索引为 key，以节点权重值为值。所有权重值必须是正整数，否则以 1 代替 -->
    <!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property> 
        用于测试时察看各物理节点与虚构节点的散布状况，如果指定了这个属性，会把虚构节点的 murmur hash 值与物理节点的映射按行输入到这个文件，没有默认值，如果不指定，就不会输入任何货色 -->
</function>

<function name="crc32slot"
          class="io.mycat.route.function.PartitionByCRC32PreSlot">
    <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
</function>
<function name="hash-int"
    class="io.mycat.route.function.PartitionByFileMap">
    <property name="mapFile">partition-hash-int.txt</property>
</function>
<function name="rang-long"
    class="io.mycat.route.function.AutoPartitionByLong">
    <property name="mapFile">autopartition-long.txt</property>
</function>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
    <!-- how many data nodes -->
    <property name="count">3</property>
</function>

<function name="func1" class="io.mycat.route.function.PartitionByLong">
    <property name="partitionCount">8</property>
    <property name="partitionLength">128</property>
</function>
<function name="latestMonth"
    class="io.mycat.route.function.LatestMonthPartion">
    <property name="splitOneDay">24</property>
</function>
<function name="partbymonth"
    class="io.mycat.route.function.PartitionByMonth">
    <property name="dateFormat">yyyy-MM-dd</property>
    <property name="sBeginDate">2015-01-01</property>
</function>

<function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
        <property name="mapFile">partition-range-mod.txt</property>
</function>

<function name="jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash">
    <property name="totalBuckets">3</property>
</function>---

title: MySQL 存储引擎和索引 # 题目
date: 2019/6/12 20:16:33 # 工夫
categories: # 分类

数据库
tags: # 标签
mysql
B+Tree

<!–more–>

利用 show profile 查看 sql 的执行周期

批改配置文件 /etc/my.cnf, 新增以下一行, 并重启 mysql

query_cache_type=1

开启 profiling:

mysql> show variables like '%profiling%';
mysql> set profiling=1;

显示最近的几次查问:

mysql> show profiles;
+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.00136600 | show variables like '%profiling%' |
|        2 | 0.00049975 | select * from mytbl2 where id = 2 |
+----------+------------+-----------------------------------+
查问 id       时长         sql

查看具体过程:

show profile cpu,block io for query 编号
就是上图的过程

查看存储引擎:

SHOW ENGINES;

1、InnoDB 存储引擎
InnoDB 是 MySQL 的默认事务型引擎，它被设计用来解决大量的短期(short-lived) 事务。除非有十分特地的起因须要应用其余的存储引擎，否则应该优先思考 InnoDB 引擎。

2、MyISAM 存储引擎
MyISAM 提供了大量的个性，包含全文索引、压缩、空间函数(GIS) 等，但 MyISAM 不反对事务和行级锁，有一个毫无疑问的缺点就是解体后无奈平安复原。

3、Archive 引擎
Archive 档案存储引擎只反对 INSERT 和 SELECT 操作，在 MySQL5.1 之前不反对索引。
Archive 表适宜日志和数据采集类利用。
依据英文的测试论断来看，Archive 表比 MyISAM 表要小大概 75%，比反对事务处理的 InnoDB 表小大概 83%。

4、Blackhole 引擎
Blackhole 引擎没有实现任何存储机制，它会抛弃所有插入的数据，不做任何保留。但服务器会记录 Blackhole 表的日志，所以能够用于复制数据到备库，或者简略地记录到日志。但这种利用形式会碰到很多问题，因而并不举荐。

5、CSV 引擎
CSV 引擎能够将一般的 CSV 文件作为 MySQL 的表来解决，但不反对索引。
CSV 引擎能够作为一种数据交换的机制，十分有用。
CSV 存储的数据间接能够在操作系统里，用文本编辑器，或者 excel 读取。

6、Memory 引擎
如果须要疾速地拜访数据，并且这些数据不会被批改，重启当前失落也没有关系，那么应用 Memory 表是十分有用。Memory 表至多比 MyISAM 表要快一个数量级。

7、Federated 引擎
Federated 引擎是拜访其余 MySQL 服务器的一个代理，只管该引擎看起来提供了一种很好的跨服务器的灵活性，但也常常带来问题，因而默认是禁用的。

罕用的有 MyISAM 和 InnoDB

它们的区别:

比照项	MyISAM	InnoDB
外键	不反对	反对
事务	不反对	反对
行表锁	表锁，即便操作一条记录也会锁住整个表，不适宜高并发的操作	行锁, 操作时只锁某一行，不对其它行有影响，适宜高并发的操作
缓存	只缓存索引，不缓存实在数据	不仅缓存索引还要缓存实在数据，对内存要求较高，而且内存大小对性能有决定性的影响
关注点	节俭资源、耗费少、简略业务	并发写、事务、更大资源

mysql 默认应用 InnoDB, 但 mysql 内置的零碎表应用 MyISAM, 因为没有高并发, 而且节俭资源.

mysql 单表瓶颈 500w 数据, 单库瓶颈 5000w 数据

MySQL 官网对索引的定义为:索引 (Index) 是帮忙 MySQL 高效获取数据的数据结构.
索引的目标在于进步查问效率，能够类比字典，

如果要查“mysql”这个单词，咱们必定须要定位到 m 字母，而后从下往下找到 y 字母，再找到剩下的 sql。

如果没有索引，那么你可能须要 a —-z，如果我想找到 Java 结尾的单词呢？或者 Oracle 结尾的单词呢？

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

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

劣势:

进步数据检索的效率, 升高数据库的 IO 老本
通过索引列对数据进行排序, 升高数据排序的老本, 升高了 CPU 的耗费

劣势:

尽管索引大大提高了查问速度, 同时却会升高更新表的速度(INSERT,UPDATE,DELETE), 因为更新表是,mysql 不仅要保留数据, 还要保留索引文件每次更新增加了索引列的字段, 都会调整因为更新所带来的键值变动后的索引信息
实际上索引也是一张表, 该表保留了主键与索引字段, 并指向实体表的记录, 所以索引也是要占用磁盘空间的

1. 索引

2. 红色方块代表指向数据的指针

3. 黄色代表向下节点的索引

比方要查找 98, 与 17,35 比照, 小于两者走 P1, 在它们之间走 P2, 大于它们走 P3, 以此类推找到最终的后果

1. 索引

2. 向下节点的索引

走到最终的叶子节点才有指向数据的指针

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+ 树的查问效率更加稳固
因为非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查问的门路长度雷同，导致每一个数据的查问效率相当。

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

聚簇索引并不是一种独自的索引类型，而是一种数据存储形式。
术语‘聚簇’示意数据行和相邻的键值聚簇的存储在一起。
如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。

聚簇索引的益处：
依照聚簇索引排列程序，查问显示肯定范畴数据的时候，因为数据都是严密相连，数据库不不必从多个数据块中提取数据，所以节俭了大量的 io 操作。
聚簇索引的限度：
对于 mysql 数据库目前只有 innodb 数据引擎反对聚簇索引，而 Myisam 并不反对聚簇索引。
因为数据物理存储排序形式只能有一种，所以每个 Mysql 的表只能有一个聚簇索引。个别状况下就是该表的主键。
为了充分利用聚簇索引的聚簇的个性，所以 innodb 表的主键列尽量选用有序的程序 id，而不倡议用无序的 id，比方 uuid 这种。

即一个索引只蕴含单个列, 一个表能够有多个单列索引

随表一起建索引：

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 ;

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，用于全文索引。

主键主动建设惟一索引
频繁作为查问条件的字段应该创立索引
查问中与其余表关联的字段, 外键关系应创立索引
单键 / 组合索引的抉择问题, 组合索引性价比更高
查问中排序的字段, 进步排序速度
查问中统计或者分组字段

表记录太少
常常增删改的表或者字段
where 条件里用不到的字段
过滤性不好的字段(如性别)

掂量是否须要加索引的真正指标

应用 EXPLAIN 关键字能够模仿优化器执行 SQL 查问语句, 从而晓得 MySQL 是如何解决你的 SQL 语句的, 剖析你的查问语句或是表构造的性能瓶颈.

通过 EXPLAIN 能够剖析出以下信息:

表的读取程序
哪些索引能够应用
数据读取操作的操作类型
哪些索引被理论应用
表之间的援用
每张表有多少行被物理查问

语法:

EXPLAIN SELECT SQL_NO_CACHE * FROM...

SQL_NO_CACHE: 敞开缓存

==id==:SELECT 查问的序列号, 蕴含一组数字, 示意查问中执行 SELECT 子句或操作表的程序.

三种状况:
- id 雷同, 执行程序由上至下
- id 不同, 如果是子查问,id 的序号会递增,id 值越大优先级越高
- id 雷同不同, 同时存在,id 如果雷同，能够认为是一组，从上往下程序执行；在所有组中，id 值越大，优先级越高，越先执行
关注点:
- id 号每个号码, 示意一趟独立的查问, 一个 sql 的查问趟数越少越好.

==type==: 显示查问应用了何种类型, 是较为重要的一个指标

后果值从最好到最坏顺次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保障查问至多达到 range 级别，最好能达到 ref。

类型	阐明
system	表只有一行记录（等于零碎表），这是 const 类型的特列，平时不会呈现，这个也能够忽略不计
const	示意通过索引一次就找到了,const 用于比拟 primary key 或者 unique 索引。因为只匹配一行数据，所以很快; 如将主键置于 where 列表中，MySQL 就能将该查问转换为一个常量
eq_ref	唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或惟一索引扫描
ref	非唯一性索引扫描，返回匹配某个独自值的所有行. 实质上也是一种索引拜访，它返回所有匹配某个独自值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
==range==	只检索给定范畴的行, 应用一个索引来抉择行。key 列显示应用了哪个索引; 个别就是在你的 where 语句中呈现了 between、<、>、in 等的查问; 这种范畴扫描索引扫描比全表扫描要好，因为它只须要开始于索引的某一点，而结束语另一点，不必扫描全副索引。
==index==	呈现 index 是 sql 应用了索引然而没用通过索引进行过滤，个别是应用了笼罩索引或者是利用索引进行了排序分组
==all==	Full Table Scan，将遍历全表以找到匹配的行
index_merge	在查问过程中须要多个索引组合应用，通常呈现在有 or 的关键字的 sql 中
ref_or_null	对于某个字段既须要关联条件，也须要 null 值得状况下。查问优化器会抉择用 ref_or_null 连贯查问。
index_subquery	利用索引来关联子查问，不再全表扫描
unique_subquery	该联接类型相似于 index_subquery。子查问中的惟一索引

==key_len==: 示意索引中应用的字节数，可通过该列计算查问中应用的索引的长度。key_len 字段可能帮你查看是否充沛的利用上了索引, 越大越好
==rows==:rows 列显示 MySQL 认为它执行查问时必须查看的行数。
越少越好
key: 理论应用的索引, 如果为 NULL, 则没有应用索引; 查问中若应用了笼罩索引，则该索引和查问的 select 字段重叠

==Extra==: 蕴含不适宜在其余列中显示但非常重要的额定信息

类型	阐明
==Using filesort==	阐明 mysql 会对数据应用一个内部的索引排序，而不是依照表内的索引程序进行读取。MySQL 中无奈利用索引实现的排序操作称为“文件排序”排序字段须要加索引
==Using temporay==	使了用长期表保留两头后果,MySQL 在对查问后果排序时应用长期表。常见于排序 order by 和分组查问 group by。分组字段须要加索引
USING index	示意相应的 select 操作中应用了笼罩索引(Covering Index)，防止拜访了表的数据行，效率不错！<br/> 如果同时呈现 using where，表明索引被用来执行索引键值的查找;<br/> 如果没有同时呈现 using where，表明索引只是用来读取数据而非利用索引执行查找。<br/> 利用索引进行了排序或分组
Using where	表明应用了 where 过滤
==using join buffer==	应用了连贯缓存,表之间的连贯条件须要加索引
impossible where	where 子句的值总是 false，不能用来获取任何元组,sql 不正确的揭示
select tables optimized away	在没有 GROUPBY 子句的状况下，基于索引优化 MIN/MAX 操作或者 <br/> 对于 MyISAM 存储引擎优化 COUNT(*)操作，不用等到执行阶段再进行计算，<br/> 查问执行打算生成的阶段即实现优化。

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

select_type: 查问的类型, 次要是用于区别一般查问, 联结查问, 子查问等简单查问

类型	阐明
SIMPLE	简略的 select 查问, 查问中不蕴含子查问或者 UNION
PRIMARY	查问中若蕴含任何简单的子局部，最外层查问则被标记为 Primary
DERIVED	在 FROM 列表中蕴含的子查问被标记为 DERIVED(衍生)MySQL 会递归执行这些子查问, 把后果放在长期表里。
SUBQUERY	在 SELECT 或 WHERE 列表中蕴含了子查问
DEPENDENT SUBQUERY	在 SELECT 或 WHERE 列表中蕴含了子查问, 子查问基于外层
UNCACHEABLE SUBQUERY	不能应用缓存的子查问, 通常是应用零碎变量作为 WHERE 条件的查问
UNION	若第二个 SELECT 呈现在 UNION 之后，则被标记为 UNION；若 UNION 蕴含在 FROM 子句的子查问中, 外层 SELECT 将被标记为：DERIVED
UNION RESULT	从 UNION 表获取后果的 SELECT

table: 显示这一行的数据是对于那张表的
partitions: 代表分区表中命中状况, 非辨别表, 该项为 null
possible_keys: 显示可能利用在这张表中的索引，一个或多个。
查问波及到的字段上若存在索引，则该索引将被列出，但不肯定被查问理论应用

mysql 索引寄存在 information_schema 元数据库 STATISTICS 统计表中

取出某个表的索引

SELECT INDEX_NAME FROM information_schema.statistics
WHERE TABLE_NAME='t_emp' AND TABLE_SCHEMA='mydb'
AND INDEX_NAME <> 'PRIMARY' AND SEQ_IN_INDEX=1
#排除主键索引

如何遍历这个索引汇合

CURSOR 游标
FETCH xxx INTO xxx

如何让 mysql 执行一个字符串

PREPARE 预编译 xxx
EXECUTE

用存储过程实现

DELIMITER $$
CREATE  PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
       DECLARE done INT DEFAULT 0;
       DECLARE ct INT DEFAULT 0;
       DECLARE _index VARCHAR(200) DEFAULT '';
       DECLARE _cur CURSOR FOR  SELECT   index_name   FROM information_schema.STATISTICS   WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND    index_name <>'PRIMARY'  ;
       DECLARE  CONTINUE HANDLER FOR NOT FOUND set done=2 ;      
        OPEN _cur;
        FETCH   _cur INTO _index;
        WHILE  _index<>'' DO 
               SET @str = CONCAT("drop index",_index,"on",tablename); 
               PREPARE sql_str FROM @str ;
               EXECUTE  sql_str;
               DEALLOCATE PREPARE sql_str;
               SET _index=''; 
               FETCH   _cur INTO _index; 
        END WHILE;
   CLOSE _cur;
   END$$

导致索引生效的细节

where 条件后的字段都应该建设索引, 达到最大索引命中

如果索引蕴含了多列，要恪守最左前缀法令。指的是查问从索引的最左前列开始并且不跳过索引中的列。

计算、函数、(主动 or 手动)类型转换，会导致索引生效而转向全表扫描, 如下:

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

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE   emp.name <>  'abc';

EXPLAIN SELECT * FROM emp WHERE age IS NULL
EXPLAIN SELECT * FROM emp WHERE age IS NOT NULL

含糊查问最好明确首字母

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name=123

留神实体类中的字段类型要与 mysql 中统一, 否则导致主动类型转换, 索引生效

对于单键索引，尽量抉择针对以后 query 过滤性更好的索引
在抉择组合索引的时候，以后 Query 中过滤性最好的字段在索引字段程序中，地位越靠前越好。
在抉择组合索引的时候，尽量抉择能够可能蕴含以后 query 中的 where 字句中更多字段的索引
在抉择组合索引的时候，如果某个字段可能呈现范畴查问时，尽量把这个字段放在索引秩序的最初面
书写 sql 语句时，尽量避免造成索引生效的状况

驱动表与被驱动表

1. 当应用 left join 时，左表是驱动表，右表是被驱动表
2. 当应用 right join 时，右表时驱动表，左表是驱动表
3. 当应用 join 时，mysql 会抉择数据量比拟小的表作为驱动表，大表作为被驱动表

在 sql 优化中，永远是以小表驱动大表

join 查问在有索引条件下:
驱动表有索引不会应用到索引
被驱动表建设索引会应用到索引

MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的后果集作为循环根底数据，而后一条一条地通过该后果集中的数据作为过滤条件到下一个表中查问数据，而后合并后果。

驱动表的全表扫描是无奈防止的, 所以应该为被驱动表建设索引

LEFT JOIN 时数据量小的表应该作为驱动表

INNER JOIN 时 mysql 会本人抉择驱动表

子查问尽量不要放在被驱动表，有可能应用不到索引

可能间接多表关联的尽量间接关联，不必子查问

STRAIGHT_JOIN 性能和 INNER JOIN 一样, 但可指定后面的表为驱动表,== 留神须要明确两张表的数量集 ==

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

SELECT * FROM emp a WHERE a.id NOT IN
(SELECT b.CEO FROM dept b WHERE b.CEO IS NOT NULL);

SELECT * FROM emp a LEFT JOIN dept b ON a.id = b.CEO
WHERE b.id IS NULL;

无过滤不索引 where 或 limit
程序错，必排序(using filesort) 优化器不会调整排序字段的程序
方向反必排序(using filesort) 多个排序字段, 要么都升序, 要么都降序

当范畴筛选条件和 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 前面的查问的字段。

Why

进步 Order By 的速度

Order by 时 select * 是一个大忌只 Query 须要的字段，这点十分重要。在这里的影响是：
1.1 当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改良后的算法——单路排序，否则用老算法——多路排序。
1.2 两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创立 tmp 文件进行合并排序，导致屡次 I /O，然而用单路排序算法的危险会更大一些, 所以要进步 sort_buffer_size。
尝试进步 sort_buffer_size
不论用哪种算法，进步这个参数都会提高效率，当然，要依据零碎的能力去进步，因为这个参数是针对每个过程的 1M-8M 之间调整
尝试进步 max_length_for_sort_data
进步这个参数，会减少用改良算法的概率。然而如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，显著症状是高的磁盘 I / O 流动和低的处理器使用率. 1024-8192 之间调整

GROUP BY 优化

group by 应用索引的准则简直跟 order by 统一，惟一区别是 group by 即便没有过滤条件用到索引，也能够间接应用索引。

最初应用索引的伎俩：笼罩索引

什么是笼罩索引？
简略说就是，select 到 from 之间查问的列 <= 应用的索引列 + 主键

所以最好不要应用 select * 应用明确的字段, 能够应用笼罩索引

剖析 GROUP BY 与长期表的关系 :

1. 如果 GROUP BY 的列没有索引, 产生长期表.
2. 如果 GROUP BY 时,SELECT 的列不止 GROUP BY 列一个, 并且 GROUP BY 的列不是主键 , 产生长期表.
3. 如果 GROUP BY 的列有索引,ORDER BY 的列没索引. 产生长期表.
4. 如果 GROUP BY 的列和 ORDER BY 的列不一样, 即便都有索引也会产生长期表.
5. 如果 GROUP BY 或 ORDER BY 的列不是来自 JOIN 语句第一个表. 会产生长期表.
6. 如果 DISTINCT 和 ORDER BY 的列没有索引, 产生长期表.

一年之后零碎变慢怎么优化?

开启慢查问日志一周~ 两周(运维), 通过日志剖析工具 mysqldumpslow 针对对访问量多的数据和慢查问进行建设索引优化

MySQL 的慢查问日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应工夫超过阀值的语句，具体指运行工夫超过 long_query_time 值的 SQL，则会被记录到慢查问日志中。

具体指运行工夫超过 long_query_time 值的 SQL，则会被记录到慢查问日志中。long_query_time 的默认值为 10，意思是运行 10 秒以上的语句。

由他来查看哪些 SQL 超出了咱们的最大忍受工夫值，比方一条 sql 执行超过 5 秒钟，咱们就算慢 SQL，心愿能收集超过 5 秒的 sql，联合之前 explain 进行全面剖析。

默认状况下 slow_query_log 的值为 OFF，示意慢查问日志是禁用的，
能够通过设置 slow_query_log 的值来开启

SHOW VARIABLES LIKE '%slow_query_log%';

开启慢查问日志只对以后数据库失效，如果 MySQL 重启后则会生效

set global slow_query_log=1

如果要永恒失效，就必须批改配置文件 my.cnf（其它零碎变量也是如此）

批改 my.cnf 文件，[mysqld]下减少或批改参数, 而后重启 MySQL 服务器。

slow_query_log =1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log

slow_query_log_file，它指定慢查问日志文件的寄存门路，零碎默认会给一个缺省的文件 host_name-slow.log

开启慢查问日志后. 指定慢查问的工夫阈值long_query_time，默认状况下 long_query_time 的值为 10 秒，

SHOW VARIABLES LIKE 'long_query_time%';

能够应用命令批改，也能够在 my.cnf 参数外面批改

set long_query_time= 0.2

my.cnf
slow_query_log=1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3
log_output=FILE

在生产环境中，如果要手工剖析日志，查找、剖析 SQL，显然是个体力活，MySQL 提供了日志剖析工具mysqldumpslow。

mysqldumpslow –help

-a: 不将数字形象成 N，字符串形象成 S
-s: 是示意依照何种形式排序；
c: 拜访次数
l: 锁定工夫
r: 返回记录
t: 查问工夫
al: 均匀锁定工夫
ar: 均匀返回记录数
at: 均匀查问工夫
-t: 即为返回后面多少条的数据；
-g: 后边搭配一个正则匹配模式，大小写不敏感的；

罕用参考:

失去返回记录集最多的 10 个 SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
 
失去拜访次数最多的 10 个 SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
 
失去依照工夫排序的前 10 条外面含有左连贯的查问语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
 
另外倡议在应用这些命令时联合 | 和 more 应用，否则有可能呈现爆屏状况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

SHOW PROCESSLIST

显示过程列表

能干什么：查问所有用户正在干什么
如果呈现不悦目的
kill [id]

是什么
将一段查问 sql 封装为一个虚构的表。
这个虚构表只保留了 sql 逻辑，不会保留任何查问后果。
作用
1、封装简单 sql 语句，进步复用性
2、逻辑放在数据库下面，更新不须要公布程序，面对频繁的需要变更更灵便
实用场景
很多中央能够共用的一组查问后果
报表统计数据常常变动, 将统计 sql 封装到一个视图中, 与程序解耦

创立

CREATE VIEW view_name 
AS
SELECT column_name(s)
FROM table_name
WHERE condition

应用

select * from view_name

更新

CREATE OR REPLACE VIEW view_name 
AS
SELECT column_name(s)
FROM table_name
WHERE condition

注意事项(实用 5.5)
mysql 的视图中不容许有 from 前面的子查问，但 oracle 能够

复制的基本原理:

MySQL 复制过程分成三步：
1 master 将扭转记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；
2 slave 将 master 的 binary log events 拷贝到它的中继日志（relay log）；
3 slave 重做中继日志中的事件，将扭转利用到本人的数据库中。MySQL 复制是异步的且串行化的

复制的根本准则
每个 slave 只有一个 master
每个 slave 只能有一个惟一的服务器 ID
每个 master 能够有多个 salve

复制的最大问题:IO 多, 延时

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

主机批改 my.ini 配置文件

主服务器惟一 ID
     server-id=1
启用二进制日志
    log-bin= 本人本地的门路 /data/mysqlbin
    log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin
设置不要复制的数据库
    binlog-ignore-db=mysql
设置须要复制的数据库
    binlog-do-db= 须要复制的主数据库名字
设置 logbin 格局
    binlog_format=STATEMENT（默认）

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

从机配置文件批改 my.cnf 的 [mysqld] 栏位下

 server-id = 2
 relay-log=mysql-relay

因批改过配置文件，请主机 + 从机都重启后盾 mysql 服务,
主机从机都敞开防火墙

主机创立一个用户受权复制权限

GRANT REPLICATION SLAVE ON *.* TO 'slave200404'@'%' IDENTIFIED BY '123456';

查看主机状态

SHOW MASTER STATUS;
|File          | Positon | Binlog_Do_DB |Binlog_lgnore_DB
mysqlbin.000001       107       mydb_200404      mysql

File: 日志文件

Postion: 接入点

Binlog_Do_DB: 要复制的数据库

Binlog_lgnore_DB: 不要复制的数据库

从机对照主机状态拜大哥

CHANGE MASTER TO MASTER_HOST='192.168.0.104',
MASTER_USER='slave200404',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysqlbin.000001',MASTER_LOG_POS=107;

如果之前做过须要先进行再设置

stop slave;
reset master;

启动从服务器复制性能

start slave;

查看主从配置状态

show slave status\G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
胜利

主机创立相应的数据库

CREATE DATABASE mydb_200404;

刷新从机呈现数据库胜利

数据库中间件, 前身是阿里的 cobar, 仅能够用作读写拆散、以及分表分库、多数据源整合, 容灾备份

基本原理:

Mycat 的原理中最重要的一个动词是“拦挡”，它拦挡了用户发送过去的 SQL 语句，首先对 SQL 语句做了
一些特定的剖析：如分片剖析、路由剖析、读写拆散剖析、缓存剖析等，而后将此 SQL 发往后端的实在数据库，
并将返回的后果做适当的解决，最终再返回给用户

这种形式把数据库的分布式从代码中解耦进去，Mycat 是代理，Mycat 前面就是物理数据库。和 Web 服务器的 Nginx 相似。对于使用者来说，拜访的都是 Mycat，不会接触到后端的数据库。

概念:

0、逻辑库 / 表：mycat 中存在的库和表

1、分片表：本来有大量数据的表

2、ER 表：关系表

3、全局表：相似于字典表这种表，字典表和很多表都有关联，mycat 采纳数据冗余存储

4、分片节点（dataNode）：一张大表分为多个数据库上，每个数据库就是分片节点

5、分片主机（dataHost）：分片节点能够在不同的主机，一个或者多个节点所在的主机就是分片主机

6、分片规定（rule）：数据划分的规定

7、全局序列号：数据切分后本来的主键就无奈应用了，因而须要引入一个值保证数据唯一性。

8、多租户（很重要）：多个环境下专用雷同的零碎，并且保障隔离性

8-1：独立数据库，隔离性高，然而代价比拟宏大

8-2：共享一个数据库，不是齐全隔离，隔离性不高，容易出错

8-3：共用数据结构，数据架构，通过 ID 进行辨别租户数据（也就是用 mycat）

简略来说，就是指通过某种特定的条件，将咱们寄存在同一个数据库中的数据扩散寄存到多个数据库（主机）
下面，以达到扩散单台设施负载的成果

数据的切分（Sharding）依据其切分规定的类型，能够分为两种切分模式。一种是依照业务将表进行分类（或者
Schema）来切分到不同的数据库（主机）之上，这种切能够称之为数据的垂直（纵向）切分；

另外一种则是依据表中的数据的逻辑关系依照某个字段的某种规定，将同一个表中的数据依照某种条件拆分到多台数据库（主机）下面，这种切分称之为数据的程度（横向）切分。

linux 下能够下载 Mycat-server-xxxxx.linux.tar.gz 解压拷贝 mycat 目录到 /usr/local 目录

cp -r mycat /usr/local

目录如下：

目录	阐明
bin	mycat 命令，启动、重启、进行等
catlet	catlet 为 Mycat 的一个扩大性能
conf	Mycat 配置信息, 重点关注
lib	Mycat 援用的 jar 包，Mycat 是 java 开发的
logs	日志文件，包含 Mycat 启动的日志和运行的日志。

配置文件:

文件	阐明
server.xml	Mycat 的配置文件，设置账号、参数等
schema.xml	Mycat 对应的物理数据库和数据库表的配置
rule.xml	Mycat 分片（分库分表）规定

schema.xml
定义逻辑库，表、分片节点等内容

将 </schema> 中的内容删除, 并加上 dataNode="dn1" 属性指定数据节点名称

<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 逻辑库    name 名称,   checkSQLschema SQL 查看优化       sqlMaxLimit 开端是否要加 limit xxx-->
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
    <!-- 逻辑库    name 名称,   dataHost 援用的哪个 dataHost      database: 对应 mysql 的 database-->
        <dataNode name="dn1" dataHost="host1" database="mydb_200404" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <!-- 心跳检测 -->
                <heartbeat>select user()</heartbeat>
                <!-- 写主机 can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.0.104:3306" user="root"
                                   password="000000">
                        <!-- 读主机 can have multi read hosts -->
                        <readHost host="hostS1" url="192.168.107.108:3306" user="root" password="000000" />
                </writeHost>
        </dataHost>
</mycat:schema>

schema:

属性	阐明
name	逻辑数据库名，与 server.xml 中的 schema 对应
checkSQLschema	数据库前缀相干设置，倡议看文档，这里临时设为 false
sqlMaxLimit	select 时默认的 limit，防止查问全表

table:

属性	阐明
name	表名，物理数据库中表名
dataNode	表存储到哪些节点，多个节点用逗号分隔。节点为下文 dataNode 设置的 name
primaryKey	主键字段名，主动生成主键时须要设置
autoIncrement	是否自增
rule	分片规定名，具体规定下文 rule 具体介绍

dataNode

属性	阐明
name	节点名，与 table 中 dataNode 对应
datahost	物理数据库名，与 datahost 中 name 对应
database	物理数据库中数据库名

dataHost

属性	阐明
name	物理数据库名，与 dataNode 中 dataHost 对应
balance	平衡负载的形式
writeType	写入形式
dbType	数据库类型
heartbeat	心跳检测语句，留神语句结尾的分号要加。

server.xml
配置 mycat 用户名明码

schemas: 数据库名，这里会和 schema.xml 中的配置关联，多个用逗号离开，例如须要这个用户须要治理两个数据库 db1,db2，则配置 db1,db2

# 批改用户名和 mysql 辨别开
<user name="mycat">
                <property name="password">123456</property>
                <property name="schemas">TESTDB</property>

验证数据库拜访状况

mysql -uroot -p123123 -h 192.168.154.1 -P 3306
mysql -uroot -p123123 -h 192.168.154.154 -P 3306

如本机近程拜访报错，请建对应用户
grant all privileges on *.* to hzy@'%'  identified by '000000';

启动程序

  控制台启动：去 mycat/bin 目录下 mycat console
  后盾启动：去 mycat/bin 目录下 mycat start

登录

后盾治理窗口(运维)
    mysql -umycat -p654321 -P9066 -h192.168.67.131
命令
    show database
    show @@help
数据窗口(开发)
    mysql -umycat -p123456 -P8066 -h192.168.107.108
连贯形式和一般数据库一样，如：jdbc:mysql://192.168.0.2:8066/

如果在启动时发现异常，在 logs 目录中查看日志。

wrapper.log 为程序启动的日志，启动时的问题看这个
mycat.log 为脚本执行时的日志，SQL 脚本执行报错后的具体谬误内容, 查看这个文件。mycat.log 是最新的谬误日志，历史日志会依据工夫生成目录保留。

查看库表

 show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+

mysql> use TESTDB;

mysql> show tables;
+-----------------------+
| Tables_in_mydb_200404 |
+-----------------------+
| mytable               |
+-----------------------+

负载平衡类型，目前的取值有 4 种：1. balance="0", 不开启读写拆散机制，所有读操作都发送到以后可用的 writeHost 上。2. balance="1"，全副的 readHost 与 stand by writeHost 参加 select 语句的负载平衡，简略的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，失常状况下，M2,S1,S2 都参加 select 语句的负载平衡。3. balance="2"，所有读操作都随机的在 writeHost、readhost 上散发。4. balance="3"，所有读申请随机的散发到 readhost 执行，writerHost 不累赘读压力
 <dataHost name="host1" maxCon="1000" minCon="10" balance="2"

在主机执行

INSERT mytable(id,`name`) VALUES(2,@@hostname);

重启 mycat, 再查问

+------+------------+
| id   | name       |
+------+------------+
|    1 | stig       |
|    2 | dockerhost |
+------+------------+
2 rows in set (0.01 sec)

mysql> select * from mytable;
+------+----------------+
| id   | name           |
+------+----------------+
|    1 | stig           |
|    2 | HUAWEIMatebook |
+------+----------------+
2 rows in set (0.00 sec)

mysql> select * from mytable;
+------+------------+
| id   | name       |
+------+------------+
|    1 | stig       |
|    2 | dockerhost |
+------+------------+

依照业务将表进行分类, 不须要 join 关系的表离开

<mycat:schema xmlns:mycat="http://io.mycat/">

    <!-- 数据库配置，与 server.xml 中的数据库对应 -->
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
                <!-- 配置表分到哪个数据节点 -->
                <table name="customer" dataNode="dn2" ></table>
                <table name="orders" dataNode="dn1"></table>
        </schema>
        <!-- 分片配置 -->
        <dataNode name="dn1" dataHost="host1" database="orders__200405" />
        <dataNode name="dn2" dataHost="host2" database="orders_200405" />
    
       <!-- 物理数据库配置 -->
       <!--balance="0" 不配置读写拆散 -->
       <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.0.104:3306" user="root"
                                   password="000000">
                </writeHost>
        </dataHost>
        
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM2" url="192.168.107.108:3306" user="hzy"
                                   password="000000">
                </writeHost>
        </dataHost>

</mycat:schema>

schema.xml

<mycat:schema xmlns:mycat="http://io.mycat/">

    <!-- 数据库配置，与 server.xml 中的数据库对应 -->
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
                <!-- 配置表分到哪个数据节点 -->
                <table name="customer" dataNode="dn2" ></table>
                <table name="orders" dataNode="dn1"></table>
<!-- dictionary 写入了 dn1、dn2 两个节点, 分片的规定为：mod-long 源于 rule.xml 文件中 tableRule 的 name -->
                <table name="dictionary" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2"  rule="mod-long" />
        </schema>
        <!-- 分片配置 -->
        <dataNode name="dn1" dataHost="host1" database="orders__200405" />
        <dataNode name="dn2" dataHost="host2" database="orders_200405" />
    
       <!-- 物理数据库配置 -->
       <!--balance="0" 不配置读写拆散 -->
       <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.0.104:3306" user="root"
                                   password="000000">
                </writeHost>
        </dataHost>
        
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM2" url="192.168.107.108:3306" user="hzy"
                                   password="000000">
                </writeHost>
        </dataHost>

</mycat:schema>

rule.xml
定义分片规定

这个文件外面次要有 tableRule 和 function 这两个标签。在具体使
用过程中能够依照需要增加 tableRule 和 function。

mod-long 就是将数据均匀拆分。因为是两台物理库，所以 rule.xml 中 mod-long 对应的 function count 为 2

 
<tableRule name="mod_rule">
             <rule>
                        <columns>customer_id</columns>
                        <algorithm>mod-long</algorithm>
             </rule>
</tableRule>
 
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- 多少数据节点 -->
                <property name="count">2</property>
        </function>

tableRule

name 属性指定惟一的名字，用于标识不同的表规定
内嵌的 rule 标签则指定对物理表中的哪一列进行拆分 (columns) 和应用什么路由算法(algorithm)

algorithm 应用 function 标签中的 name 属性。连贯表规定和具体路由算法。当然，多个表规定能够连贯到
同一个路由算法上。table 标签内应用。让逻辑表应用这个规定进行分片

function

name 指定算法的名字
class 制订路由算法具体的类名字
property 为具体算法须要用到的一些属性

罕用分片规定

   分片枚举（hash-int）：在配置文件中配置可能呈现的枚举 id，配置分片
   固定分片 Hash 算法（func1）：二进制操作的求模运算
   范畴约定（rang-long）：提前规定好字段范畴属于哪个分片
   取模（mod-long）：依据 ID 进行 10 进制的求模运算
   日期分片（sharding-by-date）：依照工夫划分
   取模范畴（sharding-by-pattern）：依照取模运算和范畴运算联合
   利用指定（sharding-by-substring）：运行阶段有利用自主决定路由到那个分片

<function name="murmur"
        class="io.mycat.route.function.PartitionByMurmurHash">
        <property name="seed">0</property><!-- 默认是 0 -->
        <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
        <property name="virtualBucketTimes">160</property><!-- 一个理论的数据库节点被映射为这么多虚构节点，默认是 160 倍，也就是虚构节点数是物理节点数的 160 倍 -->
        <!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，没有指定权重的节点默认是 1。以 properties 文件的格局填写，以从 0 开始到 count- 1 的整数值也就是节点索引为 key，以节点权重值为值。所有权重值必须是正整数，否则以 1 代替 -->
        <!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property> 
            用于测试时察看各物理节点与虚构节点的散布状况，如果指定了这个属性，会把虚构节点的 murmur hash 值与物理节点的映射按行输入到这个文件，没有默认值，如果不指定，就不会输入任何货色 -->
    </function>

    <function name="crc32slot"
              class="io.mycat.route.function.PartitionByCRC32PreSlot">
        <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
    </function>
    <function name="hash-int"
        class="io.mycat.route.function.PartitionByFileMap">
        <property name="mapFile">partition-hash-int.txt</property>
    </function>
    <function name="rang-long"
        class="io.mycat.route.function.AutoPartitionByLong">
        <property name="mapFile">autopartition-long.txt</property>
    </function>
    <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
        <!-- how many data nodes -->
        <property name="count">3</property>
    </function>

    <function name="func1" class="io.mycat.route.function.PartitionByLong">
        <property name="partitionCount">8</property>
        <property name="partitionLength">128</property>
    </function>
    <function name="latestMonth"
        class="io.mycat.route.function.LatestMonthPartion">
        <property name="splitOneDay">24</property>
    </function>
    <function name="partbymonth"
        class="io.mycat.route.function.PartitionByMonth">
        <property name="dateFormat">yyyy-MM-dd</property>
        <property name="sBeginDate">2015-01-01</property>
    </function>
    
    <function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
            <property name="mapFile">partition-range-mod.txt</property>
    </function>
    
    <function name="jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash">
        <property name="totalBuckets">3</property>
    </function>

title: MySQL 存储引擎和索引 # 题目
date: 2019/6/12 20:16:33 # 工夫
categories: # 分类

数据库
tags: # 标签
mysql
B+Tree

<!–more–>

利用 show profile 查看 sql 的执行周期

批改配置文件 /etc/my.cnf, 新增以下一行, 并重启 mysql

query_cache_type=1

开启 profiling:

mysql> show variables like ‘%profiling%’;
mysql> set profiling=1;

显示最近的几次查问:

mysql> show profiles;
Query_ID	Duration	Query
1	0.00136600	show variables like ‘%profiling%’
2	0.00049975	select * from mytbl2 where id = 2

查问 id 时长 sql

查看具体过程:

show profile cpu,block io for query 编号
就是上图的过程

查看存储引擎:

SHOW ENGINES;

1、InnoDB 存储引擎 InnoDB 是 MySQL 的默认事务型引擎，它被设计用来解决大量的短期(short-lived) 事务。除非有十分特地的起因须要应用其余的存储引擎，否则应该优先思考 InnoDB 引擎。

2、MyISAM 存储引擎 MyISAM 提供了大量的个性，包含全文索引、压缩、空间函数(GIS) 等，但 MyISAM 不反对事务和行级锁，有一个毫无疑问的缺点就是解体后无奈平安复原。

3、Archive 引擎 Archive 档案存储引擎只反对 INSERT 和 SELECT 操作，在 MySQL5.1 之前不反对索引。Archive 表适宜日志和数据采集类利用。依据英文的测试论断来看，Archive 表比 MyISAM 表要小大概 75%，比反对事务处理的 InnoDB 表小大概 83%。

4、Blackhole 引擎 Blackhole 引擎没有实现任何存储机制，它会抛弃所有插入的数据，不做任何保留。但服务器会记录 Blackhole 表的日志，所以能够用于复制数据到备库，或者简略地记录到日志。但这种利用形式会碰到很多问题，因而并不举荐。

5、CSV 引擎 CSV 引擎能够将一般的 CSV 文件作为 MySQL 的表来解决，但不反对索引。CSV 引擎能够作为一种数据交换的机制，十分有用。CSV 存储的数据间接能够在操作系统里，用文本编辑器，或者 excel 读取。

6、Memory 引擎 如果须要疾速地拜访数据，并且这些数据不会被批改，重启当前失落也没有关系，那么应用 Memory 表是十分有用。Memory 表至多比 MyISAM 表要快一个数量级。

7、Federated 引擎 Federated 引擎是拜访其余 MySQL 服务器的一个代理，只管该引擎看起来提供了一种很好的跨服务器的灵活性，但也常常带来问题，因而默认是禁用的。

罕用的有 MyISAM 和 InnoDB

它们的区别:

比照项

MyISAM

InnoDB

外键

不反对

反对

事务

不反对

反对

行表锁

表锁，即便操作一条记录也会锁住整个表，不适宜高并发的操作

行锁, 操作时只锁某一行，不对其它行有影响，适宜高并发的操作

缓存

只缓存索引，不缓存实在数据

不仅缓存索引还要缓存实在数据，对内存要求较高，而且内存大小对性能有决定性的影响

关注点

节俭资源、耗费少、简略业务

并发写、事务、更大资源

mysql 默认应用 InnoDB, 但 mysql 内置的零碎表应用 MyISAM, 因为没有高并发, 而且节俭资源.

mysql 单表瓶颈 500w 数据, 单库瓶颈 5000w 数据

MySQL 官网对索引的定义为:索引 (Index) 是帮忙 MySQL 高效获取数据的数据结构. 索引的目标在于进步查问效率，能够类比字典，

如果要查“mysql”这个单词，咱们必定须要定位到 m 字母，而后从下往下找到 y 字母，再找到剩下的 sql。

如果没有索引，那么你可能须要 a —-z，如果我想找到 Java 结尾的单词呢？或者 Oracle 结尾的单词呢？

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

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

劣势:

进步数据检索的效率, 升高数据库的 IO 老本
通过索引列对数据进行排序, 升高数据排序的老本, 升高了 CPU 的耗费

劣势:

尽管索引大大提高了查问速度, 同时却会升高更新表的速度(INSERT,UPDATE,DELETE), 因为更新表是,mysql 不仅要保留数据, 还要保留索引文件每次更新增加了索引列的字段, 都会调整因为更新所带来的键值变动后的索引信息
实际上索引也是一张表, 该表保留了主键与索引字段, 并指向实体表的记录, 所以索引也是要占用磁盘空间的

1. 索引

2. 红色方块代表指向数据的指针

3. 黄色代表向下节点的索引

比方要查找 98, 与 17,35 比照, 小于两者走 P1, 在它们之间走 P2, 大于它们走 P3, 以此类推找到最终的后果

1. 索引

2. 向下节点的索引

走到最终的叶子节点才有指向数据的指针

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+ 树的查问效率更加稳固因为非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查问的门路长度雷同，导致每一个数据的查问效率相当。

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

聚簇索引并不是一种独自的索引类型，而是一种数据存储形式。术语‘聚簇’示意数据行和相邻的键值聚簇的存储在一起。如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。

聚簇索引的益处：依照聚簇索引排列程序，查问显示肯定范畴数据的时候，因为数据都是严密相连，数据库不不必从多个数据块中提取数据，所以节俭了大量的 io 操作。聚簇索引的限度：对于 mysql 数据库目前只有 innodb 数据引擎反对聚簇索引，而 Myisam 并不反对聚簇索引。因为数据物理存储排序形式只能有一种，所以每个 Mysql 的表只能有一个聚簇索引。个别状况下就是该表的主键。为了充分利用聚簇索引的聚簇的个性，所以 innodb 表的主键列尽量选用有序的程序 id，而不倡议用无序的 id，比方 uuid 这种。

即一个索引只蕴含单个列, 一个表能够有多个单列索引

随表一起建索引：

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 ;

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，用于全文索引。

主键主动建设惟一索引
频繁作为查问条件的字段应该创立索引
查问中与其余表关联的字段, 外键关系应创立索引
单键 / 组合索引的抉择问题, 组合索引性价比更高
查问中排序的字段, 进步排序速度
查问中统计或者分组字段

表记录太少
常常增删改的表或者字段
where 条件里用不到的字段
过滤性不好的字段(如性别)

掂量是否须要加索引的真正指标

应用 EXPLAIN 关键字能够模仿优化器执行 SQL 查问语句, 从而晓得 MySQL 是如何解决你的 SQL 语句的, 剖析你的查问语句或是表构造的性能瓶颈.

通过 EXPLAIN 能够剖析出以下信息:

表的读取程序
哪些索引能够应用
数据读取操作的操作类型
哪些索引被理论应用
表之间的援用
每张表有多少行被物理查问

语法:

EXPLAIN SELECT SQL_NO_CACHE * FROM…

SQL_NO_CACHE: 敞开缓存

id:SELECT 查问的序列号, 蕴含一组数字, 示意查问中执行 SELECT 子句或操作表的程序.

三种状况:
- id 雷同, 执行程序由上至下
- id 不同, 如果是子查问,id 的序号会递增,id 值越大优先级越高
- id 雷同不同, 同时存在,id 如果雷同，能够认为是一组，从上往下程序执行；在所有组中，id 值越大，优先级越高，越先执行


关注点:

*   id 号每个号码, 示意一趟独立的查问, 一个 sql 的查问趟数越少越好.

type: 显示查问应用了何种类型, 是较为重要的一个指标

后果值从最好到最坏顺次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保障查问至多达到 range 级别，最好能达到 ref。

类型

阐明

system

表只有一行记录（等于零碎表），这是 const 类型的特列，平时不会呈现，这个也能够忽略不计

const

示意通过索引一次就找到了,const 用于比拟 primary key 或者 unique 索引。因为只匹配一行数据，所以很快; 如将主键置于 where 列表中，MySQL 就能将该查问转换为一个常量

eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或惟一索引扫描

ref

非唯一性索引扫描，返回匹配某个独自值的所有行. 实质上也是一种索引拜访，它返回所有匹配某个独自值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

range

只检索给定范畴的行, 应用一个索引来抉择行。key 列显示应用了哪个索引; 个别就是在你的 where 语句中呈现了 between、<、>、in 等的查问; 这种范畴扫描索引扫描比全表扫描要好，因为它只须要开始于索引的某一点，而结束语另一点，不必扫描全副索引。

index

呈现 index 是 sql 应用了索引然而没用通过索引进行过滤，个别是应用了笼罩索引或者是利用索引进行了排序分组

all

Full Table Scan，将遍历全表以找到匹配的行

index_merge

在查问过程中须要多个索引组合应用，通常呈现在有 or 的关键字的 sql 中

ref_or_null

对于某个字段既须要关联条件，也须要 null 值得状况下。查问优化器会抉择用 ref_or_null 连贯查问。

index_subquery

利用索引来关联子查问，不再全表扫描

unique_subquery

该联接类型相似于 index_subquery。子查问中的惟一索引
key_len: 示意索引中应用的字节数，可通过该列计算查问中应用的索引的长度。key_len 字段可能帮你查看是否充沛的利用上了索引, 越大越好
rows:rows 列显示 MySQL 认为它执行查问时必须查看的行数。越少越好
key: 理论应用的索引, 如果为 NULL, 则没有应用索引; 查问中若应用了笼罩索引，则该索引和查问的 select 字段重叠
Extra: 蕴含不适宜在其余列中显示但非常重要的额定信息

类型

阐明

Using filesort

阐明 mysql 会对数据应用一个内部的索引排序，而不是依照表内的索引程序进行读取。MySQL 中无奈利用索引实现的排序操作称为“文件排序”排序字段须要加索引

Using temporay

使了用长期表保留两头后果,MySQL 在对查问后果排序时应用长期表。常见于排序 order by 和分组查问 group by。分组字段须要加索引

USING index

示意相应的 select 操作中应用了笼罩索引(Covering Index)，防止拜访了表的数据行，效率不错！如果同时呈现 using where，表明索引被用来执行索引键值的查找; 如果没有同时呈现 using where，表明索引只是用来读取数据而非利用索引执行查找。利用索引进行了排序或分组

Using where

表明应用了 where 过滤

using join buffer

应用了连贯缓存,表之间的连贯条件须要加索引

impossible where

where 子句的值总是 false，不能用来获取任何元组,sql 不正确的揭示

select tables optimized away

在没有 GROUPBY 子句的状况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*)操作，不用等到执行阶段再进行计算，查问执行打算生成的阶段即实现优化。
ref: 显示索引的哪一列被应用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
select_type: 查问的类型, 次要是用于区别一般查问, 联结查问, 子查问等简单查问

类型

阐明

SIMPLE

简略的 select 查问, 查问中不蕴含子查问或者 UNION

PRIMARY

查问中若蕴含任何简单的子局部，最外层查问则被标记为 Primary

DERIVED

在 FROM 列表中蕴含的子查问被标记为 DERIVED(衍生)MySQL 会递归执行这些子查问, 把后果放在长期表里。

SUBQUERY

在 SELECT 或 WHERE 列表中蕴含了子查问

DEPENDENT SUBQUERY

在 SELECT 或 WHERE 列表中蕴含了子查问, 子查问基于外层

UNCACHEABLE SUBQUERY

不能应用缓存的子查问, 通常是应用零碎变量作为 WHERE 条件的查问

UNION

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

UNION RESULT

从 UNION 表获取后果的 SELECT
table: 显示这一行的数据是对于那张表的
partitions: 代表分区表中命中状况, 非辨别表, 该项为 null
possible_keys: 显示可能利用在这张表中的索引，一个或多个。查问波及到的字段上若存在索引，则该索引将被列出，但不肯定被查问理论应用

mysql 索引寄存在 information_schema 元数据库 STATISTICS 统计表中

取出某个表的索引

SELECT INDEX_NAME FROM information_schema.statistics
WHERE TABLE_NAME=’t_emp’ AND TABLE_SCHEMA=’mydb’
AND INDEX_NAME <> ‘PRIMARY’ AND SEQ_IN_INDEX=1

如何遍历这个索引汇合

CURSOR 游标
FETCH xxx INTO xxx

如何让 mysql 执行一个字符串

PREPARE 预编译 xxx
EXECUTE

用存储过程实现

DELIMITER $$
CREATE PROCEDURE proc_drop_index(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN

   DECLARE done INT DEFAULT 0;
   DECLARE ct INT DEFAULT 0;
   DECLARE _index VARCHAR(200) DEFAULT '';
   DECLARE _cur CURSOR FOR  SELECT   index_name   FROM information_schema.STATISTICS   WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND    index_name <>'PRIMARY'  ;
   DECLARE  CONTINUE HANDLER FOR NOT FOUND set done=2 ;      
    OPEN _cur;
    FETCH   _cur INTO _index;
    WHILE  _index<>'' DO 
           SET @str = CONCAT("drop index",_index,"on",tablename); 
           PREPARE sql_str FROM @str ;
           EXECUTE  sql_str;
           DEALLOCATE PREPARE sql_str;
           SET _index=''; 
           FETCH   _cur INTO _index; 
    END WHILE;

CLOSE _cur;
END$$

导致索引生效的细节

where 条件后的字段都应该建设索引, 达到最大索引命中

如果索引蕴含了多列，要恪守最左前缀法令。指的是查问从索引的最左前列开始并且不跳过索引中的列。

计算、函数、(主动 or 手动)类型转换，会导致索引生效而转向全表扫描, 如下:

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

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <> ‘abc’;

EXPLAIN SELECT * FROM emp WHERE age IS NULL
EXPLAIN SELECT * FROM emp WHERE age IS NOT NULL

含糊查问最好明确首字母

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name=123

留神实体类中的字段类型要与 mysql 中统一, 否则导致主动类型转换, 索引生效

对于单键索引，尽量抉择针对以后 query 过滤性更好的索引
在抉择组合索引的时候，以后 Query 中过滤性最好的字段在索引字段程序中，地位越靠前越好。
在抉择组合索引的时候，尽量抉择能够可能蕴含以后 query 中的 where 字句中更多字段的索引
在抉择组合索引的时候，如果某个字段可能呈现范畴查问时，尽量把这个字段放在索引秩序的最初面
书写 sql 语句时，尽量避免造成索引生效的状况

驱动表与被驱动表

1. 当应用 left join 时，左表是驱动表，右表是被驱动表 2. 当应用 right join 时，右表时驱动表，左表是驱动表 3. 当应用 join 时，mysql 会抉择数据量比拟小的表作为驱动表，大表作为被驱动表

在 sql 优化中，永远是以小表驱动大表

join 查问在有索引条件下: 驱动表有索引不会应用到索引被驱动表建设索引会应用到索引

MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的后果集作为循环根底数据，而后一条一条地通过该后果集中的数据作为过滤条件到下一个表中查问数据，而后合并后果。

驱动表的全表扫描是无奈防止的, 所以应该为被驱动表建设索引

LEFT JOIN 时数据量小的表应该作为驱动表

INNER JOIN 时 mysql 会本人抉择驱动表

子查问尽量不要放在被驱动表，有可能应用不到索引

可能间接多表关联的尽量间接关联，不必子查问

STRAIGHT_JOIN 性能和 INNER JOIN 一样, 但可指定后面的表为驱动表, 留神须要明确两张表的数量集

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

SELECT * FROM emp a WHERE a.id NOT IN
(SELECT b.CEO FROM dept b WHERE b.CEO IS NOT NULL);

SELECT * FROM emp a LEFT JOIN dept b ON a.id = b.CEO
WHERE b.id IS NULL;

无过滤不索引 where 或 limit
程序错，必排序(using filesort) 优化器不会调整排序字段的程序
方向反必排序(using filesort) 多个排序字段, 要么都升序, 要么都降序

当范畴筛选条件和 group by 或者 order by 的字段呈现二选一时，优先察看条件字段的过滤数量，如果过滤的数据足够多，而须要排序的数据并不多时，优先把索引放在范畴字段上。反之，亦然。

起因是所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很耗费性能，即便索引优化了排序但理论晋升性能很无限。绝对的范畴筛选条件如果没有用到索引的话，要对几万条的数据进行扫描，这是十分耗费性能的

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

双路排序 MySQL 4.1 之前是应用双路排序, 字面意思就是两次扫描磁盘，最终失去数据，读取行指针和 orderby 列，对他们进行排序，而后扫描曾经排序好的列表，依照列表中的值从新从列表中读取对应的数据输入从磁盘取排序字段，在 buffer 进行排序，再从磁盘取其余字段。取一批数据，要对磁盘进行了两次扫描，家喻户晓，IO 是很耗时的，所以在 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 前面的查问的字段。

Why

进步 Order By 的速度

Order by 时 select * 是一个大忌只 Query 须要的字段，这点十分重要。在这里的影响是：1.1 当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改良后的算法——单路排序，否则用老算法——多路排序。1.2 两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创立 tmp 文件进行合并排序，导致屡次 I /O，然而用单路排序算法的危险会更大一些, 所以要进步 sort_buffer_size。
尝试进步 sort_buffer_size 不论用哪种算法，进步这个参数都会提高效率，当然，要依据零碎的能力去进步，因为这个参数是针对每个过程的 1M-8M 之间调整
尝试进步 max_length_for_sort_data 进步这个参数，会减少用改良算法的概率。然而如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，显著症状是高的磁盘 I / O 流动和低的处理器使用率. 1024-8192 之间调整

GROUP BY 优化

group by 应用索引的准则简直跟 order by 统一，惟一区别是 group by 即便没有过滤条件用到索引，也能够间接应用索引。

最初应用索引的伎俩：笼罩索引

什么是笼罩索引？简略说就是，select 到 from 之间查问的列 <= 应用的索引列 + 主键

所以最好不要应用 select * 应用明确的字段, 能够应用笼罩索引

剖析 GROUP BY 与长期表的关系 :

如果 GROUP BY 的列没有索引, 产生长期表.
如果 GROUP BY 时,SELECT 的列不止 GROUP BY 列一个, 并且 GROUP BY 的列不是主键 , 产生长期表.
如果 GROUP BY 的列有索引,ORDER BY 的列没索引. 产生长期表.
如果 GROUP BY 的列和 ORDER BY 的列不一样, 即便都有索引也会产生长期表.
如果 GROUP BY 或 ORDER BY 的列不是来自 JOIN 语句第一个表. 会产生长期表.
如果 DISTINCT 和 ORDER BY 的列没有索引, 产生长期表.

一年之后零碎变慢怎么优化?

开启慢查问日志一周~ 两周(运维), 通过日志剖析工具 mysqldumpslow 针对对访问量多的数据和慢查问进行建设索引优化

MySQL 的慢查问日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应工夫超过阀值的语句，具体指运行工夫超过 long_query_time 值的 SQL，则会被记录到慢查问日志中。

具体指运行工夫超过 long_query_time 值的 SQL，则会被记录到慢查问日志中。long_query_time 的默认值为 10，意思是运行 10 秒以上的语句。

由他来查看哪些 SQL 超出了咱们的最大忍受工夫值，比方一条 sql 执行超过 5 秒钟，咱们就算慢 SQL，心愿能收集超过 5 秒的 sql，联合之前 explain 进行全面剖析。

默认状况下 slow_query_log 的值为 OFF，示意慢查问日志是禁用的，能够通过设置 slow_query_log 的值来开启

SHOW VARIABLES LIKE ‘%slow_query_log%’;

开启慢查问日志只对以后数据库失效，如果 MySQL 重启后则会生效

set global slow_query_log=1

如果要永恒失效，就必须批改配置文件 my.cnf（其它零碎变量也是如此）

批改 my.cnf 文件，[mysqld]下减少或批改参数, 而后重启 MySQL 服务器。

slow_query_log =1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log

slow_query_log_file，它指定慢查问日志文件的寄存门路，零碎默认会给一个缺省的文件 host_name-slow.log

开启慢查问日志后. 指定慢查问的工夫阈值long_query_time，默认状况下 long_query_time 的值为 10 秒，

SHOW VARIABLES LIKE ‘long_query_time%’;

能够应用命令批改，也能够在 my.cnf 参数外面批改

set long_query_time= 0.2

my.cnf
slow_query_log=1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3
log_output=FILE

在生产环境中，如果要手工剖析日志，查找、剖析 SQL，显然是个体力活，MySQL 提供了日志剖析工具mysqldumpslow。

mysqldumpslow –help

-a: 不将数字形象成 N，字符串形象成 S -s: 是示意依照何种形式排序；c: 拜访次数 l: 锁定工夫 r: 返回记录 t: 查问工夫 al: 均匀锁定工夫 ar: 均匀返回记录数 at: 均匀查问工夫 -t: 即为返回后面多少条的数据；-g: 后边搭配一个正则匹配模式，大小写不敏感的；

罕用参考:

失去返回记录集最多的 10 个 SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

失去拜访次数最多的 10 个 SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

失去依照工夫排序的前 10 条外面含有左连贯的查问语句
mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/atguigu-slow.log

另外倡议在应用这些命令时联合 | 和 more 应用，否则有可能呈现爆屏状况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

SHOW PROCESSLIST

显示过程列表

能干什么：查问所有用户正在干什么如果呈现不悦目的 kill [id]

是什么将一段查问 sql 封装为一个虚构的表。这个虚构表只保留了 sql 逻辑，不会保留任何查问后果。作用 1、封装简单 sql 语句，进步复用性 2、逻辑放在数据库下面，更新不须要公布程序，面对频繁的需要变更更灵便实用场景很多中央能够共用的一组查问后果报表统计数据常常变动, 将统计 sql 封装到一个视图中, 与程序解耦

创立

CREATE VIEW view_name
AS
SELECT column_name(s)
FROM table_name
WHERE condition

应用

select * from view_name

更新

CREATE OR REPLACE VIEW view_name
AS
SELECT column_name(s)
FROM table_name
WHERE condition

注意事项(实用 5.5) mysql 的视图中不容许有 from 前面的子查问，但 oracle 能够

复制的基本原理:

MySQL 复制过程分成三步：1 master 将扭转记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；2 slave 将 master 的 binary log events 拷贝到它的中继日志（relay log）；3 slave 重做中继日志中的事件，将扭转利用到本人的数据库中。MySQL 复制是异步的且串行化的

复制的根本准则 每个 slave 只有一个 master 每个 slave 只能有一个惟一的服务器 ID 每个 master 能够有多个 salve

复制的最大问题:IO 多, 延时

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

主机批改 my.ini 配置文件

主服务器惟一 ID

 server-id=1

启用二进制日志

log-bin= 本人本地的门路 /data/mysqlbin
log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin

设置不要复制的数据库

binlog-ignore-db=mysql

设置须要复制的数据库

binlog-do-db= 须要复制的主数据库名字

设置 logbin 格局

binlog_format=STATEMENT（默认）

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

从机配置文件批改 my.cnf 的 [mysqld] 栏位下

server-id = 2
relay-log=mysql-relay

因批改过配置文件，请主机 + 从机都重启后盾 mysql 服务, 主机从机都敞开防火墙

主机创立一个用户受权复制权限

GRANT REPLICATION SLAVE ON . TO ‘slave200404’@’%’ IDENTIFIED BY ‘123456’;

查看主机状态

SHOW MASTER STATUS;
|File | Positon | Binlog_Do_DB |Binlog_lgnore_DB
mysqlbin.000001 107 mydb_200404 mysql

File: 日志文件

Postion: 接入点

Binlog_Do_DB: 要复制的数据库

Binlog_lgnore_DB: 不要复制的数据库

从机对照主机状态拜大哥

CHANGE MASTER TO MASTER_HOST=’192.168.0.104′,
MASTER_USER=’slave200404′,
MASTER_PASSWORD=’123456′,
MASTER_LOG_FILE=’mysqlbin.000001′,MASTER_LOG_POS=107;

如果之前做过须要先进行再设置

stop slave;
reset master;

启动从服务器复制性能

start slave;

查看主从配置状态

show slave statusG;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
胜利

主机创立相应的数据库

CREATE DATABASE mydb_200404;

刷新从机呈现数据库胜利

数据库中间件, 前身是阿里的 cobar, 仅能够用作读写拆散、以及分表分库、多数据源整合, 容灾备份

基本原理:

Mycat 的原理中最重要的一个动词是“拦挡”，它拦挡了用户发送过去的 SQL 语句，首先对 SQL 语句做了一些特定的剖析：如分片剖析、路由剖析、读写拆散剖析、缓存剖析等，而后将此 SQL 发往后端的实在数据库，并将返回的后果做适当的解决，最终再返回给用户

这种形式把数据库的分布式从代码中解耦进去，Mycat 是代理，Mycat 前面就是物理数据库。和 Web 服务器的 Nginx 相似。对于使用者来说，拜访的都是 Mycat，不会接触到后端的数据库。

概念:

0、逻辑库 / 表：mycat 中存在的库和表

1、分片表：本来有大量数据的表

2、ER 表：关系表

3、全局表：相似于字典表这种表，字典表和很多表都有关联，mycat 采纳数据冗余存储

4、分片节点（dataNode）：一张大表分为多个数据库上，每个数据库就是分片节点

5、分片主机（dataHost）：分片节点能够在不同的主机，一个或者多个节点所在的主机就是分片主机

6、分片规定（rule）：数据划分的规定

7、全局序列号：数据切分后本来的主键就无奈应用了，因而须要引入一个值保证数据唯一性。

8、多租户（很重要）：多个环境下专用雷同的零碎，并且保障隔离性

8-1：独立数据库，隔离性高，然而代价比拟宏大

8-2：共享一个数据库，不是齐全隔离，隔离性不高，容易出错

8-3：共用数据结构，数据架构，通过 ID 进行辨别租户数据（也就是用 mycat）

简略来说，就是指通过某种特定的条件，将咱们寄存在同一个数据库中的数据扩散寄存到多个数据库（主机）下面，以达到扩散单台设施负载的成果

数据的切分（Sharding）依据其切分规定的类型，能够分为两种切分模式。一种是依照业务将表进行分类（或者 Schema）来切分到不同的数据库（主机）之上，这种切能够称之为数据的垂直（纵向）切分；

另外一种则是依据表中的数据的逻辑关系依照某个字段的某种规定，将同一个表中的数据依照某种条件拆分到多台数据库（主机）下面，这种切分称之为数据的程度（横向）切分。

linux 下能够下载 Mycat-server-xxxxx.linux.tar.gz 解压拷贝 mycat 目录到 /usr/local 目录

cp -r mycat /usr/local

目录如下：

阐明

bin

mycat 命令，启动、重启、进行等

catlet

catlet 为 Mycat 的一个扩大性能

conf

Mycat 配置信息, 重点关注

lib

Mycat 援用的 jar 包，Mycat 是 java 开发的

logs

日志文件，包含 Mycat 启动的日志和运行的日志。

配置文件:

文件

阐明

server.xml

Mycat 的配置文件，设置账号、参数等

schema.xml

Mycat 对应的物理数据库和数据库表的配置

rule.xml

Mycat 分片（分库分表）规定

schema.xml 定义逻辑库，表、分片节点等内容

将 </schema> 中的内容删除, 并加上 dataNode="dn1" 属性指定数据节点名称

<mycat:schema xmlns:mycat=”http://io.mycat/”>
<!– 逻辑库 name 名称, checkSQLschema SQL 查看优化 sqlMaxLimit 开端是否要加 limit xxx–>

    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    </schema>
<!-- 逻辑库    name 名称,   dataHost 援用的哪个 dataHost      database: 对应 mysql 的 database-->
    <dataNode name="dn1" dataHost="host1" database="mydb_200404" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                      writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            <!-- 心跳检测 -->
            <heartbeat>select user()</heartbeat>
            <!-- 写主机 can have multi write hosts -->
            <writeHost host="hostM1" url="192.168.0.104:3306" user="root"
                               password="000000">
                    <!-- 读主机 can have multi read hosts -->
                    <readHost host="hostS1" url="192.168.107.108:3306" user="root" password="000000" />
            </writeHost>
    </dataHost>

</mycat:schema>

schema:

属性

阐明

name

逻辑数据库名，与 server.xml 中的 schema 对应

checkSQLschema

数据库前缀相干设置，倡议看文档，这里临时设为 false

sqlMaxLimit

select 时默认的 limit，防止查问全表

table:

属性

阐明

name

表名，物理数据库中表名

dataNode

表存储到哪些节点，多个节点用逗号分隔。节点为下文 dataNode 设置的 name

primaryKey

主键字段名，主动生成主键时须要设置

autoIncrement

是否自增

rule

分片规定名，具体规定下文 rule 具体介绍

dataNode

属性

阐明

name

节点名，与 table 中 dataNode 对应

datahost

物理数据库名，与 datahost 中 name 对应

database

物理数据库中数据库名

dataHost

属性

阐明

name

物理数据库名，与 dataNode 中 dataHost 对应

balance

平衡负载的形式

writeType

写入形式

dbType

数据库类型

heartbeat

心跳检测语句，留神语句结尾的分号要加。

server.xml 配置 mycat 用户名明码

schemas: 数据库名，这里会和 schema.xml 中的配置关联，多个用逗号离开，例如须要这个用户须要治理两个数据库 db1,db2，则配置 db1,db2

            <property name="password">123456</property>
            <property name="schemas">TESTDB</property>

验证数据库拜访状况

mysql -uroot -p123123 -h 192.168.154.1 -P 3306
mysql -uroot -p123123 -h 192.168.154.154 -P 3306

如本机近程拜访报错，请建对应用户
grant all privileges on . to hzy@’%’ identified by ‘000000’;

启动程序

控制台启动：去 mycat/bin 目录下 mycat console
后盾启动：去 mycat/bin 目录下 mycat start

登录

后盾治理窗口(运维)

mysql -umycat -p654321 -P9066 -h192.168.67.131

命令

show database
show @@help

数据窗口(开发)

mysql -umycat -p123456 -P8066 -h192.168.107.108

连贯形式和一般数据库一样，如：jdbc:mysql://192.168.0.2:8066/

如果在启动时发现异常，在 logs 目录中查看日志。

wrapper.log 为程序启动的日志，启动时的问题看这个
mycat.log 为脚本执行时的日志，SQL 脚本执行报错后的具体谬误内容, 查看这个文件。mycat.log 是最新的谬误日志，历史日志会依据工夫生成目录保留。

查看库表

show databases;
DATABASE
TESTDB

mysql> use TESTDB;

mysql> show tables;
Tables_in_mydb_200404
mytable

负载平衡类型，目前的取值有 4 种：

balance=”0″, 不开启读写拆散机制，所有读操作都发送到以后可用的 writeHost 上。
balance=”1″，全副的 readHost 与 stand by writeHost 参加 select 语句的负载平衡，简略的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，失常状况下，M2,S1,S2 都参加 select 语句的负载平衡。
balance=”2″，所有读操作都随机的在 writeHost、readhost 上散发。
balance=”3″，所有读申请随机的散发到 readhost 执行，writerHost 不累赘读压力
<dataHost name=”host1″ maxCon=”1000″ minCon=”10″ balance=”2″

在主机执行

INSERT mytable(id,name) VALUES(2,@@hostname);

重启 mycat, 再查问

id	name
1	stig
2	dockerhost

2 rows in set (0.01 sec)

mysql> select * from mytable;
id	name
1	stig
2	HUAWEIMatebook

2 rows in set (0.00 sec)

mysql> select * from mytable;
id	name
1	stig
2	dockerhost

依照业务将表进行分类, 不须要 join 关系的表离开

<mycat:schema xmlns:mycat=”http://io.mycat/”>

<!-- 数据库配置，与 server.xml 中的数据库对应 -->
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
            <!-- 配置表分到哪个数据节点 -->
            <table name="customer" dataNode="dn2" ></table>
            <table name="orders" dataNode="dn1"></table>
    </schema>
    <!-- 分片配置 -->
    <dataNode name="dn1" dataHost="host1" database="orders__200405" />
    <dataNode name="dn2" dataHost="host2" database="orders_200405" />

   <!-- 物理数据库配置 -->
   <!--balance="0" 不配置读写拆散 -->
   <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                      writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <!-- can have multi write hosts -->
            <writeHost host="hostM1" url="192.168.0.104:3306" user="root"
                               password="000000">
            </writeHost>
    </dataHost>

<dataHost name=”host2″ maxCon=”1000″ minCon=”10″ balance=”0″

                      writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <!-- can have multi write hosts -->
            <writeHost host="hostM2" url="192.168.107.108:3306" user="hzy"
                               password="000000">
            </writeHost>
    </dataHost>

</mycat:schema>

schema.xml

<mycat:schema xmlns:mycat=”http://io.mycat/”>

<!-- 数据库配置，与 server.xml 中的数据库对应 -->
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
            <!-- 配置表分到哪个数据节点 -->
            <table name="customer" dataNode="dn2" ></table>
            <table name="orders" dataNode="dn1"></table>

<!– dictionary 写入了 dn1、dn2 两个节点, 分片的规定为：mod-long 源于 rule.xml 文件中 tableRule 的 name –>

            <table name="dictionary" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2"  rule="mod-long" />
    </schema>
    <!-- 分片配置 -->
    <dataNode name="dn1" dataHost="host1" database="orders__200405" />
    <dataNode name="dn2" dataHost="host2" database="orders_200405" />

   <!-- 物理数据库配置 -->
   <!--balance="0" 不配置读写拆散 -->
   <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                      writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <!-- can have multi write hosts -->
            <writeHost host="hostM1" url="192.168.0.104:3306" user="root"
                               password="000000">
            </writeHost>
    </dataHost>

<dataHost name=”host2″ maxCon=”1000″ minCon=”10″ balance=”0″

                      writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <!-- can have multi write hosts -->
            <writeHost host="hostM2" url="192.168.107.108:3306" user="hzy"
                               password="000000">
            </writeHost>
    </dataHost>

</mycat:schema>

rule.xml 定义分片规定

这个文件外面次要有 tableRule 和 function 这两个标签。在具体使用过程中能够依照需要增加 tableRule 和 function。

mod-long 就是将数据均匀拆分。因为是两台物理库，所以 rule.xml 中 mod-long 对应的 function count 为 2

         <rule>
                    <columns>customer_id</columns>
                    <algorithm>mod-long</algorithm>
         </rule>

</tableRule>

            <!-- 多少数据节点 -->
            <property name="count">2</property>
    </function>

tableRule

name 属性指定惟一的名字，用于标识不同的表规定
内嵌的 rule 标签则指定对物理表中的哪一列进行拆分 (columns) 和应用什么路由算法(algorithm)

algorithm 应用 function 标签中的 name 属性。连贯表规定和具体路由算法。当然，多个表规定能够连贯到同一个路由算法上。table 标签内应用。让逻辑表应用这个规定进行分片

function

name 指定算法的名字
class 制订路由算法具体的类名字
property 为具体算法须要用到的一些属性

罕用分片规定 分片枚举（hash-int）：在配置文件中配置可能呈现的枚举 id，配置分片固定分片 Hash 算法（func1）：二进制操作的求模运算范畴约定（rang-long）：提前规定好字段范畴属于哪个分片取模（mod-long）：依据 ID 进行 10 进制的求模运算日期分片（sharding-by-date）：依照工夫划分取模范畴（sharding-by-pattern）：依照取模运算和范畴运算联合利用指定（sharding-by-substring）：运行阶段有利用自主决定路由到那个分片

<function name=”murmur”

    class="io.mycat.route.function.PartitionByMurmurHash">
    <property name="seed">0</property><!-- 默认是 0 -->
    <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
    <property name="virtualBucketTimes">160</property><!-- 一个理论的数据库节点被映射为这么多虚构节点，默认是 160 倍，也就是虚构节点数是物理节点数的 160 倍 -->
    <!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，没有指定权重的节点默认是 1。以 properties 文件的格局填写，以从 0 开始到 count- 1 的整数值也就是节点索引为 key，以节点权重值为值。所有权重值必须是正整数，否则以 1 代替 -->
    <!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property> 
        用于测试时察看各物理节点与虚构节点的散布状况，如果指定了这个属性，会把虚构节点的 murmur hash 值与物理节点的映射按行输入到这个文件，没有默认值，如果不指定，就不会输入任何货色 -->
</function>

<function name="crc32slot"
          class="io.mycat.route.function.PartitionByCRC32PreSlot">
    <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
</function>
<function name="hash-int"
    class="io.mycat.route.function.PartitionByFileMap">
    <property name="mapFile">partition-hash-int.txt</property>
</function>
<function name="rang-long"
    class="io.mycat.route.function.AutoPartitionByLong">
    <property name="mapFile">autopartition-long.txt</property>
</function>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
    <!-- how many data nodes -->
    <property name="count">3</property>
</function>

<function name="func1" class="io.mycat.route.function.PartitionByLong">
    <property name="partitionCount">8</property>
    <property name="partitionLength">128</property>
</function>
<function name="latestMonth"
    class="io.mycat.route.function.LatestMonthPartion">
    <property name="splitOneDay">24</property>
</function>
<function name="partbymonth"
    class="io.mycat.route.function.PartitionByMonth">
    <property name="dateFormat">yyyy-MM-dd</property>
    <property name="sBeginDate">2015-01-01</property>
</function>

<function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
        <property name="mapFile">partition-range-mod.txt</property>
</function>

<function name="jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash">
    <property name="totalBuckets">3</property>
</function>

正文完

mysql

发表至： mysql

2021-04-02

0

关于mysql:Mysql到TiDB迁移双写数据库兜底方案

关于mysql:MySQL数据库数据安装与卸载

关于mysql:MySQL如何存储Emoji表情UTF8和UTF8MB4字符编码有何区别

关于mysql:mysql-静默恢复备份文件

关于数据库:MyCat学习笔记

关于mysql:Mysql存储引擎索引学习笔记

mysql 逻辑架构

存储引擎

索引

索引构造

BTree

B+Tree

B+Tree 与 B -Tree 的区别

对于工夫复杂度

聚簇索引和非聚簇索引

索引分类

单值索引

惟一索引

主键索引

复合索引

应用 ALTER 命令

哪些状况须要创立索引

哪些状况不要创立索引

性能剖析

执行打算蕴含的信息:

如何正确的删除索引

索引注意事项

全值匹配我最爱

最佳左前缀法令

不在索引列上做任何操作

存储引擎不能应用索引中范畴条件左边的列

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

is not null 无奈应用索引, 然而 is null 能够应用索引

like 以通配符结尾 (‘%abc…’) 索引生效

字符串不加单引号索引生效

一般性倡议

关联查问优化

子查问优化

排序分组优化

查问截取剖析

慢查问日志

视图

主从复制

一主一丛常见配置

MyCat

数据分片

装置

读写拆散

分库

程度分表

mysql 逻辑架构

存储引擎

索引

索引构造

BTree

B+Tree

B+Tree 与 B -Tree 的区别

对于工夫复杂度

聚簇索引和非聚簇索引

索引分类

单值索引

惟一索引

主键索引

复合索引

应用 ALTER 命令

哪些状况须要创立索引

哪些状况不要创立索引

性能剖析

执行打算蕴含的信息:

如何正确的删除索引

排除主键索引

索引注意事项

全值匹配我最爱

最佳左前缀法令

不在索引列上做任何操作

存储引擎不能应用索引中范畴条件左边的列

须要保障范畴查问的字段在最左边

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

is not null 无奈应用索引, 然而 is null 能够应用索引

like 以通配符结尾 (‘%abc…’) 索引生效

字符串不加单引号索引生效

一般性倡议

关联查问优化

子查问优化

排序分组优化