MySQL：（九）MySQL索引
（一）关系型数据库基础
（二）MySQL安装
（三）管理数据库和表
（四）用户和权限管理
（五）函数，存储过程和触发器
（六）MySQL架构
（七）存储引擎
（八）MySQL服务器选项，系统和状态变量
（九）优化查询和索引管理
（十）锁和事务管理
（十一）日志管理
（十二）备份还原
（十三）MySQL集群
优化查询和索引管理
查询的执行路径
即DQL（select）语句的查询过程:
①通信协议：MySQL就是MySQL协议
注：mysql协议是应用层协议，不加密
mysql通信时客户端和服务器端一般是一个安全网络
如果涉及到透过公网加密时需要加密！
②查询缓存：查询命令首先看是否使用缓存和是否具有缓存
如果使用缓存并且具有缓存时，直接返回缓存结果
③解析：不经过缓存或者没有缓存时，需要查询数据库文件
首先需要解析SQL语句，判断是否有语法错误
④查询优化器：查询优化器为查询语句选择合适的执行路径。
比如是否选择索引
⑤查询执行：根据查询优化器的执行计划去磁盘查询相应的资源
⑥对于查询的结果，一份返回给查询，一份生成缓存。
查询缓存
1）查询缓存的原理
- 查询缓存（ Query Cache ）原理
缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预处理查询语句请求，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的SQL语句，是否完全一样，区分大小写
查询缓存的逻辑：首先对查询命令进行缓存处理
缓存处理：将select 语句做hash运算
==>是否具有缓存？
查询select语句做hash运算之后的值
<==> 比较
缓存中select语句hash运算后的值
由于hash的问题：意味两条select语句必须是严格一致，才能匹配出具有缓存
==> select语句的要求和写法必须标准化
- 查询缓存的优缺点
①不需要对SQL语句做任何解析和执行，当然语法解析必须通过在先，直接从
Query Cache中获得查询结果，提高查询性能
②查询缓存的判断规则，不够智能，也即提高了查询缓存的使用门槛，降低效率
③查询缓存的使用，会增加检查和清理Query Cache中记录集的开销
2）哪些查询可能不会被缓存
-
①查询语句中加了SQL_NO_CACHE参数
-
②查询语句中含有获得值的函数，包含自定义函数，如：NOW()、CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ()等
-
③对系统数据库的查询：mysql、information_schema 查询语句中使用SESSION级别变量或存储过程中的局部变量
-
④查询语句中使用了LOCK IN SHARE MODE、FOR UPDATE的语句，查询语句中类似SELECT …INTO 导出数据的语句
-
⑤对临时表的查询操作；存在警告信息的查询语句；不涉及任何表或视图的查询语句；某用户只有列级别权限的查询语句
-
⑥事务隔离级别为Serializable时，所有查询语句都不能缓存
3）查询缓存相关的服务器变量
-
query_cache_min_res_unit：查询缓存中内存块的最小分配单位，默认4k，较小值会减少浪费，但会导致更频繁的内存分配操作，较大值会带来浪费，会导致碎片过多，内存不足
-
query_cache_limit：单个查询结果能缓存的最大值，默认为1M，对于查询结果过大而无法缓存的语句，建议使用SQL_NO_CACHE
-
query_cache_size：查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍，最小值40KB，低于此值有警报
-
query_cache_wlock_invalidate：如果某表被其它的会话锁定，是否仍然可以从查询缓存中返回结果，默认值为OFF，表示可以在表被其它会话锁定的场景中继续从缓存返回数据；ON则表示不允许
-
query_cache_type：是否开启缓存功能，取值为ON, OFF, DEMAND
DEMAND：按需启用缓存，需要select 语句加 SQL_CACHE才会启用缓存
注：默认情况系统并未启用查询缓存
查询缓存的启用：
需要设置以下某些变量才能启用
①query_cache_min_res_unit
②query_cache_limit
③query_cache_size
④query_cache_wlock_invalidate
⑤query_cache_type
查询相关变量：
> select @@query_cache_min_res_unit as unit;
+------+
| unit |
+------+
| 4096 | set临时修改时以bit为单位修改
+------+
....
4）SELECT语句的缓存控制
- SQL_CACHE：显式指定存储查询结果于缓存之中
- SQL_NO_CACHE：显式查询结果不予缓存
5）query_cache_type参数变量
-
query_cache_type的值为OFF或0时，查询缓存功能关闭
-
query_cache_type的值为ON或1时，查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则，不予缓存，显式指定SQL_NO_CACHE，不予缓存，此为默认值
-
query_cache_type的值为DEMAND或2时，查询缓存功能按需进行，显式指定SQL_CACHE的SELECT语句才会缓存；其它均不予缓存
-
参看：
https://mariadb.com/kb/en/library/server-system-variables/#query_cache_type
https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html
6）优化查询缓存
7）查询缓存相关的状态变量：SHOW GLOBAL STATUS LIKE ‘Qcache%’;
- Qcache_free_blocks：处于空闲状态 Query Cache中内存 Block 数 Qcache_total_blocks：Query Cache 中总Block ，当Qcache_free_blocks相对此值较大时，可能用内存碎片，执行FLUSH QUERY CACHE清理碎片
- Qcache_free_memory：处于空闲状态的 Query Cache 内存总量
- Qcache_hits：Query Cache 命中次数
- Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次数，即没有命中的次数
- Qcache_lowmem_prunes：记录因为内存不足而被移除出查询缓存的查询数
- Qcache_not_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于 query_cache_type 设置的不会被 Cache 的 SQL语句
- Qcache_queries_in_cache：在 Query Cache 中的 SQL 数量
查询缓存相关的状态变量：
> show global status like 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33534696 |
| Qcache_hits | 0 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 14 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8）与缓存相关的计算
- 查询缓存中内存块的最小分配单位query_cache_min_res_unit ： (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
- 查询缓存命中率 ：Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100%
- 查询缓存内存使用率：(query_cache_size – qcache_free_memory) / query_cache_size * 100%
索引
索引是什么
-
索引：是特殊数据结构，定义在查找时作为查找条件的字段，在MySQL又称为键key，索引通过存储引擎实现
-
索引的优点：
索引可以降低服务需要扫描的数据量，减少了IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序I/O -
索引的缺点：
占用额外空间，影响插入速度
索引类型：
-
B+TREE、HASH、R TREE
-
聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
-
主键索引、二级（辅助）索引
-
稠密索引、稀疏索引：是否索引了每一个数据项
-
简单索引、组合索引
左前缀索引：取前面的字符做索引
覆盖索引：从索引中即可取出要查询的数据，性能高 -
冗余和重复索引：
注：需要避免冗余和重复索引
冗余索引：（A），（A，B）
重复索引：已经有索引，再次建立索引
MySQL索引
- https://blog.csdn.net/wdirdo/article/details/101000687
B+TREE索引※※※※※
二叉树：每个节点往下最多两个分支。
B-TREE索引
其中B表示balance
b-tree索引的特点：
1.关键字集合分布在整颗树中；
2.任何一个关键字出现且只出现在一个结点中；
3.搜索有可能在非叶子结点结束；
4.其搜索性能等价于在关键字全集内做一次二分查找；
5.自动层次控制；
其中根节点、分支节点、叶子节点中含有索引数据和对应索引编号的完整记录
==>数据块大小有规则，当数据块大小固定时：
==>根据记录的大小将会影响树的形状
记录越大，树状结构越高，树的层次越多，则查询到叶子节点的速度越慢
==>综上：查询的速度不一致，查询索引区间的记录会重复的从根节点开始
B-tree索引的缺点：
查询速度不一致，查询至叶子节点的时间较长
对于区间查询没有任何的优化
B+TREE索引
B+Tree相对于B-Tree有几点不同：
非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。
B+tree --> mysql数据库的索引结构
1、只有叶子节点才存放数据，根节点和分支节点只存放索引数据
==>可尽可能的降低数的层次
==>查询每个记录的时间相等
2、对于区间查询不用再从根节点去搜索
-
B+Tree索引：顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据
-
可以使用B+Tree索引的查询类型：
①全值匹配：精确所有索引列，如：姓wang，名xiaochun，年龄30
②匹配最左前缀：即只使用索引的第一列，如：姓wang
③匹配列前缀：只匹配一列值开头部分，如：姓以w开头的
④匹配范围值：如：姓ma和姓wang之间
⑤精确匹配某一列并范围匹配另一列：如：姓wang,名以x开头的只访问索引的查询 -
B+Tree索引的限制：
如不从最左列开始，则无法使用索引，如：查找名为xiaochun，或姓为g结尾
不能跳过索引中的列：如：查找姓wang，年龄30的，只能使用索引第一列 -
特别提示：
索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引
为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求
Hash索引
- Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好
- Memory存储引擎支持显式hash索引，InnoDB和MyISAM存储引擎不支持
- 适用场景：只支持等值比较查询，包括=, <=>, IN()
- 不适合使用hash索引的场景
不适用于顺序查询：索引存储顺序的不是值的顺序
不支持模糊匹配
不支持范围查询
不支持部分索引列匹配查找：如A，B列索引，只查询A列索引无效
其他索引
- 空间数据索引R-Tree（ Geospatial indexing ）
MyISAM支持地理空间索引，可以使用任意维度组合查询，使用特有的函数访问，常用于做地理数据存储，使用不多InnoDB从MySQL5.7之后也开始支持 - 全文索引(FULLTEXT)
在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎InnoDB从MySQL 5.6之后也开始支持
对某字段建立索引
- ①将字段抽取出来按照一定规则排序（排序规则为字符集默认的排序规则）
- ②然后排序的字段后面对应一个指针即记录存放的位置
- 对数据库建立索引可将其抽象为如下过程：
聚簇和非聚簇索引，主键和二级索引
-
聚簇索引：数据和索引紧密存放在一起
eg：innodb:数据和索引存放在一起
table_name.ibd -
非聚簇索引：数据和索引未存放在一起
eg：myisam数据和索引分开存放
table_name.MYD
table_name.MYI
-
主键索引：当对一张表创建主键时，将自动对主键创建索引，如上图所示
然后创建主键索引之后，主键就排序然后数据将会按照主键的顺序依次存放 -
二级索引：（非主键索引）二级索引排序之后，存放的是索引和主键的对应关系
因此二级索引的查询会经过主键索引再定位数据位置。
索引优化
索引优化策略：※※※※※
- 独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧
- 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性来评估索引选择性：不重复的索引值和数据表的记录总数的比值
- 多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
- 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧
索引优化建议
- 只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
- 尽量使用短索引，如果可以，应该制定一个前缀长度
- 对于经常在where子句使用的列，最好设置索引
- 对于有多个列where或者order by子句，应该建立复合索引
- 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
- 尽量不要在列上进行运算（函数操作和表达式操作）
- 尽量不要使用not in和<>操作
SQL语句性能优化
- 查询时，能不要就不用，尽量写全字段名
- 大部分情况连接效率远大于子查询
- 多表连接时，尽量小表驱动大表，即小表 join 大表
- 在有大量记录的表分页时使用limit
- 对于经常使用的查询，可以开启缓存
- 多使用explain和profile分析查询语句
- 查看慢查询日志，找出执行时间长的sql语句优化
索引的管理
- 创建索引：
①CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],…);
②ALTER TABLE tbl_name ADD INDEX index_name(index_col_name);
help CREATE INDEX; - 删除索引：
①DROP INDEX index_name ON tbl_name;
②ALTER TABLE tbl_name DROP INDEX index_name(index_col_name); - 查看索引：
SHOW INDEXES FROM [db_name.]tbl_name; - 优化表空间：
OPTIMIZE TABLE tb_name; - 查看索引的使用
userstat=1<配置文件>
SET GLOBAL userstat=1; 临时生效
SHOW INDEX_STATISTICS; 监控 索引的使用情况
创建复合索引：
mysql> create index idx_name_age on students(name,age);
对name前三个字母创建索引：
mysql> create index idx_name on students(name(3));
查询索引：
mysql> show index from students;
+----------+------------+----------+--------------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+----------+------------+----------+--------------+-------------+
| students | 0 | PRIMARY | 1 | StuID |
| students | 1 | idx_name | 1 | Name |
+----------+------------+----------+--------------+-------------+
其中Non_unique（唯一键索引）需要添加的，但是得保证添加的字段是唯一的
索引监控：
启用索引监控：userstat=1
查看索引的使用情况：> show index_statistics;
> select * from students where stuid=10;
...
> show index_statistics;索引的使用情况
+--------------+------------+------------+-----------+
| Table_schema | Table_name | Index_name | Rows_read |
+--------------+------------+------------+-----------+
| hellodb | students | PRIMARY | 1 |
+--------------+------------+------------+-----------+
但是索引的监控只能看到索引的使用情况，但是无法显示哪个命令对应使用了索引
查询语句分析：explain和profile
查询语句分析：explain
-
通过EXPLAIN来分析索引的有效性
-
EXPLAIN SELECT clause
获取查询执行计划信息，用来查看查询优化器如何执行查询 -
输出信息说明：
参考 https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
> explain select * from students ;
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 29 | |
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
type：all 全表扫描
possible_keys：可以使用的索引
key：使用的索引
有可能出现：possible_keys：可以使用的索引 但是查询时候不使用索引
==>利不利用索引要看具体的优化情况
如果不利用索引查询更快当然不会使用索引
索引目的就是为了提升查询效率
- id: 当前查询语句中，每个SELECT语句的编号
复杂类型的查询有三种：
简单子查询
用于FROM中的子查询
联合查询：UNION
注意：UNION查询的分析结果会出现一个额外匿名临时表 - select_type：
简单查询为SIMPLE
复杂查询：
SUBQUERY 简单子查询
PRIMARY 最外面的SELECT
DERIVED 用于FROM中的子查询
UNION UNION语句的第一个之后的SELECT语句
UNION RESULT 匿名临时表 - table：SELECT语句关联到的表
- type：关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式，以下顺序，性能从低到高
==>ALL: 全表扫描
==>index：根据索引的次序进行全表扫描；如果在Extra列出现“Using index”表示了使用覆盖索引，而非全表扫描
==>range：有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束于另一点
==>ref: 根据索引返回表中匹配某单个值的所有行
==>eq_ref：仅返回一个行，但与需要额外与某个参考值做比较
==>const, system: 直接返回单个行 - possible_keys：查询可能会用到的索引
- key: 查询中使用到的索引
- key_len: 在索引使用的字节数
- ref: 在利用key字段所表示的索引完成查询时所用的列或某常量值
- rows：MySQL估计为找所有的目标行而需要读取的行数
- Extra：额外信息
Using index：MySQL将会使用覆盖索引，以避免访问表
Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤
Using temporary：MySQL对结果排序时会使用临时表
Using filesort：对结果使用一个外部索引排序
查询语句分析：profile
-
profile：可以用来查看某个指令的执行所花的时间。
-
注：profiling 只是系统变量，不是服务器选项。
> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF | 默认未启用
+---------------+-------+
profiling ==>临时测试使用
启用：
> set profiling=on;
测试profile：
> select *,sleep(1) from teachers;
> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------+
| 1 | 0.00069030 | show variables like 'profiling' |
| 2 | 4.00463092 | select *,sleep(1) from teachers |
| 3 | 0.00012638 | show profilings |
+----------+------------+---------------------------------+
查看具体的Profiles中具体的SQL语句：
> show profile for query 2;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000055 |
| Waiting for query cache lock | 0.000006 |
| init | 0.000003 |
| checking query cache for query | 0.000064 |
| checking permissions | 0.000009 |
| Opening tables | 0.000016 |
| After opening tables | 0.000005 |
| System lock | 0.000005 |
| Table lock | 0.000006 |
| init | 0.000023 |
| optimizing | 0.000009 |
| statistics | 0.000013 |
| preparing | 0.000052 |
| executing | 0.000005 |
| Sending data | 0.000046 |
| User sleep | 1.001106 |
| User sleep | 1.001030 |
| User sleep | 1.001033 |
| User sleep | 1.001002 |
| end | 0.000019 |
| query end | 0.000011 |
| closing tables | 0.000004 |
| Unlocking tables | 0.000012 |
| freeing items | 0.000007 |
| updating status | 0.000018 |
| logging slow query | 0.000067 |
| cleaning up | 0.000004 |
+--------------------------------+----------+
==>可见命令执行过程，做了哪些步骤。
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
