Mysql 复习

数据库语法

INSERT INTO mypractise1 VALUE(4,"666",TRUE)
DELETE FROM mypractise1 WHERE user_id = 1;

SELECT user_name FROM mypractise1;
UPDATE mypractise1 SET user_name= "luoshenshen" WHERE user_id = 4;


ALTER TABLE mypractise1 ADD COLUMN address VARCHAR(20);
ALTER TABLE mypractise1 DROP COLUMN sex;
ALTER TABLE mypractise1 MODIFY COLUMN address  VARCHAR(30); #改变列属性
ALTER TABLE mypractise1 CHANGE COLUMN address newaddress VARCHAR(30); #该列名

DROP TABLE mypractise1;

CREATE INDEX indexName on mypractise1(user_id, user_name);
ALTER TABLE ADD INDEX mypractise1(user_id)

DROP INDEX indexName ON mypractise1

CREATE VIEW VIEW_NAME
    AS
SELECT * FROM mypractise1

ALTER VIEW VIEW_NAME
    AS
SELECT * FROM mypractise1



LOCK table mypractise1 read; #加读锁
LOCK table mypractise1 write; #加写锁


UNLOCK tables;

SET AUTOCOMMIT = 0;

BEGIN
SELECT * FROM mypractise WHERE USER_ID = 1 FOR UPDATE;  #加行级锁   &&   索引部分自动加行级锁,否则转变为表级锁
COMMIT;

#间隙锁
SELECT * FROM mypractise WHERE USER_ID > 1 FOR UPDATE; #范围符号转化为间隙级锁  危害:插入失败

索引相关

• 聚簇索引 ：Innodb 找到索引可以直接找到数据。
• 非聚簇索引：Myisam 索引 找到索引后，需要根据索引指向数据的地址找到数据。
• 单值索引：索引的列是单列
• 唯一索引：索引的值唯一
• 复合索引：多列组成一个索引

索引优化

索引失效

索引优化

1. 全值匹配
2. 最左侧法则，从最左列开始，不跳过索引中的列
3. 不在索引列上进行任何操作（计算，函数，类型转化）
4. 存储引擎不能使用索引中范围条件右边的列
5. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致））按需取数据，用多少取多少，尽量与索引一致；Extra中出现了using index很好！
6. 在使用不能于的时候无法使用索引
7. is null， not null
8. like模糊查询
9. 字符串不加单引号（类似于类型转化）
   10.使用or连接索引失效

sql优化

explain

id：

执行顺序由大到小，由上至下。衍生：DERVIED(类似于一个虚表)\

select_type:

1.SIMPLE： 简单的select，不包含子查询或UNION
2. **PRIMARY:**查询中包含任何复杂的子部分的最外层查询
3. SUBQUERY:
4. DERIVED：
5. UNION
6. UNION RESULT

table

type

system>const>eq_ref>ref>range>index>all

1. system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。
2. const：表示通过索引一次就找到了，const用于比较primary key或则unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
4. ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。
5. range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不会扫描全部索引
6. index：Full Index Scan，index与All区别为index类型只遍历索引树。这通常比All快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
7. all：Full Table Scan，将遍历全表以找到匹配的行。

possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出。但不一定被查询实际使用。

keys

实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中，不会出现在possible_keys列表中。（覆盖索引：查询的字段与建立的复合索引的个数一一吻合）

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

ref：

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。查询中与其它表关联的字段，外键关系建立索引。

rows：

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra：

包含不适合在其他列中显示但十分重要的额外信息。

1.Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为“文件排序”
2.**Using temporary：**使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
3. Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
4. Using where：表明使用了where过滤。
5.Using join buffer：使用了连接缓存。
6. impossible where：where子句的值总是false，不能用来获取任何元组。（查询语句中where的条件不可能被满足，恒为False）
7. select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT()操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
8. select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT()操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

主从复制

Binary log:主数据库的二进制日志.
Relay log:从服务器的中继日志.
1.master在每个事务更新数据完成之前,将该操作记录串行地写入到binlog(主机二进制日志)文件中.
2. alve开启一个I/O Thread,该线程在master打开一个普通连接,主要工作是binlog dump process.如果读取的进度已经跟上了master,就进入睡眠状态并等待master产生新的事件.I/O线程最终的目的是将这些事件写入到中继日志中.
3.SQL Thread会读取中继日志, 并顺序执行该日志中的SQL事件,从而与主数据库中的数据保持一致.