MySQL-索引管理及执行计划

一、索引介绍

1.1、索引作用

提供了类似于书中目录的作用,目的是为了优化查询

1.2、索引算法上分类

大的分类：

B树索引 
Hash索引 
R树 
Full text 
GIS  #地图类索引
-------------------------------

#B树基于不同的查找算法分类：
B-tree 
以下两种类型在范围查询方面提供了更好的性能(> < >= <=)
B+Tree   
B*Tree

1.3、索引功能上的分类

1.3.1、辅助索引

1）辅助索引(S)怎么构建B树结构的?

(1)索引是基于表中列(索引键)的值生成的B树结构
(2)首先提取此列所有的值,进行自动排序
(3)将排好序的值,均匀的分布到索引树的叶子节点中(16K)
(4)然后生成此索引键值所对应得后端数据页的指针
(5)生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度

2）辅助索引细分

1）普通的单列辅助索引（普通索引）
2）覆盖索引(联合索引)：多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表查询
3）唯一索引:索引列的值都是唯一的.

1.3.2、聚集索引

1）前提

(1)表中设置了主键,主键列就会自动被作为聚集索引.
(2)如果没有主键,会选择唯一键作为聚集索引.
(3)聚集索引必须在建表时才有意义,一般是表的无关列(ID)

2）聚集索引(C)怎么构建B树结构的?

(1) 在建表时,设置了主键列(ID)
(2) 在将来录入数据时,就会按照ID列的顺序存储到磁盘上.(我们又称之为聚集索引组织表)
(3) 将排好序的整行数据,生成叶子节点.可以理解为,磁盘的数据页就是叶子节点

1.3.3、索引高度影响因数

1）数据量级, 解决方法:分表,分库,分布式

2）索引列值过长 , 解决方法:前缀索引

3）数据类型:

变长长度字符串,使用了char,解决方案:变长字符串使用varchar

enum类型的使用enum ('山东','河北','黑龙江','吉林','辽宁','陕西'......)

1.3.4、B树查找算法

二、索引基本管理

2.1、查看索引

mysql> 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       |                |
+-------------+----------+------+-----+---------+----------------+
Field :列名字
key   :有没有索引,索引类型
	  PRI: 主键索引
	  UNI: 唯一索引 
	  MUL: 辅助索引(单列,联和,前缀)

mysql> show index from city;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city  |          0 | PRIMARY     |            1 | ID          | A         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | CountryCode |            1 | CountryCode | A         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

2.2、索引创建修改删除

1）创建索引

alter table city add index idx_name(name);		#方法一
create index idx_name1 on city(name);			#方法二
show index from city;

2）删除索引

alter table city drop index idx_name1;		#直接删除索引名称即可

3）覆盖索引(联合索引)

alter table city add key idx_co_po(countrycode,population);	#多个字段上建立索引
alter table city add index idx_co_po(countrycode,population);	#多个字段上建立索引

4）前缀索引

alter table city add index idx_di(district(5));	#在前5个字符上建立索引

5）唯一索引

alter table city add unique index idx_uni1(name);

ERROR 1062 (23000): Duplicate entry 'San Jose' for key 'idx_uni1'	#唯一键冲突

三、执行计划

3.1、执行计划介绍

获取到的是优化器选择完成的,他认为代价最小的执行计划.

作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题.

3.2、select 获取数据的方法

1. 全表扫描(应当尽量避免,因为性能低)

2. 索引扫描

3. 获取不到数据

3.3、执行计划获取及分析

获取优化器选择后的执行计划：explain或者desc

explain select SQL_NO_CACHE * from test where name='AAA'\G #SQL_NO_CACHE的作用是禁止缓存查询结果。

mysql> desc select * from city where countrycode='CHN'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: ref
possible_keys: CountryCode
          key: CountryCode
      key_len: 3
          ref: const
         rows: 363
     filtered: 100.00
        Extra: NULL
---------------------------------------------------------------

#执行计划相关信息分析：
table: city                               #查询操作的表  
possible_keys: CountryCode,idx_co_po      #可能会走的索引
key: CountryCode						  #真正走的索引  
type: ref								  #索引类型      
Extra: Using index condition              #额外信息

四、索引类型详解 *****

如下为索引类型，从左到右性能依次变好.

ALL			#全表扫描
index			#全索引扫描
range			#索引范围查询
ref				#辅助索引的等值查询
eq_ref			#多表连接的表,On的条件是主键或唯一键
system(const)	#主键或唯一键的等值查询
NULL			#索引中扫描不到这个数据

在索引扫描类型方面，至少保证在range以上级别。

4.1、all: 全表扫描

