MySQL数据库优化及实战

1.索引

索引是一种数据结构，有序的、能快速查找的数据结构，B+树。使用索引能提高查找的效率，类似字典的目录。

      优势：提高数据检索的速度，降低数据库的IO成本；降低数据库排序的成本，减少CPU的消耗。

      劣势：占用内存空间、降低更新表的速度。

   1.1 什么时候建索引？

   1.主键自动建立唯一的索引。2.频繁的查询条件。3.与其它表有关联的字段（外键）。4.需要统计或者分组的字段。

   1.2 什么时候不需要建索引？

   1.表太少。2.经常增删改的表。3.数据重复而且分布平均的字段。

//给user表里面的name属性建索引(单值索引)
create index idx_user_name on user(name) 

//给user表里面的name属性建索引(复合索引)
create index idx_user_nameEmail on user(name,email) 

---

2.explain

explain各字段解析：

*（1）id: 表示select子句的执行顺序或操作表的顺序。

         有以下三种情况：1.id相同。执行顺序从上往下。2.id不同。id越大执行的优先级就越高，如果是子查询，id序号会递增。3.部分id相同，部分id不相同。id最大的先执行，然后顺序执行相同的id。

 （2）selete_type：sql语句的查询类别。simple（普通查询）、primary（主查询）、subquery（子查询）、derived（衍生）、union、union result

 （3）table: 数据属于哪张表。

*（4）type：访问的类别排列。类型从最好到最差依次为：system > const > eq_ref > ref > range > index > all（常见的类型）

system	一个表而且只有一行数据，相当于系统表；
const	通过索引一次就能找到数据，是一个常量；
eq_ref	常用于主键或唯一索引扫描，只能有一个唯一的结果；
ref	非唯一索引扫描，能有多个匹配的结果；
range	只检索给定范围的行；
index	扫描全部的索引 ；
all	全表扫描；

   ps：一般要将type优化到range级别，最好是ref级别。

 （5）possible_keys:  可能用在表上的索引，一个或多个，但实际上不一定被使用。

*（6）key：实际上使用的索引。

 （7）key_len:  索引的最大可能长度，并非实际使用长度。在不损失精度的情况下(可以理解为查询条件)，长度越短越好。

 （8）ref: 显示哪一列被使用了。

*（9）rows: 找到所需记录所需要读取的行数。

*（10）Extra: 十分重要的额外信息。

Using filesort	mysql数据库会使用外部排序的索引排序，而不是按照表内的索引顺序进行读取。 “九死一生”，需尽快修改
Using temporary	使用了临时表保存中间结果。在group by和order by上比较常见。 "必死无疑"，严重影响效率，必须赶紧优化
Using index	select操作使用了覆盖索引，避免访问了表的数据行。出现using where，表明索引被用来查找索引键值。没有，则表明索引用来读取数据而非执行查找动作 。做得不错
using where	表明用了where过滤。
using join buffer	使用了连接缓存。
impossible where	where条件的值为false，筛选条件不可成立。

覆盖索引：所查字段的顺序和个数和索引相同。使用覆盖索引时，要select所需的列，不可select *。

---

3.SQL优化实战：

1.单表查询

author_id=1，comments>1，views最多的author_id（）

acticle表结构：

原SQL语句：

EXPLAIN SELECT
	id,
	author_id 
FROM
	article 
WHERE
	category_id = 1 
	AND comments > 1 
ORDER BY
	views DESC 
	LIMIT 1;

执行结果： 

建立索引:

CREATE INDEX idx_article_cv ON article ( category_id, views );

SHOW INDEX FROM article;

建立索引后的结构：

执行结果：

ps:如果如下这样建立索引的话：

create index idx_article_ccv on article(category_id,comments,views);

这中建立索引的方式并不是很好。首先要明确的是，BTree的索引原理是先排序category_id，再到comments，最后才是views。因为comments>1是一个范围值，而且处于索引的中间位置，无法利用后面的views索引，所以优化提升不大。

2.双表查询

表结构：

                       

SQL语句：

EXPLAIN SELECT
	* 
FROM
	class
	LEFT JOIN book ON class.card = book.card

