MySQL笔记

1、 一般情况下查询和卸载用rpm
用rpm -qa | grep mysql查询有没有
如果有的话，版本不一致时，用rpm -e –nodeps mysql-lib…卸载
然后用rpm -ivh …就可以安装了。有安装包的时候用rpm，没有安装包的时候用yum。
Rpm和yum的区别：
Rpm最大的功能在于安装软件包，而不是下载。Rpm用rpm -i也可以下载，但是没有对应的源，也就是没有对应的数据仓库，而yum有对应的数据仓库，而且rpm有严重的软件包依赖关系。还有一点，rpm卸载软件包用的是软件的名字，比如rpm -e sudo;安装软件使用的是软件包的名字。
2、 Mysql启动服务后，设置密码时，/usr/bin/mysqladmin -u root password ‘123123’
然后通过 mysql -uroot -p123123进行登录，设置密码有空格，登录没空格
3、 中文乱码问题：
（1） 查看字符集：
show variables like ‘character%’; 或者show variables like ‘%char%’;
（2） 复制修改配置文件：
复制/usr/share/mysql/my-huge.cnf到/etc/my.cnf，
修改：
[client]
default-character-set=utf8
[mysqld]
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
[mysql]
default-character-set=utf8
（3） 重启mysql服务。
这样的话以后新建的库都改成了utf8的格式，并且系统默认的几个库也改成了utf8的格式。但是已经自己建立过的库的字符集没有修改。
查看库的字符集命令：show create database 库名;
（4） 对已经生成的库修改字符集。
修改数据库的字符集alter database mydb character set ‘utf8’;
修改数据表的字符集alter table mytbl convert to character set ‘utf8’;
（5） 但是数据表中原有的数据如果不是用的utf8的格式，数据格式本身不会发生改变，所以需要更新之前的数据，或者重新插入数据。
4、 Mysql用户管理
Mysql中在mysql数据库中有一个user表。

可以看出mysql的用户确认是根据主机和用户名联合确认的，也就是说，在用户名密码对的情况下，如果主机不是user中的主机，依然连不上服务器。
修改（修改用户，删除用户，修改密码）user表的操作和操作普通表是一致的。
User表中不止包含主机，用户名，密码，还包含各种操作权限。所以在创建用户的时候如果按照和普通表相同的插入语句，要写非常长的语句。所以就有了授权管理。
5、 授权管理：
（1） 授予权限：
grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接口令’;
（2） 查看权限：show grants;
（3） 收回权限命令：
revoke 权限1,权限2,…权限n on 数据库名称.表名称 from 用户名@用户地址 ;（注：必须用户重新登陆后才能生效）
6、 杂项设置
Sql_mode是一个很容易被忽视的变量，默认是空值，在这种设置下是可以允许一些非法操作的，比如允许非法数据的插入。在生产环境下，必须将这个值设置为严格模式，所以开发，测试环境的数据库也必须设置，这样在开发环境测试阶段就可以发现问题。
查看sql_mode状态：show variables like ‘sql_mode’;
非法操作举例：group by语句如果后面不把select后面的非函数字段添加完，没有添加的字段，select就会默认选择该字段在这个组的第一个，这样结果就是错的。这样我们可以设置一下set sql_mode=‘ONLY_FULL_GROUP_BY’;这样如果再出现上面的情况，语句就会报错。还有其他非法操作，见原文档。
7、 Mysql总体概述
和其他数据库相比，mysql的架构可以在多种不同场景中应用并发挥很好的作用。主要体现在存储引擎架构上。插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需求选择合适的存储引擎。
就是将查询处理，其他系统任务，以及存储相分离，通俗点说就是利用不同的引擎讲业务处理和存储提取相分离。
8、 Mysql逻辑架构

过程解读：
（0） mysql之外类似于java程序访问。
（1） 和连接池沟通
（2） Select查询时，先在缓存中查询，看有没有需要的数据，有的话直接返回，就到此结束了，没有的话进行（4）
先检查查询缓存，如果命中，直接返回结果，否则进行语句解析。也就是说，在解析查询之前，服务器会先访问查询缓存（query cache）–它存储select语句以及相应的查询结果集。
（3） Sql接口接受用户sql命令，并且返回用户需要查询的结果。
（4） 语法解析器和预处理：
解析成解析树。首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。
语法规则的验证和解析查询。mysql解析器将使用mysql语法规则验证和解析查询；
检查参数是否合法。预处理器则根据一些mysql规则进一步检查解析数是否合法。。
（5） 查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。存储引擎按计划进行分类执行。
（6） 将查询到的结果保存到缓存中和返回。
四层结构：接口层，服务层，引擎层，存储层
服务层：SQL接口，解析器，查询优化器，查询缓存。
9、 查看sql执行周期
先开启 show variables like ‘%profiling%’;
set profiling=1;
show profiles; #显示最近的几次查询
show profile cpu,block io for query Query_ID #查看程序的执行步骤
10、 SQL的执行顺序