desc select * from city;
desc select * from city where name like '%C%';
desc select * from city where name != 'CHN';	#或者<>
desc select * from city where countrycode not in ('CHN','USA');
#注意：生产中几乎是没有这种需求的。尽量避免

4.2、index: 全索引扫描

需要扫描整个索引树,获取到想要数据,比ALL性能好,顺序IO,可以减少回表查询

mysql> 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       |                |
+-------------+----------+------+-----+---------+----------------+

mysql> desc select CountryCode from city;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | index | NULL          | CountryCode | 3       | NULL | 4188 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+

4.3、 range : 索引范围查询

4.3.1、情况种类

>  <  >= <= 
in 
or 
like 'CH%'
between and
---------------------------------------------------
注意：
B+树额外优化了
> < >= <= 
between and 
like 'CH%'
in or无法享受B+树的额外优化,可以用union all来替代

4.3.2、示例

mysql> desc select * from city where id<10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    9 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

mysql> desc select * from city where countrycode in ('CHN','USA');
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | CountryCode   | CountryCode | 3       | NULL |  637 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+

mysql> desc select * from city where countrycode like 'CH%';
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | CountryCode   | CountryCode | 3       | NULL |  397 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+

4.3.3、优化案例：in=>union all

mysql> desc select * from city where countrycode in ('CHN','USA');
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | CountryCode   | CountryCode | 3       | NULL |  637 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+

mysql> desc select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL  |
|  2 | UNION       | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  274 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

4.4、 ref: 辅助索引的等值查询

mysql> desc select * from city where countrycode = 'CHN';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

4.5、eq_ref

多表连接的表,On的条件是主键或唯一键

4.6、system 或 const

主键或唯一键的等值查询

mysql> desc select * from city where id=10;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

4.7、NULL

索引中扫描不到这个数据

mysql> desc select * from city where id=5000;  #id=5000不存在
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+

4.8、Extra字段：Using filesort问题

Extra字段：Using filesort	#出现说明有问题，要优化
desc select * from city where countrycode='CHN' order by population desc limit 10;   #contrycode上有索引，但population上是没有索引的
mysql> desc select * from city where countrycode='CHN' order by population desc limit 10;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+

#解决思路：
索引可以减少排序,可以很大程度减少CPU时间
辅助索引 应用顺序(优化器选择的)
如果查询条件:符合覆盖索引的顺序时,优先选择覆盖索引
不符合顺序,优先会走where条件的索引

#解决方法：可以在countrycode和population上建立联合索引
mysql> alter table city add index idx_po(countrycode,population);
mysql> desc select * from city where countrycode='CHN' order by population limit 10;
+----+-------------+-------+------------+------+--------------------+--------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys      | key    | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+--------------------+--------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode,idx_po | idx_po | 3       | const |  363 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+--------------------+--------+---------+-------+------+----------+-----------------------+

4.9、explain使用场景

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

五、索引压力测试

5.1、mysqlslap测试示例

mysqlslap工具介绍
 mysqlslap来自于mariadb包，测试的过程默认生成一个mysqlslap的schema,生成测试表t1，查询和插入测试数据，mysqlslap库自动生成，如果已经存在则先删除。用--only-print来打印实际的测试过程，整个测试完成后不会在数据库中留下痕迹。

常用选项：

--auto-generate-sql, -a 自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力
--auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)
--auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持
--number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列，默认1
--number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认1
--number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)
--query=name,-q 使用自定义脚本执行测试，例如可以调用自定义的存储过程或者sql语句来执行测试
--create-schema 代表自定义的测试库名称，测试的schema，MySQL中schema也就是database
--commint=N 多少条DML后提交一次
--compress, -C 如服务器和客户端都支持压缩，则压缩信息
--concurrency=N, -c N 表示并发量，即模拟多少个客户端同时执行select；可指定多个值，以逗号或者--delimiter参数指定值做为分隔符
--engine=engine_name, -e engine_name 代表要测试的引擎，可以有多个，用分隔符隔开
--iterations=N, -i N 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次
--only-print 只打印测试语句而不实际执行
--detach=N 执行N条语句后断开重连
--debug-info, -T 打印内存和CPU的相关信息
测试示例：

1）单线程测试

[root@centos7 ~]# mysqlslap -a -uroot -p
Enter password: 
Benchmark
        Average number of seconds to run all queries: 0.004 seconds
        Minimum number of seconds to run all queries: 0.004 seconds
        Maximum number of seconds to run all queries: 0.004 seconds
        Number of clients running queries: 1
        Average number of queries per client: 0
2）多线程测试，使用--concurrency来模拟并发连接

[root@centos7 ~]# mysqlslap -uroot -p -a -c 500
Enter password: 
Benchmark
        Average number of seconds to run all queries: 3.384 seconds
        Minimum number of seconds to run all queries: 3.384 seconds
        Maximum number of seconds to run all queries: 3.384 seconds
        Number of clients running queries: 500
        Average number of queries per client: 0
