MySQL优化

1.mysq 数据优化可以从以下几方面进行优化：
  1、sql及索引优化。 
  2 数据库表结构，根据数据设计查询最优的表结构。
  3，系统配置优化，对打开文件数和安全的限制。 
  4、硬件，选择最适合数据库的cpu，更快的IO,更大的内存，cpu不是越多越好， IO并不能减少锁的机制，也就是不能减少阻塞，所以说硬件的优化成本越高，效果最差。

2:  演示数据
  下载演示数据库网址：https://dev.mysql.com/doc/index-other.html
  查看数据库表结构网址：https://dev.mysql.com/doc/sakila/en/sakila-installation.html
  2.1登录下载数据库网址，下载sakila database的zip包，解压出现三个文件：sakila-data.sql、sakila-schema.sql、sakila.mwb
  2.2 创建数据库并插入数据
    shell> mysql -u root -p
    mysql> SOURCE C:/temp/sakila-db/sakila-schema.sql;
    mysql> SOURCE C:/temp/sakila-db/sakila-data.sql;
    mysql> USE sakila;
    mysql> SHOW TABLES;
    mysql> SELECT COUNT(*) FROM film;
    mysql> SELECT COUNT(*) FROM film_text;
3：SQL及索引优化
  1、show_query_log_file：慢查日志存储位置
  2、log_queries_not_use_indexes：是否把没有使用索引的查询记录到慢查询日志中
  3、long_query_time：超过多少秒的查询记录到慢查询日志中
    使用MySQL慢查日志对有效率问题的SQL进行监控
    show variables like 'slow_query_log';    //查看是否开启慢查日志
    命令行：
      show variables like 'slow_query_log';--查看开启状态
        set global slow_query_log=on;
      show variables like '%log%'; -- 看log_queries_not_using_indexes | OFF 这条记录
      --显示log_queries_not_using_indexes      off  没有使用索引的日志
        set global log_queries_not_using_indexes=on;
      show variables like 'long_query_time';--查询时间
        set  long_query_time=0;
      -- 查看慢日志具体目录
      show variables like 'slow%';
     进入慢日志文件：
      1）退出mysql模式，
      2)#> tail -50 /usr/local/var/mysql/Hadess-slow.log
      3）日志显示内容
        --执行SQL的内容
        SELECT * FROM store LIMIT 10;
        # Time: 2019-03-27T02:36:31.678927Z
        -- 执行SQL的主机信息
        # User@Host: root[root] @ localhost [127.0.0.1]  Id:     2
        -- 执行SQL的信息
        # Query_time(查询时间): 0.002786  Lock_time(锁定时间): 0.000148 Rows_sent(发送的行数): 3  Rows_examined(扫描的行数): 1032
        -- 执行SQL的时间
        SET timestamp=1553654191;
4：工具
  慢查询日志分析工具:1)mysqldumpslow ,2)pt-query-digest
  1.mysqldumpslow(mysql官方自带的,安装的时候就带有)
  2.mysqldumpslow -t 3 /home/mysql/data/mysql-slow.log  | more(输出排名前三的查询慢的日志)
  3.-s (可以规定慢查询日志以什么方式排序展示)
  #> mysqldumpslow -t 3 /usr/local/var/mysql/Hadess-slow.log | more
  
  Reading mysql slow query log from /usr/local/var/mysql/Hadess-slow.log
    Count: 1  Time=0.02s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  set global slow_query_log=on
    Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=2.0 (4), root[root]@localhost
  show variables like 'S'
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=23.0 (23), root[root]@localhost
   show tables

5:通过explain查询和分析SQL的执行计划
  -- example https://blog.csdn.net/why15732625998/article/details/80388236
  -- example https://www.cnblogs.com/galengao/p/5780958.html
  explain返回各列的含义
    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
    table:显示这一行的数据是关于哪张表的
    type:
      从最好到最差依次是：system > const > eq_ref > ref > range > index > all
      这是重要的列，显示连接使用了何种类型。从最好的到最差的连接类型为const/eq_reg/ref/range/index和ALL
      1)system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
      2)const 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
      3)eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
      4)ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，
          然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
      5)range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
      6)index Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。
        （也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
      7)Full Table Scan 将遍历全表以找到匹配的行 
      
    possiable_keys:显示可能应用在这张表中的索引。如果为空，没有可能的索引。
    key:实际使用的索引，如果为null，则没有使用索引
    key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。
            key_len显示的值为索引字段的最大可能长度，并         非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
    ref：显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。 
    rows:根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好 
    Extra:包含不适合在其他列中显式但十分重要的额外信息
      1)Using filesort（九死一生）
        说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
        MySQL中无法利用索引完成的排序操作称为“文件排序”。 
      2)Using temporary（十死无生）
        使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
      3)Using index（发财了）
        表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
      4)Using where
        表明使用了where过滤
      5)Using join buffer
        表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
      6)impossible where
        where子句的值总是false，不能用来获取任何元组
      7)select tables optimized away
        在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
      8)distinct
        优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作
    filtered:
      如果你用EXPLAIN EXTENDED将会展示出这列filtered(MySQL5.7缺省就会输出filtered)，它指返回结果的行占需要读到的行(rows列的值)的百分比。按说filtered是个非常有用的值，因为对于join操作，前一个表的结果集大小直接影响了循环的次数。但是我的环境下测试的结果却是，filtered的值一直是100%，也就是说失去了意义。
  
