sql优化问题

1.优化什么

　　优化查询速度 避免全表扫描、避免索引不命中、避免文件排序、磁盘排序、未完待续。

2.什么是索引

　　是一种帮助数据库快速查询的数据结构。

3.为什么选择b+ tree

　　提高查询速度，减少磁盘io

3.1 各个数据结构的特点

　　链表：查询次数O(n) 

　　HASH桶 查询速度很快，但是对于 范围查询、不等于等不能查询 、hash冲突

　　二叉树：可能成为链表。树的高度比较高、查询次数比较多

　　b树：相对降低了io次数，但是因为数据存在各个节点中，减少了数据页承载数据的量，所以不如b+树

　　b+树：非叶子节点存在冗余、叶子节点存在双向指针、每个数据也可容纳更多的数据、高度为三的b+树可达到4千万级数据

3.2主键

 　　1.建议设置成整型的自增主键

　　 2.整型 相对比其他数据类型所占字节少、自增避免数据库做树优化浪费性能、不增加主键数据库innodb会自动生成

4.sql 执行的过程

　　客户端---连接器--缓存---分析器--有优化器--执行器---执行引擎

4.1.客户端：navicat 等

4.2.连接器：经过tcp的三次握手连接后，先校验账号密码，在把权限缓存，后续的操作权限都是这个缓存中获取，所以即使管理员更改了权限也要等再次连接的时候生效。

　　查看连接的进程：show processlist;   kill id  杀掉进程；  

　　默认休眠时间：8小时

 　　show global variables like 'wait_timeout';

       set global wait_timeout=28801

4.3缓存：

 　　查看缓存是否开启 show global variables like "%query_cache_type%"; 0 off 1:on 2 :demand (查询字段前面增加 SQL_CACHE)  demand 比较灵活

　　查看缓存命中的情况:show status like "%Qcache%";

　　Qcache_free_blocks:表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理。

　　Qcache_free_memory:查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，DBA可以根据实际情况做出调整。

　　Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。

　　Qcache_inserts:表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数，次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。

　　Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。

　　Qcache_not_cached:表示因为query_cache_type的设置而没有被缓存的查询数量。

　　Qcache_queries_in_cache:当前缓存中缓存的查询数量。

　　Qcache_total_blocks:当前缓存的block数量。

4.4分析器：

　　词法分析器 ---语法分析器---语义分析器---构造语法树-生成执行计划  sql语句写错了就在此步骤发现 sql error

4.5 优化器：

　　优化执行所选用的索引、语句的调整、等

4.6执行器：调用引擎接口执行sql

4.7用到的sql

　　连接数据库：mysql -uroot -p 

　　创建用户：　create user 'xxx'@'%' identified by '123456';

　　赋予权限：　grant all privileges on *.* to 'xxx'@"%";

　　刷新数据库：flush privileges;

　　查看用户全新：show grants for 'xxx'@'%';

　　更改密码：alter user 'xxx'@'%' identified with mysql_native_password by '654321';

　　更改用户权限：update user set host='localhost' where user='xxx';

　　查看表结构: describe user;

　　创建索引：alter tablename add index indexname(colum);

　　清空表：

4.4logbin

5优化sql 的工具

5.1 工具介绍

5.2 explain

5.3 show warning

5.4 开启看sql的 评估过程

6sql 查询的三个途径

7.影响sql选择查询途径的原因

7.1扫描行数

举例：

7.2回表次数

举例:

7.3是否可用索引：是否有序

7.4 覆盖索引

8.优化实例

8.1order by 

8.2 group by 

8.3 覆盖索引

8.4 索引下推

8.5 最左原则

9索引创建原则

9.1

9.2

9.3

9.4

9.5