一.数据库基础

1.事务隔离级别

  • 读未提交（Read uncommitted）

一个事务A进行了修改但是未提交，此时另个事务B会读取到A中未提交的事务，如果一个事务已经开始写数据,则另一个事务不允许同时进行写操作.【解决了更新丢失但是会出现脏读、不可重复读、幻读问题】

  • 读已经提交（Read committed）

如果是一个读事务，则允许其他事务读写，如果是写事务，将会禁止其他事务访问该行数据。避免了脏读，但是可能出现不可重复读，事务B再事务A修改提交事务前后进行查询，导致两次结果不同.【解决了脏读，但会出现不可重复读、幻读问题】

  • 可重复读（Repeatable Read）

在一个事务内，多次读同一数据，在事务还未结束前，其他事务不能访问该数据（包括读写），这样就可以在同一个事务内多次读到的数据是一样的。【解决了脏读、不可重复读，但是仍会出现幻读问题】

  • 可序化（Serializable）

严格按照事务串行执行，解决以上问题，但是执行效率低下。

开启事务
数据库是默认开启事务的，默认隔离级别为【读已提交】，一般会修改为可重复读

  • 查看事务级别
    • select @@tx_isolation
  • 修改事务级别
    • set global transaction isolation level read committed; //全局的
    • set session transaction isolation level read committed; //当前会话

  • 开启事务

    set autocommit = 0;
    start transaction;
    sql语句；
    commit/rollback;

2.视图

  • 创建视图

create view v1 AS select xxx from table;

  • 使用视图

select v1;

  • 修改视图

    1. 1.alter view v1 AS select xxx from table1;<br />2.create or replace v1 AS select xxx from table1;

  • 删除视图

drop view v1,v2….

  • 查看视图

1.desc v1
2.show create view v1

  • 修改视图中数据 (危险操作,禁止操作)

update v1 set name = “xxx”

3.删除(truncate与delete)

truncate table t1 不支持事务
delete from t1 支持事务

4.不常见sql整理

mysql 查询记录行号

SELECT
@n := @n+1
FROM
(select @n := 0) tmp
WHERE
1 = 1
limit 0,10000;

SELECT
@n := @n+1,
hpce.settle_way
FROM
hzero_cms.hcms_po_contract_exe hpce,
(select @n := 0) tmp
WHERE
1 = 1
limit 0,10000;

mysql 一行转多行

SELECT
SUBSTRING_INDEX( SUBSTRING_INDEX( su.ATTRIBUTE2, ‘,’, b.help_topic_id + 1 ), ‘,’,- 1 ) AS ids
FROM
sys_user AS su
JOIN mysql.help_topic AS b ON b.help_topic_id < ( length( su.ATTRIBUTE2 ) - length( REPLACE ( su.ATTRIBUTE2, ‘,’, ‘’ ) ) + 1 )
WHERE
su.USER_NAME = ‘shichen@tj.cmcc’;

添加虚拟列并新建索引

ALTER TABLE hwms_department_batch ADD COLUMN root_batch varchar(100) AS (SUBSTRING_INDEX(level_path,’|’,1)) stored,
ADD INDEX hwms_department_batch_u2 (root_batch);

一列拆多列

case when locate(‘规格:’, hrli.item_attr_comb) > 0 then substring_index( substring_index(hrli.item_attr_comb, ‘规格:’, -1), ‘;’, 1) else null end as specification,/规格/ case when locate(‘材质:’, hrli.item_attr_comb) > 0 then substring_index( substring_index(hrli.item_attr_comb, ‘材质:’, -1), ‘;’, 1) else null end as material, /材质/ case when locate(‘品牌:’, hrli.item_attr_comb) > 0 then substring_index( substring_index(hrli.item_attr_comb, ‘品牌:’, -1), ‘;’, 1)

