Relationship between post and category is not stored in the posts table. You must extend your query for additional tables.
By category id:
SELECT YEAR(p.post_date) FROM {$wpdb->posts} p
JOIN {$wpdb->term_relationships} tr ON tr.object_id = p.id
JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
WHERE tt.term_id = 4 AND p.post_status="publish" AND p.post_type="post"
GROUP BY YEAR(p.post_date) DESC
Or by category slug:
SELECT YEAR(p.post_date) FROM {$wpdb->posts} p
JOIN {$wpdb->term_relationships} tr ON tr.object_id = p.id
JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
JOIN {$wpdb->terms} t ON t.term_id = tt.term_id
WHERE t.slug = 'your_category_slug' AND p.post_type="post" AND p.post_status="publish"
GROUP BY YEAR(p.post_date) DESC