| 分类 | MySQL写法 | TiDB写法 | 注意事项 |
| 建表 | alter table A add column phone bigint(20),add column address varchar(100); | alter table A add column phone bigint(20); alter table A add column address varchar(100); | 1.一个DDL脚本仅支持一个字段修改 2.新建表时,尽量提前规划好相应字段 |
| 建表 | create table A(`id` bigint(20) NOT NULL AUTO_INCREMEN) | create table A(`id` bigint(20) NOT NULL AUTO_INCREMEN) | TiDB自增主键全局唯一,但不严格递增(仅各Server内部连续) 需要严格连续自增主键时,业务系统自己生成写入 |
| 建表 | create table A as select * from B | 不支持 | |
| 建表 | create temporary table A | 不支持 | 不支持临时表 |
| SQL DML提交前,建议结合explain和explain analyze命令和业务场景,确认执行计划 |
三、 查询SQL语法差异&优化建议
| 分类 | MySQL写法 | TiDB写法 | 注意事项 |
| 查询 (结果条数统计) | select * from A select count() from A | select name,age,address from A select count(age) from A | 1.避免全量字段查询,节省网络带宽 2.当开启TiFlash统计行数据时,TiDB会使用列模式提升查询性能 |
| 查询 (闭区间查询) | select name,age from A where age>10 | select name,age from A where age>10 and id110 and cityName!='北京' | 尽可能的将使用频率高的,经常被点查使用的列排在前面,将经常进行范围查询的列排在后面 |
| 查询 (显示优化规则) DBA不建议 | select name,age from A where name='张三' | select name,age from A where name='张三' use index(name_age) | 显示通知TiDB优化器,使用name_age索引 |
| 查询 (覆盖索引) | select name,age from A where name='张三' order by age | ORDER BY,GROUP BY,DISTINCT 的字段需要添加在索引的后面,形成覆盖索引 |
| 查询 (显示优化规则) DBA不建议 | select name,age from A where name='张三' | select /+ read_from_storage(tiflash[A]) */ name,age from A where name='张三' | 显示通知TiDB优化器,使用TiFlash提升性能 |
| MySQL常见SQL优化规则(如not in,like ‘abc%’,减少查询返回列,避免在索引列使用函数),对于TiDB同样适用 |
四、 SQL执行计划差异&优化建议
| 分类 | MySQL写法 | TiDB写法 | 注意事项 |
| 执行计划 | explain select count() from A | explain select count() from A explain analyze select count(*) from A | 1.TiDB提供explain和explain analyze两种查询计划分析,前者不会执行,后者会实际执行 2.explain参考:https://docs.pingcap.com/zh/tidb/stable/explain-walkthrough 3.explain analyze参考:https://docs.pingcap.com/zh/tidb/stable/sql-statement-explain-analyze/ |
| 查询 (结果分析优化) | operator中包含stats:pseudo | SQL对应表统计信息已失真,执行analyze tableName修复即可(注:关注数据期间卡表修复对业务的影响) |
| 查询 (类型优化) | select name,age from A where zip=0 (其中zip为bit类型) | select name,age from A where zip=0 (修改zip为int类型) | TiDB字段尽量使用常见mysql类型 |
| 注意:analyze tableName对TiDB集群的影响较大,执行前千万与DBA做好沟通评估,临时情况可通过显示指定索引(USE INDEX)绕开流量高峰期 |
五、 TiDB执行计划分析简介
下面我们通过2个实际SQL说说TiDB的执行计划:
l SQL1
*1:IndexLookUp算子:根据索引获取结果记录
*2 & *3:Build算子总是优先于Probe算子执行,*2 算子根据条件从索引中获取数据,*3算子在结果中匹配结果
*4:TableRowIdScan:通过 *3 算子结果中的表主键id从TiKV获取行记录
*5:cop【tikv】标识将计算逻辑从tidb下放到tikv执行,同理还会有cop【tiflash】
*6:tikv通过范围索引扫描出对应记录
*7:根据id获取行记录后直接返回上层,无需排序
------------------------------------------------------------------------------------------------------------------------------
l SQL2 优化前,两表直接join:
explain analyze SELECT m.id AS id, m.order_id AS orderId, s.status AS status,m.sendpay_map as sendPayMap FROM tableA m LEFT JOIN tableB s on m.order_id = s.order_id WHERE m.id >= 100 AND m.id = 100 AND m.id