Mysql笔记（一）

说明

以前看某个视频做的一些笔记，防止丢失，在此做个备份。

语法类

1、查询模型

select name, age from user where uid = 2;

扫描每一行，where后面接的条件表示该行数据是否是需要的，作为条件，类比于下面这个表达式的condition

if( condition ) {

}

所以condition可以为1，可以为等式，可以为不等式，只要结果是布尔类型的即可。

而前面的name, age都是变量，可以做一些简单的运算，例如

select name, age + 18 from user where uid = 2;

2、having-group

有如下表及数据
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 张三 | 数学    |    90 |
| 张三 | 语文    |    50 |
| 张三 | 地理    |    40 |
| 李四 | 语文    |    55 |
| 李四 | 政治    |    45 |
| 王五 | 政治    |    30 |
+------+---------+-------+

要求:查询出2门及2门以上不及格者的平均成绩

select name, avg(score), sum(score < 60) as gk from student group by name having gk >= 2

这里为什么不能用count，因为 score< 60的结果是 0 或者 1，

当使用count的时候，所有情况都会计算进去

3、子句的顺序

where ， group by ， having， order by， limit

4、视图

视图的语法：

create view  视图名  as  select *******

视图的作用

1、简化查询

2、权限控制（将某个表的列单独拿出来做权限控制）

特点

不能更新不能删除

5、常用见表/视图语句

查看所有的表 show tables;

查看表结构 desc 表名/视图名

查看建表过程 show create table 表名

查看建视图过程 show create view 视图名

删除表 drop table 表名

删除视图 drop view 视图名

查看表详细信息 show table status;

查看表详细信息 show table status where name =“表名”

改表名 rename table oldName to newName

清空数据表 truncate

6、data文件夹下文件的含义

INNOD引擎

frm结尾=表结构信息

MYD结尾=表存储的信息

MYI结尾=表的索引

索引

索引分类

索引的类型

key，普通索引

unique key， 唯一索引

primary key，主键索引

fulltext， 全文索引，中文环境下无效

索引相关操作

查看索引

show index from 表名

或者

show create table 表名

删除索引

drop index 索引名 on 表名

或者

alter table 表名 drop index 索引名

添加索引

添加普通索引
alter table 表名 add index 索引名(列， 列)

添加唯一索引
alter table 表名 add unique 索引名(列) 

添加主键索引
alter table 表名 add primary key（列）
删除主键索引
alter table 表名 drop primary key

事务

语法

基本语法

start transaction;
sql
sql
sql
commit;

回滚

start transaction
sql
sql
sql
rollback;

MySql优化

表的优化和列类型选择

1、定长与不定长分离

哪些是定长的？

int四个字节， char（4）， time等

核心常用的字段适合制成定长字段，放在一个表里面。

varchar，text，blob，这种边长字段适合单方一张表里面，用主键和核心表关联起来。

2、常用字段与不常用字段分离

结合网站具体的业务来分析，分析字段的查询场景，查询频度低的字段单拆出来。

3、适当冗余（反三范式）

尤其是一对N的情况下，为了查询效率，可以空间换时间

4、类型选择原则

顺序

整型

date,time

enum,char

varchar

blob,text

分析：

1、tinny 与 char（1）

空间上来看，都是占1个字节，但是order by排序，前者快。后者需要考虑字符集和校对集（排序规则）

2、time

定长，运算快，但是需要考虑时区，写sql不方便

where > ‘2020-12-25’

3、enum

能起约束值得目的，内部用整型来存储，但是与char联查的适合，内部需要经历串与值得转化

4、char与varchar

char，定长，考虑字符集和校对集。

varchar，不定长 要考虑字符集得转化与排序时得校对集，速度慢。

5、text/Blob

无法使用内存临时表，排序只能在磁盘上进行。

原则

1、够用就行，给不定长分配空间的时候要尽量得小。

2、避免用NULL，不利于索引，要用特殊字节来标志，虽然mysql5.7对null做了改进，但是查询依然不方便。

null有一个坑点，做查询时，必须用 is null 或者 is not null

如果是 where = null 或者 where ！= null，都会查不到值。

索引优化

索引类型

• B-tree索引

都用到了平衡树，但是具体的类型不一定相同。

• hash索引

随机读取快

不适合范围查询

无法利用前缀索引

排序无法优化

必须回行，通过索引拿到数据后，必须回表中取数据

B-TREE索引常见误区

