sql优化详解

sql优化:

第一种软优化:优化sql语句

重启mysql:service mysql restart;
mysql清屏:system clear、ctrl+L
mysql的逻辑分层:连接层,服务层,引擎层,存储层
innodb(默认):事务优先,(适合高并发操作,行锁)。
myisam:性能有限(表锁)
查看数据库引擎:支持哪些引擎?
show engines;
查看当前使用的引擎:show variables like ‘%storage_engine%’;

指定数据库对象的引擎:
create table tb(
id int(4) auto_increment,
name varchar(5),
dept varchar(5),
primary key(id)
)ENGING=MyISAM AUTO_INCREMENT=1

查看那些表加了锁: show open tables;// 1代表被加了锁。
分析表锁定的严重程度:show status like ‘table%’;
table_lock_immed
iate:立刻能获取到的表。
table_locks_waited: 需要等待的表锁数。 //后边的数越大,说明存在越大的锁竞争。
如果table_locks_immediate/table_locas_waite >5000 建议使用innodb引擎,否则用Myisam引擎。

DDL表示Data Definition Language数据定义语言,主要包括CREATE,ALTER,DROP;隐性提交的,不能rollback。
DML表示Data Manipulation Language数据操作语言,主要的DML有SELECT,INSERT,UPDATE,DELETE;可以手动控制事务的开启、提交和回滚的。

行锁是通过事务来解锁的;
行锁的特殊锁:间隙锁。也就是值在范围内,但是不存在。(如果操作一个表中数据的条件是大于1小于5的,但是数据中心没有3,执行到回滚段中时,where后边的条件内的所有数据都加锁,如果这个时候有个语句来操作where条件之间的数据,比如添加数据,那么是被锁定的)。
表锁是通过:unlock tables来解锁的,也可以通过提交或回滚事务来解锁;

行锁(默认InnoDB):
mysql每执行一行语句自动默认commit,(set autocommit=0,begin,start transaction三种设置不自动提交)
在查询语句后加for update 会对查询的语句加锁,别人在做操作时时加锁的。
oracle默认不自动commit,需要手动提交。
什么情况下会加锁:当两个用户同时访问一条数据,第一个人正在执行但是没提交,第二个人去操作会被锁行处于等待状态,如果等待过程中对方提交了(提交就是释放锁),则可执行,如果对方长时间没提交,则放弃操作。
行锁锁一行,操作不同数据不影响。
如果没有索引,行锁会转为表锁
查看目前的索引情况: show indexfrom linelock;
为表中的列加索引: alter table 表名 and index 索引名(列名)
如果索引列发生了类型转换,则行锁失效。
缺点:比表锁性能消耗大。
优点:并发能力强效率高。
所以高并发用InnoDB,否则则用MyISAM。

删除mysql注意事项:电脑自带的程序删除,将缓存文件中mysql(隐藏的)删掉,删除注册表中和mysql相关的(查找-删除-查找下一个-删除),重启计算机。
mysql端口号默认3306.
linux版本5.5.XX(XX代表数字)

mysql复制的原理:

主机做增删改操作,从机做查操作,当主机昨晚操作之后,会以日志的方式存放到这个binary log日志文件中,在从机有一个io线程来对这个binary log文件做读写操作,当发现有修改的数据时,会将修改的数据读出来写到从机的Relav log日志文件中,然后在通过sql线程对从机的数据库做读写操作。
mysql的主从复制是异步的,串行的,有时间延迟。所以如果主机或者从机坏掉了,备份的话可能会丢失一些少的数据。

操作数据库的流程:
首先客户端访问服务器提供sql语句--------》通过连接层,不做处理,提供与客户端连接的服务-------》再到服务层,有两个功能,一种是提供各种用户使用的接口,第二个是提供sql优化器sql语句会经过优化处理(弊端有时候会改数据)------》再到引擎层,提供了各种存储数据的方式(常见的有innodb引擎和myisam引擎)-------》存储层,存储数据

查询数据库支持什么引擎:show engines;
查看当前的存储引擎: show variables like ‘&storage_engine&’;

sql优化:性能低,执行时间长,等待时间长,sql语句欠佳(连接查询),索引失效,服务器参数设置不合理(缓冲区,线程数)。

sql语句优化:主要是优化索引
编写过程: 如 select…frim…join…on…where…group by…having…order by…limit…
解析过程:from…on…where…group by…having…select…order by …limit…

索引:相当于数的目录
索引:index是帮助mysql高效获取数据的数据结构,它是一种数据结构(B树,hash树,二叉树),索引用的是B树数据类型。

B树索引:(参考下图在网上找的)
拓展:三叉树,有数据有指针,比二叉树更高效性能更高,三层三叉树存的数据时百万级的,BTree树有很多类型,一般情况下是只B+树,数据全部存放在叶节点中(可以简单的理解为最底层)查询数据的次数是树的高度,如果树的高度是三就差三次。

