mysql优化

1 mysql优化

1-1 第一句：不查询数据库，就是最好的优化。

要点：
	1. 前端：优化需求，减少没有意义的查询操作，和太复杂的业务逻辑。
	2. 后台：在服务层，使用redis做缓存

1-2 第二句：合理的表结构，可以提高查询效率。

1. 拆表：
   1-1定长与变长分离
	如 id int, 占4个字节, char(4) 占4个字符长度,也是定长, time 
	char(10): 定长的，最多存放10个字符，存入的数据不到10个字符，其他的空间闲置。
	varchar(10)：不定长的，最多存放10个字符，如果如果存入的数据不到10个字符，实际占用的空间，以存入的数据为准。
	即每一单元值占的字节是固定的.
	而varchar, text,blob,这种变长字段,适合单放一张表, 用主键与核心表关联起来.   

   一个人的信息： int id, varchar(10)name, char(20)telphone
         4 +10+20  = 34个字节
         
        分离之后的结果，一个表变成2个表。
        
   1-2常用字段和不常用字段要分离
	核心且常用字段,宜建成定长,放在一张表.
	需要结合网站具体的业务来分析,分析字段的查询场景,查询频度低的字段,单拆出来.
	学生： id，姓名，年龄，学历，学号，家庭住址，个人介绍，电话号码，微信号码
	
2. 合理添加冗余字段
    潜台词：违背三范式原则。
    
    三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。
    
    订单表：id, 用户id, 时间，订单的总金额（冗余的）
       订单详情表：id , 订单id, 商品id, 商品数量
    
3. 除开主键约束外，原则不保留任何约束（唯一，外键）
	使用业务逻辑(service层)来控制，而不用数据库来控制。
	理解约束的本质： 数据库管理系统的额外负担。
	
	理解：心中有外键，表中无外键

1-3 第三句：最后才是优化sql查询语句

1 第一步：定位存在问题的sql语句，mysql启动慢查询，oracle查询相关的数据字典参数。
2 第二步：分析存在问题sql语句，mysql的explain语句， oracle的执行计划（explain plan）
3 第三步：分析是否正确使用了索引。
    3-1 查询频繁的列添加索引，即在where，group by，order by，on从句中出现的列
 	3-2 留意索引的正确使用，例如：对于字段，使用函数的操作，从而导致索引失效。
 	3-3 针对复杂的查询，考虑联合索引的使用。

1-4 索引失效原因

1、对索引列运算，运算包括（+、-、*、/、！、<>、%、like'%_'（%放在前面）
2、类型错误，如字段类型为varchar，where条件用number。
3、对索引应用内部函数，这种情况下应该建立基于函数的索引
如select * from template t where ROUND(t.logicdb_id) = 1
此时应该建ROUND(t.logicdb_id)为索引，mysql8.0开始支持函数索引，5.7可以通过虚拟列的方式来支持，之前只能新建一个ROUND(t.logicdb_id)列然后去维护
4、如果条件有or，即使其中有条件带索引也不会使用（这也是为什么建议少使用or的原因），如果想使用or，又想索引有效，只能将or条件中的每个列加上索引
5、如果列类型是字符串，那一定要在条件中数据使用引号，否则不使用索引；
6、B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走
7、组合索引遵循最左原则

1-5 SQL语句优化

1. 使用索引，尽量使用主键索引，使用联合索引时，出现最频繁的索引字段出现在最左边，其他依此类推
2. 尽量不要对等号左边的字段进行数学运算或字符串操作，它们不会使用索引，而应在对等号右边的字段进行相关运算或操作，如：不要使用convert(),cast(),substring()等
				2.1 select * from table 
						where convert(varchar(10),dateField,120)=‘2010-01-01’
				 或
					select * from table 
						where cast(dateField as varchar(10))=‘2010-01-01’
				 可改成
					select * from table 
						where dateFiled between ‘2010-01-01’ and ‘2010-01-02’。
				2.2 select * from table where substring(strField,0,2)=‘xx’
				 可改成
					select * from table where strField like ‘xx%’
