mysql高级
1、mysql配置文件
1.数据文件：
1.frm文件-存放表结构
2.myd文件-存放表数据
3.myi文件-存放索引
2、mysql与其他数据库的不同点:他的架构可以在不同的场景中发挥良好的作用，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其他系统任务以及数据存储提取相分离。这种架构可以根据业务的需求选择合适的存储引擎
3、mysql引擎对比
1.MyISAM 和 InnoDB的对比
对比项 MyISAM InnoDB
主外键 不支持 支持
事务 不支持 支持
行表锁 表锁，即使操作一条 行锁，操作时只锁住某一行，
记录也会锁住整个表， 不对其他行有影响，适合高
不适合高并发操作 并发操作
缓存 只缓存索引，不缓存真实数据 不仅缓存索引也缓存真实数
真实数据 据，对内存要求较高，而且
内存大小对性能有决定性影响
表空间 小 大
关注点 性能 事务
默认安装 是 是
4、性能下降，SQL执行时间长，等待时间长的原因
1.查询语句写的烂
2.索引失效
3.关联查询太多join
4.服务器调优各个参数设置
5.什么是索引？
1.索引是帮助mysql高效获取数据的数据结构，即排好序的快速查找数据的数据结构
2.在数据库之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
3.为了加快数据的查找，可以维护一个二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录的物理地址的指针，这样就可以运用二叉树在一定的复杂度内获取到相应数据，从而快速查找
4.一般来说，索引本身也很大，不可能全部存储在内存，因此索引往往以文件方式存储在磁盘
5.我们常说的索引，一般指的是b数索引，其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认使用b+树索引，统称索引。除了b+树这种索引外，还有哈希索引等
6.索引的优缺点
优点：
1.类似于图书馆的书索引，提高数据检索效率，降低数据库的IO成本
2.通过索引对数据进行排序，降低数据排序成本，降低CPU消耗
缺点：
1.索引实际也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间
2.虽然索引大大提高了查询速度，但是会降低更新速度，因为更新表，mysql不仅要保存数据，还要保存索引文件每次更新的索引列字段，调整因为更新所带来的键值变化的索引信息
3.如果数据库有大量的表，那么就需要花时间来研究建立最优秀的索引，或者优化查询
7.索引的分类
1.单值索引：一个索引只包含单个列
2.唯一索引：索引列值必须唯一，但允许空值
3.复合索引：一个索引包含多个列
8.基本语法：
1.创建：
1.create 【unique】index indexName on table（col1,col2 ...）
2.create2 alter table add [unique] index [indexname] on (col1 ,col2 ...)
2.删除：drop index [indexname] on table;
3.查看：show index from table;
9.mysql索引结构：
索引原理：
1.Btree索引
2.哈希索引
3.full-text
4.R-tree索引
10.哪些情况下应该创建索引
1.主键自动创建唯一索引
2.频繁作为查询条件的字段应创建索引
3.查询中与其他表关联的字段，外键关系建立索引
4.频繁更新的字段不适合创建索引！
5.where条件中用不到的字段不要创建索引
6.单键组合索引，高并发情况下选择组合
7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
8.查询中统计或者分组字段
11.那些情况不适合做索引（300万性能下降）
1.表的数据量过少
2.经常更新的表
3.数重复且分布均匀的字段
假如一个表10万记录，一个字段只有t和f两种值，且分布为55开，那么对这字段简历索引一般不会
提高检索效率
索引的选择性是指索引列不同值的数目与表记录个数的比。如果一个表2000条记录，有1980个不同
值，那么这个索引的选择性就是1980/2000= 0.99，索引的选择性越接近于1，效率就越高
12、mysql常见瓶颈：
1.cpu:cpu在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
2.IO：磁盘IO瓶颈发生在装入数据远大于内存容量的时候
3.服务器硬件的性能瓶颈：top,free,iostat,和vmstat来查看性能的状态
13、mysql执行计划explain
1.使用explain关键字可以模拟优化器执行SQL查询语句，从而知道mysql是如何处理你的SQL语句的。分析查询
语句或者表结构的性能瓶颈
2.能干嘛：
1.表的读取顺序
2.数据的读取操作类型
3.哪些索引可以使用
4.哪些索引被实际使用
5.表之间的引用
6.每张表有多少行被优化器查询
3.执行计划包含的信息
1.id:select查询的序列号，包含一组数字，表示查询执行select字句或操作表的顺序
1.id相同，执行顺序由上而下
2.id不同，如果是子查询，id的序号会递增，id越大的优先级越高，越先被执行
3.id相同不同，同时存在
2.select_type：查询的种类
1.simple：简单查询select，不包含子查询或者union
2.primary：查询中若包含任何复杂的子查询，最外部的查询被标记为primary
3.subquery:在select或者where中包含了子查询
4.derived:在from列表中包含的子查询被标记为derived(衍生)，mysql会递归执行这些子查询，把结果放入临时表
5.union：若第二个select出现在union之后，则被标记为union，若union出现在from字句的子查询中，则外层select被标记derived
6.union result :从union表中获取结果的select
3.table：关于那张表
4.type：显示查询使用了何种类型：最好到最差依次是：system>const>eq_red>ref>range>index>all,得保证查询至少为range，最好为ref
1.system:表只有一行，相当于系统表，这是const类型，平时不常出现，可以忽略不记
2.const:表示通过索引1次就找到，const用于比较primary key和unique索引。因为只匹配一行数据，所以很快将主键置于where列表中，mysql就能将该查询转化为常量
3.eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于珠江或者唯一索引扫描
4.ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，他返回的是所有匹配的某个单独值的行，然而他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
5.range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引，一般就是where语句中出现了between，<,>in等查询，这种扫描比全表扫描要好，因为他只需要开始于索引的某一点，而结束另外一点，不用扫描全表
6.full index scan,index与all的区别是index只遍历索引树，这通常比all块，因为索引文件通常比数据文件小，即虽然index 和 all都是读全表，但是index从索引读取，而all是硬盘读取
7.all:全表扫描
5.possible_keys:显示 可能应用这张表中的索引，一个或多个。查询涉及到的字段若存在索引，则该索引会被列出，但不一定实际会用
6.key：实际使用的索引，如果为null，则没有使用索引。如果查询中使用了覆盖索引，则该索引仅出现在key列表中
7.key_len：表示索引使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精度的情况下，长度越短越好，key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即Key_len是根据定义计算而得，不是通过表内检索出的
8.ref：显示索引的那一列被使用了，如果可以的话，是一个常数。哪一个列或者常量被用于查找索引列上的值
9.rows：根据表统计信息以及索引选取情况，大致估计算出所需要的记录所需要读取的行数
10.extra：包含不适合在其他列显示但是十分重要的额外信息
1.Using filesort:说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引进行读取，mysql中无法利用索引完成的排序称为文件排序
2.Using temporary:使用了临时表保存中间结果，mysql对查询结果排序时使用临时表，常见于排序order by和分组查询group by
3.Using Index:表示相应的select操作中使用了覆盖索引，避免了访问表的数据行数，效果不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没同时出现，using where，表示索引用来读取数据而非执行查找动作
4.using where:表明使用了where过滤
5.using join buffer ：使用了连接缓存
6.impossible where：where字句中的值总是false，不能来获取任何元组
5.覆盖索引：就是select的数据列只从索引中就能获得，不必读取数据行，mysql可以利用索引返回select列表字段，而不必根据索引再次读取数据文件，换句话说就死查询列要被所建的索引覆盖
14、join语句优化
1.尽可能减少join语句中的嵌套循环的循环总次数，永远用小结果集驱动大结果集
2.优先优化嵌套循环的内存循环
3.保证join语句中被驱动表上join条件字段已经被索引
4.当无法保证被驱动表的join条件字段被索引且内存资源充足的条件下，不要太吝啬joinbuffer的设置
15、索引优化以及如何避免索引失效
1.全值匹配我最爱
2.最佳左前缀法则：如果索引了多列，要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列
3.不在索引列上做任何操作（计算，函数，自动或者手动的类型转换），会导致索引失效而转向全表扫描
4.存储引擎不能使用索引汇总范围条件右边的列
5.尽量使用覆盖索引，减少select *
6.mysql在使用不等于时候无法使用索引会导致全表扫描
7.is null ,is not null 也无法使用索引
8.like 以通配符开头，索引会失效成为区全表扫描，解决办法是使用覆盖索引
9.字符串不加单引号索引失效
10.少用or,用它来连接时会索引失效
16、一般性建议
1.对于单间索引，尽量选择针对当前的query过滤性更好的索引
2.在选择组合索引的时候，当前的query中过滤最好最好的字段在索引字段顺序中，位置越靠前越好
3.在选择组合索引的时候，尽量选择可以包含当期query中where中更多字段的索引
4.尽可能通过分析统计信息和调整query的写法来达到选择适合索引的目的
17、优化口诀
全值匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后全失效
like 百分写最右，覆盖索引不写星
不等空值还有or, 索引失效要少用
var引号不可丢， SQL高级也不难
18、生产SQL优化步骤
1.观察，至少跑1天，看看生产的慢的SQL
2.开启慢查询日志，设置阙值，比如运行超过5秒以上的SQL
3.explain SQL分析
4.show profile
5.dba进行数据库参数调优
19、查询优化
1.永远小表驱动大表，类似嵌套循环
2.order by 关键字优化
1.mysql支持两种方式的排序：filesort和index，index效率高，它指mysql扫描索引本身完成排序。filesort效率低
2.order by 满足两种情况会使用index排序：
1.order by 语句使用索引最左前列
2.使用where子句和orderby子句条件组合满足索引最左前列
3.如果不在索引列上，filesort有两种算法：双路排序，和单路排序
1.双路排序：扫描两次磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
2，单路算法：从磁盘读取查询的所有列，按照orderby列在buffer对他们进行排序，然后扫描排序后的列表进行输出，他的效率更快一些，避免了第二次读取数据。并且把随机IO变成顺序IO，但是他会使用更多的空间，因为他把每一行都保存在内存
4.优化策略：
1.增大sort_buffer_size参数设置
2.增大max_length_for_sort_data参数
3.提高order by 的速度：
1.使用orderby时select * 是大忌，只查询需要的字段，非常重要
4.为排序使用索引
1.mysql两种排序方式：文件排序或者扫描有序索引排序
2.mysql能为排序与查询使用相同的索引
key a_b_c(a,b,c)
1.order by 能使用索引最左前缀
- order by a
-order by a,b
-order by a,b,c
-order by a desc ,b desc c desc
2.如果where使用索引的最左前缀为常量，则order by使用索引
-where a = const order by b,c
-where a = const and b = const order by c
-where a = const order by b,c
-where a = const and b>const order by c
3.不能使用索引的情况
-order by a asc,b desc, c desc #顺序不一致
-where g= const order by b,c #丢失a
-where a= const order by c #丢失b
-where a= const order by a,d #d不是索引
-where a in() order by b,c #对于排序来说多个相等也是范围查询
20、group by优化
1.group by 实质是先排序后进行分组，遵照索引键的最佳左前缀
2.当无法使用索引列，增大max_length_for_sort_data参数设置+增大sort_buffer_size参数
3.where高于hiving,能在写where条件就不要having了
21、慢查询日志分析
1.mysql慢查询日志是mysql提供的一种日志记录，用来记录mysql中响应时间超过闸值的语句，具体指运行时间超过long_query_time的SQL，会被记录在慢查询日志里
2.long_query_time默认值为10s
3.默认mysql没有开启慢查询，需要手动配置参数，如果不是调优需要的话，一般不开启，因为开启慢查询日志或多或少对性能有一定影响。慢查询日志支持写入文件
4.查看慢查询设置：show variables like '%slow_query_log%';
5.设置慢查询日志：set global slow_query_log=1;
6.使用set global slow_query_log=1开启慢查询日志只对当前数据库生效，mysql重启后失效
7.永久生效：修改配置文件my.cnf，在[mysqld]下增加
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
8.查看超时时间：show variables like 'long_query_time%'
9.设置超时时间：set global long_query_time =3假如运行时间正好等于long_query_time的情况，不会被记录，mysql源码里判断是的大于,注意的是需要重开mysql连接才看到变化
22、mysql提供日志分析工具：mysqldumpslow，进行分析日志，查找，分析
1.得到翻会记录最多的10个sql:mysqldumpslow -s r -t 10 /var/lib/mysql/my-slow.log
2.得到访问次数最多的10个SQL：mysqldumpslow -s c -t 10 /var/lib/mysql/my-slow.log
3.按照时间排序的前10条里面含有左连接的查询语句：mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/my-slow.log
4.结合|more 使用 防止爆屏：mysqldumpslow -s r -t 10 /var/lib/mysql/my-slow.log | more
5.mysqldumpslow详细信息：
1.s:按照何种方式排序
2.c:访问次数
3.l:锁定时间
4.r:返回记录
5.t:查询时间
6.al:平均锁定时间
7.ar:平均返回记录数
8.at:平局查询时间
9.t:返回前面多少条记录
10.g:后面接正则匹配，大小写不敏感
23、show profile
1.是什么：是mysql提供的可以用于分析当前语句执行的资源消耗情况。可以用于SQL的调优测量
2.默认：参数关闭，并保存最近15次运行结果
3.分析步骤：
1.是否支持：看看当前mysql版本是否支持
2.开启功能：默认关闭，使用前开启
1.查看参数:show variables like 'profiling';
2.开启：set profiling = on;
3.运行SQL
4.查看结果：show profiles;
5.诊断SQL，show profile cpu,block io for query上一步的问题SQL数字号码；
type:
1.all :显示所有开销
2.block io :显示块io相关开销
3.context switches:上下文切换开销
4.cpu:CPU开销
5.ipc:发送和接受相关开销信息
6.memory：内存相关开销
7.page faults:页面错误相关开销
8.source：显示和source_function,source_file，source_line相关开销
9.swaps：显示交换次数相关开销
主要问题：
1.converting heap to myisam 查询结果太大，内存都不够用了往磁盘上搬
2.creating tmp table 创建临时表：拷贝到临时表；用完再删除
3.copying to tmp table on disk 把内存中临时表复制到磁盘，危险
4.locked
6.日常开发注意
24、全局查询日志:永远不要在开发环境中开启这个功能（略）
25、mysql锁机制
1.锁分类：
1.对数据操作类型分：读写锁
2.对数据操作粒度分：表锁/行锁
2.读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响
3.写锁（排它锁）：当前写操作没有完成，他会阻断其他写锁和读锁
4.表锁：偏向MyisAm存储引擎，开销小，加锁快，无死锁，锁的粒度大，发生锁冲突概率最高，并发度最低。
5.加读锁举例：为mylock表加read锁：lock tables mylock read;
session1:
1.获得表mylock的read锁定
2.当前session可以查询该表的记录
3.当前session不能查询其他没有锁定的表
4.当前session插入或者更新锁定的表都会提示错误
5.释放锁
session2：
1.其他session也可以查询该表的记录
2.其他session可以查询或者更新未锁定的表
3.其他session插入或者更新锁定表会一直等待获得锁
4.session2获得锁，插入成功
6.加写锁举例：lock tables mylock write;
session1:
1.获得mylock表的write锁定
2.当前session对锁定表的查询更新插入都可以执行
session2:
1.其他session对锁定表的查询被阻塞，需要等待锁被释放
26、案例总结：
1.MyISAM执行查询时，会自动给涉及到的所有表加读锁，在执行增删改操作前，会自动给涉及表加写锁，mysql的表级锁有两种模式：表共享读锁，表独占写锁
2.对MyISAM表的读操作，不会阻塞其他进程对同一表读请求，但会阻塞对同一表的写请求。只有读锁释放后，才会执行其他进程的写操作。
3.对MyISAM表的写操作，会阻塞其他进程对同一表的读写操作，只有当写锁释放后，才会执行其他进程的读写操作。
4.简而言之就是读锁会阻塞写，但是不会阻塞读，而写锁会把读和写都阻塞
27、分析表的锁定:show status like 'table%'
1.这两个变量记录mysql内部表锁定的情况，说明如下
2.table_locks_immediate:产生表级锁定的次数，表示可以立即获取所得查询次数，每获得一次锁值+1
3.table_locks_waited:出现表级锁定争用而发生的等待的次数（不能立即获得锁的次数，每等待一次锁值加1），此值越高说明存在较为严重的表级锁争用情况
4.MyISAM的读写锁调度是写优先，这也是MyISAM不适合作为写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永久阻塞
28、行表锁：偏向InNoDB存储引擎，开销大，加锁慢，会出现死锁，锁的粒度小，发生锁冲突的概率最低，并发度最高
1.InNoDB与MyISAM最大不同有两点，一是支持事务，二是采用行级锁
2.索引失效导致行锁升级为表锁
29、事务的特性：ACID
1.原子性：事务是一个原子操作单元，对于数据的修改，要么全部执行，要么全都不执行
2.一致性：在事务的开始和完成时，数据都必须保持一致性，这意味着相关的数据规则都必须应用于事务的修改，以保证数据的完整性，事务结束时所有内部数据也必须是正确的
3.隔离性：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作的影响下独立执行，事务处理过程中的中间状态对外部都是不可见的
4.持久性：事务完成后，对于数据的修改是永久的，即使出了故障也能保持
30、间隙锁的危害
1.定义：当我们用范围条件而不是相等条件检索数据时，并请求共享或排他锁时，InNoDB会给符合条件的已有数据的索引项加锁，对于键值在范围条件内但并不存在的记录，叫做间隙（gap）,innodb也会对这个间隙加锁，这个锁机制就是所谓的间隙锁
2.危害：因为查询过程中通过范围查询的话，会锁定整个范围内的所有索引键值，即使这个键值不存在，间隙锁有个致命的弱点，就是锁定一个范围的键值后，即使某些不存在的键值也会被锁定，而造成锁定的时候无法注入锁定的键值范围内的任何数据，在某些场景下有极大危害。
31、如何锁定某一行？
1.begin;select * from user where id = 9 for update;commit;
32、总结：
1.InNoDB由于实现了行级锁定，虽然在锁定机制实现方面带来的性能损耗可能比表级锁定要高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定，当系统并发较高时，InNoDB性能明显提高
2.但是，InNoDB行级锁也有其脆弱的一面，当使用不当时，可能表现的性能更差
33、如何分析行锁定
1.通过命令：show status like 'innodb_row_lock%';
2.各个状态量的说明：
1.innodb_row_lock_current_waits:当前正在等待锁定的数量；重要！
2.innodb_row_lock_time:从系统启动到现在锁定总时间长度；
3.innodb_row_lock_time_avg:每次等等待所花的平均时间；重要！
4.innodb_row_lock_time_max:从系统到启动到现在等到最长一次所花的时间
5.innodb_row_lock_waits:系统启动到现在总共等待的次数；重要！
3.尤其是等待次数很高，而且每次等待时长不小时，就需要分析系统中为什么有这么多的等待，最后优化
34、优化建议：
1.尽可能让所有数据都通过索引检索完成，避免无索引行锁升级为表锁
2.合理设计索引，尽量减小锁的范围
3.尽可能减少检索条件，避免间隙锁
4.尽量控制事务大小，减少锁定资源量和时间
5.尽可能降低事务隔离级别
35、mysql的主从复制