mysql 索引（二）

表 索引 语句 索引 事务 主从

show process list—>临时表(group by order by )

表（常用 or 不常用  定长 or 变长  节约 tinyint 0-255岁）

索引：

+独立索引，只能使用一个 

+ 多列索引，><和like之后的and 

+ 场景索引：复合索引 (cate_id,price)

+覆盖索引（using index，走索引，不用回行查找）

+聚族 or  非聚族：既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”

myisam 依赖行号  innodb 数据行放入主键，不连续会产生索引页的分裂

+理想索引：区分度 和 长度

select count(distinct left(word,6))/count(*) from dict;

+延迟关联：select a.* from it_area as a inner join (select id from it_area where name like '%东山%') as t on a.id=t.id 

+排序：覆盖索引取出来就是有序的 or  取出来后形成临时表再通过filesort排序 =>争取取出来就是有序的

比如: goods商品表, (cat_id,shop_price)组成联合索引,

where cat_id=N order by shop_price ,可以利用索引来排序,

select goods_id,cat_id,shop_price from goods order by shop_price;

// using where,按照shop_price索引取出的结果,本身就是有序的.

select goods_id,cat_id,shop_price from goods order by click_count;

// using filesort 用到了文件排序,即取出的结果再次排序

+重复索引, 去掉.

+冗余索引: x m xm mx

+索引碎片与维护 alter table xxx engine innodb   optimize table 表名 ,也可以修复.

sql语句：等待 + 执行（查 取）

优化：explain

--type列: 是指查询的方式, 非常重要,是分析”查数据过程”的重要依据

all:  意味着从表的第1行,往后,逐行做全表扫描.,运气不好扫描到最后一行.

index: 比all性能稍好一点，（２种情况可能出现：索引覆盖但是又必须全索引扫描 or 是利用索引来进行排序,但取出所有的节点）

range: 意思是查询时,能根据索引做范围的扫描

ref  意思是指 通过索引列,可以直接引用到某些数据行

eq_ref 是指,通过索引列,直接引用某1行数据，常见于连接查询中

const, system, null  这3个分别指查询优化到常量级别, 甚至不需要查找时间.

--extra:

using index: 是指用到了索引覆盖,效率非常高

using where 是指光靠索引定位不了,还得where判断一下 

using temporary 是指用上了临时表, group by 与order by 不同列时,或group by ,order by 别的表的列.

using filesort : 文件排序(文件可能在磁盘,也可能在内存), (

select sum(shop_price) from  goods group by cat_id(这句话,用到了临时表和文件排序)

--语法：

in(先外后内)==>join(先内后外)

from子查询（内层返回要尽量少，因为有了临时表）

count 别用where

min/max mysql内部已优化

group by 用于分组而不是筛选

union+all，不要去重

limit：limit offset,N,  取offset+N行,返回放弃前offset行,返回N行.

 

优化办法:

从业务上去解决:不允许翻过100页

or

不用offset,用条件查询.

mysql> select id,name from lx_com limit 5000000,10;

mysql> select id,name from lx_com where id>5000000 limit 10;

问题: 2次的结果不一致

原因: 数据被物理删除过,有空洞.

解决:一般来说,大网站的数据都是不物理删除的,只做逻辑删除 ,比如 is_delete=1

 

非要物理删除,还要用offset精确查询,还不限制用户分页,怎么办?

分析: 优化思路是 不查,少查,查索引,少取.  这种技巧就是延迟索引.

mysql> select id,name from lx_com inner join (select id from lx_com limit 5000000,10) as tmp using(id);

事务和隔离性：

原子性: 是指某几句sql的影响,要么都发生,要么都不发生.

一致性: 事务前后的数据,保持业务上的合理一致.

隔离性:其他事务,看不到此事务的任何效果.

持久性: 事务一旦发生,不能取消. 只能通过补偿性事务,来抵消效果.

 

read uncommitted: 读未提交的事务内容,显然不符原子性, 称为”脏读”.

read commited: 读不到另一个进行事务的操作,但是,可以读到另一个结束事务的操作影响.

repeatable read: 可重复读,即在一个事务过程中,所有信息都来自事务开始那一瞬间的信息,

serializeable 串行化 ,隔离的级别最高事务必须编号,事务相互等待的等待长

主从：

步骤:binlog relay-log  grant  changemaster  starslave

状态：

show master status ; 查看master的状态, 尤其是当前的日志及位置

show slave stattus; 查看slave的状态.

reset slave ;  重置slave状态.

start slave ; 启动slave 状态(开始监听msater的变化)

stop slave; 暂停slave状态;

多主：offset increment ==> redis incrby 

原则：最左前缀—>顺序 abc= a or ab or abc

匹配类型：全值 最左前缀 列前缀  范围值 精确匹配某一列并范围匹配另外一列 只访问索引的查询，同时，支持order by排序

如果不是按照索引最左列开始查找，无法使用索引

不能跳过索引中的列

某个列进行范围查询，右边的列无法使用索引

如果查询中只有索引包含的列，那么就可以使用索引直接在引擎读取数据行，引擎不用再返回数据行给mysql服务器了

--索引扫描排序

只有当索引的列顺序和order by子句顺完全一致，且所有列的排序方向一致时，才行。索引排序也需要满足最左排序。

如果多张表关联，那么只能用到第一张表的索引。

有一种例外情况，就是覆盖索引，如果有一个常量在where子句，那么可以和order by合并成覆盖索引。

--无法覆盖索引的情况有，不在索引中的列，无法组合成索引的最左前缀，排序方向不一致，有范围查询导致后面索引用不上