MySQL学习手册--进阶篇

致力于性能稳定、执行高效的Mysql语句!

大家好,我是隔壁小王~这次发布的是Mysql的进阶版,可以搭配之前发布的基础版一起观看。

 

1.修改linux版本mysql密码

/user/bin/mysqladmin -u root password ******

 

2.Linux的Mysql自启动服务

chkconfig mysql on

检查:chkconfig --list | grep mysql,得到的结果是mysql的运行级别,详细内容可以通过cat /etc/inittab这个命令来查看这些级别的含义。

或者:

使用更底层的命令:ntsysv,命令前带有*的表示开机启动。

 

3.修改Linux的Mysql字符集

如果在mysql插入中文后出现乱码?

查看字符集类型:show vaiables like '%char%'

通过编辑/etc/my.cnf进行修改mysql的配置文件。

[client]模块的末行加上:default-character-set=utf8

[mysqlid]模块中加上:

character_set_server=utf8

character_set_client=utf8

collation-server=utf8_general_ci

[mysql]模块的末行加上:default-character-set=utf8

修改配置后记得重启mysql服务:service mysql stop,service mysql start

注意:要在修改配置后新建的表才能支持中文。

 

4.mysql的存储引擎

最常用的两个:InnoDB和MyISAM。

两者的主要区别:

查看引擎的指令:

看你的mysql现在已提供什么存储引擎:show engines;

看你的mysql当前默认的存储引擎:show variables like '%storage_engine%';

 

5.导致sql执行慢的常见原因:

查询语句编写水平低;

索引失效;

关联查询太多join;

服务器调优及参数设置。

 

6.机器读取mysql的顺序

from - on - join - where - group by - having - select - distinct - order by - limit

 

7.sql的所有join关系

 

8.外键

例如有两个表   

A(a,b):a为主键,b为外键(来自于B.b)   

B(b,c,d):b为主键    

如果我把字段b的外键属性去掉,对编程没什么影响,所以说一般的简单表结构并不会建立外键的关联。

如上面,A中的b要么为空,要么是在B的b中存在的值,有外键的时候,数据库会自动帮你检查A的b是否在B的b中存在。

 

9.索引--index

9.1什么是索引

索引是帮助MySql高效获取数据的数据结构(这是本质)

索引的目的在于提高查找效率,类比于字典。可以理解为“排好序的快速查找数据结构”

官方解释:在数据之外,数据库系统还维护着满足特性查找算法的数据结构,这些数据以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。

 

9.2索引的实现方式示例

上图是一个简单的索引实现的原理,这里是二叉查找树(最常用的是b-tree)。每个节点分别包含索引值和一个指向对应数据物理地址的指针。这里的索引基于大的向右小的向左,因此在查找过程中可以省去相当多的无效搜索,因此速度很快。

 

9.3索引的优势和劣势

优势:

1)降低了数据库的io成本。

2)降低了cpu的运算成本。

劣势:

1)索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引是需要占用空间的。

2)索引虽然大大提高了查询速度,但是会降低更新表的速度,如对表进行更新插入操作时,Mysql不仅要保存数据,还要保存一下索引文件每次更新添加的索引列的字段。

 

9.4索引的分类

1)单值索引

一个索引只包含单个列,一个表可以有多个单列索引。

create index 索引名(格式如idx_table_field) on 表名(字段名);

2)唯一索引

索引列的值必须唯一,但允许有控制。

3)符合索引

一个索引包含多个列

create index 索引名(格式如idx_table_field) on 表名(字段名1, 字段名2);

4)基本语法

创建:

create [unique] index indexName on tableName(columnName1,2,3...) ;

alter tableName add [unique/fulltext] index [indexName] on (columnName1,2,3...) ;

如果建唯一索引就用unique,全文索引使用fulltext。

删除:

drop index [indexName] on tableName;

查看:

show index from tableName;

 

9.5索引的结构和检索原理

BTree索引

这是一个b+树,深蓝色表示数据项,黄色表示指针。对于根节点,p1表示小于17的,p2表示17到35之间的,p3表示大于35的,真实的数据存在于叶子节点,非叶子节点不存储真实的数据,只存储索引搜索方向的数据项。树的高度决定了你查索引的速度,索引的优势在于可以将上百万次的查找减少到数次的查询。

Hash索引

full-text索引

RTree索引

 

9.6索引的使用条件

适合:频繁作为查找条件的;查询中与其他表关联的字段;高并发优选复合索引;需要排序的字段;查询中统计或者分组字段。