1、不能再常用的列都加上索引

比如，name, age, gender

index(name), index(age), index(gender)这样是没有意义的，只能同时用上一个

所以一般建联合索引

index(name, age, gender)

2、在多列上建立索引后，不是说查哪个字段都会让索引发挥作用的。

以 index(a, b, c)为例

语句	索引是否发挥作用
where a=3	是
where a=3 and b=5	是
where a=3 and b=5 and c=4	是
where b=3 / where c=4	否
where a = 3 and c = 4	a可以，c没有
where a=3 and b>10 and c=7	a可以，b可以，c不能
where a=3 and b like ‘xxx%’ and c=7	a可以，b可以，c不能
where a=3 and b like ‘%xxx’ and c=7	a可以，b不可以，c不能

怎么理解？

类似于a,b,c三个木板搭成了一座桥。如果中间的b查询条件是 “%” 以及大于小于之类的，会中断后面的路，到不了C。

如果b是左前缀， “XXXX%”，是可以用上b的索引的，但如果是“%XXXXXX”，则B也不能发挥作用。

测试

实际测试一下：

创建一个数据及表

create table t1 (
c1 tinyint not null default 0,
c2 tinyint not null default 0,
c3 tinyint not null default 0,
c4 tinyint not null default 0,
c5 tinyint not null default 0,
key c1234(c1, c2, c3, c4)
)

案例1

查看索引是否被使用到

explain  select * from t1 where c1 = 3;

possible_keys可能用到的键

key实际用到的键

key_len 用到的键的长度

+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | c1234         | c1234 | 1       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+

案例2

explain xplain select *1 where c1=1 and c2=3 and c4>5 and c5=7 order by c3 \G

结果

mysql> explain xplain select *1 where c1=1 and c2=3 and c4>5 and c5=7 order by c3 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 2
          ref: const,const
         rows: 1
     filtered: 33.33
        Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)

虽然显示的长度为2，c1，c2发挥作用，但其实c3也发挥了作用，索引也可以提高排序的效率。

案例3

mysql> explain select * from t1 where c1=3 and c4=4 group by c3,c2 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 1
          ref: const
         rows: 1
     filtered: 33.33
        Extra: Using index condition; Using temporary; Using filesort
1 row in set, 1 warning (0.01 sec)

出现 Using temporary;表示使用了临时表，查询的时候没用上索引。

但如果把c3,c2换个位置

mysql> explain select * from t1 where c1=3 and c4=4 group by c2,c3 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 1
          ref: const
         rows: 1
     filtered: 33.33
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

案例4

mysql> explain select * from t1 where c1=3 and c2=4 and c5=3  group by c2,c3 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: c1234
          key: c1234
      key_len: 2
          ref: const,const
         rows: 1
     filtered: 33.33
        Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)

查询用到了c1，c2

排序用到了c3

因为查询后的结果都有c2，排序的时候只能根据c3来区分，排序只用到了c3

聚簇索引和非聚簇索引

聚簇索引

聚簇索引指的是一种数据存储方式。具体细节依赖于实现方式。

聚簇索引就是按照每张表建一个B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚簇索引的叶子节点称为数据页。（Innodb）

优点：

1、数据访问比较快，因为索引和实际的数据都在一个B+树。

2、因为innodb中的聚簇索引默认根据主键来建，所以对于主键的范围查找和排序查找都比较快。

缺点：

1、插入数据时，顺序插入是比较快的，但如果是无规则插入，可能会出现页分裂现象，影响性能。

这也是为什么innodb下，推荐使用自增的id作为主键。

2、更新主键的代价高，这样会导致该主键所在行移动，所以innodb下，一般定义主键不可更新。

非聚簇索引

也叫做辅助索引

我们手动添加的索引，比如使用命令

alter table XX  add index  XX

创建的都是非聚簇索引，也叫二级索引，也叫辅助索引。

那他有什么用呢？

非聚簇索引的叶子节点存储的是主键值，找到主键值后，可以通过主键值到聚簇索引（主索引）中找到实际的行数据。

Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。

MylSAM虽然也是B+Tree作为索引结构，但是它的叶子节点没有直接存放数据，而是数据的地址。

索引的选择

1、频率和长度

1、查询频繁 2、区分度高 3、长度小 4、尽量能覆盖常用查询字段

关于长度小这一点，不是绝对的，长度小，意味着区分度就低了，所以要再2、3两点之间取一个平衡点。

