MySQL随手记

一、MySQL数据迁移(由远端主机迁移到本地)

1、导出数据库
mysqldump -u root -p db > dump_db_date.sql
root: 账户
db: 需要导出的数据库名

2、将导出的dump_db_date.sql文件scp到本地

3、在本地机器建立新数据库
mysql > create database new_db;

4、导入数据
mysql -u root -p new_db < dump_db_date.sql

 

二、索引

InnoDB引擎本质上是一种B+树结构。传送门：从B树、B+树、B*树谈到R 树。

1、聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。索引的顺序就是数据存放的顺序，所以，很容易理解，一张数据表只能有一个聚簇索引。

聚簇索引要比非聚簇索引查询效率高很多，特别是范围查询的时候。具体细节依赖于其实现方式，InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

 

主键的顺序就是实际数据存放的顺序。因此，按主键进行范围查询的时候，效率会高很多。

2、查询效率？

假设有张表有2个索引，主键作为聚簇索引，还有一个二级索引。聚簇索引的B+树高度为h1，二级索引的B+树高度为h2。

那么通过主键来查询，时间复杂度为O(h1)；通过二级索引来查询，时间复杂度为O(h1+h2)。原因在于，在InnoDB引擎中，二级索引的叶子节点中存储的不是行指针，而是主键值。

这样做的好处是当移动行的时候无须更新二级索引中的指针，减少了行移动或者数据页分裂时的维护工作。

3、采用自增类型还是uuid作为主键？

自增类型作为主键的好处：一是长度短，节省空间，尤其是在表中有很多二级索引的情况下；二是索引顺序和实际数据存放顺序一致，insert操作总是插入到索引的最后，和uuid相比，避免了大量的为新的行寻找合适位置插入以及频繁的页分裂操作。既节省空间又速度快。

自增作为主键的坏处：对于高并发工作负载，瞬时插入大量数据，会造成明显的锁竞争，而uuid则不存在这样的问题。另外，在分布式架构中，采用自增作为主键有个主键全局唯一性问题(uuid也有极小概率会冲突)。

4、使用索引扫描来做排序的条件

1）只有当索引列顺序和order by子句的顺序完全一致，并且所有列的排序方向都一样时；

2）如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一张表时；

3）需要满足索引的最左前缀的要求，除非前导列为常量。

比如：

create table rental(

　　...

　　unique key rental_date(rental_date, inventory_id, customer_id),

);

select * from rental order by rental_date, inventory_id;　　//符合，满足最左前缀要求

select * from rental where rental_date='2016-10-30' order by inventory_id, customer_id;　　//符合，前导列为常量

select * from rental order by rental_date, customer_id;　　//不符合，不满足最左前缀要求

select * from rental where rental_date in ('2016-10-28', '2016-10-30') order by inventory_id;　　//不符合，前导列为范围

 

lyhabc的Mysql总结: http://www.cnblogs.com/lyhabc/category/573945.html