MySQL学习笔记二

一、MySQL索引

MySQL索引的好处：如果正确设计并使用索引，MySQL会是一辆跑车，不用索引的MySQL就是一辆三轮车。对于没有索引的表，单表查询，可能十几万数据就是瓶颈，而大型网站每天可能产生几十万甚至上百万数据，没有索引查询会变的很慢。

1. 索引的概念

索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一部分），索引包含这对数据表里所有记录的引用指针。更通俗地说，所以好比是一本书前面的目录，能加快数据库的查询速度。

索引分为聚簇索引和非聚簇索引。聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引则不是。所以聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索快。InnoDB支持聚簇索引，而MyISAM不支持。

2. 索引的类型

索引是在存储引擎层实现的，而不是服务器层实现的，所以不是所有的存储引擎都支持所有的索引类型。

1）BTree索引

MyISAM和InnoDB默认的索引是BTree索引

例子：create table people (
last_name varchar(50) not null,
first_name varchar(50) not null,
dob date not null,
gender enum(‘m’, ‘f’) not null,
key(last_name, first_name, dob)
);

索引中的值按索引列中的顺序排列。B-Tree索引可进行全字查询、关键字范围查询、关键字前缀查询。

（1）匹配全值：对索引中的所有的列都指定具体的值。如查询1960-01-01出生的Cuba Allen.
（2）匹配最左前缀：查找last_name为Allen的人
（3）匹配列前缀：查找last_name以J看头的人
（4）匹配值的范围：查找last_name在Allen和Barry之间的人。
（5）部分精确匹配，部分范围查找：查找last_name为Allen, 而fist_name以K开头的人。

2）唯一索引 ： Unique index

索引的列不允许重复，但允许有空值（这与主键不同），如果是组合索引，那么列值的组合必须唯一。

3）全文索引 ： FullText

FullText索引仅可用于MyISAM引擎，但对于大容量的数据库表，生成全文索引是非常耗费时间和空间的。

4）单列索引、多列索引

多个单列索引和单个多列索引的查询效果不同。因为执行查询时，MySQL只能使用一个索引。
例如：存在组合索引 index_t1(c1, c2），那么对于查询语句：
select * from t1 where c1=1 and c2=2;
能使用该索引，对于查询语句：
select * from t1 where c1=1；
也能够使用该索引，但对于查询语句：
select * from t1 where c2=2;
则不能使用该索引。因为没有组合索引的引导列c1.

3. 如何选择用于索引的列的类型

1）越小的数据类型越好，因为越小的数据类型在磁盘、内存、CPU缓存中都处理更快。
2）越简单的数据类型越好，如整型好于字符型；用时间戳而不是字符串来存储时间；用整型存储IP地址。
3）尽量避免NULL. 在MySQL中，含有NULL的类很难查询优化，因为他们使得索引的运行更复杂。可使用0或者一个特殊的值或空字符串代替NULL.

4. 索引与排序

利用索引进行排序非常快，如order by, group by. 若其作用的列是索引，则会非常快。如果其作用的列不是索引，MySQL会利用自己的快速排序算法在内存（sort buffer）总对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序）。

二、MySQL引擎

1. InnoDB引擎

优点：
1）支持事务
2）提供了行级锁、外键约束
3）设计目标是处理大容量的数据库
4）支持故障恢复

缺点：
1）不支持FullText索引
2）没有保存表的行数，select count(*) from table时，会扫描全表。

2. MyISAM引擎

优点：
1）支持FullText索引
2）存储了表的行数，select count(*) from table时，不必扫描全表。

缺点：
1）不支持事务
2）不支持行锁和外键约束，因此insert, update时，要锁定整张表，效率会低。
3）无法故障恢复

3. 两种存储引擎的选择

MySQL默认的存储引擎为Innodb, 当处理数据量大时，要用Innodb, 因为他支持事务，且可灾难恢复。仅当数据量小，且读操作远大于写操作，且不要求事务时，可考虑MyISAM.

三、MySQL查询优化

1. 确保查询缓存开启

MySQL默认都开启了查询缓存，即查询结果对于下次查询时可复用，不用再次查询。

但有些函数默认关闭查询缓存，如：curdate(), now(), rand()等

例如：select username from user where maytime <= curtime(); // 关闭查询缓存

类似的函数都不会开启查询缓存，所以要使用一个变量来替代函数。如：
$today=date(“Y-m-d”);selectusernamefromuserwheremaytime>=‘$today’;

2. 通过explain，优化select查询

explain可以知道MySQL是如何执行SQL的，索引是如何被使用的，数据库表是如果排序的…

3. 当只需要一行数据时，使用limit 1

例如：查询是否有中国的用户：
select * from user where country = ‘China’;
可以写出：
select * from user where country = ‘China’ limit 1;
在使用limit 1后，在查到一条后，会停止查找。

4. 为经常所有的列建立索引。

5. 两个表做连接时，连接的列的类型要相同，且使用索引。

例如：select name from A left join B on (A.state = B.state) where A.userId=‘abc’;
这里，A.state和B.state的类型最好相同，且这两个列最好都创建索引。

6. 不要用order by rand()

例如：随机挑选一条记录：
select username from user order by rand() limit 1; // 不要这样做。
这样做的话，MySQL会先执行rand()函数，这很耗费CPU时间，然后再为每行记录排序，即使Limit 1 也无济于事。

推荐这样写：即通过查询max(id) * rand()来随机获取数据：
select * from user where userId >= (
(select max(userId) from user) - (select min(userId) from user))
) * rand() limit 1;