查询分组后的主键最大记录

  1. SELECT hica.cost_price,

    hica.batch_num FROM hzero_fin.hfin_inv_cost_accont hica WHERE hica.id IN ( SELECT max( hica1.id ) FROM hzero_fin.hfin_inv_cost_accont hica1 GROUP BY 

    1. hica1.batch_num )

  2. SELECT

    FROM
    ( SELECT     FROM doc_rate dr ORDER BY dr.id DESC LIMIT 10000 ) temp
    GROUP BY
    temp.doc_id

5.存储过程

  1. 创建方式

    create procedure 存储过程名(参数列表)begin

    1.         存储过程体(一组合法的sql语句)

    end

    注意 : 1.参数列表包含三部分 参数模式 参数名 参数类型

    in name varchar(20)

    参数模式: in 该参数可以作为输入,需要调用方传入 out 该参数作为输出 inout 该参数既作为输入也作为输出 2.如果存储过程只有一句话 begin end可以省略,存储过程体中的每条sql语句的结束要求必须加分号 存储过程的结尾可以使用delimiter重新设置 语法: delimiter 结束标记 案例: delimiter $

  2. 调用语法

    call 存储过程名(实参列表);

  3. 案例

    delimiter ; // 定义结束符 create procedure myp1() begin

    1.         insert into student(username, pws) 
    2.         values('zs','123')

    end;

    调用

    call myp1();

    案例二: delimiter; CREATE PROCEDURE myp1 ( IN username VARCHAR ( 20 ) ) BEGIN DECLARE

    1. result INT DEFAULT 0;#声明变量并初始化

    SELECT

    1. count( * ) INTO result

    FROM

    1. acl_user u

    WHERE

    1. u.`name` = username;

    SELECT IF

    1. ( result > 0, '成功', '失败' );#使用变量

    END;

    调用

    CALL myp1 ( ‘admin’ );

  4. 删除存储过程

drop procedure 存储过程名称
drop procedure p1,p2;

  1. 查看存储过程信息

1.desc p1;
2.show create procedure p1;

6.函数

  1. 创建语法

    create function 函数名(参数列表) returns 返回类型 begin 函数体 end;
    注意: 1.参数列表 包含两部分 参数名 参数类型 2.函数体: 一定要有return语句,如果没有会报错

  2. 调用语法

    select 函数名(参数列表)

  1. 案例

    create function myfc() returns int begin declare c int default 0; select count(*) into c from acl_user; return c; end;

    调用

    select myfc()

7.循环结构

分类：while 、loop、repeat（需要在begin end代码块中使用——函数、存储过程中使用）
循环控制：
iterate类似于 continue，
leave 类似于break

  1. while

语法： while 循环条件 do
循环体
end while；

  1. loop

语法： loop
循环体
end loop；

  1. repeat

语法： repeat
循环体
until 结束循环的条件；

  1. 案例

    truncate table doc_rate;drop PROCEDURE test_while; CREATE PROCEDURE test_while ( IN count INT ) BEGIN DECLARE

    1. i INT DEFAULT 1;

    a:WHILE i <= count DO

    1. INSERT INTO doc_rate

    VALUES

    1. ( NULL, i, i / 100, '测试', now( ) );

    IF

    1. i >= 20 THEN
    2. LEAVE a;

    END IF;

    SET i = i + 1;

    END WHILE a; END;

    调用

    call test_while(100);

二.数据库高级

1.性能下降sql慢执行时间长分析

  1. 查询语句写的烂
  2. 索引失效（单值、复合）
  3. 关联查询太多join（设计缺陷或不得已的需求）
  4. 服务器调优以及各个参数设置（缓冲、线程数等）

2.SQL优化方案总结

SQL 性能起飞了！

