程序员应该知道的MySQL优化技巧
2018-06-13 16:28:08 
阿炯
增删改优化关于单表查询优化
关于多表查询优化
索引优化
存储过程和函数
视图的优化
存储引擎的内存优化
通过慢查询日志定位优化
主从复制
数据库设计规范
-------------------------------
增删改优化
一、NSERT语句：
基本：INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), …]
注意：如果要插入的值列表包含所有字段并且顺序一致，则可以省略字段列表。可同时插入多条数据记录！REPLACE 与 INSERT 完全一样，可互换。
(1)当我们需要批量插入数据的时候，单条的语句却会出现性能问题。例如说，如果有需要插入100000条数据，那么就需要有100000条insert语句，每一句都需要提交到关系引擎那里去解析，优化，然后才能够到达存储引擎做真的插入工作。而只要一个insert语句后跟多个values语句，即同时插入多条就是这样一种优化。
(2)将进程/线程数控制在2倍于CPU数目相对合适
(3)采用顺序主键策略(例如自增主键，或者修改业务逻辑，让插入的记录尽可能顺序主键)
(4)考虑使用replace语句代替insert语句。(REPLACE语句请参考下文，有详细讲述)
二、DELETE语句：
DELETE FROM 表名[ 删除条件子句](没有条件子句，则会删除全部)
补充：Mysql中的truncate table和delete语句都可以删除表里面所有数据，但是在一些情况下有些不同！
例子：
truncate table gag;
(1)truncate table删除速度更快，但truncate table删除后不记录mysql日志，不可以恢复数据。(谨慎使用)
(2)如果没有外键关联，innodb执行truncate是先drop table(原始表),再创建一个跟原始表一样空表,速度要远远快于delete逐条删除行记录。
(3)如果使用innodb_file_per_table参数，truncate table 能重新利用释放的硬盘空间,在InnoDB Plugin中，truncate table为自动回收，如果不是用InnoDB Plugin,那么需要使用optimize table来优化表，释放空间。truncate table删除表后，optimize table尤其重要，特别是大数据数据库，表空间可以得到释放！
(4)表有外键关联，truncate table删除表数据为逐行删除，如果外键指定级联删除(delete cascade)，关联的子表也会会被删除所有表数据。如果外键未指定级联(cascde)，truncate table逐行删除数据，如果是父行关联子表行数据，将会报错。
注意：一个大的 DELETE 或 INSERT 操作，要非常小心，因为这两个操作是会锁表的，表一锁住，其他操作就进不来了。因此我们要交给DBA去拆分，重整数据库策略，比如限制处理1000条。
另外，扩展下删除和索引的联系(关于索引优化，后面的查询优化也会讲解)，由于索引需要额外的维护成本；因为索引文件是单独存在的文件,所以当我们对数据的增加、修改、删除都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。所以我们想要删除百万数据的时候可以先删除索引，然后删除其中无用数据，删除完成后重新创建索引，创建索引也非常快。与之前的直接删除绝对是要快速很多，更别说万一删除中断，一切删除会回滚，那更是坑了。
三、UPDATE语句：
UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件]
优化：更新多条记录，更新多条记录的多个值。
(1). 尽量不要修改主键字段。
(2). 当修改VARCHAR型字段时，尽量使用相同长度内容的值代替。
(3). 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。
(4). 避免UPDATE将要复制到其他数据库的列。
(5). 避免UPDATE建有很多索引的列。
(6). 避免UPDATE在WHERE子句条件中的列。
四、REPLACE语句：
根据应用情况可以使用replace语句代替insert/update语句。例如：如果一个表在一个字段上建立了唯一索引，当向这个表中使用已经存在的键值插入一条记录，将会抛出一个主键冲突的错误。如果我们想用新记录的值来覆盖原来的记录值时，就可以使用REPLACE语句。
使用REPLACE插入记录时，如果记录不重复(或往表里插新记录)，REPLACE功能与INSERT一样，如果存在重复记录，REPLACE就使用新记录的值来替换原来的记录值。使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一，形成一个原子操作。这样就可以不必考虑同时使用DELETE和INSERT时添加事务等复杂操作了。
在使用REPLACE时，表中必须有唯一有一个PRIMARY KEY或UNIQUE索引，否则使用一个REPLACE语句没有意义。
用法：
(1)同INSERT
含义一：与普通INSERT一样功能
REPLACE INTO score (change_type,score,user_id) VALUES ('吃饭',10,1),('喝茶',10,1),('喝茶',10,1);
含义二：找到第一条记录，用后面的值进行替换
REPLACE INTO score (id,change_type,score,user_id) VALUES (1,'吃饭',10,1)
此语句的作用是向表table中插入3条记录。如果主键id为1或2不存在就相当于插入语句：
INSERTINTO score (change_type,score,user_id) VALUES (‘吃饭’,10,1),(‘喝茶’,10,1),(‘喝茶’,10,1);
如果存在相同的值则不会插入数据。
(2)replace(object, search, replace)，把object中出现search的全部替换为replace。
用法一：并不是修改数据，而只是单纯做局部替换数据返还而已。
SELECT REPLACE('喝茶','茶','喝')//结果： 喝喝123
用法二：修改表数据啦，对应下面就是，根据change_type字段找到做任务的数据，用bb来替换
UPDATE score SET change_type=REPLACE(change_type,'做任务','bb')
在此，做下对比：UPDATE和REPLACE的区别：
1)UPDATE在没有匹配记录时什么都不做，而REPLACE在有重复记录时更新，在没有重复记录时插入。
2)UPDATE可以选择性地更新记录的一部分字段。而REPLACE在发现有重复记录时就将这条记录彻底删除，再插入新的记录。也就是说，将所有的字段都更新了。
其实REPLACE更像INSERT与DELETE的结合。
-------------------------------
单表查询优化
单表查询优化：(关于索引，后面会讲解到)
(0)可以先使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的，这可以帮我们分析是查询语句或是表结构的性能瓶颈
(1)写sql要明确需要的字段，要多少就写多少字段，而不是滥用 select *
(2)可以用使用连接(JOIN)来代替子查询
(3)使用分页语句：limit start , count或者条件 where子句时，有什么可限制的条件尽量加上，查一条就limit一条。做到不滥用。比如说我之前做过的的p2p项目,只是需要知道有没有一个满标的借款，这样的话就可以用上 limit 1，这样mysql在找到一条数据后就停止搜索，而不是全文搜索完再停止
(4)开启查询缓存：大多数的MySQL服务器都开启了查询缓存，这是提高查询有效的方法之一。当有很多相同的查询被执行了多次的时候，这些查询结果会被放到一个缓存中，这样后续的相同的查询就不用操作表而直接访问缓存结果了
查询缓存工作流程：
1)：服务器接收SQL，以SQL+DB+Query_cache_query_flags作为hash查找键
2)：找到了相关的结果集就将其返回给客户端
3)：如果没有找到缓存则执行权限验证、SQL解析、SQL优化等一些列的操作
4)：执行完SQL之后，将结果集保存到缓存
当然，并不是每种情况都适合使用缓存，衡量打开缓存是否对系统有性能提升是一个整体的概念。那怎么判断要不要开启缓存呢，如下：
1)通过缓存命中率判断, 缓存命中率 = 缓存命中次数 (Qcache_hits) / 查询次数 (Com_select)
2)通过缓存写入率, 写入率 = 缓存写入次数 (Qcache_inserts) / 查询次数 (Qcache_inserts)
3)通过 命中-写入率 判断, 比率 = 命中次数 (Qcache_hits) / 写入次数 (Qcache_inserts), 高性能MySQL中称之为比较能反映性能提升的指数,一般来说达到3:1则算是查询缓存有效,而最好能够达到10:1
相关参数及命令：
与缓存相关的主要参数可以使用命令SHOW VARIABLES LIKE '%query_cache%'查看。
缓存数据失效时机：
在表的结构或数据发生改变时，查询缓存中的数据不再有效。有这些INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE会导致缓存数据失效。所以查询缓存适合有大量相同查询的应用，不适合有大量数据更新的应用。
可以使用下面三个SQL来清理查询缓存：
1)FLUSH QUERY CACHE; // 清理查询缓存内存碎片。
2)RESET QUERY CACHE; // 从查询缓存中移出所有查询。
3)FLUSH TABLES; //关闭所有打开的表，同时该操作将会清空查询缓存中的内容。
InnoDB与查询缓存：
Innodb会对每个表设置一个事务计数器，里面存储当前最大的事务ID。当一个事务提交时，InnoDB会使用MVCC中系统事务ID最大的事务ID跟新当前表的计数器，只有比这个最大ID大的事务能使用查询缓存，其他比这个ID小的事务则不能使用查询缓存。另外在InnoDB中，所有有加锁操作的事务都不使用任何查询缓存。
-------------------------------
多表查询优化
一、多表查询连接的选择：
就那4种jion方式。
相信这内连接，左连接什么的大家都比较熟悉了，当然还有左外连接什么的，基本用不上我就不贴出来了。这图只是让大家回忆一下，各种连接查询。需要根据查询的情况，想好使用哪种连接方式效率更高。
二、MySQL的JOIN实现原理
在MySQL中，只有一种Join 算法，就是大名鼎鼎的Nested Loop Join，他没有其他很多数据库所提供的Hash Join，也没有Sort Merge Join。顾名思义，Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与Join，则再通过前两个表的Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。 ——摘自《MySQL 性能调优与架构设计》
三、mysql对sql语句的容错问题
即在sql语句不完全符合书写建议的情况，mysql会允许这种情况，尽可能解释它：
1)一般cross join后面加上where条件，但是用cross join+on也是被解释为cross join+where
2)一般内连接都需要加上on限定条件，如上面场景一；如果不加会被解释为交叉连接
3)如果连接表格使用的是逗号，会被解释为交叉连接
注：sql标准中还有union join和natural inner join，mysql不支持，而且本身也没有多大意义，其实就是为了“健壮”，但是其实结果可以用上面的几种连接方式得到。
四、超大型数据尽可能尽力不要写子查询，使用连接(JOIN)去替换它：
当然，关于这句话，也不一定就全是这样。
1)因为在大型的数据处理中，子查询是非常常见的，特别是在查询出来的数据需要进一步处理的情况，无论是可读性还是效率上，这时候的子查都是更优。
2)然而在一些特定的场景，可以直接从数据库读取就可以的，比如一个表(A表 a，b，c字段，需要内部数据交集)join自己的效率必然比放一个子查在where中快得多。
五、使用联合(UNION)来代替手动创建的临时表
UNION是会把结果排序的！
union查询：它可以把需要使用临时表的两条或更多的select查询合并的一个查询中(即把两次或多次查询结果合并起来。)。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。
要求：两次查询的列数必须一致(列的类型可以不一样，但推荐查询的每一列，相对应的类型要一样)
可以来自多张表的数据：多次sql语句取出的列名可以不一致，此时以第一个sql语句的列名为准。
如果不同的语句中取出的行，有完全相同(这里表示的是每个列的值都相同)，那么union会将相同的行合并，最终只保留一行。也可以这样理解，union会去掉重复的行。
如果不想去掉重复的行，可以使用union all。
如果子句中有order by,limit，需用括号()包起来。推荐放到所有子句之后，即对最终合并的结果来排序或筛选。
注意：
1、UNION 结果集中的列名总是等于第一个 SELECT 语句中的列名
2、UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同
UNION ALL的作用和语法：
默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。当 ALL 随 UNION 一起使用时(即 UNION ALL)，不消除重复行。
六、总结
(1)对于要求全面的结果时，我们需要使用连接操作(LEFT JOIN / RIGHT JOIN / FULL JOIN)
(2)应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
备注、描述、评论之类的可以设置为 NULL，其他最好不要使用NULL。不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值(NULL也包含在内)，都是占用 100个字符的空间的，如果是varchar这样的变长字段，null 不占用空间。可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num = 0
(3)in 和 not in 也要慎用，否则会导致全表扫描。对于连续的数值，能用 between 就不要用 in 了，很多时候用 exists 代替 in 是一个好的选择。
(4)尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
(5)尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限(只有主键索引)。
(6)不要迷信使用MySQL的一些连接操作对查询有多么大的改善，其实核心是索引。
-------------------------------
索引优化手册
多关于索引，分为以下几点来讲解：
一、索引的概述(什么是索引,索引的优缺点)
二、索引的基本使用(创建索引)
三、索引的基本原理
四、索引的数据结构(B树，hash)
五、创建索引的原则
六、百万级别或以上的数据如何删除
一、索引的概述
1)什么是索引
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，索引就相当于目录。当你在用新华字典时，帮你把目录撕掉了，你查询某个字开头的成语只能从第一页翻到第一千页。
2)索引的优缺点：
可以大大加快数据的检索速度，这也是创建索引的最主要的原因，且通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。但索引也是有缺点的：索引需要额外的维护成本，因为索引文件是单独存在的文件，对数据的增加、修改、删除都会产生额外的对索引文件的操作，这些操作需要消耗额外的IO，会降低增、改、删的执行效率。
二、索引的基本使用
1)创建索引：(三种方式)
第一种方式：在创建表时指定即可
create table ...
第二种方式：使用ALTER TABLE命令去增加索引
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。
索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。
第三种方式：使用CREATE INDEX命令创建
CREATE INDEX可对表增加普通索引或UNIQUE索引。(但不能创建PRIMARY KEY索引)
三、索引的基本原理
索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。
索引的原理很简单，就是把无序的数据变成有序的查询：
1)把创建了索引的列的内容进行排序
2)对排序结果生成倒排表
3)在倒排表内容上拼上数据地址链
4)在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据
四、索引的数据结构(b树，hash)
1)B树索引
mysql通过存储引擎取数据，基本上90%的人用的就是InnoDB了，按照实现方式分，InnoDB的索引类型目前只有两种：BTREE(B树)索引和HASH索引。B树索引是Mysql数据库中使用最频繁的索引类型，基本所有存储引擎都支持BTree索引。通常我们说的索引不出意外指的就是(B树)索引(实际是用B+树实现的，因为在查看表索引时，mysql都给出BTREE，所以简称为B树索引)。
查询方式：
主键索引区：PI(关联保存的时数据的地址)按主键查询
普通索引区：si(关联的id的地址,然后再到达上面的地址)。所以按主键查询速度最快
B+tree性质：
1)n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。
2)所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
3)所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大(或最小)关键字。
4)B+树中，数据对象的插入和删除仅在叶节点上进行。
5)B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。
2)哈希索引
简要说下，类似于数据结构中简单实现的HASH表(散列表)一样，当我们在mysql中用哈希索引时，主要就是通过Hash算法(常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法)，将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞(两个不同关键字的Hash值相同)，则在对应Hash键下以链表形式存储。
注意：这里需要太多地涉及数据结构，此处不做详细讲解。
五、创建索引的原则
索引虽好，但也不是无限制的使用，最好符合一下几个原则：
1)最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2)较频繁作为查询条件的字段才去创建索引
3)更新频繁字段不适合创建索引
4)若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，离散度实在太低)
5)尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
6)定义有外键的数据列一定要建立索引。
7)对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
8)对于定义为text、image和bit的数据类型的列不要建立索引。
百万级别或以上的数据如何删除
关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件，所以当我们对数据的增加、修改、删除都会产生额外的对索引文件的操作，这些操作需要消耗额外的IO，会降低增、改、删的执行效率。所以在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。
所以我们想要删除百万数据的时候可以先删除索引
然后删除其中无用数据
删除完成后重新创建索引
与之前的直接删除绝对是要快速很多，更别说万一删除中断，一切删除会回滚。那更是坑了。
-------------------------------
表的设计及优化
优化1：创建规范化表，消除数据冗余
数据库范式是确保数据库结构合理，满足各种查询需要、避免数据库操作异常的数据库设计方式。满足范式要求的表，称为规范化表，范式产生于20世纪70年代初，一般表设计满足前三范式就可以，在这里简单介绍一下前三范式。
通俗的给大家解释一下(可能不是最科学、最准确的理解)
第一范式：属性(字段)的原子性约束，要求属性具有原子性，不可再分割
第二范式：记录的惟一性约束，要求记录有惟一标识，每条记录需要有一个属性来做为实体的唯一标识
第三范式：属性(字段)冗余性的约束，即任何字段不能由其他字段派生出来，在通俗点就是：主键没有直接关系的数据列必须消除(消除的办法就是再创建一个表来存放他们，当然外键除外)
当然，其实我们经常打破第三范式且不可避免的，其实就是要在数据冗余和处理速度之间找到合适的平衡点。
优化2：合适的字段属性
先举个例子：关于资金流水类型的字段的选取。本来资金流水类型总共就那么十几种，基本固定死的，那我们就可以选择tinyint(4)就完全足够了(要知道的是，tinyint的长度就是8位，tinyint(1)和tinyint(4)只是显示长度)。
下面以下给出几个字段的建议：
0)数值型字段的比较比字符串的比较效率高得多，所以字段类型尽量使用最小、最简单的数据类型。如IP地址可以使用int类型。
1)建议不要使用DOUBLE，不仅仅只是存储长度的问题，同时还会存在精确性的问题。
2)对于整数的存储，在数据量较大的情况下，建议区分开 TINYINT/INT/BIGINT 的选择。
3)char是固定长度，所以它的处理速度比varchar快得多，但缺点是浪费存储空间，不能在行尾保存空格。在MySQL中，MyISAM建议使用固定长度代替可变长度列；InnoDB建议使用varchar类型，因为在InnoDB中，内部行存储格式没有区分固定长度和可变长度。
4)尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。
5)text与blob区别：blob保存二进制数据；text保存字符数据，有字符集。text和blob不能有默认值。实际场景：text与blob主要区别是text用来保存字符数据(如文章，日记等)，blob用来保存二进制数据(如照片等)。blob与text在执行了大量删除操作时候，有性能问题(产生大量的“空洞“)，为提高性能建议定期optimize table 对这类表进行碎片整理。
6)自增字段要慎用，不利于数据迁移。
7)强烈反对在数据库中存放 BLOB 类型数据，虽然数据库提供了这样的功能，但这不是他所擅长的，我们更应该让合适的工具做他擅长的事情，才能将其发挥到极致。
8)尽量将表字段定义为NOT NULL约束，这时由于在MySQL中含有空值的列很难进行查询优化，NULL值会使索引以及索引的统计信息变得很复杂，可以使用0或者空字符串来代替。
9)尽量使用TIMESTAMP类型，因为其存储空间只需要DATETIME类型的一半，且日期类型中只有它能够和实际时区相对应。对于只需要精确到某一天的数据类型，建议使用DATE类型，因为他的存储空间只需要3个字节，比TIMESTAMP还少。
优化3：索引
索引是一个表优化的重要指标，在表优化中占有极其重要的成分，所以上篇索引优化详解没看过的可以先看看，这里不再赘叙。
优化4：表的拆分(大表拆小表)
1、垂直拆分(其实就是列的拆分将原来的一个有很多列的表拆分成多张表)
注意：垂直拆分应该在数据表设计之初就执行的步骤，然后查询的时候用jion关键起来即可。
通常我们按以下原则进行垂直拆分:
1)把不常用的字段单独放在一张表;
2)把text，blob等大字段拆分出来放在附表中;
3)经常组合查询的列放在一张表中;
缺点也很明显，需要使用冗余字段，而且需要join操作。
2、水平拆分(如果你发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是，以该表主键的某个值为界线，将该表的记录水平分割为两个表)。当然，我们还可以用增量法。如流水这类不会改变的数据，我们用增量查询。
1)创建一张日充值表,记录每天充值总额
2)每天用定时器对当前充值记录进行结算
3)创建每月充值表,每月最后一天用定时器计算总额
4)则要查询总额,则从月报表中汇总，再从日报表查询当天之前的数据汇总，再加上今天的使用当天流水表记录今天的流水，三张表加起来,汇总。这样子效率是极好的！
优化5：传说中的'三少原则'
1)数据库的表越少越好
2)表的字段越少越好
3)字段中的组合主键、组合索引越少越好
当然这里的少是相对的，是减少数据冗余的重要设计理念。
-------------------------------
储过程和存储函数
一、MYSQL储存过程简介
储存过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。存储过程通常有以下优点：
1)存储过程能实现较快的执行速度
如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
心得：编译优化，快！
2)存储过程允许标准组件是编程
存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
心得：封装与抽象，简单调用
3)存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算
心得：功能强大，逻辑强大
4)存储过程可被作为一种安全机制来充分利用
系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。
心得：限制与安全
5)存储过程能过减少网络流量
针对同一个数据库对象的操作(如查询、修改)，如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。
心得：减少网络流量
二、存储函数(自定义函数)
封装一段sql代码，完成一种特定的功能，必须返回结果。其余特性基本跟存储过程相同。
三、存储函数与存储过程的区别
1)存储函数有且只有一个返回值，而存储过程不能有返回值，就是说能不能使用return(函数可返回返回值或者表对象，绝对不能返回结果集)。
2)函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。
3)存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。比如：工期计算、价格计算。
4)存储过程可以调用存储函数。但函数不能调用存储过程。
5)存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。
四、MySQL创建一个最简单的存储过程
...
五、MySQL 存储过程特点
创建 MySQL 存储过程的简单语法为：
create procedure 存储过程名字()([in|out|inout] 参数 datatype)
begin
MySQL 语句;
end;
MySQL 存储过程参数如果不显式指定“in”、“out”、“inout”，则默认为“in”。习惯上，对于是“in” 的参数，我们都不会显式指定。
1)MySQL存储过程名字后面的“()”是必须的，即使没有一个参数，也需要“()”
2)MySQL存储过程参数，不能在参数名称前加“@”，如：“@a int”。下面的创建存储过程语法在 MySQL 中是错误的(在 SQL Server 中是正确的)。 MySQL 存储过程中的变量，不需要在变量名字前加“@”，虽然 MySQL 客户端用户变量要加个“@”。
create procedure pr_add( @a int,// 错误 b int //正确)
3)MySQL存储过程的参数不能指定默认值。
4)MySQL存储过程不需要在 procedure body 前面加 “as”。而 SQL Server 存储过程必须加 “as” 关键字。
create procedure pr_add( a int, b int)as - 错误，MySQL 不需要 “as” begin mysql statement ...;end;
5)如果MySQL存储过程中包含多条 MySQL 语句，则需要 begin end 关键字。
create procedure pr_add( a int, b int)begin mysql statement 1 ...; mysql statement 2 ...;end;
6)MySQL存储过程中的每条语句的末尾，都要加上分号 “;”
... declare c int; if a is null then set a = 0; end if; ...end;
7)不能在MySQL存储过程中使用 “return” 关键字。
set c = a + b;select c as sum; /* return c;- 不能在 MySQL 存储过程中使用。return 只能出现在函数中。*/end;
8)调用MySQL存储过程时候，需要在过程名字后面加“()”，即使没有一个参数，也需要“()”，调用out及inout参数格式为@arguments_name形式。
call pr_no_param();
9)因为MySQL存储过程参数没有默认值，所以在调用 MySQL 存储过程时候，不能省略参数。可以用 null 来替代。
call pr_add(10, null);
1，实战前提：需要MySQL 5及以上 ，需要用到是DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以”;”为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将”;”当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
2，变量
使用DECLARE来声明，DEFAULT赋默认值，SET赋值
DECLARE counter INT DEFAULT 0; SET counter = counter+1;
3，条件判断
IF THEN、ELSEIF、ELSE、END IF
DROP PROCEDURE IF EXISTS discounted_price;
CREATE PROCEDURE discounted_price(normal_price NUMERIC(8, 2), OUT discount_price NUMERIC(8, 2)) BEGIN IF (normal_price > 500) THEN SET discount_price = normal_price * 0.8; ELSEIF (normal_price > 100 and normal_price<=500) THEN SET discount_price = normal_price * 0.9; ELSE SET discount_price = normal_price; END IF;
select discount_price as price; END;call discounted_price(600.0,@discount);//out参数调用时可以用@任意字符串
4，循环
LOOP、END LOOP
drop procedure if exists simple_loop;
create procedure simple_loop(out counter int)BEGIN declare temp int default 0; set counter=0; my_loop:LOOP set counter=counter+1; set temp=temp+1; if counter=10 THEN leave my_loop; end if; end loop my_loop;
select temp as result;end;call simple_loop(@a);
5、WHILE DO、END WHILE
DROP PROCEDURE IF EXISTS simple_while;
CREATE PROCEDURE simple_while(OUT counter INT) BEGIN declare temp int default 0; SET counter =0; WHILE counter != 10 DO SET counter =counter+1; set temp =temp+1; END WHILE; select counter as temp1; END;
call simple_while(@a);
6、REPEAT、UNTILL
drop PROCEDURE if exists simple_repeat;
create procedure simple_repeat(out counter int)BEGIN set counter=0; REPEAT set counter=counter+1; until counter=10 end repeat; select counter as temp;end;
call simple_repeat(@q);
7，存储方法
存储方法与存储过程的区别
1)存储方法的参数列表只允许IN类型的参数，而且没必要也不允许指定IN关键字
2)存储方法返回一个单一的值，值的类型在存储方法的头部定义
3)存储方法可以在SQL语句内部调用
4)存储方法不能返回结果集
语法：
create function 函数([函数参数[,….]]) Returns 返回类型
Begin If Return (返回的数据)
Else Return (返回的数据)end if; end;
8，触发器
触发器在INSERT、UPDATE或DELETE等DML语句修改数据库表时触发，触发器的典型应用场景是重要的业务逻辑、提高性能、监控表的修改等，触发器可以在DML语句执行前或后触发。
DROP TRIGGER sales_trigger;
CREATE TRIGGER sales_trigger BEFORE INSERT ON salesFOR EACH ROWBEGINIF NEW.sale_value > 500 THEN SET NEW.free_shipping = 'Y';ELSE SET NEW.free_shipping = 'N';END IF;IF NEW.sale_value > 1000 THEN SET NEW.discount = NEW.sale_value * .15;ELSE SET NEW.discount = 0;END IF;END;
-------------------------------
视图的优化
一、视图概述
(1)什么是视图
视图是基于 SQL 语句的结果集的可视化的表。
视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。视图并不在数据库中以存储的数据值集形式存在，而是存在于实际引用的数据库表中，视图的构成可以是单表查询，多表联合查询，分组查询以及计算(表达式)查询等。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
(2)视图的优点：
a)简化查询语句(视图机制使用户可以将注意力集中在所关心地数据上。如果这些数据不是直接来自基本表，则可以通过定义视图，使数据库看起来结构简单、清晰，并且可以简化用户的的数据查询操作。)
b)可以进行权限控制，把表的权限封闭，但是开放相应的视图权限，视图里只开放部分数据列等。
c)大数据表分表的时候，比如某张表的数据有100万条，那么可以将这张表分成四个视图。
d)用户能以多种角度看待同一数据：使不同的用户以不同的方式看待同一数据，当许多不同种类的用户共享同一个数据库时，这种灵活性是非常必要的。
e)对重构数据库提供了一定程度的逻辑独立性：视图可以使应用程序和数据库表在一定程度上独立。
(3)视图的缺点：
a)性能差：
把视图查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，sql server也要把它变成一个复杂的结合体，需要花费一定的时间。
b)修改限制：
当用户试图修改试图的某些信息时，数据库必须把它转化为对基本表的某些信息的修改，对于简单的试图来说，这是很方便的，但是，对于比较复杂的试图，可能是不可修改的。
(4)视图使用场景(其实就是需要用到视图上面的几个优点的时候)：
a)需要权限控制的时候。
b)如果某个查询结果出现的非常频繁，就是要经常拿这个查询结果来做子查询，使用视图会更加方便。
c)关键信息来源于多个复杂关联表，可以创建视图提取我们需要的信息，简化操作.
(5)视图的分类
1)关系视图：它属于数据库对象的一种，也就是最常见的一种关联查询。
2)内嵌视图：它不属于任何用户，也不是对象，创建方式与普通视图完全不同，不具有可复用性，不能通过数据字典获取数据。
3)对象视图：它是基于表对象类型的视图，特性是继承、封装等可根据需要构建对象类型封装复杂查询(官方:为了迎合对象类型而重建数据表是不实现的)。
4)物化视图：它主要用于数据库的容灾(备份)，实体化的视图可存储和查询，通过DBLink连接在主数据库物化视图中复制，当主库异常备库接管实现容灾。
二、视图的使用
1)、创建视图
create or replace view v_test asselect * from user;
加上OR REPLACE表示该语句还能替换已有的视图
2)、调取视图
select * from v_test;
3)、修改视图
alter view v_test as select * from user1;
4)、删除视图
drop view if exists v_test;
5)、查看视图
show tables;
视图放在information_schema数据库下的views表里
6)、查看视图的定义
show table status from companys like 'v_test';
在这之前，我们必须明确：增删改最终都是修改到基础表。且视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，就可能会造成数据更新失败。
三、视图的算法
存在两种执行的算法：
a)Merge：合并的执行方式，每当执行的时候，先将我们的视图的sql语句与外部查询视图的sql语句，混合在一起，最终执行。
b)Temptable：临时表模式，每当查询的时候，将视图所使用的select语句生成一个结果的临时表，再在当当前临时表内进行查询。
四、视图使用注意点
(1)修改操作时要非常非常小心，不然不经意间你已经修改了基本表里的多条数据
(2)视图中的查询语句性能要调到最优
(3)虽说上面讲到，视图有些是可以修改的。但是更多的是禁止修改视图
对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系或者特殊的没有约束的一对多字段。还有一些特定的其他结构，这类结构会使得视图不可更新。不可更改的情况如下：视图中含有以下的都不可被修改了。
五、视图使用特性
(一)聚合函数(SUM(), MIN(), MAX(), COUNT()等)。
(二)DISTINCT
(三)GROUP BY
(四)HAVING
(五)UNION或UNION ALL
(六)位于选择列表中的子查询
(八)FROM子句中的不可更新视图
(九)WHERE子句中的子查询，引用FROM子句中的表。
(十)ALGORITHM = TEMPTABLE(使用临时表总会使视图成为不可更新的)。
-------------------------------
存储引擎的内存优化
一、InnoDB内存优化
InnoDB用一块内存区域做I/O缓存池，该缓存池不仅用来缓存InnoDB的索引块，而且也用来缓存InnoDB的数据块。
1、innodb_log_buffer_size
决定了InnoDB重做日志缓存的大小，可以避免InnoDB在事务提交前就执行不必要的日志写入磁盘操作。
2、设置Innodb_buffer_pool_size
改变量决定了InnoDB存储引擎表数据和索引数据的最大缓存区大小。
二、MyISAM内存优化
MyISAM存储引擎使用key_buffer缓存索引模块，加速索引的读写速度。对于MyISAM表的数据块，mysql没有特别的缓存机制，完全依赖于操作系统的IO缓存。
1、read_rnd_buffer_size
对于需要做排序的MyISAM表查询，如带有order by子句的sql，适当增加read_rnd_buffer_size的值，可以改善此类的sql性能。但需要注意的是read_rnd_buffer_size独占的，如果默认设置值太大，就会造成内存浪费。
2、key_buffer_size
key_buffer_size决定MyISAM索引块缓存分区的大小。直接影响到MyISAM表的存取效率。对于一般MyISAM数据库，建议1/4可用内存分配给key_buffer_size:
key_buffer_size=2G
3、read_buffer_size
如果需要经常顺序扫描MyISAM表，可以通过增大read_buffer_size的值来改善性能。但需要注意的是read_buffer_size是每个seesion独占的，如果默认值设置太大，就会造成内存浪费。
三、调整MySQL参数并发相关的参数
1、调整max_connections
提高并发连接
2、调整thread_cache_size
加快连接数据库的速度，MySQL会缓存一定数量的客户服务线程以备重用，通过参数thread_cache_size可控制mysql缓存客户端线程的数量。
3、innodb_lock_wait_timeout
控制InnoDB事务等待行锁的时间，对于快速处理的SQL语句，可以将行锁等待超时时间调大，以避免发生大的回滚操作。
-------------------------------
慢查询日志优化
日志就跟人们写的日记一样，记录着过往的事情。但是人的日记是主观的(记自己想记的内容)，而数据库的日志是客观的，根据记录内容分为以下好几种日志：
a、错误日志：记录启动、运行或停止mysqld时出现的问题。
b、通用日志：记录建立的客户端连接和执行的语句。
c、更新日志：记录更改数据的语句。该日志在MySQL 5.1中已不再使用。
d、二进制日志：记录所有更改数据的语句。还用于复制。
e、慢查询日志：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。
f、Innodb日志：innodb redo log
缺省情况下，所有日志创建于mysqld数据目录中。可以通过刷新日志，来强制mysqld来关闭和重新打开日志文件(或者在某些情况下切换到一个新的日志)。
当你执行一个FLUSH LOGS语句或执行mysqladmin flush-logs或mysqladmin refresh时，则日志被老化。对于存在MySQL复制的情形下，从复制服务器将维护更多日志文件，被称为接替日志。
这次我们介绍的就是慢查询日志。何谓慢查询日志？MySQL会记录下查询超过指定时间的语句，我们将超过指定时间的SQL语句查询称为慢查询，都记在慢查询日志里，我们开启后可以查看究竟是哪些语句在慢查询。
开启慢查询日志
mysql>show variables like '%slow%'; 查看慢查询配置，没有则在my.cnf中添加。
分析日志 – mysqldumpslow
分析日志，可用mysql提供的mysqldumpslow，使用很简单，参数可用--help指令查看
推荐用分析日志工具 – mysqlsla
【说明】
queries total: 总查询次数 unique:去重后的sql数量
sorted by : 输出报表的内容排序
最重大的慢sql统计信息, 包括 平均执行时间, 等待锁时间, 结果行的总数, 扫描的行总数
Count, sql的执行次数及占总的slow log数量的百分比
Time, 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比。95% of Time, 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间
Lock Time, 等待锁的时间。95% of Lock,95%的慢sql等待锁时间
Rows sent, 结果行统计数量, 包括平均, 最小, 最大数量
Rows examined, 扫描的行数量
Database, 属于哪个数据库
Users, 哪个用户,IP, 占到所有用户执行的sql百分比
Query abstract, 抽象后的sql语句
Query sample, sql语句
-------------------------------
主从复制
一、什么是主从复制
二、主从复制的作用
三、主从复制的原理
四、三步轻松构建主从
五、必问面试题干货分析
一、什么是主从复制
主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。
二、主从复制的作用
1)做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
2)架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
3)读写分离，使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。
三、主从复制的原理
1)数据库有个bin-log二进制文件，记录了所有sql语句。
2)我们的目标就是把主数据库的bin-log文件的sql语句复制过来。
3)让其在从数据的relay-log重做日志文件中再执行一次这些sql语句即可。
4)下面的主从配置就是围绕这个原理配置
5)具体需要三个线程来操作：
binlog输出线程。每当有从库连接到主库的时候，主库都会创建一个线程然后发送binlog内容到从库。
在从库里，当复制开始的时候，从库就会创建两个线程进行处理：
a)从库I/O线程。当START SLAVE语句在从库开始执行之后，从库创建一个I/O线程，该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件，其中包括relay log文件。
b)从库的SQL线程。从库创建一个SQL线程，这个线程读取从库I/O线程写到relay log的更新事件并执行。
可以知道，对于每一个主从复制的连接，都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个binlog输出线程，每一个从库都有它自己的I/O线程和SQL线程。
主从复制原理如下图：
步骤一：主库db的更新事件(update、insert、delete)被写到binlog
步骤二：从库发起连接，连接到主库
步骤三：此时主库创建一个binlog dump thread，把binlog的内容发送到从库
步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
步骤五：还会创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db(技术文)
四、三步轻松构建主从
一)、Master主服务器上的配置(192.168.20.116)
1)编辑my.cnf
server-id = 1 中 1 是可以自己定义的，但是需要保持它的唯一性，是服务器的唯一标识。
1.log_bin 启动MySQL二进制日志
2.binlog_do_db 指定记录二进制日志的数据库
3.binlog_ignore_db 指定不记录二进制日志的数据库。
注释掉 binlog_do_db 和 binlog_ignore_db ，则表示记录全部数据库的写日志。做完这些后，重启下数据库。
2)登录主服务器mysql创建从服务器用到的账户和权限
3)查看主数据库的状态
记录 mysql-bin.000007 以及 276，编写以下命令待用:
change master to master_host='192.168.20.116',master_port=3316,master_user='repl',master_password='yourpaswd',master_log_file='mysql-bin.000009',master_log_pos=276;
二)、Slave从服务器配置上的配置
1)编辑my.cnf
在[mysqld]中
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index
加入这两条是用于固定相关文件的名称。在其命令行中执行：
stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.20.116',MASTER_PORT=3316,MASTER_USER='repl',MASTER_PASSWORD='yourpaswd',MASTER_LOG_FILE='mysql-bin.000009',MASTER_LOG_POS=276;
start slave;
执行MariaDB [(none)]> show slave status\G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
这两个为'Yes'时，表明主从成功，否则要通过其报错日志找原因了。
其实主从复制也存在一些问题：
1)负载均衡，由于复制的时间差，不能保证同步读，而且写仍然单点，没法多点写，我对这个理解就是半吊子的读写均衡。
2)容灾，基本都是有损容灾，因为数据不同步，在5.7版本中实现了半同步，数据的完整性有进一步有提升。
可能只是提供一种成本较低的数据备份方案加不完美的容灾和负载均衡吧，这种方案注定是一种过渡方案，当然在不是体量巨大的情况下，还是不失为一个优化的解决办法。
待解决的问题：
1)从数据库的读的延迟问题了解吗，如何解决？
2)做主从后主服务器挂了怎么办？
数据库设计规范
1.引子
对于后端开发工程师来说，数据库设计，优化是一项必备的技能，经常在项目中吐槽其他小伙伴编写的sql语句，既如此，千万不要让其他小伙伴有机会吐槽回来。甚至应该做到在编写sql语句的时候，脑海中已经浮现了该sql语句的执行轨迹，这样一来，相信写出的sql语句质量会非常高。因为工作上的需要，抽空整理了一版数据库设计开发参考规范，把它叫做必知必会之数据库规约，并分享给出来，期望带来一些收获！
内容分为：
建表规范
sql规范
索引规范
2.建表规范
2.1.范式化
#1.关系数据库表设计基础理论：第一范式、第二范式、第三范式
##1.1.第一范式
强调列的原子性，字段不可再分割
##1.2.第二范式
强调行的唯一性，不可存在相同的行表中必须有主键字段
##1.3.第三范式
强调主外键关联，消除冗余性需要注意，在互联网项目中，一般不建立主外键约束，在代码层面实现业务关联
因此今天我们有时候在强调反范式化设计，就是针对的第三范式
2.2.存储引擎
#1.mysql数据库，存储引擎建议选择InnoDB
##原因：
InnoDB存储引擎支持事务、支持行级锁并发性能更好、支持Crash safe能力redo log能力
2.3.数据类型
#1.选择合适的数据类型
##1.1.整数
TinyInt,SmallInt,MediumInt,Int,BigInt 使用的存储8,16,24,32,64位存储空间。使用Unsigned表示不允许负数，可以使正数的上线提高一倍
##1.2.实数
Float,Double , 支持近似的浮点运算
Decimal，用于存储精确的小数通常用于货币存储
##1.3.字符串
VarChar，存储变长的字符串。需要1或2个额外的字节记录字符串的长度
Char，定长，适合存储固定长度的字符串，如MD5值。
Blob，Text 为了存储很大的数据而设计的。分别采用二进制和字符的方式
##1.4.时间
DateTime，保存大范围的值，占8个字节，存储范围1001-9999。
TimeStamp，推荐，与UNIX时间戳相同，占4个字节，存储范围1970-2038
如何选择？
尽量使用对应的数据类型。比如不要用字符串类型保存时间
选择更小的数据类型。能用TinyInt，就不用Int
标识列identifier column，建议使用整型，不推荐字符串类型，占用更多空间，而且计算速度比整型慢
2.4.字符集
#1.统一字符集客户端、服务端，建议使用utf-8字符集，mysql数据库需要注意真正的utf-8字符集应该选择：utf8mb4
2.5.命名
#1.见名知意，禁止拼音英文混用
#2.约定库名、表名、字段名小写、下划线风格，不超过32个字符
#3.禁止使用保留字
2.6.注释
#1.表、字段必须添加必要的注释千万不要偷懒
2.7.默认值
#1.字段定义为 NOT NULL 且需提供默认值
##原因：
NULL的列使索引/索引统计/值比较都更加复杂，数据库自身更难优化
NULL这种类型Msql内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多
NULL值需要更多的存储空，无论是表还是索引中每行中的NULL的列都需要额外的空间来标识
2.8.手写schema
#1.禁止通过工具，或者orm框架生产schema。全部ddl sql必须手工提供
3.sql规范
3.1.select *
#1.大原则：客户端需要什么，就返回什么
#2.读取不需要的列，会增加cpu、Io、网络开销
#3.select * 不能有效利用覆盖索引
3.2.where条件
#1.禁止where条件属性上，执行隐式转换，隐式转换会让索引失效
比如select id, name,phone from table where phone=18688438888 phone是字符串类型
#2.禁止where条件属性上，使用函数或者表达式，where条件属性上使用函数，会让索引失效，同理表达式让索引失效
比如select id,name,age where age+1 = 10
3.3.外键关联
#1.禁止使用外键、级联。一切外键概念必须要应用层解决
##原因：
外键与级联更新适用于单机低并发，不适合分布式、高并发集群
外键影响数据库的插入速度
级联更新是强阻塞，存在数据库更新风暴的风险
3.4.or连接条件
#1.尽量避免在where子句中，通过or连接条件
##原因：
or 连接条件可能会使索引失效
通过union all 替换 or连接条件
3.5.模糊查询
#1.主流关系数据库oracle、mysql支持前缀索引
#2.模糊查询应用场景，like子句中要放在后面
比如：select id,name from table where name like '小明%'
3.6.表关联
#1.表关联数量，尽量不要超过5个表，连表越多，编译的时间和开销也就越大
#2.把连接表拆开成较小的几个执行，可读性更高
#3.表之间的关联，让小表成为驱动表
#4.多个表关联时，每一列上必须明确来源表
比如：select A.id,B.name from A,B WHERE A.id=B.id
3.7.限制结果集
#1.如果明确查询结果最多只有1条记录，请使用好limit=1 或者rownum<=1
4.索引规范
4.1.索引原理
#1.索引的原理：空间换时间
##优势：
减少查询扫描的数据量
避免排序和零时表
将随机IO变为顺序IO
##代价：
需要更多的存储空间
影响更新维护效率增删改
4.2.索引选择
#1.B-tree索引
实践中使用更多的索引类型
支持精确查找、范围查找、前缀查找、支持排序
#2.hash索引
查询效率更高，但只支持精确查找
不支持范围、前缀查找，不支持排序
4.3.索引实践
#1.索引字段区分度要高(索引字段值不能有太多重复数据)
##1.1.比如：select id,name,age from user where sex=1
##1.2.解释：
性别只有男，女，每次过滤掉的数据很少，不宜使用索引
经验上，能过滤80%数据时就可以使用索引。对于订单状态，如果状态值很少，不宜使用索引，如果状态值很多，能够过滤大量数据，则应该建立索引
#2.用好复合索引
##2.1.复合索引，指多个字段联合起来创建索引，比如字段A、字段B，联合创建索引A,B
##2.2.利用复合索引，可以有效减少索引数量，索引(A,B),相当于建立了索引(A)，与索引(A,B)
#3.删除冗余重复的索引，原因参考索引的代价