SQL语句查询优化

1.如何写语句

1. 使用慢查询(怎么开启),对慢语句执行explain
2. 尽量不要用not in和!=这样的排除语句,引擎会放弃使用索引直接全表扫描(这条有问题)
3. 索引列不要放在函数或者表达式中,比如where a-1=10是用不到索引的
4. 尽量不要用NULL来筛选,可以用0代替NULL(这条有问题)
5. 避免用or来连接,否则会放弃索引,可以用select union select 来代替(这条有毛病啊)
6. 模糊查询开头也会用不到索引,如where name like '%abc%',(这条我有疑问..)

7. 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：select id from t where num=@num,可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num(这条第一次听)

8. 尽量用数字,字符型太长可能会降低查询连接的性能,读入内存中比较会占用大,尽可能的使用 varchar/nvarchar 代替 char/nchar

2.索引设计

1. 索引字段,不要加到修改多的字段上,尽量不要加到重复过多的上面
2. 对于group by,order by和where字段后面的条件可以考虑加索引
3. 对于多列索引,建立联合索引,这样(a,b,c)相当于(a),(a,b)等,,其中顺序要遵循最左匹配原则
4. join on用到会更高效(?)
5. 尽量利用覆盖索引,即不回表,像平时用select * 而不是指定索引列就可能会导致回表,回表会增加IO次数
6. 索引选择器,重复少的放左边(?)

3.唯一索引和普通索引的选择

1. 查询时:唯一索引查找后则不继续查,普通索引会继续找到直到不等,但开销很小
2. 更新时:普通索引可以用change buffer,而unique index因为更新时要判断唯一性要读进整页故不能用change buffer;可以把写操作给缓存下来,下次读的时候进行merge操作,这样的话可以提高写入速度,但是这样的开销不是特别大
3. 唯一性要求不高,或者业务代码可以保证唯一性的时候,可以用普通索引,因为普通索引是可以用到change buffer;
4. 对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统
5. 利用覆盖索引,用覆盖索引也可以避免,比如排序时用到的一些临时文件(?)
6. 在版本xx以后,开MRR(multi range read),即把回表之前,把ID读到一个buffer中,进行一个排序,把原来的一个随机操作变成一个顺序操作

4.没走索引的情况:

1. 连接查询的时候,两个表编码不一样,比较的字段类型不一样,比如string和ID比较,会用到一个隐式的cast
2. 还有可能因为表增删太多,导致内存空洞太多,会造成索引的选择问题,(是InnoDB的主索引树增删的页分裂导致的吗)
3. 没走索引的原因,MYSQL底层问题:mysql的底层有会根据随机采样来计算索引基数是如何,如果采样错了,虽然索引列的重复性或者说选择性比较大,但是mysql的底层认为小,就不走索引,页就是走错索引的意思,一个解决办法是用force index来让它强制走,force index是应急方案,迁到其他数据库就用不了了,还需要做代码的重新发布;;或者可以用analyze table来刷新下让它重新采样,;;
4. 或者是不是索引统计信息有问题,可以用analyze table重新统计所有信息,因为索引信息不是一个准确值,而是一个随机采样的过程,

详细补充

3.2对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；对于非唯一索引来说，直接将记录更新在change buffer中，直接结束

4.4,对于索引是全表扫描还是走索引,数据库系统会进行一个推测,有时候全表扫描会比走索引的代价低,因为索引读取.从辅助索引跳转到主索引至少也要两次IO,

而数据库系统是怎么判断怎么走的呢?是通过索引的区分度,(区分度可以由count(distinct left(列名, 索引长度))/count(*)来表示),也就是索引列的重复值个数,这个值叫做基数,基数越大,区分度越高则走索引意味着会更有优势,而这个索引基数是通过随机采样的方式来得到的(因为全部采样代价太高了),

我们也可以通过show index from t;来查询索引的基数和实际是否符合，如果和实际很不符合的话，我们可以用这条命令analyze table t;来统计索引的基数

参考资料

1. 帅地的https://zhuanlan.zhihu.com/p/62941196
2. 一千多赞https://zhuanlan.zhihu.com/p/72071609
3. https://blog.csdn.net/qq_25827845/article/details/90736098里面有区分度的计算

TODO

1. https://www.jianshu.com/p/b84a02fb12b5