mysql高级1——sql_mode、mysql存储引擎、join、索引、怎么看EXPLAIN()

先从一段sql说起:

CREATE TABLE mytbl2 (id INT,NAME VARCHAR(200),age INT,dept INT);
INSERT INTO mytbl2 VALUES(1,'zhang3',33,101);
INSERT INTO mytbl2 VALUES(2,'li4',34,101);
INSERT INTO mytbl2 VALUES(3,'wang5',34,102);
INSERT INTO mytbl2 VALUES(4,'zhao6',34,102);
INSERT INTO mytbl2 VALUES(5,'tian7',36,102);
#每个机构年龄最大的人
SELECT NAME,dept,MAX(age) FROM mytbl2 GROUP BY dept;
 
 

 
 上面语句是错的
 group by使用原则：select 后面只能放 函数 和group by后的字段
 
1、每个机构最大年龄 2、的人
SELECT * FROM mytbl2 m INNER JOIN(
SELECT dept,MAX(age)maxage FROM mytbl2 GROUP BY dept
)ab ON ab.dept=m.dept AND m.age=ab.maxage

从上面可以引出mysql的一些杂项配置

sql_mode

MySQL的sql_mode合理设置
sql_mode是个很容易被忽视的变量，默认值是空值，在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。

使用这个命令可以查看sql的配置：show variables like ‘sql_mode’;

sql_mode常用值如下：
set sql_mode=‘ONLY_FULL_GROUP_BY’;

• ONLY_FULL_GROUP_BY：
  对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中

• NO_AUTO_VALUE_ON_ZERO：
  该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

• STRICT_TRANS_TABLES：
  在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制
  NO_ZERO_IN_DATE：
  在严格模式下，不允许日期和月份为零

• NO_ZERO_DATE：
  设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

• ERROR_FOR_DIVISION_BY_ZERO：
  在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如 果未给出该模式，那么数据被零除时MySQL返回NULL

• NO_AUTO_CREATE_USER：
  禁止GRANT创建密码为空的用户

• NO_ENGINE_SUBSTITUTION：
  如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

• PIPES_AS_CONCAT：
  将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似

• ANSI_QUOTES：
  启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

• ORACLE：
  设置等同：PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

Mysql存储引擎

命中缓存的条件是 sql语句要一模一样

在Linux Mysql5.7中证明上面的顺序

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

• 修改配置文件/etc/my.cnf

• 新增一行：query_cache_type=1
  重启mysql

• 先开启 show variables like ‘%profiling%’;

• set profiling=1;

select * from xxx ;

show profiles; #显示最近的几次查询

show profile cpu,block io for query 编号 #查看程序的执行步骤

手写的代码

机读

存储引擎

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
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁,操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
关注点	节省资源、消耗少、简单业务	并发写、事务、更大资源
默认安装	Y	Y
默认使用	N	Y
自带系统表使用	Y	N

索引优化分析

数据过多 -》 分库分表

关联了太多的表，太多join -》 SQL优化

没有充分利用到索引 -》 索引建立

服务器调优及各个参数设置 -》 调整my.cnf

join

 
1   所有有门派的人员信息 
（ A、B两表共有）
 select * from t_emp a inner join t_dept b on a.deptId = b.id; 
 
2   列出所有用户，并显示其机构信息 
 （A的全集）
 select * from t_emp a left join t_dept b on a.deptId = b.id; 
 
3   列出所有门派 
（B的全集）
 select * from  t_dept  b  
 
4   所有不入门派的人员 
（A的独有）
select * from t_emp a left join t_dept b on a.deptId = b.id where b.id is null; 
 select a.name from t_emp a left join t_dept b on a.deptId = b.id where a.deptId  is null;
5   所有没人入的门派 
（B的独有）
 select * from t_dept b left join  t_emp a on a.deptId = b.id where a.deptId is null;  
 
6  列出所有人员和机构的对照关系
(AB全有)
#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
 #left join + union(可去除重复数据)+ right join
 
SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id
 UNIon all 不去重