这篇文章总结了 52 条 SQL 性能优化技巧，用简洁明了的语言介绍

  1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
  2. 应尽量避免在 where 子句中对字段进行 null 值判断，创建表时NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个特殊的值，如0，-1作为默 认值。
  3. 应尽量避免在 where 子句中使用!=或<>操作符， MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。 关于 MySQL 相关的文章和教程发布了很多，关注微信公众号 Java后端，回复 666 下载就行了。
  4. 应尽量避免在 where 子句中使用 or 来连接条件， 否则将导致引擎放弃使用索引而进行全表扫描， 可以 使用UNION合并查询： select id from t where num=10 union all select id from t where num=20
  5. in 和 not in 也要慎用，否则会导致全表扫描，对于连续的数值，能用 between 就不要用 in 了： Select id from t where num between 1 and 3
  6. 下面的查询也将导致全表扫描： select id from t where name like ‘%abc%’ 或者 select id from t where name like ‘%abc’ 若要提高效率，可以考虑全文检索。而 select id from t where name like ‘abc%’ 才用到索引
  7. 如果在 where 子句中使用参数，也会导致全表扫描。
  8. 应尽量避免在 where 子句中对字段进行表达式操作，应尽量避免在where子句中对字段进行函数操作
  9. 很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b).用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)
  10. 索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
  11. 应尽可能的避免更新 clustered 索引数据列， 因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
  12. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
  13. 尽可能的使用 varchar/nvarchar 代替 char/nchar ， 因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
  14. 最好不要使用”“返回所有：select from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
  15. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
  16. 使用表的别名(Alias)：当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
  17. 使用“临时表”暂存中间结果 简化SQL语句的重要方法就是采用临时表暂存中间结果，但是，临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在tempdb中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。
  18. 一些SQL查询语句应加上nolock，读、写是会相互阻塞的，为了提高并发性能，对于一些查询，可以加上nolock，这样读的时候可以允许写，但缺点是可能读到未提交的脏数据。使用 nolock有3条原则。 查询的结果用于“插、删、改”的不能加nolock ！查询的表属于频繁发生页分裂的，慎用nolock ！使用临时表一样可以保存“数据前影”，起到类似Oracle的undo表空间的功能，能采用临时表提高并发性能的，不要用nolock 。
  19. 常见的简化规则如下：不要有超过5个以上的表连接（JOIN），考虑使用临时表或表变量存放中间结果。少用子查询，视图嵌套不要过深,一般视图嵌套不要超过2个为宜。
  20. 将需要查询的结果预先计算好放在表中，查询的时候再Select。这在SQL7.0以前是最重要的手段。例如医院的住院费计算。
  21. 用OR的字句可以分解成多个查询，并且通过UNION 连接多个查询。他们的速度只同是否使用索引有关,如果查询需要用到联合索引，用UNION all执行的效率更高.多个OR的字句没有用到索引，改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到索引。
  22. 在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数。
  23. 尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的SQL语句，是控制流语言的集合，速度当然快。反复执行的动态SQL,可以使用临时存储过程，该过程（临时表）被放在Tempdb中。
  24. 当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率；否则使用 配制线程数量<最大连接数启用SQL SERVER的线程池来解决,如果还是数量 = 最大连接数+5，严重的损害服务器的性能。
  25. 查询的关联同写的顺序 select a.personMemberID, from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = ‘JCNPRH39681’ （A = B ,B = ‘号码’） select a.personMemberID, from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = ‘JCNPRH39681’ and b.referenceid = ‘JCNPRH39681’ （A = B ,B = ‘号码’， A = ‘号码’） select a.personMemberID, from chineseresume a,personmember b where b.referenceid = ‘JCNPRH39681’ *and a.personMemberID = ‘JCNPRH39681’ （B = ‘号码’， A = ‘号码’）
  26. 尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。
  27. 尽量使用“>=”，不要使用“>”。
  28. 索引的使用规范：索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引；尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index index_name来强制指定索引；避免对大表查询时进行table scan，必要时考虑新建索引； 在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用；要注意索引的维护，周期性重建索引，重新编译存储过程。
  29. 下列SQL条件语句中的列都建有恰当的索引，但执行速度却非常慢： SELECT FROM record WHERE *substrINg(card_no,1,4)=’5378’ (13秒)

