Mysql存储引擎,索引学习笔记

mysql逻辑架构

利用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不仅要保存数据,还要保存索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息
实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引也是要占用磁盘空间的

索引结构

BTree

1.索引

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

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

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

B+Tree

1.索引

2.向下节点的索引

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

B+Tree与B-Tree 的区别

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

关于时间复杂度

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

聚簇索引和非聚簇索引

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

聚簇索引的好处：
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不不用从多个数据块中提取数据，所以节省了大量的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命令

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

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

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

is not null 无法使用索引,但是is null可以使用索引

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

like以通配符开头('%abc...')索引失效

模糊查询最好明确首字母

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

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;

刷新从机出现数据库成功

MyCat

数据库中间件,前身是阿里的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>

Mysql存储引擎,索引学习笔记

mysql逻辑架构

存储引擎

索引

索引结构

BTree

B+Tree

B+Tree与B-Tree 的区别

关于时间复杂度

聚簇索引和非聚簇索引

索引分类

单值索引

唯一索引

主键索引

复合索引

使用ALTER命令

哪些情况需要创建索引

哪些情况不要创建索引

性能分析

执行计划包含的信息:

如何正确的删除索引

索引注意事项

全值匹配我最爱

最佳左前缀法则

不在索引列上做任何操作

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

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

is not null 无法使用索引,但是is null可以使用索引

like以通配符开头('%abc...')索引失效

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

一般性建议

关联查询优化

子查询优化

排序分组优化

查询截取分析

慢查询日志

视图

主从复制

一主一丛常见配置

MyCat

数据分片

安装

读写分离

分库

水平分表

打了个冷颤

引用和评论

SparkSQL执行update操作修改mysql数据

在Java程序中监听mysql的binlog

Mysql数据库sql优化案例

MySQL 回收表碎片实践教程

高效工程师的七个习惯

Mysql梳理1

mysql梳理2