不适合:频繁更新的字段;表的记录很少;数据重复且分布平均的表字段。

索引的选择性是索引列中不同数值的数量与数据总数的比值,这个数值越接近1索引的性能越明显。

 

10.explain

sql语句的性能优化器,可以给出相应的sql分析结果。用发:Explain + SQL语句

执行计划包含的信息:

 

10.1各个字段的解读:

id:select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序。它的值有三种情况:1)id相同,都是同级别查询,不存在子查询;2)id都不同,存在子查询,id会自增,id大的优先级越高,子查询的优先级会更高;3)id存在部分不同,既存在同级别的查询,也存在不同级别的查询。id越大,优先级越高,对于mysql来说越先执行。

 

select_type:常见3种取值。

1)simple(简单的查询,查询中不包含子查询或者联合查询);

2)primary(查询中包含子查询,最外层查询是primary);

3)subquery(查询中包含子查询,非最外层查询是subquery);

4)derived(在from列表中包含的子查询被标记为derived--衍生,mysql会地柜执行这些子查询,把结果放在临时表里);

5)union(若第二个select出现在union后,则被标记为union;若union包含在from子句的子查询中,外层select被标记为derived);

6)union result(从union表获取结果的select)。

 

table:表名

 

type:type的值类型如下

显示查询使用了何种类型,最常见的从最好到最差依次是:system>const>eq_ref>ref>range>index>ALL,只要能达到range和ref级别就可以了。

system:表只有一行记录,等于系统表,这是const类型的特例;

const:表示通过索引一次就找到了,常出现于where加主键或唯一索引。

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

ref:非唯一性索引扫描,返回匹配某个单独值的所有行。

range:只检索给定范围的行,使用一个索引来选择行,key列显示使用了哪个索引,一般出现在where后加between、>、<等。

index:全索引扫描,只遍历索引树。

all:全表扫描。

 

possible_keys:显示可能应用在这张表中的索引,属于mysql推测。

 

keys:本次sql中用到的索引。

 

key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在确保精确性的前提下,长度越短越好。key_len为索引字段的最大可能长度,并非实际使用长度,这是根据表的定义计算出的,不是表内检索出的。

 

ref:显示索引的哪一列被用了,可以理解为在查询的过程中作为查询的条件的是哪些列,值可能是一个列名或者const。

 

rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数。

 

extra:包含不适合在其他列中显示但十分重要的额外信息,它一般指的是你的sql使用了哪些搜索的功能。它包含如下的集中类型:

1)Using filesort:说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,mysql无法利用索引完成的排序操作称为文件排序。要尽量避免这种情况,这种情况是一种低效的sql。例如,你创建了一个包含三列的复合索引,在做group by或者order by的时候尽量按照索引的创建顺序进行排序会更好,如果跳过索引2只用索引3进行排序,就会出现这种使用文件排序的情况。

2)Using temporary:如果出现这种,则sql性能降低严重。这种情况是新建了一个内部临时表。

3)Using index:高性能的表现,表示相应的select操作使用了覆盖索引,避免访问了表的数据行。如果同时出现了Using where,表名索引被用来执行索引键值的查找,否则表明索引用来读取数据而非执行查找动作。

4)Using where:使用了where条件。

5)Using join buffer:使用了连接缓存。

6)impossible where:where子句的值总是false,类似于where 1=0。

7)select tables optimized away:略

8)distinct:略

 

11.join语句的优化结论

  • 尽可能减少join语句中的nestedloop(嵌套)的循环总次数:永远用小的结果集驱动大的结果集(也就是left时右表要小);
  • 优先优化嵌套的内层循环;
  • 保证join语句中被驱动表上join条件字段已经被索引。
  • 在一些特殊情况下,不要太吝啬joinbuffer的设置。

 

12.索引的优化

索引失效常见的原因:

注意:最佳左前缀的意思说白了就是使用where进行索引条件搜索的时候,where后接的索引列顺序要以建立索引的顺序为参照,查询要从索引的最左前列开始不能跳过中间列,例如索引建立了A、B、C,那么where后要可以使用A=xx或者A=xx and B=xx或者A=xx and B=xx and C=xx,而不可以使用B=xx and C=xx这种不按索引建立顺序的条件,如果这样搜索类型就会由ref变成all。

注意:在select的内容是非覆盖索引时,使用like %..%和%..都是全表扫描,而..%不是。如果一定要使用like %..%但不能是全表扫描,则select的列需要覆盖索引(即这些列需要建立索引)。