11、 存储引擎
（1）如何用命令查看
#看你的mysql现在已提供什么存储引擎:
mysql> show engines;
#看你的mysql当前默认的存储引擎:
mysql> show variables like ‘%storage_engine%’;
（2）各个引擎的介绍
（3）MYISAM和InnoDB引擎的比较。
MYISAM不支持外键和事务，INNODB支持
MYISAM是表锁，INNODB是行锁，并发高。
MYISAM只缓存索引，不缓存真是数据；INNODB不仅缓存索引还缓存真实数据，因为缓存的东西比较多，所以对内存要求比较高，而且内存大小对性能有决定性的影响。这一点，如果在查询数据时，表用的MYISAM，即使之前查过了这条数据，缓存中存在这条数据，还是要从数据库中查询，因为MYISAM中缓存的只是索引，没有数据，相反，如果这个表的引擎是INNODB就可以。
MYISAM比较节省资源，消耗少，业务简单。原因前几条应该都有。INNODB并发写，支持事务，需要更大的资源。
两个引擎都默认安装。
默认使用的是INNODB。
自带系统表使用的是MYISAM，因为系统表拿user来说，设置用户名密码这种，不需要那么大的并发量。同时，还节省资源。
（4） 阿里淘宝用的数据库
Percona，这个数据库是对mysql进行了改进。并且存储引擎为xtradb，可以完全代替innodb。
阿里大部分使用的mysql数据库其实使用的percona的原型加以修改：AliSql+AliRedis
12、 性能下降SQL慢，执行时间长，等待时间长原因及处理方法
（1） 数据过多：分库分表
（2） 关联了太多的表，太多的join：SQL优化
（3） 没有充分利用索引：索引建立
（4） 服务器调优及各个参数的设置：调整my.conf
13、 索引简介：
MySQL官方的定义为：索引是帮助MySQL高效获取数据的数据结构。可以得到索引的本质是索引是数据结构。
（1）是什么？
索引的目的在于提高查询效率，可以类比字典。
索引详情：在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方法引向数据，这样就可以在这些数据上实现高级查找算法。这种数据结构就是索引。

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址，为了加快col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值，和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取相应的数据，从而快速的检索出符合条件的记录。
本质上就是用二叉树的节点存储索引键值，和一个指向对应数据记录物理地址的指针。进行查找。
索引是数据结构，可以理解为索引是排好序的快速查找数据结构。
通俗的说索引是一种数据结构，是一种支持快速查找算法的数据结构。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
（2）优势：
类似大学图书馆建书目索引。提高数据的检索效率，降低数据库IO成本。因为有了索引，就不用一条一条数据进行查找了，而是根据索引直接检索到那条数据，这样吗，就减少了读数据的次数，也就是降低了数据库IO成本。
通过索引对数据进行排序，降低了数据排序的成本，降低了CPU的消耗。
（3）劣势
相关定义：
数据结构：是指相互之间一种或多种关系的数据元素的集合和该集合中数据元素之间的关系的组成。记为：Data_structure=(D,R)，其中D是数据元素的集合，R是该集合中所有元素之间的关系的有限集合。
通俗的说数据结构可以理解为数据和数据之间关系的集合。
算法：是解决特定问题求解步骤的描述，在计算机中表现为指令的有限序列，并且每条指令表示一个或多个操作。
通俗的说算法就是解决问题的步骤。
数据结构和算法的关系：
14、 那些情况需要创建索引：
（1） 逐渐自动建立唯一索引
（2） 频繁作为查找条件的字段应该创建索引
（3） 查询中与其他表关联的字段，外间联系建立索引
（4） 单键/组合索引的选择问题，组合索引性价比更高
（5） 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
（6） 查询中统计或者分组字段
哪些情况不适合创建索引：
（1） 表记录太少
（2） 经常增删改的表或者字段：索引提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为MySQL不仅保存数据，还要保存索引文件。
（3） Where条件里用不到的字段不创建索引
（4） 过滤性不好的不合适建索引。比如性别就两个。
15、 EXPLAIN
1、 是什么？
可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或者是表结构性能的瓶颈。
2、 能干什么？
表的读取顺序
哪些索引可以使用
数据读取操作的操作类型
哪些索引被实际使用
表之间的引用
每张表有多少行被物理查询
3、 怎么用？
Explain+SQL语句