文章目录
MySQL高级
1、Linux 下的 MySQL(5.7)
修改字符集乱码
字符集乱码原因
-
如果在建库建表的时候,没有明确指定字符集,则采用默认的字符集 latin1,其中是不包含中文字符的
-
进入到mysql,查看默认字符集:show variables like ‘%char%’
永久修改
-
1、vim /etc/my.cnf (在文件末尾添加)
character-set-server=utf8 [client] default-character-set=utf8 [mysql] default-character-set=utf8
-
2、重启MySQL:
systemctl restart mysqld
-
3、进入到mysql,查看默认字符集:show variables like ‘%char%’
设置大小写不敏感
-
windows 系统默认大小写不敏感,但是 linux 系统是大小写敏感的
-
设置大小写不敏感
- 1、在 my.cnf 这个配置文件 [mysqld] 中加入 lower_case_table_names = 1
- 2、然后重启服务器:
systemctl restart mysqld
MyISAM 和 InnoDB 对比
对比项 | MyISAM | InnoDB |
---|---|---|
外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
行表锁 | 表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作 | 行锁,操作时只锁某一行,不对其它行有影响,适合高并发的操作 |
缓存 | 只缓存索引,不缓存真实数据 | 不仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响 |
关注点 | 读性能 | 并发写、事务、资源 |
默认安装 | Y | Y |
默认使用 | N | Y |
自 带 系 统 表使用 | Y | N |
2、索引
简介
- MySQL 官方对索引的定义为:索引(Index)是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质:索引是数据结构。可以简单理解为排好序的快速查找数据结构。
- 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引
- 优势
- 提高数据检索的效率,降低数据库的IO成本
- 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗
- 劣势
- 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息
- 实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的
Mysql 索引分类
- 单值索引:即一个索引只包含单个列,一个表可以有多个单列索引
- 唯一索引:索引列的值必须唯一,但允许有空值
- 主键索引:设定为主键后数据库会自动建立索引,innodb为聚簇索引
- 复合索引:即一个索引包含多个列
索引的创建时机
- 适合创建索引的情况
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段,外键关系建立索引
- 单键/组合索引的选择问题, 组合索引性价比更高
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
- 不适合创建索引的情况
- 表记录太少
- 经常增删改的表或者字段
- where 条件里用不到的字段不创建索引
- 过滤性不好的不适合建索引
3、索引优化
单表使用索引
- 1、最好全值匹配索引
- 查询的字段按照顺序在索引中都可以匹配到
- SQL 中查询字段的顺序,跟使用索引中字段的顺序,没有关系。优化器会在不影响 SQL 执行结果的前提下,给
你自动地优化
- 2、最佳左前缀法则
- 查询字段与索引字段顺序的不同会导致,索引无法充分使用,甚至索引失效
- 原因:使用复合索引,需要遵循最佳左前缀法则,即如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
- 结论:过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用
- 3、不要在索引列上做任何计算
- 不在索引列上做任何操作(计算、函数、(自动 or 手动)类型转换),会导致索引失效而转向全表扫描
- 4、索引列上不能有范围查询
- 将可能做范围查询的字段的索引顺序放在最后
- 5、尽量使用覆盖索引
- 查询列和索引列一致,不要写 select *
- 6、最好不使用不等于(!= 或者<>)
- mysql 在使用不等于(!= 或者<>)时,有时会无法使用索引会导致全表扫描
- 7、最好不使用 is not null
- is not null 用不到索引,is null 可以用到索引
- 8、like 的前后模糊匹配
- 前缀不能出现模糊匹配“%”
- 9、减少使用 or
- 使用 union all 或者 union 来替代
关联查询优化
- 1、在优化关联查询时,只有在被驱动表上建立索引才有效
- left join 时,左侧的为驱动表,右侧为被驱动表
- inner join 时,mysql 会自己帮你把小结果集的表选为驱动表
- straight_join: 效果和 inner join 一样,但是会强制将左侧作为驱动表
- 2、子查询尽量不要放在被驱动表,有可能使用不到索引
- 3、能够直接多表关联的尽量直接关联,不用子查询
order by优化
- 1、无过滤,不索引
- where,limit 都相当于一种过滤条件,只有用了where或limit,order by才能使用上索引
- 2、顺序错,必排序
- order by字段顺序与索引字段顺序不一样,会 “文件排序”影响效率
- 3、优化 using filesort
- ①增大 sort_butter_size 参数的设置
- 提高这个参数都会提高效率,要根据系统的能力去提高,因为这个参数是针对每个进程的
- 1M-8M 之间调整
- ②增大 max_length_for_sort_data 参数的设置
- mysql 使用单路排序的前提是排序的字段大小要小于 max_length_for_sort_data。
- 提高这个参数,会增加用改进算法的概率。但是如果设的太高,数据总容量超出 sort_buffer_size 的概率就增大,明显症状是高的磁盘 I/O 活动和低的处理器使用率。
- 1024-8192 之间调整
- ③减少 select 后面的查询的字段
- 需要查询哪些字段就在select后面写哪些字段,不要使用 select *
- ①增大 sort_butter_size 参数的设置
group by 优化
- group by 使用索引的原则几乎跟 order by 一致
- 唯一区别是 group by 即使没有过滤条件用到索引,也可以直接使用索引
4、Explain 性能分析
-
使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句,从而知道 MySQL 是如何处理你的 SQL 语句的
-
用法: Explain+SQL 语句
-
id
-
select 查询的序列号,包含一组数字, 表示查询中执行 select 子句或操作表的顺序
-
id 值越大优先级越高,越先被执行
-
三种情况
-
①id 相同, 执行顺序由上至下
-
②id 不同,id 不同,如果是子查询,id 的序号会递增,id 值越大优先级越高,越先被执行
-
③有相同也有不同
-
-
-
select_type
-
代表查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询
select_type属性 含义 SIMPLE 简单的 select 查询,查询中不包含子查询或者 UNION PRIMARY 查询中若包含任何复杂的子部分,最外层查询则被标记为 Primary DERIVED 在 FROM 列表中包含的子查询被标记为 DERIVED(衍生) MySQL 会递归执行这些子查询, 把结果放在临时表里。 SUBQUERY 在SELECT或WHERE列表中包含了子查询 DEPEDENT SUBQUERY 在SELECT或WHERE列表中包含了子查询,子查询基于外层 UNCACHEABLE SUBQUERY 无法使用缓存的子查询 UNION 若第二个SELECT出现在UNION之后,则被标记为UNION;
若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVEDUNION RESULT 从UNION表获取结果的SELECT
-
-
table
- 这个数据是基于哪张表的
-
type
-
查询的访问类型。是较为重要的一个指标,结果值从最好到最坏依次是:
-
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL (加粗是常见的)
-
一般来说,得保证查询至少达到 range 级别及以上,最好能达到 ref
type属性 含义 system 表只有一行记录(等于系统表),这是 const 类型的特列,平时不会出现,这个也可以忽略不计 const 表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据,所以很快,如将主键置于 where 列表中,MySQL 就能将该查询转换为一个常量 eq_ref 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描 ref 非唯一性索引扫描,返回匹配某个单独值的所有行.本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体 range 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束语另一点,不用扫描全部索引 index 出现index是sql使用了索引但是没用通过索引进行过滤,一般是使用了覆盖索引或者是利用索引进行了排序分组 all Full Table Scan,将遍历全表以找到匹配的行
-
-
-
possible_keys
- 显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用
-
key
- 实际使用的索引。如果为NULL,则没有使用索引
-
key_len
-
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。 key_len 字段能够帮你检查是否充分的利用上了索引。ken_len 越长,说明索引使用的越充分
-
-
ref
- 显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值
-
rows
- rows 列显示 MySQL 认为它执行查询时必须检查的行数。越少越好
-
Extra
-
其他的额外重要的信息
Extra参数 含义 Using filesort 影响索引效率,说明 mysql 会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。
MySQL 中无法利用索引完成的排序操作称为“文件排序”Using temporary 影响索引效率,使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。
常见于排序 order by 和分组查询 group byUsing index 索引效率不错,Using index 代表表示相应的 select 操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!
如果同时出现 using where,表明索引被用来执行索引键值的查找;
如果没有同时出现 using where,表明索引只是用来读取数据而非利用索引执行查找。Using where 表明使用了 where 过滤 Using join buffer 使用了连接缓存 impossible where where 子句的值总是 false,不能用来获取任何元组 select tables optimized away 在没有 GROUP BY 子句的情况下,基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化
-
5、查询分析
慢查询日志
介绍
- MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中
- 具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10秒以上的语句
使用
-
默认情况下,MySQL 数据库没有开启慢查询日志,需要我们手动来设置这个参数。
-
如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响
-
开启设置
SQL 语句 描述 备注 SHOW VARIABLES LIKE ‘%slow_query_log%’; 查看慢查询日志是否开启 默认 slow_query_log 的值为 OFF,表示慢查询日志是禁用的 set global slow_query_log=1; 开启慢查询日志 SHOW VARIABLES LIKE ‘long_query_time%’; 查看慢查询设定阈值 单位秒 set long_query_time=1 设定慢查询阈值 单位秒 -
永久生效需要修改配置文件 my.cnf 中**[mysqld]**下配置
[mysqld] slow_query_log=1 slow_query_log_file=/var/lib/mysql/atguigu-slow.log long_query_time=3 log_output=FILE
日志分析工具 mysqldumpslow
-
mysqldumpslow --help
参数 描述 -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 profile
-
查看 profile 是否开启
show variables like '%profiling%';
-
开启 profile
set profiling=1
-
使用 profile
-
查看最近的几次查询
-
show prifiles;
-
-
查看 sql 的具体执行步骤
-
show profile cpu,block io for query Query_id;
(cpu,block io=查看的类型,Query_id=需要查看的id号)
-
-
-
注意
-
当具体执行步骤中的 Status 为一下四中情况就得注意
-
6、MySQL锁机制
- 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响
- 写锁(排它锁):当前写操作没有完成前,它会阻断其他写锁和读锁。
表锁
-
特点
- 偏向MyISAM存储引擎,开销小,加锁快,无死锁,锁定粒度大,发生锁冲突的概率最高,并发最低
-
手动加表锁和释放表锁
-
数据库中表的加锁状态:
show open tables;
- 0 表示未锁,1 表示被锁
-
分析表锁定:
show status like 'table%';
-
通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定
- Table_locks_immediate:产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取锁这个值加1;
- Table_locks_waited:出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1),此值高则说明存在着较严重的表级锁争用情况;
-
-
加表锁:
- 读锁:
lock table 表名 read;
- 写锁:
lock table 表名 write;
- 读锁:
-
释放表锁:
nlock tables;
-
-
结论
- 对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作
- 对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作
- 读锁会阻塞写,但是不会堵塞读。而写锁则会把读和写都堵塞
- MyISAM在执行查询语句前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁
- Myisam的读写锁调度是写优先,这也是myisam不适合做写为主表的引擎。因为写锁后,其他线程不能做任何操作,大量的更新会使查询很难得到锁,从而造成永远阻塞
行锁
-
特点
- 偏向InnoDB存储引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
- InnoDB与MyISAM的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁
-
分析行锁定:
show status like 'innodb_row_lock%';
- Innodb_row_lock_current_waits:当前正在等待锁定的数量;
- Innodb_row_lock_time:从系统启动到现在锁定总时间长度;
- Innodb_row_lock_time_avg:每次等待所花平均时间;
- Innodb_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间;
- Innodb_row_lock_waits:系统启动后到现在总共等待的次数;
-
优化
- 尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
- 合理设计索引,尽量缩小锁的范围
- 尽可能较少检索条件,避免间隙锁
- 尽量控制事务大小,减少锁定资源量和时间长度
- 尽可能低级别事务隔离
页锁
- 开销和加锁时间界于表锁和行锁之间:会出现死锁;
- 锁定粒度界于表锁和行锁之间,并发度一般。
7、主从复制
基本原理
-
slave会从master读取binlog来进行数据同步,主从复制的三步骤
- 1、master将改变记录到二进制日志(binary log)。这些记录过程叫做二进制日志事件(binary log events)
- 2、slave将master的binary log events拷贝到它的中继日志(relay log)
- 3、slave重做中继日志中的事件,将改变应用到自己的数据库中。MySQL复制是异步的且串行化的
基本原则
- 每个slave只有一个master
- 每个slave只能有一个唯一的服务器ID
- 每个master可以有多个salve
配置步骤(一主一从)
主机修改
-
第一步:修改主机配置 my.cnf 文件, 在[mysqld]部分插入
log-bin=mysql-bin #开启二进制日志 server-id=1 #设置server-id
-
第二步: 重启mysql,创建用于同步的用户账号
- 创建用户:
CREATE USER '用户名'@'从机器数据库IP' IDENTIFIED BY '密码';
- 分配权限:
GRANT REPLICATION SLAVE ON*.* TO '用户名'@'从机器数据库IP' IDENTIFIED BY '密码';
- 刷新权限:
flush privileges;
- 创建用户:
-
第三步:查询master的状态,记录下File和Position的值
-
查询master的状态:
show master status;
-
记录下File和Position的值(从机需要用到)
-
从机修改
-
第四步:修改从机配置 my.cnf 文件, 在[mysqld]部分插入
server-id=2 #设置server-id,必须唯一
-
第五步: 重启mysql,打开mysql会话,执行同步SQL语句,参数都是主机里面设置的
CHANGE MASTER TO MASTER_HOST='主机IP', MASTER_USER='主机用户名', MASTER_PASSWORD='主机密码', MASTER_LOG_FILE='主机的File名字', MASTER_LOG_POS=主机的Position数字;
-
启动slave同步进程
start slave;
-
查看slave状态
-
show slave status\G;
-
下面两个参数都是YES,则说明主从配置成功!
- Slave_IO_Running:Yes
- Slave_SQL_Running:Yes
-
停止主从复制
- 在从机的mysql会话中执行
stop slave;