MYSQL 索引事务日志概述

tiyan

一、MYSQL查询
在mysql查询中每个查询语句只能工作在一个CPU上，不能并发到其他的CPU上。由于这个原因最好把查询语句简单化处理
后在综合起来
mysql查询一般有以下几种：
1、简单查询：单表查询
2、联接查询：多表查询
3、子查询：嵌套查询，是个复杂查询
4、联合查询：合并多个SQL语句的查询
查询中常用到的一些查询命令：
SELECT:显示内容并且能进行数学运算、执行内建函数
COUNT聚合函数，用来显示表中的总行数
MAX(field)：返回最大值
MIN(field)：返回最小值
AVG(field):求平均值
SHOW DATABASES:显示数据库中所有的数据库
SHOW TABLES:显示数据库中某个数据库中的表
SELECT :挑选列
WHERE :挑选行
FROM:指定表的，指定从哪里查询的
AS:定义别名的；别名跟元字段没有关系，只是显示时候的名字
CONDITION:比较操作
比较操作符：
    =等于、<>不等于、<=>也是等于但是能执行值为空的等于、<小于、<=小于等于、>大于、>=大于等于；
         算术比较操作符：
   +、-、*、/、%
组合条件：
逻辑操作:
        AND与、OR或、NOT非
IN：在指定列表中就可以：格式 in (18,20,22)
BETWEEN AND:大于等于：格式 between 19 and 25
IS NULL:为空：做空比较时不能使用等于号
IS NOT NULL:不为空
LIKE:通配符匹配 % 任意长度的任意字符 _任意单个字符
RLIKE 自己指定匹配方式。正则表达式
ORDER BY Name DESC ;表示降序
DISTNCT ; 特有的唯一的。
GROUP BY 将取得的结果进行分组的。
分组的主要目的是进行聚合计算的
SUM 求和的
HAVING 对 GROUP by 的结果进行过滤的
LIMIT 限制查询结果所显示行数的
LIMIT 1，2:跳过1行数,显示第2行之后的行数两行
联结查询;
交叉连结，笛卡尔乘积
内联结 按照内生的等值关系进行连接
外联结
  左外联结 以第一张表为准 tb1 LIFE JOIN tb2 ON condition
  右外联结 以第二张表为准 tb1 RIGHT JOIN tb2 ON condition
  自联结：通过一张表的等值关系建立连结关系的。
联合查询; UNION 将两个结果合并起来
子查询：
查询中嵌套了别的查询语句；
格式例如：select name,age from students where age = (select min(age) from students);
WHERE 子句或HAVING 子句中的子查询
  用于比较操作符或逻辑操作中（自查性能的返回值只能是一行）
  用于IN 操作符（子查询的返回值可以使多行）
  用于EXTSTS只要存在就可以
  用于FROM子句中的子查询
  用于UPDATE语句或DELETE中子查询
二、索引
1、索引:是一种数据结构;就是将你拿来的字段或者字段的组合中的数据取出来,按照某种特定的方式将它们重新组合起来
的数据结构结构
2、索引分类：根据不同的组织方式满足不同类型的机制：
  （1）.简单索引：建立在单个字段的索引
  （2）.组合索引：建立在多个字段上的组合索引
3、索引定义在查询条件中用到的字段上
4、索引的优缺点
   优点：对于记录数量很多的表,可以提高查询速度.
   缺点：索引是占用空间的.索引会影响update insert delete速度。
