sql优化

1.SELECT前加EXPLAIN，查看搜索行数，提示信息进行优化
2.IN中包含的数据不宜过多，能用BETWEEN就不用IN
3.SELECT后必须指明字段，而不是用*
4.只要一条数据时，使用LIMIT 1
5.排序字段无索引，尽量不排序。
6.尽量不用or，or会导致全表搜索，用union代替or。
EXPLAIN SELECT * FROM sys_user WHERE nick_name = ‘王佳磊’ OR nick_name = ‘小李’
EXPLAIN
SELECT * from sys_user WHERE nick_name = ‘王佳磊’
UNION ALL
SELECT * from sys_user WHERE nick_name = ‘小李’
7.尽量用union all代替union。
8.排序不用 ORDER BY RAND()。
9.区分in和exists， not in和not exists
IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况
select * from 表A where id in (select id from 表B)
select * from 表A where exists(select * from 表B where 表B.id=表A.id)
10.合理使用分页。
当查询时使用limit的话，分页太多会导致查询变慢。
11.分段查询时，跨度不宜过大。
12.避免在where中对字段进行null判断。
null判断时，数据库会放弃走索引而进行全表扫描。
SELECT * from sys_user where age is null

13.最好不要用%前缀进行模糊查询。
如果要实现like %name%进行查询，则需要创建全表索引，同时需要注意的是查询语句的写法与普通索引的区别
14.避免where语句中对表达式的操作。
原因：这会造成引擎放弃使用索引。
反面例子：select user_id,user_project from user_base where age*2=36;
可更改为：select user_id,user_project from user_base where age=36/2;
15.避免类型转换
EXPLAIN SELECT user_name from sys_user WHERE user_name = 123123 ;

EXPLAIN SELECT user_name from sys_user WHERE user_name = “123123” ;

16.对于联合索引来说，要遵守最左前缀法则
常用的查询字段放在最前面
17.注意范围查询语句
如果存在范围查询，比如between,>,<等条件时，会造成后面的索引字段失效
18.join中利用小表去驱动大表
19.创建索引时字段太大时可以取前几个字符创建索引，满足左前缀要求；

数据库引擎：

https://blog.csdn.net/qq_29168493/article/details/79066399
1.MyISAM:
B+数叶子节点存储地址。

2.InnoDB:
每次读取16k，叶子节点存储查询结果的数据。如果name列加索引，索引查找结果为主键，然后通过主键查询对应的数据

索引的结构：
1.hash：
hash值取余，存储到对应的链表
缺点：1.需要将所有的数据存储至内存中，比较耗费内存空间；
2.如果所有的查询都是等值查询，那么hash确实很快，但是实际工作中范围查找的数据更多不适合hash结构。
1.Hash索引仅仅能满足“=”，“IN”，不能支持范围查询
2.对于排序操作Hash索引也满足不了
3.Hash索引不能避免表扫描
4.当有大量数据的Hash值相等的时候Hash索引的性能大打折扣
2.二叉树红黑树：
缺点：1.深度变深IO读取次数变多，影响效率。
（可能）2.一次io读写的数据页是4k，但二叉树只有左右子节点，如果无法达到4k造成空间浪费；
3.B树：
特点：1.所有键值分布在整棵树中
2.搜索有可能在非叶子节点结束，在关键字全集内做一次查找，性能逼近二分查找
3.每个节点最多拥有m个子树
4.根节点至少有俩个子节点
5.分支节点至少拥有m/2颗子树（除根节点和叶子节点）
6.所有叶子节点都在同一层、每个节点最多可以有m-1和key，并已升序排列

3.B+树：
mysql使用的B+树

索引分类：
1.主键索引：
主键是一种唯一索引，但他必须指定为PRIMARY KEY，每个表只能有一个主键。
2.唯一索引：
索引列的所有值只能出现一次，值可以为空。
3.普通索引：
基本的索引类型，值可以为空，没有唯一性的限制。
4.全文索引：
全文索引的类型为FULLTEXT。全文索引可以在varchar、char、text类型上建索引。
5.组合索引：
多列值组成一个索引，专门用于组合搜索。
索引下推：
name,age为条件查询之前将符合name的数据返回到server层，再筛选符合age条件的数据
索引下推：直接获取符合条件的name和age数据返回到server层中。
回表查询：
索引覆盖：

EXPLAIN：
如果explain出来的type列的值为index，则说明mysql使用了索引来做排序

查询数据库表信息：
select table_name, table_comment, create_time, update_time from information_schema.tables
where table_schema = (select database())
AND table_name NOT LIKE ‘qrtz_%’ AND table_name NOT LIKE ‘gen_%’
AND table_name NOT IN (select table_name from gen_table);

select table_name, table_comment, create_time, update_time from information_schema.tables
where table_name NOT LIKE ‘qrtz_%’ and table_name NOT LIKE ‘gen_%’ and table_schema = (select database())
and table_name = ‘sys_config’;

查询数据库表字段信息
select column_name, (case when (is_nullable = ‘no’ && column_key != ‘PRI’) then ‘1’ else null end) as is_required, (case when column_key = ‘PRI’ then ‘1’ else ‘0’ end) as is_pk, ordinal_position as sort, column_comment, (case when extra = ‘auto_increment’ then ‘1’ else ‘0’ end) as is_increment, column_type
from information_schema.columns where table_schema = (select database()) and table_name = (‘sys_config’)
order by ordinal_position