3. 尽量不要使用!=、<>、not、or等，它们不会使用索引，，可用union、union all代替或使用between and代替区间取值
				如：
				select * from table where field=‘x1’ or field=‘x2’
				可改为
				(select * from table where field=‘x1’) 
				union 
				(select * from table where field=‘x2’)
				或者
				(select * from table where field=‘x1’)
				union all 
				(select * from table where field=‘x2’)
4. 尽量使用exists代替in，in不会使用索引
				select * from table1 t1 where t1.fid in (select nid from table2)
				改为
				select * from table1 t1 where exists (select t2.nid from table2 t2 where t2.nid = t1.fid)
				in的可选项为常量时，可用union代替
				select * from table where nstate in (10,13)
				改为
				(select * from table where nstate = 10)
				union
				(select * from table where nstate = 13)
5. 使用like时尽量使用左匹配，可以使用索引，即：字段like 'xx%'
				select * from table where field like 'x%'
				比 select * from table where field like '%x' 快
				比 select * from table where field like '%x%' 快
				比 select * from table where substring(field,1,2)='x' 快
6. 使用表连接，一般inner join比left 
			join要好，但要测试后再使用
				select t1.* from table t1 where t1.nt2id 
					in(select nid from table2 t2 where t2.nstate=‘x’)
				可改成
				select t1.* from table t1 left join table2 t2 on t1.nt2id=t2.nid where t2.nstate=‘x’
				或者改成exists
				select t1.* from table t1 where exists(select nid from table2 t2 where t2.nid=t1.nt2id and t2.nstate=‘x’)
7. 一般表连接比子查询要快，但要测试后再使用

1-6 事务优化

尽量使用数据库默认事务级别
如果要锁定数据库资源尽量使用乐观锁
	update table1 set nnum=nnum±@p1 where nnum=@p0
或
	update table1 set nnum=@p1 where nnum=@p0
	注：@p0为原数据，@p1为新数据，只有当老数据在更新时不变时数据才能更新成功，否则更新失败。

1-7 系统程序优化

1. 尽量不要单独查询表的某一个字段值或查询表所有字段，应按需要查询字段的值
2. 批量查询，尽量不要再循环中进行查询，可使用表连接查询、分组查询或批量查询，再使用数据结构来过滤、分割、重组数据
3. 批量更新，尽量不要在循环中单独更新数据，可用批量更新代替
4. 尽量使用延迟加载，特别是表字段含有大数据字段或冗余数据重大时
5. 尽量使用数据库连接池，避免频繁打开与关闭数据库连接
6. 设置数据库连接超时，不要让数据库连接处于无限等待状态，造成程序宕机

1-6 EXPLIAN执行计划

基本用法

1、desc 或者 explain 加上你的sql

2、extended explain加上你的sql，然后通过show warnings可以查看实际执行的语句，这一点也是非常有用的，很多时候不同的写法经过sql分析之后实际执行的代码是一样的

提高性能的特性

1、索引覆盖(covering index)：需要查询的数据在索引上都可以查到不需要回表 EXTRA列显示using index

2、ICP特性(Index Condition Pushdown)：本来index仅仅是data access的一种访问模式，存数引擎通过索引回表获取的数据会传递到MySQL server层进行where条件过滤,5.6版本开始当ICP打开时，如果部分where条件能使用索引的字段，MySQL server会把这部分下推到引擎层，可以利用index过滤的where条件在存储引擎层进行数据过滤。EXTRA显示using index condition。需要了解mysql的架构图分为server和存储引擎层

3、索引合并(index merge)：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。一般用OR会用到，如果是AND条件，考虑建立复合索引。EXPLAIN显示的索引类型会显示index_merge，EXTRA会显示具体的合并算法和用到的索引

extra字段

1、using filesort： 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” ，其实不一定是文件排序，内部使用的是快排

2、using temporary： 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by

3、using index： 表示相应的SELECT操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。

4、impossible where： WHERE子句的值总是false，不能用来获取任何元组

5、select tables optimized away： 在没有GROUP BY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作， 不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