6：具体sql语句优化
  1）Count和Max的优化
    Max的优化：针对max使用的字段设置索引；
    -- 创建索引
    CREATE INDEX idx_paydate on payment(payment_date);
    COUNT的优化：
      SELECT COUNT(release_year = '2006' OR NULL) AS A,
      COUNT(release_year = '2007' OR NULL) AS FROM film;
  2）子查询的优化
    通常情况下，需要把子查询优化为join查询。
    子查询优化 改为 join 连接查询时，关注两表之间 是否是一对多的关系，若有，则需要去重distinct。
    用连接的方式进行查询可能会导致数据重复,
    如:select t.id from t join t1 on t.id = t1.tid ,
    但是通过子查询就只有一条数据了:select * from t where t.id in (select ti.tid from ti);可以通过distinct去重;
  3）Group by的优化
    -- 优化之前的GROUP BY
    EXPLAIN
    SELECT actor.first_name,actor.last_name,COUNT(*)AS num
    FROM film_actor
    INNER JOIN actor USING(actor_id)
    GROUP BY film_actor.actor_id;
    -- 优化之后的GROUP BY
    EXPLAIN
    SELECT actor.first_name,actor.last_name,c.ctn
    FROM actor
    INNER JOIN
      (SELECT actor_id,COUNT(*)AS ctn 
	      FROM film_actor 
	      GROUP BY film_actor.actor_id)
    AS C USING(actor_id);
    4）limit的优化
      limit会使用Filesorts这样造成大量的IO问题;
      例如:select  film_id , description from salika.film order by title limit 500,5;
      分析:当limit 后面的数据越大,IO越高,性能越差;可以使用主键排序进行优化,避免过多的扫描;
      优化:select film_id , description from sakila.film where film_id >=500 and film_id <= 505 order by film_id limit 1,5;
      优化limit查询
        优化步骤1:使用有索引的列或这件进行order by操作
          select film_id ,description from sakila.film order by film_id limit 50,5;
        优化步骤2:记录上次返回的主键,在下次查询时用 主键过滤(避免了数据量大时扫描过多的记录)
          select film_id ,description from sakila.film where film_id >55 and film_id<=60 order by film_id
    5) 索引Index的优化
      索引的优点：
        提高查询的效率：select
      索引的缺点：
        降低写入的效率：insert、update、delete
      索引存在的目的是为了加快查询的效率，不过不是索引越多越好，建立索引要适当才好。
      5.1）选择合适的索引列
        5.1.1）在where，group by，order by，on从句中出现的列
        5.1.2）索引字段越小越好(因为数据库的存储单位是页，一页中能存下的数据越多越好 )；
        5.1.3）离散度大得列放在联合索引前面
          select count(distinct customer_id), count(distinct staff_id) from payment;
          查看离散度 通过统计不同的列值来实现 count越大 离散程度越高
          select count(distinct customer_id), count(distinct staff_id) from payment;
          是index(sftaff_id,customer_id)好？还是index(customer_id,staff_id)好呢？
          由于customer_id的离散度更大，所以应该采用ndex(customer_id,staff_id)
          计算离散度：
            SELECT COUNT(DISTINCT customer_id),COUNT(DISTINCT staff_id) FROM payment;
      5.2）索引的维护及优化
        5.2.1) 重复索引和冗余索引
          重复索引是指相同的列已以相同的的顺序建立的同类索引，类似：
            CREATE TABLE test(
              id INT NOT NULL PRIMARY KEY,
              name VARCHAR(20) NOT NULL,
              UNIQUE(ID)
            )ENGINE=INNODB;
            其中id就是建立了重复的索引。
          -- 查找重复及冗余索引
          USE information_schema;
          DESC STATISTICS;
          SELECT * FROM STATISTICS;

          SELECT a.TABLE_SCHEMA AS '数据名'
	              ,a.TABLE_NAME AS '表名'
	               ,a.INDEX_NAME AS '索引1'
	              ,b.INDEX_NAME AS '索引2'
	        FROM STATISTICS AS a 
	        JOIN STATISTICS AS b
	        ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
	        AND a.TABLE_NAME = b.TABLE_NAME
	        AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX
	        AND a.COLUMN_NAME = b.COLUMN_NAME
	        WHERE a.SEQ_IN_INDEX =1  AND a.INDEX_NAME<>b.INDEX_NAME;
	     5.2.2)工具检查重复的索引
	        pt-duplicate-key-checker工具检查重复的索引,然后删除重复的索引
       5.2.3)索引的维护及优化
          mysql中只能通过分析慢查日志配置pt-index-usage工具进行索引使用情况的分析,看哪些索引不常用,从而删除;
      6) MySQL数据库优化
        6.1)选择合适的数据类型
          6.1.1)使用可存下数据的最小的数据类型
          6.1.2)使用简单地数据类型，Int<varchar
          6.1.3)尽可能使用not null定义字段
          6.1.4)尽量少用text，非用不可最好分表
          6.1.5)MySQL函数两个日期转换函数
            FROM_UNIXTIME()将时间戳转成日期时间格式
            UNIX_TIMESTAMP() 将日期时间转成时间戳
          6.1.6)example
            用 not null 定义字段,因为定义null的字段会采用额外的字段去存储,io开销大;
            尽量不使用text类型,使用简单而小的数据类型;
            时间类型可以存储int类型(占用内存小)利用from_unixtime()(时间戳转时间)和unix_timestamp()(时间转时间戳)函数进行转化
            IP地址使用使用bigint存储,利用inet_aton(),inet_ntoa()两个函数转换(inet_aton()是将一个字符串IP地址转换为一个32位的网络序列IP地址,inet_ntoa()相反)
        6.2)数据库表的范式优化
          表的范式化即数据库设计的规范化：数据表不存在非关键字段对任意关键字段的传递函数依赖，则符合第三范式。
          可以将一张数据表进行拆分，来满足第三范式的要求。
          设计表的时候符合范式化是为了：减少数据冗余、减少表的插入、更新、删除异常
          设计表的时候使用反范式化是为了：以空间换时间、增强代码的可编程性和可维护性
        6.2)数据库反范式化
          数据库反范式化:符合第三范式基础上适当增加冗余,已达到查询优化的目的;
        6.3)表垂直拆分
          6.3.1)不经常用得放在同一个表中  
          6.3.2)经常用得放在同一个表中
          6.3.3)大的字段单独放在一个表中
        6.4)表的水平拆分
          主要是解决数据量过大的问题,水平拆分每个表的表结构都是完全一致的(当单表的数据大于一亿时,尽管加了索引,还是会比较慢);
          水平拆分方法:对id进行hash运算(取模),如果拆分为5个表,则使用mod(id)取出0-4个值,针对不同的hashID吧数据存到不同的表中;
      7) 系统配置优化
        7.1）linux系统为例
          1. 网络方面，修改/etc/sysctl.conf文件，增加tcp支持的队列数，减少断开连接时，资源的回收。
          2. 打开文件数的限制。修改/etc/security/limits.conf文件，增加一下内容以修改打开文件数量的限制。
          3. 关闭iptables，selinux等防火墙软件。
        7.2）mysql系统配置
          /usr/sbin/mysqld - -verbose - -help | grep -A 1 'Default options '
          查看MySQL配置文件路径，如果存在多个配置文件，则后面的会覆盖前面的。
          innodb_buffer_pool_size   #重要，缓冲池的大小 推荐总内存量的75%，越大越好。
          innodb_buffer_pool_instances   #该参数可以控制缓冲池的个数，默认只有一个缓冲池，
            如果一个缓冲池中并发量过大，容易阻塞，此时可以分为多个缓冲池；
          SQL计算内存大小：
          select engine,
            round(sum(data_length+index_length)/1024/1024,1) as "Total MB"
          FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN 
          ("inoformation_schema","performance_schema")
          Group by engine;
        7.3)工具配置 
            http://tools.percona.com/wizard
        7.4）服务器硬件优化
          1.cpu选择:mysql一个sql的执行只能用到单核的cpu,其次,在复制进程的时候也是只能用到单核的cpu,
            所以cpu并不是越多越好,mysql5.5是的服务器不要超过32核,偏向选择单核频率更快的cpu;
          2.Disk IO 优化，常用的RAID 级别简介