MYSQL技术笔记

1. mysql max_allowed_packet 查询和修改

mysql根据配置文件会限制server接受的数据包大小。有时候大的插入和更新会被max_allowed_packet 参数限制掉，导致失败。查看目前配置

show VARIABLES like '%max_allowed_packet%';

1)编辑my.cnf来修改（windows下my.ini）,在[mysqld]段或者mysql的server配置段进行修改。

max_allowed_packet = 20M

2)在mysql 命令行中运行

set global max_allowed_packet = 2*1024*1024*10

A communication packet is a single SQL statement sent to the MySQL server, a single row that is sent to the client, or a binary log event sent from a master replication server to a slave.

2. C API Function Overview

mysql_info() //  Returns information about the most recently executed query
mysql_error()// Returns the error message for the most recently invoked MySQL function
mysql_warning_count()//Returns the warning count for the previous SQL statement

3. 触发器

In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used

4. LOAD DATA INFILE

load DATA INFILE'people.txt' INTO TABLE people(@a,@b,@c,@d) SET name1=@a,country=@b

5. 排序优化

Fewer nullable column improve index usage.
表数据量很大时，分割后可以降低查询时需要读的数据和缩印的页数，降低了索引页数。

6. 存储过程

创建存储过程时，变量声明应该在游标和句柄声明前，下面会报错

CREATE PROCEDURE sum_t1()
BEGIN 
DECLARE done INT DEFAULT 0;
DECLARE va1 INT;
DECLARE cur CURSOR FOR SELECT f1 FROM t1;
DECLARE result INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT found SET done=1;
OPEN cur ;
REPEAT 
FETCH cur INTO va1;
IF NOT done THEN 
SET result=result+va1;
END IF;
UNTIL done END REPEAT;
SELECT result;
END 

7. 数据类型

float(7,5) 左边最大2位，右边保留5位。

10. 分组

mysql> SELECT * FROM table LIMIT 5,10;  // 检索记录行 6-15。为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1： 
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.如果只给定一个参数，它表示返回最大的记录行数目： 
mysql> SELECT * FROM table LIMIT 5;     //检索前 5 个记录行
//换句话说，LIMIT n 等价于 LIMIT 0,n。

SELECT NAME1 FROM t1 GROUP BY NAME1 ORDER by NAME1 LIMIT 2,1;
SELECT * FROM mysql.`user`//查看数据库用户
SELECT * FROM mysql.user

valid identifiers for the user table in the mysq1 database:
mysq1.`user`
mysq1.user
`mysq1`.`user`

12. 设置事务隔离级别

• 提交读(READ COMMITTED):提交读这种隔离级别保证了读到的任何数据都是提交的数据，避免读到中间的未提交的数据，脏读(dirty reads)。提交读保证我们读到的数据都是事务提交后的数据，这样子就保证了程序中数据的正确性，在我们的应用中，基本都是使用的Read Commited隔离级别。

SET GLOBAL tx_isolation='READ-COMMITTED'//设置提交读

start TRANSACTION;
select * from student;
update student set name = 'a1' where id = 1;
select * from student;//因为未提交，所以另一个session的查询到未更新数据

• 可重复读(REPEATABLE READS)：保证同一个事务从开始到结束获取到的数据一致。

SET  SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET  GLOBAL TRANSACTION ISOLATION LEVEL  REPEATABLE READ;
事务1：
start TRANSACTION;
select * from student;
update student set name = 'a2' where id = 1;
select * from student;

事务2：
start TRANSACTION;
select * from student;//查询到未提交

事务1：commit;
事务2：select * from student;//查询到未提交

• 串行化（SERIALIZABLE）。读操作会隐式获取共享锁，可以保证不同事务间的互斥。

13. non-case-sensitive

non-case-sensitive names on all operating system
Column, index, and stored routine names are not case sensitive on any platform, nor are column aliases. Trigger names are case sensitive.

14. JDBC

jdbc:mysql://localhost:3306/sakila?profileSQL=true
jdbc:mysql:replication://master,slave1,slave2,slave3/test
jdbc:mysql:loadbalance://localhost:3306,localhost:3310/sakila

15. 枚举类型排序

enum(‘paris’.’Amsterdam’.’New York’.’Tokyo’)
-》Paris, Amsterdam. New York, Tokyo

SELECT DISTINCT city FROM office ORDER BY CAST(city AS CHAR)
-》Amsterdam, New York, Paris, Tokyo

16. Metadata Locking

MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locking applies not just to tables, but also to schemas and stored programs (procedures, functions, triggers, and scheduled events).
To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session.

17. 慢查询

two features provide information that help to identify problems：
1.The slow query log；
2.The performance schema

18. Working with Decoupled Data

Previously, when using MySqlDataReader, the connection to the database was continually maintained, unless explicitly closed. It is also possible to work in a manner where a connection is only established when needed.
This decoupled mode of working with data is supported by Connector/Net.
Data Set ；Data Adapter ；Command Builder；