6、distinct： 优化distinct操作，在找到第一匹配的元祖后即停止找同样值的操作

using filesort,using temporary这两项出现时需要注意下，这两项是十分耗费性能的，在使用group by的时候，虽然没有使用order by，如果没有索引，是可能同时出现using filesort,using temporary的，因为group by就是先排序在分组，如果没有排序的需要，可以加上一个order by NULL来避免排序，这样using filesort就会去除，能提升一点性能。

7、type字段
system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现
const：如果通过索引依次就找到了，const用于比较主键索引或者unique索引。 因为只能匹配一行数据，所以很快。如果将主键置于where列表中，MySQL就能将该查询转换为一个常量
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配 某个单独值的行，然而它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现between、<、>、in等的查询，这种范围扫描索引比全表扫描要好，因为只需要开始于缩印的某一点，而结束于另一点，不用扫描全部索引
index：Full Index Scan ，index与ALL的区别为index类型只遍历索引树，这通常比ALL快，因为索引文件通常比数据文件小。 （也就是说虽然ALL和index都是读全表， 但index是从索引中读取的，而ALL是从硬盘读取的）
all：Full Table Scan，遍历全表获得匹配的行

2 索引的类型

2-1 索引的类型

mysql索引的2种类型： B-tree(树)索引和Hash索引。

思考：结合昨天，学习的数据结构知识，猜猜哪个更好？
   B-tree索引，就是二叉树索引的进阶版
   Hash索引，就是hash表。
   
原因分析：
   hash索引要好，根据内容查找，其效率是O(1), 红黑树log 2 N
   但是，回答错误。
   
   但是，数据库的索引，都是选择的B-tree树。
   
   数据的存放场景不同：hash， 红黑树，前提：数据都在内存。现在索引的数据在硬盘。
   数据的使用场景不同：根据范围查找
   
        hash索引，不适合范围查找。
        
	a. 内存容量
	b. 范围查询

实际应用： 都是使用B-树索引。

2-2 B-tree（树）

2-2-1 理清楚3个概念：B树，B-树， B+树

b-tree

B树，B-树：是指同一个事物。本质没有"B减树"
B+树：是B树的变形。

在实际的数据库的索引:使用B+树。

2-2-2 B-树的定义

1. B-树中所有结点中孩子结点个数的最大值成为B-树的阶，通常用m表示，从查找效率考虑，一般要求m>=3。一棵m阶B-树或者是一棵空树，或者是满足以下条件的m叉树。
1）每个结点最多有m个分支（子树）；而最少分支数要看是否为根结点，如果是根结点且不是叶子结点，则至少要有两个分支，非根非叶结点至少有ceil(m/2)个分支，这里ceil代表向上取整。

B树：多叉树。
 多叉树，比二叉树的优点：
   存储同样多的数据，树的高度，降低。
   多茶树的根据内容查找的效率：log 叉数 N
   
   引入一个概念：叉叉越多越好。
   
     叉叉能够无限多吗？受到限制，受到磁盘块的限制。
       
       索引，也是一个文件，存放在硬盘，读取也遵守硬盘的原则，每次读取一个磁盘块。一块可以保存4096字节的数据。
       
       索引的结构是什么样子?
       
          例如：建议一个基于姓名的索引。
             姓名+数据（该条记录）在磁盘中的位置。
             
             所以，叉叉的数量 = 4096/ (姓名的长度+数据在磁盘中的位置的长度)
             
             告诉大家，建立索引字段的数据，长度不宜过长。

3 联合索引

3-1 定义

建立一个索引，不过其数据不是来源于一个字段，而是多个字段。

3-2 应用场景

例如：
     例:select * from shop  where cat_id=3 and price>100 ; //查询第3个栏目,100元以上的商品
     
     如果，我在cat_id ， price都建立索引，问上面的查询语句，会使用哪些索引？
     
     建立联合索引
     index(cat_id, price) 一起做索引
     
     索引字段的结构
     cat_id的值+price的值+该条记录在磁盘的位置
     
     

3-3 应用的注意事项

