【读书笔记】Mysql优化的个人理解

拜读了一下知乎大神的sql优化传送门

sql基础架构图

左边，客户端，like CMD、或者workbench，navicat，右边都是mysql的服务端。
当查询出数据后，会返回给执行器。执行器一方面将结果写到查询缓存，当下次再查询，可以直接从缓存获得数据，另一方面，将结果响应回客户端。

为什么要进行sql优化

当多表连接查询，子查询的时候，服务器执行时间过长，等待结果时间太长。
比如

select dinstinct  ..from  ..join ..on ..where ..group by ..having ..order by ..limit ..

解析过程为

from .. on.. join ..where ..group by ..having ..select dinstinct ..order by ..limit ..

sql 优化主要是优化索引，索引相当于字典的目录，有了索引可以很方便的定位某条记录。

什么是索引

索引是帮助mysql高效获取数据的一种【数据结构】，索引是一种树结构，mysql一般是【B+树】，树的特点是子元素比父元素小，放左侧，相反放右侧。

第一：mysql的每行记录有一个硬件地址，比如age=50，指向硬件地址，0x09，22对应0xbc，即树形索引与源表中每行记录硬件地址建立了一直映射关系，我们就可以通过索引快速定位表中记录。
第二：当我们不加索引时，从上到下如左图扫描源表，扫描第5次才找到age=33。
第三，加索引时，直接在树形结构找，33<50，在左侧，33>23，在右侧，第三次查找就找到了。
举例：age=62,不加索引第8次才找到。加索引第3次就找到了

索引的弊端

1、当数据量大时，索引很大，虽然比源表小，但还是要放在硬盘中，占据一定内存空间
2、索引不适用所有情况，a.少量数据。2.频繁增删。3.很少使用字段，不需要加索引
3、索引会提高数据查询效率，但会降低‘增、删、改’的效率。不添加索引，只需要修改源表，当添加索引后，不仅要修改源表，还要再次修改索引，会很麻烦。
**

什么情况下会使用索引呢？

**

1、主键自动建立唯一索引

2、频繁作为查询条件的字段应该创建索引

3、查询中于其他表关联的字段，外键关系建立索引

4、频繁更新的字段不适合建立索引，因为每次更新不仅实时更新了记录还会更新索引

5、where 条件里用不到的字段不创建索引

6、查询中排序的字段，排序的字段若通过索引去访问将会大大提高排序速度

7、查询中统计或者分组的字段

索引的优点

1、提高查询效率，降低IO使用率，减少查询次数
2、降低CPU使用率，比如【order by age desc】,不加索引会把源表加载到内存中做排序操作，极大消耗资源，加了索引，本身就小，第二本身就排好序，左边最小，右边最大

B图

数据全部放在叶子节点，对于上图，最下面的第三层，属于叶子节点，对于第1，2层数据是用来分割指针块的，比如小于26的找P1，26-30找p2,大于30找P3
通过增加节点数，最后，B数查询任意数据的次数，都是N次

优化的几种办法

1、创建索引

CREATE INDEX index_name ON table_name (column_list)

先针对列创建索引，在进行筛选，最后从一开始系统要找279万的数据，到后面只需要查10万的数据


尽可能避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2、索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
3、like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
4、不要在索引列上进行运算
例如这样得语句，SELECT id WHERE age + 1 = 10，

SELECT * FROM taobao.xwfx
where day(date_ymd)<28

将在每个行上进行运算，这将导致索引失效而进行全表扫描，

因此我们可以改成

SELECT * FROM taobao.xwfx
where date_ymd<'2017-11-28'

5、in 用exist，not in 用 not exist代替

6、尽可能避免有null ,也尽量不要在where 上使用is null 判断。
7、应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
8、应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。

**9.应尽量避免在where子句中对字段进行函数操作，**这将导致引擎放弃使用索引而进行全表扫描。如：

10.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
11、分表

数据量的日剧增加，数据库中某个表有几百万条数据，导致查询和插入耗时太长，怎么能解决单表压力呢？你就该考虑是否把这个表拆分成多个小表，来减轻单个表的压力，提高处理效率，此方式称为分表。