SELECT FROM record *WHERE amount/30< 1000 （11秒）

SELECT FROM record WHERE convert(char(10),date,112)=’19991201’ （10秒） 分析： WHERE子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索，因此将SQL重写成下面这样： SELECT FROM record WHERE card_no like ‘5378%’ （< 1秒）

SELECT FROM record WHERE amount< 100030 （< 1秒）

SELECT FROM record *WHERE date= ‘1999/12/01’ （< 1秒）

  1. 当有一批处理的插入或更新时，用批量插入或批量更新，绝不会一条条记录的去更新!
  2. 在所有的存储过程中，能够用SQL语句的，我绝不会用循环去实现! (例如：列出上个月的每一天，我会用connect by去递归查询一下，绝不会去用循环从上个月第一天到最后一天)
  3. 选择最有效率的表名顺序(只在基于规则的优化器中有效)： oracle 的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.
  4. 提高GROUP BY语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果，但第二个明显就快了许多. 低效: SELECT JOB , AVG(SAL)

FROM EMP

GROUP BY JOB

HAVING JOB =’PRESIDENT’

OR JOB =’MANAGER’ 高效: SELECT JOB , AVG(SAL)

FROM EMP

WHERE JOB =’PRESIDENT’

OR JOB =’MANAGER’

GROUP BY JOB

  1. sql语句用大写，因为oracle 总是先解析sql语句，把小写的字母转换成大写的再执行。
  2. 别名的使用，别名是大型数据库的应用技巧，就是表名、列名在查询中以一个字母为别名，查询速度要比建连接表快1.5倍。
  3. 避免死锁，在你的存储过程和触发器中访问同一个表时总是以相同的顺序;事务应经可能地缩短，在一个事务中应尽可能减少涉及到的数据量;永远不要在事务中等待用户输入。
  4. 避免使用临时表，除非却有需要，否则应尽量避免使用临时表，相反，可以使用表变量代替;大多数时候(99%)，表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在TempDb数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢。
  5. 最好不要使用触发器，触发一个触发器，执行一个触发器事件本身就是一个耗费资源的过程;如果能够使用约束实现的，尽量不要使用触发器;不要为不同的触发事件(Insert，Update和Delete)使用相同的触发器;不要在触发器中使用事务型代码。
  6. 索引创建规则：
  • 表的主键、外键必须有索引；
  • 数据量超过300的表应该有索引；
  • 经常与其他表进行连接的表，在连接字段上应该建立索引；
  • 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
  • 索引应该建在选择性高的字段上；
  • 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
  • 复合索引的建立需要进行仔细分析，尽量考虑用单字段索引代替；
  • 正确选择复合索引中的主列字段，一般是选择性较好的字段；
  • 复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
  • 如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
  • 如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
  • 如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
  • 频繁进行数据操作的表，不要建立太多的索引；
  • 删除无用的索引，避免对执行计划造成负面影响；

