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

?

1 2 3 4 5 6 7 8 9 10 11 12 13

#统计每个库的数据量大小，并按数据量从大到小排序 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

（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 语句

?

1 2 3 4 5 6

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

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;<em id="__mceDel" style="background-color: rgba(255, 255, 255, 1); font-family: "PingFang SC", "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 14px">　</em>

　　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) 联合索引