Mysql查询优化

一、索引

• 涉及查询优化，首先从索引出发，一般能最快最好的解决问题
• 索引解决完成后，再从其他角度出发去优化查询；

1. 索引原理

1.1 单表扫描

SELECT * FROM ad WHERE company_num<14

1.无索引
 - 全局搜索，挑选出合适行数据，若数据量大，则全表扫描耗时
 
2.带索引
- 扫描索引，挑选出两个13的数据
- 当扫描到14时，因为索引已经排序，所以下面条件都不满足
- 退出查询，将索引为13对应的两个数据行结果返回

3.优化原因
- 方式一：得知匹配行在什么地方结束，从而不用扫描其他剩余数据
- 方式二：定位算法不用从索引开始位置线性扫描，直接跳转到匹配行数据

- 不同数据库采用不同的技术来进行快速定位索引匹配位置

4. 直接对数据进行全排序不行吗？
- 若只针对一列进行排序，和索引差不多，但一个表中可能涉及多个索引列
- 索引就是将一行列数据剥离出来，排序，并与源数据行一一对应
- 索引行数据更短。增加一行数据时，只对索引进行重排，比对整个数据行重排效率高

5. InnoDB 和 MyISAM 
- MyISAM: 数据行保留在数据文件中
          索引值保留在索引文件中
          一个表的多个索引，保存在同一个索引文件中
          每个索引都由一组有序的关键字构成，从而快速访问数据文件
          
- InnoDB:  只使用一个表空间，包含所有数据存储和索引存储

1.2 多表联查

# 1， 三个表t1，t2， t3,分别包含1000行数据，其中表的id列都为1-1000
#     要将三个表关联，将id一样的数据取到，一共有1000条有效记录
SELECT * FROM t1 INNER JOIN t2 INNER JOIN t2 
WHERE t1.id = t2.id AND t2.id = t3.id;

无索引
- 笛卡尔积关联，再去扫描每一行数据，全表扫描1000乘1000乘1000行数据

带索引
 - 笛卡尔积关联，目标数据1000乘1000乘1000行
 - t1全表扫描，t2表快速定位到t1.id = t2.id一行，t3表同理
 - 实际只检索了1000行数据
 - t1全表扫描，t2，t3索引快速定位

1.3 索引使用场景

• where子句匹配的行，与另外一个连接表里的行
• 使用MIN()，MAX() 函数的查询
• ORDER BY, GOURP BY 子句
• 查询就只想获得某个列的的某个值，因此直接利用索引查找，而不是去数据行里面查找

2. 索引代价

1. 写操作效率： 时间
写一个行数据，不仅要插入到行中，也要对该表的索引进行重排，
索引越多，重排的索引也就越多，写操作就会越慢

2. 索引占用磁盘： 空间
索引数目过多的时候，会占用大量的表磁盘空间
- MyISAM: 可能使索引存储空间 比 数据存储空间更快的达到上限值
- InnoDB: 过多索引会占用共享的存储的空间

3. 索引类型

- 主键索引(PRIMARY KEY)：   列值唯一（不可为null), 表中只有一个
- 唯一索引(UNIQUE)：        列值唯一(可含一个null), 表中可有多个
- 普通索引(INDEX)：         仅仅为查找速度加快
- 全文索引(FULLTEXT)：      对文本的内容进行分词，进行搜索
- 复合索引(INDEX)：         多列值组成一个索引，专门用于组合搜索

# 1. 创建索引
-- 主键索引：索引名为系统指定即PRIMARY，手动指定的索引名不会生效
ALTER TABLE president ADD PRIMARY KEY (id);

ALTER TABLE president ADD UNIQUE fn_index (first_name); 

ALTER TABLE president ADD INDEX ln_index (last_name); 

ALTER TABLE president ADD FULLTEXT desc_index(info_desc);

ALTER TABLE `table_name` ADD INDEX 'index_name' ( `column1`, `column2`, `column3` )

# 2. 展示所有索引
show index from president;