注意:在写sql的时候,varchar型的搜索一定要加'',否则将产生类型转换。

 

13.sql性能分析流程

1)观察,至少跑一天。

2)开启慢查询日志,设置阙值,比如超过5秒钟的就是慢sql,并将它抓取出来。

3)explain + 慢sql分析。

4)show profile。

5)SQL数据库服务器参数调优。

 

14.小表驱动大表,即小的数据集驱动大的数据集

原理:

select * from A where id in (select id from B)

等价于:

for select id from B

for select * from A where A.id = B.id

当B表的数据集小于A表的数据集时,用in优于exist。

 

select * from A where exists (select 1 from B where B.id = A.id)

等价于:

for select * from A

for select * from B where B.id = A.id

当A表的数据集小于B表的数据集时,用exists优于in。

这句话的意思是将主查询的数据放在子查询中做验证。

 

15.Order by关键字优化

尽量使用index方式排序,避免使用filesort方式。

order by使用index的两种情况:

1)order by 语句使用索引最左前列

2)使用where子句与order by子句条件列组合满足索引最左前列

尽可能在索引列上完成排序操作,遵照索引建的最佳左前缀。

如果不在索引列上,filesort有两种算法,mysql就要启动双路排序和单路排序,单路是新出的算法总体好过双路,但是要看单路一次能不能把数据读完,如果读不完单路就变成了多路。此时可以对数据库的缓存设置进行调整。

 

16.Group by关键字优化

Group by与order by的规则基本一致。

 

17.show profile的用法

profile是查看各历史sql的运行状态的语句。

1)查看状态:show variable like 'profiling'

2)打开profile:set profile=on

3)查看结果:show profiles

4)诊断:show profile cpu,block io for query

 

18.Mysql锁机制

锁是计算机协调多个进程或者线程并发访问某一资源的机制。

 

18.1表锁

表锁偏向MyISAM的存储引擎:开销小,加锁快;无死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。表锁有读锁和写锁两种。

读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会相互影响。

写锁(排它锁):当前写操作没有完成前,它会阻断其他写锁和读锁。

手动锁表:lock table 表名 read/write, 表名2 read/write, 其他;

手动解锁所有表:unlock tables;

查看各表的加锁情况:show open tables;

如何分析表锁定:show status like '%表名%'

读锁的一些性质:

当在一个会话中对A表加上读锁后,在该会话中会有如下情况:

1)该会话可以读A表;

2)该会话不可以写A表;

3)该会话不可以读其他表。

4)其他的会话可以读任何表,但如果要写A表会出现阻塞。

写锁的一些性质:

当在一个会话中对A表加上写锁后,在该会话中会有如下情况:

1)该会话可以读A表;

2)该会话不可以写A表;

3)该会话不可以读其他表;

4)其他会话读A表会阻塞;

5)其他会话写A表会阻塞。

 

此外,Myisam的读写锁调度是写优先,这也是myisam不适合做写为主表的引擎,因为写锁后,其他线程不能做任何操作,大量的更新会使查询很难得到锁,从而造成长期阻塞。

 

18.2行锁

行锁偏向InnoDB引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突概率最低,并发度也最高。InnoDB与MyISAM最大的不同有两点:一是采用了事务;二是采用了行级锁。

 

并发事务带来的常见问题

更新丢失

脏读

不可重复读

幻读

 

数据库的隔离级别

 

无索引行锁升级为表锁

如果b列是varchar型索引列,但是在update时没有对其值加'',则会导致索引失效,并且将行锁升级为表锁,对其他行也无法写入。此时其他的写入操作只有等待该update提交完毕后,才能继续写入,从而降低了并发的效率。

 

间隙锁

当会话1中对表A某个范围内的字段进行更新时(值X在该范围但是,表中无值X),那么在该操作提交之前,会话2对表A进行插入X值的操作会出现阻塞。

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内并不存在的记录,叫做间隙(‘GAP’)。InnoDB也会对这个间隙加锁,叫做‘间隙锁’。

 

如何锁定某一行

 

行锁的分析

当出现等待次数多,等待时间长的情况时,就调用show profile来进行问题排查。

 

行锁的优化建议

尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁;

合理设计索引,尽量缩小锁的范围;

尽可能较少检索条件,避免间隙锁;

尽量控制事务大小,减少锁定资源量和时间长度;

尽可能低级别事务隔离。

 

原创不易,如有转载请注明出处,感谢~~

  • 2
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值