MySql高级

1.MySql存储引擎

       查看存储引擎:   show engines;

       show variables like '%storage_engines%';

2.MySql常用存储引擎对比(MyISAM和InnoDB)

    MyISAM:    不支持外键；    不支持事务；     表锁，不适合高并发；      关注读的性能，偏读。

    InnoDB:      支持外键；       支持事务    ；     行锁，适合高并发   ；       关注事务，偏写。

   缓存方面：  MyISAM只缓存索引，不缓存数据。

                       InnoDB索引和数据都缓存，对内存要求比MyISAM高，而且内存大小对性能有绝对性影响。

   其他偏门：  xtradb存储引擎完全可以替代InnoDB，并且在性能和并发上做的更好。

3.索引优化

   sql慢的表现：sql执行时间长或等待时间长。  

  编程可控制的：别写烂sql，尽量使用到现有的索引，尽量避免多表join查询。

   分类：单值索引，复合索引

4.SQL执行顺序

手写：

SELECT <select-list> FROM <left-table> <join-type> JOIN <right-table> ON <join-condition>

WHERE <where-condition> GROUP BY <group-by-list> HAVING <having-condition> ORDER BY <order-by-condition>        LIMIT <limit-number>

机读顺序:

    1. FROM <left-table>

    2. ON <join-condition>

    3. <join-type> JOIN <right-table>

    4. WHERE <where-condition>

    5. GROUP BY <group-by-list>

    6. HAVING <having-condition>

    7. SELECT

    8. DISTINCT <select-list>

    9. GROUP BY <group-by-list>

    10. LIMIT <limit-number>

 5.JOIN全部方式

        左连接，右连接，内连接，交叉连接，全连接

 注：笛卡尔积结果数据条数是是A*B，即两个表全部数据的乘积。上面所有的连接都很难直接得到笛卡尔积。具体问题具体分析

 6.索引

定义：是帮助sql高效获取数据的数据结构，本质上是数据结构。

排序和查找均有可能使用到索引。

平时所说的索引没有特别说明一般是指B树（多路搜索树）结构组织的索引。除了B树这种类型索引之外，还有哈希索引等。

 

7.explain 查询sql执行计划

 7.1、用法 : explain + sql

 7.2、执行计划各个字段代表的意义

   id：id相同，顺序执行。id不同时，id序号越大，越先执行。

select_type: 代表查询类型。

            取值有: SIMPLE(普通查询), PRIMARY(最后加载的查询，可能有多个比如上图就是两个PRIMARY), SUBQUERY(子查询), DERIVED(衍生，上图table列<derived2>中2代表id中的2), UNION(联合查询), UNION RESULT

table:  查询数据是关于哪张表的。

type:   显示查询使用了哪种方式（访问类型）。

           从最好到最差依次是: system > const > eq_ref > ref > range > index > ALL (主要就是这些)

           system :  过滤条件为写死的常量，基本没用，比如主键过滤一条。

           const : 单表单行，基本也没用。

           eq_ref: 通过索引访问，唯一性索引扫描（对于每个索引键，表中只有一条记录与之匹配），常见于主键或唯一索引扫描。

           ref :   通过索引访问，非唯一性索引扫描，  返回匹配某个单独值的所有行，有多条记录符合。

           rang: 只检索给定范围的行，使用一个索引来选择行。一般出现在between, < , >, in 等的查询中。

                    开始于索引的某一点，结束于另一点，不用扫描全部索引。

           index: 全索引扫描, inde只遍历索引树，通常比ALL快。

           ALL: 全表扫描 ，读全表。

 

8、主从复制

配置文件my.ini（windows下的）:

    server-id=1   :  主服务器唯一id

    log-bin=自己本地的路径/mysqlbin  :  启用二进制日志

    log-err=自己本地的路径/mysqlerr   : 启用错误日志

    basedir=路径    ：根目录，mysql的根路径

    datadir=自己本地路径/Data  :  数据目录

    read-only=0  :  代表主机读写都可以

    binlog-ignore-db=mysql : 设置不需要复制的数据库

    binlog-do-db=需要复制的数据库名字  ： 设置需要复制的数据库

配置文件my.cnf（Linux下的 etc/my.cnf）【在此用作从机】：

    1. server-id=2

    2.重启主库【windows下的】

    3.保存重启从库【Linux下的】

    4.主从机都关闭防火墙【Linux下关闭防火墙：service iptables stop】

    5.主库【主机】建立账户并授权slave，见：6

    6.主库执行：GRANT REPLICATION SLAVE ON *.*  TO 'zhangsan'@'从机数据库IP' IDENTIFED BY '123456'

            即：允许从库以zhangsan的用户名,123456的密码，登录主库。

    7.主库执行：show master status;

      8.从库执行：CHANGE MASTER TO MASTER_HOST='主库IP',MASTER_USER='zhangsan',MASTER_PASSWORD=

                           '123456',MASTER_LOG_FILE='File名字',MASTER_LOG_POS=Position数字;

例如：

CHANGE MASTER TO MASTER_HOST='192.168.14.165',

MASTER_USER='zhangsan',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysqlbin.00035',MASTER_LOG_POS=341;

      9.从库执行：start slave;

      10.从库执行：show slave status\G; 查看从机状态【\G竖着显示，可以去掉】

           要求下面两个参数必须为Yes;【Slave_IO_Running和Slave_SQL_Running】

至此主从复制完成。

11.从库执行：stop slave; 可以停掉从机复制功能。

12.主库的Position数字一直是变化的，重启slave的复制功能时要也要跟随变化。