分表技术比较麻烦，要修改程序代码里的SQL语句，还要手动去创建其他表，也可以用merge存储引擎实现分表，相对简单许多。分表后，程序是对一个总表进行操作，这个总表不存放数据，只有一些分表的关系，以及更新数据的方式，总表会根据不同的查询，将压力分到不同的小表上，因此提高并发能力和磁盘I/O性能。
分表分为垂直拆分和水平拆分：

垂直拆分：把原来的一个很多字段的表拆分多个表，解决表的宽度问题。你可以把不常用的字段单独放到一个表中，也可以把大字段独立放一个表中，或者把关联密切的字段放一个表中。

水平拆分：把原来一个表拆分成多个表，每个表的结构都一样，解决单表数据量大的问题。
12、数据类型
①尽量使用可以正确存储数据的最小数据类型
更小的数据类型意味着更快，占用更少的磁盘，内存、缓存和处理时间
②尽量使用整型表示字符串
因为字符集和校对规则，使处理字符比整型更复杂，比如：我们使用数据库内置的datetime类型存储时间而不是字符类型，我们使用整型存储ip而不是直接将ip字符串存到数据库中
③尽可能使用not null
这个值是很烦人的，建字段时请尽量指定是否非空，NULL使得索引，统计，比较都变得更复杂，而且索引尽量不要创建到可以为null的字段上
④字符串类型
VARCHAR是可变长字符串
比定长字符串（CHAR）更节省空间，仅使用必要的空间另外VARCHAR需要额外字节记录字符串长度（不同情况需要字节数不同）
CHAR类型是定长字符串
开发中基本很少用(一些公司甚至基本上不考虑这种类型了)，注意：字符串长度定义不是字节数，是字符数
**13、select id from t where col like %a%;**模糊查询左侧有%会导致全表检索，可行得情况下用like‘a%’就好，如果需要全文检索可以使用全文搜索引擎比如es，slor
**14、考虑使用UNION ALL，减少使用UNION，**因为UNION ALL不去重，而少了排序操作，速度相对比UNION要快，如果没有去重的需求，优先使用UNION ALL。
**15、考虑使用limit N，少用limit M，N，**特别是大表或M比较大的时候。
16、OR改写成IN：
OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
17.建主键

其他

1、如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。
　　Apache 会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。
　　如果你把你的表锁上一段时间，比如30秒钟，那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程/线程，数据库链接，打开的文件数，可能不仅仅会让你的WEB服务崩溃，还可能会让你的整台服务器马上挂了。
　　所以，如果你有一个大的处理，你一定把其拆分，使用 LIMIT oracle(rownum),sqlserver(top)条件是一个好的方法。下面是一个mysql示例：

while(1){

//每次只做1000条

mysql_query(“delete from logs where log_date <= ’2020-11-01’ limit 1000”);

if(mysql_affected_rows() == 0){

//删除完成，退出！
　　 　　break；
　　}

//每次暂停一段时间，释放表让其他进程/线程访问。
usleep(50000)

}
2、存储引擎比较
InnoDB存储引擎
InnoDB越做越好从MySQL5.5版本之后，MySQL的默认内置存储引擎已经是InnoDB，主要特点有

容灾恢复性比较好
支持事务，默认事务隔离界别为可重复读
使用的锁粒度为行锁，可以支持更高的并发
支持外键
配合一些热备工具可以支持在线热备份
在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度
对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。根据主键进行排序，数据和索引放在一块，都位于B+数的叶子节点上
MyISAM存储引擎
在5.5版本之前，MyISAM是MySQL的默认存储引擎，该存储引擎并发性差，不支持事务，所以使用场景比较少，主要特点有

不支持事务
不支持外键，如果强行增加外键，不会提示错误，只是外键不其作用
对数据的查询缓存只会缓存索引，不会像InnoDB一样缓存数据，而且是利用操作系统本身的缓存
默认的锁粒度为表级锁，所以并发度很差，加锁快，锁冲突较少，所以不太容易发生死锁
支持全文索引（MySQL5.6之后，InnoDB存储引擎也对全文索引做了支持），但是MySQL的全文索引基本不会使用，对于全文索引，现在有其他成熟的解决方案，比如：ElasticSearch，Solr，Sphinx等
数据库所在主机如果宕机，MyISAM的数据文件容易损坏，而且难恢复。

保持渴求，不要沉寂