分类： 数据库

        //check back table exists
        $back_db = $this->_cust_bak_db;
        $table = 'voucher_'.date('Ym',strtotime($month));

        $sql = "select count(*) as cnt 
        from `INFORMATION_SCHEMA`.`TABLES` 
        where `TABLE_SCHEMA`='$back_db' and `TABLE_NAME`='$table'";

1、<=>

安全比较运算符，用来做 NULL 值的关系运算。

因为 mysql 的 NULL 值的特性，任何值和其比较的结果都是 NULL， 1 = NULL，1 <> NULL / 1 != NULL 得到的结果都是 NULL。

SELECT 1 = NULL, 1 <> NULL, 1 != NULL;
+----------+-----------+-----------+
| 1 = NULL | 1 <> NULL | 1 != NULL |
+----------+-----------+-----------+
|     NULL |      NULL |      NULL |
+----------+-----------+-----------+
1 row in set (0.00 sec)

当然我们可以用 IS NULL 去判断，即

SELECT 1 IS NULL, 1 IS NOT NULL, NOT (1 IS NULL), !(1 IS NULL);
+-----------+---------------+-----------------+--------------+
| 1 IS NULL | 1 IS NOT NULL | NOT (1 IS NULL) | !(1 IS NULL) |
+-----------+---------------+-----------------+--------------+
|         0 |             1 |               1 |            1 |
+-----------+---------------+-----------------+--------------+
1 row in set (0.00 sec)

但用 <=> 更为简洁

SELECT 1 <=> NULL, !(1 <=> NULL);
+------------+---------------+
| 1 <=> NULL | !(1 <=> NULL) |
+------------+---------------+
|          0 |             1 |
+------------+---------------+
1 row in set (0.00 sec)

2、:=

:= 和 = 运算符在大部分场景下并无区别，但 := 更为全场景些。

= 只有在 set 和update时才是和 := 一样，赋值的作用，其它都是关系运算符 等于 的作用。

:= 不只在 set 和 update 时赋值的作用，在 select 也是赋值的作用。

SET @name = 'big_cat';

SELECT @name;

# = 在 select 语句中成为了比较运算符 结果为 NULL (@name 为 NULL, 在 mysql 中 NULL 和任何值比较都为 NULL)

# := 则为仍未赋值，@name_defined 被赋值为 big_cat 后再 select 就出来了

SELECT @name = 'big_cat', @name_defined := 'big_cat', @name_defined;
+-------------------+----------------------------+---------------+
| @name = 'big_cat' | @name_defined := 'big_cat' | @name_defined |
+-------------------+----------------------------+---------------+
|              NULL | big_cat                    | big_cat       |
+-------------------+----------------------------+---------------+
1 row in set (0.00 sec)

3、@ 用户变量

@用来标识用户变量

SET @name = "big_cat";

SELECT 'big_cat' INTO @name;

SELECT @name := 'big_cat';

4、@@系统变量

系统变量又分为全局系统变量和会话系统变量

读取系统变量

SELECT @@global.sort_buffer_size;

SELECT @@session.sort_buffer_size;

SHOW GLOBAL VARIABLES LIKE 'sort_buffer_size';

SHOW SESSION VARIABLES LIKE 'sort_buffer_size';

设置系统变量

SET @@global.sort_buffer_size = 2 * 1024 * 1024;

SET @@session.sort_buffer_size = 2 * 1024 * 1024;

SET GLOBAL sort_buffer_size = 2 * 1024 * 1024;

SET SESSION sort_buffer_size = 2 * 1024 * 1024;

有些系统变量只有全局级的，比如 max_connnections，读取时可以不显示声明 global，但设置时需要，否则会提示你设置的为全局变量。

show variables where Variable_name like '%collation%'  

select @@character_set_database

alter database vms_wisma character set utf8; 

alter database vms_wisma character set latin1;

ACID

• Atomic同一个事务里，要么都提交，要么都回滚

• Consistency即在事务开始之前和事务结束之后，数据库的完整性约束没有被破坏

• Isolation 并发事务间的数据是彼此隔离的

• Durabiliy 事务提交后，所有结果务必被持久化

支持事务：InnoDB，NDBCluster， TokuDB

不支持事务：MyISAM，MEMORY

1. 隔离性通过锁实现

2. 原子性，一致性，持久性通过数据库的redo和undo来完成

撤销日志： UNDO LOG （现在正在活跃的事务，但还没有提交，或者还没有完成check point）

重做日志：REDO LOG （已经提交的事务）

REDO 和 UNDO 在做数据库恢复的时候的作用是相反的，

RODO：在做数据库恢复的时候，如果有事务还没有真正的提交，那么需要前滚（扫描RODO LOG检查哪些事务已经提交了，但还没有持久化到数据文件【table spacs， data table spage】里的，再把这些重做一次事务【再重新提交一次】）

