实战经验

强制索引

force index(索引名称)
例子：
SELECT * FROM iva_alg.ivm_face_catch force index(catch_flg_task_index)
WHERE catch_time > ‘2020-01-01 00:00:00’ and catch_time < ‘2020-06-25 00:00:00’ AND data_flg = ‘1’
AND task_id IN (SELECTid FROM iva_alg.alg_task WHERE data_flg = ‘1’ AND app_key = “a564a899fcc80863f9”)
ORDER BY catch_time DESC limit 100000,3000
场景：
当 通过SQL分析 explain发现查询语句未走索引，解决方法，强制走某索引来降低查询时间。该方法适用于查询条件固定不变化场景，比如查询操作日志，按照用户和时间区间来查询；

MYSQL索引最左匹配原则

最左原则顾名思义就是从最左边开始匹配的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式，其针对的是组合索引（又名联合索引）
例子：ABC索引组合包含了A、AB、ABC三个索引；
①当查询条件为A、B、C；
EXPLAIN SELECT FROM expain_test WHERE a = 1 and b = “1” and c = “a”
possible_keys：表示查询时，可能使用的索引，abc
②当查询条件为B、C、A或者C、B、A；
EXPLAIN SELECT FROM expain_test WHERE c = “a” and b = “1” and a = 1
possible_keys：abc；
原因：寻找最低成本的执行计划，MYSQL服务器会根据一定规则对SQL进行优化，mysql中会使用Index Merge intersection algorithm算法来调整条件子句顺序；
③当查询条件为A、B和A、C；
EXPLAIN SELECT FROM expain_test WHERE a = 1 and b = “1”
EXPLAIN SELECT FROM expain_test WHERE c = “1” and a = 1
possible_keys：abc；AB组合使用组合索引包含AB索引，AC组合使用组合索引包含的A索引
④当查询条件为B、C；
EXPLAIN SELECT * FROM expain_test WHERE c = “1” and b= “1”
possible_keys：null;全表查询，组合索引的最左匹配原则，mysql会根据A来确定下一步的搜索方向，当没有A时，就只能去全记录去寻找；

MySQL优化-Explain

image.png
①id：-
②select_type：查询类型，区别普通查询、联合查询、子查询等；
取值和含义：
simple简单select查询；
primary表示子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为primary；
subquery:select和where包含子查询;
等等…
③table：表名；
④type：访问方式，ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
⑤possible_keys：查询可能涉及使用索引；
⑥key：mysqk执行语句执行索引；
⑦key_len：表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好；
⑧ref：显示索引的哪一列被使用了，如果有可能是一个常数，哪些列或常量被用于查询索引列上的值；-
⑨rows：表统计，读取行数；
⑩Extra：-

MySQL统计

information_schema.TABLES可以统计库占用表数目、数据存储量、索引存储量等
例子：
SELECT FROM(SELECT
table_schema,
(sum(data_length + index_length) / 1024 / 1024) AS total_mb,
(sum(data_length) / 1024 / 1024) AS data_mb,
(sum(index_length) / 1024 / 1024) AS index_mb,
count() AS TABLES,
curdate() AS today
FROM
information_schema.TABLES
GROUP BY
table_schema
ORDER BY
total_mb DESC) as a
结果:
image.png

其他

索引面试题

1.MySQL中使用较多的索引有Hash索引,B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引。hash索引等值查询更有快，b+树范围查询更快；

未实战-Mysql主从配置、读写分离、集群配置