mysql一些常用使用语句和常用配置-记录并见证自己的成长

创建用户并授权

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

host - 指定该用户在哪个主机上可以登陆，此处的 "localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录，如果想远程登录的话，将 "localhost" 改为 "%"，表示在任何一台电脑上都可以登录; 也可以指定某台机器可以远程登录;

GRANT privileges ON databasename.tablename TO 'username'@'host'

privileges - 用户的操作权限, 如 SELECT , INSERT , UPDATE 等 (详细列表见该文最后面). 如果要授予所的权限则使用 ALL.;
databasename - 数据库名, tablename - 表名, 如果要授予该用户对所有数据库和表的相应操作权限则可用表示, 如.*.

grant all privileges on mq.* to test@localhost identified by '1234';
 flush privileges;  //刷新缓存

修改密码

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

查看权限

show grants for dog@localhost；

对同一张表更新时查询

update table as a inner join (select * from table where field=condition using(id)) as b set a.field=if(b.field1=value,1,0)

主从复制

也就是所谓的水平扩展，mysql版本最好相同，5.7为例

master的操作

修改my.cnf中的[myslqd],添加

  [mysqld]
  log-bin=mysql-bin  #指定log-bin日志存放的路径，默认在/var/lib/mysql目录下，可以使用绝对路径
  sync_binlog=1 #每次提交事务前将日志同步到磁盘上，保证服务器崩溃时不丢失信息
  server-id=1  #只要唯一就好
  binlog-do-db=dbname  #只需要复制的数据库名，可以多次设置，通常不设置
  binlog-ignore-db=mysql  //被忽略的数据库，可以多次设置，通常不设置

启动或重新mysql,客户端登录，执行

 show master status;

  mysql> show master status\G;
  *************************** 1. row ***************************
               File: mysql-bin.000002
           Position: 1574458
       Binlog_Do_DB: wordpress
   Binlog_Ignore_DB:
  Executed_Gtid_Set:
  1 row in set (0.00 sec)

记录 File,Position,Binlog_Do_DB 的值

创建一个专门的用来同步的用户，不创建也可以，但要有REPLICATION SLAVE权限

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE,replication client ON *.* to 'replication'@'host';

注意数据库一定要是*,不然一直报错（大概==!）;
一般只需要replication slave权限就可以同步2进制日志文件，但replication client在监控和管理复制账号时需要，所以通常也一起授权。
如果是在laradock中，修改mysql/my.cnf文件后，不止要重启mysql,workspace也重启，实在不行全部build下==!;\

slave的操作

修改my.cnf中的[myslqd],添加

[mysqld]
log-bin=mysql-bin  #使用log-bin日志
server-id=2  #只要唯一就好
log_slave_updates=1  #将重放的事件也记录到自身的2进制日志中
read_only=1 #可以设置为只读，但属于super的还是可以修改
replicate-do-db=wordpress #要同步的数据库,可以多次使用，不填为master设置的
replicate-do-table=db.tablename #要同步的表,可以多次使用
replicate-do-table=db.% #要同步db中的所有表
relay_log=/path/to/logs/relay-bin #中继日志的存放路径
skip_slave_start=1  #阻止从库在崩溃后自动启动复制，此时需要手动执行slave start

启动或重新mysql,客户端登录，执行

mysql> CHANGE MASTER TO MASTER_HOST='host', MASTER_USER='username', MASTER_PASSWORD='passowrd', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=Position;

各个值与master status中的值相对应，MASTER_LOG_POS也可以写为0
再执行

start slave;  #开启同步
show slave status;  #查看同步状态

Slave_IO_Running和Slave_SQL_Running都为 Yes 才算成功。

特别说明

新版本可以作为老版本的备(从)库，但版本差异太大(主版本不一样(5.5 5.7之类的))一般老版本不能作为新版本的从库
复制的流程
- 主库记录二进制日志（binary log）
- 从库将主库的日志复制到自己的中继日志(relay log)中
- 从库将中继日志写入到数据库中
复制的方式
- 基于sql语句的复制(5.1之前)，也称为逻辑复制(有些语句在从库执行结果会和主库不一样,如使用了current_user(),now()之类的函数或触发器等)
- 基于行的复制，5.1后默认方式
从已经运行一段时间的主库复制到从库
- todo
1主多从的应用
- 为不同的角色使用不同的从库（添加不同的索引或使用不同的存储引擎）
- 把一个从库当作一个主库
- 将一个从库作为备份或测试数据库

explain

局限
- 不会反映触发器、存储过程或udf的查询影响
- 不支持存储过程
- 不反映执行中所做的优化
- 不显示关于查询的执行计划的所有信息
- 不区分具有相同名字的事物（对内存排序和临时文件都用filesort）
- 它的结果只是一个预估值
覆盖索引：一个索引包含所有需要查询的字段的值。
部分explain 列的说明