# 3. 删除索引
# 方式一：
# 3.1 删除主键索引
ALTER TABLE president DROP PRIMARY KEY;
# 3.2 删除唯一索引: 索引类型统一用INDEX
ALTER TABLE president DROP INDEX fn_index;
# 3.3 删除普通索引
ALTER TABLE president DROP INDEX ln_index;

# 方式二： 
# DROP方式，会在mysql内部调整命令为ALTER命令
# 3.4 删除主键索引:    不能通过DROP方式删除主键索引
# 3.5 删除唯一索引
DROP INDEX fn_index ON president;
# 3.6 删除普通索引
DROP INDEX ln_index ON president;

4. 索引场景

4.1 搜索，排序，分组，最值

• where 子句后匹配的字段及多表链接时候的 on 字段；对于SELECT的字段不用加索引；
• 使用MIN()和MAX() 的搜索的字段，跳过了直接检查，直接定位到该字段的索引初始值或结束值；
• 使用GROUP BY 及ORDER BY 的字段，索引可以实现快速分组和排序；

4.2 数据列基数

• 数据列基数越大，即列维度数据不重复值越多，越适合用索引，
• 但若基数小，如只包含T和F，则即使建立索引，mysql也会去走全表扫描；

4.3 索引短小值

• 列数据，若INT可以满足，就不要使用BIGINT，短小的列值可以加快搜索；
• a. 短小列列数据的索引，比较操作时候更快，加快索引；
• b. 索引短小，加快磁盘IO请求；

4.4 索引字符串的前缀

• 如果某个数据类型是TEXT或者较长的字符串，且列数据前20个字符串唯一，后面的相同，则不用为整个字符串建立索引，只为前面的字符建立索引；
• 因为短小的索引值，可以加快查询；

4.5 利用最左前缀---- 复合索引

• 如果对一个表添加一个country/state/city的复合索引，那么索引会根据country/state/city的顺序来进行索引排序；
• 因此可以用来检索 country------country/state--------country/state/city三种组合查询，但不能用于state----city----state/city的组合查询；

# 1. 为三个列建立一个复合索引
ALTER TABLE president ADD INDEX ad_index (country,state,city);
# 建立一个复合索引，其实就是为每个列进行一个同名索引

# 2. 能够使用到该索引的查询 : 只与组合有关系，和组合的前后顺序无关
EXPLAIN SELECT * FROM president WHERE country = "中国";
EXPLAIN SELECT * FROM president WHERE country = "中国" AND state = "山西";
EXPLAIN SELECT * FROM president WHERE country = "中国" AND state = "山西" AND city = "cun";

# 3. 不能使用到该索引的查询
EXPLAIN SELECT * FROM president WHERE state = "山西";
EXPLAIN SELECT * FROM president WHERE city = "cun";
EXPLAIN SELECT * FROM president WHERE  city = "cun" AND state = "山西";

4.6 不要建立过多的索引

• 索引数目越多，对应的写操作就越慢，因此不能太多索引；
• 如上面的例子中，如果有复合索引，就不用再为country单独建立一个索引；

5. 索引失效

5.1 数据隐式类型转换

# 1. 把数值数据列和字符串常量比较时候，索引不会失效
#  '14' 只能转换为双精度的14，因此索引不会失效
EXPLAIN SELECT * FROM president WHERE num_col = "14";  '14';

# 2. 将字符串数据列和数值比较，索引失效
# 14 可以等于的字符串包含 '14','14.0', '14th'
EXPLAIN SELECT * FROM president WHERE str_col = 14;

5.2 like匹配时，%前置

# 1. 索引失效
EXPLAIN SELECT * FROM president WHERE str_col like '%4';
# 1. 索引不会失效
EXPLAIN SELECT * FROM president WHERE str_col like '4%';

5.3 索引列使用函数或者计算

# 1.1 列计算导致索引失效: 前面的字段相当于变成了 id + 1，而不是id
EXPLAIN SELECT * FROM president WHERE id + 1 < 4;
# 1.2 修改sql，使其能够使用索引
EXPLAIN SELECT * FROM president WHERE id < 3;

