mysql配置文件
- 二进制日志log-bin：用来主从复制
- 错误日志log-error：默认是关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息等
- 查询日志log：默认关闭，记录查询的sql语句，如果开启会降低mysql的整体性能，因为记录日志是需要消耗系统资源
- 数据文件
- frm文件：存放表结构
- myd文件：存放表数据
- myi文件：存放表索引
mysql逻辑架构
- mysql与其他数据库的不同在于存储引擎的架构上，即图中第三层，插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离。该特点使得开发人员可以根据业务的需求和实际需要选择合适的存储引擎。
- 第一层：连接层，主要完成类似于连接处理、授权认证、及相关的安全方案（提供线程池、可实现基于ssl的安全连接）
- 第二层：服务层，主要完成核心服务功能，完成SQL的分析和优化，提供读写性能。
- 第三层：引擎层，真正负责mysql中数据的存储和提取。根据实际需求去选择存储引擎。
- 第四层：存储层，将数据存储运行在文件系统上（硬件相关）
存储引擎
索引优化分析
- 性能下降SQL慢、执行时间长、等待时间长
- sql写的烂
- 索引失效（单值、复合）
- 关联查询太多join
- 服务器调优和各个参数的配置（缓冲和线程数等）
- 常见的join查询
- sql执行顺序
- 手写顺序
- mysql执行顺序
- 手写顺序
- 7中join查询
- sql执行顺序
索引
-
索引简介
- mysql官方定义：索引是帮助mysql高效获取数据的数据结构。即索引是一种数据结构，而且是一种排好序的快速查找的数据结构（会影响到sql语句的查询和排序）
- 满足特定查找算法的数据结构
- 索引本身也很大，往往以索引文件的形式存储在磁盘上
- 一般索引指的是b+树。其中聚集索引、次要索引、复合索引、前缀索引，唯一索引默认都是使用b+树索引。除了b+树索引以外，还有哈希索引（hash index）
-
优势
- 提高了数据检索成本，降低数据库的IO成本
- 通过索引列对数据进行了排序，降低了数据排序的成本，降低了cpu的消耗
-
劣势
- 索引也是一张表，表保存了主键和索引字段，并指向实体表的记录，索引列也是占空间的
- 索引降低了更新表的速度，因为更新表时，mysql不仅要保存数据，还要保存一下索引文件的每次更新添加了索引列的字段，都会调整因为更新带来的键值变化后的索引信息。
- 如果一张表数据量很大，需要花很多额外的时间成本去建立一套最优的索引
-
索引的分类
-
单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
-
唯一索引：索引列的值必须唯一，但是允许有null值
-
复合索引：一个索引包含多个列
-
基本语法
- 创建
- create [unique] index indexName on mytable(column(length))
- alter mytable add [unique] index [indexName] on (column(length))
- 删除
- drop index indexName on mytable
- 查看
- show index from mytable
- 创建
-
索引结构与索引原理
-
哪些情况需要建索引
- 主键自动创建索引
- 频繁查询的字段要创建索引
- 查询中与其他表关联的字段，外键关系要创建索引
- 频繁更新的字段不适合创建索引
- where条件中用不到的字段不创建索引
- 单键/组合索引的选择？（高并发下倾向创建组合索引）
- 查询中排序的字段，排序字段若通过索引访问将大大提高排序速度
- 查询中统计或分组的字段
-
哪些情况不适合建索引
- 表记录少
- 经常增删改的表
- 如果某个数据列包含许多重复的内容，为他建立索引没有太大的实际效果
-
-
性能分析
- mysql query optimizer
- Mysql中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最有的执行计划（他认为最优的数据检索方式）
- mysql常见瓶颈
- cpu：cpu在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
- IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件的性能瓶颈：top，free，iostat，vmstat查看系统的性能状况
- explain
- 用explain关键字可以模拟优化器去执行sql查询语句，从而知道mysql是如何处理你的sql语句的。分析你的sql查询语句又或者是表结构的性能瓶颈
- 如何用？
- explain + sql
- 包含的信息
- id
- select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
- 三种情况
- id相同，执行顺序由上至下
- id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id相同不同，同时存在时，先执行id大的，遇到相同的则由上至下执行
- select_type
- SIMPLE：简单的select查询，查询不包含子查询或者UNION
- PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为
- SUBQUERY：在SELEFT或者WHERE列表中包含子查询
- DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，外层SELECT被标记为DERIVED
- UNION：若第二个SELECT出现在UNION之后，则被标记为UNION，若UNION包含在FROM子句的子查询中，外层SELECT则被标记为DERIVED
- UNION RESULT：从UNION表中获取的结果的SELECT
- type
- 显示查询用了何种类型
- 从最好到最差依次是：system>const>eq_ref>ref>range>index>all
- system：单表单行
- const：用于比较primary key或者unique索引，只匹配一行数据。如将主键置于where列表中，mysql就能将该查询转化为一个常量
- eq_ref：对于唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引
- ref：非唯一性索引扫描，返回匹配某个单独值的所有行
- range：只检索给定范围的行，使用一个索引来选择行。key列显示使用哪个索引
- index：全索引表扫描（只遍历索引树，索引文件通常比数据文件小，所以比all快）
- all；全表扫描
- 显示查询用了何种类型
- possible_keys
- 显示可能用到的这张表的索引。查询中涉及到字段若存在索引，则该索引被列出，但不一定被查询实际使用到
- key
- 实际使用到的索引，若为null则没有使用索引
- 若查询中使用了覆盖索引（查询的字段和复合索引的字段刚好吻合），则该索引出现在key列表中
- key_len
- 表示索引中使用的字节数，可通过该列计算查询中使用索引的长度，在不损失精确度的情况下，越短越好
- 显示的值是索引字段中最大可能长度，并非实际使用长度，key_len是根据表定义计算而得，不是通过表内检索出的。
- ref -显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或者常量被用于查找索引列上的值
- rows
- 每张表有多少行被优化器查询
- Extra
- 包含不适合在其他列中显示但十分重要的信息
- Using filesort：说明mysql会对数据使用一个外部排序，而不是按照表内的索引顺序进行读取。即无法利用索引完成的排序操作称为“文件排序”
- Using temporary：使用了临时表保存中间结果。mysql在查询结果排序时使用临时表，常见于order by和group by
- Using index：表示select操作使用了覆盖索引，避免了访问表的数据行，效率不错。
- 如果同时出现Using where，表明索引被用来执行索引键值的查找。
- 如没有同时出现using where，表明索引被用来读取数据而非执行查找工作。
- 覆盖索引：查询列被所建的复合索引覆盖（就不用根据索引再去找数据文件啦）
- Using where：使用where进行过滤
- using join buffer：用了连接缓存
- impossible where：where里的条件值原来就是false，没有起到过滤作用
- select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MYISAM存储引擎优化COUNT(*),不必等到执行阶段在进行计算，查询执行计划生成的阶段即完成优化。
- distinct：找到第一匹配值之后停止查找同样值的动作
- 包含不适合在其他列中显示但十分重要的信息
- id
- 使用索引的一些注意点
- 索引失效（mysql会调优，顺序不影响，有用到就会用）
- 推荐全值匹配（建的复合索引依次用到）
- 最佳左前缀法则：如果索引了多列，要遵守左前缀法则。即查询时从索引的最左前列开始，并且不跳过索引中的列
- 不要在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效导致全表扫描
- 范围查询导致失效（推荐使用等值查询）
- 尽量使用覆盖索引（索引列包含查询列，避免select*）
- 使用!=<>导致索引失效（符号后断开（符号当前索引可排序））
- is null，is not null索引失效
- like以通配符开头（‘%abc’），索引失效全表扫描（like当前就不能用了）
- 若需求要用，则使用覆盖索引
- 字符串类型不加单引号会索引失效（类型转换）
- 少用or，用它会导致索引失效(or前就不能用了)
- 当使用group by和order by 要注意排序字段的顺序，要按所建索引来排，否则索引失效。（且注意排序条件是否为定值）
- join的优化
- 左连接时，索引加右表（因左边的一定都有）
- 右连接时，索引加左表（因右边的一定都有）
- 尽可能的减少join语句中nestedLoop的循环次数，永远用小结果集去驱动大结果集，优先优化nestedLoop的内层循环，保证join语句中被驱动表上的条件字段已经被索引，当无法保证保证join语句中被驱动表上的条件字段已经被索引且内存资源充足，可稍微调大joinbuffer
- 一般性优化
- 单键索引，选择针对当前query过滤性更好的索引
- 组合索引，用过滤性最好的索引字段的顺序且越靠前越好，尽可能选择更多包含在索引的字段（且有用）。
- 通过分析统计信息和调整query的写法来达到选择最合适的索引
- 索引失效（mysql会调优，顺序不影响，有用到就会用）
- mysql query optimizer
查询截取分析
查询优化
- 永远小表驱动大表
- 子查询的表大于主查询，使用exists（子查询要写成select 常量 from 子表 where 子表.id=主表.id）,否则使用in，exists返回的是true/false，in返回的是数据表。
- order by优化
-
使用index方式进行排序，避免使用filesort排序
- index效率高，扫描索引本身时完成排序。filesort效率低
- 以下order by情况，index排序
- order by语句使用索引最左前缀匹配
- where子句和order by子句条件列组合满足索引最左前缀匹配
- filesort有两种排序
- 多路排序：mysql4.1之前使用，两次扫描磁盘，最终得到数据。读取行指针和order by列，进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中数据输出
- 单路排序：mysql4.1后，从磁盘中读取所需要的列，按照orderby的列将他们在buffer排序，然后扫描排序后的列表进行输出。效率会快，避免二次读取数据，把随机io变成顺序io，但是使用了更多内存，因为每一行都存在buffer中。
- 但是会有偷鸡不成蚀把米的可能，当sort_buffer小于要取出数据的大小，则要进行多次io并且还要多路合并（创建tmp文件进行多路合并）
- 避免使用select*
- 调大sort_buffer_size、max_length_for_sort_data大小
- 但是会有偷鸡不成蚀把米的可能，当sort_buffer小于要取出数据的大小，则要进行多次io并且还要多路合并（创建tmp文件进行多路合并）
- 总的而言，单路由于多路
-
order by使用索引的一些情况
-
- group by优化
- group by实质是先排序后分组
- where高于having，能在where限定的条件就不要去having限定了
- 其他与orderby一致
慢查询日志
- 默认下，mysql的慢查询日志是关闭的（慢查询日志打开会影响mysql的性能）
- 查看是否开启：show variables like '%slow_query_log%'
- 开启慢查询：set global slow_query_log=1（永久生效要改配置文件，不建议）
- 查看当前多少秒算慢：show variables like '%long_query_time%'
- 设置慢的阈值时间：set global long_query_time=3(重连或新开一个会话才可看到修改值or show global variables like '%slow_query_log%')
- 记录在什么位置：、var/lib/mysql(linux)
- 当前系统有多少条慢查询：show global status like '%slow_queries%'
- 日志分析工具mysqldumpslow
批量插入数据脚本
- 生成随机字符串函数
- 生成随机数
- 插入emp表数据函数
- 插入dept表数据函数
- 调用dept
- 调用emp
show profile
mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可用于sql的调优测量
- 查看当前版本的mysql是否支持：show variables like 'profiling'
- 打开该功能（默认关闭）：set profiling=on;
- 执行了sql语句后可以查询结果：show profiles
- 诊断sql：show profile cpu,block,io for query sql_id;
- 日常出现的问题
全局日志（测试环境用，正式环境禁用）
mysql锁机制
概述
- 分类
- 加锁机制
- 乐观锁：先修改，保存时判断是否被更新过。应用级别
- 悲观锁：先获取锁，在操作修改。数据库级别
- 数据操作
- 读锁（共享锁、s锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响（对应于我们常用的 select * from users where id =1 lock in share mode）
- 写锁（排他锁、x锁）：当前写操作没有完成前，会阻断其他写锁和读锁（select * from users where id =1 for update）
- S锁 和 X锁是可以是表锁，也可以是行锁
- 操作的粒度
- 表锁：开销小，加锁快，粒度大，锁冲突概率大，并发度低，适用于读多写少的情况
- 行锁：Innodb存储引擎，默认选项
- 页锁：BDB存储引擎
- 锁模式
- 记录锁：单行记录的锁，行锁是加在索引上的
- 间隙锁：锁定记录之间的范围，但不包括记录
- Next Key Lock：记录锁+间隙锁，锁定记录之间的范围，且包括记录（左开又闭）
- 意向锁
- innodb为了支持多粒度的锁（表锁行锁）并存，引入意向锁，是表级锁
- IS意向共享锁
- IX意向排他锁
- 事务在请求某一行的S锁和X锁前，需要先获得对应表的IS、IX锁。
- 作用：处理行锁和表锁之间的冲突，用于表明“某个事务正在某一行上持有了锁，或者准备去持有锁”。比如，表中的某一行上加了X锁，就不能对这张表加X锁。
- 如果不在表上加意向锁，对表加锁的时候，都要去检查表中的某一行上是否加有行锁，效率大大降低。
- 插入意向锁（Insert Intention Lock）
- Gap Lock中存在一种插入意向锁，在insert操作时产生。
- 有两个作用：
- 和next-key互斥，阻塞next-key 锁，防止插入数据，这样就不会幻读。
- 插入意向锁互相是兼容的，允许相同间隙、不同数据的并发插入
- 加锁机制
两种读
-
普通读（快照读）：通过MVCC实现，该技术不仅可以保证innodb的可重复读，而且可以防止幻读。但是他读取的数据虽然是一致的，但是数据是历史数据。
- 简单的select操作(不包括 select … lock in share mode, select … for update)
-
当前读：要做到保证数据是一致的，同时读取的数据是最新的数据。, 并且对读取的记录加锁, 阻塞其他事务同时改动相同记录，避免出现安全问题。innodb提供了next-key lock，即gap锁与行锁结合来实现。
- select … lock in share mode、select … for update、insert、update、delete
三锁
- 查看哪些表被锁：show open tables
- 分析系统上的表锁定：show status like 'table%'
- 表锁（偏读）
- 特点：偏向myisam存储引擎，开销小，加锁快，无死锁，锁粒度大，发生锁冲突概率大，并发小
- 进程1对table1加读锁后，进程1不可读除table1以外其他表，不能增删改table1，进程2能读其他表，增删改table1会被阻塞。释放锁后进程2增删改table1执行，进程1可以读其他表
- 进程1对table1加写锁后，进程1不可读写除table1以外其他表，能增删改table1，进程2增删改查table1会被阻塞。释放锁后进程2增删改查table1执行，进程1可以读写其他表
- myisam在执行查询语句时，会给涉及的表加读锁，执行增删改操作时，会自动给涉及的表加写锁
- 读锁会阻塞写，但不会阻塞读，写锁会阻塞读和写
- myisam读写锁调度是写优先，这也是myisam不适合做写为主的表引擎。因写锁后，其他线程不能做任何操作，大量的更新会导致查询很难得到锁，从而造成一直阻塞
- 行锁（偏写）
- 特点：偏向innodb存储引擎，开销大， 加锁慢，会出现死锁，锁粒度小，发送锁冲突的概率小，并发高
- innodb和myisam最大区别：支持事务，采用行级锁
- 行锁支持事务
- 事务及acid属性
- 事务是有一组sql组成的逻辑处理单元
- 原子性（atomicity）：事务是一个原子操作单元，其对数据的修改，要么全部执行，要么全不执行
- 一致性（consistent）：事务开始和完成时，数据必须保持一致状态，这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性，事务结束时，所有的内部数据结构（b树索引或双向链表）也都必须正确。
- 隔离性（isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行，这意味事务处理过程中的中间状态对外部是不可见的。
- 持久性（durable）：事务完成后，他对于数据的修改是永久性的，几时系统故障也能保持
- 事务带来的问题
- 更新丢失：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题--最后的更新覆盖了其他事务的更新（如果一个人没有提交事务之前，另一个人不可访问同一文件即可避免该问题）
- 脏读：事务a读取到事务b已修改但尚未提交的数据，还在这个数据基础上做了操作，此时，如果事务b事务回滚，a读取的数据无效，不符合一致性要求。（一个事务修改了一条数据的值，结果还没提交呢，另外一个事务就读到了你修改的值，然后你回滚了，人家事务再次读，就读不到了，即人家事务读到了你修改之后还没提交的值，这就是脏读了。）
- 不可重复读：事务a开启事务去读取到事务b已提交的修改数据A，事务a还未提交事务，此时事务c再次读取修改并提交数据A，事务a再次读取数据A，此时在一次事务下两次读取数据不一样。（读取到了修改并提交的数据）
- 幻读：幻读就是你一个事务用一样的 SQL 多次查询，结果每次查询都会发现查到一些之前没看到过的数据。（读取到了新增or删除并提交的数据）
- 事务隔离级别(解决事务带来的问题)
-
查看当前数据库的隔离级别：show variables like 'transaction_isolation'
-
已提交读与可重复读是实际开发中最经常使用到的两种事务隔离级别，这两者主要是通过MVCC（Multi Version Concurrency Control）对并发事务问题的解决。
- Read Commited的做法是在事务的每一条SQL语句执行前生成一个快照，此时其他并发事务去读取这个数据时，避免了脏读的出现。
- Repeated Read的做法是在事务的第一次查询前生成一个快照，之后在这一次事务的读取过程中，都去读取这一次快照，从而避免了脏读和不可重复读。
-
可重复读级别未解决幻读（在InnoDB中解决了幻读「间隙锁加行锁」）
-
- 索引失效时，行锁会变表锁
- 间隙锁
- 用范围条件检索数据时，并请求共享或排他锁时，innodb会给符合条件的数据记录的索引项加锁，对于键值再条件范围内但不存在的记录叫做“间隙”；innodb也会给这个间隙加锁，这就是间隙锁。
- 危害：不存在的键值被无辜锁定，造成插入阻塞
- 锁定一行：begin；select xxx for update；commit；
- 分析行锁定：show status like 'innodb_row_lock%';
- 优化建议
- 事务及acid属性
- 页锁：开锁和加锁时间界于表锁和行锁之间，会出现死锁，锁定粒度界于表锁和行锁之间，并发一般
主从复制
- mysql版本一致且后台以服务运行
- 主从配置在mysqld结点下，都是小写
- 以linux为例
- 主机和从机首先需要互相ping通
- 主机修改my.conf配置文件
- 主服务器唯一ID：server-id=1
- 启动二进制日志
- log-bin=mysql路径/mysqlbin
- 启用错误日志（可选）
- log-err=mysql路径/mysqlerr
- 根目录（可选）
- basedir=mysql路径
- 临时目录（可选）
- tmpdir=mysql路径
- 数据目录（可选）
- datadir=mysql路径/Data
- read-only=0
- 读写都可以
- 设置不要复制的数据库（可选）
- binlog-ignore-db=xxx；
- 设置需要复制的数据库（可选）
- binlog-do-db=xxx
- 从机修改my.conf配置文件（开关注释即可）
- 从服务器唯一ID：server-id=2
- 启动二进制日志（可选）
- log-bin=mysql路径/mysqlbin
- 重启mysql服务
- 主从机关闭防火墙
- 主机执行sql：grant replication slave on . to '账号'@'从机ip' identified by '密码'，再执行flush privileges
- 查询master状态
- show master status，记录下file和position
- 完成后，禁止操作主机的mysql
- 查询master状态
- 从机执行sql：change master to master_host='主机ip',master_user='',master_password='',master_log_file='',master_log_pos='';
- 启动从机复制功能：start slave
- show slave status\G
'
- 停止主从复制：在从机执行stop slave