3）同时测试不同的存储引擎的性能进行对比

[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info
Enter password: 
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.192 seconds
        Minimum number of seconds to run all queries: 0.187 seconds
        Maximum number of seconds to run all queries: 0.202 seconds
        Number of clients running queries: 500
        Average number of queries per client: 2

Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.355 seconds
        Minimum number of seconds to run all queries: 0.350 seconds
        Maximum number of seconds to run all queries: 0.364 seconds
        Number of clients running queries: 500
        Average number of queries per client: 2


User time 0.33, System time 0.58
Maximum resident set size 22892, Integral resident set size 0
Non-physical pagefaults 46012, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 31896, Involuntary context switches 0
4）执行一次测试，分别500和1000个并发，执行5000次总查询

[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500,1000 --number-of-queries 5000 --debug-info
Enter password: 
Benchmark
        Average number of seconds to run all queries: 3.378 seconds
        Minimum number of seconds to run all queries: 3.378 seconds
        Maximum number of seconds to run all queries: 3.378 seconds
        Number of clients running queries: 500
        Average number of queries per client: 10

Benchmark
        Average number of seconds to run all queries: 3.101 seconds
        Minimum number of seconds to run all queries: 3.101 seconds
        Maximum number of seconds to run all queries: 3.101 seconds
        Number of clients running queries: 1000
        Average number of queries per client: 5


User time 0.84, System time 0.64
Maximum resident set size 83068, Integral resident set size 0
Non-physical pagefaults 139977, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 31524, Involuntary context switches 3
5）迭代测试

[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500 --number-of-queries 5000 --iterations=5 --debug-info
Enter password: 
Benchmark
        Average number of seconds to run all queries: 3.307 seconds
        Minimum number of seconds to run all queries: 3.184 seconds
        Maximum number of seconds to run all queries: 3.421 seconds
        Number of clients running queries: 500
        Average number of queries per client: 10


User time 2.18, System time 1.58
Maximum resident set size 74872, Integral resident set size 0
Non-physical pagefaults 327732, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 73904, Involuntary context switches 3

1）模拟数据库数据

drop database oldboy;
create database oldboy charset utf8;

2）创建一个t1的表，然后导入50万行数据

[root@db01 ~]# vim slap.sh
#!/bin/bash  
HOSTNAME="localhost" 
PORT="3306" 
USERNAME="root" 
PASSWORD="1" 
DBNAME="oldboy" 
TABLENAME="t1" 
#create database 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}" 
create_db_sql="create database if not exists ${DBNAME}" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}" 
#create table 
create_table_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname varchar(20) not null,stusex char(1)   
not null,cardid varchar(20) not null,birthday datetime,entertime datetime,address varchar(100)default null)" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}" 
#insert data to table 
i="1" 
while [ $i -le 500000 ]  
do  
insert_sql="insert into ${TABLENAME}  values($i,'alexsb_$i','1','110011198809163418','1990-05-16','2017-09-13','oldboyedu')" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}" 
let i++  
done  
#select data  
select_sql="select count(*) from ${TABLENAME}" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"

执行脚本：
sh slap.sh

或者直接source			
drop database oldboy;
source /root/oldboy.sql

3）检查数据可用性

mysql -uroot -p
select count(*) from oldboy.t1;

4）使用mysqlslap来进行压力测试

mysqlslap --defaults-file=/etc/my.cnf \
 --concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from oldboy.t1 where stuname='alexsb_100'" engine=innodb \
--number-of-queries=2000 -uroot -pmysql -verbose

没建立索引之前：

在查询条件列上建立索引：alter table t1 add index idx_name(stuname);

5.2、其他测试工具（待测试）

1）tpcc

2）sysbench

六、索引应用规范

主要根据公司的业务来建立合适的索引

产品的功能
用户的行为："热"查询语句，"热"数据

6.1、建立索引的原则

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。

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等操作的字段建立索引，排序操作会浪费很多时间。注：如果经常作为条件的列，重复值特别多，可以建立联合索引

4）使用前缀索引。如果索引字段的值很长，最好使用值的前缀来索引。

5）限制索引的数目