# 2.1 索引列进行了函数化运算，索引失效
EXPLAIN SELECT * FROM president WHERE YEAR(created_time) <2016;
# 2.2. 修改sql， 能够使用索引
# 如果不加 '',引发数据类型转换，索引页会失效
EXPLAIN SELECT * FROM president WHERE created_time < '2016-01-01';

# 3.1 索引列进行了函数化运算，索引失效
EXPLAIN SELECT * FROM president WHERE CONCAT(str_col,' ') = 'shu';

5.4 or两边的必须都为带索引

# 1. id和num_col都包含索引： 可以走索引；
EXPLAIN SELECT * FROM president WHERE id = 1 or num_col = 3;

# 2. first_name 不包含索引，因此整个sql都不会走索引
EXPLAIN SELECT * FROM president WHERE id = 1 or first_name = 'shu';

# 测试的时候要注意列数据维度，不让你可能因为这个影响or测试

5.5 组合索引

# 组合索引，索引的第二列，第三列，二，三列的AND查询，都不会走索引；

5.7 列数据维度小时

• 当列的数据维度较小的时候，会导致索引失效

5.8 系统默认

• 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

二、查询优化

• sql在执行时，mysql会对该sql进行查询优化，会充分利用索引及其他信息；
• 尽可能快的排除无用行；

# 1. 查询1=2不可能成立，因此该sql不会搜索全表，而是直接返回空数据集
EXPLAIN SELECT * FROM cnip WHERE name = "张三" AND 1=2;

# 2. 多个列匹配: mysql在对列排查的时候并不会去按照sql的书写顺序
# 哪个条件能够排除的数据最多，则先执行哪个，这样就不用对一些不符合的行数据
# 进行反复的多个条件的校验；
SELECT * FROM cnip WHERE condition1 AND condition2 And condition3;

1. 工作原理

1.1 使用EXPLAIN查看执行

• 通过EXPLAIN语句，查看当前是否索引正确使用，以及搜索效率如何；

-- 1. 正确使用了索引，因此只搜索一行，有效数据为100%
-- 2. 删除掉该索引，全表搜索了六行，有效数据为16%
EXPLAIN SELECT * FROM president WHERE country = "中国";

• possible_key：可能使用到的索引；
• key：实际使用到的索引；

1.2 使用相同类型的数据进行比较

• 当多表链接的时候，两个列数据比较时候，相同类型数据的比较相比不同类型数据，效率会快很多；
• 相同数据类型： INT 和 INT， CHAR(20)和VARCHAR(20)/CHAR(20)；
• 不同数据类型： INT和BIGINT，CHAR(20)和CHAR(40)；
• 如果数据类型不同，则尽量改为相同的数据列再进行比较；

1.3 避免数据类型自动转换

• 在列数据匹配的时候，mysql可以自动进行数据类型转换；
• 类型转换伴随着一定的性能损失；

# 1. code列为INT类型，但是和'14'进行匹配时候，会将字符和数字转换为双精度数;
SELECT * FROM president WHERE code = '14';

三、合适的数据类型

1. 多用数字运算，少用字符串运算

# 1. 比较运算符
数字之间的比较，可以在一个运算中完成，字符串之间的比较，需要多次字节与字节，或
字符与字符之间的比较；

# 2. ENUM/SET数据类型
如果某个列中，字符串取值有限，尽量使用ENUM/SET数据类型，这种数据类型在mysql
内部是以数字形式保存的；

2. 尽量使用小类型数据

# 1. 处理性能
mysql处理小类型数据的效率比大类型数据的效率高，尤其对于字符串

# 2. 磁盘读写
选用小类型数据，可以使表占用空间变小，提升磁盘的读写能力；

# 3. 带索引的
除了索引能提升性能，短小值得索引也会增加查询性能

3. 通过mysql的数据类型建议

• 根据mysql的建议，可以将某些数据类型进行修改；
• 这里因为数据行数不是很多，所以很多字段mysql都建议为ENUM值；

SELECT * FROM agree_user PROCEDURE ANALYSE();