数据库语法
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 索引 找到索引后,需要根据索引指向数据的地址找到数据。
- 单值索引:索引的列是单列
- 唯一索引:索引的值唯一
- 复合索引:多列组成一个索引
索引优化
索引失效
索引优化
- 全值匹配
- 最左侧法则,从最左列开始,不跳过索引中的列
- 不在索引列上进行任何操作(计算,函数,类型转化)
- 存储引擎不能使用索引中范围条件右边的列
- 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))按需取数据,用多少取多少,尽量与索引一致;Extra中出现了using index很好!
- 在使用不能于的时候无法使用索引
- is null, not null
- like模糊查询
- 字符串不加单引号(类似于类型转化)
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
- system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,这个也可以忽略不计。
- const:表示通过索引一次就找到了,const用于比较primary key或则unique索引。因为只匹配一行数据,所以很快。如将主键置于where列表中,MySQL就能将该查询转换为一个常量。
- eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
- ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体。
- range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不会扫描全部索引
- index:Full Index Scan,index与All区别为index类型只遍历索引树。这通常比All快,因为索引文件通常比数据文件小。(也就是说虽然all和index都是读全表,但index是从索引中读取的,而all是从硬盘中读的)
- 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事件,从而与主数据库中的数据保持一致.