19 .output of the SELECT statements

create table Code_innodb
(
id int(11) not null primary key default 0,
`code` varchar(20) null default null
) engine = InnoDB;

create table Code_myisam
(
id int(11) not null primary key default 0,
`code` varchar(20) null default null
) engine = myisam;

INSERT INTO code_innodb VALUES(3,’charlie’);
INSERT INTO code_myisam VALUES(3,’charlie’);

INSERT INTO code_innodb VALUES (1, ‘Alpha’), (2, ‘Beta’), (3, ‘charlie’),(4, ‘Delta’);
INSERT INTO code_myisam VALUES (1, ‘Alpha’), (2, ‘Beta’), (3, ‘charlie’),(4, ‘Delta’);

select * from code_innodb;
id, code
‘3’, ‘charlie’

select * from code_myisam;
id, code
‘3’, ‘charlie’
‘1’, ‘Alpha’
‘2’, ‘Beta’

20. LOOP

CREATE PROCEDURE test2()
BEGIN
DECLARE i INT DEFAULT 0;
test_loop:LOOP
SET i=i+1;
IF i>=5 THEN 
LEAVE test_loop;
END IF;
END LOOP test_loop;
END
CALL test1




DECLARE i INT DEFAULT 0;
WHILE i < 5 Do
SET i = i + 1;
END WHILE;

union syntax

Union & union All

1 所有查询中的列数和列的顺序必须相同。
2 数据类型必须兼容

union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。
union all只是简单的将两个结果合并后就返回

order of the returned results

by default, the rows in the result set produced by a SELECT statement are returned by the server to the client in no particular order. When you issue a query, the server is free to return the rows in any convenient order. This order can be affected by factors such as the order in which rows are actually stored in the table or which indexes are used to process the query.

Join

• MySQL STRAIGHT_JOIN：STRAIGHT_JOIN 是 MySQL 对标准 SQL 的扩展，用于在多表查询时指定表载入的顺序。使用了 STRAIGHT_JOIN 后，table1 会先于 table2 载入。MySQL 在执行 INNER JOIN 的时候，会根据自己内部的优化规则来决定先载入 table1 还是 table2，如果您确认 MySQL 载入表的顺序并不是最优化的时候，就可以使用 STRAIGHT_JOIN 以替代 INNER JOIN。

SELECT article.aid,article.title,user.username FROM article STRAIGHT_JOIN user ON article.uid=user.uid

• MySQL NATURAL JOIN：使用 NATURAL JOIN 时，MySQL 将表中具有相同名称的字段自动进行记录匹配，而这些同名字段类型可以不同。因此，NATURAL JOIN 不用指定匹配条件。

• INNER JOIN=JOIN:只会显示可以匹配的记录项。

• Right join ：全显右表
  The optimizer processes only the JOINs in the sequence listed in the query.

Diagnostics Area

The diagnostics area can be examined with GET DIAGNOSTICS to extract specific items, or with SHOW WARNINGS or SHOW ERRORS to see conditions or errors.

select *

• SELECT * may cause more data than you need to be read from disk if your application needs only some columns.
• SELECT * causes more data than you need to be sent via the client/server protocol if your application needs only some columns.
• SELECT *causes your application to depend on the columns present when you wrote it ,so your application could break if the table structure changes.

HASH and BTREE INDEXES

B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.

Hash Index are used only for equality comparisons that use the = or <=> operators (but are very fast).The optimizer cannot use a hash index to speed up ORDER BY operations

Lost connection to MYSQL server query

• 网络连接问题
• Sometimes the “during query” form happens when millions of rows are being sent as part of one or more queries：increasing net_read_timeout
• More rarely, it can happen when the client is attempting the initial connection to the server。increase connect_timeout
• The client connection stayed idle for longer than interactive –timeout seconds and was closed.
• max-allowed-packeage

view types are not updateable

A view created with the TEMPTABLE algorithm
A view containing a GROUP BY clause
A view containing a HAVING clause

31. PHP MYSQL API

PHP MySQLi = PHP MySQL Improved!

There are three main API options when considering connecting to a MySQL database server:
PHP’s MySQL Extension
PHP’s mysqli Extension
PHP Data Objects (PDO)

32. explain select

id:select查询的序列号

select_type:select查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询。

table:输出的行所引用的表。

type:联合查询所使用的类型。type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL.一般来说，得保证查询至少达到range级别，最好能达到ref。

possible_keys:指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。

key:显示MySQL实际决定使用的键。如果没有索引被选择，键是NULL。

key_len:显示MySQL决定使用的键长度。如果键是NULL，长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。

ref:显示哪个字段或常数与key一起被使用。

rows:这个数表示mysql要遍历多少数据才能找到，在innodb上是不准确的。