表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。 尽量不要对数据库中某个含有大量重复的值的字段建立索引。 40，mysql查询优化总结：使用慢查询日志去发现慢查询，使用执行计划去判断查询是否正常运行，总是去测试你的查询看看是否他们运行在最佳状态下。 久而久之性能总会变化，避免在整个表上使用count(*),它可能锁住整张表，使查询保持一致以便后续相似的查询可以使用查询缓存 ，在适当的情形下使用GROUP BY而不是DISTINCT，在WHERE, GROUP BY和ORDER BY子句中使用有索引的列，保持索引简单,不在多个索引中包含同一个列，有时候MySQL会使用错误的索引,对于这种情况使用USE INDEX，检查使用SQL_MODE=STRICT的问题，对于记录数小于5的索引字段，在UNION的时候使用LIMIT不是是用OR。 为了避免在更新前SELECT，使用INSERT ON DUPLICATE KEY或者INSERT IGNORE ,不要用UPDATE去实现，不要使用 MAX,使用索引字段和ORDER BY子句，LIMIT M，N实际上可以减缓查询在某些情况下，有节制地使用，在WHERE子句中使用UNION代替子查询. 在重新启动的MySQL，记得来温暖你的数据库，以确保您的数据在内存和查询速度快，考虑持久连接，而不是多个连接，以减少开销，基准查询，包括使用服务器上的负载，有时一个简单的查询可以影响其他查询，当负载增加您的服务器上，使用SHOW PROCESSLIST查看慢的和有问题的查询，在开发环境中产生的镜像数据中 测试的所有可疑的查询。

  1. MySQL 备份过程: 从二级复制服务器上进行备份。在进行备份期间停止复制，以避免在数据依赖和外键约束上出现不一致。彻底停止MySQL，从数据库文件进行备份。 如果使用 MySQL dump进行备份，请同时备份二进制日志文件 – 确保复制没有中断。不要信任LVM 快照，这很可能产生数据不一致，将来会给你带来麻烦。为了更容易进行单表恢复，以表为单位导出数据 – 如果数据是与其他表隔离的。 当使用mysqldump时请使用 –opt。在备份之前检查和优化表。为了更快的进行导入，在导入时临时禁用外键约束。 为了更快的进行导入，在导入时临时禁用唯一性检测。在每一次备份后计算数据库，表以及索引的尺寸，以便更够监控数据尺寸的增长。 通过自动调度脚本监控复制实例的错误和延迟。定期执行备份。
  2. 查询缓冲并不自动处理空格，因此，在写SQL语句时，应尽量减少空格的使用，尤其是在SQL首和尾的空格(因为，查询缓冲并不自动截取首尾空格)。
  3. member用mid做標準進行分表方便查询么？一般的业务需求中基本上都是以username为查询依据，正常应当是username做hash取模来分表吧。分表的话 mysql 的partition功能就是干这个的，对代码是透明的； 在代码层面去实现貌似是不合理的。
  4. 我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的AUTO_INCREMENT标志。
  5. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。 无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
  6. MySQL查询可以启用高速查询缓存。这是提高数据库性能的有效Mysql优化方法之一。当同一个查询被执行多次时，从缓存中提取数据和直接从数据库中返回数据快很多。
  7. EXPLAIN SELECT 查询用来跟踪查看效果 使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的……等等，等等。
  8. 当只要一行数据时使用 LIMIT 1 当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。在这种情况下，加上 LIMIT 1 可以增加性能。这样一样，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。
  9. 选择表合适存储引擎： myisam: 应用时以读和插入操作为主，只有少量的更新和删除，并且对事务的完整性，并发性要求不是很高的。 Innodb：事务处理，以及并发条件下要求数据的一致性。除了插入和查询外，包括很多的更新和删除。（Innodb有效地降低删除和更新导致的锁定）。对于支持事务的InnoDB类型的表来说，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动提交，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事物（即使autocommit打开也可以），将大大提高性能。
  10. 优化表的数据类型,选择合适的数据类型： 原则：更小通常更好，简单就好，所有字段都得有默认值,尽量避免null。 例如：数据库表设计时候更小的占磁盘空间尽可能使用更小的整数类型.(mediumint就比int更合适) 比如时间字段：datetime和timestamp, datetime占用8个字节，而timestamp占用4个字节，只用了一半，而timestamp表示的范围是1970—2037适合做更新时间 MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。 因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如， 在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间， 甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话， 我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。 应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。 对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理， 而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。
  11. 字符串数据类型：char，varchar，text选择区别
  12. 任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

3.MySQL常见瓶颈

  1. CPU:CPU再饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
  2. IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
  3. 服务器硬件性能瓶颈：top，free，iostat，vmstat来查看系统的性能状态

