MYSQL-优化篇一

1.游标
系统为用户开设的一个数据缓冲区，存放sql语句的执行结果，每个游标区都有一个名字，用户可以通过游标逐一获取记录并赋予主变量，交由主语言进一步处理。
存储过程：一个预编译的sql语句，优点是允许模块化设计，只需要创建一次，以后在该程序中就可以调用多次，如果某次操作需要执行多次SQl，使用存储过程比单独sql语句执行要快。
优点：1.存储过程是预编译过的，执行效率高。
2.存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
3.安全性高，执行存储过程需要有一定权限的用户。
4.存储过程可以重复使用，减少数据库开发人员的工作量。
缺点： 1.调试麻烦，但是PL/SQL Developer调试很方便。弥补了这个缺点。
2.移植问题，数据库端代码当然是与数据库相关的，但是如果是做工程型项目，基本不存在移植问题。
3.重新编译问题，因为后端代码运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程，包将需要重新编译（不过也可以设置成运行时自动编译）。
4.如果在一个程序系统中大量的使用存储过程，到程序交付使用时，随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说很难很难，而且代价较大维护困难。
2.触发器
触发器是用户定义在关系表上的一类由事件驱动的特殊存储过程，触发器是指一段代码，当触发某个事件时，自动执行这些代码。
使用场景：
1.可以通过数据库中的相关表实现级联更改
2.实时监控某张表中的某个字段的更改而需要做出相应的处理。
3.例如可以生产某些业务的编号。
4.注意不要滥用，否则会造成数据库及应用程序的维护困难。
5.大家需要牢记以上基础知识点，重点是理解数据类型CHAR和VARCHAR的差异 ，表存储引擎InnoDB和MyISAM的区别。
mysql中的触发器：Before Insert,After Insert,Before Update,After Update,Before Delete,After Delete。
SQL语句主要分为:
数据定义语言DDL： CREATE,DROP,ALTER
主要为以上操作 即对逻辑结构等有操作的，其中包括表结构，视图和索引。
数据查询语言DQL:SELECT,各种简单查询，连接查询等都属于DQL。
数据操作语言DML :INSERT,UPDATE,DELETE。
数据控制功能DCL:GRANT,REVOKE,COMMIT,ROLLBACK主要为以上操作，即对数据库安全性完整性等有操作的，可以简单的理解为权限控制等。
超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键。超键包含候选键和主键。
候选键：是最小超键，即没有冗余元素的超键。
主键：数据库中对储存数据对象予以唯一和完整标识的数据列或者属性的组合。一个数据列只能有一个主键，且主键的取值不能缺少，不能为空。
外键：在一个表中存在的另一个表的主键称此表的外键。
SQL约束有：
NOT NULL:用于控制字段的内容一定不能为空。
UNIQUE: 控件字段内容不能重复，一个表允许有多个Unique约束。
PRIMARY KEY:也是用于控件字段内容不能重复，但它在一个表只能出现一个。
FOREIGN KEY:用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指定的那个表中的值之一。
CHECK:用于控制字段的值范围。
六种关联查询：
交叉连接（CROSS JOIN）
内连接（INNER JOIN）笛卡尔积，很少使用
外连接 （LEFT JOIN/RIGHT JOIN）
联合查询（UNION与UNION ALL）
全连接（FULL JOIN）
交叉连接（CROSS JOIN）
内连接分等值连接，不等值连接，自连接select A A1 INNER JOIN A A2 ON A1.Id =A2.Id
外连接：左外连接，右外连接
联合查询：就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并，如果使用UNION ALL 不会合并重复的记录行。效率UNION 高于UNION ALL
全连接 FULL JOIN：MYSQL 不支持全连接。 可以使用LEFT JOIN 和UNION和RIGHT JOIN联合使用。合并左右外连接
3.子查询的三种情况：
1.单行单列的情况：结果集是一个值，父查询使用：=<>等运算符
2.子查询是多行单列:结果集类似于一个数组，父查询使用in运算
3.子查询是多行多列的情况：结果集类似于一张虚拟表，不能用于where条件，用于select子句中做为子表
mysql中in和exists区别：
mysql中的in语句是把外表和内表hash连接，而exists语句是对外表loop循环再对内表进行查询。
如果查询的两个表大小相当，in和exists差别不大。如果两个一个较小一个较大，则子查询表大的用exists.子查询表小的用in.not in和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引，而not exists 的子查询依然能用到表上的索引。所以无论哪个表大，not exists都比not in 要快
drop,delete与truncate的区别：
UNION 与UNION ALL的区别：如果使用UNION ALL,不会合并重复的记录行，效率UNION高于UNION ALL。
SQL优化：对于低性能的sql语句的定位，最重要也是最有效的方法是使用执行计划，mysql提供了explain命令来查看语句的执行计划。我们知道不管是哪种数据库，或那种数据引擎，在一条sql语句进行执行的过程中都会做很多相关的优化，对于查询语句，最重要的优化方式就是使用索引，而执行计划，就是显示数据库引擎对于sql语句的执行的详细情况，其中包含了是否使用索引。用的什么索引，使用的索引相关信息
**sql的生命周期：**应用服务器与数据库建立连接，数据库进程拿到请求的sql，解析生成执行计划并执行，读取数据到内存并进行逻辑处理，通过连接发生结果到客户端。关闭连接释放资源。
4.大表数据查询优化
1.优化sql语句+索引。
2.加缓存redis
3.主从复制，读写分离
4.垂直拆分，根据模块的耦合度，将一个大的系统分为多个小的系统，也是分布式系统
5.水平切分。针对数据量大的表，这步是最复杂的，要选择一个合理的sharding key ，为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部。
超大分页一般从两个放心上来解决：
1.数据库层面，这也是主要集中关注的，类似于select * from table where age > 20 limit 1000000,10这种查询其实也是有可以优化的余地的. 这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢. 当时我们可以修改为select * from table where id in (select id from table where age > 20 limit 1000000,10).这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果ID连续的好,我们还可以select * from table where id > 1000000 limit 10,效率也是不错的,优化的可能性有许多种,但是核心思想都一样,就是减少load的数据.
2.从需求的角度减少这种请求，主要是不做类似的需求（直接跳转到百万页之后的具体某一页，只允许逐页查看或者按照给定的路线走，这样可预测，可缓存）以及防止ID泄漏且连续被人恶意攻击。
3.主要靠缓存，可预测性的提前查到内容，缓存至redis数据库，直接返回即可。
mysql分页：limit子句可以被用于强制select语句返回指定的记录数，limit接受一个或者两个数字参数。参数必须是一个整数常量，如果给定两个参数，第一个参数指定第一个返回记录行的偏移量吗，第二参数指定返回最大数目，初始记录行偏移量是0而不是1。为了检索从某个偏移量到记录集的结束所有的记录行，可以指定第二个参数为-1，如果只给定一个参数，表示返回最大的记录行数目。
主键推荐使用自增ID而不是UUID因为InnoDB存储引擎中主键索引是聚簇索引存在的，也就是说，主键索引的B+树叶子节点上存储了主键索引以及全部数据（按照顺序）如果主键索引是自增ID,那么只需要不断向后排列即可，如果是UUID,由于到来的ID与原来的大小不确定，会造成非常多的数据插入，数据移动，然后导致产生很多的内存碎片，进而造成插入性能的下降。总之在数据量大一些的情况下，用自增键性能会好些。主键是聚簇索引，如果没有主键，InnoDB会选择一个唯一键作为一个聚簇索引，如果没有唯一键就会生成一个隐式的主键
null值会占用更多的字节，且会在程序中造成很多与预期不符的情况。
密码散列，盐，用户身份证号等固定长度的字符串应该使用char而不是varchr来存储，这样可以节省空间且提高检索效率。
优化查询过程中的数据访问：访问数据太多导致查询性能下降，确定应用程序是否在检索大量超过需要的数据，可能是太多行或列，确定Mysql服务器是否在分析大量不必要的数据行，避免犯sql语句错误，查询不需要的数据使用limit，多表关联返回全部列指定列名，总是返回全部列，不使用select ,重复查询相同的数据，缓存数据，下次直接读取缓存，扫描额外的记录，使用explain进行分析，如果查询需要扫描大量数据，但只返回少数的行，可以通过如下优化使用覆盖索引，把所有的列都放到索引中。这样存储引擎不需要回表获取对应行就可以返回结果。改变数据库和表的结构修改数据表范式。重写sql语句，让优化器以更优的方式执行查询
优化长难的查询语句：一个复杂查询还是多个简单查询，mysql内部每秒都能扫描内存中上百万行数据，相比之下，响应数据给客户端就慢得多，使用尽可能小的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的。切分查询：将一个大的查询分为多个小的相同查询，一次性删除1000万数据要比一次删1万，暂停一会的方案更加损耗服务器开销。分解关联查询让缓存的效率更高，执行单个查询可以减少锁的竞争，在应用层做关联更容易对数据库进行拆分，查询效率会大幅提升，较少冗余记录查询。
优化特定类型的查询语句：
coun()会忽略所有列，直接统计所有列数，不要使用count(列名)，myISAM中没有任何where条件的count()非常快，当有where条件时myisam的count统计不一定比其他引擎快。可以使用explain查询近似值，用近似值代替count()增加汇总表使用缓存
优化关联查询：确定on或者USING子句中是否有索引，确保group by和order by 只有一个表中的列，这样mysql才有可能使用索引。
优化子查询：使用关联查询代替，优化group by和distinct，这两种查询可以使用索引优化，是最有效的优化方式。关联查询中，使用标识列分组效率更高。如果不需要ORDER BY 进行GROUP BY 时加order by null ,mysql不会再进行文件排序，with rollup 超级聚合，可以移到应用程序处理。
优化limit分页：limit偏移量大的时候，查询效率较低，可以记录上次查询的最大ID，下次查询时直接根据该ID来查询。优化UNION,UNION ALL 的效率高于UNION。
优化WHERE子句：对于此类考题，先说明如何定位低效sql语句，然后sql语句可能低效的原因做排查，先从索引着手，如果索引没问题再考虑数据访问的问题。长难查询句的问题还是一些特定类型优化的问题，逐一回答。
sql语句优化：1.对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by 涉及列上建立索引
2.应尽量避免在where子句中进行null值判断。否则将导致引擎放弃使用索引进行全表扫描。
3.应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引进行全表扫描
4.应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃索引进行全表扫描。可以做合并表查询 or 换成union all
5.in和not in 也要慎用，尽量用between and
6.下面程序也将导致全表扫描：select id from a where name like ‘%王%’，若要提高效率，可以考虑全文检索
7.如果where子句中使用参数，也会导致全表扫描。因为sql只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时，它必须在编译时进行选择。然而如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。
8.应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引进行全表扫描。
9.应尽量避免在where子句中对字段进行函数操作这将导致引擎放弃使用索引进行全表扫描
10.不要在where子句中的“=”左边进行函数，算术运算或其他表达式运算，否则将无法正常使用索引