7 列出所有没入派的人员和没人入的门派
（A的独有+B的独有）
SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id` IS NULL
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;


#添加掌门
ALTER TABLE `t_dept` 
add  CEO  INT(11)  ;

#求各个门派对应的掌门人名称: 
select   * from t_dept as  b left  join t_emp as a on  b.CEO=a.id;
 
#求所有当上掌门人的平均年龄:
select  avg(a.age) from t_emp a inner join t_dept b on a.id=b.CEO  ;
 
#求所有人物对应的掌门名称: 
 select ab.name, c.name ceoname from
(select a.name,b.ceo from t_temp  a left join t_dept b on a.deptId =b.id) ab
left join t_emp c on ab.ceo = c.id;

索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
可以得到索引的本质：索引是数据结构。

**优势：**通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势：

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，
都会调整因为更新所带来的键值变化后的索引信息

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

BTree

1.数据 2.向下的指针 3.指向数据的指针

B+Tree （mysql选择）

数据+向下的指针（更省空间 ）

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这种。

索引分类

• 查看索引

  • SHOW INDEX FROM table_name\G

• 创建

  • CREATE [UNIQUE ] INDEX [indexName] ON table_name(column))

• 删除

  • DROP INDEX [indexName] ON mytable;

• 有四种方式来添加数据表的索引：

  • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 
    #该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
    
    ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):
    #这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
    
    ALTER TABLE tbl_name ADD INDEX index_name (column_list): 
    #添加普通索引，索引值可出现多次。
    
    ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):
    #该语句指定了索引为 FULLTEXT ，用于全文索引。
    

• 单值索引

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

  • #随表一起建索引：
    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); 
     
    

• 主键索引

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

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

• 复合索引

  • 即一个索引包含多个列

  • #随表一起建索引：
    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); 
    

哪些情况需要创建索引

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其它表关联的字段，外键关系建立索引
• 单键/组合索引的选择问题， 组合索引性价比更高
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度（order by）
• 查询中统计或者分组字段(group by 比order by更伤性能)

哪些情况不要创建索引

• 表记录太少
• 经常增删改的表或者字段
• Where条件里用不到的字段不创建索引
• 过滤性不好的不适合建索引

EXPLAIN

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是
如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

能干嘛

• 表的读取顺序
• 哪些索引可以使用
• 数据读取操作的操作类型
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被物理查询

id

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

三种情况

• id相同，执行顺序由上至下
• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
• id相同不同，同时存在

id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

关注点

id号每个号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。

select_type

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

• SIMPLE

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

• PRIMARY

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

• DERIVED

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

• SUBQUERY

在SELECT或WHERE列表中包含了子查询(=查询)

• DEPENDENT SUBQUERY

  在SELECT或WHERE列表中包含了子查询,子查询基于外层(IN范围查询)

• UNCACHEABLE SUBQUREY

  不可用缓存的子查询

• UNION

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

• UNION RESULT

从UNION表获取结果的SELECT

table

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

partitions

代表分区表中的命中情况，非分区表，该项为null

type***

显示查询使用了何种类型，
从最好到最差依次是：
system>const>eq_ref>ref>range>index>ALL

• system

表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

• const

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

• eq_ref

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

• ref

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

• range X

只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引
一般就是在你的where语句中出现了between、<、>、in等的查询
这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

• index XX

出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组

• all XXX

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

要建索引

• index_merge

在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中

• ref_or_null

对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。

• index_subquery

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

• unique_subquery

该联接类型类似于index_subquery。 子查询中的唯一索引

备注：一般来说，得保证查询至少达到range级别，最好能达到ref。

possible_keys

显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key

实际使用的索引。如果为NULL，则没有使用索引

查询中若使用了覆盖索引，则该索引和查询的select字段重叠

key_len **

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。

key_len字段能够帮你检查是否充分的利用上了索引(越大越好)

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

第一组
key_len=age的字节长度+name的字节长度=4+1 + ( 20*3+2)=5+62=67
第二组
key_len=age的字节长度=4+1=5

ref

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

rows**

rows列显示MySQL认为它执行查询时必须检查的行数。(越少越好)

filtered

这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数

Extra***

group by 、order by 、关联查询有没有用上索引

• Using filesort XXX

出现filesort的情况 oder by 没有用上索引

优化后，不再出现filesort的情况：

Using temporary XXX

group by没用上索引 group by包含 oder by
优化前存在 using temporary 和 using filesort

优化前存在 using temporary 和 using filesort 不在，性能发生明显变化：

USING index

表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
如果同时出现using where，表明索引被用来执行索引键值的查找;
如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。

利用索引进行了排序或分组

**Using where **

表明使用了where过滤

using join buffer XXX

使用了连接缓存：两表关联字段没用上索引

**impossible where **

sql错误

select tables optimized away

在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者
对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

索引库在Information_schema中的statistics中

1 查出该表有哪些索引，索引名–>集合

SHOW INDEX FROM t_emp
元数据：meta DATA 描述数据的数据

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

2 如何循环集合
CURSOR 游标
FETCH xxx INTO xxx

3 如何让mysql执行一个字符串
PREPARE 预编译 XXX

EXECUTE

CALL proc_drop_index (‘mydb’,‘t_emp’);