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分区。

总结：

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