MySQL 索引及执行计划

补充： information_schema的基本应用 ***

tables 视图的应用
mysql> use information_schema;
mysql> desc tables;

TABLE_SCHEMA 表所在的库名
TABLE_NAME 表名
ENGINE 存储引擎
TABLE_ROWS 数据行
AVG_ROW_LENGTH 平均行长度
INDEX_LENGTH 索引长度

显示所有的库和表的信息
SELECT table_schema,table_name FROM information_schema.tables;

-- 表的数据量=平均行长度*行数+索引长度
-- AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH

#统计每个库的数据量大小，并按数据量从大到小排序
SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 AS total_KB
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY total_KB DESC ;

#模仿以下语句,进行数据库的分库分表备份。
mysqldump -uroot -p123 world city >/bak/world_city.sql

SELECT
CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name
," >/bak/",table_schema,"_",table_name,".sql")
FROM information_schema.tables;

　　

show介绍*****
show databases; 查看数据库名
show tables; 查看表名
show create database xx; 查看建库语句
show create table xx; 查看建表语句
show processlist; 查看所有用户连接情况
show charset; 查看支持的字符集
show collation; 查看所有支持的校对规则
show grants for xx; 查看用户的权限信息
show variables like '%xx%' 查看参数信息
show engines; 查看所有支持的存储引擎类型
show index from xxx 查看表的索引信息
show engine innodb status\G 查看innoDB引擎详细状态信息
show binary logs 查看二进制日志的列表信息
show binlog events in '' 查看二进制日志的事件信息
show master status ; 查看mysql当前使用二进制日志信息
show slave status\G 查看从库状态信息
show relaylog events in '' 查看中继日志的事件信息
show status like '' 查看数据库整体状态信息

BTree索引的分类(算法)

 

1- 索引及执行计划

1.1 -索引的命令操作

 

（1）查询索引 
	desc stu;
	PRI   ==> 主键索引 
	MUL   ==> 辅助索引
	UNI   ==> 唯一索引 
	mysql> show index from stu\G

（2）创建索引

	单列的辅助索引:
	alter table city add index idx_name(name);

	多列的联合索引:
	 alter table city add index idx_c_p(countrycode,population);

	唯一索引:无重复的值 
	alter table city add unique index uidx_dis(district);

	前缀索引
	alter table city add index idx_dis(district(5));

（3）删除索引 
	 alter table city drop index idx_name;
	 alter table city drop index idx_c_p;
	 alter table city drop index idx_dis;

	压力测试：
	mysqlslap --defaults-file=/etc/my.cnf \
	> --concurrency=并发量 --iterations=测试次数 --create-schema='test' \
	> --query="查询语句'" engine=innodb \
	> --number-of-queries=查询次数 -uroot -p123 -verbose

　　

2- 执行分析计划
（1）作用
　　将优化器 选择后的执行计划 截取出来.便于管理管判断语句得执行效率.
　　获取执行
　　desc SQL语句
　　explain SQL 语句

	mysql> desc select * from test.t100w where k2='MN89';
	+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
	| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
	+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
	|  1 | SIMPLE      | t100w | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1027638 |    10.00 | Using where |
	+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+

　　

2）分析执行计划
　　table 表名

　　type
　　查询的类型:
　　全表扫描 : ALL
　　索引扫描 : index,range,ref,eq_ref,const(system),NULL

index: 全索引扫描
mysql> desc select countrycode  from city;

range: 索引范围扫描(> < >= <= , between and ,or,in,like )
mysql> desc select * from city where id>2000;
mysql> desc select  * from city where countrycode like 'CH%';

对于辅助索引来讲,!= 和not in等语句是不走索引的
对于主键索引列来讲,!= 和not in等语句是走range
===
mysql> desc select  * from city where countrycode='CHN' or countrycode='USA';
mysql> desc select  * from city where countrycode in ('CHN','USA');

一般改写为 union all 
desc 
select  * from city where countrycode='CHN' 
union all 
select  * from city where countrycode='USA';

ref: 辅助索引等值查询
desc 
select  * from city where countrycode='CHN' 
union all 
select  * from city where countrycode='USA';

eq_ref : 多表连接时,子表使用主键列或唯一列作为连接条件
A join B 
on a.x = B.y 

desc select b.name,a.name ,a.population  
from city as a 
join country as b 
on a.countrycode=b.code 
where a.population<100;

const(system) : 主键或者唯一键的等值查询
mysql> desc select * from  city where id=100;　

　　possible_key:可能会用到的索引
　　key: 真正选择了哪个索引
　　key_len 索引覆盖长度
　　varchar(10) : 没有not null(1)+4*10+2(起始和结束)=43
　　char(10) : 没有not null(1)+4*10=41
　　int : 没有有not null(1)+4=5
　　Extra

（3）联合索引应用细节优化
　　只要我们将来的查询,所有索引列都是<等值>查询条件下,无关排列顺序 ，唯一值多的列放在最左侧

　　在where查询中如果出现> < >= <= like时，放在条件的最后
　　mysql> desc select * from test where k1='aa' and k3='aaaa' and k4='中国你好' and k2>'中国';
　　mysql> alter table test add index idx1(k1,k3,k4,k2);

例子： explain(desc)使用场景
你做过哪些优化?
你用过什么优化工具?
你对索引这块怎么优化的?

题目意思: 我们公司业务慢,请你从数据库的角度分析原因
1.mysql出现性能问题,我总结有两种情况:
　　（1）应急性的慢：突然夯住
　　　　应急情况:数据库hang(卡了,资源耗尽)
　　　　处理过程:
　　　　1.show processlist; 获取到导致数据库hang的语句
　　　　2. explain 分析SQL的执行计划,有没有走索引,索引的类型情况
　　　　3. 建索引,改语句
　　（2）一段时间慢(持续性的):
　　　　(1)记录慢日志slowlog,分析slowlog
　　　　(2)explain 分析SQL的执行计划,有没有走索引,索引的类型情况
　　　　(3)建索引,改语句

3- 索引应用规范
---- 建立索引的原则（DBA运维规范）
　　(1) 建表必须要有主键,一般是无关列,自增长
　　(2) 经常做为where条件列 order by group by join on, distinct 的条件
　　(3) 最好使用唯一值多的列作为联合索引前导列,其他的按照联合索引优化细节来做
　　(4) 列值长度较长的索引列,我们建议使用前缀索引.
　　(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
　　(6) 索引维护要避开业务繁忙期
　　(7) 小表不建索引

---- 不走索引的情况（开发规范）
　　(1) 没有查询条件，或者查询条件没有建立索引
　　select * from city;
　　select * from city where 1=1;
　　(2) 查询结果集是原表中的大部分数据，应该是25％以上。
　　(3) 索引本身失效，统计数据不真实
　　面试题:同一个语句突然变慢?
　　统计信息过旧,导致的索引失效
　　(4) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
　　mysql> desc select * from city where id-99=1;
　　(5) 隐式转换导致索引失效.
　　(6) <> ，not in 不走索引（辅助索引）
　　(7) like "%aa" 百分号在最前面不走
　　(8) 联合索引