5、索引中注意的两点：
查找索引中列的顺序至关重要
索引越多，我们更新对象更慢，每次更新都会更新索引
6、索引的分类：
B-Tree: Balance Tree 平衡树索引，每个叶子节点到根距离长度一样。结构成树状结构，跟文件的目录一样。
假设我们这里有六十人，我们现在要查ID为48的那个人，首先从根开始查询，返回这个ID为48的范围，然后接下来查询
先从第二行查，从左向右依次查询，发现在41-60这个范围内，然后在接下查询，刚好48在41-50中，这就是平衡树索引的
查询的本质，现实中比这个复杂。
这样的索引中的叶子节点不是数据本身，只是存储的是指针，指向真正数据在磁盘位置存放砸哪里，根据指针找到页面数
据，然后就能把这数据从载入内存中读取出来。
在B-Tree索引中INNODB索引，叶节点存储不在是指针，而是能够立即找到数据，因为节点挨着就是这个数据，这样就节省
大量的磁盘IO，数据要跟索引存储在一起。，不过INNODB除了主键索引外，剩余都是第二索引。需要查询第二索引也是从
第二索引的节点存储的指针也是指向主键节点的。
最左前缀索引：自左向右取固定的字段
     全键值、键值范围、键前缀
适用场景:
   匹配全名：全键值匹配，将索引中的每个字段都和键中的所有列进行匹配
   匹配最左前缀:仅适用于索引中的第一列
   匹配范围值:>,< 等比较中，基于最左前缀
局限性：
    只要查找没从索引最左侧开始，无法使用此索引
不能跳过索引中的列
哈希索引:
    键值对:key-value
只有Memory引擎支持显示hash索引
InnoDB:自适应hash索引
局限性：
    不能根据索引排序，不支持覆盖索引，不支持部分键匹配，只支持使用=，in,<=>;
优势：
   速度极快,(在碰撞率低下的情况)
R-Tree:空间位置信息，收索地理信息
    只有myISAM引擎支持
Fulltext:全文索引,只有myISAM引擎支持
全文索引项目:
   Lucene:用JAVA开发
   Sphinx:用C++开发,速度巨快无比
覆盖索引：能够在索引中就能找到所有的所需数据的索引叫做覆盖索引，大大提升查找速度。
       把索引载入内存提升查找速度。   
               键缓存，MyISAM：
                     缓存键，（索引是指针，如果不能完成覆盖索引的话，必然会引起磁盘IO）
             缓冲区，InnoDB   
                    键+数据都缓存，（buffer-cache)
使用索引：
1、必须使用隔离列，不能参与运算
2、前缀索引和索引选择性至关重要，区别不同的数据，但是基于这个条件上要短。
     SELECT COUNT(DISCTICT field)/COUNT(*) FROM table
     SELECT COUNT(DISCTICT LEFT(field,n))/COUNT(*) FROM table
3、选择使用合适的覆盖索引
4、使用索引即完成查询又同时完成排序
    扫描有序索引：
    使用文件排序:(filesort)
5、尽可能避免使用重复索引
三、NYSQL的锁
mysql的锁和事务
读锁/写锁:共享锁/独占锁
锁粒度：
  表锁:对整张表加锁
  页锁
  行锁：只锁定某一行的
锁的实现:
   存储引擎级别：更接近锁，比服务器更跟理解数据的锁
   服务器级别
隐式锁：mysql自我实现的锁
显式锁:用户添加的锁，一般不要用
lock tab_name lock_type WRITE;  发起写锁。
unlock tables；                    释放锁。
四、存储
存储引擎也称作表类型；
查看所有支持的存储引擎:SHOW ENGINES,
查看某张表支持的存储引擎:SHOW TABLE STATUS LIKE 'S%'\G
MyISAM:5.5.3之前的默认
MyISAM特性：
  不支持事务
  表级锁
  索引类型:B-Tree,Fulltext,R-Tree
  支持延迟更新索引
  最适合用于数据仓库，读多、写少的场景中
InnoDB特性:
  支持事务，基于多版本并发控制（MVCC）
  默认表空间
  在默认隔离级别上使用间隙锁，防止幻读
  支持行级锁
  支持预读功能，基于局部性原理
  自适应HASH索引
  读读mysql的
