Mysql数据库的基本操作

1、复制表结构及数据到新表（不包含主键、索引、分区等）

CREATE TABLE 新表 SELECT * FROM 旧表

或 CREATE TABLE 新表 AS SELECT * FROM 旧表

这种方法将旧表基本结构和数据复制到新表。

不过这种方法的一个最不好的地方就是新表中没有了旧表的主键、索引、Extra（auto_increment，字符集编码及排序）、注释、分区等属性 以及触发器、外键等。

2、只复制表结构到新表

CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2 （只是第一种方式去除掉数据）

CREATE TABLE 新表 LIKE 旧表

这种方式的复制可以复制旧表的主键、索引、Extra（auto_increment，字符集编码及排序）、注释、分区等属性。但是不包含触发器、外键等

3、复制旧表的数据到新表

INSERT INTO 新表 SELECT * FROM 旧表

INSERT INTO 新表(字段1,字段2,…) SELECT 字段1,字段2,… FROM 旧表

上面两条语句的前提是新表已经存在

4、复制表结构及数据到新表（包含主键、索引、分区等）

结合上述第2、3点，即：

先 CREATE TABLE 新表 LIKE 旧表

然后 INSERT INTO 新表 SELECT * FROM 旧表

5、可以将表1结构复制到表2（mysql不支持）

SELECT * INTO 表2 FROM 表1 WHERE 1=2

6、可以将表1内容全部复制到表2（mysql不支持）

SELECT * INTO 表2 FROM 表1

7、 show create table 旧表;

这样会将旧表的创建命令列出。我们只需要将该命令拷贝出来，更改table的名字，就可以建立一个完全一样的表

8、查看表索引
show index from table_name（表名）

使用索引查询时的正确命中索引 查询

mysql,索引命中，查询级别
mysql索引无法命中情况

1，查询占表数据30%以上

2，小表查询

3，规定不用索引查询

4，查询条件给字符串加引号用索引，否则不用

5，条件中用到or，除非涉及到的列都有索引否则不命中

6，like 的 %在前不命中，在后命中

sql语句查询的优化

首先，数据量大的时候，应尽量避免全表扫描，应考虑在 where 及 order by 涉及的列上建立索引，建索引可以大大加快数据的检索速度。但是，有些情况索引是不会起效的：

1、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

2、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

3、尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20

4、下面的查询也将导致全表扫描：

select id from t where name like ‘%abc%’

若要提高效率，可以考虑全文检索。

5、in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3

6、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num

7、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2

8、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)=’abc’–name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id
应改为:
select id from t where name like ‘abc%’
select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

9、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

10、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

11、不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(…)

12、很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)

查询时的\G作用
有时候，操作返回的列数非常多，屏幕不能一行显示完，显示折行，试试”\G”，把列数据逐行显示（”\G”挽救了我，以前看explain语句横向显示不全折行看起来巨费劲，还要把数据和列对应起来）。

关于数据库索引的那些事

先说说几个概念

二叉树
二叉树是每个节点最多有两个子节点的树。
二叉树的叶子节点有0个字节点，二叉树的根节点或者内部节点有一个或者两个字节点。

二叉搜索树
二叉查找树又叫二叉搜索树，
它或者是一棵空树，或者是具有下列性质的二叉树：
若它的左子树不空，则左子树上所有结点的值均小于它的根结点的值；
若它的右子树不空，则右子树上所有结点的值均大于它的根结点的值；
它的左、右子树也分别为二叉搜索树。

平衡二叉树
所有节点的左右子树的高度差小于1的二叉树。
如下图
根节点左边高度是3，因为左边最多有3条边；右边高度而2，相差1.
根节点左边的节点50的左边是1条边，高度为1，右边有两条边，高度为2，相差1。

B树 后面有介绍
B+树 后面有介绍

索引一般在sql语句查询较慢时使用，即：
查询较慢时，可以相关字段建立索引。
索引查询较快的原理：
分为hash索引和B+树索引
B+树查询时间和树的高度成正比。大概是log（n）
hash时间一般为O（1）

这里引进一个二叉树概念，即 左侧比根节点小，右侧比根节点大并且子树都是二叉树。当插入数据为有序序列，二叉树就退化为链表。所以才有了平衡二叉树，就是为了在插入时调整这棵树，让他的节点尽可能均匀分布。而树的查找性能取决于树的高度，尽可能让树平衡就是为了降低树的高度。

B树：
B树是一个多路搜索树，他的每个节点可以拥有多于两个孩子节点。M路的B树最多能拥有M个孩子节点。一般较多适用于文件系统的索引。

文件系统的索引多用B树而不是红黑树或有序数组的原因就是:文件系统和数据库索引都是存于磁盘上的，如果文件较大的话，并不能保证文件一次性能载入内存。使用B树就可以一次加载一个节点就行。

如果在内存中，使用红黑树效率高于B树，在磁盘中，B树要高于红黑树。

B+树：
B+树就是在B树的基础上进行改造，他的数据都在叶子节点，同时叶子节点之间还加了指针形成链表。下图就是一个4路B+树：

一般在查询时会使用到where子句中的范围查询，相对于B树要做局部的中序遍历，还要跨层查询，而B+树由于所有数据都在叶子结点，不用跨层，同时由于有链表结构，只需要找到首尾，通过链表就能把所有数据取出来了。

如果只选一个数据，那确实是hash更快。但是数据库中经常会选择多条，这时候由于B+树索引有序，并且又有链表相连，它的查询效率比hash就快很多了。

总结
1、相同思想和策略

从平衡二叉树、B树、B+树、B*树总体来看它们的贯彻的思想是相同的，都是采用二分法和数据平衡策略来提升查找数据的速度；

2、不同的方式的磁盘空间利用

不同点是他们一个一个在演变的过程中通过IO从磁盘读取数据的原理进行一步步的演变，每一次演变都是为了让节点的空间更合理的运用起来，从而使树的层级减少达到快速查找数据的目的；