一、索引作用
提供了类似于书中目录的作用,目的是为了优化查询
二、索引的种类(算法)
B树索引 #mysql中最常用的的索引，90%以上
Hash索引
R树
Full text
GIS
B树
B-tree 早期使用，现在不用了
B+Tree 在范围查询方面提供了更好的性能(> < >= <= like)，在b树上进行的优化。主要要明白这个
B*Tree
B+Tree和B-Tree有几点不同：
非叶子节点只存储键值信息
所有叶子节点之间都有一个链指针
数据记录都存放在叶子节点中
三、在功能上的分类
1.辅助索引(S)怎么构建B+树结构的?
(1). 索引是基于表中,列(索引键)的值生成的B树结构
(2). 首先提取此列所有的值,进行自动排序
(3). 将排好序的值,均匀的分布到索引树的叶子节点中(16K)
(4). 然后生成此索引键值所对应得后端数据页的指针
(5). 生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度
depa_id name age gender select * from t1 where idepa_id10; 问题: 基于索引键做where查询,对于depa_id列是顺序IO,但是对于其他列的查询,可能是随机IO.
2 聚集索引
2.1前提
(1)表中设置了主键,主键列就会自动被作为聚集索引.
(2)如果没有主键,会选择唯一键作为聚集索引.
(3)聚集索引必须在建表时才有意义,一般是表的无关列(ID)
2.2 聚集索引(S)怎么构建B+树结构的?
(1) 在建表时,设置了主键列(ID)
(2) 在将来录入数据时,就会按照ID列的顺序存储到磁盘上.(我们又称之为聚集索引组织表)
(3) 将排好序的整行数据,生成叶子节点.可以理解为,磁盘的数据页就是叶子节点
2.3 聚集索引和辅助索引构成区别
聚集索引只能有一个,非空唯一,一般时主键
辅助索引,可以有多个,时配合聚集索引使用的
3.辅助索引细分
1)普通的单列辅助索引
(2)联合索引
多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表查询
联合索引： a)select * from t1 where a= b= 我们建立联合索引时： alter table t1 add index idx_a_b(a,b); alter table t1 add index idx_b_a(b,a); 注意：在这种情况下建索引时，需要考虑哪个列的唯一值更多，哪个放在索引左边 b)如果出现where条件中出现不等值条件 首先要调整语句，把=的放前面，把不等的放后面 desc select * from t_100w where k2="DEEF" and num<1000; 建立索引时也注意顺序： alter table t_100w add index idx_2_n(k2,num); c)如果查询中出现多子句 我们需要按照子句的执行顺序进行建立索引
3)唯一索引
索引列的值都是唯一的.
4.关于索引树的高度受什么影响
(1). 数据量级, 解决方法:分表,分库,分布式
(2). 索引列值过长 , 解决方法:前缀索引
(3). 数据类型:
变长长度字符串,使用了char,解决方案:变长字符串使用varchar
enum类型的使用enum ('山东','河北','黑龙江','吉林','辽宁','陕西'......)
1 2 3
5.索引的基本管理
5.0 准备 db01 [world]>desc city; +-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | MUL | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 0 | | +-------------+----------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) Field :列名字 key :有没有索引,索引类型 PRI: 主键索引 UNI: 唯一索引 MUL: 辅助索引(单列,联和,前缀) 5.1创建索引 db01 [world]>alter table city add index idx_name(name); 表 索引名（列名） db01 [world]>create index idx_name1 on city(name); db01 [world]>show index from city;  注意: 以上操作不代表生产操作,我们不建议在一个列上建多个索引 同一个表中，索引名不能同名。 5.2删除索引: db01 [world]>alter table city drop index idx_name1; 表名 索引名 5.3 覆盖索引(联合索引) Master [world]>alter table city add index idx_co_po(countrycode,population); 5.4 前缀索引 db01 [world]>alter table city add index idx_di(district(5)); 注意：数字列不能用作前缀索引。 5.5 唯一索引 db01 [world]>alter table city add unique index idx_uni1(name); ERROR 1062 (23000): Duplicate entry 'San Jose' for key 'idx_uni1' 5.6 查看索引 show index from tblname; show keys from tblname; 5.7 查看某列是不是适合做唯一索引 select population,count(id) from city group by population having count(id)>1 order by count(id) desc; 重复多则不适合做唯一索引
四、执行计划获取及分析
1.介绍
(1)获取到的是优化器选择完成的,他认为代价最小的执行计划.
作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题.
如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析优化方案。
(2) select 获取数据的方法
a. 全表扫描(应当尽量避免,因为性能低)
b. 索引扫描
c. 获取不到数据
2.执行计划获取
获取优化器选择后的执行计划
desc select * from t_100w where id=9000; table:t_100w 查询的表 type:ALL 查询类型 possible_keys:NULL 可能走的索引 key:NULL 走的索引的名 key_len:NULL 应用索引的长度 rows:997261 查询结果集的长度 Extra:Using where 额外信息 extra: filesort ,文件排序. 结论: 1.当我们看到执行计划extra位置出现filesort,说明由文件排序出现 2.观察需要排序(ORDER BY,GROUP BY ,DISTINCT )的条件,有没有索引 3. 根据子句的执行顺序,去创建联合索引
联合索引:
1. SELECT * FROM t1 WHERE a= b= 我们建立联合索引时: ALTER TABLE t1 ADD INDEX idx_a_b(a,b); ALTER TABLE t1 ADD INDEX idx_b_a(b,a); 以上的查询不考虑索引的顺序,优化器会自动调整where的条件顺序 注意: 索引,我们在这种情况下建索引时,需要考虑哪个列的唯一值更多,哪个放在索引左边. 2. 如果出现where 条件中出现不等值查询条件 DESC SELECT * FROM t_100w WHERE num <1000 AND k2='DEEF'; 我们建索引时: ALTER TABLE t_100w ADD INDEX idx_2_n(k2,num); 语句书写时 DESC SELECT * FROM t_100w WHERE k2='DEEF' AND num <1000 ; 3. 如果查询中出现多子句 我们要按照子句的执行顺序进行建立索引.
2.1 type类型
2.1.1 ALL：全表扫描，不走索引 不走索引的案例：(辅助索引) (1)查询条件列，没有索引 例子：select * from t_100w where id=9000; (2)查询条件出现以下语句 <>,!= 不等于 select * from city where countrycode <> "CHN"; not in (value1,value2,value3) select * from city where countrycode not in ("CHN","USA"); like select * from city where countrycode like "%CH%"; 注意：对于聚集索引列，使用以上语句，依然会走索引 2.1.2 INDEX:全索引扫描。整个索引树都扫描一次 注意：这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据，和all相比，他们都是取得了全表的数据，而且index要先读索引而且要回表随机取数据，因此index不可能会比all快（取同一个表数据），但为什么官方的手册将它的效率说的比all好，唯一可能的原因在于，按照索引扫描全表的数据是有序的。这样一来，结果不同，也就没法比效率的问题了。 如果回表查询的是无序的数据，那么效率更低。如果只查询索引列的值，那么使用索引也能提升效率。 案例： (1)查询需要获取整个索引树种的值时 select countrycode from city; (2)联合索引中，任何一个非最左列作为查询条件时(mysql版本不同，可能有的版本会走全索引扫描，有的不会) idx_a_b_c(a,b,c) select * from t1 where b select * from t1 where c 2.1.3 RANGE:索引范围扫描 辅助索引:> < = >= <= <> like, in, or, 主键：<> not in 例子 desc select * from city where id<5; desc select * from city where countrycode like "CH%"; desc select * from city where countrycode in ("CHN","USA"); 注意：1和2的例子中，可以享受到B+树的优势，但是3例子是不能享受的(B+树叶子节点存放隔壁的指针，有指向，例3享受不到)。 所以，我们可以把3号例子改写 desc select * from city where countrycode="CHN" union all select * from city where countrycode="USA"; 2.1.4 ref：非唯一性索引，等值查询 desc select * from city where countrycode="CHN"; 2.1.5 eq_ref:在多表连接时，连接条件使用了唯一索引(uk pk) 例： desc select b.name,a.name from city as a join country on a.countrycode=b.code where a.population<100; 2.1.6 const：唯一索引的等值查询 desc select * from city where id=10; 2.1.7 system：表只有一行：system表。这是const连接类型的特殊情况。 效率：按上面的介绍顺序从低到高
3.执行计划分析
3.1重点关注信息
table: city ---->查询操作的表 ** possible_keys: CountryCode,idx_co_po ---->可能会走的索引 ** key: CountryCode ---->真正走的索引 *** type: ref ---->索引类型 ***** Extra: Using index condition ---->额外信息 *****
3.2 explain(desc)使用场景（面试题）
题目意思: 我们公司业务慢,请你从数据库的角度分析原因 mysql出现性能问题,我总结有两种情况: （1）应急性的慢：突然夯住 应急情况:数据库hang(卡了,资源耗尽) 处理过程: 1)show processlist; 获取到导致数据库hang的语句 2) explain 分析SQL的执行计划,有没有走索引,索引的类型情况 3) 建索引,改语句 （2）一段时间慢(持续性的): (1)记录慢日志slowlog,分析slowlog (2)explain 分析SQL的执行计划,有没有走索引,索引的类型情况 (3)建索引,改语句
3.3 案例
#案例： 优化前: [root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf > --concurrency=100 --iterations=1 --create-schema='oldboy' > --query="select * from oldboy.t_100w where k2='780P'" engine=innodb > --number-of-queries=2000 -uroot -p123 -verbose mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Running for engine rbose Average number of seconds to run all queries: 701.743 seconds Minimum number of seconds to run all queries: 701.743 seconds Maximum number of seconds to run all queries: 701.743 seconds Number of clients running queries: 100 Average number of queries per client: 20 SHOW INDEX FROM city; ALTER TABLE city ADD INDEX CountryCode(CountryCode); ALTER TABLE city DROP INDEX idx_c_p; DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population ALTER TABLE city ADD INDEX idx_(population); DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population ALTER TABLE city ADD INDEX idx_c_p(countrycode,population); ALTER TABLE city DROP INDEX idx_; ALTER TABLE city DROP INDEX CountryCode; DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population 优化后: [root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Running for engine rbose Average number of seconds to run all queries: 0.190 seconds Minimum number of seconds to run all queries: 0.190 seconds Maximum number of seconds to run all queries: 0.190 seconds Number of clients running queries: 100 Average number of queries per client: 20
五、索引应用规范
1.(必须的)建表时一定要有主键，一般是个无关列
2.选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。
优化方案: (1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分 (2) 可以将此列和其他的查询类,做联和索引 select count(*) from world.city; select count(distinct countrycode) from world.city; select count(distinct countrycode,population ) from world.city;
3.(必须的) 为经常需要where 、ORDER BY、GROUP BY,join on等操作的字段
排序操作会浪费很多时间。 where A B C ----》 A B C in where A group by B order by C A,B，C 如果为其建立索引，优化查询 注：如果经常作为条件的列，重复值特别多，可以建立联合索引。
4.尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。
5.限制索引的数目
索引的数目不是越多越好。
可能会产生的问题:
(1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
(2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
(3) 优化器的负担会很重,有可能会影响到优化器的选择.
percona-toolkit中有个工具,专门分析索引是否有用
6.删除不再使用或者很少使用的索引(percona toolkit)
pt-duplicate-key-checker
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
7.大表加索引,要在业务不繁忙期间操作
8.尽量少在经常更新值的列上建索引
9.建索引原则
(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期
六、不走索引的情况(开发规范)
1.没有查询条件，或者查询条件没有建立索引
select * from tab; 全表扫描。 select * from tab where 1=1; 在业务数据库中，特别是数据量比较大的表。 是没有全表扫描这种需求。 对用户查看是非常痛苦的。 对服务器来讲毁灭性的。 （1） select * from tab; SQL改写成以下语句： select * from tab order by price limit 10 ; 需要在price列上建立索引 （2） select * from tab where name='zhangsan' name列没有索引 改： 换成有索引的列作为查询条件 将name列建立索引
2.查询结果集是原表中的大部分数据，应该是25％以上
查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。 假如：tab表 id，name id:1-100w ，id列有(辅助)索引 select * from tab where id>500000; 如果业务允许，可以使用limit控制。 怎么改写 ？ 结合业务判断，有没有更好的方式。如果没有更好的改写方案 尽量不要在mysql存放这个数据了。放到redis里面。
3.索引本身失效，统计数据不真实
索引有自我维护的能力。 对于表内容变化比较频繁的情况下，有可能会出现索引失效。 一般是删除重建 现象: 有一条select语句平常查询时很快,突然有一天很慢,会是什么原因 select? --->索引失效,，统计数据不真实 DML ? --->锁冲突
4.查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
例子： 错误的例子：select * from test where id-1=9; 正确的例子：select * from test where id=10; 算术运算 函数运算 子查询
5.隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
这样会导致索引失效. 错误的例子： mysql> alter table tab add index inx_tel(telnum); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> desc tab; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | telnum | varchar(20) | YES | MUL | NULL | | +--------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> select * from tab where telnum='1333333'; +------+------+---------+ | id | name | telnum | +------+------+---------+ | 1 | a | 1333333 | +------+------+---------+ 1 row in set (0.00 sec) mysql> select * from tab where telnum=1333333; +------+------+---------+ | id | name | telnum | +------+------+---------+ | 1 | a | 1333333 | +------+------+---------+ 1 row in set (0.00 sec) mysql> explain select * from tab where telnum='1333333'; +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+ | 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition | +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec) mysql> explain select * from tab where telnum=1333333; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from tab where telnum=1555555; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from tab where telnum='1555555'; +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+ | 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition | +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec) mysql>
6.<> ，not in 不走索引（辅助索引）
EXPLAIN SELECT * FROM teltab WHERE telnum <> '110'; EXPLAIN SELECT * FROM teltab WHERE telnum NOT IN ('110','119'); mysql> select * from tab where telnum <> '1555555'; +------+------+---------+ | id | name | telnum | +------+------+---------+ | 1 | a | 1333333 | +------+------+---------+ 1 row in set (0.00 sec) mysql> explain select * from tab where telnum <> '1555555'; 单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit or或in 尽量改成union EXPLAIN SELECT * FROM teltab WHERE telnum IN ('110','119'); 改写成： EXPLAIN SELECT * FROM teltab WHERE telnum='110' UNION ALL SELECT * FROM teltab WHERE telnum='119'
7.like "%_" 百分号在最前面不走
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%' 走range索引扫描 EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110' 不走索引 %linux%类的搜索需求，可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品