不需要索引的条件:
1.索引本书是占内存/磁盘空间的。
2.索引不是所有情况都用的(数据量少,频繁更新的字段,很少使用的列)。
3.索引会降低增删改的效率(提高查询,降低增删改)。

优势:提高查询效率(降低了IO的使用率)。
降低CPU使用率(索引不需要排序,直接整理输出)
具体解析流程可查看之下网址:
https://www.cnblogs.com/annsshadow/p/5037667.html

索引的类型:
单值索引:如name的列加索引,那么这个name的索引就是单值索引(一个表可以有多个单一索引)。
唯一索引:唯一索引的值不能重复(一个表中可以有多个唯一索引)。
符合索引:多个列构成的索引(相当于书的二级目录)。

创建索引:create 索引类型 索引名 on 表(列名)
符合索引:create 索引类型 索引名 on 表(列1,列2…)
创建单值索引:create index name_index on user(name);
创建唯一索引:create unique index name_index on user(name);
创建符合索引:create index name_age_index on user(name,age…);

第二种创建索引:修改表(具体语法参考上边)
alter table 表名 add 索引类型 索引名(列名)
如果在id列上加上primary key 即使你没有加索引,数据库也会给你创建主键索引。如果那个列创建了唯一数据库也会为你添加唯一索引。
主键索引和唯一索引的区别:主键索引不能重复不能是null,唯一索引不能重复,可以是null。
删除索引:drop index 索引名 on 表名;
查询索引 show index from 表名 /G或者直接show index from 表名;

sql性能问题:

1.分析sql的执行流程:explain 可以模拟sql优化器执行sql语句,从而让程序员知道自己写的sql语句的状况,
2.mysql的查询优化会干扰我们的优化。

具体优化可查看官网优化:
https://dev.mysql.com/doc/refman/5,5/en/optimization.html
查询执行计划:explain+sql语句

随机拓展(desc降序,asc升序)

表的执行顺序是:笛卡尔积原理(三个数相乘,内层小的优先使用)
如:2 3 4 结果不管怎么成都是24
但是 2x3 =6 6x4=24 和 3x4=12 12x2=24 可以看出虽然结果一样 但是内层的数值6明显要小于12.
数据小的表优先查询,id值不同,id值越大越优先查询。
未完待续。。。。。

第二种 硬优化(分区):
针对海量数据键索引又会占用空间,和索引的维护,就可以使用分区(也就是大表拆成小表)。

竖切和横切。
竖切:垂直分表
两张表要有关联性所以两张表都要有id列。

横切:水平分表
一般情况下使用横切(水平分表)。因为建表的时候表的字段都不会设置的特别大,并且关联性也很强,以及记录不稳定。
好处是:物理上将一个表切成多个表。
坏处是:需要改sql语句,后期代码量的维护增加。(也就是说表分了,sql的语句的表名的都需要改变)
5.1之后以一个插件的形式添加了分区技术避免了这些坏处。

分区技术:
常用的分区技术是四种:(总共有五种,最常用的技术的顺序是下边的顺序)
1.RANGE分区:基于一个给定连续区间的列值(也就是给定的一个字段,基于这个字段为参考点),把多行分配给分区。
2.LIST分区:属于RANGE分区的一个特例,是以固定值或者是枚举值得分区(如男和女)。
3.HASH分区:将数据随机的平均分配到多个分区中(数值没有规律),可用于mysql分区技术的测试。
4.KEY分区:类似于HASH分区,区别在于KEY分区值计算一列或多列,且mysql服务器提供其自身的哈希函数。

RANGE分区:
如图:(网上找到的)

上边是创建表,中间的括号没有分好注意。
下边的创建四个分区的sql语句,语句含义依次是是:
所有store_id小于6的,放到p0分区,
所有大于等于6小于11的放到p1分区,
所有大于等于11小于16的放到p2分区,
所有大于等于16小于21的放到p3分区(特别注意本表store_id的值定义的是从1–20)。

下边是按年分区的例子:

意思跟之前的差不多,最下边的是大于等于2001年的放到p3分区。

虽然分了四个区,但是存储的文件是八个,每个分区生成两个文件,一个是存索引一个是存数据。再加上两个文件,也就是说如果一个表分四个区的话,会产生10个文件。
LIST分区:
首先来看下代码:

如图:意思差不多,如果store_id的值是3,5,6,9,17则放到pNorth分区,下边的一个意思(固定值、枚举值)。

HASH分区:(方便)

如图:根据hired的年来作为基准点,分四个区。不确定年份是多长时间的,就用HASH来随机的把数据分配到四个分区中。(通过hash算法的值来平均分配)

KEY分区:
对于其他存储类型的表,使用自己内部的hash算法来存的,这个函数是基于PASSWORD()一样的算法法则,可以不是整数如字符串等。
MySQL簇使用函数MD5()来实现KEY分区。

总结:

更详细的在我的最全面试文章的下边!!!
欢迎查看。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值