常用的办法是通过设置不同的长度慢慢测试区分度。

select count(distinct left(world, 6))/count(*) from dict;

2、对于左前缀不易区分的列，建索引的技巧

1、内容倒过来存储，并建立索引。

比如说

http://www.baidu.com

http://www.qq.com

可以倒过来存储

moc.udiab.www//:ptth

2、伪hash索引效果

比如你相对 url这个字段建索引，但是他做前缀重复的太多了，这个时候可以考虑用伪hash的办法，多加一列，用作记录url的hash值，建索引的时候，根据urlcc建就可以了。

至于这个hash值怎么来的，方式有很多，可以通过内置函数，比如md5, crc33等加密方式，就算有重复的也可以接受。

查询的时候，可以这么做

select * from XXX where urlcc = crc33(“http://www.baidu.com”)

多列索引创建依据

按照查询的频率以及长度来排序，比如商品的大分类，小分类，大分类用的多，大分类就放前面，小分类放后面。

索引与排序

1、对于覆盖索引，直接在索引上查询的时候，就是有顺序的，using index

2、先取出数据，形成临时表左filesort（文件排序，但是文件可能在磁盘中，也可能在内存中）

重复索引与冗余索引

1、重复索引

比如 对 age建立多个索引，相同索引不会有效率上的提高，只会占用内存。

2、冗余索引

比如有文章和标签的表

id tagId articleId

建索引的时候应该这么建

alter table XX add index tag_article(tagId, articleId)

alter table XX add index tag_article(articleId, tagId)

而不是

tag_article(tagId)

tag_article(articleId)

因为第一种写法，属于覆盖索引，不需要回表查询。

索引碎片和维护

两种方式修复

1、nop操作（对数据没有实质性影响的操作）

alter table xxx engine innodb

2、optimize 命令

optimize table 表名

修复频率

如果更新或者插入比较频繁，一周/月次

如果不频繁可以往后推

SQL优化

IN子查询注意点

执行顺序要注意

select * from a where b_id in (select b_id from b where parent_id = 5)

执行的顺序是：

#1、扫描a表，拿出一条数据，

#2、去b对比
相当于执行
select b_id from b where b_id = XX  and parent_id=5

#3、得到最终结果

所以说对于a来说，是全表扫描，如果a量比较大的话，效率就比较低了。

解决措施:

select * from a  inner join (select * from b where parent_id = 5) as c on a.b_id = c.b_id

先把in里面的需要的数据查出来，和a表做一个连表查询，这种方法在a数据量大的情况下，速度较块。（因为走a的索引）

EXITS原理与in类似，也是会全表扫描from后面的表

Union优化

尽量使用 union all，表示不去重，然后在程序中去重，提高了效率

Explain命令详解

mysql> explain select * from t1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

1、select_type

可能为以下几个值：

simple（不含子查询）

primary（最外面的select，含子查询或者派生查询）， subquery（非from子查询），derived（from型子查询），union（联合查询除第一个以外的查询，即union关键字后面的查询）， union result（联合查询的结果）

2、partitions

匹配到的分区

3、type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

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

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

4、possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

5、key

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

6、key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

不损失精确性的情况下，长度越短越好

7、ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

参考

https://www.cnblogs.com/jiawen010/p/11805241.html

其他技巧

计算sql执行时间

show variables like 'profiling%'; 查看 Show Profile 是否开启

set profiling=on; 开启 Show Profile

set profiling_history_size=150;设置显示条数默认15


使用步骤；

1.执行SQL

2.通过 show profiles; 指令查看结果

3.show profile cpu, block io for query SQL编号;

4.参数

参数备注：
ALL：显示所有的开销信息
BLOCK IO：显示块IO相关开销
CONTEXT SWITCHES：上下文切换相关开销
CPU：显示CPU相关开销信息
IPC：显示发送和接收相关开销信息
MEMORY：显示内存相关开销信息
PAGE FAULTS：显示页面错误相关开销信息
SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息
SWAPS：显示交换次数相关开销的信息
日常开发需要注意的结论（出现以下字段需要进行调优了）

converting HEAP to MyISAM：查询结果太大，内存都不够用了往磁盘上搬了。
Creating tmp table：创建临时表，mysql 先将拷贝数据到临时表，然后用完再将临时表删除
Copying to tmp table on disk：把内存中临时表复制到磁盘，危险！！！
locked：锁表