Extra:如果是Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。如果是where used，就是使用上了where限制。如果是impossible where 表示用不着where，一般就是没查出来啥。

33. determine why the MYSQL server stopped

• The syslog on Linux/UNIX or the Event view on windows
• The MYSQL server error log file

35. 级联删除

CREATE TABLE child(
id INT ,
parent_id INT,
FOREIGN KEY(parent_id) REFERENCES parent(id) 
ON DELETE CASCADE
)

cascade方式
在父表上update/delete记录时，同步update/delete掉子表的匹配记录
On delete cascade从mysql3.23.50开始可用; on update cascade从mysql4.0.8开始可用

. set null方式
在父表上update/delete记录时，将子表上匹配记录的列设为null
要注意子表的外键列不能为not null
On delete set null从mysql3.23.50开始可用; on update set null从mysql4.0.8开始可用

. No action方式
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
这个是ANSI SQL-92标准,从mysql4.0.8开始支持

. Restrict方式
同no action, 都是立即检查外键约束
. Set default方式

36 prepare

mysql> PREPARE prod FROM "INSERT INTO examlple VALUES(?,?)";
mysql> SET @p='1';
mysql> SET @q='2';
mysql> EXECUTE prod USING @p,@q;
mysql> SET @name='3';
mysql> EXECUTE prod USING @p,@name;
mysql> DEALLOCATE PREPARE prod; 

38. union/union all

SELECT col1 FROM t1 WHERE col1< 100
UNION
SELECT col1 FROM t1 WHERE col1 BETWEEN 100 and 200
UNION ALL
SELECT col1 FROM t1 WHERE col 1 >=900//先执行第一个UNION，去除重复项。再去执行第二个UNION ALL，保留剩下的所有项

innoDB和MyISAM区别

◆1.InnoDB不支持FULLTEXT类型的索引。

　　◆2.InnoDB 中不保存表的具体行数，也就是说，执行select count() from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含 where条件时，两种表的操作是一样的。

　　◆3.对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。

　　◆4.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。

　　◆5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。

　　另外，InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”

查询缓存

服务器收到和以前同样的查询时，会直接检索缓存。

set session query_cache_type=ON;

合并表和分区表

//子表均为myisam引擎
create table mergetable(
data1 int not null primary key
)engine=merge union=(table1,table2) insert_method=last;//插入数据到最后表

数据库分区是为了某些查询操作减少响应时间，同时对于应用来讲分区是透明的。垂直分区和水平分区。

create table emp(

)partition by range(salary)(
   partition p1 values less than(1000),
   partition p2 values less than(2000)
  )

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但innoDB 的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。InnoDb将通过主键聚集数据。

• MyISAM的B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址，这里的索引都是非聚簇索引。

• InnoDB的数据文件本身就是索引文件，B+Tree的叶子节点上的data就是数据本身，key为主键，这是聚簇索引。

  1. 相关的数据保存在一起
  2. 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比非聚簇索引中快
  3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

索引类型

1. FULLTEXT：目前只有MyISAM引擎支持。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE INDEX创建FULLTEXT索引，要比先为一张表建立FULLTEXT然后再将数据写入的速度快很多。
2. HASH：hash就是一种（key=>value）形式的键值对。Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高
3. BTREE：就是一种将索引值按一定的算法，存入一个树形的数据结构。

复制如何工作

(1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
(2) slave将master的binary log events拷贝到它的中继日志(relay log)；
(3) slave重做中继日志中的事件，将改变反映它自己的数据。

redo undo

redo：在页修改的时候，先写到redo log buffer 里面，然后写到redo log 的文件系统缓存里面(fwrite)，然后再同步到磁盘文件（fsync）。 每当有操作执行前，将数据真正更改时，先前相关操作写入重做日志。这样当断电，或者一些意外，导致后续任务无法完成时，系统恢复后，可以继续完成这些更改
undo：在MySQL5.5之前，undo只能存放在ibdata*文件里面，5.6之后，可以通过设置innodb_undo_tablespaces参数把undo log存放在ibdata*之外。当一些更改在执行一半时，发生意外，而无法完成，则可以根据撤消日志恢复到更改之前的壮态。

一般情况下，mysql在崩溃之后，重启服务，innodb通过回滚日志undo将所有已完成并写入磁盘的未完成事务进行rollback，然后redo中的事务全部重新执行一遍即可恢复数据，

Binlog格式介绍

Statement,MiXED,以及ROW

using filesort

一般出现在 使用了 order by 语句当中。using filesort不一定引起mysql的性能问题。但是如果查询次数非常多，那么每次在mysql中进行排序，还是会有影响的。这里的优化方式是在order by 的字段建立索引。

一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。

1、如果A表TID是自增长,并且是连续的,B表的ID为索引
select * from a,b where a.tid = b.id and a.tid>500000 limit 200;
如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。
select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;