MySQL开发

MySQL框架介绍

 

一，MySQL在linux的安装以及解决乱码问题

1. 安装MySQL的客户端和服务端，client，server,  安装前先用rpm命令查看之前是否有安装过 。

   • rpm  -qa|grep mysql。如果存在mysql-libs的旧版本， 请执行卸载命令，rpm -e --nodeps  mysql-libs.(CentOS6）。

• rpm  -qa|grep mariadb。 如果存在maria-libs的旧版本，进行卸载，rpm -e  --nodeps  mariadb-libs(CentOS7)。

 

        2.检查/tmp文件夹的权限    全部权限都拥有就是正常的。

        3.将client，server复制到/opt 文件夹下，执行rpm -ivh  MySQL命令进行安装。先安装客户端 。

        4.mysql服务首次启动后需要设置初始密码。

 

        解决中文乱码问题

        

        1，原因是字符集问题，  show variables like 'character%';  查看mysql的默认编码格式

        2,   修改配置文件   在/usr/share/mysql/中找到my-huge.cnf  ,将此文件复制并改名为/etc/my.cnf, 

        3， 用vim编辑器添加，1， [client] default-character-set=utf8               2， [mysql] default-character-set=utf8

                                            3，[mysqld] default-character-set=utf8                  character-set-server=utf8             collation-server=utf8_general_ci

 

        4，修改数据库的编码   alter database mydb character set'utf8';    修改表的编码  alter table mytbl convert to character set'utf8';

        5,  已经乱码的数据删除或者update语句进行更新。

 

二、MySQL的用户与权限管理    

1. 什么安装好后将配置文件改为my.cnf并移动到/etc 目录下，因为mysql的init 脚本文件中，有一行代码，默认读取/etc 目录中的my.cnf 配置。

2. 在mysql软件的mysql库中，有一张user表，里面存放着mysql的用户及权限，mysql默认自带的4个用户权限全是本机的，需要输入指令手动添加可以远程访问的用户。

           eg： grant all privileges on *.* to root@'%'  identified by '123123';

 

三、MySQL 杂项设置

    1.group by 使用原则，select 后面只能放group by后面的字段和函数；

 

    2.sql_mode   输入SQL命令 show variables like 'sql_mode';       查看sql_mode设置，， 查看后添加sql_mode设置，SET  sql_mode='ONLY_FULL_GROUP_BY';

 

四、MySQL的逻辑架构

1. 

 

索引优化分析  

1、性能下降 SQL慢，执行时间长，等待时间长   

• 数据过多     分库分表          

• 关联了表太多，太多join     sql优化        

• 没有充分利用到索引     索引建立        

• 服务器调优及各个参数设置       调整  my.cnf.

2，索引的本质

• 索引是帮助mysql快速获取结果的数据结构，索引是数据结构。

• 索引本身也很大，不可能全部存储到内存中，因此索引往往以索引文件的形式存放在磁盘上。

• 优势：检索，排序快  因为已根据索引排好了顺序。            劣势：delete ，update，insert变慢，因为要更新相应的索引文件。

3，索引的结构

• MySQL底层索引结构为B+tree的结构，指向数据的指针在叶子节点内。 因为B+tree的数据结构中，枝干节点内部不保存指向数据的指针，所以每次加载的节点比Btree多，每次加载更多的节点可以更高效的获取到数据。

• 时间复杂度：有一个问题，随着问题规模时间增长的复杂程度是趋于平稳的上升。

• 聚簇索引：表示数据行和相邻的键值聚簇的存储在一起。 主键索引。

• 非聚簇索引：

4，索引的分类

• 单值索引:即一个索引只包含单个列，一个表可以有多个单列索引。

• 唯一索引：索引列的值必须唯一，但允许有空值。

• 主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引。

• 复合索引：即一个索引包含多个列。

• 基本语法 

  • 查看  -show index from table_name

  • 删除  -drop index【indexName】on table_name

  • 创建  -create 【unique】index【indexName】on table_name(column)

  • 创建复合索引  -create 【unique】index【indexName】on table_name(column1，column2, column3, ...)

    4.1、什么情况下创建索引！！！

• 主键自动创建索引

• 频繁作为查询条件的字段应该创建索引

• 查询中与其他表关联的字段，外键关系建立索引

• 单键/组合索引的选项问题，组合索引性价比更高

• 查询中排序的字段，排序字段若通过索引去访问，将大大提高排序速度

• 查询中统计或分组字段

    4.2、什么情况下不适合创建索引

• 表记录太少

• 经常增删改的表或者字段

• where条件里用不到的字段不用创建索引

• 过滤性不好的不适合创建索引

 

 

5、性能分析   Explain

• 是什么： EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道mysql是如果处理你的sql语句的，分析你的查询语句或者是表结构的性能瓶颈。

• 能干什么：1，表的读取顺序  2，哪些索引可以使用  3，数据读取操作的操作类型   4，哪些索引被实际使用  5，表之间的引用   6，每张表有多少行被物理查询。

• 使用  ：explain+sql语句

• 各字段解释：

  • id:   id号每个号码，表示一趟独立的查询，一个sql的查询趟数越少越好。

  • type：表示查询使用了何种类型，从最好到最次依次为：system>const>eq_ref>ref>range>index>ALL

  • Extra:  order by 没有使用索引

   

 

6.查询优化   建立索引

• 单表使用索引及常见索引失效 

  • 全值匹配我最爱  ，最好对where后面的字段全部加上索引。 索引命中需要顺序，如果是复合索引，where 条件后面的字段与复合索引字段字面一致，顺序不需要一致，优化器进行优化，但是如果where后面缺少某个索引字段，索引有可能会失效，索引字段匹配需要顺序，如果索引某个字段没匹配上后面字段也无法匹配。

  • 最佳左前缀法则，最佳左前缀法则就是上面原则解释的最好实践。

  • 索引失效

    • 不在索引列上做任何操作，（计算，函数，类型转换（自动或手动）），会导致索引失效而转向全表扫描。

    • 存储引擎不能使用索引中 范围条件（<,>,<>）右边的字段

    • mysql在使用不等于，（！=，<>）是无法使用索引

    • is not null 无法使用索引，is null 是可以使用索引的， 有not的都无法使用索引

    • like 以通配符开头会导致索引失效（%,  '.'）

    • 字符串不加单引号会导致索引失效

• 一般性建议

    

 

•     关联查询优化

  • 建议

    • 保证被驱动表的join字段已经被索引 （关联查询驱动表不可避免的要进行全表扫描，所以加索引无效）

    • left join时，选择小表作为驱动表，大表作为被驱动表（关联查询驱动表不可避免的进行全表扫描，被驱动表可以建立索引）

    • inner join时，mysql会自己帮你把小结果集的表选为驱动表

    • 子查询尽量不要放在被驱动表，有可能使用不到索引（虚拟表无法建立索引）

    • 能够直接多表关联的尽量直接关联，不用子查询

•  子查询优化

  • 建议

    • 尽量不要使用not in或者not exists  用left outer join on XXX is null 替代

 

• 排序分组优化

  • order by  无过滤不索引   实在没有可以加limit条件进行过滤

  • order by  顺序错  必排序

  • order by   方向反  必排序

  • group by排序没有order by的第一个规则

• 覆盖索引优化    （type = index）select 和from之间的字段用上了索引 

  • 查询时候不要使用select *  ，使用具体字段

7，MySQL中间件  --mycat  ，实现mysql读写分离既分步式mysql。

• 干什么

  • 读写分离

  • 数据分片

    • 垂直拆分

    • 水平拆分

    • 垂直+水平拆分

  • 多数据源整合

• 怎么用

  • 安装解压即可用，解压后移动到/usr/local/目录下

  • 修改配置文件

    • schema.xml  定义逻辑库，表，分片节点等内容。

    • rule.xml  定义分片规则。

    • server.xml   定义用户及系统相关变量，如端口。

• 分库

  • 原则：不会出现join关联的表进行拆分，关联的表放在一个库里。

  • 原理：mycat分库是在两台新增主机上创建两个新的空数据库，在将原来数据库的数据灌输到新分的库上。

• 分表

  • 原则：电商分表可以按照用户的id进行分表， 同一个用户的订单放到同一个表中，分表是将一个表水平拆分，放到不同的主机上。

  • 跨库join：

    • ER表

      • 为了相关联的表的行尽量分在一个库下

      • 修改 schema.xml文件

      • 建相关表

    • 全局表

      • 两个库里都必须有的表且两个库里的表数据完全一致，例如：字典表。

      • 全局表中  type=global

• 全局序列