在备份数据的时候一定要施加锁
五、事务和用户授权
事务：Transaction就是一组SQL语句
ACID:
A:原子性 atomicity
C:一致性 consistency
I:隔离性 isolation 当多个事务执行时候，每个事件之间是否产生影响
D:持久性 durability 一旦一个事务完成结束后那么这个事务就会被永久性存储下了
事务越小越好，基于满足事务的需求
SHOW GLOBAL VARIABLES LIKE 'tx%'
事务隔离级别：
   READ UNCOMMITTED 能看到别人没有提交就能看到  （脏读）
   READ COMMITTED  能看到别人提交后才能看到，但会出现幻读 （不可重复读）
   REPETABLE READ  可重复读 （幻读）
   SERIABLIZABLE  串行化防止出现幻读 （加锁读）
mysql默认是三级别
事务回滚rollback,commit
手动启动事务：START TRANSACTION
服务器级别:管理类
数据库级别:
表级别；
字段级别
用户授权相关:
user,host.db, 数据库级别的授权
columns_priv
tables_priv 表级别的授权
procs_priv 基于进程
FLUSH PRIVTLEGES重读授权表
用户账号：用户@来源地址
创建用户：CREATE USER username@host
创建用户密码:
删除用户:DROP USER username@host
查看某个用户在mysql上的授权权限：SHOW GRANTS FOR testuser@'172.16.%.%'
授权：GRANTS
撤销授权:REVOKE
管理类:
SUPER;特殊的管理类权限
ALTER ROUTINE:删除或修改历程
CREATE:用户是否有权限创建表
CREATE ROUTINE:用户是否创建表的历程
DROP:删除表的权限
EXECUTE执行权限的历程
with GRANT OPTION:权限授权的，可以把自己的权限给别人
INDEX删除索引权限
INSERT:插入行的权限
UPDATE:修改行的权限
收回权限：REVOKE INSERT ON jiaowu.students FROM testuser@'172.16.%.%'
给用户重名名:RENAME USER old_user TO new_user
资源限定有关的
with_option:
    GRANT OPTION           授权选项
  | MAX_QUERIES_PER_HOUR count  每小时最大查询数
  | MAX_UPDATES_PER_HOUR count  每小时最大修改数
  | MAX_CONNECTIONS_PER_HOUR count  每小时最大连接数
  | MAX_USER_CONNECTIONS count  最大连接并发数
FLUSH HOSTS清楚主机缓存
max_user_connetions:同一个账号最大允许连接数
max_connetions:最大允许连接次数
max_allowed_packet:允许最大发送报文的最大个数
max_connect_errors：最大允许连接登录次数
connect_timeout:连接超时时间
六、MYSQL的日志：
错误日志
一般查询日志：只负责记录常用的查询信息
慢查询日志:分析MYSQL的性能能帮助优化
二进制日志：
中继日志;从主才能用
事务日志
错误日志：
  1、服务器启动和关闭过程中的信息
  2、服务器运行过程中的错误信息
  3、事件调度器运行一个事件时产生的信息
  4、在从服务器上启动从服务器进程时产生的信息
二进制日志：
能够作用于复制和即时点恢复
二进制的记录就是一个二进制日志事件
要单独存放一个磁盘，不能数据文件存储在一个磁盘上
慢查询日志：查询时间长度超过规定时间，都要记录在这个日志中
SHOW GLOBAL VARIABLES LIKE 'long%'查看需要的信息
事务日志:是事务引擎特有的日志;就是一个连续的磁盘空间,大小提前确定不能修改大小
能够将随机IO转换成顺序IO;拥有两个日志文件，称为事务日志组；一般情况下数据跟事务日志最好分开，事务日志最好
做成ruad1模型
innodb的恢复操作，当事务因为种种原因没有完成，再一次操作这次事务是要回滚，撤销上次的事务操作
二进制日志记录有三种：
基于语句
基于行
混合方式：默认方式

我积极解决

我在马路边丢了一分钱

mwjhw216

真是 收益 匪浅

爱死你了

听君一席话，省我十本书!

hitl

我是个凑数的。。。

cheun

禽兽尚且有半点怜悯之心，而我一点也没有，所以我不是禽兽。

帝王

脱了衣服我是禽兽，穿上衣服我是衣冠禽兽!