SQL-结构化查询语言

SQL基本语法

SQL语句可以单行或者多行书写,以分号结尾
可以使用空格或者缩进,来增强语句的可读性
MySQL不区分大小写,建议关键字使用大写

SQL语句分类

DDL:数据定义语言,对库表结构的操作。
DML:数据操作语言,对表记录进行更新(增删改)。
DQL:数据查询语言,对表的记录进行查询。
DCL:数据控制语言,对用户进行创建及授权。

索引

索引,是帮助MySQL高效获取数据的数据结构。
索引,目的在于提高查找效率,可以类比字典。
索引,是排好序的快速查找的数据结构。
索引,会影响where和orderby(查找和排序)。

数据本身之外,数据库还维护着一个满足特定查找算法的数据结构;
这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,
这种数据结构就是索引。
默认一遍都是B+数索引。

索引优势劣势

劣势:
索引也是要占用空间的。
虽然索引提供了查询速度;但是降低了写入(插入更新)速度。
索引只是提供查询效率的一个因子,需要花大量时间研究优化。

优势:
提高数据检索效率,降低数据库的IO成本。
通过索引对数据排序,降低数据排序成本,降低了cpu的消耗。

索引分类

单值索引,即一个索引只包含单个列,一个表可以有多个单值索引。
唯一索引,索引列的值必须唯一,但允许有空值。
符合索引,即一个索引包含多个列。

添加索引:
添加主键: alter table tb1 add primary key (column_list)
添加唯一索引:alter table tb1 add unique index_name (column_list)
添加一般索引:alter table tb1 add index index_name(column_list)
添加全文索引:alter table tb1 add fulltext index_name(column_list)

索引结构:BTree索引

创建索引的情况

主键自动建立唯一索引
频繁作为查询条件的列,应该创建索引
查询中与其他表关联的字段,外键关系建立索引
频繁更新的字段不适合创建索引
where条件里用不到的字段,不要创建索引
单键索引与组合索引,高并发情况下,倾向于选择组合索引
查询中,需要排序的字段
查询中,统计或者分组的字段

不需要创建索引的情况

表数据很少
经常增删改的列
数据重复且分布均匀。
例如:10w行数据,某列A,只有两种状态且每种状态分布为50%;那么一般对A建索引,不会提高查询效率。
索引的选择性是指索引列中不同指的数量与表数据量的比值。如果一个表有10w条记录,表索引列有9w9999个不同值;那么这个索引的选择性就是:9w9999 / 10w 约等于 0.999。一个索引的选择性越接近于1,这个索引的效率就越高。

索引失效

1、使用!= 或者 <> 导致索引失效
例如:

SELECT a.* FROM coupon_state_manager a WHERE a.customer_id != 101

2、类型不一致导致的索引失效
(1)字段varchar,条件为int,会导致失效
例如:

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_phone = 13756494953

(2)字段int,条件字符型,不会导致索引失效
例如:

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_id = '101'

3、函数导致的索引失效
例如:

EXPLAIN SELECT * FROM coupon_state_manager  WHERE DATE(receive_date) = '2020-02-17 14:08:31'

4、运算符导致的索引失效
对列进行了(+,-,*,/,!), 那么都将不会走索引
例如:

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_id -1 = 100

5、OR引起的索引失效
(1)or连接的字段,都建立了索引,不会失效
(2)or连接的字段,如果其中一个没建立索引,则会是整个索引失效
例如:

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_id = 101 or coupon_id = 2

6、模糊搜索导致索引失效
(1)'%abc' '%abc%'会导致索引失效(要查询的字段,都在索引中,不会失效)
例如:

EXPLAIN SELECT * FROM coupon_state_manager  WHERE  customer_name LIKE '%李%'

(2)'abc%'不会导致索引失效
例如:

EXPLAIN SELECT * FROM coupon_state_manager  WHERE  customer_name LIKE '李%'

7、NOT IN、NOT EXISTS导致索引失效
例如:

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_id NOT IN (100,101)

8、IS NOT NULL会导致索引失效
例如:

EXPLAIN SELECT * FROM coupon_state_manager  WHERE  customer_name IS NOT NULL

9、复合索引
Mysql从左到右的使用索引中的字段;
一个查询可以只使用索引中的一部分,但只能是最左侧部分。
例如索引是key index (a,b,c)。
可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 。
当最左侧字段是常量引用时,索引就十分有效。

创建复合索引时,应该仔细考虑列的顺序。
对索引中的所有列执行搜索或仅对前几列执行搜索时,复合索引非常有用;
仅对后面的任意列执行搜索时,复合索引则没有用处。

上一篇:微信开发笔记


下一篇:第四篇 :微信公众平台开发实战Java版之完成消息接受与相应以及消息的处理