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

它们的区别:

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

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 wherewhere子句的值总是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.statisticsWHERE TABLE_NAME='t_emp' AND TABLE_SCHEMA='mydb'AND INDEX_NAME <> 'PRIMARY' AND SEQ_IN_INDEX=1#排除主键索引

如何遍历这个索引汇合

CURSOR 游标FETCH xxx INTO xxx

如何让mysql执行一个字符串

PREPARE 预编译 xxxEXECUTE

用存储过程实现

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 NULLEXPLAIN 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.CEOWHERE 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的速度

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

GROUP BY优化

group by 应用索引的准则简直跟order by统一 ，惟一区别是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 =1slow_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.cnfslow_query_log=1slow_query_log_file=/var/lib/mysql/atguigu-slow.loglong_query_time=3log_output=FILE

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

mysqldumpslow --help

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

罕用参考:

失去返回记录集最多的10个SQLmysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log 失去拜访次数最多的10个SQLmysqldumpslow -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 ASSELECT column_name(s)FROM table_nameWHERE condition

应用

select * from view_name

更新

CREATE OR REPLACE VIEW view_name ASSELECT column_name(s)FROM table_nameWHERE 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_DBmysqlbin.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: YesSlave_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

目录如下：

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

配置文件:

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

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
sqlMaxLimitselect 时默认的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 3306mysql -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

mysql逻辑架构

<!--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_IDDuration  Query                            
       10.00136600show variables like '%profiling%'
       20.00049975select * 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)，防止拜访了表的数据行，效率不错！ 如果同时呈现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);

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进行排序，再从磁盘取其余字段。 取一批数据，要对磁盘进行了两次扫描，家喻户晓，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的速度

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

GROUP BY优化

group by 应用索引的准则简直跟order by统一 ，惟一区别是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/mysqlbinlog-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;

刷新从机呈现数据库胜利

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 databaseshow @@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,再查问

idname
1stig
2dockerhost

2 rows in set (0.01 sec)

mysql> select * from mytable;
idname
1stig
2HUAWEIMatebook

2 rows in set (0.00 sec)

mysql> select * from mytable;
idname
1stig
2dockerhost

分库

依照业务将表进行分类,不须要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

mysql逻辑架构

<!--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

它们的区别:

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

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 wherewhere子句的值总是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.statisticsWHERE TABLE_NAME='t_emp' AND TABLE_SCHEMA='mydb'AND INDEX_NAME <> 'PRIMARY' AND SEQ_IN_INDEX=1#排除主键索引

如何遍历这个索引汇合

CURSOR 游标FETCH xxx INTO xxx

如何让mysql执行一个字符串

PREPARE 预编译 xxxEXECUTE

用存储过程实现

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 NULLEXPLAIN 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.CEOWHERE 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的速度

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

GROUP BY优化

group by 应用索引的准则简直跟order by统一 ，惟一区别是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 =1slow_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.cnfslow_query_log=1slow_query_log_file=/var/lib/mysql/atguigu-slow.loglong_query_time=3log_output=FILE

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

mysqldumpslow --help

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

罕用参考:

失去返回记录集最多的10个SQLmysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log 失去拜访次数最多的10个SQLmysqldumpslow -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 ASSELECT column_name(s)FROM table_nameWHERE condition

应用

select * from view_name

更新

CREATE OR REPLACE VIEW view_name ASSELECT column_name(s)FROM table_nameWHERE 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_DBmysqlbin.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: YesSlave_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

目录如下：

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

配置文件:

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

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
sqlMaxLimitselect 时默认的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 3306mysql -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

mysql逻辑架构

<!--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_IDDuration  Query                            
       10.00136600show variables like '%profiling%'
       20.00049975select * 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)，防止拜访了表的数据行，效率不错！ 如果同时呈现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);

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进行排序，再从磁盘取其余字段。 取一批数据，要对磁盘进行了两次扫描，家喻户晓，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的速度

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

GROUP BY优化

group by 应用索引的准则简直跟order by统一 ，惟一区别是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/mysqlbinlog-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;

刷新从机呈现数据库胜利

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 databaseshow @@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,再查问

idname
1stig
2dockerhost

2 rows in set (0.01 sec)

mysql> select * from mytable;
idname
1stig
2HUAWEIMatebook

2 rows in set (0.00 sec)

mysql> select * from mytable;
idname
1stig
2dockerhost

分库

依照业务将表进行分类,不须要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>