ps：左连接用于确定如何从右表搜索行，左边一定都有，右边是关键点，一定要建立索引。

执行结构：

建立索引：

alter table `book` add INDEX Y (`card`)

执行结果：

3.三表查询

表结构：

              

SQL语句：

EXPLAIN SELECT
	* 
FROM
	class c
	INNER JOIN book b ON c.card = b.card
	INNER JOIN phone p ON b.card = p.card

执行结果：

ps：确保全部表的索引都删除

建立索引：

alter table `phone` add index z (`card`);
alter table `book` add index k (`card`);

执行结果：

Join语句的优化：

1.永远用小表驱动大表。2.优先优化内层循环。3.保证Join条件字段已经被索引。

---

4.索引失效的情况：

首先要知道一个法则，索引的最佳左前缀法则，其内容为：如果有多个索引，要从最左边的索引开始查询，不能跳过前面的索引而直接使用后面的索引。举个例子，前面的索引就像是一楼，后面的索引就是二楼以上，不可能不走一楼就能上二楼及其以上的楼层。（这里别钻牛角尖，懂的都懂哈。）

      1.不遵守最佳左前缀法则，索引会失效。

      2.在索引列进行计算、使用函数和类型转换都会使索引失效，从而变成全表扫面。

      3.由于最后的筛选条件前面的条件由确定变成了一个范围，所以导致最后一个索引失效了，根本没用上。比如，name、age and address都是索引，原来的查询条件是name='z3' and age=13 and address='gz' 变成了name='z3' and age>13 and address='gz，此时索引aaddress就失效了，虽然此时可能还是能查到正确的数据。

      4.在where后面使用了不等于号，导致全表扫描。

      5.使用了is null,is not null，无法使用索引。 ps:使用is not null的效率比is null高。

      6.like的%要写在右边才能避免全表扫描，比如nama like 'jacky%'，%放在其它位置会导致索引失效。ps:如果不使用like就不能查询出正确结果的话，那么就得使用覆盖索引。

      7.字符串类型(varchar)不加单引号。虽然mysql会隐式地帮你做类型转换，但是会导致全表扫描，这里要联系上上面第二点中的类型转换会导致全表扫描。

      8.用or。 尽量少用

ps：不要因为索引失效而不去使用某些方法，要具体情况具体分析，由需求决定。

---

5.查询优化：

优化原则：小表驱动大表，即用小的数据集驱动大的数据集。

select * from A where id in (select id from B)
等价于
for select id from B
for select * from A where A.id = B.id
----------------------------------------------------------------
select * from A where exists (select 1 from B where B.id = A.id)
等价于
for select id from A
for select * from B where B.id = A.id

select ... from table where exists (subquery)
:在子查询中根据条件验证主查询的数据，根据认证结果（true或false）来决定是否保留主查询的数据。

当B表的数据集小于A表的数据集时，用in优于exists。反之，是exists优于in。

ps:A表和B表的ID字段应建立索引。

提高order by和group by的速度：

（1）不用select *   (2)尝试提高sort_buffer_size    (3)尝试提高max_length_for_sort_data

ps：group by的实质是先排序后分组，按照索引建的最佳左前缀。能用where就不用having。

---

6.show profile

作用：分析执行当前SQL语句的资源消耗情况

1.查看当前数据库是否支持：show VARIABLES like 'profiling'

2.开始: set profiling = on

若status中出现了：converting HEAP to MyISAM （查询结果太大，内存不够用，往磁盘转移）、creating tmp table（创建临时表）、copying to tmp on disk（把内存中的临时表复制到磁盘）和locked时，需注意，得优化。

加读锁和写锁: 对MyISAM表加读锁会阻塞写，但是不会阻塞读。而加写锁会阻塞读和写。

ps:MyISAM的读写调度是写优先，所以不适合当主表的引擎。因为写锁之后，其它线程不能做任何操作，大量地更新会导致很难得到锁，从而造成永远阻塞。

InnoDB和MyISAM的最大不同：支持事务和采用行级锁。

如何锁定一行：

begin;
select * from xxx where a=1 for update;
commint;