2019/04/13 数据库性能优化01

用户发送请求如何让mysql数据库经过若干复杂过程，最终让用户看到数据
第一让用户连接数据库，通常用户不会自己去写命令，通常都是用软件，运用接口
连接，就有connection连接器
比如c语言 native C api，java 是JDBC,不同语言连接数据库，mysql都提供了不同的程序等于驱动程序
在连接数据库之后，不是每个人都能连接的，
mysql是单进程多线程的，就会分一个线程给用户
mysql通过连接池找一个空闲的线程提供服务
如果服务结束了，这个线程就会把之前用于的信息清空，进而可以给其他用户使用，线程的复用
线程的限制
一个服务器的性能终究有限，链接的并发量过大，对于mysql来讲达到上限，怎么接受新的用户连接呢，要么排队，要么拒绝
mysql对外并发的连接数最多几百，不像httpd可以达到几万，所有性能瓶颈就在mysql这。
所以有些网站尽可能就把这些东西计算的不交给mysql，降低mysql负载
还要一种就是搭建集群
内存，
缓存，用户通过缓存访问数据
LAMP

sqlinterface
查询，插入等sql语言的命令就需要通过接口发送给mysql来执行，检查语句是否正确
正确的时候，进入到parser解释器，sql转换，翻译成二进制指令，对象特权，权限是否可以执行
在执行的时候还需要进行优化OPTIMIZER,可能在执行的时候有多种方式去实现，access paths 访问路径
选择一条最优路线来执行，这个过程可以通过执行计划来看到
执行完后，用户就可以看到返回的客户端了
读数据，数据先放到caches里
写数据，写的数据写放到buffer再写到磁盘上
要把数据写到磁盘上就需要借助存储引擎

mysql在走数据库的，文件系统有块，block，不同的块大小，不同的文件系统还可以指定
mysql数据库的块默认是16k，是文件系统的块的整数倍
所以用户在访问数据的时候，数据库抽取16k，mysql进行筛选，筛选出数据给用户显示
oracle也有块，最小也是文件系统的块的一倍，安装的时候可以指定。
mysql需要改源码

**用户发指令给数据库里的查询过程是如何实现的
用户在连接数据库后，查询东西，数据库不是马上去执行，而是查看查询缓存
但是有个要求，就是sql指令跟上次查询的时候，sql语句大小都一样，差一个字母都不行，因为使用哈希值比较的
程序员写代码规范就比较重要
如果查询缓存没有的话，就需要发到解析器，这个解析器负责把指令转换成2进制，还需要查看是否有访问权限，
接下来是解析树，就是要得到数据，有多种路径，预处理器，选择最优路径，有可能还需要进一步修改语句的写法
修改他经过优化之后，进而去执行，执行计划，发送到存储引擎，进而得到数据，返回用户的同时放到查询缓存
**

查询缓存也会增加系统的负载，不是所有的命令都适合缓存的
比如下面的命令

now（）直接返回系统时间，这个就没必要缓存


sql_no_cache，如果加这个选项的化，sql语句就不会被缓存了，如果想要看一个大表，几十兆。就不要缓存，增加负担
、
把这个关键字一加就成临时表了
临时表一般都是临时处理数据的，用完就会自动删除





这个默认值是4K

多大就无法缓存了，既然缓存不了，就建议加sql_no_cache


全部可用的缓存空间。0就是不限制


什么时候需要关闭缓存

demand，只有当用户需要缓存的时候，才去缓存
sql_cache 用户就在执行sqk语句的时候要求缓存

不加sql_cache如果是on，就默认给你是缓存的

如果你的数据库修改大于查询就可以考虑把缓存关了

如何优化缓存
命中率就是用户能够通过select语句查询缓存到的数据，命中越多，命中率越高
如果select语句不是大部分能够在缓存中查询的，查找原因，是不是默认缓存的数据1M太小了，如果够大，确实无法缓存
如果不够大，那就增加缓存


是否发生了很多验证的事情，缓存是否被碎片化了，如果是碎片化了，就减少这个值，就可以节约内存
查询缓存的最小单位

如果不是缓存被碎片化了，继续看是不是因为有内存过低导致问题，就可以增加缓存大小
如果仍然不是，是否有更新语句，这些就不适合缓存，修改的多，查的少就没必要去改了
如果没有更新，就可能还有一些错误配置
**
没有发生很多验证工作，就去看看缓存是否启用
启用了，但是从来没有查询，这是你第一次查询，第一次查询当然不会有缓存了

根据上面的过程来决定查询缓存的调大调小
下面是查询缓存的状态变量，状态变量就意味着不能进行修改

查看状态变量

查看和缓存相关的变量


计算公式，查询缓存中内存块的最小分配单位大小
缓存总大小-缓存剩余空间/在缓存区中sql的数量（有多少条sql语句已经被缓存了）
算出的戺每条语句空间占用的大小

命中的数量/(命中的数量+没有命中的数量=等于总的数量)
进行一次查询，查看缓存里有没有缓存的大小

查询缓存是否启用

但是经过查询，无法查询到缓存有所记录
、
查询的次数在涨

换一台机器测试是否查询能够缓存

未查询前也是空的

查询之后还是空的



加上强制缓存，还是无法查看

还有INNodb的缓存问题

预读就是，当用户去访问某个数据库资源的时候，相邻的或最新的时间，会把附近的数据再去读取
预读最好把周边的数据也缓存到 里面，提高了访问效率


索引的实现不是依赖于存储引擎的，而是数据库自身决定的，它的索引技术是差别很大的
能够花两次读取磁盘数据，就不要用10次，因为磁盘的速度是比较慢的
在磁盘上找数据，以前没有索引到处找，全表扫描，有了索引以后可以将数据按照一定的次序排序，排序完以后，可以快速的定位
索引本身缺点就是也要占用空间，有可能就会影响添加数据的速度

聚簇索引和非聚簇索引的区别就是数据和索引存储顺序是否一致

数据结构可以这样理解，如下

**key1类似学员编号，会把编号拿出来，按照从小到大来排序，比如1-100
假设有个30号学员 keyN是70号
key1=30号学员旁边有两个指针，指针指向，左边是小于30号的学生，右边是大于30号学员的指针）
这几个值也有指针，指向同一级别的
下面也可以表达
**
比如，上面是17,35学员
小于17的和大于17 的两个指针在下面
下面的也是小于8，大于8 的
这就是索引的存放格式

如果是主键索引。那么序号10的学生，会自动往下

叶子节点，不仅存放了索引，也存放了数据本身
从这里面找10号学员

找10号，就是小于17 从p1这里查找，应该是介于8和12
就找p2，下面就是叶子节点不需要跑到磁盘上去找了
聚簇索引，它的索引和数据是放在一块的
聚簇索引，就是数据和键是放在一块的
索引从小到大，数据存也必然从小到大，
如果顺序一致就是聚簇索引，如果不一致就是非聚簇索引

还有一种就不是聚簇索引，比如找学生姓名建索引，相当于对章节做索引
】
主键和id，索引的排序是按照abcd排序的，但是数据的存放是按照主键的顺序存放的
次序是不一致 ，就叫非聚簇索引
比如书的后面有附录，告诉你某个单词出现在第几页
但是只有书前的目录和我们的书的顺序一致
myisam不支持聚集索引
一般主键索引，就是把数据和索引放在一起的
二级索引则是分离的，是指针，通过指针去找数据

辅助索引就是二级索引
稠密索引，和稀疏索引，就是索引是否连续
hash的索引就 不适合按照 字母或者数字顺序来排

用的最多的是B+tree

hash是对数据运算，数据差一点的话数据就千差万别