学习视频网址：尚硅谷MySQL数据库高级，mysql优化，数据库优化_哔哩哔哩_bilibili
目录
1、索引的简介
创建索引：
user表中字段： id name email weixinNumber
sql语句：select * from user where name=" " and email=" ";
给user表中name字段创建唯一索引：create index idx_user_name on user(name)；
给user表中多个字段创建索引：create index idx_user_nameEmail on user(name,email);
索引的优势：
类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本，通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
索引的劣势：
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的，虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MYSQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
mysql索引分类：
单值索引;即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：即一个索引包含多个列
基本语法：创建 ：create [unique] index indexName on mytable(columnname(length));
alter mytable add [unique] index [indexName] on(columnname(length)）
删除：drop index [indexname] on mytable
查看：show index from table_name\G
使用alter命令
mysql索引结构：
BTree索引-索引原理：
Hash索引
full-text全文索引
R-Tree索引
那些情况需要创建索引：
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段，外键关系建立索引
- 频繁更新的字段不适合创建索引-因为每次更新不单单是更新了记录还会更新索引
- where条件里用不到的字段不创建索引
- 单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引）
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
那些情况不要创建索引：
- 表记录太少
- 经常增删改的表：Why：提高了查询速度，同时却会降低更新表的速度，如对表进行insert,update和delete。因为更新表时，MYSQL不仅要保存数据，还要保存一下索引文件
- 数据重复且分布平均的表字段，因此应该只为最经常查询最经常排序的数据列建立索引。注意：如果某个数据列包含许多重复的内容，为他建立索引就没有实际的效果。（假如一个表有10万行记录，有一个字段A只有T和F两种字段值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一张表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。
2、性能分析
MySQL Query Optimizer
MYSQL常见瓶颈
CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
IO：磁盘I/O发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈：top,free,iostat和vmstat来查看系统的性能状态
Explain
是什么（查看执行计划）:
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
官网地址：http://dev.mysql.com/doc/refman/5.5/en/explain-output.html
能干嘛
- 表的读取顺序
- 数据读取操作的操作类型
- 那些索引可以使用
- 那些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
怎么玩
- Explain+sql语句
- 执行计划包含的信息：
id select_type table type possible_keys key key_len ref rows Extra
名字段解释
id:select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
如果是子查询,id的序号会递增，id值越大优先级越高，越先被执行。（执行顺序：t3,t1,t2)
id如果相同，可以认为是一组，从上往下顺序执行；
在所有组中，id值越大，优先级越高，越先执行
上述括号语句中执行后产生的表为衍生的虚表=DERIVED(执行顺序：t3,<derived2>,t2)
select_type:
select_type包含那些：
- SIMPLE ：简单的select查询，查询中不包含子查询或者UNION
- PRIMARY:查询中包含任何复杂的子部分，最外层查询则被标记为primary
- SUBQUERY ：在SELECT或WHERE列表中包含了子查询
- DERIUED:在FROM列表中包含了子查询被标记为derived(衍生）MYSQL会递归执行这些子查询，把结果放在临时表里。
- UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIUED
- UNION RESULT:从UNION表获取结果的SELECT
table:显示这一行的数据是关于那一张表的
type：
访问类型排序，显示查询使用了何种类型，从最好到最差依次是：
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>range>index>ALL
简单记为：system>const>eq_ref>ref>range>index>ALL
一般来说，得保证查询至少达到range级别，最好能达到ref
- system:表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计
- const:表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MYSQL就能将查询转换为一个常量。
- er_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
- ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，他可能会找到多个符合条件的行，所有它应该属于查找和扫描的混合体。
- range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
- index：Full Index Scan,index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读取的）
- all:Full Table Scan,将遍历全表找到匹配的行。
possible_keys:
显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
key:
实际使用的索引。如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引仅出现在key列表中。
key_len:
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
ref:
显示索引的哪一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值
rows:
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
Extra:
包含不适合在其他列中显示但十分重要的额外信息。
- Using filesort:说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MYSQL中无法利用索引完成的排序操作成为“文件排序”
- Using temporary:使用了临时表保存中间结果，MYSQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by
- USING index:表示相应的select操作中使用了覆盖索引（Covering Index),避免访问了表的数据行，效率不错！如果同时出现using where,表明索引被用来执行索引键值的查找；如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。（覆盖索引：理解方式一：就是select的数据列只用从索引中就能够取得，不必读取数据行，MYSQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖，理解方式二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了他们索引的数据；当能通过索引就可以找到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了），满足查询结果的数据就叫做覆盖索引。）
- Using where:表明使用了where过滤
- Using join buffer:使用了连表缓存
- impossible where:where子句的值总是false,不能用来获取任何元组
- select tables optimized away:在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MYISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
- distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
热身Case
3、索引失效（应该避免）
3.1建表sql:
3.2案例（索引失效）
- 全值匹配我最爱（sql语句中将创建的索引都用到）
- 最佳左前缀法则（1、创建的索引列从最左侧开始使用，不能跳过第一个索引使用第二第三个索引，这将导致索引失效。2、中间兄弟不能断，不能跳过第二个索引，直接使用第一第三个索引）
- 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引列失效而转向全表扫描
- 存储引擎不能使用索引中范围条件右边的列
- 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
- mysql在使用不等于（！=或者<>)的时候无法使用索引会导致全表扫描
- is null,is not null也无法使用索引
- like以通配符开头（%abc...')mysql索引失效会变成全表扫描的操作----解决方法：like的百分号写右边或者使用覆盖索引；
- 字符串不加单引号索引失效
- 少用or,用它来连接时会索引失效
小总结：
一般性建议
- 对于单键索引，尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
- 在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引
- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
【优化总结口诀】:
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！
4、查询截取分析
sql语句分析：
- 观察，至少跑一天，看看生产的慢sql情况
- 开启慢查询日志，设置阀值，比如超过5秒钟的就是慢sql，并将它抓取出来
- explain+慢sql分析
- show profile
- 运维经理 or DBA，进行sql数据库服务器的参数调优
4.1查询优化
永远小表驱动大表：类似嵌套循环Nested Loop
EXISTS:
select from table where exists(subquery)
该语法可以理解为:将主查询的数据,放到子查询中做条件验证,根据验证结果(TURE 或 FALSE)来决定主查询的数据结果是否得以保留.
提示:
EXISTS与IN两种写法对比:
order by关键字优化
1. ORDER BY子句,尽量使用 index方式排序,避免使用FileSort方式排序
2. 尽可能在索引列上完成排序操作,遵照索引建的最佳左前缀
3. 如果不在索引列上,filesort有两种算法:mysql就要启动双路排序和单路排序
- 双路排序:MYSQL4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据,读取指针和order by列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出,从磁盘取排序字段,在buffer进行排序,再从磁盘取其他字段.取一批数据,要对磁盘进行了两次扫描,众所周知,I/O是很耗时的,所以在mysql4.1之后,出现了第二种改进的算法,就是单路排序.
- 单路排序:从磁盘读取查询需要的所有列,按照order by 列在buffer对它们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据.并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存在内存中了.
- 结论及引申出的问题:由于单路是后出的,总体而言好过双路,但是用单路有问题:
4. 优化策略:增大sort_buffer_size参数的设置、增大max_length_for_sort_data参数的设置
小总结：
GROUP BY关键字优化
- group by实质是先排序后进行分组，遵照索引建的最佳左前缀
- 当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
- where高于having，能写在where限定的条件就不要去having限定了
4.2慢查询日志
是什么？
1、mysql的慢查询日志是mysql提供的一种日志记录，它用来记录在mysql中响应时间超时阀值的语句，具体指运行时间超过long_query_time值得SQL，则会被记录到慢查询日志中。
2、具体指运行时间超过long_query_time值得SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。
3、由他来查看那些SQL超出了我们最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的SQL，结合之前explain进行全面分析。
怎么玩？
说明：
默认情况下，MYSQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
查看是否开启及如何开启：
查看是否开启：SHOW VARIABLES LIKE'%slow_query_log%';
开启慢查询日志：set_global_slow_query_log=1;
如果要永久生效，就必须修改配置文件my.cnf(其他系统变量也是如此）
修改my.cnf文件，[mysqld]下增加或修改参数
slow_query_log和slow_query_log_file后，然后重启MYSQL服务器。也即将如下两行配置进my.cnf文件
show_query_log=1
show_query_log_file=/var/lib/mysql/atguigu-slow.log
那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？
这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，
命令：SHOW VARIABLES LIKE 'long_query_time';
可以使用命令修改，也可以在my.cnf参数里面修改。
假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里面是判断大于long_query_time,而非大于等于。
- 查看当前多少秒算慢：SHOW VARIABLES LIKE 'long_query_time%’；
- 设置慢的阀值时间：set global long_query_time=3;
- 为什么设置后看不出变化？需要重新连接或新开一个会话才能看到修改值。1、重新连接使用命令show global variables like 'long_query_time';2、重新开一个窗口然后使用命令：SHOW VARIABLES LIKE 'long_query_time%';
- 查询当前系统中有多少条慢查询记录：show global status like '%Slow_queries%';
日志分析工具mysqldumpslow
1、查看mysqldumpslow的帮助信息：在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MYSQL提供了日志分析工具mysqldumpslow。
2、工作常用参考：
4.3批量数据脚本
插入1000万数据：
1、建表
2、设置参数log_bin_trust_function_creators=1
3、创建函数，保证每条数据都不同
随机产生字符串：
随机产生部门编号：
4、创建存储过程
创建往emp表中插入数据的存储过程
创建往dept表中插入数据的存储过程
5、调用存储过程
调用存储过程dept
调用存储过程emp
4.4Show Profile
是什么：是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量
官网：http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
默认情况下，参数处于关闭状态，并保存最近15次的运行结果
分析步骤：
1、是否支持，看看当前的mysql版本是否支持。show variables like 'profiling';
2、开启功能，默认是关闭，使用前需要开启。set profiling=on;
3、运行SQL。
select * from tbl_emp;
select * from tbl_emp e inner join tbl_dept d on e.deptld=d.id;
select * from emp group by id%10 limit 150000;
select * from emp group by id%20 order by 5;
4、查看结果，show profiles；
5、诊断SQL，show profile cpu,block io for query上一步前面的问题SQL数字号码；
参数备注
6、日常开发需要注意的结论
- converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了
- Creating tmp table创建临时表：拷贝数据到临时表；用完再删除
- Copying to tmp table on disk把内存中临时表复制到磁盘，危险！！
- locked
4.5全局查询日志
配置启用：
编码启用：
永远不要在生产环境开启这个功能。
5、MySql锁机制
锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，所冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
锁的分类：
从对数据操作的类型分（读/写）
- 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
- 写锁（排它锁）：当前写操作没有完成前，它会阻断其它写锁和读锁。
从对数据操作的粒度分：表锁、行锁
三锁：表锁、行锁、页锁
5.1表锁
特点：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
案例分析：
1、创建SQL
2、加读锁
3、加写锁
4、解除锁
unlock tables;
5.2行锁
事务及其ACID属性
行锁支持事务： 并发事务处理带来的问题：1、更新丢失2、脏读3、不可重复读4、幻读
事务隔离级别
案例分析：
1、建表SQL
2、行锁定基本演示
3、无索引行锁升级为表锁
4、间隙锁危害
5、面试题：常考如何锁定一行
行锁分析：
优化建议：
- 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
- 合理设计索引，尽量缩小索引的范围
- 尽可能减少检索条件，避免间隙锁
- 尽量控制事务大小，减少锁定资源量和时间长度
- 极可能低级别事务隔离
5.3页锁
开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
1601
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
