MySQL实战积累

1.mysql两种主流存储引擎：Innodb引擎、MyIASM引擎

  应用场景：

    InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，应该选择InnoDB

    MyIASM管理非事务表，提供高速存储和检索以及全文搜索能力，如果再应用中执行大量select操作，应该选择MyIASM

   mysql部分系统表采用MyIASM存储引擎

2.通过事物批量插入数据

?

1 2 3 4

List<string> sqlList = new List<string>(); string sql = "Insert/Delete/Update ..."; sqlList.Add(sql);//此处拼接增删改语句，通过事物一次提交 MySqlDBHelper.ExecuteTransaction(sqlList);

 3.LOCK TABLE  表名  READ/WRITE      UNLOCK TABLES

4.索引

索引是表的索引目录，在查找内容之前先查目录中查找索引位置，从而快速定位查询数据；可以理解成新华字典中的索引；索引会保存在额外的文件中。

一般的索引种类及功能：

普通索引：仅加速查询

唯一索引：加速查询 + 列值唯一（可以有null）

主键索引：加速查询 + 列值唯一 +　表中只有一个（不可以有null）

组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

全文索引：对文本的内容进行分词，进行搜索

索引合并：使用多个单列索引组合查询搜索

覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

http://www.cnblogs.com/lyhabc/p/3691555.html 

对已有表添加索引

?

1 2 3 4 5 6 7 8

第一种语法： CREATE  索引类型    索引名     ON   表名 (字段名) 第二种语法：  ALTER TABLE 表名 ADD  索引类型   索引名 (字段名)      示例： CREATE INDEX index_emp_name on emp1(name); ALTER TABLE emp2 ADD UNIQUE INDEX index_bank_num(band_num);

删除索引

?

1 2 3 4

语法： DROP INDEX 索引名 on 表名 DROP INDEX index_emp_name on emp1; DROP INDEX bank_num on emp2;

5.show processlist

 6.复制表的内容

?

1	create table emp_bak (select * from emp)

7.查询的顺序如下

?

1	FROM  ,ON ,  WHERE, SELECT ,  GROUP  BY ,  ORDER BY   ,HAVING

8.mysql最大连接数

查看最大连接数

show variables like '%max_connections%';

第一种修改最大连接数（这种方式有个问题，就是设置的最大连接数只在mysql当前服务进程有效，一旦mysql重启，又会恢复到初始状态。因为mysql启动后的初始化工作是从其配置文件中读取数据的，而这种方式没有对其配置文件做更改。）

set GLOBAL max_connections = 200;

第二种通过修改配置文件来修改mysql最大连接数(max_connections)。

这种方式说来很简单，只要修改MySQL配置文件my.ini 或 my.cnf的参数max_connections，将其改为max_connections=1000，然后重启MySQL即可。

9.把password_expired 改成不过期

?

1 2 3

update user set password_expired='N' where user='root'; flush privileges; quit

10.巧用CAS解决数据一致性问题

  高并发“查询并修改”的场景，可以用CAS(Compare and Set)的方式解决数据一致性问题。对应到业务，即在set的时候，加上初始条件的比对。

  并发操作发生时：

  业务1执行 => UPDAtE t_yue SET money=28 WHERE uid=$uid AND money=100

  业务2执行 => UPDAtE t_yue SET money=38 WHERE uid=$uid AND money=100

11.表必备三字段： id, gmt_create, gmt_modified。

    永远为每张表设置一个ID做为其主键，而且最好的是一个INT型的(推荐使用UNSIGNED)，并设置上自动增加的AUTO_INCREMENT标志。就算是你 users表有一个主键叫 “email”的字段，你也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。

12.解决修改datadir路径后无法启动问题

如果在Windows系统中datadir路径后无法启动问题，报错1067错误，可以使用下列方法解决：

在新创建的data文件夹上右键，选择“属性”->“安全”，在组和用户(G)中添加NETWORK SERVICE即可。

13.设计表时不允许空值

     通过声明列“NOT NULL”来实现这一点

14.MySQL中lock tables和unlock tables浅析

15.使用join链接代替子查询