MySQL运维—索引(二)

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

2)一段时间慢：
（1）记录忄曼日，分析slowlog
(2)explain分析SQL的执行计划，有没有走索引，索引的类型情况
（3）建索引，改语句

二、索引优化刚才压力测试的语句

1、查看查询情况，走的全表扫描

2、创建索引

3、执行结果对比，显而易见

三、索引建立原则

1、建表时一定要有主键，如果相关列不可以作为主，做一个无关列

2、选择唯一性素引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可很快的确定某个学生的信息
如果使用姓名的话，可能存在同名现象，从而降低查询速度。

主键索引和唯一键索引，在查询中使用是效率最高的。

　　select count(*) from world.city：
　　select count(distinct countrycode)from world.city;
　　select count(distinct countrycode，population) from world.city;

注意：如果重复值较多，可以考虑采用联合索引

3、为经常需要where、ORDERBY、GROUPBY,join in 等操作的字段，排序操作会浪费很多时间。
如果为其建立索引，可以有效地避免排序操作。

4、为常作为ere查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。
为这样的字段建立索引，可以提高整个表的查询速度。
4.1经常查询
4.2列值的重复值少（业务层面调整）
注：如果经常作为条件的列，重复值特别多，可以建立联合索引

5、尽量使用前缀来素引
如果索引字段的值很长，最好使用值的前缀来索引。

—以上的是重点关注的，以下是能保证则保证的—-

6、限制索引的数目
素引的数目不是越多越好。每个素引都需要占用磁盘空间，素引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

7、删除不再使用或者很少硬的素引(percona tootkit)
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

8、大表加索引，要在业务不繁忙期间操作

9、少在经常更新值的列上建索引
建索引原则
（1）必须要有主键，如果没有可以做为主键条件的列，创建无关列
（2）经常做为where条件列 order by group by join on的条件（业务：产品功能+用户行为）
（3）最好使用唯一值多的列作为索引，如果索引列重复值较多，可以考虑使用联合索引
（4）列值长度较长的索引列，我们建议使用前缀索引，
（5）降低索引条目，一方面不要创建没用索引，不常使用的索引清理，perconatoolkit
（6）索引维护要避开业务繁忙期

四、不走索引的情况
1、不走索引的情况（开发规范）
重点关注：

（1）没有查询条件，或者查询条件没有建立索引

　　　　select * from tab;全表扫描：
　　　　select * from tab where1=1；

在业务数据库中，特别是数据量比较大的表：

是没有全表扫描这种需求。
（2）对用户查看是非常痛苦的：
（3）《对服务器来讲毁灭性的0
（4）select*fromtab，

SQL改写成以下语句：

　　select * from tab order by price limit 10      需要在price列上建立索引
    setect * from tab where name='zhangsan'        name列没有索引

改：
1)换成有索引的列作为查询条件
2)将name列建立索引

2、查询结果集是原表中的大部分数据，应该是25％以上。
查询的结果集，超过了总数行数25‰优化器觉得就没有必要走索引了。
假如：tab表文id，name id:1-100w,id列有素引

      setect * from tab where id>50000;

如果业务允许，可以使用lim文t控制。
怎么改写？
结合业务判断，有没有更好的方式：如果没有更好的改写方案
尽量不要在mysql放这个数据了。放到redis里面。

3、索引本身失效，统计数据不真实
索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引夫效。

4、查询条件使用函数在索引列上，或者对索引列进行运算，运算包括（+，
例子：

错误的例子：setect * from test where id-1=9;
正确的例子：select * from test where id=10;

算术运算
函数运算
子查询
5|隐式转换导致索．引．夫效《这一点应当讲起重视杰也是开发中经常会犯的错、
这样会导致索引失效，错误的例子：

6、<>,not in不走索引

EXPLAIN SELECT * FROM tettab WHERE telnum <> '10';
EXPLAIN SELECT * FROM teltab WHERE telnum NOT IN（'110','119');
select * from tab where telnum <> '1555555';
explain select * from tab where telnum <> '1555555';

单独的>，<,in有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或in尽量改成union

EXPLAIN SELECT * FROM tettab WHERE tetnum IN（'110','119');
改写成：
EXPLAIN SELECT * FROM teltab WHERE telnum = '110'
UNION ALL
SELECT * FROM tettab WHERE telnum='119';

7、like “%_”百分号在最前面不走

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'; 走range索引扫描
EXPLAIN SELECT * FROM tettab WHERE tetnum LIKE '%110'; 不走索引

％linux％类的搜索需求，可以使用etasticsearch专门做搜索服务的数据库产品

8、单独引用联合索引里韭第一位置的素引列，作为条件查询时不走索引，
列子：
复合索引：

DROP TABLE t1
CREATE TABL tl（id INT,NAME VARCHAR(20)，age INT,sex ENUM('m','f'),money INT);
ALTER TABLE tl ADD INDEX tl_idx(money,age,sex)；
DESC tl
SHOW INDEX FROM tl

走索引的情况测试：

EXPLAIN SELECT NAME,age,sex,money FROM tl WHERE money=30 AND age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM tl WHERE money=30 AND age=30;
EXPLAIN SELECT NAME,age,sex,money FROM tl WHERE money=30 AND sex='m'; --->部分走索引
不走索引的：
EXPLAIN SELECT NAME,age,sex,money FROM tl WHERE age=20
EXPLAIN SELECT NAME,age,sex,money FROM tl WHERE age=30 AND sex='m'；
EXPLAIN SELECT NAME,age,sex,money FROM tl WHERE sex='m';

总之：任何一个不以第一索引列做为查询条件的setect，都是不走索引的