4.索引

  1. 索引创建

    1.create index index_name on table_name (column) 2.alter table table_name add index index_name(column1, column2, column3)

  2. 哪些情况需要创建索引

    1.主键自动建立唯一索引 2.频繁作为查询条件的字段应该创建索引 3.查询中以其他表关联的字段，外键关系建立索引 4.频繁更新的字段不适合创建索引，因为美再次更新不仅更新数据 还会更新索引，影响性能。 5.where条件里用不到的字段不适合创建索引 6.单键/组合索引的选择问题，高并发下倾向创建组合索引 7.查询中排序的字段，排序字段若通过索引去访问将大大提升访问速度 8.查询中统计或分组的字段

  3. 哪些情况不适合创建索引

    1.数据量小 2.数据重复的字段（例如性别） 3.经常增删改的表

5.使用Explain分析sql执行计划

image.png

  1. 表的读取顺序 （id决定）

image.png
2.select_type 查询类型，主要用于区分简单查询、联合查询、子查询等复杂查询

1.simple:简单的select查询，查询中不包含子查询或者union 2.primary:查询中若包含任何复杂的子部分，最外层查询则被标记 3.subquery:在select或者where列表中包含了子查询 4.derived:在from列表包含的子查询被标记为derived(衍生)，MySQL会递归执行这些子查询，把结果放在临时表里 5.union:若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为derived 6.union result:从union表获取结果的select

  1. type 访问类型，是较为重要的一个指标

结果值从好到坏依次是：
system>const>eq_ref>ref>range>index>all

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

  1. possible_keys:可能用到的索引key，但查询优化策略不一定采用，可以使用函数 force_index(index) 来强制使用索引key。
  2. key：实际使用的索引，如果为null，则没有使用索引，查询种若使用了覆盖索引，则该索引近出现在key列表中

  3. key_len:表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的是索引字段的最大可能长度，而非实际定义的长度，即是根据表定义计算而得，不是通过表内检索出的。

  4. ref:显示索引的哪一列被使用了，如果可能的话，是一个常量，哪些列或常量被用于查找索引列上的值。

image.png

  1. rows:根据表统计信息及索引使用情况，大致估算出找到所需的记录需要读取的行数。
  2. Extra:
    1. Using filesort : 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引进行读取。MySQL中无法利用索引完成的排序称为“文件排序”。（需进行优化）
    2. Using temporary : 查询过程中创建了临时表来存放中间结果，多发生于group by 、order by语句中。（需进行优化）
    3. Using index : 查询使用到索引
    4. Using where : 用到了查询筛选条件

6. 索引优化方案

  1. 单表优化

主要问题：如果是联合索引 index_cc（c1,c2,c3）, 查询条件为 c1 = xxx and c2>xxx order by c3 desc 则会产生文件内排序问题。
原因在于：联合索引在遇到范围查询时 后面的索引字段会失效
解决方法：将 索引修改为 index_cc1(c1,c3)

  1. 两表、多表关联优化

select a.*,b.name from a left join b on a.order_id = b.order_id 索引建在a表还是b表？？
结论：
1.应加在副表上。 （索引建在b表）。

  1. join语句优化

  2. 尽可能减少join语句中的nestedLoop的循环总次数，尽量用小结果集作为主表去驱动大的结果
    2.优先优化nestedLoop的内层循环
    3.保证join语句中被驱动表上连接条件字段已经被索引

7.索引失效问题

  1. 最佳左前缀法则
  2. 不在索引列上做任何操作(计算、函数、自动或手动类型转换)，会导致索引失效而转向全表扫描
  3. 存储引擎不能使用索引中范围条件右边的列（范围条件后的索引字段全失效）
  4. 尽量使用覆盖索引（只查询索引列）
  5. mysql中再使用不等于（!= 或 <>）的时候无法使用索引会导致全表扫描
  6. is null, is not null 也无法使用索引
  7. like以通配符开头（’%abc…’）将无法使用索引
  8. 字符串不加单引号索引会失效
  9. 少用or，使用or会导致索引失效

