一、查询语句的生命周期
1.MYSQL服务器监听3306端口
2.验证访问用户
3.创建MySQL线程
4.检查内存（Qcache），当查询命中缓存，MYSQL立刻返回结果，跳过解析、优化、执行阶段。若没有缓存，则继续以下步骤：
5.解析SQL
6.生成查询计划
7.打开表
8.检查内存（Buffer Pool）
9.到磁盘取数据
10.写入内存
11.返回数据给客户端
12.关闭表
13.关闭线程
14.关闭连接
二、MYSQL架构
三、MYSQL连接数
MYSQL默认最大连接数是100，在大并发下连接数会不够用，需要调整最大连接数。
设置MYSQL最大连接数：
Windows:安装目录下的my.ini
Linux:安装目录下的my.cnf
Lampp安装的则在：
/opt/lampp/etc/my.cnf
在MYSQL配置文件中，找到mysqld,添加如下内容：
max_connections = 1000
wait_timeout = 1000000 #超时时间
设置好后，重启MYSQL服务，查看当前有多少个连接： show status like '%Threads_connected%'; show processlist;
四、缓存
查询缓存（QueryCache）
开启了缓存，会自动将查询语句和结果集返回到内存，下次再查直接从内存中取；查询缓存会跟踪系统中每张表，若表发生变化，则和张表相关的所有查询缓存全部失效；检查查询缓存时，MYSQL不会对SQL做任何处理，它精确的使用客户端传来的查询，只要字符大小写或注释有点不同，查询缓存就认为是不同的查询；任何一个包含不确定的函数（比如now（）、curren_date()）的查询不会被缓存。
MYSQL查询缓存可改善性能，但有问题需注意：
开启查询缓存对读写增加了额外开销。对于读，在查询前需先检查缓存；对于写，写入后需更新缓存。一般情况下这些开销相对较小，因此需要根据业务权衡是否开启查询缓存。
Qcache-参数
在ＭＹＳＱＬ配置文件ｍｙ．ｃｎｆ中设置：
1.query_cache_type = on 是否开启查询缓存，具体选项是off，on
2.query_cache_size = 200M 分配给查询缓存的总内存，一般建议不超过256M
3.query_cache_limit = 1M 这个选项限制了MySQL存储的最大结果。如果查询的结果比这个大，那么就不会被缓存。
查询qcache状态：
SHOW VARIABLES LIKE '%query_cache%';
命令参数列表 have_query_cache 该MySQL 是否支持Query Cache；
query_cache_limit 缓存块大小，超过该大小不会被缓存
query_cache_min_res_unit 每个qcache最小的缓存空间大小
query_cache_size 分配给查询缓存的总内存
query_cache_type 是否开启
query_cache_wlock_invalidate 控制当有锁加在表上的时候，是否先让该表相关的 Query Cache失效
监控Qcache使用情况
查询qcache当前使用情况： SHOW STATUS LIKE 'Qcache%';
命令参数列表
Qcache_free_blocks ：Query Cache中目前还有多少剩余的blocks。如果该值显示较大，则说明Query Cache 中的内存碎片较多了，可能需要寻找合适的机会进行整理。 如果这个值非常大，可以使用
FLUSH QUERY CACHE;语句来清理查询缓存碎片以提高内存使用性能。该语句不从缓存中移出任何查询。
Qcache_free_memory： Query Cache 中目前剩余的内存大小
Qcache_hits ：缓存命中次数
Qcache_inserts： 多少次未命中然后插入
Query Cache命中率= Qcache_hits / ( Qcache_hits + Qcache_inserts )；
Qcache_lowmem_prunes： 多少条Query 因为内存不足而被清除出Query
Qcache_not_cached ：因为query_cache_type 的设置或者不能被cache 的Query 的数量
Qcache_queries_in_cache： 当前Query Cache 中cache 的Query 数量；
Qcache_total_blocks： 当前Query Cache 中的block 数量
注：命中率低，说明没从内存中取，还是从磁盘取，则多走了一步。看是否sql老变还是什么问题；
Qcache_not_cached 没有缓存的数据，数值大则说明设置缓存的大小太小了，好多无法缓存。
存储引擎层-innodb buffer pool
buffer pool是innodb存储引擎带的一个缓存池，查询数据的时候，它首先会从内存中查询，如果内存中存在的话，直接返回，从而提高查询响应时间。
innodb buffer pool和qcache的区别是：
qcacche缓存的是sql语句及对应的结果集，buffer pool中缓存的是整张表中的数据。Buffer pool是设置的越大越好，一般设置为服务器物理内存的70%。
Innodb_buffer_pool参数：
Innodb_buffer_pool_size :Innodb_buffer_pool的大小
Innodb_buffer_pool_dump_now:默认为关闭OFF。如果开启该参数，停止MySQL服务时，InnoDB将InnoDB缓冲池中的热数据保存到本地硬盘。
Innodb_buffer_pool_load_at_startup:默认为关闭OFF。如果开启该参数，启动MySQL服务时，MySQL将本地热数据加载到InnoDB缓冲池中。
热数据：经常查询的数据。
查询Innodb_buffer_pool状态：
SHOW VARIABLES LIKE '%innodb_buffer_pool%';
命令参数列表：
innodb_buffer_pool_size： 设置的bp大小
innodb_buffer_pool_filename ：热数据文件名称
innodb_buffer_pool_dump_at_shutdown： 停止mysq服务时是否自动保存热数据
innodb_buffer_pool_dump_now ：启动mysql服务时是否自动读取热数据
监控Innodb_buffer_pool使用情况：
SHOW STATUS LIKE '%Innodb_buffer_pool%';
命令参数列表 ：
主要关注的两个参数：
Innodb_buffer_pool_read_requests 总共查询bp的次数
Innodb_buffer_pool_reads 从物理磁盘中获取到数据的次数 通过这两个参数我们可以知道bp的命中率
安装图形化监控工具lepus，可以方便快捷的监控ＭＹＳＱＬ使用情况
MYSQL主从复制
MYSQL复制用途：
分布数据：MySQL通常不会对带宽造成很大的压力。因此可以在不同的地理位置来分布数据，实现跨机房跨地域的数据分布。
负载均衡：通过MySQL复制可以将读操作分布到多个服务器上，实现对读密集型应用的优化。
备份： 复制对备份很有帮助，但是从服务器并不是备份。
高可用性和故障转移： 复制可以避免在应用程序中出现MySQL失效。好癿故隓转移能显著的减少停机时间，甚至让用户无感知。
测试MySQL版本升级： 一个常见的方法是先把从服务器升级到MySQL新版本，然后用它来测试查询，确保无异常后再升级主服务器。
案例：修改了XX信息，提示修改成功，再查看还是旧数据，是怎么回事？
MySQL复制小结：
3个线程完成复制：
主库1个线程负责记录数据库变更日志
从库1个线程负责拉取主库的变更日志
从库1个线程负责执行主库的变更日志
实现了获取事件和重放事件的解耦，允许异步进行。
复制的瓶颈：主库并行(多线程)写入和从库串行（单线程）写入，会造成主从延迟。
MYSQL单表数据超过500W后，读写速度很慢，需要拆库分表进行优化。
数据id为8，8%2 余为0 则分布在第一台数据库上；数据id为9,9%2 余1 则分布在第二台数据库上，以此类推进行拆库分表。
MySQL主从延时延时问题如何处理?
1.偶发性延时： 控制写入速度，削峰填谷。（比如刷微博时，操作台频繁会提示稍后操作之类的就是控制写入速度。）
2.频发性延时： 拆分数据库实现多点写入
最后一招：从库磁盘硬件升级为ssd（SSD为固态硬盘，速度很快）
慢查询：
记录哪些sql执行的慢，也就是我们常说的slowquery，通过设--log-slow-queries[=file_name]来打开该功能并设置记录位置和文件名。
配置MYSQL慢查询（配置文件）：
Linux: 在mysql配置文件my.cnf中增加：
log-slow-queries=/opt/data/slowquery.log (指定日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log)
long_query_time=0.001 (记录超过的时间，默认为10s)
log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启)
Windows: 在my.ini的[mysqld]添加如下语句：
log-slow-queries = E:\web\mysql\log\mysqlslowquery.log
long_query_time = 2(其他参数如上)
配置MYSQL慢查询（执行命令）
set global slow_query_log=on;
set global long_query_time=1;#设置记录查询超过多长时间的sql
set global slow_query_log_file=‘/opt/data/slow_query.log’;#设置mysql慢查询日志路径，此路径需要有写权限 这种方式不用重启mysql服务。
配置好后，执行下一sql语句，则会记录对应sql日志，查看日志 tail -f 日志路径
解析慢查询日志
使用mysqldumpslow命令可以解析mysql慢查询日志。 Mysqldumpslow命令参数如下：
-s，是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
-t，是top n的意思，即为返回前面多少条的数据；
-g，后边可以写一个正则匹配模式，大小写不敏感的；
mysqldumpslow -s t -t 20 -g 'select' /opt/lampp/var/mysql/主机名-slow.log
若出下图问题则yum安装下perl（若yum安装不了，则vi 下/opt/lampp/bin/mysqldumpslow,将文件顶端的/usr/local/perl改成/opt/lampp/bin/perl，则不用yum了，直接用lampp下集成的perl）
mysqldumpslow命令结果：
explain
该命令是查看查询优化器如何决定执行查询的主要方法，这个功能有局限性，只是一个近似结果.
explain中的列
ID列 这一列就是1个编号。如果语句中没有子查询或联合查询，那么每一行都会显示1.否则内层的select语句会顺序编号。 id如果相同，可以认为是一组，从上往下顺序执行，这个顺序不总是与其原始SQL中的相一致；在所有组中，id值越大，优先级越高，越先执行
explain select * from students where id in (select id from students);
explain select * from (select * from students) as s;
explain中的列--select_type
select列,它有以下几种值:
simple 它表示简单的select,没有union和子查询.
primary 最外面的select,在有子查询的语句中，最外面的select查询就是primary
DERIVED 值表示包含在FROM字句的子查询中的SELECT，MySQL会递归执行并将结果放到一个临时表中。服务器内部称其“派生表”，因为该临时表是从子查询中派生来的
UNION union语句的第二个或者说是后面那一个.
explain中的列--type列
指MySQL的访问类型，也就是如何查找表中的行，下面是最重要的访问方法，依次从最差到最优。
all<index<range<ref<eq_ref<const,system<null
Key列 这一列显示了MySQL决定采用哪个索引来优化对该表的访问。如果该索引没有出现在possible_keys中，那么MySQL选用它是出于另外的原因，比如覆盖索引。
Key_len列： 该列显示了MySQL在索引里使用的字节数。
Ref列： 显示索引的哪一列被使用了
Rows列 MYSQL 认为必须检查的用来返回请求数据的行数
可以查看sql执行效率高低、有无索引、语句返回数据行数。
MYSQL profile
分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等。
开启 profile： set @@profiling=1;
查看profile是否开启 select @@profiling; 1为开启，0为关闭 开启后只对当前session有效。
如何使用profile
开启profile后，执行的sql都会被profile记录 show profiles;可以看到当前已经被记录的sql
查看profile结果
show profile for query n;#n为show profile中的query_id
show profile可以看到sql执行计划中每步的执行时间，以及cpu、内存、io的消耗
show profile cpu for query 1;#查看cpu消耗
show profile block io for query 1;#查看io消耗
show profile memory for query 1;#查看cpu
也可以一起写 show profile cpu ,block io for query 1;
SQL优化
SQL优化目标
1.减少 IO 次数 减少 IO 次数是 SQL 优化中需要第一优先考虑，当然，也是收效最明显的优化手段。
2.降低 CPU 计算 除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标。
SQL优化方法
改变SQL执行计划，让他尽量“少走弯路”、“尽量通过各种捷径”来找到需要的数据，达到减少IO次数和降低CPU计算目标。
SQL优化基本原则
尽量少join
尽量少排序 对于mysql来说，减少排序有多种方法：
通过利用索引来排序的方式进行优化；减少参与排序的记录条数 ；非必要不对数据进行排序
尽量避免select *
Select * 一般都会造成全表扫描，显示所有列，select 需要的字段即可。 尽量用 join 代替子查询 虽然 Join 性能并不佳，但是和 MySQL 的子查询比起来还是有非常大的性能优势。MySQL 的子查询执行计划一直存在较大的问题，虽然这个问题已经存在多年，但是到目前已经发布的所有稳定版本中都普遍存在，一直没有太大改善。虽然官方也在很早就承认这一问题，并且承诺尽快解决，但是至少到目前为止我们还没有看到哪一个版本较好的解决了这一问题。
尽量用 union all 代替 union： union 和 union all 的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU 运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用 union all 而不是 union。
禁用外键
避免大sql
1）一个SQL只能在一个cpu上运行 2）高并发环境中，大SQL容易影响性能问题 3）可能一个大SQL把数据库搞死 4）拆分SQL
保持事物的短小精悍
1）即开即用，用完即关 2）无关操作踢出事务，减少资源占用 3）保持一致性的前提下，拆分事务
避免大批量更新
避开高峰 1）白天限制速度 2）加sleep
避免类型转换
避免取过量数据，建议使用limit
避免在SQL 语句中进行数学运算、函数计算、逻辑判断等操作
避免OR 1）同一字段，推荐in 2）不同字段，推荐union
优先优化高并发的 SQL，而不是执行频率低某些“大”SQL 从全局出发优化，而不是片面调整 尽可能对每一条运行在数据库中的SQL进行explain。
SQL优化实例--业务逻辑上优化
优化要从整个业务逻辑上进行，针对数据库问题优化，首先要考虑不查或少查数据库。
若查询不可避免，可以考虑下面两种方式：
1.避免磁盘IO，让查询在内存中完成； 2.通过sql和 索引的调整，让mysql更高效率的方式查询。
SQL优化实例--索引设计原则
最适合索引的列是出现在where子句中的列，或连接子句中指定的列，而不是select字后选择列表中的列；
使用唯一索引：使用唯一索引的列，索引效果最好，有多个重复值的列，索引效果最差 ；
使用短索引；利用最左前缀；不要过度索引。
事务
事务的四个特性：原子性：指整个数据库事务是不可分割的工作单位。只有使事务中的所有数据库操作执行都成功，才算整个事务成功。如果任何一个SQL语句执行失败，那么已经执行成功的SQL语句也必须撤销，数据库状态退回到执行事务前的状态；
一致性：指事务将数据库从一种状态转变为下一种一致的状态。在事务开始之前和事务结束之后，数据库的完整性约束没有被破坏；
隔离性：一个事务的影响在该事务提交前对其他事务都不可见，这通过锁（lock）来实现；
持久性：事务一旦提交，其结果就是永久性的。即使发生宕机等故障，数据库也能将数据恢复。
锁
锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问，一方面最大程度的提供并发访问，另一方面要确保每个用户能以一致性的方式读取和修改数据。
InnoDB存储引擎会在行级别上对表数据加锁。
对应MyISAM存储引擎来说，其锁是表锁。并发情况下的读没有问题，但是并发写入性能比较差。 InnoDB存储引擎是行锁，提供一致性的非锁定读。它的行级锁没有开销，可以同时得到并发性和一致性。
关闭自动提交
查看自动提交是否开启 select @@autocommit;#0为关闭，1为开启 设置自动提交关闭 set @autocommit = 0;#这种方式只对当前session有效 修改配置文件在[mysqld]模块下加入autocommit = 0;#重启后永久生效
lock的对象是事务，用来锁定的是数据库中的对象，如表、行。并且一般lock的对象仅在事务commit或者rollback后释放。 特点：InnoDB是通过对索引上的索引项加锁来实现行锁。这种特点也就意味着，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。
锁 对索引列操作的话，是行级锁；对非索引列操作的时候，就是表级锁。
查看是否死锁：
show engine innodb status\G；（一般日志里有dblock、lock等字样） SELECT * FROM information_schema.INNODB_TRX\G （定位哪个线程导致死锁）
死锁
学习加锁分析，作用有二： To 研发和测试：可以根据MySQL的加锁规则，写出或测出不会发生死锁的SQL； To dba：可以根据MySQL的加锁规则，定位出线上产生死锁的原因。
小结： 多线程并发才有可能死锁 ；避免交叉加锁 ；减少涉及的表，表联接会大大增加锁范围； 避免全表更新 ；控制更新行数
alias造命令 永久生效
在根目录下，找到.bashrc文件进行设置。[1)cd ~ 2)ls -a 找到隐藏的.bashrc文件]
whereis +某命令 可以查找某个命令所在目录