索引的数目不是越多越好。可能会产生的问题:
	(1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
	(2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
	(3) 优化器的负担会很重,有可能会影响到优化器的选择.

6）删除不再使用或者很少使用的索引(使用percona toolkit)

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

7）大表加索引,要在业务不繁忙期间操作

8）尽量少在经常更新值的列上建索引

建立索引原则总结：

(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,使用percona toolkit工具
(6) 索引维护要避开业务繁忙期

6.2、不走索引的情况

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

select * from tab;              #全表扫描
select  * from tab where 1=1;

在业务数据库中，特别是数据量比较大的表，是没有全表扫描这种需求。

对用户查看是非常痛苦的。
对服务器来讲毁灭性的。

（1）select * from tab;
SQL改写成以下语句：
selec  * from  tab  order by  price  limit 10 ;     #需要在price列上建立索引

（2）select  * from  tab where name='zhangsan'          #name列没有索引
改成如下语句：
	1、换成有索引的列作为查询条件
	2、将name列建立索引

2）查询的结果集，超过了总数行数25%，优化器默认没有必要走索引

解决方法：
1、如果业务允许，可以使用limit控制。
2、尽量不要在mysql存放这个数据了，可以放到redis里面

3）索引本身失效，统计数据不真实

索引有自我维护的能力。对于表内容变化比较频繁的情况下，有可能会出现索引失效。一般是删除重建

4）查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)

错误的例子：select * from test where id-1=9; 
正确的例子：select * from test where id=10;
在索引列上使用算术运算，函数运算，子查询时，可能不会走索引

5）隐式转换导致索引失效

mysql> alter table tab add index inx_tel(telnum);	
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    |       |	#字段类型是varchar，同时建立了索引
+--------+-------------+------+-----+---------+-------+

mysql> select * from tab where telnum='1333333';	#查询条件是字符串，走索引
+------+------+---------+
| id   | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+

mysql> select * from tab where telnum=1333333;	#查询条件是数字，隐式转换为字符串
+------+------+---------+
| id   | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+

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 |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+

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 |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

6）<> ，not in 不走索引

EXPLAIN  SELECT * FROM teltab WHERE telnum   <> '110';
EXPLAIN  SELECT * FROM teltab WHERE telnum  NOT IN ('110','119');

注意：
1. 单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
2. 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 专门做搜索服务的数据库产品

8）单独引用联合索引里非第一位置的索引列.作为条件查询时不走索引

idx_a_b_c(a,b,c)	#建立的联合索引

走索引的情况：
where  a  b  c
where  a b 
where  a
============
部分走索引
where a c
where a c b
============
不走索引
where c 
where b 
where bc 
where cb
where ca 
where cba

posted @ 2019-10-08 12:12 运维人在路上阅读(577) 评论(0) 编辑收藏举报

刷新页面返回顶部

运维人在路上

当你发现自己的才华撑不起野心时，就请安静下来学习吧

MySQL-索引管理及执行计划

一、索引介绍

1.1、索引作用

1.2、索引算法上分类

1.3、索引功能上的分类

1.3.1、辅助索引

1.3.2、聚集索引

1.3.3、索引高度影响因数

1.3.4、B树查找算法

二、索引基本管理

2.1、查看索引

2.2、索引创建修改删除

三、执行计划

3.1、执行计划介绍

3.2、select 获取数据的方法

3.3、执行计划获取及分析

四、索引类型详解 *****

4.1、all: 全表扫描

4.2、index: 全索引扫描

4.3、 range : 索引范围查询

4.3.1、情况种类

4.3.2、示例

4.3.3、优化案例：in=>union all

4.4、 ref: 辅助索引的等值查询

4.5、eq_ref

4.6、system 或 const

4.7、NULL

4.8、Extra字段：Using filesort问题

4.9、explain使用场景

五、索引压力测试

5.1、mysqlslap测试示例

5.2、其他测试工具（待测试）

六、索引应用规范

6.1、建立索引的原则

6.2、不走索引的情况

公告

运维人在路上

当你发现自己的才华撑不起野心时，就请安静下来学习吧

MySQL-索引管理及执行计划

一、索引介绍

1.1、索引作用

1.2、索引算法上分类

1.3、索引功能上的分类

1.3.1、辅助索引

1.3.2、聚集索引

1.3.3、索引高度影响因数

1.3.4、B树查找算法

二、索引基本管理

2.1、查看索引

2.2、索引创建修改删除

三、执行计划

3.1、执行计划介绍

3.2、select 获取数据的方法

3.3、执行计划获取及分析

四、索引类型详解 *****

4.1、all: 全表扫描

4.2、index: 全索引扫描

4.3、 range : 索引范围查询

4.3.1、情况种类

4.3.2、示例

4.3.3、优化案例：in=>union all

4.4、 ref: 辅助索引的等值查询

4.5、eq_ref

4.6、system 或 const

4.7、NULL

4.8、Extra字段：Using filesort问题

4.9、explain使用场景

五、索引压力测试

5.1、mysqlslap测试示例

5.2、其他测试工具（待测试）

六、索引应用规范

6.1、建立索引的原则

6.2、 不走索引的情况

公告

6.2、不走索引的情况