问题1：如何使 like ‘%aa%’ 使用索引？
使用覆盖索引可以解决此情形不走索引的问题。例如 select id，name，age from xxx where name like ‘%aa%’; (索引字段为 name, age 联合索引)

问题2：name字段是varchar类型，select * from xxx where name = 2000走索引吗？
上述不走索引，查询条件要加引号 select * from xxx where name = ‘2000’

问题3：以下sql 索引使用情况？
image.png
除 3）仅仅使用3个索引。1），2)均用到了全部索引。
原因：mysql底层优化器会对查询顺序优化 类似 交换律 2+1 <=> 1+2

image.png

image.png

8.查询截取分析慢sql

  • 慢查询的开启并捕获
  • explain+慢sql分析
  • show profile查询sql在mysql服务器中的执行细节和生命周期情况
  • sql数据库服务器的参数调优

1.慢查询的开启并捕获

  1. 默认情况下，MySQL数据库没有开启慢查询日志，需要手动设置这个参数。
  2. 如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会带来一定的性能影响。慢查询日志支持将日志记录写入文件。
  3. 开启慢日志记录

    show variables like ‘%slow_query_log%’

    set globle slow_query_log=1; [临时开启]

    show variables like ‘%long_query_time%’ 【查看慢日志时间】

    set globle long_query_time = 3 【修改为阈值到3秒的就是慢sql】

2.提高order by的速度

  1. order by 时select * 是一个大忌，只查询需要的字段，这点非常重要。在这里的影响是：

1.1 当查询的字段大小总和小于max_length_for_sort_data 而且排序字段不是TEXT|BLOB类型时，会用改进后的算法—单路排序。否则用老算法—多路排序。
1.2 两种算法的数据都有可能超出sort_buffer的容量。超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。

  1. 尝试提高sort_buffer_size

不管用哪种算法，提高这个参数都会提高效率。当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。

  1. 尝试提高max_length_for_sort_data

提高这个参数，会增加用改进后算法的概率。但是如果设置的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O和低的处理器使用率。

image.png

3.抓取慢sql

mysqldumpslow —help命令查看详细命令

1.得到返回记录最多的10个sql mysqldumpslow -s r -t 10 /var/lib/mysql/xxx-slow.log 2.得到访问次数最多的10个sql mysqldumpslow -s -c -t 10 /var/lib/mysql/xxx-slow.log 3.得到按照时间排序的前10条里卖含有左连接的查询语句 mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/xxx-slow.log 4.另外建议在使用这些命令时结合|和more使用，否则有可能出现爆屏情况 mysqldumpslow -s r -t 10 /var/lib/mysql/xxx-slow.log | more

4.show profiles

1.是什么？
是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于sql的调优的测量
默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

2.使用
1.首先查看是否开启
show variables like ‘%profiling%’;
2.开启
set profiling=on；
3.通过show profiles 查看结果
image.png

4.使用show profile对sql语句进行诊断。
show profile cpu,block io for query Query_ID;/Query_ID为#3步骤中show profiles列表中的Query_ID/
比如执行：show profile cpu,block io for query 15;
image.png
5.show profile的常用查询参数。

①ALL：显示所有的开销信息。 ②BLOCK IO：显示块IO开销。 ③CONTEXT SWITCHES：上下文切换开销。 ④CPU：显示CPU开销信息。 ⑤IPC：显示发送和接收开销信息。 ⑥MEMORY：显示内存开销信息。 ⑦PAGE FAULTS：显示页面错误开销信息。 ⑧SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。 ⑨SWAPS：显示交换次数开销信息。

6.日常开发需注意的结论。

①converting HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了。 ②Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。 ③Copying to tmp table on disk：把内存中临时表复制到磁盘上，危险！！！ ④locked。

如果在show profile诊断结果中出现了以上4条结果中的任何一条，则sql语句需要优化。