3-3-1 左前缀原则

​ 多列索引上,索引发挥作用,需要满足左前缀要求。

​ 顺序非常重要，第一个顺序，建立索引的顺序 第二个顺序，使用的顺序。

以 index(a,b,c) 为例

语句	索引是否发挥作用
Where a=3	是,只使用了a列
Where a=3 and b=5	是,使用了a,b列
Where a=3 and b=5 and c=4	是,使用了abc
Where b=3 where c=4	否
Where a=3 and c=4	a列能发挥索引,c不能
Where a=3 and b>10 and c=7	A能利用,b能利用, C不能利用
同上,where a=3 and b like ‘xxxx%’ and c=7	A能用,B能用,C不能用

3-3-2 感受下

扎金花： 每人三张牌，从大到小，排好。
a:百位数   b: 十位数   c: 个位数

abc: 组成一个三位数。



sql语句：
create table t4 (
c1 tinyint(1) not null default 0,
c2 tinyint(1) not null default 0,
c3 tinyint(1) not null default 0,
c4 tinyint(1) not null default 0,
c5 tinyint(1) not null default 0,
index c1234(c1,c2,c3,c4)
);
insert into t4 values (1,3,5,6,7),(2,3,9,8,3),(4,3,2,7,5);


explain select *  from t4;


explain 的作用：
 用于分析查询语句的执行流程。通过分析，获取到sql语句的查询效率，是否使用了索引。

4 聚簇索引

4-1 前置知识：mysql存储引擎

4-1-1 定义

 什么是存储引擎？
   mysql提供了不同的存储技术，不同的存储机制，索引机制，锁的层次。

MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力

4-1-2 常见存储引擎

MyISAM：最古老的，不支持事务，也不支持外键，尤其是访问速度快，对事务完整性没有要求或者以SELECT为主的应用，基本都可以使用这个引擎来创建表。

InnoDB：支持事务，支持外键，行级锁。使用最频繁。

MEMORY：（内存）
    使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间，采用的逻辑存储介质是系统内存。虽然在内存中存储表数据确实会提供很高的性能，但当mysqld守护进程崩溃时，所有的Memory数据都会丢失。
    它要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型，VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用。
	支持散列索引。
	

功 能	MYISAM	Memory	InnoDB
存储限制	256TB	RAM	64TB
支持事务	No	No	Yes
支持全文索引	Yes	No	No
支持树索引	Yes	Yes	Yes
支持哈希索引	No	Yes	No
支持外键	No	No	Yes

4-2 聚簇索引与非聚簇索引

4-2-1 定义

1. 定义:聚簇索引与非聚簇索引，虽然都叫做索引，但这并不是一种单独的索引类型（B+树，hash索引），而是一种数据存储方式。
2 特点:
	聚簇索引存储来说， 主键B+树在叶子节点存储行数据。 	辅助键B+树只存储辅助键和主键的值。
	非聚簇索引存储来说，主键B+树在叶子节点存储指向数据行的指针，而非真实的行数据。
	
	InnoDB,使用的是聚簇索引。

4-2-2 使用分析

1 特点分析
（演示）
对于innodb而言，默认，选择主键，建立聚簇索引，如果，没有主键，会根据自己的内部规范，选择表中的一个字段，当做主键，建立聚簇索引。

对于innodb而言，建立默认的聚簇索引，还可以建立其他的索引。其他的索引，成为：辅助索引。

5 补充

5-1 数据库建表原则

a. 在满足三范式的原则上，根据业务的需求，增加冗余字段。（订单表：价格，论坛：统计计算）
b. 拆表：
   多于字段特别长的表，可以根据定长和非定长，使用频繁和使用较少的原则，进行拆分。使用频繁的字段，尽量设定为定长，作为一个表，其他字段，作为另外一个表，两个表之前的关系是：1对1.
c. 在表中，只需要创建主键约束，不创建外键，唯一，非空等约束。在service层，通过代码实现相关的越苏需求。
d. 针对需要建立索引的字段。，合理使用类型，减低字段的长度