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;