mysql优化

rm mysql-community-server-minimal-5.7.20-1.el7.x86_64.rpm
yum install -y mysql-community-*rpm
setenforce 0
vi /etc/sysconfig/selinux
getenforce  返回 Permissive
mysql_install_db --datadir=/var/lib/mysql --user=mysql

vi /etc/my.cnf

[root@localhost MYSQL]# systemctl start
Too few arguments.

systemctl start mysqld

//启动mysql
mysql -p -uroot -p

复制表结构+复制表数据
create table t2 like t1;
insert into t3 select * from t1;

chkconfig mysql on        开机自启动
ps -ef|grep mysql  安装目录

=================================================================================================

frm文件：存放表结构
MYD文件：存放表数据
MYI：存放表索引

查看mysql现在提供的存储引擎
show engines;
查看mysql当前默认的存储引擎
show variables like ‘%storage_engine%’

sql慢的原因：
    查询语句写的烂
    索引失效
        单值索引：用该表的某一个字段建的索引
        复合索引
    关联查询太多join
    服务器调优及哥哥参数设置    

索引的优势：
    提供数据检索的效率，降低数据库IO成本
    通过索引列对数据进行排序，降低数据排序成本，降低CPU消耗
索引的劣势：
    索引是一张表，每次增删改，mysql不仅要保存数据，还要保存索引，就会降低更新的速度
    数据量大的时候，需要花大量的时间研究建立优秀的索引

四种方式添加索引：
    alter table tbl_name add primary key(column_list);添加一个主键，意味着索引值是唯一的，且不能为null
    alter table tbl_name add unique index_name(column_list);创建索引的值必须是唯一的（除了null外，null可能会出现多次）
    alter table tbl_name add index index_name(column_list);添加普通索引，索引值可出现多次
    alter table tbl_name add fulltext index_name(column_list);指定索引为fulltext，用于全文索引

哪些情况需要创建索引：
    1.主键字段建立的唯一索引
    2.频繁作为查询条件的字段应该创建索引
    3.外键关系建立索引
      4.在高并发下倾向创建组合索引
    5.查询中排序的字段，通过索引访问将大大提高排序速度
    6.查询中统计或者分组字段

哪些情况不需要创建索引：
    1.表记录太少（300万的数据以上）
    2.经常增删改的表
    3.数据重复且分布平均的表字段
    4.范围的数据比如> < ，会导致索引失效

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

explain    使用explain关键字可以模拟优化器执行申请了查询语句，从而知道mysql是如何处理你的sql语句，分析你的查询语句或者表结构的性能瓶颈
写法 explain+sql语句

id    select_type     table    type    possible_key    key_len        ref  rows Extra    

id：   
    select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
    三种情况：
        id相同，执行顺序由上至下
        id不同，如果查询子句，id的序号会递增，id值越大优先级越高，越先被执行
        id相同和不同同时存在，参照前两种情况

select_type：
    simple：简单的select查询，查询中不包含子查询或者union
    primary    查询中若包含任何复杂的子部分，最外层查询则被标记为primary
    subquery 在select或where列表中包含了子查询
    derived    在from列表中保留的子查询标记为derived（衍生） mysql会递归执行这些子查询，把结果放在临时表里
    union 若第二个select出现在union之后，则被标记为union，；若union包含在from子句的子查询中，外层select将被标记为derived
    union result  从union表获取结果的select
table：显示这一行的数据是关于哪张表的

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

possible_key:显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将别列出，但不一定被查询实际使用

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），避免访问了表的数据行，效率不错！如果同时出现usering where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作
    Using where  使用where过滤
    using join buffer 使用了连接缓存
    impossible where：  where子句的值总是false，不能用来获取任何元组

join语句的优化
    尽可能减少join语句中的nestedLoop的循环次数“永远用小结果集驱动大结果集”

索引优化（索引失效）：
    1.全值匹配
    2.最佳左前缀法则（如果索引了多列，查询从索引的最左前列开始，并且不跳过索引中的列）
    3.不在索引列做任何操作
    4.存储引擎不能使用索引中范围条件右边的列
    5.尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致））
    6.mysql在使用不等于的时候无法使用索引，导致全表扫描
    7.is null,is not null 也无法使用索引
    8.like以通配符开通（‘%abd..’）mysql索引失效会变成全表扫描；如果非要是%号，可以用覆盖索引解决这个问题，即查询字段全部建了索引，顺序跟复合索引一致
    9.字符串不加单引号，索引失效
    10.少用or，用它来连接会索引失效

查询截取分析
    1.观察，至少跑一天，看看生产的慢SQL情况
    2.开启慢查询日志，设置阀值，比如超过5秒钟就是慢SQL，并抓取出来
    3.explain+慢SQL分析
    4.show profile查询SQL在mysql服务器里面的执行细节和生命周期情况
    5.运维经理 or DBA，进行SQL数据库服务的参数调优

当B表数据集必须小于A表数据集时，用in优于exists    
当A表数据集必须小于B表数据集时，用exists优于in

提高order by速度
    1.Order by 时 select* 是一个大忌只query需要的字段，这点非常重要，在这里的影响是：
        1.1 当query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法--单路排序，否则用老算法--多路排序
        1.2 两种算法的数据都有可能超过sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size.
    2.尝试提高sort_buffer_size
    不管用哪种算法，听这个参数都会提高效率，当然，要根据系统的能力入提高，因为这个参数是针对每个进程的
    3.尝试提高max_length_for_sort_data
        提高这个参数，会增加用改进算法的概率，但是如果设的太高，数据总容量超过sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率

mysql的慢查询日志是mysql提供的一种日志记录，它用来记录在mysql中响应时超过阀值的语句，具体指运行时间超过long_query_time的sql，则会被记录到忙查询日志中
具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time默认值为10，意思是运行10秒以上的语句
由他来查看哪些sql超过了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的SQL，结合之前的explain进行全面分析

默认情况下，没有开启慢查询日志，需要我们手动设置，如果不是调优需要的话，一般不建议启动改参数，因为开启会影响性能

开启：默认show variables like ‘%slow_query_log’
    开启 set global slow_query_log=1;

开启profiling
    show variables like 'profiling'
    set profiling=on；
    查看结果 show profiles；
    诊断sql show profiles cpu,block io for query 上一步数字号码；    
    永远不要在生产环境开启这个功能

锁的分类
    对数据操作类型（读\写）
    对数据操作的粒度（表锁，行锁）    

三锁： 
    表锁（偏读）：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突概率最高，并发度最低
    行锁（偏写）
    页锁