UNDO： 正好是相反 找到还没有提交的事务 但是再undo log里需要进行一次回滚，就是把已经提交的事务回滚掉

在做数据库恢复的时候要做这样的一个过程

autocommit = 0|1

设置事务自动 开启|关闭

显示事务 启动 | 关闭

1. 以START TRANSACTION/BEGIN开始事务

2. 以COMMIT/ROLLBACK TRANSACTION结束事务

隐性事务提交

主要是DDL,DCL会引发事务的隐性提交

SQL四种语言：DDL,DML,DCL,TCL

mysql中 myisam，innodb默认使用的是 Btree索引，至于btree的数据结构是怎样的都不重要，
只需要知道结果，既然是索引那这个数据结构最后是排好序；就像新华字典他的目录就是按照a,b,c..这样排好序的；
所以你在找东西的时候才快，比如你找 “中” 这个字的解释，你肯定就会定位到目录的 z 开头部分；

组合索引可以这样理解，比如（a,b,c），abc都是排好序的，在任意一段a的下面b都是排好序的，任何一段b下面c都是排好序的；

组合索引的生效原则是  从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；
比如

where a=3 and b=45 and c=5 .... 这种三个索引顺序使用中间没有断点，全部发挥作用；
where a=3 and c=5... 这种情况下b就是断点，a发挥了效果，c没有效果
where b=3 and c=4... 这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果；
where b=45 and a=3 and c=5 .... 这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关

（a,b,c） 三个列上加了联合索引（是联合索引 不是在每个列上单独加索引）

还需注意，  (a,b,c)多列索引和 (a,c,b)是不一样的，看上面的图也看得出来关系顺序是不一样的；
分析几个实际例子来加强理解；
分析句子中使用的索引情况

select * from mytable where a=3 and b=5 and c=4;
abc三个索引都在where条件里面用到了，而且都发挥了作用

select * from mytable where  c=4 and b=6 and a=3;
这条语句列出来只想说明 mysql没有那么笨，where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样

select * from mytable where a=3 and c=7;
a用到索引，b没有用，所以c是没有用到索引效果的

select * from mytable where a=3 and b>7 and c=3;
a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引

select * from mytable where b=3 and c=4;
因为a索引没有使用，所以这里 bc都没有用上索引效果

select * from mytable where a>4 and b=7 and c=9;
a用到了  b没有使用，c没有使用

select * from mytable where a=3 order by b;
a用到了索引，b在结果排序中也用到了索引的效果，前面说了，a下面任意一段的b是排好序的

select * from mytable where a=3 order by c;
a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort

select * from mytable where b=3 order by a;
b没有用到索引，排序中a也没有发挥索引效果

用voucher表来做单表测试

1. 创建voucher_id为主键

2. 创建voucher_idx(voucher_id, prefix, voucher_no)

    

当我查主键的时候毫无疑问 肯定是走主键索引的

当我只查找主键字段的时候 Extra显示“Using index”不回表直接使用到覆盖索引

可以看到 当查询条件里有主键的时候 查询会直接用主键查询 可能是因为主键是唯一索引 直接就定位到是最简方法

下面不用主键来做条件， 直接用voucher_idx里的字段来查找

可以看到type变成了ref 不再是有主键时的const

ref：是一种索引访问（有时也叫索引查找），它返回所有匹配某个单个值的行。然而，它可能会找到多个符合条件的行，因此它是查找和扫描的混合体…

可以看到找到了45310条记录，测试的voucher总数是319814  45310/319814≈0.15 不到30%所以还是能使用索引的

但是这就不对了 prefix是‘A10’的占了76% 还是能用到索引 是不是说明“一个值超过30%就无法使用索引”的描述？ 可能是新版功能加强了吧

这个确实能证实，不是从索引左侧的列搜索用不到索引，这条语句扫描了319814行

下面这个语句 其实说明了不按索引顺序 也是能用到索引的 应该是索引优化器把where里的索引列又排了下

查了一下优化器执行的代码 好像也没有对索引重排 看来还是service层做了优化让我们能使用到索引？

我又创建了一个索引voucher_idx2,我以为搜索条件是voucher_no, prefix和 prefix,voucher_no的时候会不一样，结果都用到了voucher_idx2

这可以说明当查询条件和搜索条件都在索引内时，不按索引顺序也是能用到索引的，但是扫描了全表

但是查询列或者搜索列里有不在索引列里的列的时候 是会扫描全表的

那我把所有列都拿进来 是不是都能用到索引？？ 

 

可以看到，其实是走了索引，但是也是扫了全表，效率应该相当差， 需要用数据量很大的库来试一下，如果不按索引顺序来跑的效率