select_type	`simple` 简单查询不包含子查询或unoin查询 `primary` 复合查询(包含子查询或union) `subqueryselect`中的列中包含子查询 `rerivedfrom`子句中包含子查询 `union` 包含unoin的查询 `union ressult` unoin接的查询为一个临时表
table	查询的表名或表别名
type	由差到好 `all` 全表扫描，查询了所有行 `index` 按索引次序进行全表扫描,避免了排序的消耗 `range` 在有限制的索引内的范围扫描 `ref` 索引访问(查找)，返回索引中匹配的行(一般索引) `eq_ref` 索引返回唯一的一行记录，完全命中(主键或唯一索引命中) `const,system` 对查询的部分优化为一个常量 `NULL` 不需要扫描，直接返回结果
possible_keys	显示查询可以使用的索引
key	实际会使用的索引
key_len	使用的索引的字节数
ref	显示key列中索引的字段名和常量
rows	预估的读取行数，非结果集行数
filtered	它的值为百分比，乘以rows数量为预估的过滤的行数
extra	`using index` 将使用覆盖索引 `using where` 将在存储引擎检索行再进行过滤 `using temporary` 对结果排序会使用临时表 `using filesort`对结果使用一个外部索引排序，内存或磁盘 `range checked for each record` 没有好用的索引，新的索引将在不如的每一行上重新估算。

show status

返回一些计数器，包含服务器级和会话级的。

flush status ; //清空会话级的计数器

show status where Variable_name like 'Handler%' or Variable_name like 'Created%';

Created_tmp_disk_tables 创建的临时磁盘表数
Created_tmp_tables 创建的临时表数

show global status

 show global status where variable_name like 'threads%'

threads_cached  缓存的线程数
threads_connected 当前连接数据库的线程数
threads_created  历史连接过的线程数
threads_running 正在执行的线程数

优化索引

一般小表无需创建索引，数据量大才创建

索引的类型

索引可以是单列或多列，多列时的优先顺序为创建索引时的顺序

默认索引为 b-tree,所有值都是按顺序存储的，适合键值范围查找，全键值，键左前缀，也适用于order by 的排序
哈希索引(hash index),只有匹配索引所有列的查询才有效。
- 无法用于排序
- 不支持部分索引查找( 在a,b列建hash,但查询的只有a)
- 只等值(=、 in、 <=>)查询
- 如果值的哈希相同将影响性能
- 适用于数据值大多不相同的场景
空间(地理)数据索引(r-tree),需使用 gis相关函数
全文索引(full text)

索引的优点

减小服务器扫描的数据量
避免排序和临时表
将随机i/o变为顺序i/o

使用索引

索引不能是表达式的一部分也不能是函数的参数
where条件中的列顺序按照索引的顺序排列
范围查询使用 in not int以后，无法再使用后面的索引列了，in 与多个列的查询效果并不相同
联合索引：建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引（对于b-tree索引）。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
最左前缀匹配：在检索数据时从联合索引的最左边开始匹配：
- 对列col1、列col2和列col3建一个联合索引 KEY test_col1_col2_col3 on test(col1,col2,col3); 联合索引 test_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。 SELECT * FROM test WHERE col1=“1” AND clo2=“2” AND clo4=“4” 上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。
- 索引的字段可以是任意顺序
order by
- 当索引的列顺序和order by子名的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，才能使用索引来做排序
- 多表关联时只有order by的字段全部为第一个表时才能使用索引
- 都需要满足索引的最左前缀要求

查询的生命周期

查询缓存==>解析器==>预处理==>查询优化器==>查询执行引擎==>将结果缓存并返回
解析器：检验语法是否正确
预处理器：验证权限
查询优化器：
- in 与多个or并不等价，in中的数据是先进行排序，再通过2分查找，时间复杂度为O(logN),多个or的时间复杂度为O(n)
执行查询计划

查询优化

limit 在数据量大的时候性能会下降，可以使用延迟关联

select * from table inner join (select id from table where condition order by field limit offset,row) temp using(id)

先查出主键再关联查询

表的分区

分区指的是一张表存储的时候是多个文件组成而非完整的一个但操作时还是相当于一张完整表的操作
一个表最多只能有1024个分区
分区表达式必须是整数，或者是返回整数的表达式
分区表不能使用外键约束
可以使数据分布在不同的设备上
一般用于在数据量比较大的表中
分区的类型：
- 列
- 键值
- 哈希
- 列表范围

查询缓存

mysql会将一些没有使用不确定函数如now(),current_date()等函数或不是用户自定义函数，存储函数、用户变量或查询的不是临时表、系统权限表的结果缓存在一些缓存表（内存）中，通过哈希值引用。对查询的性能有较大的提升，但对写入性能有影响。目前mysql8已经删除也查询缓存功能，所以以下只针对5.7及以下版本。

一般在sql语句非常复杂时，或update,insert,delete操作相对较少时，或查询返回的数据比较少时
判断查询缓存有效：
- 查看show status中的Qcache_hits和Qcache_inserts的比值，当这个比值大于3:1有是有效的，如果没有达到10:1可以考虑禁用查询缓存.
配置和维护查询缓存，修改my.cnf的值

参数	解释
have_query_cache	当前版本是否支持查询缓存功能
query_cache_limit	允许 Cache 的单条Query 结果集的最大容量，默认是 `1MB`，超过此参数设置的 Query 结果集将不会被 Cache
query_cache_min_res_unit	每次分配内存的最小空间大小，也就是每个 Query 的 Cache 最小占用的内存空间大小
query_cache_size	使用的内存大小，默认值为 `16M`，大小必须是 `1024` 的整数倍，如果不是整数倍，MySQL 会自动调整降低最小量以达到 `1024` 的倍数
query_cache_type	控制 Query Cache 功能的开关，可以设置为`0(OFF)`, `1(ON)` 和 `2(DEMAND)` 三种： 0(off) 关闭 Query Cache 功能，任何情况下都不会使用 Query Cache 1(on) 开启 Query Cache 功能，但是当 SELECT 语句中使用的 SQL_NO_CACHE 提示后，将不使用 Query Cache 2(demand) 开启 Query Cache 功能，但是只有当 SELECT 语句中使用了 SQL_CACHE 提示后，才使用 Query Cache
query_cache_wlock_invalidate	控制当有写锁加在表上的时候，是否先让该表相关的 Query Cache 失效 1(true) 在写锁定的同时将使该表相关的所有 Query Cache 失效 0(false) 在锁定时刻仍然允许读取该表相关的 Query Cache

show status中的缓存值说明

show status中的缓存值说明

Qcache_free_blocks	目前还处于空闲状态的 Query Cache 中内存 Block 数目
Qcache_free_memory	目前还处于空闲状态的 Query Cache 中内存量
Qcache_hits	Query Cache 命中次数
Qcache_inserts	向 Query Cache 中插入新的 Query Cache 的次数，也就是没有命中的次数
Qcache_lowmem_prunes	当 Query Cache 内存容量不够，需要从中删除老的 Query Cache 以给新的 Cache 对象使用的次数
Qcache_not_cached	没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于 query_cache_type 设置的不会被 Cache 的 SQL
Qcache_queries_in_cache	目前在 Query Cache 中的 SQL 数量
Qcache_total_blocks	Query Cache 中总的 Block 数量

全文索引

5.6 innodb以上

在配置文件my.cnf中设置分词大小

  [mysqln]
  ngram_token_size=2    默认就是2 ，可以不用设置

[mysqln]
ngram_token_size=2    #默认就是2 ，可以不用设置

添加全文索引

after table table_name add fulltext [key|index] `index_name`(column1[,column2[,.....]]) with parser ngram

查询

 select * from table_name where match(colum1[,column2]) against('keywords'[mode]); //语法


  select match(colum1[,column2]) against('keywords') as relation from table_name where match(colum1[,column2]) against('keywords'); //relation为相关度

  select * from table_name where match(colum1[,column2]) against('keywords');//默认自然语言方式， 对keywords自动进行分词搜索


  select * from table_name where match(colum1[,column2]) against('+key1+key2' IN BOOLEAN MODE);//布尔模式，key1和key2同时存在时返回结果


  select * from table_name where match(colum1[,column2]) against('+key1-key2' IN BOOLEAN MODE);//key1存在key2不存在时返回结果


  select * from table_name where match(colum1[,column2]) against('>key1+key2' IN BOOLEAN MODE);//key1和key2同时存在，但降低key1的相关性

mode默认为 IN NATURAL LANGUAGE MODE,常用还有IN BOOLEAN MODE;
match against 在select 中返回相关度（词汇覆盖度越高，词频越高相关度越大），在where中时为匹配条件。
match函数指定的列必须与创建全文索引时的列完全相同
限制或禁止使用，因为插入，更新，删除时会非常慢，修改100词，需要对索引进行100次操作

某个分类下的前几名查询

使用变量

set @ranks := 0, @type := 0;
select *
from (select type_id,colunm,if(@type = type_id, @ranks := @ranks + 1, @ranks := 1) as ranks,@type := type_id as tid
      from table_name
      where condition order by order,) aa
where ranks <= 2;

使用多个Union

select * from table_name where type=1
union 
select * from table_name where type=2

where (select count())

todo

锁

更新插入

有就更新,没有就插入
所在表必须要有主键或唯一索引

insert table (field1,field2,...) value (value1,value2,...) on duplicate key update field1=value1 , field2=value,...